VHT2 Task 1

.docx

School

Western Governors University *

*We aren’t endorsed by this school

Course

C170

Subject

Business

Date

May 9, 2024

Type

docx

Pages

Uploaded by insired on coursehero.com

VHT2 Task 1: Normalization and Database Design Part A Part A1 Second Normal Form (2NF) BAGEL ORDER BAGEL ORDER LINE ITEM BAGEL PK Bagel Order ID PK Bagel Order ID PK Bagel ID Order Date 1:M FK Bagel ID M:1 Bagel Name First Name Bagel Quantity Bagel Description Last Name Bagel Price Address 1 Address 2 City State Zip Mobile phone Delivery Fee Special Notes I placed all attributes that pertains to a “bagel order” into the BAGEL ORDER table. The Primary key must be the order ID. All attributes of a line item of each order went into the BAGEL ORDER LINE ITEM table, with the order ID as the primary key and the bagel ID as the foreign key. The table BAGEL contains all attributes of a bagel, with Bagel ID as the primary key. Cardinality between BAGEL ORDER and BAGEL ORDER LINE ITEM is one-to-many since multiple line items can exist in each order. Cardinality between BAGEL ORDER LINE ITEM and BAGEL is many-to-one since many different line items can reference a bagel, but each line item must only reference a single entry in the bagel table.

Part A2 Third Normal Form (3NF) BAGEL ORDER BAGEL ORDER LINE ITEM BAGEL PK Bagel Order ID PK Bagel Order ID PK Bagel ID FK Customer ID 1:M FK Bagel ID M:1 Bagel Name Order Date Bagel Quantity Bagel Description Delivery Fee Bagel Price Special Instructions M:1 CUSTOMER PK Customer ID First Name Last Name Address 1 Address 2 City State Zip Mobile Phone All attributes relating to customer information was removed from the BAGEL ORDER table and moved to its own CUSTOMER table. BAGEL ORDER LINE ITEM and BAGEL table was untouched. The cardinality between BAGEL ORDER and BAGEL ORDER LINE ITEM is one-to-many since multiple line items can exist in each order. Cardinality between BAGEL ORDER and CUSTOMER is many-to-one because many orders may exist for each customer in the CUSTOMER table. Cardinality between BAGEL ORDER LINE ITEM and BAGEL is many-to-one since many line items may exist for each bagel in the BAGEL table, but each line item must only reference a single entry in the bagel table.

Part A3 Final Physical Database Model BAGEL ORDER BAGEL ORDER LINE ITEM BAGEL PK bagel_order_id INT PK bagel_order_id INT PK bagel_id CHAR(2) FK customer_id INT 1:M FK bagel_id CHAR(2) M:1 bagel_name VARCHAR(20) order_date TIMESTAMP bagel_quantity INT bagel_descriptio n VARCHAR(255) delivery_fee NUMERIC(5,2) bagel_price NUMERIC(5,2) special_instructi ons VARCHAR(255) M:1 CUSTOMER PK customer_id INT first_name VARCHAR(25) last_name VARCHAR(25) address_1 VARCHAR(50) address_2 VARCHAR(50) city VARCHAR(50) state CHAR(2) zip CHAR(5) mobile_phone Char(12)

Part B Part B1 CREATE TABLE COFFEE_SHOP ( shop_id int NOT NULL, shop_name varchar(50), city varchar(50), state char(2), PRIMARY KEY (shop_id) ); CREATE TABLE SUPPLIER ( supplier_id int NOT NULL, 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 int NOT NULL, shop_id int NOT NULL, supplier_id int NOT NULL, 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) ); CREATE TABLE EMPLOYEE ( employee_id int NOT NULL, first_name varchar(30),

last_name varchar(30), hire_date date, job_title varchar(30), shop_id int NOT NULL, PRIMARY KEY (employee_id), FOREIGN KEY (shop_id) REFERENCES COFFEE_SHOP(shop_id) );

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version