Posted:

CITS1402 RDBMS Assignment Project Description Paper

CITS1402 RDBMS Assignment Project Description
Semester 2, 2018: 2024 – Write My Essay For Me | Essay Writing Service For Your Papers Online
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: 2024 – Write My Essay For Me | Essay Writing Service For Your Papers Online Sunday 23:59pm. No late submissions will be accepted.

Order | Check Discount