IFB105 — Database Management

Practice Examination

Time allowed: 2 hours  |  Total: 100 marks  |  Conditions: Open book — paper notes permitted (no digital).

This paper has two sections. Section A is multiple choice (20 marks). Section B is short answer (80 marks). Answer all questions. Where a question says "explain", a one-sentence reason is enough. SQL questions use the GigHub case-study database on the next page. Steps 6–7 of the CSDP are not examined.

Sit it like the real thing: phone away, paper notes only, 2-hour timer. Then mark yourself against _PRACTICE_EXAM_Solutions.

The GigHub case-study database (for the SQL questions)

A live-music ticketing system. The schema (primary keys bold, foreign keys italic):

• Venue(venueID, venueName, city, capacity)
• Event(eventID, eventName, eventDate, venueID) — venueID → Venue
• Customer(customerID, firstName, lastName, email (UNIQUE), city)
• Ticket(ticketID, eventID, customerID, ticketType, price) — eventID → Event, customerID → Customer

Current data:

Venue

Event

Customer

Ticket

Section A — Multiple choice (20 × 1 mark = 20 marks)

Circle the single best answer.

A1. Which of the following is an elementary fact? (a) Tom is happy and Sara is sad.   (b) The Student with ID 'n7' is enrolled.   (c) All students passed.   (d) If it rains, the match is cancelled.

A2. In "The Lecturer with StaffID 'E22' teaches IFB105", what is StaffID? (a) entity type   (b) reference mode   (c) value   (d) predicate

A3. "The Seat with number 'A4' is reserved." What is the arity of this fact? (a) unary   (b) binary   (c) ternary   (d) it is not a fact

A4. Each passport belongs to exactly one person, and each person has exactly one passport. This association is: (a) 1:1   (b) 1:n   (c) n:1   (d) m:n

A5. Which statement is true? (a) Every super key is a candidate key.   (b) Every candidate key is a super key.   (c) A primary key may contain nulls.   (d) An alternate key is the chosen primary key.

A6. A non-key attribute that depends on only part of a composite key is a: (a) transitive dependency   (b) partial dependency   (c) full dependency   (d) trivial dependency

A7. A table with composite PK (StudentID, UnitID) has StudentName depending only on StudentID. It is at best in: (a) 0NF   (b) 1NF   (c) 2NF   (d) 3NF

A8. To move a relation into 2NF you eliminate: (a) repeating groups   (b) partial dependencies   (c) transitive dependencies   (d) foreign keys

A9. To move a relation into 3NF you eliminate: (a) partial dependencies   (b) transitive dependencies   (c) non-atomic values   (d) candidate keys

A10. Which relational-mapping rule maps a many-to-many fact type to its own table? (a) Rule 1   (b) Rule 2   (c) Rule 3   (d) Rule 4

A11. In RMap notation, a double underline indicates: (a) a foreign key   (b) an optional column   (c) the primary key   (d) a uniqueness constraint that is not the key

A12. Which SQL clause filters groups after aggregation? (a) WHERE   (b) GROUP BY   (c) HAVING   (d) ORDER BY

A13. SELECT name FROM Staff WHERE name LIKE '%n'; returns names that: (a) start with n   (b) end with n   (c) contain n   (d) equal "n"

A14. Which statement removes all rows from table T but keeps the table? (a) DROP TABLE T;   (b) DELETE FROM T;   (c) REMOVE FROM T;   (d) ALTER TABLE T DROP;

A15. An INSERT supplies a foreign-key value with no matching parent row. It fails due to: (a) entity integrity   (b) domain integrity   (c) referential integrity   (d) a syntax error

A16. Which set operator returns rows in the first query but not the second? (a) UNION   (b) INTERSECT   (c) EXCEPT   (d) JOIN

A17. A join returning all rows from the left table plus matched right rows (NULLs where none) is a: (a) INNER JOIN   (b) LEFT JOIN   (c) RIGHT JOIN   (d) CROSS JOIN

A18. Which mechanism hides sensitive columns from a user by exposing only a stored query? (a) Index   (b) View   (c) Trigger   (d) Commit

A19. In the CARE principles, the principle that the community decides what data is collected and shared is: (a) Collective Benefit   (b) Authority to Control   (c) Responsibility   (d) Ethics

A20. Which transaction (ACID) property guarantees that all operations occur or none do? (a) Atomicity   (b) Consistency   (c) Isolation   (d) Durability

Section B — Short answer (80 marks)

B1 — Elementary facts & objects (12 marks)

(a) Write one elementary fact for each fact type represented by this table (4 marks):

(b) How many objects are in this fact? (2 marks) "The Customer with customerID 'C3' bought the Ticket with ticketID 'T8' for the Event with eventID 'E1'."

(c) State the arity of: "The Ticket with ticketID 'T8' is refunded." (2 marks)

(d) Explain why elementary facts must not use logical connectives (and, or, not, if) or quantifiers (all, some). (4 marks)

B2 — Reading an ORM schema (10 marks)

An ORM schema contains the binary fact type Customer (.custID) — places — Order (.orderNr). There is a uniqueness bar under the Order role only, and a mandatory-role dot on the Customer side of places.

(a) What is the multiplicity of places (Customer to Order)? (2 marks) (b) What does the mandatory-role dot on Customer mean in plain English? (2 marks) (c) For "exactly one" to be enforced on a role, which two constraints must both be present? (2 marks) (d) State whether each CS-diagram fragment is legal or illegal, with a one-line reason (4 marks):   (i) Two entity-type boxes joined directly by a line (no role box between them).   (ii) A single role box connected to two different entity-type boxes.

B3 — Relational mapping (12 marks)

An ORM conceptual schema has these fact types and constraints:

• Member (.memberID) has MemberName — 1:1, functional & mandatory on Member
• Member (.memberID) has email — optional (may be null)
• Member enrolled in Class (.classID) — many-to-many
• Class (.classID) has ClassName — 1:1, functional
• Class (.classID) is led by Trainer (.trainerID) — each class has one trainer; a trainer leads many classes
• Trainer (.trainerID) has TrainerName — 1:1, functional

Produce the full relational mapping: list every relation with its columns, underline primary keys, mark optional columns with [ ], and note each foreign key (which column references which table). Apply the rules in order. (12 marks)

B4 — Normalisation to 3NF (14 marks)

The relation ENROLMENT has primary key (StudentID, UnitCode) and is in 1NF:

The functional dependencies are: StudentID → StudentName · UnitCode → UnitName · (StudentID, UnitCode) → Grade, TutorID · TutorID → TutorName

(a) Is ENROLMENT in 2NF? Identify every partial dependency. (4 marks) (b) Identify the transitive dependency. (2 marks) (c) Give the 3NF schema: list each relation with PK underlined and FKs noted. (8 marks)

B5 — SQL: Data Definition (8 marks)

Write a CREATE TABLE statement for Ticket:

• ticketID — integer, auto-incrementing primary key
• eventID — integer, mandatory, foreign key to Event(eventID)
• customerID — integer, mandatory, foreign key to Customer(customerID)
• ticketType — may only be 'GA', 'VIP', or 'Backstage'
• price — decimal

B6 — SQL: Data Manipulation & Select (16 marks)

Use the GigHub data on page 2.

(a) For each statement, write Executed or Fail, and if it fails give the reason (6 marks):   (i) INSERT INTO Ticket VALUES (7, 2, 3, 'GA', 45);   (ii) INSERT INTO Customer VALUES (4, 'Sam', 'Day', 'mia@x.com', 'Cairns');   (iii) INSERT INTO Ticket VALUES (8, 9, 1, 'GA', 50);

(b) Write a query returning the first and last name of every customer who bought a VIP ticket. (4 marks)

(c) Write a query returning each eventName and the number of tickets sold, but only for events with more than 2 tickets. (4 marks)

(d) What does this query return (give the actual rows)? (2 marks)

SELECT eventName FROM Event
WHERE venueID = (SELECT venueID FROM Venue WHERE venueName = 'The Triffid');

B7 — Ethics: the CARE principles (8 marks)

A university obtains health data from an Indigenous community after gaining consent for a diabetes study only. Two years later, researchers reuse the same data for an unrelated genetics study without returning to the community, and publish findings that portray the community negatively.

(a) Does this comply with Authority to Control (A)? Explain. (3 marks) (b) Does this comply with Ethics (E)? Explain. (3 marks) (c) State one thing the researchers should have done to act ethically. (2 marks)

END OF PAPER — 100 marks. Now check yourself against _PRACTICE_EXAM_Solutions.

Member   {memberID, memberName, [email]}
Class    {classID, className, trainerID}        FK trainerID → Trainer.trainerID
Trainer  {trainerID, trainerName}
EnrolledIn {memberID, classID}                  FK memberID → Member.memberID,
                                                FK classID  → Class.classID

Student   {StudentID, StudentName}
Unit      {UnitCode, UnitName}
Tutor     {TutorID, TutorName}
Enrolment {StudentID, UnitCode, Grade, TutorID}
            FK StudentID → Student.StudentID
            FK UnitCode  → Unit.UnitCode
            FK TutorID   → Tutor.TutorID

CREATE TABLE Ticket (
  ticketID    INT PRIMARY KEY AUTO_INCREMENT,
  eventID     INT NOT NULL,
  customerID  INT NOT NULL,
  ticketType  ENUM('GA','VIP','Backstage'),
  price       DECIMAL(6,2),
  FOREIGN KEY (eventID)    REFERENCES Event(eventID),
  FOREIGN KEY (customerID) REFERENCES Customer(customerID)
);

SELECT DISTINCT c.firstName, c.lastName
FROM Customer c
JOIN Ticket t ON c.customerID = t.customerID
WHERE t.ticketType = 'VIP';

SELECT e.eventName, COUNT(*) AS ticketsSold
FROM Event e
JOIN Ticket t ON e.eventID = t.eventID
GROUP BY e.eventID, e.eventName
HAVING COUNT(*) > 2;