Need computer science Help with Database Design

Dec 28th, 2015
SoccerBoss
Category:
Computer Science
Price: $300 USD

Question description

Introduction:

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. After running the code, you will take a screenshot of your results, which should be copied into the document  “SQL Fiddle Instructions.” 
Scenario:

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

Requirements:

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 unnormalized order form (“Sales Order Form” attached below) for the donut ordering database as a reference, produce the final logical schema for this database by doing the following:

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

a. Design a table that is in1st normal formand fulfills the following 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 you designed the table.

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

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

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

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

i.  Write aoneparagraph explanation of how you designed the table

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

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

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

•  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 you designed the table

B. Using the tables you designed in3rd normalform from part A1c, create an entity-relationship (E-R) diagram that fulfills the following requirements:

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

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

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

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

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

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

1. Provide a written explanation for the following:

a. Explain why you selected the entities represented in your diagram.

b. Explain how you determined the relationships between these entities.

c. Explain what the cardinality is foreachof the relationships.

C. Develop the SQL code foreachof the tables you designed in part A and refined in part B by doing the following:

1. Provide the SQL code you wrote foreachtable.

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

a. Demonstrate that you have used SQL Fiddle to test your code from part C1 by providing a screenshot of your results.

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

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

a. Demonstrate that you have used SQL Fiddle to test your code from part D1 by providing a screenshot of your results.

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

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

a. Demonstrate that you have used SQL Fiddle to test your code from part E1 by providing a screenshot of your results.

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

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

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

a. Demonstrate that you have used SQL Fiddle to test your code from part F1 by providing a screenshot of your results.

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

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

a. Demonstrate that you have used SQL Fiddle to test your code from part G1 by providing a screenshot of your results.

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

a. Demonstrate that you have used SQL Fiddle to test your code from part G2 by providing a screenshot of your results.


sales_order.docx
sql_fiddle_instructions.docx

Tutor Answer

(Top Tutor) Daniel C.
(997)
School: UT Austin
PREMIUM TUTOR

Studypool has helped 1,244,100 students

8 Reviews


Summary
Quality
Communication
On Time
Value
tinytim47
Dec 1st, 2016
" Wow this is really good.... didn't expect it. Sweet!!!! "
Five Star Tutor
Nov 21st, 2016
" Outstanding Job!!!! "
mixedballz
Nov 17th, 2016
" excellent work as always thanks for the help "
lilmoe415
Nov 8th, 2016
" Thank you, Thank you, for top quality work, this is your guy!! "
darnay
Oct 26th, 2016
" The best tutor out there!!!! "
dontuwannaknow
Oct 17th, 2016
" Excellent work as always thanks so much "
SjSurvivor143
Oct 3rd, 2016
" Thanks for the help. "
Joemoe
Sep 20th, 2016
" <3 it, thanks for saving me time. "
Ask your homework questions. Receive quality answers!

Type your question here (or upload an image)

1824 tutors are online

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors