EDEXCEL BTEC LEVEL 3 90 CREDIT DIPLOMA IN INFORMATION TECHNOLOGY
Assignment 2
Designing a Relational Database
Joel
16/06/2017
, Task 1:
Normalisation:
1NF:
Order Number
Date of Order
Customer ID
Customer Name
Customer Address
Customer Postcode
Customer Contact
Number
Delivery Address
Delivery Postcode
Delivery Date
Delivery Time
Sandwich Code
Sandwich Description
Sandwich Price
Sandwich Quantity
Line Total
Total Price
Order Paid (Tick box)
Shop ID
Shop Location
Staff ID
Staff Name
2NF:
To get to 2NF the headings need to be split up into several tables.
Customer Customer Customer Customer Customer Delivery Delivery Deliver Deliver
ID Name Address Postcode Contact Address Postcode y Date y Time
Number
Sandwich Code Sandwich Description Sandwich Price
Staff ID Shop ID Shop Location
Order Date of Sandwich Line Total Total Price Order Paid
Number Order Quantity (Tick box)
3NF:
To get to 3NF the tables need to be split up to ensure there aren’t any headings that are in a table if
they aren’t dependent on that tables primary key. To do this another table for deliveries needs to be
created.
Joel Page 1
, Customer ID Customer Name Customer Address Customer Postcode Customer Contact
Number
Delivery ID Delivery Address Delivery Postcode Delivery Date Delivery Time
The other tables stay the same since they are already at 3NF. These two tables have now also been
put into 3NF since now everything in the table is dependent on its primary key.
Entity Relationship Diagram:
Data Dictionary:
Customer Table
Field Name Data Type Size Notes
Customer ID AutoNumbe
r
Surname Text 20
Name Text 20
Address Text 50
Postcode Text 10 >LL00\ 0LL;0;_
Contact Number Text 20 \(9999") "0009009;0;_
Delivery Table
Field Name Data Type Size Notes
Delivery ID AutoNumbe
Joel Page 2
Assignment 2
Designing a Relational Database
Joel
16/06/2017
, Task 1:
Normalisation:
1NF:
Order Number
Date of Order
Customer ID
Customer Name
Customer Address
Customer Postcode
Customer Contact
Number
Delivery Address
Delivery Postcode
Delivery Date
Delivery Time
Sandwich Code
Sandwich Description
Sandwich Price
Sandwich Quantity
Line Total
Total Price
Order Paid (Tick box)
Shop ID
Shop Location
Staff ID
Staff Name
2NF:
To get to 2NF the headings need to be split up into several tables.
Customer Customer Customer Customer Customer Delivery Delivery Deliver Deliver
ID Name Address Postcode Contact Address Postcode y Date y Time
Number
Sandwich Code Sandwich Description Sandwich Price
Staff ID Shop ID Shop Location
Order Date of Sandwich Line Total Total Price Order Paid
Number Order Quantity (Tick box)
3NF:
To get to 3NF the tables need to be split up to ensure there aren’t any headings that are in a table if
they aren’t dependent on that tables primary key. To do this another table for deliveries needs to be
created.
Joel Page 1
, Customer ID Customer Name Customer Address Customer Postcode Customer Contact
Number
Delivery ID Delivery Address Delivery Postcode Delivery Date Delivery Time
The other tables stay the same since they are already at 3NF. These two tables have now also been
put into 3NF since now everything in the table is dependent on its primary key.
Entity Relationship Diagram:
Data Dictionary:
Customer Table
Field Name Data Type Size Notes
Customer ID AutoNumbe
r
Surname Text 20
Name Text 20
Address Text 50
Postcode Text 10 >LL00\ 0LL;0;_
Contact Number Text 20 \(9999") "0009009;0;_
Delivery Table
Field Name Data Type Size Notes
Delivery ID AutoNumbe
Joel Page 2
