Normalizing a Schema into Fourth Normal Form

Question

For a homework assignment for my databases class I am struggling to understand how to normalize this schema into fourth normal form.

Here is what I have to do:

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

Books(accessionno, isbn, title, author, publisher)
Users(userID, name, deptID, deptname)

Accesssionno -> isbn
Isbn -> title
Userid -> name
Userid -> deptid
Deptid -> deptname

And here is my attempt at it:

Books(accessionno, isbn)
Books2(accessionno, title)
Books3(accessionno, author, publisher)

Users(userID, name)
Users2(userID, deptID)
Users3(userID, deptName)

What I am getting confused about is the isbn -> title and Deptid -> deptname

I am not sure how to handle these two functional dependencies, can anyone please provide some assistance? I have looked up examples onlnie but am struggling to relate it to my specific problem. Thank you for your help, it is greatly appreciated!

EDIT: After taking a look at more examples and reading more material, this is my second attempt at the solution. Any suggestions?

Books(accessionno, isbn, title, author, publisher)
Accesssionno -> isbn
Isbn -> title

Normalized:
Books1(accessionno, isbn)
Books2(accessionno, isbn, title)
Books3(accessionno, author, publisher)

Users(userID, name, deptID, deptname)
Userid -> name
Userid -> deptid
Deptid -> deptname

Normalized:
Users1(userID, name)
Users2(userID, deptID)
Users3(userID, deptID, deptName)

Answer 1

First, you have two different relation schema, with no common attributes, so it is correct to normalize them separately.

So, starting from the first relation:

Books(AccessionNo, Isbn, Title, Author, Publisher)
AccessionNo → Isbn
Isbn → Title

the problem is that there are no dependency specified with the attributes Author and Publisher, while cleary we can interpret the relation as describing books, and in this case there should be also other two dependencies:

Isbn → Author
Isbn → Publisher

or, equivalenty, one could write that the relation has the two dependencies:

Books(AccessionNo, Isbn, Title, Author, Publisher)
AccessionNo → Isbn
Isbn → Title, Author, Publisher

With this “correction”, you can bring the relation in Boyce-Codd normal form by producing the following subschemas:

R1 < (Isbn, Author, Publisher, Title),
{ Isbn → Author
  Isbn → Publisher
  Isbn → Title }>

R2 < (AccessionNo Isbn),
{ AccessionNo → Isbn } >

where the first has the only key Isbn while the second one has the only key AccessionNo.

If, on the other hand, the schema should have only the two functional dependencies mentioned, the decomposition in BCNF would be more complicated and not very significant:

R1 < (Isbn, Title) ,
{ Isbn → Title } >

R2 < (AccessionNo, Isbn) ,
{ AccessionNo → Isbn } >

R3 < (AccessionNo, Author, Publisher) ,
{ } >

in which the first relation has key Isbn, the second has key AccessionNo and the third has key (AccessionNo, Author, Publisher).

For the second relation,

Users(UserID, Name, DeptID, DeptName)    
UserID → Name
UserID → DeptID
DeptID → DeptName

the dependencies make sense, since the schema describes a relation with users and their departments, where each user belongs to a single department. In this case the Boyce-Codd Normal Form is given by the following decomposition:

R1 < (UserID, Name, DeptID) ,
{ UserID → Name
  UserID → DeptID } >

R2 < (DeptID, DeptName) ,
{ DeptID → DeptName } >

in which the first relation has key UserID (describing the users), and the second relation has key DeptID (describing the departments).

As final note: all the decompositions produced are in Boyce-Codd Normal Form, and for this reason they are automatically already in Third Normal Form. They are also in Fourth Normal Form because there are no multivalued dependencies, that would require a special treatment.