DBMS Keys: Super, Candidate, Primary, Foreign

DBMS Keys Overview

• DBMS keys are attributes or sets of attributes uniquely identifying records (rows) in a database table.
• Keys enable unique access to data rows, prevent duplicate entries, ensure data integrity, and establish relationships between tables.
• Without keys, retrieving specific data rows becomes difficult, potentially returning multiple or no records.
• Keys enforce data identity, ensuring all insertions are identifiable and maintain data integrity.
• Relationships between tables can be established using keys, ensuring integrity across related data.

Types of DBMS Keys

• There are eight types of DBMS keys: super key, candidate key, primary key, alternate key, foreign key, composite key, compound key, and surrogate key.
• Super key serves as a default containing all possible keys.
• Composite and compound keys are related, functioning as subsets of superkeys

Super Key

• A super key is a set of attributes uniquely identifying records in a table.
• A table with attributes like sID, registration ID, and email, any combination of these (e.g., sID, email) qualifies as a super key.
• All possible combinations of attributes that can uniquely identify tuples are considered super keys.

Candidate Key

• It's a minimal subset of a super key, uniquely identifying rows without redundant attributes.
• For example, If email, sID, and registration ID can individually identify a row, they are candidate keys.
• If any subset of a super key is still a super key, the original set is not a candidate key.
• sID, email, and registration ID are candidate keys because no smaller combination can uniquely identify rows.

Primary Key

• A primary key is a candidate key chosen to uniquely identify each row in a table.
• A database administrator selects one candidate key to serve as the primary key.
• If creating a user interface to search for student information, sID or registration ID would be ideal as a primary key.

Alternate Keys

• Alternate keys are candidate keys that are not selected as the primary key.
• They are the remaining candidate keys once the primary key has been chosen.

Foreign Key

• A foreign key is an attribute in one table that relates to the primary key of another table.
• Foreign keys maintain data integrity across related tables by enforcing valid relationships.
• For example, in a student table, a branch code could be a foreign key referencing the branch table.
• This ensures that only valid branch names are entered for students, preventing errors.
• Databases often prevent modifications to columns used as foreign keys to maintain referential integrity.

Composite Key

• A composite key consists of multiple attributes combined to uniquely identify records.
• Any super key with more than one attribute is a composite key.
• Examples are using email and sID together, or sID and registration ID together.

Compound Key

• This is a composite key that contains at least one attribute which is a foreign key.
• It's a combination of attributes, where one of the attributes is a reference to another table.

Surrogate Key

• A surrogate key is an artificial key created when a table lacks natural attributes for unique identification.
• These keys add no intrinsic meaning to the data but serve solely to identify rows uniquely.
• A surrogate key is created when there is no natural primary key

Learn about DBMS keys, including super keys, candidate keys, primary keys, and foreign keys. Understand their roles in uniquely identifying records, preventing duplicates, and establishing relationships between database tables. Explore composite, compound, and alternate keys.