C170 Performance Assessment

9/11/24, 2:05 C170 Performance
PM Assessment




SECTION A.
1.
Second Normal Form (2NF)

BAGEL ORDER BAGEL ORDER LINE ITEM BAGEL
PK Bagel Order ID PK / FK Bagel Order ID PK Bagel ID
Order Date 1:M PK / FK Bagel ID M:1 Bagel Name
First Name Bagel Quantity Bagel Price
Last Name Bagel Description
Address 1
Address 2
City
State
Zip
Mobile Phone
Delivery Fee
Special Notes

1c.
I organized the attributes by grouping them through similarity. All the attributes involved with bagels,
more specifically the specific type of bagel and its specific attributes were grouped together in the Bagel
table. Anything to that dealt with the specifics of the order, such as customer information, order ID, and
date of order was grouped together in the Bagel Order table. Lastly the Bagel Order Line Item table is
used as a junction table between the other two tables to link order to the correct bagels.

The cardinality between Bagel Order and Bagel Order Line Item table was declared as one-to-many
because for every Bagel Order, there could be multiple bagels being ordered in that single order and
each type of bagel being ordered would be given its own row in the Bagel Order Line Item table.
Cardinality between Bagel Order Line Item and Bagel was found to be many-to-one because the bagel
order line item could have several different bagels for one order and every one of those bagels in the
order is connected to one entry in the bagel table.

2.
Third Normal Form (3NF)

Bagel Order BAGEL ORDER LINE ITEM BAGEL
PK Bagel Order ID PK / FK Bagel Order ID PK Bagel ID
FK Customer ID 1:M PK / FK Bagel ID M:1 Bagel Name
Order Date Bagel Quantity Bagel Price
Special Notes Bagel Description
Delivery Fee
M:1
Customer
PK Customer
ID First
Name




about:blan 1/
k 8

, 9/11/24, 2:05 C170 Performance
PM Assessment




Last Name
Address 1
Address 2
City
State
Zip
Mobile Phone
2e.
To further normalize the previous table, more tables were necessary. The Bagel Order table was further
broken down and the Customer table was needed to do so. The Bagel Order table now only contains
information pertinent to that specific order, with the Customer table keeping track of customer
information This is great if they are reoccurring customers as they will have an assigned Customer ID
primary key and that can be used in the Bagel Order form as a foreign key instead of listing their
information every time they order.

There was one extra cardinality introduced with the third normal form. This cardinality is found between
the Bagel Order and Customer tables. The cardinality between these two tables is many-to-one because
there can be many orders linked to the same customer. If the customer is a regular, they could have
thousands of orders, but their information will be the same, unless they update their information, but
even then they will still have the same Customer ID and only the other attributes will be updated.

3.
Bagel Order BAGEL ORDER LINE ITEM
PK bagel_order_id INT PK / FK bagel_order_id INT
FK customer_id INT 1:M PK / FK bagel_id CHAR(2)
order_date TIMESTAMP bagel_quantity INT
special_notes VARCHAR(100) M:1
delivery_fee NUM(5,2) BAGEL
M:1 PK bagel_id CHAR(2)
Customer bagel_name VARCHAR(40)
PK customer_id INT bagel_price NUMERIC(3,2)
first_name VARCHAR(20) bagel_description VARCHAR(40)
last_name VARCHAR(20)
address_1 VARCHAR(20)
address_2 VARCHAR(20)
city VARCHAR(20)
state CHAR(2)
zip CHAR(10)
mobile_phone CHAR(10)




about:blan 2/
k 8