"Integrity Constraints & Normal Forms" — Practice

Question 1

State Armstrong's three axioms.

Accepted Answer

Reflexivity: if Y is contained in X then X determines Y. Augmentation: if X determines Y then XZ determines YZ. Transitivity: if X determines Y and Y determines Z then X determines Z.

Question 2

Why is any relation with exactly two attributes automatically in BCNF?

Accepted Answer

The only possible non-trivial dependencies are one attribute determining the other. If such a dependency holds, its determinant determines both attributes and is therefore a superkey; if none holds, there is nothing to violate the condition.

Question 3

Give the lossless-join condition for a binary decomposition.

Accepted Answer

The intersection of the two schemas must functionally determine at least one of them, meaning the shared attributes form a key of at least one piece.

Question 4

For R(A,B,C,D) with A to B, B to C and C to D, find the candidate key and the highest normal form.

Accepted Answer

A appears on no right side so it is in every key, and A closure is ABCD, so A is the unique candidate key. B to C and C to D have non-superkey determinants and non-prime right sides, so the relation is in 1NF and 2NF but violates 3NF.

Question 5

Find all candidate keys of R(A,B,C,D,E) with A to BC, CD to E, B to D and E to A.

Accepted Answer

A closure and E closure are both ABCDE, and CD closure and BC closure are also ABCDE, while every single other attribute and the pair BD fall short. The candidate keys are A, E, BC and CD, making every attribute prime.

Question 6

Compute a canonical cover for A to BC, B to C, A to B and AB to C.

Accepted Answer

Splitting right sides and dropping the duplicate gives A to B, A to C, B to C and AB to C. B is redundant in AB to C since A closure already contains C, so it collapses to A to C. Then A to C is itself redundant because A gives B which gives C. The cover is A to B and B to C.

Question 7

Is decomposing R(A,B,C,D) with A to B and C to D into R1(A,B) and R2(C,D) lossless? Justify with a counterexample.

Accepted Answer

No. The intersection is empty and determines nothing, so the join is a Cartesian product. Two original tuples give two rows in each piece and four on rejoining, two of which are spurious.

Question 8

Decompose R(A,B,C) with AB to C and C to B into BCNF and identify the lost dependency.

Accepted Answer

Candidate keys are AB and AC, so all attributes are prime and the relation is in 3NF. C to B violates BCNF since C closure is only BC. Decomposing into R1(C,B) and R2(A,C) is lossless because C determines all of R1, but AB to C is checkable on neither piece and is therefore lost.

Question 9

A relation Course(cid, instructor, textbook) allows any instructor of a course to use any textbook of that course. Give its highest normal form and normalise it.

Accepted Answer

The only key is all three attributes, so no functional dependency is violated and it is in BCNF. However cid multidetermines instructor and multidetermines textbook, and cid is not a superkey, so 4NF is violated. Decompose into CourseInstructor(cid, instructor) and CourseTextbook(cid, textbook), which reduces 12 rows to 7 for a course with 3 instructors and 4 textbooks.

Question 10

Explain why 3NF can always be achieved with both losslessness and dependency preservation while BCNF cannot.

Accepted Answer

The 3NF synthesis algorithm builds one relation per dependency in a canonical cover, so every dependency sits wholly inside some relation and is preserved, and adding a relation containing a candidate key makes it lossless. BCNF must be reached by splitting on violating dependencies, which can separate the attributes of another dependency across pieces with no way to reassemble them.

Practice — "Integrity Constraints & Normal Forms"

Sign in to save attempts to your dashboard and track your accuracy over time

Find a verified tutor

Join a live course

Browse coaching centres

About

Categories

Support

Documentation

Get Study Tips & Updates

Download Our App

Download for iOS

Download for Android

Popular Cities