Short Notes: Concept of keys | Database Management System (DBMS) - Computer Science Engineering (CSE) PDF Download

 Page 1


1 Concept of Keys in DBMS
1.1 Introduction to Keys
In a Database Management System (DBMS), keys are attributes or sets of attributes
used to uniquely identify records, establish relationships between tables, and ensure data
integrity. Keys play a critical role in relational databases for e?cient data retrieval and
organization.
1.2 Types of Keys
• Super Key: A set of one or more attributes that uniquely identi?es a record in a
table. Example: {StudentID, Name} in a Student table.
• Candidate Key: A minimal super key with no redundant attributes. A table can
have multiple candidate keys. Example: {StudentID} or {Email} if both uniquely
identify a student.
• Primary Key: A chosen candidate key that uniquely identi?es each record in a
table. It cannot be NULL or duplicate. Example: {StudentID}.
• ForeignKey: Anattribute(orsetof attributes)inonetablethatreferstothepri-
mary key of another table, establishing a relationship. Example: {DepartmentID}
in a Student table referencing the Department table.
• Composite Key: A primary key consisting of two or more attributes to uniquely
identify records. Example: {CourseID, StudentID} in an Enrollment table.
• Alternate Key: A candidate key not selected as the primary key. Example:
{Email} if {StudentID} is the primary key.
• Unique Key: Ensures all values in a column (or set of columns) are unique but
may allow NULL values (unlike primary key). Example: {Email} in a User table.
1.3 Role of Keys in DBMS
• Uniqueness: Ensures each record can be uniquely identi?ed (e.g., primary key,
unique key).
• Relationships: Facilitates linking tables via foreign keys for data consistency.
• Data Integrity: Enforces rules to prevent duplicate or invalid data.
• E?cient Retrieval: Keys (especially primary and unique) are indexed for faster
query performance.
1.4 Characteristics of Keys
• Primary Key: Unique, non-NULL, one per table.
• Foreign Key: Matches the primary key of another table, may allow NULLs, en-
forces referential integrity.
• Unique Key: Ensures unique values, may allow one NULL (in most DBMS).
• Candidate Key: Minimal, unique identi?er, no redundant attributes.
1.5 Advantages of Keys
• Ensures data integrity and consistency.
• Enables e?cient data retrieval through indexing.
• Supports relationships between tables for complex queries.
1
Page 2


1 Concept of Keys in DBMS
1.1 Introduction to Keys
In a Database Management System (DBMS), keys are attributes or sets of attributes
used to uniquely identify records, establish relationships between tables, and ensure data
integrity. Keys play a critical role in relational databases for e?cient data retrieval and
organization.
1.2 Types of Keys
• Super Key: A set of one or more attributes that uniquely identi?es a record in a
table. Example: {StudentID, Name} in a Student table.
• Candidate Key: A minimal super key with no redundant attributes. A table can
have multiple candidate keys. Example: {StudentID} or {Email} if both uniquely
identify a student.
• Primary Key: A chosen candidate key that uniquely identi?es each record in a
table. It cannot be NULL or duplicate. Example: {StudentID}.
• ForeignKey: Anattribute(orsetof attributes)inonetablethatreferstothepri-
mary key of another table, establishing a relationship. Example: {DepartmentID}
in a Student table referencing the Department table.
• Composite Key: A primary key consisting of two or more attributes to uniquely
identify records. Example: {CourseID, StudentID} in an Enrollment table.
• Alternate Key: A candidate key not selected as the primary key. Example:
{Email} if {StudentID} is the primary key.
• Unique Key: Ensures all values in a column (or set of columns) are unique but
may allow NULL values (unlike primary key). Example: {Email} in a User table.
1.3 Role of Keys in DBMS
• Uniqueness: Ensures each record can be uniquely identi?ed (e.g., primary key,
unique key).
• Relationships: Facilitates linking tables via foreign keys for data consistency.
• Data Integrity: Enforces rules to prevent duplicate or invalid data.
• E?cient Retrieval: Keys (especially primary and unique) are indexed for faster
query performance.
1.4 Characteristics of Keys
• Primary Key: Unique, non-NULL, one per table.
• Foreign Key: Matches the primary key of another table, may allow NULLs, en-
forces referential integrity.
• Unique Key: Ensures unique values, may allow one NULL (in most DBMS).
• Candidate Key: Minimal, unique identi?er, no redundant attributes.
1.5 Advantages of Keys
• Ensures data integrity and consistency.
• Enables e?cient data retrieval through indexing.
• Supports relationships between tables for complex queries.
1
• Prevents duplicate records, maintaining data accuracy.
1.6 Disadvantages of Keys
• Increased complexity in database design and maintenance.
• Overhead due to indexing, which may slow down write operations.
• Foreign key constraints can complicate data deletion or updates.
1.7 Short Questions and Answers
1. What is a primary key? A unique, non-NULL attribute (or set of at-
tributes) that identi?es each record in a table.
2. How does a foreign key di?er from a primary key? A foreign key links
to a primary key in another table to establish relationships, while a primary
key uniquely identi?es records in its own table.
3. What is a candidate key? A minimal set of attributes that can uniquely
identify a record, from which the primary key is chosen.
4. Why use a unique key? To ensure unique values in a column (e.g., email)
while allowing NULLs, unlike a primary key.
2