Skip to main content
ExamExplained

Relational databases, ER diagrams, integrity and normalisation to 3NF: WACE Computer Science Unit 4

Syllabus dot point

“Explain relational database concepts (entities, attributes, primary, foreign and composite keys, relationships and anomalies), model data with ER diagrams, relational notation and data dictionaries, and apply data integrity, data quality and normalisation to third normal form”

WACEComputer ScienceUnit 4: Data management9 min read

Quick answer

Relational databases store entities in tables linked by primary and foreign keys. Model them with ER diagrams, relational notation and data dictionaries, enforce entity, referential and domain integrity, and normalise to 3NF (atomic values, no partial dependencies, no transitive dependencies) to remove redundancy and anomalies.

Jump to a section
  1. What this dot point is asking
  2. The answer
  3. Practice questions

What this dot point is asking

The data management content in Unit 4 asks you to design relational databases properly. You need the vocabulary (keys, relationships, anomalies), the modelling tools (ER diagrams, relational notation, data dictionaries), and the process of normalisation to third normal form, with integrity and data quality in mind.

The answer

Relational concepts

  • Entity: a thing stored as a table (STUDENT). Attribute: a field (Surname). Record/tuple: a row.
  • Primary key: uniquely identifies each record. Composite key: a primary key of two or more attributes. Foreign key: an attribute that references a primary key in another table.
  • Relationships: one-to-one, one-to-many, many-to-many (resolved with an associative table).
  • Anomalies in poorly designed tables: insertion, update and deletion anomalies caused by redundancy.

Modelling

  • ER diagrams: entities as boxes, relationships as lines, with cardinality shown (for example crow's foot notation for "many").
  • Relational notation: TABLE(PrimaryKey, Attribute, ForeignKey), with the primary key underlined (shown bold here) and foreign keys marked.
  • Data dictionary: each attribute's name, data type, size, description, constraints and example.

Integrity and quality

  • Entity integrity: primary keys are unique and not null.
  • Referential integrity: foreign keys match existing primary keys; related records cannot be orphaned.
  • Domain integrity: values fit their type and allowed range (validation, CHECK constraints).
  • Data quality: accuracy, completeness, consistency, timeliness and relevance.

Normalisation

1NF, 2NF, 3NF
  • 1NF: atomic values, no repeating groups, a primary key.
  • 2NF: 1NF and no partial dependencies (every non-key attribute depends on the whole of a composite key).
  • 3NF: 2NF and no transitive dependencies (non-key attributes depend only on the key).
    Memory aid: every non-key attribute depends on "the key, the whole key and nothing but the key".
Worked example

A table BOOK(ISBN, Title, AuthorID, AuthorName, PublisherName, PublisherCity):

  1. Key: ISBN. Already 1NF (atomic values).
  2. 2NF: single-attribute key, so no partial dependencies.
  3. 3NF: AuthorName depends on AuthorID, and PublisherCity depends on PublisherName (transitive).
  4. Result: BOOK(ISBN, Title, AuthorID, PublisherID), AUTHOR(AuthorID, AuthorName), PUBLISHER(PublisherID, PublisherName, PublisherCity).
Common traps
Stopping at 2NF when the key is a single attribute
Check for transitive dependencies too.
Putting foreign keys on the "one" side
They go on the "many" side.
Leaving many-to-many relationships unresolved
Use an associative entity.

Practice questions

Original practice questions graded from foundation to exam level, each with a full worked solution. Try them before revealing the solution.

foundation3 marks
In a flat table ORDER(OrderID, CustomerName, CustomerPhone, ProductName, ProductPrice), give an example of an insertion, update and deletion anomaly.
Show worked solution →
  • Insertion: a new product cannot be recorded until someone orders it.
  • Update: if a customer changes phone number, every order row for that customer must be changed; missing one leaves inconsistent data.
  • Deletion: deleting the only order for a product also deletes the product's price information.

Marking guide: 1 mark each.

core5 marks
Normalise ENROLMENT(StudentID, StudentName, CourseCode, CourseTitle, TeacherID, TeacherName, Grade) to 3NF, where a student can take many courses, each course has one teacher, and a grade is per student per course.
Show worked solution →

1NF: assume atomic values; primary key is the composite (StudentID, CourseCode).

2NF (remove partial dependencies):

  • StudentName depends only on StudentID: STUDENT(StudentID, StudentName)
  • CourseTitle, TeacherID, TeacherName depend only on CourseCode: COURSE(CourseCode, CourseTitle, TeacherID, TeacherName)
  • Grade depends on both: ENROLMENT(StudentID, CourseCode, Grade)

3NF (remove transitive dependencies): in COURSE, TeacherName depends on TeacherID, not on CourseCode:

  • COURSE(CourseCode, CourseTitle, TeacherID)
  • TEACHER(TeacherID, TeacherName)

Final 3NF: STUDENT, COURSE, TEACHER, ENROLMENT, with StudentID and CourseCode as foreign keys in ENROLMENT and TeacherID as a foreign key in COURSE.

Marking guide: 1 mark for the composite key, 2 marks for correct 2NF tables, 1 mark for the 3NF split, 1 mark for identifying foreign keys.

exam6 marks
Draw (or describe) an ER diagram for a gym where members book classes, each class is run by one trainer, and a trainer runs many classes. Include cardinality, and explain how the many-to-many relationship is resolved.
Show worked solution →

Entities: MEMBER, CLASS, TRAINER, BOOKING.

Relationships:

  • TRAINER to CLASS: one-to-many (a trainer runs many classes; each class has one trainer). CLASS holds TrainerID as a foreign key.
  • MEMBER to CLASS: many-to-many (a member books many classes; a class has many members).

Resolving many-to-many: create an associative entity BOOKING(MemberID, ClassID, BookingDate) with a one-to-many relationship from MEMBER to BOOKING and from CLASS to BOOKING. Its composite primary key is made of the two foreign keys.

Relational notation:

MEMBER(MemberID, Name, Email), TRAINER(TrainerID, Name), CLASS(ClassID, ClassName, StartTime, TrainerID), BOOKING(MemberID, ClassID, BookingDate).

Marking guide: 1 mark for entities, 2 marks for correct cardinalities, 2 marks for resolving many-to-many, 1 mark for keys in notation.

core4 marks
A school stores club sign-ups in STUDENT_CLUB(StudentID, StudentName, Clubs). Two sample rows are (S01, Ava Lee, "Chess, Robotics") and (S02, Tom Ng, "Debating"). (a) Explain why this table is not in first normal form. (1 mark) (b) Rewrite the table so it is in first normal form, showing the sample data as rows, and state its primary key. (2 marks) (c) Identify the partial dependency that stops your 1NF table from being in second normal form. (1 mark)
Show worked solution →

(a) The Clubs field is not atomic: the S01 row holds two values ("Chess, Robotics") in one field, which is a repeating group. 1NF requires every field to hold a single atomic value.

(b) STUDENT_CLUB(StudentID, ClubName, StudentName)

StudentID ClubName StudentName
S01 Chess Ava Lee
S01 Robotics Ava Lee
S02 Debating Tom Ng

The primary key is the composite key (StudentID, ClubName), because StudentID alone now repeats.

(c) StudentName depends only on StudentID, which is only part of the composite key. (Fix for 2NF: STUDENT(StudentID, StudentName) and STUDENT_CLUB(StudentID, ClubName).)

Marking guide: (a) 1 mark for identifying the non-atomic Clubs field or repeating group. (b) 1 mark for one club per row with the data correctly repeated, 1 mark for the composite key (StudentID, ClubName). (c) 1 mark for StudentName depending on StudentID only. Total 4 marks.

exam5 marks
A sports club database has MEMBER(MemberID, Name, Email) and REGISTRATION(RegID, MemberID, Season, FeePaid). The data dictionary states: RegID is text and the primary key; MemberID in REGISTRATION is a foreign key to MEMBER; FeePaid is a decimal that must be between 0 and 400. MEMBER holds only M01, M02 and M03. The following inserts are attempted. Row 1: REGISTRATION (null, M03, 2026, 150). Row 2: REGISTRATION (R21, M99, 2026, 150). Row 3: REGISTRATION (R22, M02, 2026, -50). Row 4: MEMBER (M04, Jo Park, jo.park@gmial.com), where the member's real address is jo.park@gmail.com. (a) For each of Rows 1 to 3, name the type of integrity that is broken and explain why. (3 marks) (b) Row 4 is accepted by the database. Identify the data quality problem and explain why integrity constraints did not stop it. (2 marks)
Show worked solution →

(a)

  • Row 1: entity integrity. RegID is the primary key, and a primary key value must never be null.
  • Row 2: referential integrity. The foreign key M99 does not match any existing MemberID in MEMBER, so the registration would be an orphaned record.
  • Row 3: domain integrity. FeePaid is -50, which is outside the allowed range of 0 to 400 in the data dictionary (enforced with a CHECK constraint).

(b) The email is inaccurate: it does not match the real value, so it fails the accuracy dimension of data quality (the club's messages will not reach the member). Integrity constraints only test whether a value is allowed (right type, in range, key present, foreign key matches). "jo.park@gmial.com" is a validly formed value, so the database cannot tell that it is wrong. Accuracy has to be protected by other means, such as verification (asking the member to confirm the address) or double entry.

Marking guide: (a) 1 mark per row for the correct integrity type with a valid reason (3 marks). (b) 1 mark for identifying accuracy as the problem, 1 mark for explaining that the value is valid in type and format so constraints cannot detect it. Total 5 marks.

exam14 marks
A veterinary clinic records every appointment in one flat table: VISIT(ApptID, ApptDate, PetID, PetName, Species, OwnerID, OwnerName, OwnerPhone, VetID, VetName, TreatmentCode, TreatmentDesc, Fee). Business rules: each appointment is for one pet and is handled by one vet; an appointment can include several treatments, and the table stores one row per treatment given at an appointment; each pet has one owner, and an owner can have many pets; each treatment has a standard description and fee. (a) Using this table, describe one example each of an insertion, an update and a deletion anomaly. (3 marks) (b) State the primary key of VISIT and justify your choice. (2 marks) (c) Normalise VISIT to third normal form. Show the tables at 2NF and at 3NF in relational notation, identifying all primary and foreign keys, and name the dependency removed at each step. (6 marks) (d) Describe the relationships between the entities in your 3NF design, including cardinality, as they would appear on an ER diagram. (3 marks)
Show worked solution →

(a)

  • Insertion: a new treatment (with its description and fee) cannot be recorded until it is given at an appointment, and a new owner cannot be stored until their pet has an appointment.
  • Update: if an owner changes phone number, every row for every visit by every pet of that owner must be changed; missing one leaves inconsistent phone numbers.
  • Deletion: deleting the only appointment at which a treatment was given also deletes that treatment's description and fee (or deleting a pet's only visit loses the pet and owner details).

(b) The primary key is the composite key (ApptID, TreatmentCode). ApptID alone repeats when an appointment has several treatments, and TreatmentCode repeats across appointments, but each combination of appointment and treatment appears only once.

(c) 2NF (remove partial dependencies on part of the composite key):

  • ApptDate, PetID, PetName, Species, OwnerID, OwnerName, OwnerPhone, VetID and VetName depend on ApptID only.
  • TreatmentDesc and Fee depend on TreatmentCode only.

APPOINTMENT(ApptID, ApptDate, PetID, PetName, Species, OwnerID, OwnerName, OwnerPhone, VetID, VetName)
TREATMENT(TreatmentCode, TreatmentDesc, Fee)
APPT_TREATMENT(ApptID, TreatmentCode), with ApptID and TreatmentCode as foreign keys.

3NF (remove transitive dependencies): in APPOINTMENT, PetName, Species and OwnerID depend on PetID; OwnerName and OwnerPhone depend on OwnerID; VetName depends on VetID. None of these depend directly on ApptID.

APPOINTMENT(ApptID, ApptDate, PetID, VetID), with PetID and VetID as foreign keys
PET(PetID, PetName, Species, OwnerID), with OwnerID as a foreign key
OWNER(OwnerID, OwnerName, OwnerPhone)
VET(VetID, VetName)
TREATMENT(TreatmentCode, TreatmentDesc, Fee)
APPT_TREATMENT(ApptID, TreatmentCode), with both attributes as foreign keys

(d)

  • OWNER to PET: one-to-many (an owner has many pets; each pet has one owner).
  • PET to APPOINTMENT: one-to-many (a pet has many appointments; each appointment is for one pet).
  • VET to APPOINTMENT: one-to-many (a vet handles many appointments; each appointment has one vet).
  • APPOINTMENT to TREATMENT is many-to-many, resolved by the associative entity APPT_TREATMENT: APPOINTMENT to APPT_TREATMENT is one-to-many, and TREATMENT to APPT_TREATMENT is one-to-many. The crow's foot (many end) is at PET, APPOINTMENT and APPT_TREATMENT respectively.

Marking guide: (a) 1 mark each for a valid insertion, update and deletion anomaly (3 marks). (b) 1 mark for (ApptID, TreatmentCode), 1 mark for the justification. (c) 1 mark for naming partial dependencies at 2NF, 2 marks for the correct 2NF tables, 1 mark for naming the transitive dependencies at 3NF, 2 marks for the correct final 3NF tables with keys and foreign keys (6 marks). (d) 1 mark for the three one-to-many relationships, 1 mark for identifying the many-to-many between APPOINTMENT and TREATMENT, 1 mark for resolving it with APPT_TREATMENT. Total 14 marks.

exam18 marks
A music school is building a database from these requirements. Each student has an ID, name, date of birth and email. The school teaches several instruments, each with an ID, name and weekly hire fee. A student can learn several instruments and an instrument is learnt by many students. For each instrument a student learns, the school records the start date and the one tutor assigned. Each tutor has an ID, name and phone number, and can be assigned to many student and instrument pairs. (a) Identify the entities and describe each relationship with its cardinality, as shown on an ER diagram. (4 marks) (b) Explain how the many-to-many relationship is resolved, including the primary key of the new entity and why it is suitable. (3 marks) (c) Write the design in relational notation, identifying every primary and foreign key. (4 marks) (d) Write data dictionary entries for the student Email attribute and the instrument HireFee attribute, giving data type, size or format, description and a constraint for each. (4 marks) (e) Explain how entity integrity and referential integrity apply to the table that holds the start date, giving an example of an insert each would reject. (3 marks)
Show worked solution →

(a) Entities: STUDENT, INSTRUMENT, TUTOR and the associative entity ENROLMENT.

  • STUDENT to INSTRUMENT: many-to-many (a student learns many instruments; an instrument is learnt by many students).
  • STUDENT to ENROLMENT: one-to-many; INSTRUMENT to ENROLMENT: one-to-many (after resolving the many-to-many).
  • TUTOR to ENROLMENT: one-to-many (a tutor is assigned to many student and instrument pairs; each pair has one tutor).
    On the ER diagram the crow's foot (many end) is at ENROLMENT on each line.

(b) A many-to-many relationship cannot be stored directly, so an associative entity ENROLMENT is created between STUDENT and INSTRUMENT. It holds StudentID and InstrumentID as foreign keys, plus the attributes that belong to the pairing (StartDate and TutorID). Its primary key is the composite key (StudentID, InstrumentID): each student learns a given instrument only once, so each combination is unique, while either attribute alone repeats.

(c)

STUDENT(StudentID, FirstName, Surname, DateOfBirth, Email)
INSTRUMENT(InstrumentID, InstrumentName, HireFee)
TUTOR(TutorID, TutorName, Phone)
ENROLMENT(StudentID, InstrumentID, StartDate, TutorID)
Foreign keys: in ENROLMENT, StudentID references STUDENT, InstrumentID references INSTRUMENT and TutorID references TUTOR.

(d)

Attribute Data type Size/format Description Constraint
Email (STUDENT) Text Up to 100 characters, name@domain Contact email for the student or family Required (not null); must contain one @ and a full stop after it
HireFee (INSTRUMENT) Decimal (currency) 2 decimal places, for example 12.50 Weekly fee in dollars to hire the instrument Must be 0 or more, for example CHECK (HireFee >= 0)

(e)

  • Entity integrity: every ENROLMENT record must have a complete, unique primary key, so neither StudentID nor InstrumentID may be null and the same (StudentID, InstrumentID) pair may not appear twice. It would reject, for example, a second enrolment of the same student on the same instrument, or an enrolment with no InstrumentID.
  • Referential integrity: each foreign key in ENROLMENT must match an existing record. It would reject an enrolment whose StudentID, InstrumentID or TutorID does not exist in STUDENT, INSTRUMENT or TUTOR, and it stops a tutor being deleted while enrolments still refer to them, so no orphaned records are created.

Marking guide: (a) 1 mark for the entities, 1 mark for the many-to-many between STUDENT and INSTRUMENT, 1 mark for TUTOR one-to-many, 1 mark for correct cardinality at the associative entity (4 marks). (b) 1 mark for the associative entity, 1 mark for the composite key, 1 mark for why it is unique (3 marks). (c) 1 mark per correct table with keys, including foreign keys in ENROLMENT (4 marks). (d) 2 marks per complete entry: 1 mark for type and size or format, 1 mark for a sensible description and constraint (4 marks). (e) 1 mark for explaining entity integrity with a rejected example, 1 mark for explaining referential integrity with a rejected example, 1 mark for linking to the composite key or orphaned records (3 marks). Total 18 marks.

Practise this

Sources & how we know this

Based on the official SCSA syllabus. Not yet individually reviewed by a person; always check SCSA and your school's materials for current rules.

ExamExplained