WGU C170 Objective Assessment Edward M Burke

WGU C170 Objective Assessment Edward M Burke. Third Normal Form – 4 Tables In order to achieve third normal form we need to eliminate any transitive dependency, meaning an attribute depends oanother attribute that is not the primary key. For example, looking at our second normal form tables, Customer Last Name is dependent on Donut Order ID. (Each Donut Order ID has only one Customer Last Name value associated witTo transform into third normal form we simply move any transitively dependent attributes to their own relation where they depend on only the primary key. Entity Relationship Diagram using tables from A1C: Both the Customer and Donut entities have a single primary key and related attributes that describe them. The orderDetails table is an associative entity that shows the interactive relationship between the Donut, and donutOrder entities. The DonutOrder entity has a primary key of DonutOrder_ID, and a foreign key consisting of Customer_ID to record the order date, notes, and totals that describe the interaction of the Customer and Donut purchase with corresponding foreign keys. The cardinality from customer to donutOrder is 1-to-many as a customer can place multiplorders, while the cardinality in the other direction will be many-to-1 because a donutOrder will have at most one customer per order. The donut to orderDetails cardinality is 1-to-many as one donut can fill multiple orderDetail records, while in the other direction, an orderDetails record will have 1 donut per record, representing a many-to-1 relationship. The donutOrder to orderDetails cardinality is 1-to-many as one donutORder may reference multiple orderDetail records, and in the other direction, one orderDetails record will only pertain to one donutOrder. The orderDetails table has a composite primary and composite foreign key key consisting of donutOrder_ID and Donut_ID which shows the intersection data of the Donut and donutOrder tables. C. Develop the SQL code to create each of the third normal form tables you designed in part A and refined in part B: CREATE TABLE `Customer` ( `Customer_ID` INT UNSIGNED NOT NULL, `First_Name` VARCHAR(25) NOT NULL, `Last_Name` VARCHAR(25) NOT NULL, `Address` VARCHAR(25) NOT NULL, `Apt` VARCHAR(4), `City` VARCHAR(25) NOT NULL, `State` VARCHAR(2) NOT NULL, `Zip` VARCHAR(5) NOT NULL, `Home_Phone` VARCHAR(12) NOT NULL, `Mobile_Phone` VARCHAR(12), `Other_Phone` VARCHAR(12), PRIMARY KEY (`Customer_ID`) ); CREATE TABLE `Donut` ( `Donut_ID` TINYINT UNSIGNED NOT NULL, `Donut_Name` ENUM('Plain', 'Glazed', 'Cinnamon', 'Chocolate', 'Sprinkle', 'Gluten-Free'), `Donut_Description` VARCHAR(255) NOT NULL, `Unit_Price` Decimal(5,2) NOT NULL, PRIMARY KEY (`Donut_ID`) ); CREATE TABLE `donutOrder` ( `DonutOrder_ID` INT UNSIGNED NOT NULL, `Customer_ID` INT UNSIGNED NOT NULL, `Order_Date` DATETIME DEFAULT CURRENT_TIMESTAMP, `Special_Notes` VARCHAR(255), PRIMARY KEY (`DonutOrder_ID`), FOREIGN KEY (`Customer_ID`) REFERENCES Customer(Customer_ID) ); CREATE TABLE `oderDetails` ( `DonutOrder_ID` INT UNSIGNED NOT NULL, `Donut_ID` TINYINT UNSIGNED NOT NULL, `Quantity` INT NOT NULL, PRIMARY KEY (`DonutOrder_ID`, `Donut_ID`), FOREIGN KEY (`DonutOrder_ID`) REFERENCES donutOrder(DonutOrder_ID), FOREIGN KEY (`Donut_ID`) REFERENCES donut(Donut_ID)