Timothy Clark C170: Data Management – Applications (MySQL) 1A) 1NF RAW MySQL:. 100% proven pass rate.

Document Content and Description Below

Timothy Clark C170: Data Management – Applications (MySQL) 1A) 1NF RAW MySQL: CREATE TABLE Orders_1NF ( OrderID INT NOT NULL AUTO_INCREMENT, DonutID INT NOT NULL, DonutName CHAR(75) NOT NULL ... , Description CHAR(255) NULL, UnitPrice DECIMAL(2,2) NULL, Quantity INT NOT NULL, OrderDate DATE NOT NULL, SpecialHandlingNotes CHAR(255) NULL, CustomerID INT NULL, CustomerFirstName CHAR(50) NULL, CustomerLastName CHAR(50) NULL, CustomerStreetAddress1 CHAR(50) NULL, CustomerStreetAddress2 CHAR(50) NULL, CustomerCity CHAR(50) NULL, CustomerState CHAR(2) NULL, CustomerZip CHAR(6) NULL, CustomerHomePhone CHAR(10) NULL, CustomerMobilePhone CHAR(10) NULL, CustomerOtherPhone CHAR(10) NULL, CONSTRAINT PK_Orders_1NF PRIMARY KEY (OrderID, DonutID) ); Explanation: The above code was reached after the following considerations. Firstly, I reviewed the Sales Order Form which was provided with this assignment. After reviewing the Order Form, I broke down the sections into individual elements which I then converted into SQL table columns. This will allow orders to populate as Rows into the Orders_1NF table. The primary key selected is derived from OrderID and DonutID creating a composite key with the two data points which enforces complete uniqueness. This study source was downloaded by 100000842525582 from CourseHero.com on 05-11-2022 11:57:29 GMT -05:00 https://www.coursehero.com/file/117787214/C170-Timothy-Clarkpdf/Timothy Clark C170: Data Management – Applications (MySQL) 1B) 2NF - A RAW MySQL: CREATE TABLE Donuts_2NF ( DonutID INT NOT NULL, DonutName CHAR(75) NOT NULL, Description CHAR(255) NULL, UnitPrice DECIMAL(2,2) NULL, CONSTRAINT PK_Donuts_2NF PRIMARY KEY (DonutID) ); 1B) 2NF - B RAW MySQL: CREATE TABLE Orders_2NF ( OrderID INT NOT NULL AUTO_INCREMENT, OrderDate DATE NOT NULL, CustomerID INT NULL, DonutID INT NOT NULL, SpecialHandlingNotes CHAR(255) NULL, CustomerFirstName CHAR(50) NULL, CustomerLastName CHAR(50) NULL, CustomerStreetAddress1 CHAR(50) NULL, CustomerStreetAddress2 CHAR(50) NULL, CustomerCity CHAR(50) NULL, CustomerState CHAR(2) NULL, CustomerZip CHAR(6) NULL, CustomerHomePhone CHAR(10) NULL, CustomerMobilePhone CHAR(10) NULL, CustomerOtherPhone CHAR(10) NULL, CONSTRAINT PK_Orders_2NF PRIMARY KEY (OrderID) ); This study source was downloaded by 100000842525582 from CourseHero.com on 05-11-2022 11:57:29 GMT -05:00 https://www.coursehero.com/file/117787214/C170-Timothy-Clarkpdf/Timothy Clark C170: Data Management – Applications (MySQL) 1B) 2NF – C RAW MySQL: CREATE TABLE LineItems_2NF ( DonutID INT NOT NULL, OrderID INT NOT NULL, QUANTITY INT NOT NULL, CONSTRAINT FK_LineItems_2NF_Order FOREIGN KEY (OrderID) REFERENCES Orders_2NF(OrderID), CONSTRAINT FK_LineItems_2NF_Donut FOREIGN KEY (DonutID) REFERENCES Donuts_2NF(DonutID), CONSTRAINT PK_LineItems_2NF PRIMARY KEY (OrderID,DonutID) ); Explanation: The above code was reached after the following considerations. Firstly, the order data was broken out into three sections. Donut data to be stored separately into a singular table where each row represents an individual item that can be ordered. An Orders table which will hold information specific to each individual sales transaction that occurs as an independent row. Lastly, a line item table that will be utilized to store each specific line item within an order. Two foreign keys are configured to enforce complete uniqueness when a line item is created and linked to a valid order and donut. These two foreign keys combine to create a composite primary key. 1C) 3NF - A RAW MySQL: CREATE TABLE Customers_3NF ( CustomerID INT NULL, CustomerFirstName CHAR(50) NULL, CustomerLastName CHAR(50) NULL, CustomerStreetAddress1 CHAR(50) NULL, CustomerStreetAddress2 CHAR(50) NULL, CustomerCity CHAR(50) NULL, CustomerState CHAR(2) NULL, CustomerZip CHAR(6) NULL, CustomerHomePhone CHAR(10) NULL, CustomerMobilePhone CHAR(10) NULL, CustomerOtherPhone CHAR(10) NULL, CONSTRAINT PK_Customers_3NF PRIMARY KEY (CustomerID) [Show More]

Last updated: 3 years ago

Preview 1 out of 9 pages