Computer Science · Class 12

Active learning ideas

Database Design and Schema Implementation for Project

Active learning works best for database design because students learn by doing, not by memorising. When they convert real-world project data into schemas, mistakes become visible immediately, making concepts like normalisation and constraints tangible rather than abstract. This approach builds confidence as students see their designs tested against actual queries and edge cases.

• CBSE Learning Outcomes
• CBSE: Project Work - System Design - Class 12

• 30–50 min
• Pairs → Whole Class
• 4 activities

Activity 01

• Collaborative Problem-Solving
• 45 min · Pairs

Pairs: ER Diagram to DDL Challenge

Pairs analyse a project scenario, sketch an ER diagram on paper, identify entities and relationships. They then convert it to SQL DDL statements using laptops with SQLite. Pairs test by inserting sample data and checking for errors.

Design a normalized database schema for your project's data requirements.

Facilitation Tip

During the Pairs: ER Diagram to DDL Challenge, circulate with sample data so pairs can test their SQL inserts to verify primary and foreign key choices.

What to look for

Present students with a simple project scenario (e.g., a library management system). Ask them to identify 3-4 key entities, list their essential attributes, and suggest a primary key for each. Review their responses for understanding of entity identification and key selection.

• Apply
• Analyze
• Evaluate
• Create
• Relationship Skills
• Decision-Making
• Self-Management

Activity 02

• Collaborative Problem-Solving
• 35 min · Small Groups

Small Groups: Normalisation Workshop

Provide groups with unnormalised data tables from a sample project. Groups apply 1NF, 2NF, 3NF steps collaboratively, rewriting tables and noting changes. Share final schemas with the class for comparison.

Construct SQL DDL commands to create the tables and relationships for your database.

Facilitation Tip

In the Small Groups: Normalisation Workshop, provide a messy dataset first and ask groups to normalise it step-by-step, timing their queries to experience the trade-offs.

What to look for

Students exchange their designed database schemas (ER diagrams or table definitions). Each student reviews their partner's work, checking: Are primary keys clearly identified? Are foreign keys correctly linking related tables? Do the relationships make logical sense for the project? Partners provide written feedback on at least one aspect of the design.

• Apply
• Analyze
• Evaluate
• Create
• Relationship Skills
• Decision-Making
• Self-Management

Activity 03

• Collaborative Problem-Solving
• 30 min · Whole Class

Whole Class: Schema Peer Review Walk

Students post printed schemas on walls. Class walks around, reviews designs using a checklist for keys, normalisation, and relationships. Provide sticky notes for feedback; revise based on inputs.

Justify the choice of primary and foreign keys in your database design.

Facilitation Tip

For the Whole Class: Schema Peer Review Walk, use a gallery walk format where students leave sticky notes with specific feedback on at least one schema they review.

What to look for

Provide students with a partially written CREATE TABLE statement for a project table, leaving a blank for the primary key and a foreign key definition. Ask them to fill in the blanks with appropriate SQL syntax and briefly justify their choice for the foreign key's referenced table.

• Apply
• Analyze
• Evaluate
• Create
• Relationship Skills
• Decision-Making
• Self-Management

Activity 04

• Collaborative Problem-Solving
• 50 min · Individual

Individual: Project Schema Prototype

Each student implements their project schema in an online SQL editor. Run basic INSERT and SELECT queries to verify integrity. Submit screenshots with a justification note.

Design a normalized database schema for your project's data requirements.

Facilitation Tip

For the Individual: Project Schema Prototype, require students to include a brief design rationale alongside their SQL scripts to connect theory with practice.

What to look for

Present students with a simple project scenario (e.g., a library management system). Ask them to identify 3-4 key entities, list their essential attributes, and suggest a primary key for each. Review their responses for understanding of entity identification and key selection.

• Apply
• Analyze
• Evaluate
• Create
• Relationship Skills
• Decision-Making
• Self-Management

A few notes on teaching this unit

Start with small, familiar projects before students tackle complex ones. Teach normalisation using concrete examples like student records or purchase orders, then gradually introduce edge cases where composite keys or partial denormalisation make sense. Avoid overwhelming students with theory upfront; let them discover rules through trial and error. Research shows that students retain schema design better when they build, break, and fix their own schemas.

By the end of these activities, students should confidently translate project requirements into a normalised relational schema, write correct SQL DDL commands, and explain their design choices. They should also identify when to denormalise for performance and handle constraints like foreign keys with precision.

---

Watch Out for These Misconceptions

• During Pairs: ER Diagram to DDL Challenge, students often assume primary keys must always be single columns.

  While reviewing pairs' SQL scripts, point to sample data where a single column fails uniqueness. Ask them to trial-insert duplicate data to see the error, then guide them to consider composite keys or natural keys like employee ID.

• During Small Groups: Normalisation Workshop, students believe normalisation eliminates all data redundancy.

  Ask groups to time queries on their normalised vs partially denormalised schemas using real project data. Highlight where redundancy improves query speed, like storing user names in a posts table for faster retrieval.

• During Whole Class: Schema Peer Review Walk, students think foreign keys automatically prevent all invalid data without additional constraints.

  During the walk, have students test sample ON DELETE and ON UPDATE clauses in their peers' schemas. Ask them to simulate what happens when a record is deleted to identify orphaned records and missing cascade rules.

---

Methods used in this brief

• Collaborative Problem-Solving