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;
.png)
.png)
![Preview of eBook [PDF] Database Systems Design Implementation, & Management 14th Edition By Carlos Co](https://browseimages.nyc3.digitaloceanspaces.com/paper-images/2025/Aug/02/QbpHFGyM2025-08-02-07-27688d93c923bcc.png)
