WGU C170 Data Management - Applications Performance Assessment.

Sales order 1NF WGU C170 Data Management - Applications Performance Assessment. EXPLANATION: I am going to call this table the "Order Table" I also need to make sure that this table contains a primary Key, in this particular table I am going to make the “OrderID” and “DonutID” the Primary Key (composite key), the reason I chose to use OrderID and DonutID as the primary key is because Order ID or DonutID by themselves do not guarantee Uniqueness but when combined uniqueness is achieved. I am going to separate city, state and zip in order to make the data atomic with no repeating groups. I am also going to get rid of Customer since there is already a Customer ID field and customer will most likely be a table of its own. I left the Donut name and description empty because my table was starting to be too big to fit it in this document. Orde r ID Custome r ID (PK) OrderDat e First Nam e Last Nam e Street addres s Apt # City State Zip Home phone Mobile phone Other phone Donu t ID Qt y Donu t Nam e Descriptio n Unit price Note s 1 2 9/1/17 John Perez 12th ST #4 Austi Texa 1234 1 1 $1.5 None n s 5 1 9 9 0 1 2 9/1/17 John Perez 12th ST #4 Austi Texa 1234 2 5 $1.7 None n s 5 9 9 5 1 2 9/1/17 John Perez 12th ST #4 Austi Texa 1234 3 12 $1.7 None n s 5 9 9 5 1 2 9/1/17 John Perez 12th ST #4 Austi Texa 1234 4 3 $1.7 None n s 5 9 9 5 1 2 9/1/17 John Perez 12th ST #4 Austi Texa 1234 5 4 $1.7 None n s 5 9 9 5 1 2 9/1/17 John Perez 12th ST #4 Austi Texa 1234 6 5 $2.0 None n s 5 9 9 Sales Order 2NF EXPLANATION In Second Form I need to have fulfilled 1NF and ensure that No functional dependencies exist, in order to accomplish that I need to Separate Donut ID and Order ID and create their own tables and since Quantity can cause a many to many relationship To resolve that issue I will create the OrderLineItem table in which OrderID and DonutID will be the PK, however since OrderID and Donut ID are the primary keys for their own respective tables they are also Foreign keys in the OrderLineItem table and therefore reference their own table as FK in orderlineitem table . In the Order Table I have chosen to use Order ID as the Primary Key since the Order table most likely will contain order related information like customer ID Order ID the Date and the notes. Orders Table Order ID (PK) Custo mer ID OrderDate First Name Last Name Street address Apt# City State Zip Home phone Mobile phone Other phone Notes 1 2 9/1/17 John Perez 12th ST #4 Austin Texas 12345 1 None Donut Table Donut ID (PK) Name Description Unit Price 1 Plain Plain Donut $1.50 2 Glazed Glazed Donut $1.75 3 Cinnamon Cinnamon Donut $1.75 4 Chocolate Chocolate $1.75 5 Sprinkle Sprinkle Donut $1.75 6 Gluten-Free Gluten-Free Donut $2.0 Order Line Item Table Order ID (PK) (FK) Donut ID (PK) (FK) Quantity 1 1 1 1 2 5 1 3 12 1 4 3 1 5 4 1 6 5 Sales Order 3NF EXPLANATION: There was no need to have all the customer information as it is redundant so I removed it. I also created two more tables one to house the information that does not need to be in Order table like Donut ID, Quantity, Donut name, Description or unit prize that information needs to go in to separate tables. The quantity of each line item is defined with the combination of the Order ID and the Donut ID, so that information was separated into the Order Line Item table. The Name, Description, and Unit Price of each donut can be defined by a Donut ID, so that was separated into a table to reduce redundancy. A customer will most likely have more than one order if we were to leave the customer ID in the Order table the customer information will be repeated for each of their orders. To avoid this the customer table was created that way the customer information is only added once. In Order Table the PK is OrderID and the FK is CustomerID. In Order Line Item table the OrderID and DonutID are the PK and also FKs. In Donut table the DonutID is the PK and in the customer table the CustomerID is the PK. Order Table Order ID (PK) Custo merID (FK) OrderDate Notes 1011 0001 9/10/17 None Order Line Item Table Order ID (PK) (FK) Donut ID (PK) (FK) Quantity 1 1 1 1 2 5 1 3 12 1 4 3 1 5 4 1 6 5 Donut Table Donut ID (PK) Name Description Unit Price 1 Plain Plain Donut $1.50 2 Glazed Glazed Donut $1.75 3 Cinnamon Cinnamon Donut $1.75 4 Chocolate Chocolate $1.75 5 Sprinkle Sprinkle Donut $1.75 6 Gluten-Free Gluten-Free Donut $2.0 Customer Table Customer ID (PK) First Last Street Apt City State Zip Home phone Mobile Phone Other Phone 1 John Gutierrez 1205 Laguna Drive B Phoenix AZ 85034 . SECTION B E-R Diagram The E-R diagram was drawn using licidchart. Each table represents the data from the sales order in 3NF. B4A,B Explanation Why I chose the entities I did. Well I looked at the sales form and I asked my Self, What type of information do we need to store and manage? So after looking at the sales form we need a system where we can manage customers, Orders and Donuts. So after evaluating the type of information we need to store and manage, I decided in making Customer, Order, Donut my Entities since they are the information we need to store and manage and they can exist on their own and are Unique. The relationship between these entities: A customer can walk in to the Donut store and order a Donut. Now the customer by ordering a donut be linked to an order that order will be then be related to a donut. Therefore the customer is related to the donut by ordering the donut the donut is then related to the order and the Order related to the customer after all is said and done the customer the order and the donut all have a relationship. In the E-R model we can see that each customer may have one or more orders but one order can only belong to 1 customer, each order may have 1 or more line items but each line item belongs to only one order, each line item can have multiple donuts but only 1 of specific donut can belong to a line item. SECTION C For creating the code and tables I used MySQL 5.6 in SQL Fiddle. Code to create Customer Table and PK: CREATE TABLE customer(CustomerID INT(10)NOT NULL AUTO_INCREMENT, FirstName VARCHAR(10)NOT NULL, LastName VARCHAR(10)NOT NULL, Street VARCHAR(30)NOT NULL, Apt VARCHAR(5), City VARCHAR(30)NOT NULL, State VARCHAR(10)NOT NULL, Zip INT(5)NOT NULL, HomePhone CHAR(15), MobilePhone CHAR(15), OtherPhone CHAR(15), PRIMARY KEY (CustomerID)); Screen Shot: Code To Create Donut table and PK: CREATE TABLE Donut(DonutID INT(5)NOT NULL AUTO_INCREMENT, Name VARCHAR(50)NOT NULL, Description VARCHAR(150)NOT NULL, UnitPrice DECIMAL(4,2)NOT NULL, PRIMARY KEY (DonutID));