7.42

Normalize the following schema, with given constraints, to 4NF.

books(accessionno, isbn, title, author, publisher)
users(userid, name, deptid, deptname)

accessionno \(\rightarrow\) isbn
isbn \(\rightarrow\) title
isbn \(\rightarrow\) publisher
isbn \(\twoheadrightarrow\) author

userid \(\rightarrow\) name
userid \(\rightarrow\) deptid
deptid \(\rightarrow\) deptname

---

Apply the algorithm given on Figure 7.16.

First let’s consider the relation schema, books(accessionno, isbn, title, author, publisher)

Since isbn \(\twoheadrightarrow\) author is a nontrivial multivalued dependency that holds on books and isbn is not a superkey, we can use this dependency to decompose books into

b1(isbn, author)
b2(accessionno, isbn, title, publisher)

Since the multivalued dependency isbn \(\twoheadrightarrow\) title, publisher hold on the relation schema b2 and nontrivial, we can use it to decompose b2 into:-

b2.1(accessionno, isbn)
b2.2(isbn, title, publisher)

Thus the relation schema books(accessionno, isbn, title, author, publisher) is decomposed into

b1(isbn, author)
b2.1(accessionno, isbn)
b2.2(isbn, title, publisher)

Candidate key for b1 would be (isbn, author), while candidate key for b2.1 is accessionno. Candidate key for b2.2 would be isbn.

Considering the other relation users(userid, name, deptid, deptname) , we see that the multivalued dependency deptid \(\twoheadrightarrow\) deptname holds. Since this is a nontrivial multivalued dependency and deptid is not a superkey, we can use it to decompose the users relation into:-

u1(deptid, deptname)
u2(userid, name, deptid)

deptid is a candidate key for u1 while userid is a candidate key for u2.