Keys and Dependencies in DBMS: Understanding Types and Functionalities

Keys

A key is an attribute or a set of attributes used to uniquely identify each row in a table. Understanding different types of keys is crucial for maintaining data integrity and efficiently organizing and accessing data.

Why Are Keys Important?

Consider an Employee table:

Suppose you need to update Bob's salary. Without a unique key, you can't accurately identify which Bob you're referring to. This is where keys come into play.

Types of Keys

Candidate Key

A candidate key is a column, or set of columns, that can uniquely identify any row in the table.

Example: EmpID is a candidate key because it uniquely identifies each employee and remains unique over time.

Primary Key

A primary key is a candidate key chosen to uniquely identify each row in a table. It must be unique and not null.

Example: In the Employee table, EmpID is selected as the primary key. Primary keys help maintain the uniqueness and integrity of data in a table.

Super Key

A super key is a set of one or more columns (attributes) that can uniquely identify a row in a table. Super keys include primary keys, candidate keys, and any other combination that can uniquely identify rows.

Example: [EmpID], [EmpID, Name], [EmpID, Name, Email] are all super keys. However, we prefer minimal keys (like EmpID) to avoid redundancy.

Alternate Key

All candidate keys that are not chosen as primary keys are known as alternate keys.

Unique Key

A unique key is a column (or set of columns) with a constraint that ensures all values are unique. Unlike primary keys, unique keys can have null values.

Foreign Key

A foreign key is a field (or set of fields) in one table that uniquely identifies a row of another table. This key establishes a relationship between the two tables.

Example: In a Student table, CourseID could be a foreign key that references the CourseID in a Course table.

Student Table:

Course Table:

Composite Key

A composite key is a key that consists of two or more columns to uniquely identify a row.

Example: [Firstname, Lastname, Email] collectively can serve as a composite key.

Compound Key

A compound key is a composite key where each column is a foreign key. It is commonly used in junction tables to represent many-to-many relationships.

Example: A Junction Table might use [StudentID, CourseID] as a compound key to link Student and Course.

Junction Table:

Dependencies

Functional Dependency

• Definition: A functional dependency exists when one attribute uniquely determines another attribute.

• Example: If StudentID -> StudentName, then StudentName is functionally dependent on StudentID.

Partial Dependency

• Definition: A partial dependency occurs when a non-prime attribute depends only on a part of a composite primary key.

• Example: In a table where the primary key is [StudentID, CourseID], if StudentName depends only on StudentID, it's a partial dependency.

Transitive Dependency

• Definition: A transitive dependency is when a non-prime attribute depends on another non-prime attribute, which in turn depends on the primary key.

• Example: If InstructorName depends on CourseID and CourseID depends on StudentID, then InstructorName is transitively dependent on StudentID.