Data Integrity in Databases & Various Keys in RDBMS

Lecture Notes: Data Integrity in Databases & Various Keys in RDBMS

Data Integrity in Databases

Importance of Data Integrity - Data integrity ensures accuracy, consistency, and reliability of data throughout its lifecycle. It's crucial for maintaining the quality and trustworthiness of data in databases.

Ensuring Data Accuracy - Implemented through constraints, validation rules, and database transactions to prevent invalid data entry and maintain data accuracy.

Validation Rules - Set of rules that data must conform to before being accepted into the database. This can include data type checks, range constraints, and format specifications.

Various Keys in RDBMS

Primary Keys - A primary key is a column (or a set of columns) used to uniquely identify each row in a table. - Example: In an Employee table, EMPID could serve as the primary key since it uniquely identifies each employee.

Secondary Keys - Secondary keys are attributes or sets of attributes that are not primary keys but can be used for indexing and retrieval. - Example: EmpLicense and EmpPassport might be used as secondary keys to look up employees based on their license or passport number.

Super Keys - A super key is a set of one or more attributes that, taken collectively, allow us to uniquely identify a record in a table. - Example: The combination of EMPID and EmpName could form a super key, as it uniquely identifies employees, although EMPID alone is sufficient as a primary key.

Foreign Keys - A foreign key is a column (or a set of columns) in one table that references the primary key of another table. It's used to establish a link between the data in two tables. - Example: DID in the Employee table is a foreign key that references Did in the Designations table, establishing a relationship between an employee and their designation.

Candidate Keys - Candidate keys are columns in a table that qualify as unique identifiers for the table's rows. Out of these, one is chosen as the primary key, and the rest are alternative keys. - Example: EMPID, EmpLicense, and EmpPassport could all serve as candidate keys because each of them can uniquely identify an employee. EMPID might be chosen as the primary key due to its simplicity and relevance.

Examples Using Employee and Designations Tables

1. Employee Table (EMPID, EmpName, EmpLicense, EmpPassport, DID)
2. EMPID: Primary Key, uniquely identifies each employee.
3. EmpLicense & EmpPassport: Secondary/Candidate Keys, can be used to look up employees.
4. DID: Foreign Key, links an employee to their designation in the Designations table.
5. Designations Table (Did, Designations)
6. Did: Primary Key in the Designations table, referenced by the DID foreign key in the Employee table.

---