Section 1
First Normal Form
I gathered all the data from the sales invoice and created an
unnormalized dataset. I determined the primary key that provided
me with a unique value for all my records in this table. I us
...
Section 1
First Normal Form
I gathered all the data from the sales invoice and created an
unnormalized dataset. I determined the primary key that provided
me with a unique value for all my records in this table. I used
composite primary keys because both keys would be able to
uniquely identify all the data in the table. Finally, I removed all the
repeating groups and made the address atomic by creating
separate fields for each one.
Donut ID (PK)
Donut Order ID
(PK)
Customer ID
Order Date
First Name
Last Name
Street Address
Apt #
City
State
Zip Code
Home Phone
Mobile Phone
Other Phone
Quantity
Name
Description
Unit Price
NotesSecond Normal Form
There is a multivalued key, so I looked for partial dependencies
on part of this key. I had a partial dependency on Donut Order ID.
I appropriated everything that was dependent on the Donut Order
ID and generated a second table with them. I did the same for
Donut ID and separated the rest into the Quantity table to provide
a link between the two.
Donut Order Quantity Donut
Donut Order (PK)
Customer ID
Last Name
First Name
Street Address
Apt #
City
State
Zip Code
Home Phone
Mobile Phone
Other Phone
Notes
Order Date
Donut Order
(PK)(FK)
Donut ID (PK)(FK)
Quantity
Donut ID (PK)
Name
Description
Unit PriceThird Normal Form
Lastly, I removed transitive dependencies by creating the
Customer table. There are multiple types of attributes that are
associated with the Customer ID. Customer ID will be the primary
key for this table making it a foreign key in the Donut Order table.
Donut Order Quantity Donut Customer
Donut Order
ID (PK)
Customer ID
(FK)
Notes
Order Date
Donut Order
(PK)(FK)
Donut ID
(PK)(FK)
Quantity
Donut ID (PK)
Name
Description
Unit Price
Customer ID
(PK)
First Name
Last Name
Street Address
Apt #
City
State
Zip Code
Home Phone
Mobile Phone
Other PhoneSection 2
Entity-Relationship (E-R) Diagram
The entities I chose are from the tables created in part A-1(c). The
“Customer” and “Donut” tables are entities that each have a single
primary key and related attributes that describe them. The “Donut
Order” and “Quantity” tables are associative entities that
represents the shared relationship between “Customer” and
“Donut”. The “Quantity” table has a multi-attribute primary key,
“donutOrder_id” and “donut_id”, which also acts as foreign keys.
The relationship between the “Customer” table to “DonutOrder” is
1:M meaning that one customer can place multiple orders and
multiple orders can be tied to one customer. The “Quantity” table
provides a link between the “DonutOrder” table and the “Donut”
table. The relationship between the “DonutOrder” table and
“Quantity” is also a 1:M relationship. The same goes for the
“Donut” table and “Quantity”. This mean that a donut order can
many different quantity of donut types.Section 3
Creating Tables Code
[Show More]
