代做CITS1402、代做SQL程序、RDBMS Assignment代写代做、代写SQL设计

日期：2018-10-21 09:07

CITS1402 RDBMS Assignment Project Description

Semester 2, 2018

Business Scenario

There is a client who runs an online restaurant where customers can order their dinner

online and have it delivered. Customers are uniquely identified by their mobile phone

numbers, they also have names (first, last), addresses and account info such as method

of payment (credit card, cheque, or cash). To keep it simple, you are NOT required to

consider walk in customers.

When ordering dinner, customers may choose multiple dishes together in one order

with a specific ordering time. The restaurant sells pizza and allows customers to choose

the ingredients (toppings). Customers pick ingredients for their pizzas and sometimes

will require different quantities such as a double portion (i.e. quantity is 2) of specific

ingredients. Pizzas have various sizes (small, medium, large), ingredients (pepperoni,

sausage, mushrooms, onions, extra cheese, etc.), price information, discount prices, thin

or thick crust, etc. The other items available (e.g., drinks or chips) have product codes,

descriptions, manufacturers, suppliers and prices. To keep it simple, ONLY pizzas have

ingredients. When ordering dinner, one customer may order multiple pizzas and/or

drinks/chips together.

Each order is associated with a particular customer and is handled by a cook and a

driver. The cook and the driver are both employees of the restaurant. An employee may

be a cook or a driver or have both roles. Each employee has a name, address, phone

number and base salary. Assume that they are paid their base salary in addition to being

paid for the amount of work that they do, i.e., number of pizzas and deliveries made.

An employee earns 50c for each pizza that they make and $2 for each order they deliver.

The above address should be organised by street number, street name and suburb, e.g.,

43 Stirling Highway, Crawley. Each item or ingredient has a cost and retail price, which

can be used to calculate the profit.

Tasks

Imagine that you have been assigned to develop a delivery database system for the

restaurant based on the above scenario. To develop this database, you need to have the

skills and knowledge about relational database concepts, ER diagram modelling,

database design theory, and MySQL queries. This assignment is worth 20% of the total

assessment for the unit CITS1402 and consists of three main components:

1. Design and draw an ER model for the pizza delivery database [6 marks in total].

a. You need to clearly identify essential entities, their attributes, and

primary keys. (2 marks)

b. You need to clearly identify all relationships, including IS_A and any

other relationships. You also need to specify the cardinality and

minimum cardinality for each relation. If you make any assumptions

that are not a part of the business scenario, state them clearly and

briefly justify them. (3 marks)

c. Please provide a short paragraph (500 words approx.) to explain the

entities, attributes, and relations that you have taken in to consideration

and any non-trivial decisions you have made in your design, i.e. why you

believe that the entities and their attributes and relationships that you

have chosen are important to the business. (1 marks)

Hint: You should present your understanding of the business in Task 1.c, i.e. if you

have identified an entity/relation, state why you believe such an entity/relation is

important to the business and its database, and vice versa. Remember, there is no

unique/ best answer to a business problem, hence, this is your chance to present your

solution to the stakeholders and argue why it is the most appropriate/cost effective

solution to the problem. Failure to present your understanding of the business may

lead penalties in Task 1.a and 1.b as the stakeholders may not be able to understand

your solution.

2. Implement the relational data schemas based on your ER model [6 marks in

total].

a. Provide the database schemas based on your ER Model. To complete

this task, you need to identify functional dependencies and ensure that

all tables are normalised to at least 3NF. Provide descriptions of the

decomposition process that is applied to any table/entities in your ERD.

(3 marks)

b. Identify data types for each attribute in your tables. You need to firstly

list the data types, and then briefly explain why you choose that

particular data type. (1 mark)

c. Please provide all SQL queries to create the tables. (1 mark)

d. Please supply SQL queries that insert some dummy records (10 approx.)

to each table. You need to refer Task 3 to ensure that you will have valid

records returned for each query in Task 3. (1 mark)

Note: You should NOT create tables and records using the features provided

by MySQL Workbench. That is, you must provide SQL queries/code to

complete Task 2.c and 2d. You will NOT receive any mark for 2.c and 2.d

if no SQL Query is submitted. You will be penalised if any bug is found in

your SQL queries/code, which may also lead further problems in Tasks 3.

3. SQL Queries, Procedures and Views [8 marks in total]:

Provide SQL queries, procedures and views to answer the following questions.

Please ensure that you have inserted sufficient data in Task 2.d. That is, you

need to have at least one record returned by your query for each of the following

questions. Otherwise, a penalty will be applied.

a. List all the employee information of employees who have the roles of

both cook and driver in the restaurant. (1 mark)

b. List the total revenue of this restaurant in different suburbs in descending

order. Tip: you can identify the suburb information by using customers’

addresses. (1 mark)

c. Create a procedure SuburbBestCustomer() to list the best customer for

each suburb. The best customer is the customer who spent the largest

amount in the restaurant. The list should be sorted in descending order.

(2 mark)

d. List the pizza ingredients that no customers have chosen before. (1 mark)

e. Create a view EmployeeSalary() to list all the employees and their total

income. Note: the total income consists of base salary and the actual

workload as depicted in the above description. (2 mark)

f. List the top 10 most profitable ingredients in the restaurant in terms of

the number of pizzas sold. (1 mark)

Submission Requirements

This is an individual assignment. You need to complete all the tasks on your own and

must not copy the work of other students. You need to submit two files: a PDF file

named after your student ID (e.g. 21224999.pdf), and a SQL Query file also named after

your student ID (e.g. 21224999.sql) via the link in Blackboard by the Due Date.

Submission Format

Please read the required submission format carefully. Penalties may be applied for

incorrect submission formats.

The PDF file should contain all your design work for Task 1, Task 2.a and 2.b. Your

solution for each task should be clearly identified by the Task ID with the comment

command ‘#’. For example:

#Task 1.a Solution:

Your solution goes here

The SQL file should contain all the queries for Task 2.c, Task 2.d and Task 3. You need

to write comments for each question and answer. You must use the ‘#’ command in your

code to make comments, which will not be executed by the SQL Server. For example:

# Query for Task 2.c – creating tables

# create CUSTOMER table

CREATE CUSTOMER

…

#Query for Task 2.d – inserting records

#insert 15 records for table CUSTOMER

INSERT

…

It is your responsibility to ensure that your SQL queries are bug free. To do so, carefully

test all your queries using MySQL Workbench. Please also save and backup your work

regularly to avoid any unexpected loss of your work.

Submission Due Date:

21st October 2018 Sunday 23:59pm. No late submissions will be accepted.