SQL Database diagram 1NF, 2NF, 3NF, and SQL commands with SQL Fiddle, computer science homework help

For this assessment, you will be creating models, databases,tables, and query reports for a smartphone application.

To complete this assessment, you will be using SQL Fiddle, an online SQL tool,to test run the MySQL code that you will develop for parts C through G. Afterrunning the code, you will take a screenshot of your results, which should becopied into the document that you will submit to TaskStream for thisassessment. Instructions for how to use this tool for each part of theassessment is included in the attached document “SQL Fiddle Instructions.”Please note that for each part of the assessment, there are explicitinstructions on what SQL code you will need to copy and paste into SQL Fiddlepanels to run your test.

The work you complete for each part of the assessment (i.e., the designmodels/diagrams, tables, written explanations, SQL script code, and screenshotresults from running your SQL scripts in SQL Fiddle) should be saved as asingle *.pdf (Portable Document Format) file that you will submit toTaskStream.

Scenario:

You are the database designer and developer for a donut shop that wants tocreate a smartphone application where customers can order donuts. First, youwill design a normalized entity-relationship (E-R) logical database model tostore data related to the customer, donuts, and donut order. Next, you willcreate three tables with primary and foreign keys that are derived from yourE-R model. Once the tables have been built, then you will create views andindexes to protect and fine-tune query performance. You will populate each ofthe tables with sample data. Finally, you will create both a simple“select-from-where” (sfw) query and a complex join query to produce meaningfulreports on individual donut orders and summaries to determine which donuts sellthe best.

Requirements:

A. Constructa normalized model to represent the donut shop smartphone application databasethat supports the scenario above by doing the following:

1. Usingthe unnormalized order form (“Sales Order Form” attached below) for the donutordering database as a reference, produce the final logical schema for thisdatabase by doing the following:

Note: You can design thetables using any method of your choice (e.g., using a drawing tool usingtables).

a. Designa table that is in1st normalformand fulfills thefollowing requirements:

• The table should have a primary key that uniquely identifies the records.

• The values in each of the columns should be atomic.

• There should be no repeating groups.

i. Write aoneparagraph explanation of how youdesigned the table.

b. Designat leasttwotables that are in2nd normal formand fulfill the followingrequirements:

• The tables should meet all of the requirements for the 1st normal form.

• Redundant data appearing in multiple rows should be eliminated with thecreation of the new tables.

• Each of the tables should be related using foreign keys.

i. Write aoneparagraph explanation of how youdesigned the table

c. Designat leastthreenormalized tables that are in3rd normalform and fulfill the followingrequirements:

• The tables should meet all of the requirements for the 1st and 2nd normalforms.

• Fields that do not depend on the primary key or any other field should beeliminated.

• There should be at least three resulting tables, one for customer information,one for donut information, and one for order information.

i. Write aoneparagraph explanation of how youdesigned the table

B. Usingthe tables you designed in3rdnormalform from part A1c,create an entity-relationship (E-R) diagram that fulfills the followingrequirements:

Note: You can use any drawingtool of your choice to create the diagram.

• Entities should be drawn that representeachof the tables from the 3rd normalizedform.

• All appropriate fields (i.e., attributes) should be entered into each of theentities.

• Primary keys (PK) and foreign keys (FK) should be appropriately designated.

• Data types foreachattribute (Numeric, Fixed, Char,Varchar, or Timestamp) should be appropriately designated.

• Relationships drawn between the entities should be labeled with a relationshipname.

1. Providea written explanation for the following:

a. Explainwhy you selected the entities represented in your diagram.

b. Explainhow you determined the relationships between these entities.

c. Explainwhat the cardinality is foreachof the relationships.

C. Developthe SQL code foreachof the tables you designed in part Aand refined in part B by doing the following:

1. Providethe SQL code you wrote foreachtable.

Note: Make sure all of theprimary and foreign keys and data types are assigned to each of the fields inthe create table SQL code.

a. Demonstratethat you have used SQL Fiddle to test your code from part C1 by providing ascreenshot of your results.

D. Usingthe table that contains the customer information fields, develop SQL code tocreate a View that shows all of the customer information with the First Nameand Last Name concatenated (CONCAT()) to show full name as one field by doingthe following:

1. Providethe SQL code you wrote to create the View for the customer information.

a. Demonstratethat you have used SQL Fiddle to test your code from part D1 by providing ascreenshot of your results.

E. Usingthe table that contains the donut information fields, develop SQL code tocreate an Index for the donut name field by doing the following:

1. Providethe SQL code you wrote to create the Index for the donut information.

a. Demonstratethat you have used SQL Fiddle to test your code from part E1 by providing ascreenshot of your results.

F. Usingall of the tables you developed in part C, develop SQL code to populateeachof the tables by doing the following:

1. Providethe SQL code that inserts data into all of the tables.

Note: Make sure that data isinserted first into the table(s) with primary keys before inserting data intothe other tables.

a. Demonstratethat you have used SQL Fiddle to test your code from part F1 by providing ascreenshot of your results.

G. Usingthe tables you populated in part F, develop SQL code to display the values in arequested  table or tables by doing the following:

1. Providethe SQL code for the simple (sfw) queries to display all of the data in each ofthe tables you have created and populated.

a. Demonstratethat you have used SQL Fiddle to test your code from part G1 by providing ascreenshot of your results.

2. Providethe SQL code for a complex join query to display all of the informationcontained in the attached “Sales Order Form.”

a. Demonstratethat you have used SQL Fiddle to test your code from part G2 by providing ascreenshot of your results.

H. Submitparts A–G as a *.pdf (Portable Document Format) to TaskStream.

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