Database Management > EXAM > WGU C170 VHT2 TASK 1 NORMALIZATION AND DATABASE DESIGN (All)
VHT2 TASK 1: NORMALIZATION AND DATABASE DESIGN Course: Data Management – Applications – C170 Student ID # ######## Nora’s Bagel Bin Database Blueprints First No... rmal 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; [Show More]
Last updated: 1 year ago
Preview 1 out of 17 pages
Database Management> EXAM > DATA MANAG C170 VHT2 TASK 1: NORMALIZATION AND DATABASE DESIGN Course: Data Management – Applications – C170 | 100% Guaranteed | Western Governors University (All)
DATA MANAG C170 VHT2 TASK 1: NORMALIZATION AND DATABASE DESIGN Course: Data Management – Applications – C170 | 100% GUranteed | Western Governors University
By A+ Solutions , Uploaded: Feb 28, 2023
$10
*NURSING> EXAM > WGU C170 VHT2 TASK 1: NORMALIZATION AND DATABASE DESIGN (All)
WGU C170 VHT2 TASK 1: NORMALIZATION AND DATABASE DESIGN
By Ellen Ronald , Uploaded: Jun 10, 2022
$11.5
*NURSING> EXAM > WGU C170 VHT2 TASK 1: NORMALIZATION AND DATABASE DESIGN (All)
VHT2 TASK 1: NORMALIZATION AND DATABASE DESIGN Course: Data Management – Applications – C170 John Smith Western Governors University Student ID # ######## John Smith C170 – VHT2 Nora’...
By markstudys , Uploaded: Sep 13, 2022
$11
Computer Science> EXAM > WGU C170 VHT2 TASK 1 NORMALIZATION AND DATABASE DESIGN (All)
WGU C170 VHT2 TASK 1 NORMALIZATION AND DATABASE DESIGN
By professor chue , Uploaded: Sep 27, 2022
$9.5
Computer Science> EXAM > WGU C170 VHT2 TASK 1 NORMALIZATION AND DATABASE DESIGN / ALREADY PASSED 100% ⭐⭐⭐⭐⭐ (All)
WGU C170 VHT2 TASK 1 NORMALIZATION AND DATABASE DESIGN / ALREADY PASSED 100% ⭐⭐⭐⭐⭐WGU C170 VHT2 TASK 1 NORMALIZATION AND DATABASE DESIGN / ALREADY PASSED 100% ⭐⭐⭐⭐⭐WGU C170 VHT2 TASK 1 NORMA...
By Otieno , Uploaded: Jan 20, 2023
$14
Computer Science> EXAM > WGU C170 VHT2 TASK 1 NORMALIZATION AND DATABASE DESIGN / ALREADY PASSED 100% ⭐⭐⭐⭐⭐ (All)
WGU C170 VHT2 TASK 1 NORMALIZATION AND DATABASE DESIGN / ALREADY PASSED 100% ⭐⭐⭐⭐⭐WGU C170 VHT2 TASK 1 NORMALIZATION AND DATABASE DESIGN / ALREADY PASSED 100% ⭐⭐⭐⭐⭐WGU C170 VHT2 TASK 1 NORMA...
By IMMANUEL , Uploaded: Jan 20, 2023
$14
Computer Science> EXAM > WGU C170 VHT2 TASK 1 NORMALIZATION AND DATABASE DESIGN / ALREADY PASSED 100% ⭐⭐⭐⭐⭐ (All)
WGU C170 VHT2 TASK 1 NORMALIZATION AND DATABASE DESIGN / ALREADY PASSED 100% ⭐⭐⭐⭐⭐WGU C170 VHT2 TASK 1 NORMALIZATION AND DATABASE DESIGN / ALREADY PASSED 100% ⭐⭐⭐⭐⭐WGU C170 VHT2 TASK 1 NORMA...
By Doctor Immanuel , Uploaded: Jan 20, 2023
$14
Health Care> EXAM > Ethics for Health Professions HCA-200 Final Exam Questions with Answers 100% Correct (All)
Ethics for Health Professions (HCA- 200) Final Examination Part 1: Multiple Choice (1 points each) ● Identify the choice that best completes the statement or answers the question. ● Clearly circ...
By Quiz Merchant , Uploaded: Apr 19, 2021
$9
*NURSING> EXAM > ENPC Test Questions & Answers (All)
ENPC Test Questions & Answers-An unresponsive 2-year-old child was found by his mother with a bottle labeled "Elavil 50 mg" by his side. Which piece of information is important to obtain from his moth...
By PROF , Uploaded: Apr 25, 2024
$9.5
Religious Studies> EXAM > CWV TOPIC 2 QUIZ. QUESTIONS AND ANSWERS LATEST UPDATED. (Score 100%) (All)
CWV TOPIC 2 QUIZ QUESTIONS AND ANSWERS LATEST UPDATED
By ELIANA , Uploaded: Aug 01, 2022
$9
Connected school, study & course
About the document
Uploaded On
Jan 19, 2023
Number of pages
17
Written in
This document has been written for:
Uploaded
Jan 19, 2023
Downloads
0
Views
178
Avoid resits and achieve higher grades with the best study guides, textbook notes, and class notes written by your fellow students
Your fellow students know the appropriate material to use to deliver high quality content. With this great service and assistance from fellow students, you can become well prepared and avoid having to resits exams.
Your fellow student knows the best materials to research on and use. This guarantee you the best grades in your examination. Your fellow students use high quality materials, textbooks and notes to ensure high quality
Get paid by selling your notes and study materials to other students. Earn alot of cash and help other students in study by providing them with appropriate and high quality study materials.
In Browsegrades, a student can earn by offering help to other student. Students can help other students with materials by upploading their notes and earn money.
We're available through e-mail, Twitter, Facebook, and live chat.
FAQ
Questions? Leave a message!
Copyright © Browsegrades · High quality services·