IFB105 Practice Exam — Fully Worked Solutions

Every question from the official practice exam, with the answer and the reasoning so you can reproduce it. Try each yourself first, then check.

SECTION A — Multiple choice (15 × 1 mark)

Q1 — Which is an elementary fact? → (c) An elementary fact is atomic, asserts something true, and has no conditions.

• (a) has Phone and Email → can be split → not elementary.
• (b) "is not a patient" → a negation, not a true assertion.
• (c) "Employee E1235 has the Name Elizabeth Smith" → single atomic true fact ✓.
• (d) "...so..." → contains a condition.

Q2 — Value type object in "Student N2345 received Grade 5 in Unit ABC123"? → (a) Grade A value type has no reference mode. StudentID/UnitCode are reference modes; Unit is an entity type; "ABC123" is a data value. Grade is the value type object.

Q3 — Arity of "Hotel Room 1123 is vacant"? → (a) Unary Only one object (Hotel Room) playing one role (is vacant/occupied) → unary.

Q4 — One-to-one association? → (d)

• (a),(b) many-to-many. (c) one-to-many (one prof, many classes).
• (d) one patient ↔ one medical record both ways → 1:1 ✓.

Q5 — Which diagram is incorrect? → (b) The n–1 rule: a uniqueness constraint must span at least (n–1) roles of an n-ary fact type. Option B's UC placement breaks this rule, so it's the invalid diagram.

Q6 — True statement about the hospital ORM? → (a)

• (a) Patient↔Bed shown as 1:1 ✓ — "one bed per patient, one patient per bed".
• (b) would need a mandatory dot on Bed (none shown).
• (c) Patient–Clinician is m:n, not 1:n.
• (d) Clinician–Specialty is 1:n, not 1:1.

Q7 — Correct RMap? → (d)

• Rule 1 (m:n): beingSeenBy {patientID, clinicianEmpID}, integratedDevice {bedID, deviceID}.
• Rule 2: patient {patientID, patientName}, clinician {empID, clinicianName, medicalSpecialty}, bed {bedID, wardName}.
• Rule 4.2 (mandatory on Patient): bedID joins the patient table → patient {patientID, patientName, bedID}. Only (d) has all of these correctly.

Q8 — "Every super key is a candidate key, but not every candidate key is a super key." → (b) False It's the reverse: every candidate key is a super key, but not every super key is a candidate key (some super keys are reducible).

Q9 — Highest normal form of the Store/Product table? → (b) 1NF Atomic + composite key (StoreID, ProductID) → 1NF. But ManagerID & ManagerName depend on StoreID alone (part of the key) → partial dependency → fails 2NF. Highest = 1NF.

Q10 — Book table normalised to 3NF? → (a) LoanedTo is optional (nulls present) → [LoanedTo], eliminating (b),(c). AuthorID is mandatory, eliminating (d). A book has multiple authors → m:n needs a bridge table:

Book {BookID, BookTitle, [LoanedTo]}
Author {AuthorID, AuthorName}
Authorship {BookID, AuthorID}

Q11 — True statement (PK = PatientID, DoctorID)? → (d) P1214 appears twice with different WardNames → WardName is NOT dependent on PatientID (kills a,b,c). But the same PatientID always gives the same PatientName → PatientName depends on part of the PK (PatientID only) → partially dependent ✓.

Q12 — Candidate key? → (c) (PatientID, WardName) (DoctorID, WardName) duplicates; PatientID alone duplicates. (PatientID, WardName) is unique and irreducible (drop DoctorID from option d and it's still unique) → candidate key.

Q13 — "A primary key is used in the child table to reference the parent." → (b) False That describes a foreign key.

Q14 — What does the GROUP BY/COUNT query return? → (d) Groups loans by bookID and counts rows per book → how many times each bookID appears in the loans table.

Q15 — Correct UPDATE query? → (c)

UPDATE Customers SET Address = '19 Albert Street' WHERE CustomerID = 'CU091214';

ALTER changes structure; option B is missing UPDATE; MODIFY isn't valid SQL.

SECTION B — Short answer

Q16 — CARE principles for the AIBS scenario

• (a) C — Collective Benefit: YES. The data was used to improve health outcomes across many hospitals → benefits the community.
• (b) A — Authority to Control: NO. The communities no longer have access to the data or any say in how it's managed/used → they don't control it.
• (c) R — Responsibility: NO. After collection, relationships with the communities weren't maintained (all original contacts gone) → fails responsibility.
• (d) E — Ethics: NO. Communities weren't consulted on benefits, harms or future use; potential for use that violates their rights → fails ethics.

Q17 — Number of objects per elementary fact

• "Employee E0123456 has Job Title Associate Lecturer" → 2 (Employee, Job Title).
• "Student N014156 received Mark 65 on Exam ABC123" → 3 (Student, Mark, Exam).
• "Device D854125 is unallocated" → 1 (Device).
• "Device D893665 allocated to Employee E0123456" → 2 (Device, Employee).

Q18 — CSDP Steps 1 & 2 for the Product table (a) One elementary fact per fact type (using PRO123512):

1. The Product with ProductID "PRO123512" has the ProductName "Sunshine Farms Apple Juice".
2. The Product with ProductID "PRO123512" has the Price $5.00.
3. The Product with ProductID "PRO123512" has the Quantity 2L.
4. The Product with ProductID "PRO123512" has the UnitPrice $2.50.

(b) Step 2 — fact types drawn (described, since you'll draw it):

• product (.ID) is the central entity type.
• product —[has name]→ ProductName (value type, dashed box).
• quantity (L:) —[has]→ product (value type with unit L).
• product —[costs]→ price (AUD:) (value type).
• product —[costs per litre]→ unitPrice (AUD:) (value type). All are binary fact types with the uniqueness bar on the product side (each product has one name/price/quantity/unitPrice).

Q19 — Constraint violations (ORM: Student currently-enrolled-in / has-completed Degree) Constraints: C1 = every Student must play "currently enrolled in" (mandatory); C2 = student number unique in "currently enrolled in" (a student is enrolled in one degree at a time); C3 = (student, course) combination unique in "has completed".

Q20 — RMap for the Device/Installation schema

• Rule 1: does not apply (no m:n).
• Rule 2: Device {assetID, RFIDTagNo, MFID, [technicianNotes]} (technicianNotes optional).
• Rule 3 (nested "installation"): Installation {bedID, assetID, installationDate} — maintenanceDate omitted (derived = installationDate + 1 year).
• Rule 4: does not apply.
• FK: Installation.assetID → Device.assetID.

Q21 — Normalise the Provider/Appointment table to 3NF (PK = ProviderID, AppointmentTime)

• providerName, role depend on ProviderID alone → partial dependency (fails 2NF).
• patientName depends on patientID (non-key) → transitive (fails 3NF).

Provider    {providerID, providerName, role}
Appointment {providerID, appointmentTime, patientID}    FK providerID→Provider, FK patientID→Patient
Patient     {patientID, patientName}

Q22 — Fill in the CREATE TABLE blanks

Q23 — Will each INSERT execute or fail?

Q24 — Customers whose combined purchases > $100

SELECT firstName, lastName, email
FROM Customer
WHERE customerID IN (
    SELECT customerID FROM Orders
    GROUP BY customerID
    HAVING SUM(totalPrice) > 100
);

(Group orders by customer, sum totalPrice, keep those over 100, return their details.)

Q25 — What do these queries return?

1. Nested IN: VIP tickets → orderID 4 → customer 1 → Holly Richardson.
2. HAVING count(*)>2 gives orders {1,3,5}; totalPrice<100 gives {1,4,5}; EXCEPT removes {1,4,5} → 3.
3. Extras orderIDs {2,5}; only order 5 has a 'Child' ticket → 5.
4. LEFT JOIN where notes IS NULL AND quantity IS NOT NULL → only Extras row for order 5 (Popcorn, qty 2, no notes) → totalPrice of order 5 → 59.00.