Flat-file and relational databases, data dictionaries and SQL: HSC Enterprise Computing Data Science
“Develop a flat-file database; apply computational thinking to design a relational database with appropriate user views, including developing a data dictionary, linking tables via key fields, sorting and searching data, including using structured query language (SQL), and using forms and reports”
A flat-file database stores everything in one table, which causes redundancy and update anomalies. A relational database, designed by decomposing the system into entities, links tables with primary and foreign keys, is documented in a data dictionary, is searched and sorted with SQL, and presents data through forms, reports and user views.
Jump to a section
What this dot point is asking
You need to build a simple flat-file database, then use computational thinking to design a relational database: a data dictionary, tables linked by key fields, SQL queries to sort and search, and forms, reports and user views for different users.
The answer
Flat-file databases
A flat-file database keeps all data in one table. It is quick to set up and suits small, simple lists (a class contact list). As data grows, it causes:
- Redundancy: repeated data (a customer's address on every order).
- Update anomalies: changing repeated data in one place but not another.
- Insertion and deletion anomalies: you cannot add a new product until it is ordered, or deleting an order removes the only copy of a customer's details.
Designing a relational database with computational thinking
- Decomposition: break the system into entities (things you store data about): Customers, Orders, Products.
- Pattern recognition: notice repeated groups of data that belong in their own table.
- Abstraction: keep only the attributes the system needs.
- Algorithms: define the steps and queries the system must run.
Each table gets a primary key (unique identifier). Tables are linked when a primary key is stored in another table as a foreign key. Most links are one-to-many (one customer has many orders). A many-to-many relationship (orders and products) needs a linking table (OrderLines).
The data dictionary
| Field name | Data type | Size/format | Description | Validation | Example |
|---|---|---|---|---|---|
| CustomerID | Integer (auto) | 6 digits | Unique customer number (PK) | Unique, not null | 100245 |
| Text | 60 characters | Customer email | Must contain @ | ana@example.com | |
| OrderDate | Date | DD/MM/YYYY | Date order placed | Not in the future | 03/05/2026 |
Sorting and searching with SQL
SELECT Surname, Suburb
FROM Customers
WHERE Suburb = 'Dubbo' AND JoinDate >= '2025-01-01'
ORDER BY Surname ASC;
Other key parts: JOIN ... ON to combine tables, GROUP BY with COUNT, SUM, AVG to summarise, LIKE with wildcards to search patterns (WHERE Surname LIKE 'Mc%'), and INSERT, UPDATE, DELETE to change data.
Forms, reports and user views
- Forms are designed screens for entering and viewing records, with drop-down lists and validation to reduce errors.
- Reports present query results formatted for printing or sharing (grouped totals, headings).
- User views show each type of user only what they need (a receptionist sees bookings, not payment details), supporting usability and privacy.
A vet clinic moves from one spreadsheet of visits to a relational database.
- Entities: Owners, Pets, Vets, Visits.
- Keys: OwnerID, PetID, VetID, VisitID as primary keys. Pets holds OwnerID (FK); Visits holds PetID and VetID (FKs).
- Query: all visits this week for dogs, sorted by time:
SELECT Pets.Name, Visits.VisitTime
FROM Visits JOIN Pets ON Visits.PetID = Pets.PetID
WHERE Pets.Species = 'Dog' AND Visits.VisitTime BETWEEN '2026-03-02' AND '2026-03-08'
ORDER BY Visits.VisitTime;
- User view: vets see medical history; reception sees owner contact details and appointments only.
- Using a name as a primary key
- Names are not unique; use an ID.
- Forgetting the foreign key goes on the "many" side
- Orders stores CustomerID, not the other way around.
- Writing SQL without ORDER BY when asked to sort
- Read the question for every requirement.
Practice questions
Original practice questions graded from foundation to exam level, each with a full worked solution. Try them before revealing the solution.
foundation3 marksA music school keeps one table with columns StudentName, StudentPhone, TeacherName, TeacherPhone, Instrument and LessonTime. Explain two problems with this flat-file design.Show worked solution →
Redundancy: each teacher's phone number is repeated in every lesson row for that teacher, wasting space.
Update anomalies: if a teacher changes phone number, every row must be edited; missing one leaves inconsistent data. Deleting a student's last lesson may also delete the only record of a teacher (deletion anomaly).
Marking guide: 1 mark for each problem named, 1 mark for explaining a consequence.
core5 marksRedesign the music school data as a relational database. Identify the tables, primary keys and foreign keys, and write a data dictionary entry for one field.Show worked solution →
Tables.
- Students (StudentID PK, FirstName, Surname, Phone)
- Teachers (TeacherID PK, Name, Phone, Instrument)
- Lessons (LessonID PK, StudentID FK, TeacherID FK, LessonDateTime)
Students and Teachers each link to Lessons through their primary keys, which appear in Lessons as foreign keys (one-to-many relationships).
Data dictionary entry.
| Field | Data type | Size or format | Description | Validation | Example |
|---|---|---|---|---|---|
| LessonDateTime | Date/time | DD/MM/YYYY HH:MM | Start time of a lesson | Must be a future date during opening hours | 15/03/2026 16:30 |
Marking guide: 1 mark for three suitable tables, 1 mark for primary keys, 1 mark for foreign keys, 2 marks for a complete data dictionary entry.
exam6 marksUsing the relational design from the previous question, write SQL to (a) list every lesson for teacher 'Ms Tran' with the student's name and time, sorted by time, and (b) count how many lessons each teacher has. Then explain how a user view could protect privacy for teachers.Show worked solution →
(a)
SELECT Students.FirstName, Students.Surname, Lessons.LessonDateTime
FROM Lessons
JOIN Students ON Lessons.StudentID = Students.StudentID
JOIN Teachers ON Lessons.TeacherID = Teachers.TeacherID
WHERE Teachers.Name = 'Ms Tran'
ORDER BY Lessons.LessonDateTime;
(b)
SELECT Teachers.Name, COUNT(Lessons.LessonID) AS LessonCount
FROM Teachers
JOIN Lessons ON Teachers.TeacherID = Lessons.TeacherID
GROUP BY Teachers.Name;
User view. Create a view (a saved query or form) for teachers that shows only their own lessons and each student's first name and lesson time, but not student phone numbers or other teachers' timetables. This follows the principle of least privilege and protects student privacy while still giving teachers what they need.
Marking guide: 2 marks for (a) with correct joins, condition and sort, 2 marks for (b) with COUNT and GROUP BY, 2 marks for the user view explanation.