WGU C170 PROJECT WITH COMPLETE SOLUTION
1NF 2NF
DONUT ORDER ID
DONUT ID DONUT ORDER
Highlighted fields will form a DONUT ID (PK)
DATE ID(PK)
Composite primary key. City DONUT NAME
CUSTOMER ID DATE
State and Zip were separated DONUT DESCRIPTION
LAST NAME CUSTOMER ID
UNIT PRICE
FIRST NAME And are now atomic LAST NAME
STREET ADDRESS FIRST NAME
CITY STREET ADDRESS
STATE APT DONUT ORDER ID (PK)
ZIP CODE CITY (FK)
HOME PHONE STATE QUANTITY
MOBILE PHONE ZIP CODE DONUT ID (PK)(FK)
OTHER PHONE HOME PHONE
DESCRIPTION MOBILE PHONE
QTY OTHER PHONE
UNIT PRICE NOTES
NOTES No duplicate data, each of the above tables has a Primary Key, and a key
DONUT NAME (foreign) that can refer to another table in the group.
3NF
CUSTOMER ID (PK)
DATE
LAST NAME
CUSTOMER ID (FK)
FIRST NAME
NOTES
STREET ADDRESS DONUT ORDER ID
DONUT ORDER ID
APT (PK)(FK)
(PK)
CITY No QUANTITY transitive dependency. New table has 2
STATE of DONUT ID (PK)(FK) the PKs from the other tables as Foreign
ZIP CODE DONUT ID (PK)
Keys.
HOME PHONE DONUT NAME
DONUT Each table has a primary key which can refer to another one of the
MOBILE PHONE
DESCRIPTION tables in this group.
OTHER PHONE
UNIT PRICE
ORDER TABLE
DATE DATETIME
1:M CUSTOMERID INT (fk)
DONUT TABLE
DONUT_ORDER_ID
DONUT_ID INT (pk) INT (pk)
One Customer can place many Orders
DONUT_NAMEQUANTITY
NOTES TABLE
VARCHAR
VARCHAR (255)
(100)
ER DIAGRAM Customer table has single pk
QUANTITY VARCHAR
DONUT_DESCRIPTION INT (255)
DONUT_ORDER_ID
UNIT_PRICE DECIMAL (10,2) INT (pk) (fk)
DONUT_ID INT (pk)(fk)
, One order can have many
different quantities
1:M
CUSTOMER TABLE
CUSTOMER_ID INT (pk)
LAST_NAME VARCHAR (30)
FIRST_NAME VARCHAR (30)
STREET_ADDRESS VARCHAR (100)
APT INT,
CITY VARCHAR (30)
STATE VARCHAR (30)
ZIP_CODE INT
HOME_PHONE VARCHAR (20)
MOBILE_PHONE VARCHAR (20) One donut order can hold
OTHER_PHONE VARCHAR (20) many different quantities
of donut types of donut types
1:M
create table customers(
customer_id INT,
1NF 2NF
DONUT ORDER ID
DONUT ID DONUT ORDER
Highlighted fields will form a DONUT ID (PK)
DATE ID(PK)
Composite primary key. City DONUT NAME
CUSTOMER ID DATE
State and Zip were separated DONUT DESCRIPTION
LAST NAME CUSTOMER ID
UNIT PRICE
FIRST NAME And are now atomic LAST NAME
STREET ADDRESS FIRST NAME
CITY STREET ADDRESS
STATE APT DONUT ORDER ID (PK)
ZIP CODE CITY (FK)
HOME PHONE STATE QUANTITY
MOBILE PHONE ZIP CODE DONUT ID (PK)(FK)
OTHER PHONE HOME PHONE
DESCRIPTION MOBILE PHONE
QTY OTHER PHONE
UNIT PRICE NOTES
NOTES No duplicate data, each of the above tables has a Primary Key, and a key
DONUT NAME (foreign) that can refer to another table in the group.
3NF
CUSTOMER ID (PK)
DATE
LAST NAME
CUSTOMER ID (FK)
FIRST NAME
NOTES
STREET ADDRESS DONUT ORDER ID
DONUT ORDER ID
APT (PK)(FK)
(PK)
CITY No QUANTITY transitive dependency. New table has 2
STATE of DONUT ID (PK)(FK) the PKs from the other tables as Foreign
ZIP CODE DONUT ID (PK)
Keys.
HOME PHONE DONUT NAME
DONUT Each table has a primary key which can refer to another one of the
MOBILE PHONE
DESCRIPTION tables in this group.
OTHER PHONE
UNIT PRICE
ORDER TABLE
DATE DATETIME
1:M CUSTOMERID INT (fk)
DONUT TABLE
DONUT_ORDER_ID
DONUT_ID INT (pk) INT (pk)
One Customer can place many Orders
DONUT_NAMEQUANTITY
NOTES TABLE
VARCHAR
VARCHAR (255)
(100)
ER DIAGRAM Customer table has single pk
QUANTITY VARCHAR
DONUT_DESCRIPTION INT (255)
DONUT_ORDER_ID
UNIT_PRICE DECIMAL (10,2) INT (pk) (fk)
DONUT_ID INT (pk)(fk)
, One order can have many
different quantities
1:M
CUSTOMER TABLE
CUSTOMER_ID INT (pk)
LAST_NAME VARCHAR (30)
FIRST_NAME VARCHAR (30)
STREET_ADDRESS VARCHAR (100)
APT INT,
CITY VARCHAR (30)
STATE VARCHAR (30)
ZIP_CODE INT
HOME_PHONE VARCHAR (20)
MOBILE_PHONE VARCHAR (20) One donut order can hold
OTHER_PHONE VARCHAR (20) many different quantities
of donut types of donut types
1:M
create table customers(
customer_id INT,
