4.1P Normalisation Page 1 of 2 SIT103/SIT772: Database Fundamentals 4.1P: Normalisation Overview In this task, you will learn how to draw dependency diagram and normalize a given database. Tasks to do...

1 answer below »
normalisation


4.1P Normalisation Page 1 of 2 SIT103/SIT772: Database Fundamentals 4.1P: Normalisation Overview In this task, you will learn how to draw dependency diagram and normalize a given database. Tasks to do 1. Using the INVOICE table structure shown in Table 1 (note that in this table rows are attributes and columns are data records), draw its dependency diagram and identify all dependencies (including all partial and transitive dependencies). You can see that an invoice has more than one product and a product is in more than one invoice. From the business rules, we know that a product is supplied by one vendor only, but a vendor can supply many products. Note that you fist need to determine the Primary Key to identify partial and transitive dependencies. What do you think can be the Primary Key in this table? Note that it is a minimal candidate key that can uniquely identify every records. Table 1. Sample INVOICE Records You can draw Dependency Diagram using any tool you such as Word or Powerpoint. See Class slides for examples. You can find how to draw Dependency Diagram using Lucid Chart and Powerpoint in the following video. https://video.deakin.edu.au/media/t/1_diuebsth 2. Using the initial dependency diagram drawn for Question 1, remove all partial dependencies and draw dependency diagrams of new table structures. Note that your actions may result in multiple tables. 3. Using the tables structures you created for Question 2, remove transitive dependencies in all tables https://video.deakin.edu.au/media/t/1_diuebsth Page 2 of 2 and draw dependency diagrams of all tables. 4. Your might have multiple tables by now. For each table, please check if they satisfy conditions for 1NF, 2NF, and 3NF and justify why they do or do not satisfy. 5. Using the table structures from Question 3, draw a complete Entity Relationship Diagram (ERD) of the database using Crow Foot notations in your choice of professional tool (Visio, Lucidchat or draw.io). NOTE: Because the dependency diagrams cannot show the nature (1:1, 1:M, M:N) of the relationships, the ERD remains crucial to the design effort. Complex design is impossible to produce successfully without some form of modelling such as ERD. Yet, as the preceding decompositions demonstrate, the dependency diagrams are a valuable addition to the designer's toolbox. Normalization is likely to add tables/entities that may not have been considered during the initial (conceptual) modelling process. Therefore, initial ERD may change after normalization. Submission Requirements: Submit one PDF/WORD file showing all step/process of normalization and answering all questions above. Submission Due The due for each task has been stated via its OnTrack task information dashboard.
Answered 2 days AfterAug 09, 2022Deakin University

Answer To: 4.1P Normalisation Page 1 of 2 SIT103/SIT772: Database Fundamentals 4.1P: Normalisation Overview In...

Sairama answered on Aug 11 2022
70 Votes
Assignment 4.1 P Normalization.
Question 1:
As shown in the above. {INV_NUM, PROD_NUM} together fo
rm the primary key of the relation. The following are the functional dependencies present. The above relation schema follows 1NF, as there are no composite or multi valued attributes.
FD1: {INV_NUM, PROD_NUM} -----> {SALE_DATE, PROD_DESCRIPTION, VEND_CODE,                            VEND_NAME, QUANT_SOLD, QUANT_PRICE}
FD2: PROD_NUM -----> {PROD_DESCRIPTION, VEND_CODE, VEND_NAME}
FD3: INV_NUM -----> SALE_DATE
FD4: VEND_CODE ----> VEND_NAME
Partial Dependencies: FD2, FD3
Transitive Dependency: FD4
Question 2:
From the Invoice table, I have created three tables – INV-1, INV-2, INV-3. I have removed the partial dependencies FD2, FD3 from the INVOICE table and created INV-2...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here