15 questions · 26 marks · 45 minutes. Negative marking on MCQ only: −1/3 mark for a wrong 1-mark MCQ, −2/3 for a wrong 2-mark MCQ. No negative marking for MSQ or NAT; MSQ has no partial credit.
Q1 · MCQ · 1 mark(s) — Anomalies
Deleting the last enrolment of a course also deletes the course's title from the database. This is:
- (A) an update anomaly
- (B) a lossy join
- (C) an insertion anomaly
- (D) a deletion anomaly
Q2 · MSQ · 2 mark(s) — FD on instance
Instance of R(A, B, C): (a, 1, x), (b, 1, y), (a, 2, x), (c, 2, z). Which FDs hold in this instance?
- (A) A → C
- (B) AB → C
- (C) C → A
- (D) B → C
Q3 · MCQ · 1 mark(s) — Axioms
From AB → C and C → D, which cannot be derived?
- (A) AB → D
- (B) ABE → CD
- (C) A → D
- (D) ABC → D
Q4 · NAT · 2 mark(s) — Keys
R(A, B, C, D, E), F = {A → B, BC → D, D → A}. How many candidate keys?
Numerical answer: ________
Q5 · NAT · 2 mark(s) — Superkeys
R(A, B, C, D, E, F), F = {A → B, B → C, C → A, D → E}. How many superkeys?
Numerical answer: ________
Q6 · MCQ · 2 mark(s) — Equivalence
F = {A → B, B → C, AC → D}, G = {A → BCD, B → C}. Is F ≡ G?
- (A) Yes
- (B) No, G does not imply AC → D
- (C) No, F does not imply A → D
- (D) No, G does not imply B → C
Q7 · NAT · 2 mark(s) — Minimal cover
How many FDs are in a minimal cover of {A → BC, CD → E, B → D, E → A}?
Numerical answer: ________
Q8 · MCQ · 2 mark(s) — Lossless
R(A, B, C, D, E), F = {AB → C, C → D, D → E}, decomposed into {ABC, CDE}. It is:
- (A) lossless, because C → CDE
- (B) lossy, because C is not a key of R
- (C) lossless, because AB is a key
- (D) lossy, because E is not in ABC
Q9 · MCQ · 2 mark(s) — Preservation
R(A, B, C, D), F = {A → B, B → C, C → D}, pieces {AB, AC, AD}. Which is true?
- (A) Lossless and preserving
- (B) Lossless, not preserving
- (C) Lossy, preserving
- (D) Lossy, not preserving
Q10 · MCQ · 1 mark(s) — 2NF
Which relation is guaranteed to be in 2NF?
- (A) Every relation with transitive dependencies
- (B) Every relation whose candidate keys are all single attributes
- (C) Every relation with NULLs
- (D) Every relation with a composite primary key
Q11 · MCQ · 2 mark(s) — Highest NF
R(A, B, C, D) with F = {AB → C, C → D}. Highest NF?
- (A) 3NF
- (B) BCNF
- (C) 2NF
- (D) 1NF
Q12 · MCQ · 2 mark(s) — Highest NF
R(A, B, C, D) with F = {A → B, B → C, C → D, D → A}. Highest NF?
- (A) 2NF
- (B) 3NF
- (C) BCNF
- (D) 1NF
Q13 · NAT · 2 mark(s) — 3NF synthesis
How many relations does 3NF synthesis produce for R(A, B, C, D, E, F) with F = {AB → C, C → D, E → F}?
Numerical answer: ________
Q14 · MCQ · 2 mark(s) — BCNF
Which statement about R(A, B, C) with F = {AB → C, C → B} is true?
- (A) It is in BCNF
- (B) Every lossless BCNF decomposition of it loses AB → C
- (C) It is not in 3NF
- (D) It has a single candidate key
Q15 · MCQ · 1 mark(s) — MVD
R(student, sport, language): a student's sports and languages are independent. The relation violates:
- (A) 1NF
- (B) 2NF
- (C) 4NF
- (D) BCNF
✓Answer Vault
Reveal answers when you’re ready
Q1 Answer: (D) · 1 mark(s)
Losing other facts with a delete.
- (A) is wrong: No.
- (B) is wrong: No.
- (C) is wrong: No.
Re-read: Lesson 4.1.1 — Why normalize: redundancy and insertion/deletion/update anomalies
Q2 Answer: (A) (B) (C) · 2 mark(s)
A → C: a → x, x. C → A: x → a, a. B → C: 1 → x, y fails.
- (D) is wrong: B = 1 gives x and y.
Re-read: Lesson 4.2.1 — FD definition; trivial vs non-trivial; checking whether an FD holds on an instance
Q3 Answer: (C) · 1 mark(s)
A alone does not give C.
- (A) is wrong: Transitivity.
- (B) is wrong: Union + augmentation.
- (D) is wrong: Augmentation of C → D.
Re-read: Lesson 4.2.2 — Armstrong's axioms and derived rules
Q4 Answer: 3 · 2 mark(s)
C and E never on the right. CE⁺ = CE. ACE, BCE, CDE.
Re-read: Lesson 4.2.3 — Attribute closure; all candidate keys; prime vs non-prime; counting superkeys
Q5 Answer: 14 · 2 mark(s)
Keys ADF, BDF, CDF: must contain D and F and at least one of A, B, C; E free: 7 × 2.
Re-read: Lesson 4.2.3 — Attribute closure; all candidate keys; prime vs non-prime; counting superkeys
Q6 Answer: (A) · 2 mark(s)
Under F: A⁺ = ABCD. Under G: A⁺ = ABCD, so AC → D holds; B → C is in G.
- (B) is wrong: AC⁺ under G = ABCD.
- (C) is wrong: A⁺ under F = ABCD.
- (D) is wrong: It is in G.
Re-read: Lesson 4.2.4 — Closure of an FD set; membership test; equivalence of FD sets
Q7 Answer: 5 · 2 mark(s)
Split: A → B, A → C, CD → E, B → D, E → A. No extraneous attributes, none redundant.
Re-read: Lesson 4.2.5 — Minimal (canonical) cover
Q8 Answer: (A) · 2 mark(s)
Common C; C⁺ = CDE.
- (B) is wrong: Not the test.
- (C) is wrong: Wrong reason.
- (D) is wrong: Irrelevant.
Re-read: Lesson 4.3.1 — Lossless-join decomposition: binary test and chase (table) method
Q9 Answer: (B) · 2 mark(s)
Every piece contains the key A, so the chase makes the first row all a's: lossless. Test B → C: B⁺ = BCD; the pieces AB, AC, AD each give back only B or nothing, so C is never reached: lost (C → D too).
- (A) is wrong: B → C and C → D are lost.
- (C) is wrong: It is lossless.
- (D) is wrong: It is lossless.
Re-read: Lesson 4.3.2 — Dependency preservation
Q10 Answer: (B) · 1 mark(s)
No proper subset of a single attribute can cause a partial dependency.
- (A) is wrong: Unrelated to 2NF directly.
- (C) is wrong: Unrelated.
- (D) is wrong: Composite keys allow partial dependencies.
Re-read: Lesson 4.4.2 — 2NF (partial dependency)
Q11 Answer: (C) · 2 mark(s)
Key AB. C → D: C not part of the key, D non-prime → transitive.
- (A) is wrong: C → D violates 3NF.
- (B) is wrong: No.
- (D) is wrong: No partial dependency.
Re-read: Lesson 4.4.5 — Drill: highest normal form of a given relation
Q12 Answer: (C) · 2 mark(s)
Every attribute determines all others: A, B, C, D are all keys.
- (A) is wrong: Higher.
- (B) is wrong: Higher.
- (D) is wrong: Higher.
Re-read: Lesson 4.4.5 — Drill: highest normal form of a given relation
Q13 Answer: 4 · 2 mark(s)
Tables ABC, CD, EF; key ABE is not inside any → add ABE.
Re-read: Lesson 4.4.6 — Algorithms: 3NF synthesis, BCNF decomposition
Q14 Answer: (B) · 2 mark(s)
The only BCNF split is {AC, BC} (or worse), and A, B are never together.
- (A) is wrong: C → B violates BCNF.
- (C) is wrong: B is prime: 3NF.
- (D) is wrong: Keys AB and AC.
Re-read: Lesson 4.4.4 — BCNF; 3NF vs BCNF trade-off
Q15 Answer: (C) · 1 mark(s)
Non-trivial MVD with a non-superkey left side; no FDs, so BCNF holds.
- (A) is wrong: Atomic.
- (B) is wrong: All attributes prime.
- (D) is wrong: No non-trivial FDs.
Re-read: Lesson 4.4.7 — Multivalued dependencies and 4NF