What are Database Keys in Database System

What are Database Keys in Database System

• 1.
  Database keys Introduction toDatabase Keys
• 2.
  Introduction to DatabaseKeys  Definition:  A key in a database is an attribute or a set of attributes that uniquely identifies a tuple (row) in a table.  Importance:  Ensures data integrity and uniqueness.  Helps in indexing and fast retrieval of data.  Facilitates relationships between tables.
• 3.
  Types of keys 1. Primary Key  2. Candidate Key  3. Super Key  4. Foreign Key  5. Composite Key  6. Alternate Key  7. Unique Key
• 4.
  Primary key  Definition:A column or a combination of columns that uniquely identifies a row in a table.  Characteristics:  Must contain unique values.  Cannot have NULL values.  Only one primary key per table.  Example: CREATE TABLE Students ( StudentID INT PRIMARY KEY, Name VARCHAR(100), Age INT);
• 5.
  Candidate key  Definition:A set of attributes that can uniquely identify a tuple, from which the primary key is chosen.  Example: If a table has StudentID and Email as unique attributes, both are candidate keys, but only one is chosen as the primary key.
• 6.
  Super key  Definition:A set of one or more attributes that uniquely identify a tuple in a table.  Example:  (StudentID)  (StudentID, Email)  (StudentID, Name, Email)  All candidate keys are super keys, but not all super keys are candidate keys.
• 7.
  Foreign key  Definition:A key used to establish and enforce a link between two tables.  Characteristics:  References the primary key in another table.  Can have duplicate and NULL values.  Example:  CREATE TABLE Enrollments ( EnrollmentID INT PRIMARY KEY, StudentID INT, CourseID INT, FOREIGN KEY (StudentID) REFERENCES Students(StudentID));
• 8.
  Composite key  Definition:A key that consists of two or more columns to uniquely identify a row.  Example: In an OrderDetails table, (OrderID, ProductID) together can act as a composite key. Alternate Key  Definition: Candidate keys that are not chosen as the primary key.  Example: If both StudentID and Email are candidate keys, but StudentID is chosen as the primary key, Email becomes an alternate key.
• 9.
  Unique key  Definition:Ensures uniqueness in a column but allows NULL values.  Example:  CREATE TABLE Employees ( EmpID INT PRIMARY KEY, Email VARCHAR(255) UNIQUE);
• 10.
  Key Type UniquenessNULL Allowed Can be Multiple? Primary Key Yes No No Candidate Key Yes No Yes Super Key Yes Yes Yes Foreign Key No Yes Yes Composite Key Yes No Yes Alternate Key Yes No Yes Unique Key Yes Yes Yes Comparision of keys
• 11.
  Summary  Keys ensuredata integrity, uniqueness, and relationships.  Primary vs. Foreign Keys: Primary keys uniquely identify rows, whereas foreign keys establish relationships.  Candidate and Super Keys: All candidate keys are super keys but not vice versa.  Composite Keys consist of multiple columns.