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.