Designate primary keys and foreign keys

C-170 Data Management Application Assessment

A. Construct a normalized model to represent the donut shop smartphone application database that supports the scenario above by doing the following:
1. Using the attached “Sales Order Form” (unnormalized order form) for the donut ordering database as a reference, produce the final logical schema for this database by doing the following:

• Do not include the calculated attributes/fields (Line Total, Subtotal, Sales Tax and Total) in your normalization diagrams.

i. Explain how you designed the table (suggested length of 1 paragraph).

i. Explain how you designed the tables (suggested length of 1 paragraph).

B. Create an entity-relationship (E-R) diagram, using the tables you designed in third normal form from part A1c, that fulfills the following requirements:

3. Label relationships drawn between the entities with a relationship name.

Note: The cardinality of each relationship needs to be identifiable.

a. Demonstrate that you have tested your code from part C1 by providing a screenshot of your results.

CREATE TABLE CUSTOMER ( CUSTOMER_ID INT IDENTITY NOT NULL CONSTRAINT
PK_CUSTOMER_ID PRIMARY KEY (CUSTOMER_ID), FIRST_NAME VARCHAR(20) NOT NULL, LAST_NAME VARCHAR(20) NOT NULL, STREET_ADDRESS VARCHAR(50) NOT NULL,
APARTMENT VARCHAR(10), ZIP VARCHAR(5) NOT NULL, CITY VARCHAR(30) NOT NULL, STATE VARCHAR(2) NOT NULL, HOME_PHONE VARCHAR(15), MOBILE_PHONE VARCHAR(15) NOT NULL, OTHER_PHONE VARCHAR(15) );
ii) CREATE TABLE DONUT ( DONUT_ID INT NOT NULL CONSTRAINT PK_DONUT_ID PRIMARY KEY (DONUT_ID), DONUT_NAME VARCHAR(30) NOT NULL, DONUT_DESCRIPTION VARCHAR(100) NOT NULL, UNIT_PRICE DECIMAL(4,2) NOT NULL );
iii) CREATE TABLE ORDER_HEADER ( ORDER_ID INT NOT NULL CONSTRAINT PK_ORDER_ID PRIMARY KEY (ORDER_ID), CUSTOMER_ID INT NOT NULL CONSTRAINT FK_CUSTOMER_ID FOREIGN KEY REFERENCES CUSTOMER(CUSTOMER_ID) ON DELETE CASCADE, ORDER_DATE DATETIME, SPECIAL_HANDLING_NOTES VARCHAR(100) );
iv) CREATE TABLE ORDER_LINE ( ORDER_ID INT NOT NULL CONSTRAINT FK_ORDER_ID FOREIGN KEY REFERENCES ORDER_HEADER(ORDER_ID) ON DELETE CASCADE, DONUT_ID INT NOT NULL CONSTRAINT FK_DONUT_ID FOREIGN KEY REFERENCES DONUT(DONUT_ID) ON DELETE CASCADE, CONSTRAINT PK_ORDER_LINE_ID PRIMARY KEY (ORDER_ID, DONUT_ID ), QUANTITY INT );

a. Demonstrate that you have tested your code from part D1 by providing a screenshot of your results.

E. Develop SQL code to create an “Index” for the donut name field using the table that contains the donut information fields by doing the following:
1. Provide the SQL code you wrote to create the “Index” for the donut information.

'12-34-56', '78- 90-11', NULL);

INSERT INTO DONUT VALUES(500, 'Cream', 'Finished with cream frosting', 1.23);

SELECT * FROM CUSTOMERS; SELECT * FROM DONUT; SELECT * FROM ORDER_HEADR; SELECT *

FROM ORDER_LINE;
a. Demonstrate that you have tested your code from part G1 by providing a screenshot of your results.

C.OTHER_PHONE) AS CONTACTS, OL.QUANTITY, D.DONUT_ID, D.DONUT_NAME,

D.DONUT_DESCRIPTION, D.UNIT_PRICE FROM CUSTOMER C INNER JOIN ORDER_HEADER OH ON

Note: You can use any word-processing or other program of your choice to compile each part of the assessment. Please clearly label each part. Please save your document as a *.pdf (Portable Document Format) file before submitting.

File Restrictions
File name may contain only letters, numbers, spaces, and these symbols: ! - _ . * ' ( )
File size limit: 200 MB
File types allowed: doc, docx, rtf, xls, xlsx, ppt, pptx, odt, pdf, txt, qt, mov, mpg, avi, mp3, wav, mp4, wma, flv, asf, mpeg, wmv, m4v, svg, tif, tiff, jpeg, jpg, gif, png, zip, rar, tar, 7z