Browse By

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

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





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

Leave a Reply