Database Design SQL Fiddle, assignment help

Question description

DATA MANAGEMENT – APPLICATIONS

Competencies:4018.1.1: Conceptual Models to Physical Schemas – The graduatecreates conceptual data models and translates them into physicalschemas.4018.1.2: Create Databases – The graduate creates databasesutilizing SQL Data Definition Language (DDL) in MySQL environment.4018.1.3: Create/Modify Tables and Views – The graduate createsand modifies tables and views employing SQL Data DefinitionLanguage (DDL) in MySQL environment.4018.1.4: Create Primary Keys/Foreign Keys and Indexes – Thegraduate creates and modifies primary keys (PKs) and foreign keys(FKs) and indexes with SQL Data Definition Language (DDL) in MySQLenvironment.4018.1.5: Populate Tables – The graduate populates tables withinsert, update, and delete using DML in the MySQL environment.4018.1.6: Create Simple and Complex Queries – The graduatecreates simple Select-From-Where (SFW) and complex 3+ table joinqueries with Data Manipulation Language (DML) in MySQLenvironment.Introduction:

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

Tocomplete this assessment, you will be using SQL Fiddle, an online SQLtool, to test run the MySQL code that you will develop for parts Cthrough G. After running the code, you will take a screenshot of yourresults, which should be copied into the document that you will submitto TaskStream for this assessment. Instructions for how to use thistool for each part of the assessment is included in the attacheddocument “SQL Fiddle Instructions.” Please note that for each part ofthe assessment, there are explicit instructions on what SQL code youwill need to copy and paste into SQL Fiddle panels to run yourtest.

The work you complete for each part of the assessment (i.e.,the design models/diagrams, tables, written explanations, SQL scriptcode, and screenshot results from running your SQL scripts in SQLFiddle) should be saved as a single *.pdf (Portable Document Format)file that you will submit to TaskStream.

Scenario: 

You are the database designer and developer for a burgerstand that wants to create a smartphone application where customerscan order meals. First, you will design a normalized entity-relationship (E-R) logical database model to store data related to the customer,products, and product order. Next, you will create three tables withprimary and foreign keys that are derived from your E-R model. Oncethe tables have been built, then you will create views and indexes toprotect and fine-tune query performance. You will populate each of thetables with sample data. Finally, you will create both a simple “selectfrom-where”(sfw) query and a complex join query to producemeaningful reports on individual orders and summaries to determinewhich products sell the best.

Requirements:A. Construct a normalized model to represent the burger standsmartphone application database that supports the scenario aboveby doing the following:1. Using the unnormalized order form (“Receipt” attachedbelow) for the ordering database as a reference, producethe final logical schema for this database by doing thefollowing: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 in 1st normal formand fulfills the following requirements:• The table should have a primary keythat uniquely identifies the records.• The values in each of the columnsshould be atomic.• There should be no repeating groups.i. Write a one paragraphexplanation of how you designedthe table.b. Design at least two tables that are in 2ndnormal form and fulfill the followingrequirements:• The tables should meet all of therequirements for the 1st normal form.• Redundant data appearing in multiplerows should be eliminated with thecreation of the new tables.• Each of the tables should be related using foreign keys.i. Write a one paragraphexplanation of how you designedthe tablec. Design at least three normalized tables thatare in 3rd normal form and fulfill the followingrequirements:• The tables should meet all of therequirements for the 1st and 2nd normalforms.• Fields that do not depend on theprimary key or any other field should beeliminated.• There should be at least threeresulting tables, one for customerinformation, one for product information,and one for order information.i. Write a one paragraph explanation ofhow you designed the tableB. Using the tables you designed in 3rd normal form from part A1c,create an entity-relationship (E-R) diagram that fulfills thefollowing requirements:Note: You can use any drawing tool of your choice to create thediagram.• Entities should be drawn that represent each of the tables fromthe 3rd normalized form.• All appropriate fields (i.e., attributes) should be entered intoeach of the entities.• Primary keys (PK) and foreign keys (FK) should beappropriately designated.• Data types for each attribute (Numeric, Fixed, Char, Varchar,or Timestamp) should be appropriately designated.• Relationships drawn between the entities should be labeledwith a relationship name.1. Provide a written explanation for the following:a. Explain why you selected the entities represented inyour diagram.b. Explain how you determined the relationships between these entities.c. Explain what the cardinality is for each of the relationships.C. Develop the SQL code for each of the tables you designed in part Aand refined in part B by doing the following:1. Provide the SQL code you wrote for each table.Note: Make sure all of the primary and foreign keys and data types areassigned to each of the fields in the create table SQL code.a. Demonstrate that you have used SQL Fiddle to test your codefrom part C1 by providing a screenshot of yourresults.D. Using the table that contains the customer information fields,develop SQL code to create a View that shows all of the customerinformation with the First Name Middle Initial, and Last Nameconcatenated (CONCAT()) to show full name as one field by doingthe following:1. Provide the SQL code you wrote to create the View for thecustomer information.a. Demonstrate that you have used SQL Fiddle to test your codefrom part D1 by providing a screenshot of yourresults.E. Using the table that contains the product information fields,develop SQL code to create an Index for the product name field bydoing the following:1. Provide the SQL code you wrote to create the Index for theproduct information.a. Demonstrate that you have used SQL Fiddle to test your codefrom part E1 by providing a screenshot of yourresults.F. Using all of the tables you developed in part C, develop SQL code topopulate each of 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) withprimary 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 todisplay the values in a requested table or tables by doing thefollowing:1. Provide the SQL code for the simple (sfw) queries to display allof the data in each of the tables you have created andpopulated.a. Demonstrate that you have used SQL Fiddle to test your codefrom part G1 by providing a screenshot of yourresults.2. Provide the SQL code for a complex join query to display all of theinformation contained in the attached “Receipt.”a. Demonstrate that you have used SQL Fiddle to test your codefrom part G2 by providing a screenshot of your results.H. Submit parts A–G as a *.pdf (Portable Document Format)