Practice Exercise: CREATE TABLE Statements for Pizza Delivery Shop Database

One of the main goals in both the CREATE TABLE Assignments and the following INSERT INTO Assignment is to reinforce the concept of referential integrity.  These two assignments should make sure that all students understand the rules associated with enforcing referential integrity.  When Creating Tables in a Relational Database Management System (RDMBS) that enforces referential integrity 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. 

If the guideline is not followed, then the tables involved with the CREATE TABLE Statements that do not follow the guideline will not get created when the CREATE TABLE Statement executes.  This can have cascading errors in other CREATE TABLE Statements that are dependent on another CREATE TABLE Statement executing without syntax errors. This will also have cascading errors down into the INSERT INTO Statements that are dependent on a specific table being created successfully.  Issues involving Referential Integrity enforcement with INSERT INTO Statement will be described in the next Module.  

For this assignment, students have the option of using their own design material to write the create table statements, or students can just follow and create the guided tutorial version of the database based on the presentation.  The presentation version will create a database using an Enhance Entity Relationship Version of the Database.  If you want to practice implementing a database that includes Specialization, Generalization, and History, I recommend following the guided video.  If you want to implement a less complex version of the database, I recommend using the design documentation that you followed and created from the previous Pizza Delivery Database Assignments.   

Additionally, if you did not follow the previous video guides and created the design artifacts for the Pizza Delivery Database exactly and received constructive feedback, and did not make changes, it would be best to follow this video series on creating your database and tables exactly as opposed to using your previous materials.  However, if you have been following the previous video guides exactly and received perfect scores on your guided tutorial designs, then the choice is entirely up to you.  Students that received feedback for improvements should make the effort to make modifications to their personal designs.  This will improve your skills in designing databases. Once again this decision is optional for the practice assignments.

If working from the previous material that you are sure will work because you received good grades on them, Use the ER Diagram, Relational Schema, and Metadata documents from your previous homework assignments from the Pizza Delivery Shop Database as a guide.  Write the CREATE DATABASE and CREATE TABLE Statements from its conceptual design.

I provided a functional ER Diagram, a Relation Schema, and Metadata Documents for students that did not score well on these earlier assignments.  Students that scored well can use the designs and translations that they created. 

If you are not sure, then it is best to write your version of the create table statements by following the guided video series in this assignment exactly.  Doing this will provide you with an expanded experience

USE the 'CREATE TABLE Basics - Video Series' and the ' Video Series - CREATE TABLE Statements for Pizza Delivery Shop Database ' as a guide for completing this assignment

Upload copies of the ER Diagram, Relational Schema, and Metadata documents that you used to write the CREATE Table Statements. 

The Script that you upload must be able to execute in a continuous sequence.  Any script not executing in a continuous sequence will be assessed as being incorrect.  There is no partial credit for creating table statements that do not execute due to errors in creating tables that it references. If the script does not execute continuously from top to button, it does not count towards your accumulated points for the assignment.   It is your responsibility to ensure that you test your script thoroughly before submitting it for assessment.

Design Material Based or Original Requirements

These files are for the students who want to implement the regular ER Diagram not the Enhanced ER (EER) Diagram in the Video associated with this assignment.

ERD Pizza Delivery Shop Database - Summer 2022.pdf Download ERD Pizza Delivery Shop Database - Summer 2022.pdf

Translation from ERD To Relation Schema for the Pizza Delivery Database - Summer 2022.pdf Download Translation from ERD To Relation Schema for the Pizza Delivery Database - Summer 2022.pdf 

Metadata for Pizza Delivery System - Summer 2022.pdf Download Metadata for Pizza Delivery System - Summer 2022.pdf  

EER Diagram being used in the Guide Video Tutorial for this Practice Assignment 

This is the ER Diagram associated with the Guided Video Tutorial link with this assignment.

EER_PizzaShop.pdf Download EER_PizzaShop.pdf 

Submit the Following

1. ER Diagram used for assignment
2. Relational Schema Document Used for the Assignment
3. Metadata Document used for the assignment
4. Text File containing SQL Script used to Create Database and Tables for the Pizza Delivery Shop Database.

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. 

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

Please do not Submit Documents in a Zipped Format