Practice Exercise: INSERT INTO statements for the Pizza Delivery Shop Database

Student can follow the Links under the Text Header named  "Tutorial Videos  for the INSERT INTO Pizza Delivery Assignment" in the Module to participate in this assignment. 

One of the main goals in both the previous CREATE TABLE Assignments and this INSERT INTO Assignment is to reinforce the concept of referential integrity.  These two linked assignments should make sure that all students understand the rules associated with enforcing referential integrity.  In the last assignment, when Creating Tables in a Relational Database Management System (RDMBS) that enforces referential integrity, you should have discovered that it is important to understand that the tables on the one side of 1_M Relationships must be created before the tables that are on the many sides of 1_M Relationships.

In this segment of the development process for the Practice Database, should also discover that it is equally important to insert the test data for the database and the real-time data in the same order.  The data for the tables on the 1 side of 1_M Relationships must be inserted into their tables before the data on the many sides of the 1_M Relationships can be inserted into their tables.    

If the guideline is not followed, then the tables involved with the INSERT INTO Statements that do not follow the guideline will not get data inserted when the INSERT INTO Statement executes.  This can have cascading errors in other INSERT INTO Statements that are dependent on another INSERT INTO Statement executing without syntax errors. This will also have cascading errors down into the INSERT INTO Statements that are dependent on specific data on the one side of relationships being inserted successfully.  If a Primary Key data value that a Foreign Key data value is referencing does not exist in the Primary Key Table, a Parent/Child Referential Integrity Error will occur.   This will prevent the record/rows containing the Foreign Key value from being inserted successfully into the Foreign Key Table. 

This assignment is based on a Video Presentation that is published.  After reviewing the video, you are to duplicate the tasks performed in the video.  After duplicating the tasks, submit the SQL script that you wrote for assessment.

I am supplying the SQL Script that contains the CREATE TABLE Statements that corresponds to the INSERT INTO Statements being illustrated and executed in the video.

Below are links to Alternate SQL files to create the database.  

Pizza Delivery Shop - DB.sql Download Pizza Delivery Shop - DB.sql 

EER_PizzaDelivery_DB_Demo.sql Download EER_PizzaDelivery_DB_Demo.sql

Alternate Design

EER_PizzaDelivery_DB_Demo Summer 2020.sql Download EER_PizzaDelivery_DB_Demo Summer 2020.sql

I am also including the sample data that I created and used in the INSERT INTO Statements that you will view in the sequence of videos.

Below is the link to the Excel file that contains the test data used in the INSERT INTO Statements

TestDataPizzaDelivery.xlsx Download TestDataPizzaDelivery.xlsx

Create sample data for your Pizza Delivery Lab Database.  Use a Spreadsheet Workbook or a sequence of Tables in a Word Processor to document the test data values that you will use.   Keep your Sample Size Small.  Between 5 to 10 rows per table.

Once your test data values are organized, write the SQL INSERT INTO Statements to populate the tables of the database with your test data. 

Upload the Following Documents for the Assignment

1. ER Diagram that is associated with CREATE TABLE Statements in your SQL Script - submit as a PDF
2. Metadata Document that is associated with your SQL Script.  Submit in as a PDF or Word Processor Format 
3.  Word Processing Document or PDF containing the Test Data that you selected to use.  Must be in DOCX or PDF Format
   
   
4. SQL Text containing the SQL Script to Create the Home Database and Tables
   
   
5. SQL Text containing the SQL Script to Populate the Tables of your Database data
   
   
6. Submit all SQL Script in a single SQL Text File with an SQL extension

Many Students had problems with their Schema and Metadata Documentation. If you received feedback concerning Primary and Foreign Key Problems, you may want to use the Metadata and Schema that I provided below.  However, I encourage all students to review the constructive feedback and make the appropriate modifications to their design documentation.

One of the main goals of this assignment is for you to create a continuous sequence of SQL statements to create the database, use the database, create the tables, and insert data into the database without any errors.

The Excel File that I provided contains all the necessary data needed. The Excel File is a Workbook consisting of multiple worksheets.  Each worksheet can be accessed through a tab at the bottom of the screen.  Each worksheet has a name representing the table that it is aligned with. 

A prerequisite for CIS 205 is CIS 103 which covers the basic use of a spreadsheet, particularly Microsoft Excel. If working with multiple worksheets in a workbook was not one of your strong points, I can point you to some online tutorials to refresh your understanding. Send me an email if you require a refresher on working with multiple worksheets

In Conclusion

for the INSERT INTO Assignment for the Pizza Delivery Company, I provided the Sample Data in an Excel Spreadsheet.  The Excel File is a Workbook consisting of multiple worksheets.  Each worksheet can be accessed through a tab at the bottom of the screen.  Each worksheet has a name representing the table that it is aligned with. 

I also provided my own version of the Script to Create the Database and Tables that match a Video Guide that I provided. My expectations are that students will use the Video Guide for Inserting Data into the Pizza Delivery Database. 

The Bottom Line Is This

1. if you received a 100 on the CREATE TABLE Assignment then your structure is sound, and you can make a choice on which CREATE TABLE Script to use. 
   
   
2. If you did not receive a 100 on previous design documentation prior to this assignment, then it is likely you will have a view referential integrity errors in your INSERT INTO Statements using your Script from the previous CREATE TABLE Assignment,  If this is so, you should use my script then. 
   
   
3. Then there is also the likelihood that a student used the feedback from the previous CREATE TABLE Assignment to correct the problems.  If this is the case, then you can also choose which CREATE TABLE Script that you would like to use.
   
   

Since you have been exposed to the CREATE TABLE Statements, you can use them as your metadata.  You can also use the DESCRIBE Table Statement to obtain the Metadata from the RDBMS

Submit an SQL Text File for the SQL portion of this assignment.  An SQL Text File is a text file with a ".SQL" extension on the file name.

Submit the Following

• ER Diagram that was used to Design the Version of the Pizza Delivery Database Being Created
  
• Metadata Document Describing and Illustrating the Tables and Attributes in your version of the Pizza Delivery Database
  
  
• An SQL Text File containing the Create Table Statements and the Insert Into Database 
  
  
• a PDF or Word Processing Document Containing the Sample Data that was used in the INSERT INTO Statements

 
Requirements for the SQL Script

• The script should be well documented with comments
  
  
• All the script should be placed in one file
  
  
• The script should run continuously without errors