Computer Science · Class 12

Active learning ideas

Keys: Primary, Candidate, Alternate, Foreign

Keys in relational databases are abstract concepts that students often confuse without hands-on practice. Active learning helps students visualize why keys exist by letting them design schemas, identify constraints, and troubleshoot errors in real time. When students manipulate data directly, they build lasting understanding of how keys maintain integrity across tables.

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

• 20–40 min
• Pairs → Whole Class
• 4 activities

Activity 01

• Decision Matrix
• 30 min · Pairs

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.

Differentiate between primary, candidate, alternate, and foreign keys.

Facilitation Tip

During Pair Schema Design, provide each pair with a partially completed schema so they must debate which columns qualify as primary or candidate keys before finalizing their design.

What to look for

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.'

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management

Activity 02

• Decision Matrix
• 40 min · Small Groups

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.

Explain the role of a primary key in uniquely identifying records.

Facilitation Tip

In Group Puzzle: Key Hunt Challenge, give each group a mix of valid and invalid key examples so they must justify their choices by testing each candidate against uniqueness and relationship rules.

What to look for

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.

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management

Activity 03

• Decision Matrix
• 25 min · Whole Class

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.

Analyze how foreign keys establish relationships between different tables.

Facilitation Tip

For Class Simulation: Data Integrity Demo, deliberately introduce duplicate or null values to show students how primary keys react, making the error messages meaningful.

What to look for

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?'

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management

Activity 04

• Decision Matrix
• 20 min · Individual

Individual Mapping: ER Diagram Practice

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

Differentiate between primary, candidate, alternate, and foreign keys.

Facilitation Tip

During Individual Mapping: ER Diagram Practice, require students to annotate their diagrams with key symbols (PK, FK, AK) to reinforce visual literacy alongside technical terms.

What to look for

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.'

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management

A few notes on teaching this unit

Teach keys by starting with real-world analogies students can relate to, like student IDs in schools or Aadhaar numbers in India. Avoid overwhelming them with theory; instead, let them discover key properties through trial and error in controlled activities. Use frequent quick-checks to correct misconceptions immediately, as these concepts build on each other and confusion early on can persist.

By the end of these activities, students will confidently distinguish between primary, candidate, alternate, and foreign keys in practical schemas. They will also explain how keys enforce uniqueness, relationships, and data integrity through clear reasoning. Group discussions and peer reviews will show their ability to apply these concepts to new scenarios.

---

Watch Out for These Misconceptions

• During Pair Schema Design, watch for students who allow nulls or duplicates in primary key columns. Redirect them by asking, 'How will the database stop two students from sharing the same roll number if nulls are allowed?'

  Remind them to test inserting sample data during their design review to see errors firsthand, reinforcing that primary keys must be unique and non-null.

• During Group Puzzle: Key Hunt Challenge, watch for students who think foreign keys must be primary keys in their own table. Redirect them by asking, 'If the foreign key is a primary key elsewhere, does it still need to enforce a relationship? How?'

  Have them trace a manual join between two tables using foreign keys to see that the constraint works without the local column being a primary key.

• During Individual Mapping: ER Diagram Practice, watch for students who dismiss alternate keys as unnecessary. Redirect them by asking, 'What if your primary key is a long composite of three columns? How would you speed up queries?'

  Encourage them to consider indexing strategies and performance trade-offs, showing how alternate keys can optimize searches.

---

Methods used in this brief

• Decision Matrix