A 3.2.6 Database scenarios
A3.2.6 Constructing databases
Learn to construct 3NF databases for real-world scenarios (e.g., library, hospital, e-commerce, school, employee, inventory, crime reporting). Design efficient, normalized databases for practical applications.
Learning Objectives
By the end of this lesson, students will be able to:
- Define First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF).
- Identify violations of 1NF, 2NF, and 3NF in a given database table.
- Decompose an unnormalized database table into a set of tables in 3NF.
- Explain the purpose and benefits of database normalization.
- Apply the concepts of primary keys, foreign keys, and functional dependencies in the normalization process.
IB ATL Elements:
This lesson will explicitly engage the following Approaches to Learning (ATL) skills:
- Thinking Skills:
- Critical Thinking: Analysing the structure of database tables to identify redundancies and dependencies.
- Problem Solving: Devising strategies to decompose tables and eliminate normalization issues.
- Information Literacy: Locating and interpreting information related to database normalization concepts (though not a primary focus of this specific lesson, it connects to broader learning).
- Communication Skills:
- Presenting: Explaining the rationale behind each step of the normalization process (during class discussions).
- Collaborating: Working with peers on the student activity to discuss and agree on normalization steps (if done as pair work).
- Self-Management Skills:
- Organisation: Structuring their understanding of the normalization process into a logical sequence.
- Time Management: Completing the student activity within the allocated time.
Lesson Resources
Presentation
eBook
The eBook covers the range of scenarios in the guide and explains the optimal 3NF database structure for each. It also includes a quiz to test students' understanding of the core concepts.
Worksheets
Lesson planning
| Time | Activity | Content covered | Slide(s) | Notes for teacher |
|---|---|---|---|---|
| 0-5 | Introduction & Learning Objectives | Welcome students, briefly introduce the topic of database normalization and its importance. Clearly state the learning objectives and highlight the relevant ATL skills (Thinking, Communication, Self-Management) that will be developed during the lesson | 1 | Ensure students understand what they will be able to do by the end of the lesson. Emphasise the connection to real-world database design. |
| 5-10 | What is Database Normalization? | Explain the concept of database normalization and its main goals: reducing redundancy and improving data integrity. Briefly mention the three normal forms (1NF, 2NF, 3NF) that will be covered. | 2 | Use simple analogies to explain redundancy and data inconsistencies. For example, having the same student address listed multiple times. |
| 10-20 | First Normal Form (1NF) | Define 1NF, emphasising atomic values and no repeating groups. Present the unnormalized Library Loans Table example and discuss the violations of 1NF. Show the transformation to the 1NF Library Loans Table by creating separate rows for each book loan. Briefly discuss the need for a primary key (composite key example). | 3,4 | Clearly illustrate the "repeating group" issue and how creating separate rows resolves it. Introduce the concept of a composite primary key if needed. Encourage students to identify other potential issues in the 1NF table (e.g., redundancy of borrower information). |
| 20-30 | Second Normal Form (2NF) | Define 2NF (being in 1NF and no partial dependencies). Using the 1NF Library Loans Table, identify the primary key (LoanID, BookTitle) and the non-key attributes. Explain the concept of partial functional dependency with examples from the table (borrower details depending only on LoanID, author depending only on BookTitle). Show the decomposition into the Loans, Books, and LoanItems tables to achieve 2NF. Explain the role of primary and foreign keys in the new tables. | 5,6 | Clearly explain the concept of functional dependency ("If we know X, we know Y"). Use arrows or diagrams to visually represent the dependencies. Emphasize why removing partial dependencies is important for data integrity (e.g., if a borrower changes address, we only need to update it in one place). Introduce the term "junction table" for LoanItems. |
| 30-40 | Third Normal Form (3NF) | Define 3NF (being in 2NF and no transitive dependencies). Examine the 2NF Loans table and explain the concept of transitive dependency (LoanID determines BorrowerName, and BorrowerName determines BorrowerAddress). Show the further decomposition of the Loans table into Loans and Borrowers tables to achieve 3NF. Explain the use of the foreign key (BorrowerID) to link the tables. Briefly review the final 3NF schema for the library management system. | 7,8 | Ensure students understand the "indirect" dependency in a transitive dependency. Highlight the benefits of removing transitive dependencies (e.g., separating borrower information allows for managing borrower details independently of loans). |
| 40-55 | Introduce Student Activity | Introduce the School Management System unnormalized table. Clearly explain the Worksheet: normalize this table to 3NF, showing the tables at each normal form, identifying primary and foreign keys, and explaining the reasons for each step. Suggest students work individually or in pairs. | 9 (student activity) | Circulate and answer any initial questions about the worksheet. Encourage students to apply the concepts learned during the lesson. Remind them to consider primary keys and dependencies carefully. |
| 55-60 | Wrap Up & Briefly Review Student Worksheet (Optional: Show Answer Key) | Briefly summarise the three normal forms and the normalization process. If time permits, quickly review the expected steps and the final 3NF schema for the School Management System (show the answer key). Answer any remaining questions. Briefly reiterate the benefits of normalization. | 9 (summary answer key) | Focus on the key concepts and the logical reasoning behind each normalization step. Highlight common mistakes students might make. Encourage students to review the answer key in their own time if there isn't enough class time. |
All materials on this website are for the exclusive use of teachers and students at subscribing schools for the period of their subscription. Any unauthorised copying or posting of materials on other websites is an infringement of our copyright and could result in your account being blocked and legal action being taken against you.