Overview
This lecture explains the concept of keys in Database Management Systems (DBMS), their importance, and the different types used to uniquely identify records and maintain data integrity.
Introduction to DBMS Keys
- A DBMS key is an attribute or set of attributes that uniquely identifies a record (row) in a table.
- Keys prevent ambiguity when accessing data and ensure each row is uniquely identifiable.
- Keys enforce data integrity and establish relationships between tables.
Types of DBMS Keys
Super Key
- A super key is any combination of attributes that can uniquely identify a record in a table.
- Examples: sID, email, registrationID, or combinations like sID+email.
Candidate Key
- Candidate keys are minimal super keys; removing any attribute makes them unable to uniquely identify records.
- Each candidate key is unique and minimal (no subset is a super key).
- In the example, sID, email, and registrationID are candidate keys.
Primary Key and Alternate Key
- The primary key is a candidate key chosen by the database administrator to uniquely identify records.
- All other candidate keys not selected as the primary key are called alternate keys.
Foreign Key
- A foreign key is an attribute in one table that links to the primary key of another table, establishing a relationship.
- Foreign keys ensure referential integrity between tables (e.g., branch code in student table referring to branch table).
Composite Key
- A composite key is a super key with more than one attribute.
- Example: sID + email or registrationID + email together as a key.
Compound Key
- A compound key is a composite key where at least one attribute is a foreign key.
Surrogate Key
- A surrogate key is an artificial primary key created when no natural key exists; it has no meaning in business context, only for identification.
Key Terms & Definitions
- Super Key — Attribute(s) that uniquely identify records, including all possible combinations.
- Candidate Key — Minimal super key; smallest set able to uniquely identify records.
- Primary Key — Chosen candidate key used for uniquely identifying records.
- Alternate Key — Candidate keys not selected as primary key.
- Foreign Key — Attribute linking one table to another, enforcing relationships.
- Composite Key — Key consisting of more than one attribute.
- Compound Key — Composite key with at least one foreign key element.
- Surrogate Key — Artificially created key for identifying records when no natural key exists.
Action Items / Next Steps
- Review examples of different DBMS keys in your textbook.
- Practice identifying keys in sample database tables.
- Prepare questions on DBMS keys for the next class discussion.