SIT10
3/SIT772
:
Database
Fundamentals
4
.1
P
:
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 rule
s
, 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.
Submit one PDF
/WORD
file
showing all step/process of normalization and
answering all question
s
above
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.