Computer Science · Class 12 · Computer Networks and Connectivity · Term 1

Keys: Primary, Candidate, Alternate, Foreign

Students will learn about different types of keys (primary, candidate, alternate, foreign) and their importance in maintaining data integrity and relationships.

• CBSE Learning Outcomes
• CBSE: Database Management - Relational Data Model - Class 12

About This Topic

In relational databases, keys maintain data integrity and establish links between tables. A primary key uniquely identifies each record, such as a roll number in a students table, ensuring no duplicates or nulls. Candidate keys are attributes that could serve as primary keys, like roll number or Aadhaar number if both unique. Alternate keys are candidate keys not selected as primary, while foreign keys reference primary keys in other tables, for instance, department ID in students table linking to departments table.

This topic forms the core of CBSE Class 12 Database Management System under the Relational Data Model. Students grasp how keys support normalisation, prevent anomalies in insert, update, delete operations, and enable efficient SQL queries. Practical applications include school databases or inventory systems common in Indian contexts.

Active learning benefits this abstract topic greatly. When students design schemas or simulate data entry in groups, they witness integrity violations firsthand, like orphan records from poor foreign keys. This hands-on practice reinforces conceptual understanding and prepares them for viva and projects.

Key Questions

1. Differentiate between primary, candidate, alternate, and foreign keys.
2. Explain the role of a primary key in uniquely identifying records.
3. Analyze how foreign keys establish relationships between different tables.

Learning Objectives

• Compare and contrast the properties and roles of primary, candidate, alternate, and foreign keys in a relational database schema.
• Explain the mechanism by which a primary key ensures entity integrity and uniqueness of records.
• Analyze how foreign keys enforce referential integrity and establish relationships between related tables.
• Design a simple database schema for a school or library system, correctly identifying and implementing primary and foreign keys.

Before You Start

Introduction to Databases

Why: Students need a basic understanding of what a database is and the concept of tables to grasp the role of keys within them.

Data Types and Attributes

Why: Understanding different data types helps in identifying suitable columns for keys and their constraints like uniqueness and non-nullability.

Key Vocabulary

Primary Key	A column or set of columns that uniquely identifies each row in a table. It cannot contain NULL values and must be unique.
Candidate Key	A column or set of columns that could potentially serve as a primary key. It must be unique and non-NULL.
Alternate Key	A candidate key that was not chosen as the primary key. It still maintains uniqueness within the table.
Foreign Key	A column or set of columns in one table that refers to the primary key in another table. It establishes a link and enforces referential integrity.

Watch Out for These Misconceptions

Common Misconception

Primary keys can allow null or duplicate values.

What to Teach Instead

Primary keys must be unique and non-null to identify records precisely. Hands-on data insertion activities reveal errors immediately, helping students adjust their schemas during group reviews.

Common Misconception

Foreign keys must always be primary keys in their own table.

What to Teach Instead

Foreign keys reference primary or unique keys elsewhere; they enforce relationships without needing to be primary locally. Role-playing table joins in pairs clarifies this, as students trace links manually.

Common Misconception

Alternate keys have no practical use.

What to Teach Instead

Alternate keys provide unique constraints for searches or indexes. Schema design tasks show their value when primary keys are long composites, building student confidence in flexible designs.

Active Learning Ideas

Pair Schema Design: School Database

Students in pairs sketch tables for students, classes, and marks. They select primary keys, list candidate and alternate keys, then add foreign keys for relationships. Pairs present one design to class for feedback.

30 min·Pairs

Group Puzzle: Key Hunt Challenge

Provide printed tables with sample data. Small groups identify primary, candidate, alternate, and foreign keys, then correct violations like duplicates. Groups share fixes on board.

40 min·Small Groups

Class Simulation: Data Integrity Demo

Use a projector to show linked tables. Whole class suggests inserts or deletes, observing foreign key constraint errors. Discuss resolutions collaboratively.

25 min·Whole Class

Individual Mapping: ER Diagram Practice

Students draw ER diagrams for a library system, labelling all key types. Submit for peer review next class.

20 min·Individual

Real-World Connections

• Database administrators at e-commerce companies like Flipkart use primary and foreign keys to manage product catalogs, customer orders, and inventory, ensuring accurate tracking and preventing duplicate entries.
• Librarians in public libraries use relational database systems with keys to manage book records, borrower information, and loan history, ensuring each book and patron is uniquely identified and loans are correctly linked.
• Software developers designing student information systems for educational institutions, such as those used by Kendriya Vidyalayas, rely on keys to link student details, course enrollments, and exam results accurately.

Assessment Ideas

Quick Check

Present students with a simple table schema (e.g., Students table with RollNo, Name, Address, Phone). Ask them: 'Which column(s) would make a good primary key and why? List one other column that could be a candidate key.'

Exit Ticket

Provide students with two table definitions: 'Courses' (CourseID, CourseName) and 'Enrollments' (EnrollmentID, StudentID, CourseID). Ask them to identify the primary key for each table and specify which column in 'Enrollments' would be a foreign key, explaining its purpose.

Discussion Prompt

Pose a scenario: 'Imagine a database for a cinema hall. We have tables for Movies, Shows, and Bookings. If a booking is made for a specific show of a movie, how would you use primary and foreign keys to ensure that a booking can only be made for an existing show and that show belongs to an existing movie?'

Frequently Asked Questions

What is the difference between primary, candidate, and alternate keys?

A primary key uniquely identifies records and is chosen from candidate keys, which are any minimal unique attributes. Alternate keys are remaining candidate keys not selected as primary. In practice, use composite candidates like roll number plus admission year if single attributes fail uniqueness, ensuring robust table design for CBSE projects.

How do foreign keys establish relationships between tables?

Foreign keys in one table reference primary keys in another, creating parent-child links for referential integrity. For example, order ID in orders table links to customers table. This prevents orphans during deletes and supports joins in queries, vital for normalised databases in school management systems.

How can active learning help students understand database keys?

Active methods like pair schema design or group data simulations make abstract keys concrete. Students experience violations, such as duplicate primaries or broken foreign links, fostering deeper insight. Collaborative fixes and presentations align with CBSE's emphasis on application, improving exam performance and project skills over rote memorisation.

Why is the primary key important in a relational database?

Primary keys ensure each record is uniquely identifiable, supporting fast searches and preventing redundancy. They form the backbone for relationships via foreign keys. In Indian school contexts, like student databases, a roll number as primary key avoids confusion during admissions or result processing, aligning with CBSE standards for data integrity.