COMP1140: Database and Information Management
Assignment 1: Numberone Pizza Project -
Requirement Analysis and Conceptual Design
WORTH 15% of the final course assessment mark.
1.
Background
Numberone Pizza is a pizza outlet in Mondel town. After years of managing the records manually,
Numberone Pizza has decided to computerise its records. You are tasked to design a database for the
shop.
The proposed database system is developed in various modules, including requirements analysis,
conceptual database design, logical database design, and physical database design. In this assignment 1,
you are required to develop user requirements specification and the conceptual database model for
Numberone Pizza's database based on the business requirements provided in this document and related
discussions in lectures.
Your lecturer will act as your client and you can query him/her for any further information and
clarifications.
2.
Main Features and Business Requirements
Order Processing
Numberone Pizza takes orders via phone as well as through walk-in customers. Numberone Pizza
provides both delivery and pickup services. When a customer orders, the customer's phone number is
entered into the system along with the id of the staff taking the order. If the customer has previously
ordered, the name and address appear on the screen. The customer is then asked for his/her name and
address, and then the order is taken. If the customer has not ordered before or if the name and address
given do not correspond with what is recorded in the computer, a new customer record is created, and
the order is taken. For each phone order, the time the call was answered as well as the time the call was
terminated is recorded. For each walk-in order, the time the customer walks in is recorded.
Each order contains the date of order, the items ordered, the quantity of each item, the price of each item,
the total amount due, payment method, order status, type (phone/walk-in), and description. If the
payment is via card, a payment approval number is recorded. For a phone order, if the order is a pickup
order, the pickup time is recorded; if the order is a delivery order, the delivery time and address and the
driver who delivered the order are recorded. For a walk-in order, only pickup is available, where the
pickup time is recorded.
Menu Items, Ingredients, Ingredient Order and Suppliers
Each item in the menu has an item code (unique), name, size, and current selling price. An item on the
menu is made up of many ingredients. The ingredients and quantities used for the item are recorded in
the database.
Each ingredient has a code (unique), name, type, stock unit, description, the stock level at the current
stocktake period, the date the last stocktake was taken, suggested current stock level, and a list of/nsuppliers who supply the ingredients. A supplier can supply many ingredients. Each ingredient can be
supplied by many suppliers.
A stocktake is taken each week, where the actual levels of ingredients in store are recorded. This is
then compared with suggested levels (based on orders for the week). This report is used by the store
manager to order ingredients for the following week. Information about ingredient orders needs to be
maintained in the database, including order number, date of the order, date received order, total amount,
order status, description, quantity and price of all ingredients, supplier number, and ingredient code.
Information about ingredient suppliers needs to be maintained in the database. It includes the supplier's
name, phone number, address, and email.
Employees
Employees at the store can be divided into two types: instore staff those who work in the shop are paid
hourly, and driver those who carry out deliveries and are paid by the number of deliveries. For each
employee, there is an employee number, first name, last name, postal address, contact number, tax file
number, bank details (bank code, bank name, account number), payment rate, status, and a description.
Drivers also have a driver's license number. Hours of work are not regular and a record is kept for each
time an employee works - a shift (start date, start time, end date, end time). The shift also stores the
number of deliveries in the shift for a driver, and number of work hours in the shift for an instore staff.
The orders a driver delivers during a shift are kept on the record.
Payment rates for instore staff and drivers are maintained in the database. Employee payments are made
for each shift to the employee's bank account. Employee payment record needs to be maintained in the
database. It includes gross payment, tax withheld, the total amount paid, payment date, payment period
start date, payment period end date, and bank details of the employee.
3.
Assignment Tasks (individual assignment)
The proposed database system is developed in various modules, including requirements analysis,
conceptual database design, logical database design, and physical database design. In this assignment 1,
you are required to complete the first two stages of the database design, i.e., to develop user requirements
specification and the conceptual database model for the database based on the business requirements
provided in this document and related discussions in lectures. There are two parts to be completed in
assignment 1 as described below.
You are required to write and submit a report that includes all the content of the two parts that are
described below. The report must be in Microsoft Word (No mark will be given if the report is submitted
in other file formats).
Part 1: Requirements
In this assignment, you are required to develop a user requirements specification truly fulfilling the data
requirements (identify what types of data need to be stored in the database), transaction requirements
(identify the important and frequent database operations - data manipulation and queries), and business
rules (which is based on this document and elaborations in lecture) for the Numberone Pizza's database
mentioned above./nData Requirements outlining the major data items, including their names, and short
descriptions of what the data is and what is stored in each field. For each data, give its
relationship with other related data types and related business rules, and its subclass info if it
has.
Transaction requirements - outlining the data manipulation and queries
• Business Rules
Hint: Sample requirements documents are discussed in weeks 2 and 3, and are available in appendices
A and B of your main textbook.
You may interview your client (i.e. lecturer) for clarification and include your interview questions and
responses
Part 2: EER Model with data dictionary
Draw an EER model for the requirements identified in Part 1. The EER must be included in the report
(you also need to submit your original EER file). The EER Model must be shown in UML notation
which is discussed in class and provided in our text. Please note: other notations (other than UML) will
NOT be accepted, i.e., a zero mark will be given for the EER model part if a notation other than UML
is used.
Note: in the report, you must put your whole EER inside no-more-than 2 A4-sized pages. It is strongly
suggested that you put your EER within one page. If your EER is in more-than-2 pages, 10 marks (out
of 150) will be deducted.
The EER model should be accompanied by a data dictionary that includes entity type table, relationship
type table, and attribute table.
Assignment submission format for the EER Model with Data Dictionary part: The document MUST
have the following sections:
• EER Model
•
Documentation - Data dictionary details (description of entities, relationships and attributes)
A sample format for documenting the data dictionary is provided below.
Data Dictionary Format: Use the format described in your main text in documenting the data dictionary.
The following provides samples for reference only.
ENTITY TYPES
Entity Name
Collection
Description
A collection is a physical
collection of items in the
library located at a particular
physical location
RELATIONSHIP TYPES
Entity name
PhysicalCopy 0..*
Multiplicity Relationship
Located In
Aliases Occurrence
section Physical area of the
library is divided into
a set of collections
Multiplicity
1..1
Entity name
Collection./nATTRIBUTES
Entity Attributes Description Data
Name
Type &
Length
Student studentId
A unique id char N
given
to
every
student
Nulls
Multi-
valued
Method of submission: A softcopy submission is required:
•
N
Derived Default
N
Hint: Sample EER models and documentation is provided in Chapter 16 of your text.
The lecturer will discuss the details of the requirements in class as well as act as the client of the
system. You need to implement all the details mentioned in lecture as well as described in this
document and the marking scheme document. You are encouraged to ask questions to the lecturer
and tutor to clarify requirements.
Your assignment report must have page numbers, your Student ID and your name in the
footer of each page.
zip all required files into one zip file (basically 2 files - one is your word-format report, the
other is EER in Visio file, or pdf if you use an editor other than Visio). The file name
MUST be identified by 4 sections: A1, your first name, your surname, and your student
number, e.g., A1 SimonLee 1234567.zip. It must be submitted to Canvas-> Assignments ->
Assignment1
• In the report file, you must have on the front a signed copy of the cover sheet (Assessment
Item Cover Sheet - Individual) which is available from:
http://www.newcastle.edu.au/_data/assets/pdf_file/0008/75383/AssessmentItemCoverShe
et.pdf
Note: please make sure to fill in your Tutorial Group (i.e., date/time/place), Tutor's Name, as
well as other items. Otherwise, your submission marking may be delayed.
Note: Ten percent of the possible maximum mark for the assessment item will be deducted for each day
or part day that the item is late. This applies equally to week and weekend days. Assessment items
submitted more than five days after the due date will be awarded zero marks.
Fig: 1
Fig: 2
Fig: 3
Fig: 4