CSG1207/CSI5135 Systems and Database Design Proof Reading Services

CSG1207/CSI5135 Systems and Database Design Proof Reading Services

Task 1 – Normalization 1

According to the table given in the task, following are the relation sets in their different normalized forms:

0NF

R1:(CatID#, CatName{PieceID#, Title, Artist, AreaID#, AreaName})

0NF TO 1NF

Assumptions

The primary key CatID# needs to be mentioned as a foreign key in R12.

R1:(CatID#, CatName{PieceID#, Title, Artist, AreaID#, AreaName})

R1:(CatID#, CatName{PieceID#, Title, Artist, AreaID#, AreaName})

R11: (CatID#, CatName)

R12: (PieceID#,CatID#, Title, Artist, AreaID#, AreaName)

Final 1NF

R11: (CatID#, CatName)

R12: (PieceID#, CatID#, Title, Artist, AreaID#, AreaName)

1NF TO 2NF

There is no partial dependency, therefore it is already in 2NF.

Final 2NF

R11: (CatID#, CatName)

R12: (PieceID#, CatID#, Title, Artist, AreaID#, AreaName)

2NF TO 3NF

R11: (CatID#, CatName)

Removing transitive dependency from R12

R12: (PieceID#, CatID#, Title, Artist, AreaID#, AreaName)

R121: (PieceID#,CatID#,AreaID#, Title, Artist)

R122: (AreaID#,AreaName)

Final 3NF

R11: (CatID#, CatName)

R121: (PieceID#,CatID#,AreaID#, Title, Artist)

R122: (AreaID#,AreaName)

Resultant Datasets

Category: (CatID#, CatName)

Piece: (PieceID#,CatID#,AreaID#, Title, Artist)

Area: (AreaID#,AreaName)

Physical ERD

Task 2 – Normalization 2

According to the invoice given in the task, following are the relation sets in their different normalized forms:

Assumptions

1. The invoice header including ABN, Tel, Fax are the same for the company and they need not be stored in the database.

2. The subtotal for each item can be calculated from the Cost attribute and the Qty attribute of the item.

3. The Total Cost can be calculated from the Subtotal and is equivalent to it. therefore, it needs not be stored.

4. The Balance Outstanding can be calculated from the Amount
5. Paid and the TotalCost and therefore needs not be stored.

0NF

R1:(Invoice#, Date, Staff#, Salesperson, {ItemCode#, Item, CatID#, Category, Cost, Qty}, AmountPaid)

0NF TO 1NF

R1:(Invoice#, Date, Staff#, Salesperson, {ItemCode#, Item, CatID#, Category, Cost, Qty}, AmountPaid)

R1:(Invoice#, Date, Staff#, Salesperson, {ItemCode#, Item, CatID#, Category, Cost, Qty}, AmountPaid)

R11: (Invoice#, Date, Staff#, Salesperson, AmountPaid)

R12: (ItemCode#, Invoice#, Item, CatID#, Category, Cost, Qty)

Final 1NF

R11: (Invoice#, Date, Staff#, Salesperson, AmountPaid)

R12: (ItemCode#, Invoice#, Item, CatID#, Category, Cost, Qty)

1NF TO 2NF

R11: (Invoice#, Date, Staff#, Salesperson, AmountPaid)

R12: (ItemCode#, Invoice#, Item, CatID#, Category, Cost, Qty)

R121: (ItemCode#, Invoice#, Qty)

R122: (ItemCode#, Item, CatID#, Category, Cost)

Final 2NF

R11: (Invoice#, Date, Staff#, Salesperson, AmountPaid)

R121: (ItemCode#, Invoice#, Qty)

R122: (ItemCode#, Item, CatID#, Category, Cost)

2NF TO 3NF

R11: (Invoice#, Date, Staff#, Salesperson, AmountPaid)

R111: (Invoice#, Date, AmountPaid, Staff#)

R112: (Staff#, Salesperson)

R121: (ItemCode#, Invoice#, Qty)

R122: (ItemCode#, Item, CatID#, Category, Cost)

R1221:(ItemCode#, Item, Cost, CatID#)

R1222:(CatID#, Category)

Final 3NF

R111: (Invoice#, Date, AmountPaid, Staff#)

R112: (Staff#, Salesperson)

R121: (ItemCode#, Invoice#, Qty)

R1221:(ItemCode#, Item, Cost, CatID#)

R1222:(CatID#, Category)

Resultant datasets

Invoice: (Invoice#, Date, AmountPaid, Staff#)

Staff: (Staff#, Salesperson)

InvoiceItem: (ItemCode#, Invoice#, Qty)

Item:(ItemCode#, Item, Cost, CatID#)

Category:(CatID#, Category)

Physical ERD

Task 3 – Entity-Relationship Modelling 1

Entities

1. Customer (CusID#, First_Name, Last_Name, Cus_Number, Cus_Email, Cus_Password, Referrer_CusID#)

2. Category (CatID#, CategoryName)

3. Item (Item_Number#, Item_name, Item_Description, Price)

4. ItemList (Item_Number#, CatID#)

5. InvoiceItem (InvoiceNo, Ordered_Item_Number#, Quantity)

6. Invoice (InvoiceNo, DateTime, Delivery_Address, Billing_Address, CusID#)

Assumptions

1. The referrer of a customer will be a customer himself. Therefore, the Referrer_CusID# is equivalent to the CusID# and therefore a recursive or self-foreign key is established.

2. One category can have many items.

3. One item can be present in many categories.

4. One customer can have many invoices.

5. One item can be present in many invoices.

6. One invoice has at least one item. 

7. Ordered_Item_Number# is equivalent to the Item_Number#.

8. InvoiceItem contains the ordered items and their quantities and one invoice can have many invoice items.

Task 4 – Entity-Relationship Modelling 2

Entities

1. Customer (CusID#, First_Name, Last_Name, Cus_Number, Cus_Email, Cus_Password, Referrer_CusID#)

2. Category (CatID#, CategoryName)

3. Item (Item_Number#, Item_name, Item_Description, Price)

4. ItemList (Item_Number#, CatID#)

5. InvoiceItem (InvoiceNo, Ordered_Item_Number#, Quantity)

6. Invoice (InvoiceNo, DateTime, Delivery_AddressID#, Billing_AddressID#, CusID#)

7. Newsletter (Newsletter_ID#, CusID#, Interested_CatID#)

8. Addresses (AddressID#, Address_Name, CusID#)

Assumptions

1. The referrer of a customer will be a customer himself. Therefore, the Referrer_CusID# is equivalent to the CusID# and therefore a recursive or self-foreign key is established.

2. One category can have many items.

3. One item can be present in many categories.

4. One customer can have many invoices.

5. One item can be present in many invoices.

6. One invoice has at least one item. 

7. Ordered_Item_Number# is equivalent to the Item_Number#.

8. InvoiceItem contains the ordered items and their quantities and one invoice can have many invoice items.

9. One customer can have multiple addresses.

10. The Delivery_AddressID# and the Billing_AddressID# are equivalent to the AddressID# and the customer can choose them from the Addresses table.

11. The Newsletter contains the customer id and the interested category.

12. One customer will have one newsletter.

13. One or more categories can be mentioned in the newsletter.