Database Knowledge for Tester – Part 1
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
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.
· TRUNCATE – Remove all the records from a table, including all spaces allocated for the records are removed
DML – Data Manipulation Language
· 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