Types of Keys in RDBMS

Introduction

• Discussing different types of keys in relational database management systems (RDBMS).
• Common keys: Primary Keys, Unique Keys, Foreign Keys.
• Focus on keys specific to RDBMS: Candidate Key, Primary Key, Alternate Key, Super Key, Composite Key.

Designing an Employee Table

• Goal: design an employee table with integrity rules (e.g. primary key).
• Example columns: Employee ID, Employee Name, Date of Birth, Date of Joining, Social Security Number (SSN), Department ID, Manager ID.

Identifying Candidate Keys

• Definition: Attributes or columns that qualify for uniqueness of each row.
• Terminology: Attribute = Column, Relation = Table, Tuple = Row.
• Process:
  • Employee ID: Unique, can be a candidate key.
  • Employee Name: Not unique, cannot be a candidate key.
  • Date of Birth: Not unique, cannot be a candidate key.
  • Date of Joining: Not unique, cannot be a candidate key.
  • SSN: Unique, can be a candidate key.
  • Department ID: Not unique, cannot be a candidate key.
  • Manager ID: Not unique, cannot be a candidate key.
• Result: Candidate keys for the employee table are Employee ID and SSN.

Primary Key

• Definition: Candidate key attribute/column most suited to maintain uniqueness at row level.
• Selection:
  • Employee ID: Chosen as primary key.
  • SSN: Not ideal (sensitive information).

Alternate Key

• Definition: Candidate keys not chosen as the primary key.
• Example: SSN becomes an alternate key.

Super Key

• Definition: Superset of a candidate key; adding additional attributes to a candidate key.
• Examples:
  • Employee ID + Employee Name = Super Key.
  • Employee ID + Employee Name + Date of Birth = Super Key.

Composite Key

• Definition: Used when no single column qualifies as a unique candidate key; involves multiple columns to ensure uniqueness.
• Example:
  • If no Employee ID: Combine Employee Name + Date of Birth + other columns as necessary.
  • Considerations: Ideally, fewer columns are better to maintain uniqueness.

Conclusion

• Importance of selecting appropriate keys for maintaining table integrity and uniqueness.
• Process involves identifying candidate keys, choosing a primary key, and considering alternate, super, and composite keys.

Closing Remarks

• Encourage feedback and subscriptions for future videos.

Note: Always make informed decisions based on database design principles.