Nora's Bagel Bin Database Blueprints Data Management - Applications - VHT2 A.

Nora's Bagel Bin Database Blueprints Data Management - Applications - VHT2 A. 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 The table is in the First Normal form, as it satisfies the following requirements: • Have a unique identifier (primary key) • Don’t have any repeating columns • Don’t have any columns that hold more than value A composite primary key is created with Bagel Order ID and Bagel ID as the unique identifier for the records in the table above. A1. Second Normal Form (2NF) The tables are in the Second Normal form, as it satisfies the following requirements: • It is in the 1st NF. • Each non-key attribute is fully functionally dependent on the primary key. The table is reduced into 2nd normal form by splitting the BAGEL ORDER table into BAGLE ORDER LINE ITEM with Bagel Order ID, Bagel ID, Bagel Quantity as its attributes; and BAGEL table with Bagel ID, Bagel Name, Bagel Description, and Bagel Price as its attributes. It ensures that the tables are in 1st NF and all non-key attributes are fully functionally dependent on the primary key. The cardinality of the relationship between two tables: • From the tables BAGEL ORDER and BAGLE ORDER LINE ITEM, reading from left to right, there can be many line items for every order. Reading from right to left, the table indicates that each line item is associated with only one order. Thus, there exists a One-to-Many relationship between the tables. • From the tables BAGLE ORDER LINE ITEM and BAGEL, reading from left to right, it says there can be only one bagel for every line item. Reading from right to left, the table indicates that each bagel is associated with multiple line items. Thus, a Many-to-One relationship exists between the tables. A2. Third Normal Form (3NF) The tables are in the Third Normal form, as it satisfies the following requirements: • It is in the 2nd NF • No transitive functional dependency From the above tables, we can say that BAGLE ORDER LINE ITEM and BAGEL tables are in 3rd normal form because non-key attributes are fully functionally dependent on the primary key and not on any other non-key attribute. In contrast, the BAGEL ORDER table has customer data related to a Customer ID, which establishes a transitive functional dependency. So, to make the Bagel Order table into 3rd normal form, it is further split into CUSTOMER table. The cardinality of the relationship between two tables: • From the tables BAGEL ORDER and BAGLE ORDER LINE ITEM, reading from left to right, there can be many line items for every order. Reading from right to left, the table indicates that each line item is associated with only one order. Thus, there exists a One-to-Many relationship between the tables. • From the tables BAGLE ORDER LINE ITEM and BAGEL, reading from left to right, it says there can be only one bagel for every line item. Reading from right to left, the table indicates that each bagel is associated with multiple line items. Thus, a Many-to-One relationship exists between the tables. • From the tables BAGEL ORDER and CUSTOMER, reading from left to right, it says a customer can place many/multiple orders. Reading from right to left, the table indicates that a single customer can place various orders. Thus, there exists a Many-to-One relationship between the tables. A3. Final Physical Database Model 1 M : 1 CUSTOMER PK customer_id INTEGER first_name VARCHAR(30) last_name VARCHAR(30) address_1 VARCHAR(50) address_2 VARCHAR(50) city VARCHAR(10) state CHAR(2) zip INTEGER mobile_phone INTEGER B1a. SQL Code CREATE TABLE Coffee_shop ( shop_id INTEGER, shop_name VARCHAR(50), city VARCHAR(50), state CHAR(2), PRIMARY KEY (shop_id) ); CREATE TABLE Employee ( employee_id INTEGER, first_name VARCHAR(30), last_name VARCHAR(30), hire_date DATE, job_title VARCHAR(30), shop_id INTEGER, PRIMARY KEY (employee_id), FOREIGN KEY (shop_id) REFERENCES Coffee_shop (shop_id) ); CREATE TABLE Supplier ( supplier_id INTEGER, 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, shop_id INTEGER, supplier_id INTEGER, 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) ); B1b. Screenshot of the SQL commands and the database server’s response: