IFB105 — Extra Practice Questions

Fresh exam-style questions modelled on the real practice exam and tutorials. Try them by hand (you'll handwrite in the real exam), then check the Answer Key at the end.

SECTION A — Multiple choice (do these closed-book first)

P1. Which of the following is an elementary fact? (a) The Car with Rego "ABC123" is red and has 4 doors. (b) The Car with Rego "ABC123" is registered. (c) The Car with Rego "ABC123" is faster than the Car with Rego "XYZ789". (d) If the Car with Rego "ABC123" is serviced then it is roadworthy.

P2. In "The Lecturer with StaffID 'E22' teaches the Unit with UnitCode 'IFB105'", which is a reference mode? (a) Lecturer (b) StaffID (c) "IFB105" (d) teaches

P3. "The Room with Number '201' is booked." What is the arity? (a) Binary (b) Ternary (c) Unary (d) Not a fact

P4. Which describes a many-to-many association? (a) Each passport belongs to one person; each person has one passport. (b) An author writes many books; a book can have many authors. (c) A country has many cities; a city is in one country. (d) Each invoice has one customer; a customer has many invoices.

P5. "Every candidate key is a super key." This statement is: (a) True (b) False

P6. A non-key attribute that depends on another non-key attribute is a: (a) Partial dependency (b) Full dependency (c) Transitive dependency (d) Trivial dependency

P7. A table has composite PK (StudentID, CourseID). StudentName depends only on StudentID. The table is at best in: (a) 0NF (b) 1NF (c) 2NF (d) 3NF

P8. Which key has the properties Unique + Irreducible but may contain nulls? (a) Super key (b) Candidate key (c) Primary key (d) Foreign key

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

P10. Which correctly deletes the customer with ID 'C5'? (a) DROP FROM Customer WHERE id='C5'; (b) DELETE Customer WHERE id='C5'; (c) DELETE FROM Customer WHERE id='C5'; (d) REMOVE FROM Customer WHERE id='C5';

P11. SELECT name FROM Staff WHERE name LIKE '_a%'; returns names where: (a) the name contains "a" (b) the 2nd letter is "a" (c) the name starts with "a" (d) the name ends with "a"

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

P13. An INSERT adds a row whose foreign key value has no matching row in the parent table. It will: (a) Execute (b) Fail — entity integrity (c) Fail — referential integrity (d) Fail — duplicate key

P14. Which is a security mechanism that hides sensitive columns from a user? (a) Index (b) View (c) Trigger (d) Rollback

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

P16. Which transaction property guarantees "all operations happen or none do"? (a) Atomicity (b) Consistency (c) Isolation (d) Durability

P17. A JOIN that returns all rows from the left table plus matching right rows (NULLs where none) is a: (a) INNER JOIN (b) LEFT JOIN (c) RIGHT JOIN (d) CROSS JOIN

SECTION B — Short answer

P18 — Elementary facts (objects). State the number of objects in each: (i) "The Book with ISBN '978-1' has the Title 'Databases'." (ii) "The Player with PlayerID 'P9' scored a Goal in the Match with MatchID 'M3'." (iii) "The Seat with Number 'A4' is reserved."

P19 — CSDP Step 1. Write one elementary fact per fact type for this table:

P20 — RMap. An ORM schema has: Student (.Nr) has StudentName (1:1 functional, mandatory on Student); Student enrolled in Unit (.Code) (m:n); Unit has UnitName (1:1 functional). Produce the RMap (PKs underlined, FKs noted).

P21 — Normalisation to 3NF. PK = (OrderID, ProductID).

P22 — DDL. Write a CREATE TABLE for Booking(bookingID, roomID, guestID, status) where bookingID is an auto-incrementing PK, roomID and guestID are mandatory foreign keys (to Room and Guest), and status can only be 'Pending', 'Confirmed' or 'Cancelled'.

P23 — Will it execute? Using the practice-exam case study tables (Event, Ticket, Orders, Customer, Extras), say Executed/Fail and why: (i) INSERT INTO Orders VALUES (6, 2, 80.00); (ii) INSERT INTO Customer VALUES (5, 'Sam', 'Lee', 'h.rich@email.com', '04...'); (iii) INSERT INTO Ticket VALUES (20, 9, 1, 'Adult', NULL);

P24 — Write SQL. Using the case study, return the eventName of every event that has more than 3 tickets sold.

P25 — Read the query. Using the case study, what does this return?

SELECT firstName, lastName FROM Customer
WHERE customerID = (
  SELECT customerID FROM Orders WHERE orderID = 3);

P26 — DCL. Write the commands to (i) create a role clerk_role, (ii) give it SELECT and INSERT on the Orders table, then (iii) revoke INSERT.

P27 — Ethics short answer. A company buys a dataset of users' browsing history (collected via a vague "Terms & Conditions" checkbox) and uses it for targeted ads. Name two ethical concerns and state which is about data collection vs data usage.

Student {Nr, StudentName}
Unit {Code, UnitName}
enrolledIn {Nr, Code}        FK Nr → Student.Nr,  FK Code → Unit.Code

Product  {ProductID, ProductName}
Customer {CustomerID, CustomerName}
Order    {OrderID, CustomerID}            FK CustomerID → Customer
OrderLine{OrderID, ProductID, Qty}        FK OrderID → Order, FK ProductID → Product

CREATE TABLE Booking (
  bookingID INT PRIMARY KEY AUTO_INCREMENT,
  roomID  INT NOT NULL,
  guestID INT NOT NULL,
  status  ENUM('Pending','Confirmed','Cancelled'),
  FOREIGN KEY (roomID)  REFERENCES Room(roomID),
  FOREIGN KEY (guestID) REFERENCES Guest(guestID)
);

SELECT eventName FROM Event
WHERE eventID IN (
  SELECT eventID FROM Ticket
  GROUP BY eventID
  HAVING COUNT(*) > 3);

CREATE ROLE clerk_role;
GRANT SELECT, INSERT ON Orders TO clerk_role;
REVOKE INSERT ON Orders FROM clerk_role;