WGU C170 VHT2 TASK 1 NORMALIZATION AND DATABASE DESIGN

VHT2 TASK 1: NORMALIZATION AND DATABASE DESIGN Course: Data Management – Applications – C170 Student ID # ######## Nora’s Bagel Bin Database Blueprints First Normal Form (1NF) BAGEL O ...

VHT2 TASK 1: NORMALIZATION AND DATABASE DESIGN Course: Data Management – Applications – C170 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) ); 1.b. SHOW COLUMNS FROM EMPLOYEE; SHOW COLUMNS FROM COFFEE_SHOP; SHOW COLUMNS FROM COFFEE; SHOW COLUMNS FROM SUPPLIER; 2.a. INSERT INTO COFFEE_SHOP (shop_id, shop_name, city, state) VALUES (100, 'Heredia Cafe', 'Lake Mary', 'FL'), (101, 'San Jose Cafe', 'Longwood', 'FL'), (102, 'Alajuela Cafe', 'Altamonte', 'FL'); INSERT INTO EMPLOYEE (employee_id, first_name, last_name, hire_date, job_title, shop_id) VALUES (1000, 'Jose', 'Gonzalez', '2021-08-11', 'barista', 102), (1001, 'Sebastian', 'Smith', '2020-09-21', 'waiter', 100), (1002, 'Jane', 'Martinez', '2020-02-04', 'barista', 101); INSERT INTO SUPPLIER (supplier_id, company_name, country, sales_contact_name, email) VALUES (500, 'Britt Co', 'Costa Rica', 'Alberto Flores', '[email protected]'), (501, 'Dota Coffee', 'Costa Rica', 'Ricardo Soto', '[email protected]'), (502, 'Magic Beans', 'Colombia', 'Maria Yepes', '[email protected]'); INSERT INTO COFFEE (coffee_id, shop_id, supplier_id, coffee_name, price_per_pound) VALUES (10, 100, 502, 'Cafe Montana', 2.00), (11, 102, 500, 'Juan Valdez', 3.25), (12, 101, 501, 'Rey Coffee', 1.75); 2.b. SELECT * FROM COFFEE_SHOP; SELECT * FROM EMPLOYEE; SELECT * FROM SUPPLIER; SELECT * FROM COFFEE; 3.a. and 3.b. CREATE VIEW EMPLOYEE_VIEW AS SELECT employee_id, concat(first_name," ",last_name) AS employee_full_name, hire_date, job_title, shop_id FROM EMPLOYEE; SELECT * FROM EMPLOYEE_VIEW; 4.a. and 4.b. CREATE INDEX coffee_index ON COFFEE(coffee_name); SHOW INDEX FROM COFFEE; 5.a. and 5.b. SELECT employee_full_name, hire_date FROM EMPLOYEE_VIEW WHERE hire_date BETWEEN '2020-01-01' AND '2021-01-01'; 6.a. and 6.b. SELECT e.*, c.*, cs.* FROM EMPLOYEE e JOIN COFFEE_SHOP cs ON cs.shop_id = e.shop_id JOIN COFFEE c ON c.shop_id = cs.shop_id;