Wgu C170 Vht2 Task 1 Normalization And Database Design

WGU C170 VHT2 TASK 1 NORMALIZATION AND DATABASE DESIGN VHT2 TASK 1: NORMALIZATION AND DATABASE DESIGN Course: Data Management – Applications – C170 John Smith Western Governors University Student ID # ######## Nora’s Bagel Bin Database Blueprints First Normal Form (1NF) BAGEL ORDER PK Bagel Order ID PK Bagel ID Order Date First Name Last Name Address 1 Address 2 City State Zip Mobile Phone Delivery Fee Bagel Name Bagel Description Bagel Price Bagel Quantity Special Notes A. Nora’s Bagel Bin Database 1.a., 1.b. and 1.c. Nora’s Bagel Bin Database Blueprints (continued) 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 Description Last Name Bagel Price Address 1 Address 2 Each bagel order line has at most one bagel. Each bagel has many bagel order line items. City State Zip Mobile Phone Each bagel order has many bagel order line items. Each bagel order line item has at most one bagel order. Delivery Fee Special Notes 2.a., 2.b. and 2.c. 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 Description Delivery Fee Bagel Price Special Notes M:1 CUSTOMER Each bagel order line has at most one bagel. Each bagel has many bagel PK Customer ID First Name Last Name Address 1 Address 2 City State Zip Mobile Phone order line items. Each bagel order has many bagel order line items. Each bagel order line item has at most one bagel order. Each bagel order has at most one customer. Each customer has many bagel orders. 3.a., 3.b. and 3.c. Nora’s Bagel Bin Database Blueprints (continued) Final Physical Database Model BAGEL ORDER PK bagel_order_id INT FK customer_id INT order_date TIMESTAMP delivery_fee NUMERIC(5,2) special_notes VARCHAR(50) M:1 1:M BAGEL ORDER LINE ITEM PK / FK bagel_order_id INT PK / FK bagel_id CHAR(2) bagel_quantity INT M:1 BAGEL PK bagel_id bagel_name bagel_description bagel_price CHAR(2) VARCHAR(50) VARCHAR(50) NUMERIC(5,2) CUSTOMER PK customer_id INT first_name VARCHAR(30) last_name VARCHAR(30) address_1 VARCHAR(50) address_2 VARCHAR(50) City VARCHAR(30) State CHAR(2) Zip INT mobile_phone INT Each bagel order line has at most one bagel. Each bagel has many bagel order line items. Each bagel order has many bagel order line items. Each bagel order line item has at most one bagel order. Each bagel order has at most one customer. Each customer has many bagel orders. B. Jaunty Coffee Co. ERD 1.a. CREATE TABLE COFFEE_SHOP( shop_id INTEGER UNSIGNED, shop_name VARCHAR(50), city VARCHAR(50), state CHAR(2), PRIMARY KEY (shop_id) ); CREATE TABLE EMPLOYEE( employee_id INTEGER UNSIGNED, first_name VARCHAR(30), last_name VARCHAR(30), hire_date DATE, job_title VARCHAR(30), shop_id INTEGER UNSIGNED, PRIMARY KEY (employee_id), FOREIGN KEY (shop_id) REFERENCES COFFEE_SHOP (shop_id) ); CREATE TABLE SUPPLIER( supplier_id INTEGER UNSIGNED, company_name VARCHAR(50), country VARCHAR(30), sales_contact_name VARCHAR(60), email VARCHAR(50) NOT NULL, PRIMARY KEY (supplier_id) ); CREATE TABLE COFFEE( coffee_id INTEGER UNSIGNED, shop_id INTEGER UNSIGNED, supplier_id INTEGER UNSIGNED, coffee_name VARCHAR(30), price_per_pound NUMERIC(5,2), PRIMARY KEY (coffee_id), FOREIGN KEY (shop_id) REFERENCES COFFEE_SHOP (shop_id), FOREIGN KEY (supplier_id) REFERENCES SUPPLIER (supplier_id) );