ERD - Management of Information Systems - Data Base Assignment Answers

Data Base Assignment

1. Using the table and data given below, normalise the table to 3NF.
2. Use correct dependency diagram/s to show each normalisation level.
3. Create an ERD to show the resultant tables at the 3NF level with all the attributes, Primary/Foreign keys, relationships, etc.

1. Normalization: AND
2. Dependency Diagram:

Normalization technique allows reducing data redundancy in the tables as well as eliminates the data anomalies like insert, update and delete anomalies. Normalization naturally works from stages, 1 NF to 4 NF.

In case of 1 NF (Normal Form), all attributes of a table must be dependent on the PK (Primary Key). Same data should not be stored in more than one table.

The functional dependency diagram of the given table is,-

• Here, Book_Title, Bk_Author, are dependent on Book_ID (a portion of primary key).
• Again, Date_Published, Publisher and Publ_Location are dependent on Book_ID (a portion of primary key).
• Again, D_Borrowed, D_Returned are dependent on Borrower_Num (a portion of primary key).

The functional dependency diagram for three cases are,-

So, the 2 NF Tables from that above 1 NF Table are shown as below,-

Table 1: BOOK_Info

Table 2: Publisher_Info

Table 3: Borrower_dtls

The above 2NF Tables can be further decomposed into 3NF Tables if and only if two non-keys attributes are dependent on each other in a Table.

The Functional Dependency Diagram for Table 2: Publisher_Info is as follows,-

Here, Book_ID is the primary key, the non-key attributes like Date_Published, Publisher are dependent on the Primary key. Again, two non-key attributes like Publisher and Publ_Location are dependent on each other. So, the relation is further decomposed into 3NF as follows,-.

Table 2.1: Publisher_Info1

Table 2.2: Publisher_Info2

Ques 3)The Screenshot of ERD for the above relation is shown below,-

Fig: ERD drawn on MS-Visio 2007 software

From the ERD, the Entities and their Attributes, PK (Primary Key), FK(Foreign Key) with relationship details are discussed as below,-