1. Database concepts
1.1 Codd’s Rule for RDBMS – 12 Rule
Rule 1: The Information RuleAll data should be presented in a table formRule 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
- 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
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.
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
Comments