04 Database foundations, keys and integrity

Database foundations, keys and integrity

← Index · Solutions

Priority key · Priorities guide emphasis; they do not remove taught scope.

Exam recall

Candidate = minimal unique identifier; primary = chosen candidate; foreign = reference. Explain an anomaly using an operation and its consequence.

Database vocabulary that earns precise marks priority/medium

A database is an organised collection of related data. A DBMS is software for defining, storing, retrieving and controlling access to that data. In a relational database, a table/relation represents a type of entity or relationship; a record/row/tuple represents one occurrence; a field/column/attribute represents one property. A field’s domain is its permitted set/type of values.

A schema describes the structure and constraints, whereas the data consists of the current records. Data independence means changes to storage or database structure can be insulated from applications at the appropriate level; it does not mean tables have no relationships.

Compared with separate application files, a DBMS can centralise access control, enforce constraints, coordinate shared updates and reduce unnecessary duplication. Costs include software/hardware, specialist administration and dependence on a shared service. A database does not automatically remove all duplication or every failure risk.

Keys: work from the rules, not the sample rows priority/high

An identifier solves a practical problem: which single record does an update refer to? A student’s name may describe them but need not distinguish them from another student. A student ID is designed to identify them. A composite key is needed when the row represents a combination, such as one student’s enrolment in one course.

Key	Meaning
Candidate	A minimal set of attributes that uniquely identifies each record. Removing any attribute loses that guarantee.
Primary	The candidate key selected to identify records; unique and not null.
Composite	A key comprising more than one attribute.
Secondary/alternate	In your school’s terminology, a candidate key not selected as primary. Use this convention in the exam.
Foreign	Attribute(s) referencing the key of a related table, linking records and supporting referential integrity.

A column being unique in four displayed rows does not prove it will always be unique. Use business rules: names can repeat; a student can enrol in many courses; a customer can order twice in a day.

Entity integrity requires a valid unique, non-null primary key. Referential integrity requires a foreign-key value to match a referenced key, unless a null is allowed by the design. A foreign key need not be unique: many bookings may reference the same customer.

Redundancy and anomalies priority/high

Enrolment rows: StudentID, CourseID, CourseName
               S1         C2        Computing
               S2         C2        Computing
 
One fact (C2 is called Computing) is stored in two enrolments.
Change only one copy → conflicting course names: an update anomaly.
Delete both enrolments → lose the only stored name: a deletion anomaly.
New course with no enrolments → no suitable enrolment row: insertion anomaly.

Repeated foreign-key values can be necessary; unnecessarily storing the same descriptive fact in several records creates redundancy. Repeating C2 as a foreign key is how the two enrolments reference the same course; repeating the course’s descriptive facts is what creates avoidable maintenance work.

Redundancy is unnecessary repetition of the same fact. An update anomaly occurs when repeated copies must all be changed and can become inconsistent. An insertion anomaly prevents recording one fact without another unrelated fact. A deletion anomaly loses a fact unintentionally when the only record carrying it is deleted.

Use concrete operations: “Changing a customer’s phone requires updating several bookings; updating only one leaves conflicting numbers.” This explains more than “the data gets dirty”.

Backup creates a recoverable copy for failure/corruption. Archiving moves or preserves inactive historical data for long-term reference. An archive is not necessarily a current recovery copy. Access rights restrict which users can read or change data; constraints restrict which data states are accepted.

Transactions: ACID priority/medium

Atomicity: a transaction’s changes succeed together or are rolled back together. Consistency: valid transactions preserve the database’s defined integrity rules. Isolation: concurrent transactions are controlled so that interference does not expose inappropriate intermediate states, according to the isolation level. Durability: committed changes survive subsequent failure through the system’s recovery mechanisms.

For a money transfer, debit and credit should form one transaction: atomicity prevents a debit being committed while its matching credit is lost. Consistency is about maintaining rules, not promising that every human-entered fact is true. Durability concerns committed data, not uncommitted work.

Worked example — original

Enrolment(StudentID, CourseID, StudentName, CourseName) allows each student to take each course once. Neither StudentID nor CourseID alone is unique, but their pair is. Therefore (StudentID, CourseID) is a composite candidate key and a suitable primary key. Adding StudentName to this pair gives a non-minimal identifying set, not another candidate key.

If a course name is repeated across 30 enrolments, changing it in only 29 creates an update anomaly. Moving the course facts to Course(CourseID, CourseName) makes one place authoritative.

Practice

Exam focus: HCI 2024 Q5 tests anomalies and whether a key accommodates multiple trips; HCI 2025 Q5 requires suitable database structures. A key that fits the displayed rows may still fail the business rules. Read HCI 2024 Q5, PDF p.3 for the original scenario.

Answering approach: identify one row’s meaning → establish uniqueness → check minimality → test a permitted repeated occurrence. For an anomaly, name the operation, the affected fact and the unwanted result.

04A — original. Book(BookID, ISBN, Title) assumes BookID and ISBN are each unique for the records represented. BookID is chosen as primary. Identify the candidate keys and the school’s secondary key. Is (BookID, Title) a candidate key? Explain.

CK: BookID, ISBN, Title
Secondary key: ISBN and Title

No. They are a composite key

Marker feedback: ⚠️ Partially correct. BookID and ISBN are the candidate keys because each is guaranteed to be unique and each is minimal. Title is not guaranteed to be unique. The secondary key is only ISBN. Your conclusion that (BookID, Title) is not a candidate key is correct, but the reason is wrong: a candidate key is allowed to be composite. This pair fails because it is not minimal—BookID alone already identifies the record. (BookID, Title) is a composite superkey, not a candidate key.

Correct solution: The candidate keys are {BookID} and {ISBN}. Since {BookID} is selected as the primary key, {ISBN} is the school’s secondary/alternate key. (BookID, Title) is not a candidate key because it is not minimal: removing Title still leaves BookID, which uniquely identifies every record.

04B — adapted from HCI 2024 Q5. Booking(VehicleNo, CustomerID, Date, Quantity) uses primary key (VehicleNo, CustomerID, Date). Explain why permitting the same customer to book the same vehicle twice on one day challenges this key. Suggest a solution and state how VehicleNo can support referential integrity with Bus(VehicleNo, Capacity).

It is a composite key making of (VehicleNo, CustomerID, Date). However, since same customer visit, its the same CustomerID, same vehicle, so same VehicleNo, same date so Date is same. As all 3 Candidate key for this composite key is the same, this will cause data redundancy.
Create another CK called BookingID.
what is referential integrity i dont see it in my school notes

Marker feedback: ⚠️ You correctly noticed that the two bookings would have the same VehicleNo, CustomerID and Date. However, these three fields are attributes/components of one composite key; they are not “three candidate keys”. The immediate problem is not data redundancy. The primary-key uniqueness constraint prevents both bookings from being stored because their key values collide. BookingID is a suitable solution, but state that it should be unique and selected as the new primary key. The referential-integrity part was not answered.

Correct solution: Both permitted bookings would have exactly the same primary-key value (VehicleNo, CustomerID, Date), so the database could not store them as two separate records without violating primary-key uniqueness. Add a unique BookingID for every booking and use it as the primary key. Booking.VehicleNo should be a foreign key referencing Bus.VehicleNo; each non-null VehicleNo in Booking must match an existing VehicleNo in Bus, preventing a booking from referring to a bus that does not exist.

Referential integrity: A foreign-key value must match an existing referenced key in the related table, unless a null value is permitted. In this example, Booking.VehicleNo refers to Bus.VehicleNo. The definition is also given earlier in this note under “Keys: work from the rules, not the sample rows”.

Hints

04A: minimality matters. 04B: identify the two records that would collide.

Revision checklist

• 04.1 Distinguish database, DBMS, table/relation, record/tuple, field/attribute and domain.
• 04.2 Explain why a DBMS is used and compare a shared database with separate files.
• 04.3 Identify candidate, primary, secondary/alternate, composite and foreign keys using school terminology.
• 04.4 Check key minimality, uniqueness and suitability against the scenario’s business rules.
• 04.5 Distinguish redundant data from inconsistent data.
• 04.6 Explain insertion, update and deletion anomalies using a concrete record-level example.
• 04.7 Identify functional, partial and transitive dependencies from business rules.
• 04.8 Explain entity and referential integrity and the role of constraints.
• 04.9 Explain the ACID properties at the level taught in the school notes.

Visual revision mindmap

Open this mindmap and its text version · All 21 mindmaps

Your mindmap framework

Centre: Database foundations, keys and integrity. Build the six branches below. For each subbranch, add a short definition, a labelled sample and one exam trap from memory; then check the chapter.

flowchart LR
    C["04 • Revision map"]
    C --> B0["Database model"]
    C --> B1["Choosing identifiers"]
    C --> B2["Linking tables"]
    C --> B3["Redundancy and inconsistency"]
    C --> B4["Transactions and data care"]
    C --> B5["Visual checks and mistakes"]

• Database model

  • Database versus DBMS.
  • Table/relation, row/record/tuple, field/attribute.
  • Domain: permitted values; schema versus current data.
  • DBMS benefits and costs; data independence.

• Choosing identifiers

  • Candidate: unique and minimal.
  • Primary: chosen candidate, unique and non-null.
  • Composite: several attributes.
  • School secondary/alternate: unchosen candidate.

• Linking tables

  • Foreign key → referenced key.
  • Entity integrity versus referential integrity.
  • Repeated foreign keys can represent legitimate relationships.
  • Null only where permitted; do not infer uniqueness from sample rows.

• Redundancy and inconsistency

  • Same descriptive fact stored repeatedly.
  • Update anomaly: conflicting copies.
  • Insertion anomaly: cannot record one fact independently.
  • Deletion anomaly: removing a row loses another needed fact.

• Transactions and data care

  • Atomicity: all-or-nothing change.
  • Consistency: defined integrity rules.
  • Isolation: controlled concurrent interaction.
  • Durability: committed change survives failure; backup versus archive.

• Visual checks and mistakes

  • Draw Student, Course and Enrolment with PK/FK labels.
  • Show one actual insert/update/delete and its consequence.
  • Test a proposed key against two permitted same-day bookings.
  • Avoid: repeated value always redundant; foreign key always unique.

Close the notes and test the map: explain one branch aloud, sketch its sample, then answer a linked practice question. Mark any missing link to revisit.

Source trail

9569 §§3.3.1–3.3.3; school DBMS slides 1–2; MOE Introduction to Databases.

HCI 2022 Q5(a); 2024 Q5(a),(f); apply alongside the design questions in all four HCI papers.

Source guide records provenance and original-paper locations.