Western Governors University C170 Performance Assesment 3/ Data Management - Applications – 100% correct

Document Content and Description Below

Sales order 1NF Data Management - Applications – C170 Performance Assessment. EXPLANATION: I am going to call this table the "Order Table" I also need to make sure that this table contain ... s 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 123456789 12345678 12345678 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 123456789 12345678 12345678 2 5 $1.7 None n s 5 9 9 5 1 2 9/1/17 John Perez 12th ST #4 Austi Texa 1234 123456789 12345678 12345678 3 12 $1.7 None n s 5 9 9 5 1 2 9/1/17 John Perez 12th ST #4 Austi Texa 1234 123456789 12345678 12345678 4 3 $1.7 None n s 5 9 9 5 1 2 9/1/17 John Perez 12th ST #4 Austi Texa 1234 123456789 12345678 12345678 5 4 $1.7 None n s 5 9 9 5 1 2 9/1/17 John Perez 12th ST #4 Austi Texa 1234 123456789 12345678 12345678 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 1234567891 123456789 123456789 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 123456789 123456789 123456789 . 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)); Screen Shot: Code to create Order Table and PK: CREATE TABLE Orders ( OrderID int(10) NOT NULL, CustomerID int(10) NOT NULL, OrderDate DATE, Notes VARCHAR(300), PRIMARY KEY (OrderID), CONSTRAINT FK_CustomerOrders FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID) ); Screen Shot: Code to create the OrderLineItem table: CREATE TABLE OrderLineItem( OrderID INT(10) REFERENCES Orders(OrderID), DonutID INT(10) REFERENCES Donut(DonutID), Quantity INT(5), PRIMARY KEY (OrderID, DonutID) ); Screen Shot: SECTION D: Code used to create Concatenated View. CREATE VIEW v1 AS SELECT CustomerID, CONCAT(FirstName,'',LastName) AS "Name",Street, Apt, City, State, Zip, HomePhone, MobilePhone, OtherPhone FROM customer Full Code: CREATE TABLE customer(CustomerID INT(10), FirstName VARCHAR(10), LastName VARCHAR(10), Street VARCHAR(25), Apt VARCHAR(5), City VARCHAR(10), State VARCHAR(10), Zip INT(5), HomePhone CHAR(12), MobilePhone CHAR(12), OtherPhone CHAR(12)); INSERT INTO customer(CustomerID, FirstName, LastName, Street, Apt, City, State, Zip, HomePhone, MobilePhone,OtherPhone) VALUES(1,'John','Gutierrez','Laguna Drive','B','Phoenix','AZ','85034','1252156547','1234568521', '2586542354'); CREATE VIEW v1 AS SELECT CustomerID, CONCAT(FirstName,'',LastName) AS "Name",Street, Apt, City, State, Zip, HomePhone, MobilePhone, OtherPhone FROM customer Code used to view the Table: SELECT * FROM v1 Customer Table Populated after the Query has run: SECTION E: Code used to create an “Index” for the Donut name field. CREATE TABLE Donut(DonutID INT(5), Name VARCHAR(50), Description VARCHAR(150), UnitPrice DECIMAL(4,2)); INSERT INTO Donut(DonutID, Name, Description, UnitPrice) VALUES(01,'Plain','Plain Donut','1.50'); CREATE UNIQUE INDEX DonutName ON Donut (Name); Code Used to View the Donut Index. SHOW INDEX FROM Donut Donut index view after running the complete “CREATE INDEX” and “SHOW INDEX” code. SECTION F SQL Code to insert data in to all of the Tables. CREATE TABLE customer(CustomerID INT(10), FirstName VARCHAR(10), LastName VARCHAR(10), Street VARCHAR(30), Apt VARCHAR(5), City VARCHAR(30), State VARCHAR(10), Zip INT(5), HomePhone CHAR(15), MobilePhone CHAR(15), OtherPhone CHAR(15)); INSERT INTO customer(CustomerID, FirstName, LastName, Street, Apt, City, State, Zip, HomePhone, MobilePhone,OtherPhone) VALUES(1001,'John','Gutierrez','1205 Laguna Drive','B','Phoenix','AZ','85034','1252156547','1234568521','2586542354'), (1002,'Peter','Jones','1303 Alameda Ave','C','Houston','TX','10345','1234567890','2345678901','3456789012'), (1003,'Dun','Whitaker','205 Bronco st','D','Tempe','AZ','30121','4567890123','5678901234','6789012345'), (1004,'Ali','Rodriguez','706 Trip Ave','E','Glendale','AZ','012345','7890123456','8901234567','9012345678'), (1005,'Rob','Gonzo','1225 Tesla Dr','F','Victorville','CA','03131','6231234567','6238912345','6239012345'), (1006,'Pablo','Hernandez','1301 Tonto Park','G','Sandusky','OH','50621','6231477896','6233698741','6238521254'); CREATE TABLE Donut(DonutID INT(5), Name VARCHAR(50), Description VARCHAR(150), UnitPrice DECIMAL(4,2)); INSERT INTO Donut(DonutID, Name, Description, UnitPrice) VALUES(01,'Plain','Plain Donut','1.50'), (02,'Glazed','Glazed Donut','1.75'), (03,'Cinnamon','Cinnamon Donut','1.75'), (04,'Chocolate','Chocolate Donut','1.75'), (05,'Sprinkle','Sprinkle Donut','1.75'), (06,'Gluten-Free','Gluten-Free Donut','2.0'); CREATE TABLE Orders(OrderID INT(10), CustomerID INT(10), OrderDate DATE, Notes VARCHAR(300)); INSERT INTO Orders(OrderID, CustomerID, OrderDate, Notes) VALUES(1011,1001,'2017-09-10','Hmmm Donuts'), (1012,1002,'2017-08-09','Love Donuts'), (1013,1003,'2017-07-08','I am getting fat'), (1014,1004,'2017-06-07','Excersise'), (1015,1005,'2017-05-06','Homer Simpson loves Donuts'), (1016,1006,'2017-04-05','I am almost done eating all the donuts'); CREATE TABLE OrderLineItem(OrderID INT(10), DonutID INT(10), Quantity INT(5)); INSERT INTO OrderLineItem(OrderID, DonutID, Quantity) VALUES(1011,1,10), (1012,2,5), (1013,3,3), (1014,4,6), (1015,5,8), (1016,6,2); Code used to view the tables with inserted Data in them. SELECT * FROM Customer; SELECT * FROM Donut; SELECT * FROM OrderLineItem; SELECT * FROM Orders; Screen Shot of SQL fiddle showing the Code for Inserting Fictitious Data in to the tables and the Code to recall the Tables. Note: I apologize for the screen shot being so small, the result was way too big so I had to zoom out to be able to capture all the data in one screen shot. Here is the screen shot above in sections since that one was so big. Section1 Section 2 SECTION G: SQL Code for the Simple SFW queries. SFW Code For a single Row. SELECT CustomerID FROM customer WHERE CustomerID= 1002; SELECT * FROM customer WHERE CustomerID= 1002; SFW For a single Row Screen Shot. SFW Code for a single table. SELECT * FROM Customer; SFW Code for Customer Table Screen shot. SFW Code for a Donut table. SELECT FROM Donut; SFW Code for Donut Table Screen Shot. SFW Code for OrderLineItem table. SELECT * FROM OrderLineItem; SFW Code for OrderLineItem Table Screen Shot. SFW Code for Orders table. SELECT * FROM Orders; SFW Code for Orders Table Screen Shot. SECTION G: SQL Code for a complex JOIN Query to display all the information contained in the “Sales Order Form” SELECT orders.Orderdate, orders.orderID, orders.notes, customer.*, orderlineitem.quantity, donut.* FROM customer INNER JOIN orderlineitem INNER JOIN orders ON customer.customerID = Orders.CustomerID AND orders.orderID = orderlineitem.orderID INNER JOIN donut ON orderlineitem.donutID = donut.donutID SECTION G SQL code for complex JOIN query screen shot. [Show More]

Last updated: 2 years ago

Preview 1 out of 29 pages