Database Concepts for Class 12: Understanding Keys in Relational Databases

What Are Database Concepts and Their Importance in Class 12

Database Concepts form the backbone of managing and organizing data in computer systems. For Class 12 NCERT students, understanding these concepts is crucial to learning how databases store information efficiently and avoid data redundancy.

A database is a collection of related data organized in tables called relations. Each table consists of rows (tuples) and columns (attributes). To uniquely identify each row and maintain relationships between tables, keys are used. These keys ensure data integrity and help in querying data effectively.

In this chapter, you will learn about different types of keys used in relational databases, which is essential for mastering database design and operations.

Candidate Keys: The Potential Unique Identifiers

Candidate keys are attributes or sets of attributes in a table that can uniquely identify each tuple (row). A relation can have multiple candidate keys.

For example, consider a GUARDIAN table with attributes GUID (Guardian ID) and GPhone (Guardian Phone Number). Both GUID and GPhone have unique values for each guardian, so both are candidate keys.

Characteristics of Candidate Keys:

• Uniquely identify each tuple
• No redundant attributes (minimal set)
• Multiple candidate keys can exist in a relation

Example:

Both GUID and GPhone can serve as candidate keys since their values uniquely identify each guardian.

Primary Key: The Chosen Unique Identifier

Out of the candidate keys, one is selected as the primary key. The primary key uniquely identifies each tuple in the table and is used as the main reference.

The remaining candidate keys become alternate keys.

Example:

In the GUARDIAN table, if GUID is chosen as the primary key, then GPhone becomes an alternate key.

Why Choose a Primary Key?

• It enforces entity integrity
• Prevents duplicate records
• Used to establish relationships with other tables

Rules for Primary Key:

• Must contain unique values
• Cannot contain NULL values

Worked Example:

If a STUDENT table has candidate keys RollNumber and EmailID, and RollNumber is chosen as the primary key, then:

• RollNumber uniquely identifies each student
• EmailID is an alternate key

This choice depends on which attribute is more stable and reliable.

Composite Primary Key: Combining Attributes for Uniqueness

Sometimes, no single attribute can uniquely identify a tuple. In such cases, a combination of two or more attributes forms a composite primary key.

Example from NCERT:

In the ATTENDANCE table, neither RollNumber nor AttendanceDate alone can uniquely identify a record because:

• A student can have multiple attendance records on different dates
• Multiple students share the same attendance date

However, the combination {RollNumber, AttendanceDate} uniquely identifies each attendance record.

Benefits of Composite Keys:

• Ensures uniqueness when single attributes fail
• Represents natural relationships in data

Note: Composite keys are essential in many-to-many relationships.

Foreign Keys: Linking Tables in a Database

A foreign key is an attribute in one table that refers to the primary key of another table. It establishes a relationship between two tables.

Example:

In the STUDENTATTENDANCE database:

• RollNumber in ATTENDANCE is a foreign key referring to RollNumber in STUDENT
• GUID in STUDENT is a foreign key referring to GUID in GUARDIAN

This relationship helps maintain referential integrity.

Key Points:

• Foreign keys can have NULL values if not part of the primary key
• They enforce that values in the foreign key column must exist in the referenced primary key column

Schema Diagram Example:

`` STUDENT (RollNumber PK, Name, GUID FK) GUARDIAN (GUID PK, GName, GPhone) ATTENDANCE (RollNumber FK, AttendanceDate PK, Status) ``

Table: Key Types Comparison

Summary of Keys in Database Concepts for Class 12 NCERT

Understanding keys is vital for designing efficient and reliable databases. Here's a quick recap:

• Candidate Keys: Multiple unique identifiers
• Primary Key: Selected candidate key for unique identification
• Alternate Key: Candidate keys not chosen as primary
• Composite Key: Combination of attributes for uniqueness
• Foreign Key: Links tables and maintains referential integrity

Mastering these keys helps you design databases that avoid duplication, maintain data accuracy, and support complex queries.

This knowledge prepares you for advanced topics like SQL queries and database operations in your Class 12 Computer Science syllabus.