ITC556 Database System

HOME LIBRARY MODELING SOLVED ASSIGNMENT

itc556

Insert name

Course and unit

Professor name

Institution

April 18, 2018

  

Home Library

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;

1. Primary Key (PK) – In the Book entity, there is the ISBN which is the primary key that identifies every instance in the book entity as unique. In the Author entity, there is the AuthorID which identifies each entity of the Author uniquely.
2. Foreign Key (FK).–The AuthorID in the Book entity acts a foreign key which relates the Author entity with the Book entity.

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;

1. Foreign Key (FK). – The PublisherID in the Book entity acts a foreign key which relates the Author entity with the Book entity.
2. Primary Key (PK) – In the Publisher entity, there is the PublisherID which identifies each entity of the Publisher entity uniquely. In the Book entity, there is the ISBN which is the primary key that identifies every instance in the book entity as unique.

itc556

The Best Assignment help is one of the best website for assignment help. For more details you may contact us at thebestassignmenthelp@gmail.com

itc556

visit at : TAXATION IN NEW ZEALAND ACCT862 Trimester 1 2018