Database Design & Implementation (Pizza Store)
Task 1 - Database Design
Your first task is to design a database for the scenario detailed on the following pages. Your final database design should comprise of approximately 9 entities.
State any assumptions you have made regarding your database design at the beginning of the database design document. Do not make any assumptions that significantly change the structure of the scenario, as this may make Task 2 of the assignment difficult. Only make assumptions that influence your database design. If you are unsure about an assumption you wish to make, ask your tutor.
Once you feel you have identified the entities, attributes and relationships of the scenario in sufficient depth, you are required to create a logical ER diagram nd a corresponding physical ER diagram to depict your database. Adhere to the distinctions between logical and physical ER diagrams covered in Lecture 3. It is recommended that you draw your diagrams on paper first, in order to find a layout that is clear and can be created in an electronic format.
Lastly, create a data dictionary, with an entry for each entity in your database. The entries should list the name of the entity (table), a description of its purpose, a list of attributes (columns), important information about the attributes (e.g. data type, null/not null, identity, default values...), and details of any constraints applied to attributes. List the entries in your data dictionary in an appropriate table creation order that can be used to create the database. Include any additional information, if any, that may be needed to implement the database. Remember, a data dictionary should contain all the information needed to implement a database. Use the data dictionary in Lecture 4 and the data dictionary of the "company" database (Module 5) as examples. Some marks are also awarded for presentation and notation.
Your complete database design should consist of a list of assumptions, logical and physical ER diagrams and a data dictionary. This should be submitted as a single PDF document. Make sure that your assignment includes the unit code, assignment number/name, year and semester and your name and student number on the first page.
Please ensure that your completed database design is in PDF format, and open the PDF file before submitting it to ensure that your diagrams appear as intended.
Scenario Details
You are required to design and create a database for a pizza store. The database must keep track of the details of staff, orders and pizzas (including the available types of crusts and sauces).
You have the following information about the way the store operates:
- Staff details must be recorded. This includes a staff ID number, first name, last name, date of birth and phone number.
• A staff member may be supervised by another staff member. A staff member may supervise many other staff members. Not all staff members have a supervisor.
- The details of customer orders must be recorded. This includes an order ID number, the date and time that the order was placed, the name and email address of the customer who made the order, and the ID number of the staff member who took the order.
• The store does not record any other customer details, and all orders must be picked up from the store. A customer name is required, but the email address is optional.
• An order can be for multiple pizzas, but must obviously contain at least one.
- The store has divided the types of pizza they offer into ranges, e.g. "traditional", "gourmet", etc. The database must store an ID, name and price for each range.
- The details of the types of pizza available must be recorded. This includes a pizza ID number, the pizza's name, a description and a foreign key identifying the range that the pizza is in.
- The database also needs two tables to store the details of different crust types and sauce types that can be chosen when ordering a pizza.
• These tables must contain an ID number and a name for each crust/sauce.
• Some crusts/sauces result in a surcharge - a column is needed to store this amount.
• When ordering a pizza, a customer must choose which crust and sauce they want.
- The database must track the pizzas ordered in each order. This will involve:
• An auto-incrementing ordered pizza ID number.
• A foreign key identifying the order that this pizza is part of.
• A foreign key identifying which pizza was chosen.
• A foreign key identifying which crust was chosen.
• A foreign key identifying which sauce was chosen.
• A "ready" column containing a "Y" or "N" to indicate whether the pizza has been made and cooked yet (default of "N").
- The store also sells sides such as garlic bread and soft drinks. Details of these sides and their inclusion in orders must be recorded.
• The database must store an ID, name and price for each side.
• The database must keep track of which sides have been ordered in which orders, and the quantity ordered.
General Information and Guidelines
The information above describes all of the entities, attributes and relationships required in the database design. Some minor details, such as the cardinality of some relationships, have been omitted. It is up to you to make (and state) any assumptions you need in order to complete the database design. If you are uncertain about any part of the scenario described above, seek clarification from your tutor.
It is recommended that you use auto-incrementing integers as the primary key for all entities in this scenario, since there are no naturally occurring attributes that are suitable to use as primary keys. A compound primary key (not auto-incrementing) may be suitable for certain intermediary entities.
Be sure to specify the most appropriate data type (and length, where applicable) for each attribute in your data dictionary. Note that when you are storing a date/time, it should be stored as a single column - do not split the date and time into two columns unless there is a very good and necessary reason to do so.
Read the scenario details several times to ensure that your database design incorporates all the elements described. If you desire feedback on your work in progress, send it to your tutor.
Task 2 - Implementation
Once your database has been designed, it is time to implement it in a DBMS, populate the database, and then manipulate the data via queries. The deliverables of this task are three files containing SQL statements. We will be using Microsoft SQL Server 2014 or above - your SQL scripts must run in the same environment used in the unit/labs.
Create your scripts as three ".sql" files, with the filenames listed in the following headings. Templates for the script files are provided with this assignment brief - please use them. Format your code for readability, and use comments for headings and to provide further detail or information about your code if needed .
As each of the script files will contain numerous SQL statements, it is very useful to be aware of a particular feature of SQL Server Management Studio (SSMS): If you have selected some text in a query window, only the selected text will be executed when you press the Execute button.
This makes it much easier to test a single statement, or even part of a statement, within a script file.
You are required to create all of the scripts detailed below. Please take note of the file names and use the template files provided with this assignment brief.
Filename: create.sql
This file is a creation script, similar to the "company.sql" file (Module 5) used to create the company database.
Database Creation & Population Script
Produce a script to create the database you designed in Task 1 (incorporating any changes you have made since then). Be sure to give your columns the same data types, properties and constraints specified in your data dictionary, and be sure to name tables and columns consistently. Include any logical and correct default values and any check or unique constraints that you feel are appropriate.
Make sure this script can be run multiple times without resulting in any errors (hint: drop the database if it exists before trying to create it). You can use/adapt the code at the start of the creation scripts of the sample databases available in the unit materials to implement this. You will need to follow an appropriate creation order when creating your tables - you cannot create a table with a foreign key constraint that refers to a table which does not yet exist.
Once you have created your database, it is recommended that you use SSMS to create an ER diagram and use this to verify that your implementation matches your design. This can be done by right clicking on the "Database Diagrams" folder of the database in the Object Explorer in SSMS.
Following the SQL statements to create your database and its tables, you must include statements to populate the database with sufficient test data. You are only required to populate the database with enough data to make sure that all views and queries return meaningful results. You can start working on your views and queries and write INSERT statements as needed for testing as you go.
For example, imagine you are working on a query which joins data from two tables and only displays the rows that meet certain criteria, then orders the results by a certain column. To test this, you will need to insert some data in both of the tables, making sure that some of the rows meet the criteria and others don't, and making sure that the column used for ordering contains a range of different values. Once you have inserted this data, you can test that your query works.
The final create.sql should be able to create your database and populate it with enough data to make all views and queries return meaningful results.
Make sure all referential integrity is observed - you cannot add data to a column with a foreign key constraint if you do not yet have data in the table it refers to. Remember that if you are using an auto-incrementing integer, you cannot specify a value for that column when inserting a row of data. Simply pretend the column does not exist when inserting data - do not try to specify a value for it.
Filename: views.sql
The following page contains some general information and tips regarding views.
Pizza View
Create a view which shows the following details of all pizzas:
- The pizza ID number, pizza name and pizza description.
- The pizza's range ID, range name and range price.
Ordered Sides View
Create a view which shows the order ID number, side ID number, side name, quantity and total cost of all ordered sides. This involves joining the side and ordered side tables and some basic arithmetic.
Ordered Pizzas View
Create a view which shows the following details of all ordered pizzas.
- The ordered pizza ID number, order ID number and "ready" column.
- The pizza ID number and pizza name of the ordered pizza.
- The range ID number and range name of the ordered pizza.
- The crust ID number and crust name of the ordered pizza.
- The sauce ID number and sauce name of the ordered pizza.
- The cost of the pizza (add together the range price, crust surcharge and sauce surcharge)
These views essentially create joined or "flat" versions of significant tables of the database, giving you a convenient way to access related and calculated information that is stored in multiple tables.
You are encouraged to use the views to simplify the queries which follow - You can use a view in a SELECT statement in exactly the same way as you can use a table, often avoiding the need to write the same joins and calculations over and over.
When writing a view, it is easiest to write the SELECT statement first, and only add the CREATE VIEW statement to the beginning once you have confirmed that the SELECT statement is working correctly. If you wish to create additional views to use in the queries which follow, include them in this file.
Filename: queries.sql
Write SELECT statements to complete the following queries. If you do not understand or are not sure about exactly what a query requires, contact your tutor. Marks will be lost if your query does not do what is required, even if that is due to misunderstanding the requirements.
Query 1 - Customer Email List
Write a query that concatenates customer names and email addresses from the customer order table to form a single column with the following format:
"[customer_name] <[customer_email]>", e.g. "Dom "
If there are multiple orders with the same customer name and email address, it should only appear once in the results. Orders where the email address is NULL should not be included in the results.
Query 2 - Unready Pizzas
Write a query that shows the order date, pizza name, crust name and sauce name of any ordered pizzas which are not ready (ready column contains "N"). Order the results by the order date.
Using the Ordered Pizzas View in this query is recommended.
Query 3 - Staff Search
Write a query that shows the staff ID number and full name (e.g. "John Smith") and date of birth of staff members who do not have a supervisor and are less than 21 years old (calculated using the current date). Order the results by the first name column.
Query 4 - Staff Supervision
Write a query that selects the ID number and full name of all staff, as well as the ID number and full name of their supervisor. All staff should appear in the results, even if they don't have a supervisor.
Query 5 - Side Statistics
Write a query that selects the following details about the sides that have been ordered in orders:
- The side ID number and side name.
- How many orders the side has been ordered in (regardless of quantity).
- The average quantity in which the side is ordered (cast the quantity column to a FLOAT before averaging it for greater accuracy).
- The total sale cost of the side (remember to take the quantity into account).
Give all columns appropriate aliases, and order the results by the total sale cost in descending order. Using the Ordered Sides View in this query is recommended.
Query 6 - Pizza 2's Most Popular Crust/Sauce Combinations
Write a query that identifies the three most popular combinations of crust and sauce chosen for the pizza with an ID of 2. The query should select the pizza name, crust name, sauce name, cost (including crust/sauce surcharge) and the number of times that the combination has been ordered. Using the Ordered Pizzas View in this query is recommended.
Query 7 - Under-Average Ordered Pizza Costs
Write a query that shows the pizza name and average cost of ordered pizzas that have an average cost which is less than the average cost of all ordered pizzas. Order the results by the average cost, in descending order.
Using the Ordered Pizzas View in this query is recommended.
Query 8 - Customer Order Summary
Write a query that selects the following information for all customer orders:
- The order ID number, order date and customer name.
- The staff ID number and full name of the staff member who took the order.
- The total cost of pizzas in the order, total cost of sides in the order, and total cost of the order as a whole (i.e. the cost of all pizzas and all sides in the order).
Order the results by order date, and make sure that the total order cost is calculated correctly even if an order did not contain any sides or any pizzas.
Using the Ordered Pizzas View and Ordered Sides View in this query is recommended.
Presentation, Notation and Formatting
A small amount of marks are awarded for presentation, notation and formatting. This includes:
- Presentation and appearance of word processed PDF document for Task 1
- Appropriateness and consistency of notation used for diagrams/data dictionary in Task 1
- Appropriate commenting and formatting of scripts in Task 2
Attachment:- database design.zip