02.03 the anti-federalists: assessment

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/