Skip to main content

Database Knowledge for a Tester - Part I

1. Database concepts

1.1 Codd’s Rule for RDBMS – 12 Rule

Rule 1: The Information RuleAll data should be presented in a table form
Rule 2: Guaranteed Access RuleAll data should be accessible without ambiguity.
Rule 3: Systematic Treatment of Null ValuesSupport for Null values which is distinct from an empty string or a number with a value of zero.
Rule 4: Dynamic Online Catalog based on the Relational modelA relational database must provide access to its structure through the same tools that are used to access the data.
Rule 5: Comprehensive Data Sublanguage RuleThe DB must support at least one clearly defined language that includes functionality for Data Definition, Data Manipulation, Data Integrity, and Database Transaction Control.
Rule 6: View Updating RuleEach view in the database should support the same full range of data manipulation that has direct access to a table available. In reality, providing update and delete access to logical views is difficult and is not fully supported by any current database.
Rule 7: High-level Insert, Update, and DeleteInsert, Update and Delete operations should be supported for any retrievable set rather than just for a single row in a single table.
Rule 8: Physical Data IndependenceThe user is isolated from the physical method of storing and retrieving information from the database.
Rule 9: Logical Data IndependenceHow data is viewed should not be changed when the logical structure of the database changes. This rule is particularly difficult to satisfy.
Rule 10: Integrity IndependenceThe database language should support constraints on user input that maintain database integrity.
Rule 11: Distribution IndependenceA user should be totally unaware of whether or not the database is distributed.
Rule 12: Non Subversion RuleThere should be no way to modify the db structure other than through the multiple row database language.

1.2 DBMS Vs RDBMS

DBMS – Database Management System.The relationship between 2 tables or files is maintained programmatically.

  • Does not support Client/Server Architecture
  • Does not support distributed databases
  • There is no security of data
RDBMS – Relational Database Management SystemThe relationship between 2 tables or files can be specified at the time of table creation
  • Support Client/Server Architecture
  • Support distributed databases
  • There are multiple levels of security
  • Logging in at O/S level
  • Command level
  • Object Level

1.3 Normalization

It is a process that helps in designing the table structure for an application.
Normalization is technique that:
  • Decomposes data into two dimensional tables.
  • Eliminates any relationship in which table data does fully depend upon the primary key of a record.
  • Eliminates any relationship that contains transitive dependencies.

1.3.1 First Normal Form

A table is in 1st Normal Form if:
  • There are no repeating group
  • All the key attributes are defined.
  • All attributes are dependent on a primary key
1.3.2 Second Normal Form
A Table is in 2nd Normal form if:
  • Its in 1st Normal Form
  • It includes no partial dependencies i.e. an attribute is dependent on only a part of a primary key.

1.3.3 3rd Normal Form

A table is in 3rd Normal form if:
  • Its in 2nd Normal Form
  • It contains no transitive dependencies.
Note: A general case of transitive dependencies is as follows:
X, Y, Z are 3 columns in table.
If Z is related to Y
If Y is related to X
Then Z is indirectly related to X
This is when Transitive dependencies exist.
Note: Normalization slow down the database query for fetching records. So sometime to make the query faster denormalization is deliberately done on the table.

1.4 Illustration of Normalization






2 Components of SQL

2.1 DDL, DML & DCL Commands

DDL – Data Definition Language – No Rollback is possible – Commit is issued implicitly.Examples are:
· CREATE
· ALTER
· DROP
· TRUNCATE – Remove all the records from a table, including all spaces allocated for the records are removed
· GRANT
· REVOKE
DML – Data Manipulation LanguageExamples are:
· INSERT
· UPDATE
· DELETE – Deletes all records from a table, the space for the records remain.
· SELECT – more specifically a DQL – data query language.
DCL – Data Control Language
Examples are:
· COMMIT
· ROLLBACK

References

SQL, PL/SQL Third Edition by Ivan Bayross.

Download

Click here to download a PDF copy of this article.
Labels:

Comments

Post a Comment

Popular posts from this blog

Why to do Software Testing?

Regardless of the limitations, testing is an integral part in software development. It is broadly deployed in every phase in the software development cycle. Typically, more than 50% percent of the development time is spent in testing. Testing is usually performed for the following purposes: To improve quality. As computers and software are used in critical applications, the outcome of a bug can be severe. Bugs can cause huge losses. Bugs in critical systems have caused airplane crashes, allowed space shuttle missions to go awry, halted trading on the stock market, and worse. Bugs can kill. Bugs can cause disasters. The so-called year 2000 (Y2K) bug has given birth to a cottage industry of consultants and programming tools dedicated to making sure the modern world doesn't come to a screeching halt on the first day of the next century. [Bugs] In a computerized embedded world, the quality and reliability of software is a matter of life and death. Quality means the conformance to the ...
1 comment
Read more

Automation Framework - Beyond Buzzword

1. What is Automation? Controlling one application for another application. 2. What is Framework? A structure which consists of all the building blocks for developing an application. 3. What is Automation Framework? You are going to use the building block of structure to develop application which is going to control another application. 4. Background – Object Oriented If you have done development in any OO programming language then you can easily understand the concept of Framework and Automation. Take a simple example of Calculator. Can we call it an automated tool? Yes it is. You know why, because it controls the simple and scientific calculations. If you are asked to develop a program like calculator in any OO language, then you are going to make use of many pre-existing library, their methods, properties to accomplish your goal. Now these library or readymade utilities can be bundles in a framework and you just make use of that framework to execute...
3 comments
Read more

HP Quality Center - Best Practices

1.Introduction Quality Center is a test management tool which provides very good features for managing both your manual and automated test cases. This paper highlights the best practices for managing your test cases. When you open Quality center, depending on your rights it display the below mentioned option in the sidebar: 1. Requirements 2. Test Plan 3. Test Lab 4. Defects 5. Dashboard 2.Requirements When you have assigned with the responsibility of developing your test cases in the quality center then you must be wondering where to start with. I am going to share my experience to overcome such a situation. You need to find the solution of some question before you start writing your test cases. 1. Is your requirement developed and available? 2. Is your requirement organized in a logical sequence? If answer to both of the above question is Yes, then you can start with Requirement option in the side bar. In case your requirement is under development, then you keep your...
3 comments
Read more