Question

1 Approved Answer

Posted on Sep 22, 2024

Using the functional dependencies identified here, transform the following relationships into 1NF, 2NF, and BCNF. Make the changes one at a time, clearly showing the

Using the functional dependencies identified here, transform the following relationships into 1NF, 2NF, and BCNF. Make the changes one at a time, clearly showing the step-by- step transformation of the relations at each stage. In other words, transform the relations into 1NF first. Then, transform the relations into 2NF. Finally, transform the relations into BCNF.

Include updated tables for each normalization forms.

image text in transcribed

Customer Name Cell 1511551 8675309 Address 1551 E Main Street Corrie 22 N 2nd Place 1551 E Main Street Joseph 5155115 4848 W 48 Terrace Stephen 8884444 342 S Spring Way Jenny Vikki 3333342 FDs: Name } - {Address, Phone_Number} Key: [Name] Driver Car NumberNameMake Model Bill Charlie Ford Linda Nathan Subaru Outback Honda Civic scort Taurus Ford 4 FDs: {Car_Number) -{Name, Model ) {Model ) - {Make} Key: {Car_Number, Model) Order Driver Is_Delivery Order ID Pizza_ID Bill LindaTRUE NULL | FALSE Bill TRUE 4 1 7 1 TRUE 4 FDs: { Order-ID} { Driver, ls Delivery, Pizza-ID} Key: Order_ID) Pizza Crust Cheese thick mozzarella 1 thin mozzarella 2 Pizza ID Price Size Sauce Topping_1 Topping_2 Topping_3 red NULL red NULL Name Big Cheese Big Cheese Cheese Just Right thickmozzarella3 Cheese Just Right thin mozzarella | Lil Cheese Lil Cheese Mushroom n Pep thick|mozzarella7 Hawaiian Meatballa Popeye's Delight thin fontina Popeye's Delight thin fontina Popeye's Delight thin mozzarella 12 Veggie NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 9.50large 8.50 arge 7.50 medium red NULL 6.50 medium red NULL 6.00 small 5.00 small 12.50 large 12.00 large 11.00 medium white meatballNULL 11.00 medium white chicken 12.00 large 11.75 large 6.50 small red NULL red NULL redmushroom pepperoni NULL red ham thick mozzarella 5 thin mozzarella 6 thin mozzarella 8 thin fontina pineapple NULL NULL Spinach Spinach Spinach NULL 10 garlic garlic red chicken redchicken red onion arlic thin none 13 FDs: {Pizza-ID} {Name, crust, cheese, Size, Sauce] Name, Crust, Cheese, Size}-> {Price} {Name) - sTopping _1,Topping_2,Topping_3) Key: {Pizza ID) What will do in the case where we include the Name: "Build Your Own" [BYO] to our list of pizzas? We will not deal with BYO on this assignment. However, start thinking about the implications this has on our DB and how you would resolve this inside the DB representation Address Gainesville Old New York Mystic, Connecticut Mystic Pizza Restaurant Name Panucci's Pizza Panucci's Pizza 2123000 Phone Number 3523000 2011988 Tri-County, USA Pizza Planet 4328765 Required Name, Address }- {Phone_Number) Key: {Name, Address) Topping Calories FoodGroup Name 175 20 225 300 15 30 40 250 50 10 meat seasoning meat meat veggie veggie veggie meat fruit veggie chicken garlic ham meatball mushroom onion pepper pepperoni pineapple spinach Required { Name }-( Calories, Food-Group } Key: [Name] Customer Name Cell 1511551 8675309 Address 1551 E Main Street Corrie 22 N 2nd Place 1551 E Main Street Joseph 5155115 4848 W 48 Terrace Stephen 8884444 342 S Spring Way Jenny Vikki 3333342 FDs: Name } - {Address, Phone_Number} Key: [Name] Driver Car NumberNameMake Model Bill Charlie Ford Linda Nathan Subaru Outback Honda Civic scort Taurus Ford 4 FDs: {Car_Number) -{Name, Model ) {Model ) - {Make} Key: {Car_Number, Model) Order Driver Is_Delivery Order ID Pizza_ID Bill LindaTRUE NULL | FALSE Bill TRUE 4 1 7 1 TRUE 4 FDs: { Order-ID} { Driver, ls Delivery, Pizza-ID} Key: Order_ID) Pizza Crust Cheese thick mozzarella 1 thin mozzarella 2 Pizza ID Price Size Sauce Topping_1 Topping_2 Topping_3 red NULL red NULL Name Big Cheese Big Cheese Cheese Just Right thickmozzarella3 Cheese Just Right thin mozzarella | Lil Cheese Lil Cheese Mushroom n Pep thick|mozzarella7 Hawaiian Meatballa Popeye's Delight thin fontina Popeye's Delight thin fontina Popeye's Delight thin mozzarella 12 Veggie NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 9.50large 8.50 arge 7.50 medium red NULL 6.50 medium red NULL 6.00 small 5.00 small 12.50 large 12.00 large 11.00 medium white meatballNULL 11.00 medium white chicken 12.00 large 11.75 large 6.50 small red NULL red NULL redmushroom pepperoni NULL red ham thick mozzarella 5 thin mozzarella 6 thin mozzarella 8 thin fontina pineapple NULL NULL Spinach Spinach Spinach NULL 10 garlic garlic red chicken redchicken red onion arlic thin none 13 FDs: {Pizza-ID} {Name, crust, cheese, Size, Sauce] Name, Crust, Cheese, Size}-> {Price} {Name) - sTopping _1,Topping_2,Topping_3) Key: {Pizza ID) What will do in the case where we include the Name: "Build Your Own" [BYO] to our list of pizzas? We will not deal with BYO on this assignment. However, start thinking about the implications this has on our DB and how you would resolve this inside the DB representation Address Gainesville Old New York Mystic, Connecticut Mystic Pizza Restaurant Name Panucci's Pizza Panucci's Pizza 2123000 Phone Number 3523000 2011988 Tri-County, USA Pizza Planet 4328765 Required Name, Address }- {Phone_Number) Key: {Name, Address) Topping Calories FoodGroup Name 175 20 225 300 15 30 40 250 50 10 meat seasoning meat meat veggie veggie veggie meat fruit veggie chicken garlic ham meatball mushroom onion pepper pepperoni pineapple spinach Required { Name }-( Calories, Food-Group } Key: [Name]