Course and unit
April 18, 2018
Question 1: Functional dependency
The following represents the functional dependency between the attributes identified in the table provided in the diagram.
Figure 1: Functional Dependency Diagram
Question 2: Decomposing into 3NF Relations
The AuthorID and PublisherID attribute were introduced to support referential constraints so as to relate the three entities which are Author, Book and Publisher. Such a restructuring resulted to 3NF relations.
Figure 2: 3NF Relations
Question 3: Relational Schemas for 3N Relations and Referential integrity constraints.
3N Relational Schema 1: (book à author relation)
This relation is a one to many relationship (1:N). This means that there can be several instances of the Author entity in the Book entity. This relationship represents the relationship that exists between the book and author where one author can write one or many books with different ISBN.
Fig 3: 3N Relational Schema 1: (book à author relation)
Referential integrity constraints
The referential integrity constraints that exists between the Book and the Author are as follows;
3N Relational Schema 2: (book à publisher relation)
In this relational form, it can be depicted that there is one to many relationship (1:N). This means that there can be several instances of the Publisher entity in the Book entity. This relationship represents the relationship that exists between the Publisher and Book where one Publisher can publish one or many books with different ISBN value.
Figure 4: book à publisher relation
Referential Integrity constraints
The referential constraints between the Book and the Publisher are as follows;