Database Knowledge for a Tester - Part I

1. Database concepts
1.1 Codd’s Rule for RDBMS – 12 Rule
Rule 1: The Information Rule
All data should be presented in a table form
Rule 2: Guaranteed Access Rule
All data should be accessible without ambiguity.
Rule 3: Systematic Treatment of Null Values
Support 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 model
A relational database must provide access to its structure through the same tools that are used to access the data.
Rule 5: Comprehensive Data Sublanguage Rule
The 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 Rule
Each 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 Delete
Insert, 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 Independence
The user is isolated from the physical method of storing and retrieving information from the database.
Rule 9: Logical Data Independence
How 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 Independence
The database language should support constraints on user input that maintain database integrity.
Rule 11: Distribution Independence
A user should be totally unaware of whether or not the database is distributed.
Rule 12: Non Subversion Rule
There 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 System
The 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 Language
Examples 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.