ERD - Management of Information Systems - Data Base Assignment Answers

November 06, 2018
Author : Julia Miles

Solution Code: 1DDH

Question: Normalization, Functional Dependency and ER diagram

This assignment is related to ”Data Base” and experts at My Assignment Services AU successfully delivered HD quality work within the given deadline.

Data Base Assignment

Normalisation

  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.

These assignments are solved by our professional Database Experts at My Assignment Services AU and the solution are high quality of work as well as 100% plagiarism free. The assignment solution was delivered within 2-3 Days.

Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.

Solution: Normalization, Functional Dependency and ER diagram

  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,-

Find Solution for Database assignment by dropping us a mail at help@myassignmentservices.com.au along with the question’s URL. Get in Contact with our experts at My Assignment Services AU and get the solution as per your specification & University requirement.

RELATED SOLUTIONS

Order Now

Request Callback

Tap to ChatGet instant assignment help

Get 500 Words FREE