WGU C170 Data Management – Applications (MySQL) Project 2022

WGU C170 DATA MANAGEMENT-APPLICATIONS(MYSQL)
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 100000844708667 from CourseHero.com on 10-20-2022 23:28:48 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 100000844708667 from CourseHero.com on 10-20-2022 23:28:48 GMT -05:00


https://www.coursehero.com/file/117787214/C170-Timothy-Clarkpdf/