Computing · Secondary 4 · Data Management and Database Systems · Semester 1

Database Concepts and Types

Exploring the purpose of databases, their advantages over flat files, and different types of database models.

• MOE Syllabus Outcomes
• MOE: Database Systems - S4

About This Topic

SQL (Structured Query Language) is the standard language for interacting with relational databases. In this unit, students learn to move beyond just storing data to extracting meaningful insights. They master the SELECT statement, use JOINs to combine information from multiple tables, and apply aggregate functions like SUM and AVG to analyze datasets. This skill is highly relevant to Singapore's push for data literacy across all sectors.

Learning SQL requires a shift in thinking from procedural programming to declarative logic, telling the computer 'what' you want rather than 'how' to get it. Students often find the syntax challenging at first, but they grasp the logic quickly when they can visualize how tables intersect. This topic is best taught through collaborative problem-solving where students 'query' physical data before writing the code.

Key Questions

1. Explain why databases are superior to simple file systems for managing large datasets.
2. Compare different types of database models (e.g., hierarchical, network, relational).
3. Predict the challenges of managing data without a structured database system.

Learning Objectives

• Compare the advantages of using a database system over flat files for managing large datasets.
• Analyze the structure and relationships within hierarchical, network, and relational database models.
• Evaluate the potential challenges and inefficiencies of managing data without a structured database.
• Classify different types of data based on their suitability for various database models.

Before You Start

Introduction to Data Representation

Why: Students need to understand how data is organized and represented in various formats to appreciate the benefits of structured database systems.

File Management and Organization

Why: Familiarity with basic file operations and the limitations of simple file systems provides a foundation for understanding why databases are superior.

Key Vocabulary

Database	An organized collection of structured information, or data, typically stored electronically in a computer system.
Flat File	A database stored in a plain text file, where each line represents a record and fields are separated by delimiters, often leading to redundancy and difficulty in managing relationships.
Hierarchical Model	A database model where data is organized in a tree-like structure, with a parent-child relationship between records, allowing one parent to have many children but each child only one parent.
Network Model	An extension of the hierarchical model, allowing records to have multiple parent and child records, forming a more complex graph-like structure.
Relational Model	A database model that organizes data into one or more tables (relations) of columns and rows, with a unique key identifying each row, allowing for flexible data relationships.

Watch Out for These Misconceptions

Common Misconception

The order of SQL clauses doesn't matter.

What to Teach Instead

Students often try to put WHERE after ORDER BY. Using a 'clause card' activity where they have to physically arrange the keywords (SELECT, FROM, WHERE, GROUP BY, ORDER BY) helps them memorize the mandatory logical sequence the database engine follows.

Common Misconception

JOIN and UNION are the same thing.

What to Teach Instead

Students often confuse horizontal and vertical combinations. Peer explanation exercises help clarify that JOIN combines columns from different tables based on a key, while UNION combines rows from similar tables. Visualizing this with Venn diagrams or physical blocks is very effective.

Active Learning Ideas

Simulation Game: The Human Query Engine

Give students 'tables' (printed lists of data). One student acts as the 'User' and gives a SQL command (e.g., SELECT Name FROM Students WHERE Grade > 70). The 'Engine' student must physically find and point to the correct rows and columns to return the result.

25 min·Pairs

Inquiry Circle: Join the Dots

Provide two separate tables: 'Orders' and 'Customers'. Groups are given a list of questions (e.g., 'Which customer from Jurong spent the most?') and must determine which columns to JOIN and which aggregate functions to use to find the answer.

35 min·Small Groups

Think-Pair-Share: Query Optimization

Present a complex problem that can be solved with two different SQL queries. Students work individually to write a query, then pair up to compare whose version is more concise or easier to read, explaining their logic to the class.

20 min·Pairs

Real-World Connections

• Libraries use database systems to manage their vast collections of books, patron information, and borrowing records, allowing for quick searches and efficient tracking of inventory.
• E-commerce platforms like Shopee and Lazada rely on relational databases to store product details, customer orders, and inventory levels, enabling seamless online shopping experiences.
• Airlines utilize complex database systems to manage flight schedules, passenger bookings, seat assignments, and crew information, ensuring efficient operations and passenger service.

Assessment Ideas

Discussion Prompt

Pose the scenario: 'Imagine you are managing a school's student records using only Word documents, with one document per student. Discuss with a partner the difficulties you would face when trying to generate a list of all students in Year 4 who study Computing and play basketball.' Guide students to identify issues like data duplication, difficulty in searching, and lack of consistency.

Quick Check

Present students with simple diagrams representing hierarchical, network, and relational structures. Ask them to label each diagram with the correct model type and write one sentence explaining a key characteristic of each structure. For example, 'This is a hierarchical model because it shows parent-child relationships in a tree structure.'

Exit Ticket

On an index card, have students write down one significant advantage of using a database over a flat file system for managing a large music collection. Then, ask them to briefly describe one potential challenge they might encounter when trying to organize data without any predefined structure.

Frequently Asked Questions

What is the difference between WHERE and HAVING?

WHERE is used to filter individual rows before any grouping happens. HAVING is used to filter the results after you have used GROUP BY and aggregate functions. For example, use WHERE to find students older than 15, but use HAVING to find classes where the average grade is higher than 70.

Why do we need to use JOIN in SQL?

Because relational databases split data into different tables to avoid redundancy, we often need to bring that data back together to answer a question. JOIN allows us to link a 'Sales' table with a 'Products' table so we can see the actual names of the items sold instead of just their ID numbers.

How can active learning help students master SQL syntax?

Active learning, like the 'Human Query Engine', forces students to step through the logic of a query manually. When they have to physically 'filter' rows or 'join' two lists, the syntax of WHERE and JOIN becomes intuitive. Collaborative investigations also allow students to talk through the logic of complex queries, which is often more helpful than staring at a screen alone when debugging syntax errors.

What are aggregate functions in SQL?

Aggregate functions perform a calculation on a set of values and return a single result. Common ones include SUM (total), AVG (average), COUNT (number of items), MIN (lowest), and MAX (highest). They are essential for turning thousands of rows of raw data into a simple summary or report.