Database SQL Select and Join Query

Stuck on the last part of this homework, I’ll submit all my code so far, I feel good about my code but any changes are welcome, but mostly focusing on the last part of the homework.

 

BE SURE TO INCLUDE all of the fields from the donut sales order form in your normalization 

design and complex join query EXCEPT the calculated attributes/fields

(Line Total, Subtotal, 

Sales Tax and Total)

CREATE TABLE IF NOT EXISTS `Customers` (
`CustomerID` int NOT NULL AUTO_INCREMENT,
`CustomerLastName` varchar(50) NOT NULL,
`CustomerFirstName` varchar(50) NOT NULL,
`CustomerAddress` varchar(100) NOT NULL,
`AptNum` varchar(20) NOT NULL,
`City` varchar(50) NOT NULL,
`State` varchar(3) NOT NULL,
`ZipCode` varchar(9) NOT NULL,
`HomePhone` varchar(15),
`MobilePhone` varchar(15),
`OtherPhone` varchar(15),
PRIMARY KEY (`CustomerID`)
) DEFAULT CHARSET=utf8;
INSERT INTO `Customers` (`CustomerLastName`, `CustomerFirstName`, `CustomerAddress`, `AptNum`,
`City`, `State`, `ZipCode`, `HomePhone`, `MobilePhone`, `OtherPhone`) VALUES
(‘Martinez’, ‘Jose’, ‘10201 W. Beaver ST’, ‘147’, ‘Jacksonville’, ‘FL’, ‘32220’, ”, ‘904-444-2137’, ”)
—————————————————–
SELECT *
FROM `Customers`

 

 

 

 

 

 

 

 

 

[1]

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1

$1.50

$1.75

3

$1.75

4

5

$1.75

5

 

 

 

Sales Order

Donuts-R-Us

Date:

May 6 , 2 0 1 4

You eat them fresh, we bake them fresh.

Donut Order ID:

[1]

Customer ID:

Customer:

[First Name] [Last Name]

[Street Address] [Apt. #]

[City, ST ZIP Code]

[Home Phone] [Mobile Phone] [Other Phone]

Qty

Donut ID

Name

Description

Unit Price

Line Total

1

Plain

Plain Donut

$1. 5 0

5

2

Glazed

Glazed Donut

$1.75

$8.75

12

3

Cinnamon

Cinnamon Donut

$21.00

4

Chocolate

Chocolate Donut

$5.25

Sprinkle

Sprinkle Donut

$7.00

6

Gluten-Free

Gluten-Free Donut

$2.00

$10.00

Subtotal

$53.50

Sales Tax

10%

Total

$58.85

Special Handling Notes:

Please include plates and napkins.

] Thank you for your business!

C170:Step-by-Step Instructions

Please review the detailed instructions below which provide additional information/insight about each of the
sections. Hopefully, you will have a better idea of what we’re asking you to do for this project.

Here we go! 

Introduction

For this project, you are charged with creating a new sales order database from start to finish for a donut
shop. You’ll be working through all of the phases of the database design cycle: conceptual, logical, and physical. As
you recall, there are three phases in database design, each phase accomplishing a specific deliverable for the
database. Once your database is designed, you’ll get into the really fun stuff – executing real SQL queries on your
own finished database! How cool is that?!

The intro just provides you with a high-level overview of the task at hand, including the tools and processes that are
required to meet the project requirements.

Scenario

For this task, you are the DB designer and developer for a donut shop that is creating an online ordering app for
donuts. You’ll first be developing the conceptual and logical aspects of the database, normalized E-R diagrams, then
you’ll be doing the fun stuff – creating the tables using SQL queries, running queries on the tables, and inserting data
into the database tables.

This scenario is going to require you to create FOUR TABLES with primary and foreign keys, based on the Donuts ‘R Us
sales order form. Your tables should include a donut information table, a customer information table, a sales order
information table and one additional table (which you will need to define), per the section requirements that follow.

Requirements

This section provides you with step-by-step instructions for designing and developing the sales order database for
Donuts ‘R Us. This is a project, not a test, so feel free to refer back to the textbook for any concepts you’re not clear
on or to help support your database design explanations. 

Section A

For Section A, you will be creating a normalized model to represent the database based on the Sales Order Form for
Donuts ‘R Us. It may be helpful to use paper and pencil to begin to sketch out your tables and E-R Diagrams, if that’s
easier for you to do before you begin creating them in Word.

This section requires you to design ONE (and only one) TABLE in 1NF, THREE (and only
three) TABLES in 2NF, and FOUR (and only four) TABLES in 3NF (fully normalized). Basically,
you are taking the Sales Order Form, normalizing it into 1NF, then normalizing into 2NF, then completing the
normalization process by normalizing it into 3NF.

BE SURE TO INCLUDE all of the fields and from the donut sales order form in your
normalization design and complex join query EXCEPT the calculated attributes/fields (Line
Total, Subtotal, Sales Tax and Total). ONLY INCLUDE THE ATTRIBUTES ON THE SALES
ORDER FORM. DO NOT ADD ANY ATTRIBUTES TO YOUR DESIGN. DO NOT OMIT ANY
ATTRIBUTES FROM YOUR DESIGN. If you add or leave out any of the other fields on the

donut sales order form from your normalization diagram or complex join query, it will be returned to you
for

revisions.

Be sure you include your one paragraph explanation providing rationale for how you designed and normalized each
table.

For a review of the normalization concepts required to do this section, please study
the Applying the Normalization case study which provides an in-depth review of the
process you will need to use in order to complete this section.

Section B

Once you have your 3NF tables, you’re ready to move on and create the E-R Diagram for the database. Be sure you
are using your 3NF tables, include all attributes, and designate the primary and foreign keys properly (according to
how the text designates them for E-R Diagrams). Data types should be included, and the relationships with
cardinality should be drawn between the entities and labeled with the relationship name.

Please watch the Understanding Relationship Types and Diagramming Relationships
with Entity-Relationship Diagrams videos which are part of FileMaker Pro 13
Essential Training Lynda.com video course (for information on how to access
Lynda.com video courses using your WGU credentials, click here). These videos are
not generic videos about the entity-relationship diagram process; these videos

“speak” directly to your project.

Don’t forget the written explanation for why you selected the entities for your diagram and be sure to include how
you determined the relationships between the entities and what the cardinality is for each of the relationships (1:1,
1:n, m:n). You can and should refer back to the textbook to help you through this section.

Section C

Now comes the fun part! You have now entered the physical design phase – where you actually build your database
from the ground up, using the tools created in Sections A and B.
Section C wants you to develop the SQL code to build the tables for the database!!! You’ll be using data definition
language (DDL) to create your tables. Be sure you to provide the SQL code in your Word document, AND use the SQL
Fiddle program to test your code! For instructions on how to use SQL Fiddle, please review the attached
instructions. If SQL Fiddle is offline or not working for some reason, then feel free to use another relational database
management system; however, be sure to mention in your submission that SQL Fiddle was offline and thus the
reason you had to use another application to verify your code. Don’t forget to take a screen shot (on a Windows
computer, this is done by using the Print Screen (or PrtSc) button or Alt + Print Screen to copy only the selected
window) and include it in your Word document.

Please do Lab 3.2.5 which help you review the syntax for creating tables. For a brief
tutorial on foreign keys, please study the MySQLTutorial.org Foreign Key tutorial.

https://srm–c.na13.visual.force.com/apex/coursearticle?Id=kA0a0000000PKmsCAG

http://www.google.com/url?q=http%3A%2F%2Fwww.lynda.com%2FFileMaker-Pro-tutorials%2FUnderstanding-relationship-types%2F161168%2F172251-4.html&sa=D&sntz=1&usg=AFrqEzcAy_p4GnA8q6VX_8NVa4vDIudomg

http://www.google.com/url?q=http%3A%2F%2Fwww.lynda.com%2FFileMaker-Pro-tutorials%2FDiagramming-relationships-entity-relationship-ER-diagrams%2F161168%2F172252-4.html&sa=D&sntz=1&usg=AFrqEzc2Cg2hF5_i_Ofy2AUCZBfyeI1Wcg

http://www.google.com/url?q=http%3A%2F%2Fwww.lynda.com%2FFileMaker-Pro-tutorials%2FDiagramming-relationships-entity-relationship-ER-diagrams%2F161168%2F172252-4.html&sa=D&sntz=1&usg=AFrqEzc2Cg2hF5_i_Ofy2AUCZBfyeI1Wcg

https://drive.google.com/a/wgu.edu/file/d/0B2Euqa9j964zeFJHbUhPb3lkem8/view?usp=sharing

http://sqlfiddle.com/

http://sqlfiddle.com/

https://srm–c.na13.content.force.com/servlet/fileField?id=0BEa0000000Gtym

https://srm–c.na13.content.force.com/servlet/fileField?id=0BEa0000000Gtym

https://srm–c.na13.content.force.com/servlet/fileField?id=0BEa0000000Gtym

http://www.mysqltutorial.org/mysql-foreign-key/

Sample SQL Fiddle Screenshot

Section D

More fun with SQL! In this section, you’ll be using SQL to create a view on your customer information table. Your
view should show all of the customer information and should utilize the concatenation clause, CONCAT(), to
concatenate the first name and last name as one field within your view. Don’t forget to type your SQL code into your
Word document and demonstrate that you’ve executed your code in SQL Fiddle via a screen shot pasted into your
Word document. For instructions on how to use SQL Fiddle to complete this section, please review the attached
instructions.

Please do Lab 4.5.1 which will take you step-by-step through the process that you
will need to use in order to complete this section. For another great resource on
how to create a view, please visit the MySQL Helps/Information page and check out
the Creating Views in MySQL tutorial or the Lynda.com MySQL Essential Training
video course (for information on how to access Lynda.com video courses using your

WGU credentials, click here).

Sample SQL Fiddle Screenshot

http://sqlfiddle.com/

https://srm–c.na13.content.force.com/servlet/fileField?id=0BEa0000000Gtym

https://srm–c.na13.content.force.com/servlet/fileField?id=0BEa0000000Gtym

https://srm–c.na13.content.force.com/servlet/fileField?id=0BEa0000000Gtym

https://srm–c.na13.visual.force.com/apex/coursearticle?Id=kA0a0000000PKmnCAG

http://www.google.com/url?q=http%3A%2F%2Fwww.lynda.com%2FMySQL-tutorials%2FMySQL-Essential-Training%2F139986-2.html&sa=D&sntz=1&usg=AFrqEzdQjIzjGOkxHU3WfyhnE9KYLImjHw

https://drive.google.com/a/wgu.edu/file/d/0B2Euqa9j964zeFJHbUhPb3lkem8/view?usp=sharing

Section E

This section applies SQL code to the donut information table to create an index the donut name column.

Again, remember to type your code in your Word document as well as provide a screen shot after you’ve executed
the code in SQL Fiddle. For instructions on how to use SQL Fiddle to complete this section, please review the
attached instructions.

Please do Lab 5.3.1 which will take you step-by-step through the process that you
will need to use in order to complete this section. For another great resource on
how to create an index, please visit the MySQL Helps/Information page and check
out the Creating MySQL Indexes tutorial or the Lynda.com MySQL Essential Training
video course (for information on how to access Lynda.com video courses using your

WGU credentials, click here).
Sample SQL Fiddle Screenshot

Section F

Now for the really fun part – inserting the data!  You’ll be using the data manipulation language (DML) INSERT
statement to insert the data into your tables.

The data is found on the Sales Order Form. You may need to make up the customer information, or use your own
data if you’d prefer. You will need at least one customer in your database, in order to place an order. Be sure you’re
inserting the product data for the donuts into the donut information table, the customer data into the customer
information table, and the sales data into the sales order table.

Again, be sure you’re typing your SQL code for inserting data into each table into your Word document and taking
screen shots when you execute each query on the tables in SQL Fiddle. For instructions on how to use SQL Fiddle to
complete this section, please review the attached instructions.

Please do Lab 3.3.1 which will take you step-by-step through the process that you
will need to use in order to complete this section. For another great resource on
how to insert data into your tables, please visit the MySQL Helps/Information page
and check out the Inserting Data into Tables Using MySQL INSERT Statement tutorial
or the Lynda.com MySQL Essential Training video course (for information on how to

access Lynda.com video courses using your WGU credentials, click here).

http://sqlfiddle.com/

https://srm–c.na13.content.force.com/servlet/fileField?id=0BEa0000000Gtym

https://srm–c.na13.content.force.com/servlet/fileField?id=0BEa0000000Gtym

https://srm–c.na13.visual.force.com/apex/coursearticle?Id=kA0a0000000PKmnCAG

http://www.google.com/url?q=http%3A%2F%2Fwww.lynda.com%2FMySQL-tutorials%2FMySQL-Essential-Training%2F139986-2.html&sa=D&sntz=1&usg=AFrqEzdQjIzjGOkxHU3WfyhnE9KYLImjHw

https://drive.google.com/a/wgu.edu/file/d/0B2Euqa9j964zeFJHbUhPb3lkem8/view?usp=sharing

http://sqlfiddle.com/

https://srm–c.na13.content.force.com/servlet/fileField?id=0BEa0000000Gtym

https://srm–c.na13.content.force.com/servlet/fileField?id=0BEa0000000Gtym

https://srm–c.na13.visual.force.com/apex/coursearticle?Id=kA0a0000000PKmnCAG

http://www.google.com/url?q=http%3A%2F%2Fwww.lynda.com%2FMySQL-tutorials%2FMySQL-Essential-Training%2F139986-2.html&sa=D&sntz=1&usg=AFrqEzdQjIzjGOkxHU3WfyhnE9KYLImjHw

https://drive.google.com/a/wgu.edu/file/d/0B2Euqa9j964zeFJHbUhPb3lkem8/view?usp=sharing

Sample SQL Fiddle Screenshot

Section G

Now that you have data in the tables, you can manipulate it!  Data manipulation language (DML) is used to
SELECT, INSERT, UPDATE, or DELETE data within tables. Which query do you think you’ll need to display the values
for each table? You will need to choose the appropriate query type to display the values in each of your normalized
tables.

BE SURE TO INCLUDE all of the fields from the donut sales order form in your normalization
design and complex join query EXCEPT the calculated attributes/fields (Line Total, Subtotal,
Sales Tax and Total). If you leave out any of the other fields on the donut sales order form
from your normalization diagram or complex join query, it will be returned to you for

revisions.

As with the other sections, be sure you type the query for each table into your Word document, AND do a screen
shot after you execute each query in SQL Fiddle. For instructions on how to use SQL Fiddle to complete
this section, please review the attached instructions.

For a review of the concepts required to do G1, please do Lab 3.4.1 which will take
you step-by-step through the process that you will need to use in order to complete
this section. For a review of the concepts required to do G2, please do Lab 4.4.1 to
step through the process that you will need to use in order to complete this
section. For another great resource on how to use the INNER JOIN clause to select

data from multiple tables based on join conditions, please visit the MySQL Helps/Information
page and check out the MySQL Inner Join tutorial or the Lynda.com MySQL Essential Training
video course (for information on how to access Lynda.com video courses using your WGU
credentials, click here).

http://sqlfiddle.com/

https://srm–c.na13.content.force.com/servlet/fileField?id=0BEa0000000Gtym

https://srm–c.na13.content.force.com/servlet/fileField?id=0BEa0000000Gtym

https://srm–c.na13.visual.force.com/apex/coursearticle?Id=kA0a0000000PKmnCAG

http://www.google.com/url?q=http%3A%2F%2Fwww.mysqltutorial.org%2Fmysql-inner-join.aspx&sa=D&sntz=1&usg=AFrqEzc9PbbaYzy0TOWMV_sox-MN46kFPQ

http://www.google.com/url?q=http%3A%2F%2Fwww.lynda.com%2FMySQL-tutorials%2FMySQL-Essential-Training%2F139986-2.html&sa=D&sntz=1&usg=AFrqEzdQjIzjGOkxHU3WfyhnE9KYLImjHw

https://drive.google.com/a/wgu.edu/file/d/0B2Euqa9j964zeFJHbUhPb3lkem8/view?usp=sharing

Sample SQL Fiddle Screenshot

Section H

CONGRATULATIONS!!! You’re done!!!  Great job completing this project! If you did everything right, your queries
executed without any errors and you have a completed Word document that includes your normalized table and
explanations, E-R Diagrams and explanations, SQL queries, SQL query result screen shots (from SQL Fiddle).

Now, all you need to do is convert the document to a PDF. This is relatively easy in Word. Just click on “Save as” and
select “PDF”. Name your PDF and submit it to TaskStream!

TIPS AND TRICKS:
Finally, I thought I would include a few tips and tricks for you to refer to as you work on your project:

 CAREFULLY read through ALL of the instructions and sections for the C170 – VHT1 task in the COS. Print them
out, if you need to, and keep them close as you work through each section.

 Print out the rubric and refer to it OFTEN. The rubric will be used to evaluate your project, so it’s beneficial
to follow it CLOSELY as you complete each section in the project.

 Work on this project one section at a time and refer back to the textbook as you work through each section –
the textbook is your friend and you can use it as your guide as you complete the project!

I hope this has provided you with a better idea of what this project entails and an explanation of each section’s
requirements. I understand that this is a big project and may seem a little daunting, but remember, you cannot eat
an entire pie with just one bite – but you can do it by taking many smaller slices over time. 

http://sqlfiddle.com/

SET foreign_key_checks = 0;
CREATE TABLE IF NOT EXISTS `Customers` (
`CustomerID` int NOT NULL AUTO_INCREMENT,
`CustomerLastName` varchar(50) NOT NULL,
`CustomerFirstName` varchar(50) NOT NULL,
`CustomerAddress` varchar(100) NOT NULL,
`AptNum` varchar(20) NOT NULL,
`City` varchar(50) NOT NULL,
`State` varchar(3) NOT NULL,
`ZipCode` varchar(9) NOT NULL,
`HomePhone` varchar(15),
`MobilePhone` varchar(15),
`OtherPhone` varchar(15),
PRIMARY KEY (`CustomerID`));
INSERT INTO `Customers` (`CustomerLastName`, `CustomerFirstName`, `CustomerAddress`, `AptNum`,
`City`, `State`, `ZipCode`, `HomePhone`, `MobilePhone`, `OtherPhone`)
VALUES (‘Martinez’, ‘Jose’, ‘10201 W. Beaver ST’, ‘147’, ‘Jacksonville’, ‘FL’, ‘32220’, ”, ‘904-444-2137’, ”),
(‘Doe’, ‘Joe’, ‘4241 Migration DR’, ’10’, ‘Jacksonville’, ‘FL’, ‘32257’, ”, ‘904-521-8255’, ”);

CREATE TABLE IF NOT EXISTS `Donuts` (
`DonutID` int NOT NULL AUTO_INCREMENT,
`DonutName` VARCHAR (100),
`Description` VARCHAR (100),
`UnitPrice` Decimal (10,2),
PRIMARY KEY (`DonutID`));
INSERT INTO `Donuts` (`DonutName`, `Description`, `UnitPrice`)
VALUES (‘Plain’, ‘Plain Donut’, ‘1.50’),
(‘Glazed’, ‘Glaze Donut’, ‘1.75’),
(‘Cinnamon’, ‘Cinnamon Donut’, ‘1.75’),
(‘Chocolate’, ‘Chocolate Donut’, ‘1.75’),
(‘Sprinkle’, ‘Sprinkle Donut’, ‘1.75’),
(‘Gluten-Free’, ‘Gluten-Free Donut’, ‘2.00’);
CREATE INDEX Name ON Donuts (DonutName);

CREATE TABLE IF NOT EXISTS `OrdersQty` (
`DonutOrderID` int NOT NULL AUTO_INCREMENT,
`DonutID` int NOT NULL,
`Qty` int NOT NULL,
PRIMARY KEY (`DonutOrderID`, `DonutID`),
FOREIGN KEY (`DonutID`) REFERENCES Donuts (DonutID),
INDEX Donuts(DonutID));
INSERT INTO OrdersQty (DonutID, Qty)
VALUES ((SELECT DonutID FROM Donuts WHERE DonutID = 1), 1),
((SELECT DonutID FROM Donuts WHERE DonutID = 2), 5),
((SELECT DonutID FROM Donuts WHERE DonutID = 3), 12),
((SELECT DonutID FROM Donuts WHERE DonutID = 4), 3),
((SELECT DonutID FROM Donuts WHERE DonutID = 5), 4),
((SELECT DonutID FROM Donuts WHERE DonutID = 6), 5);

CREATE TABLE IF NOT EXISTS `Orders` (
`DonutOrderID` int NOT NULL AUTO_INCREMENT,
`OrderDate` DATETIME NOT NULL,
`CustomerID` int NOT NULL,
`Notes` varchar(250),
PRIMARY KEY (`DonutOrderID`),
INDEX Customers (CustomerID),
FOREIGN KEY (`CustomerID`) REFERENCES Customers (CustomerID),
INDEX OrdersQTY (DonutOrderID)
);
INSERT INTO Orders (DonutOrderID, OrderDate, CustomerID, Notes)
VALUES ((SELECT DonutOrderID FROM OrdersQty WHERE DonutOrderID = 1), ‘2018/08/03’, (SELECT CustomerID FROM Customers WHERE CustomerID = 1), ‘This is a test order’);
CREATE VIEW CustInfo AS
Select CONCAT(CustomerFirstName, ‘ ‘, CustomerLastName) AS CustomerName,
CustomerAddress,
AptNum,
City,
State,
ZipCode,
HomePhone,
MobilePhone,
OtherPhone,
CustomerID
from Customers
ORDER BY CustomerID;

Running head: Donuts-r-us database app 1

Donuts-r-us database app 7

Donuts-R-Us Database App

Jose W. Martinez

Western Governors University

Donuts-R-Us Database App

First Normal Form (1NF)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1 NF

PK

DonutOrderID

OrderDate

CustomerID

CustomerLastName

CustomerFirstName

CustomerAddress

AptNum

City

State

ZipCode

HomePhone

MobilePhone

OtherPhone

Notes

DonutID

DonutName

Qty

Description

UnitPrice

1NF – Description

PK

DonutOrderID

 

OrderDate

 

CustomerID

 

CustomerLastName

 

CustomerFirstName

 

CustomerAddress

 

AptNum

 

City

 

State

 

ZipCode

 

HomePhone

 

MobilePhone

 

OtherPhone

 

Notes

PK

DonutOrderID

PK

 

Qty

PK

DonutID

 

DonutName

 

Description

 

UnitPrice

2 NF
			DonutID

2NF – Description

PK

DonutOrderID

 

OrderDate

 

Notes

PK

CustomerID

 

CustomerLastName

 

CustomerFirstName

 

CustomerAddress

 

AptNum

 

City

 

State

 

ZipCode

 

HomePhone

 

MobilePhone

 

OtherPhone

PK

DonutOrderID

FK

DonutID

 

Qty

PK

DonutID

 

DonutName

 

Description

 

UnitPrice

3NF
	FK		CustomerID

3NF – Description

References
Poe’s Short Stories. (n.d.). Retrieved August 04, 2017, from https://www.cliffsnotes.com/literature/p/poes-short-stories/edgar-allan-poe-biography
MindEdge, Inc. (2014). Introduction to the humanities. Waltham, MA: MindEdge.
Edgar Allan Poe, “The Fall of the House of Usher” (reprint), The Works of the Late Edgar Allan Poe (1850), 1:291-309

OrdersCustomerOrder QtyDonutsDonutOrderID INTPKOrderDate DATETIMECustomerID INTFKCustomerID INTPKCustomerLastName VARCHAR (50)CustomerLastName VARCHAR (50)DonutOrderID INTFKDonutID INTFKQty INTDonutID INTPKDonutName VARCHAR (100)Description VARCHAR (100)CutomerAddress VARCHAR (100)AptNum VARCHAR (20)State VARCHAR (3)City VARCHAR (50)ZipCode VARCHAR (9)HomePhone VARCHAR (15)MobilePhone VARCHAR (15)OtherPhone VARCHAR (15)Notes VARCHAR (250)UnitPrice DECIMAL (10,2)

Orders
Customer
Order Qty
Donuts
DonutOrderID INT
int
FK
PK
OrderDate DATETIME
int
FK
PK
CustomerID INT
int
FK
PK
CustomerID INT
int
FK
PK
CustomerLastName VARCHAR (50)
int
FK
PK
CustomerLastName VARCHAR (50)
int
FK
PK
DonutOrderID INT
int
FK
PK
DonutID INT
int
FK
PK
Qty INT
int
FK
PK
DonutID INT
int
FK
PK
DonutName VARCHAR (100)
int
FK
PK
Description VARCHAR (100)
int
FK
PK
CutomerAddress VARCHAR (100)
int
FK
PK
AptNum VARCHAR (20)
int
FK
PK
State VARCHAR (3)
int
FK
PK
City VARCHAR (50)
int
FK
PK
ZipCode VARCHAR (9)
int
FK
PK
HomePhone VARCHAR (15)
int
FK
PK
MobilePhone VARCHAR (15)
int
FK
PK
OtherPhone VARCHAR (15)
int
FK
PK
Notes VARCHAR (250)
int
FK
PK
UnitPrice DECIMAL (10,2)
int
FK
PK
M1
M2
M3
M4
M1
M2
M3
M4
M1
M2
M3
M4

Running head: Donuts-r-us database app 1

Donuts-r-us database app 7

Donuts-R-Us Database App

Jose W. Martinez

Western Governors University

Donuts-R-Us Database App

First Normal Form (1NF)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1 NF

PK

DonutOrderID

OrderDate

CustomerID

CustomerLastName

CustomerFirstName

CustomerAddress

AptNum

City

State

ZipCode

HomePhone

MobilePhone

OtherPhone

Notes

DonutID

DonutName

Qty

Description

UnitPrice

1NF – Description

PK

DonutOrderID

 

OrderDate

 

CustomerID

 

CustomerLastName

 

CustomerFirstName

 

CustomerAddress

 

AptNum

 

City

 

State

 

ZipCode

 

HomePhone

 

MobilePhone

 

OtherPhone

 

Notes

PK

DonutOrderID

PK

 

Qty

PK

DonutID

 

DonutName

 

Description

 

UnitPrice

2 NF
			DonutID

2NF – Description

PK

DonutOrderID

 

OrderDate

 

Notes

PK

CustomerID

 

CustomerLastName

 

CustomerFirstName

 

CustomerAddress

 

AptNum

 

City

 

State

 

ZipCode

 

HomePhone

 

MobilePhone

 

OtherPhone

PK

DonutOrderID

FK

DonutID

 

Qty

PK

DonutID

 

DonutName

 

Description

 

UnitPrice

3NF
	FK		CustomerID

3NF – Description

References
Poe’s Short Stories. (n.d.). Retrieved August 04, 2017, from https://www.cliffsnotes.com/literature/p/poes-short-stories/edgar-allan-poe-biography
MindEdge, Inc. (2014). Introduction to the humanities. Waltham, MA: MindEdge.
Edgar Allan Poe, “The Fall of the House of Usher” (reprint), The Works of the Late Edgar Allan Poe (1850), 1:291-309

OrdersCustomerOrder QtyDonutsDonutOrderID INTPKOrderDate DATETIMECustomerID INTFKCustomerID INTPKCustomerLastName VARCHAR (50)CustomerLastName VARCHAR (50)DonutOrderID INTFKDonutID INTFKQty INTDonutID INTPKDonutName VARCHAR (100)Description VARCHAR (100)CutomerAddress VARCHAR (100)AptNum VARCHAR (20)State VARCHAR (3)City VARCHAR (50)ZipCode VARCHAR (9)HomePhone VARCHAR (15)MobilePhone VARCHAR (15)OtherPhone VARCHAR (15)Notes VARCHAR (250)UnitPrice DECIMAL (10,2)

Orders
Customer
Order Qty
Donuts
DonutOrderID INT
int
FK
PK
OrderDate DATETIME
int
FK
PK
CustomerID INT
int
FK
PK
CustomerID INT
int
FK
PK
CustomerLastName VARCHAR (50)
int
FK
PK
CustomerLastName VARCHAR (50)
int
FK
PK
DonutOrderID INT
int
FK
PK
DonutID INT
int
FK
PK
Qty INT
int
FK
PK
DonutID INT
int
FK
PK
DonutName VARCHAR (100)
int
FK
PK
Description VARCHAR (100)
int
FK
PK
CutomerAddress VARCHAR (100)
int
FK
PK
AptNum VARCHAR (20)
int
FK
PK
State VARCHAR (3)
int
FK
PK
City VARCHAR (50)
int
FK
PK
ZipCode VARCHAR (9)
int
FK
PK
HomePhone VARCHAR (15)
int
FK
PK
MobilePhone VARCHAR (15)
int
FK
PK
OtherPhone VARCHAR (15)
int
FK
PK
Notes VARCHAR (250)
int
FK
PK
UnitPrice DECIMAL (10,2)
int
FK
PK
M1
M2
M3
M4
M1
M2
M3
M4
M1
M2
M3
M4