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

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