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.

• Class 12
• Computer Science
• 4 activities
• 30 min–50 min

Learning Objectives

1. Design a normalized database schema for their project, identifying entities, attributes, and relationships.
2. Construct SQL Data Definition Language (DDL) commands to create tables, define primary keys, and establish foreign key constraints.
3. Evaluate the chosen primary and foreign keys for their database design, justifying their selection based on data integrity and functional requirements.
4. Implement the designed database schema using SQL DDL commands in a chosen database management system.

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.

Prepare & details

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.

Setup: Flexible seating that allows clusters of 5-6 students; desks can be grouped in rows of three facing each other if fixed furniture limits rearrangement. Wall or board space for displaying group norm charts and the session agenda is helpful.

Materials: Printed problem brief cards (one per group), Role cards: Facilitator, Questioner, Recorder, Devil's Advocate, Communicator, Group norm chart (printable poster format), Individual reflection sheet and exit ticket, Timer visible to the class (board countdown or projected timer)

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

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.

Prepare & details

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.

Setup: Flexible seating that allows clusters of 5-6 students; desks can be grouped in rows of three facing each other if fixed furniture limits rearrangement. Wall or board space for displaying group norm charts and the session agenda is helpful.

Materials: Printed problem brief cards (one per group), Role cards: Facilitator, Questioner, Recorder, Devil's Advocate, Communicator, Group norm chart (printable poster format), Individual reflection sheet and exit ticket, Timer visible to the class (board countdown or projected timer)

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

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.

Prepare & details

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.

Setup: Flexible seating that allows clusters of 5-6 students; desks can be grouped in rows of three facing each other if fixed furniture limits rearrangement. Wall or board space for displaying group norm charts and the session agenda is helpful.

Materials: Printed problem brief cards (one per group), Role cards: Facilitator, Questioner, Recorder, Devil's Advocate, Communicator, Group norm chart (printable poster format), Individual reflection sheet and exit ticket, Timer visible to the class (board countdown or projected timer)

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

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.

Prepare & details

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.

Setup: Flexible seating that allows clusters of 5-6 students; desks can be grouped in rows of three facing each other if fixed furniture limits rearrangement. Wall or board space for displaying group norm charts and the session agenda is helpful.

Materials: Printed problem brief cards (one per group), Role cards: Facilitator, Questioner, Recorder, Devil's Advocate, Communicator, Group norm chart (printable poster format), Individual reflection sheet and exit ticket, Timer visible to the class (board countdown or projected timer)

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

Teaching This Topic

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.

What to Expect

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

Common Misconception

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

What to Teach Instead

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.

Common Misconception

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

What to Teach Instead

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.

Common Misconception

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

What to Teach Instead

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.

Assessment Ideas

Quick Check

After the Pairs: ER Diagram to DDL Challenge, give each pair a simple project scenario (e.g., a hospital management system) and ask them to sketch an ER diagram with 3-4 entities, their attributes, and primary keys within 10 minutes.

Peer Assessment

During the Small Groups: Normalisation Workshop, have students exchange their normalised schemas and provide written feedback on two aspects: Are the primary keys well-chosen? Are the foreign keys correctly implemented and logically linked?

Exit Ticket

After the Individual: Project Schema Prototype, provide a partially written CREATE TABLE statement for a project table, leaving blanks for the primary key and a foreign key definition. Ask students to complete the statement and justify their choices in 3-4 sentences.

Extensions & Scaffolding

• Challenge: Ask students to design a schema for a social media platform with 5+ tables, including composite keys for user-post relationships.
• Scaffolding: Provide a partially completed ER diagram for students who struggle, asking them to write the corresponding SQL DDL commands.
• Deeper: Invite students to research how NoSQL databases handle schema design differently and compare their project schema to a document-based alternative.

Key Vocabulary

Normalization	The process of organizing data in a database to reduce redundancy and improve data integrity. It involves structuring tables and columns according to specific rules.
Primary Key	A column or a set of columns that uniquely identifies each row in a database table. It ensures that no two rows are identical.
Foreign Key	A column or a set of columns in one table that refers to the primary key in another table. It establishes a link between tables and enforces referential integrity.
DDL (Data Definition Language)	A subset of SQL commands used to define, modify, and delete database structures. Commands include CREATE TABLE, ALTER TABLE, and DROP TABLE.