ERR MODEL DIAGRAM

QUESTION

INFO6002 – Database Management 2
Assignment 1 – Database Design & Implementation

Trimester 2, 2012

Due Date

Assignment 1 is due on 11
th
June 2012, 9:00am. You will need to
 upload the assignment and
 submit 1 hard copy of the assignment with a signed assessment cover sheet to the
lecturer during the lecture session.

Weighting

15% of course mark

This is an INDIVIDUAL Assignment.

Assignment Background

You are asked to develop a conceptual database design using Enhanced Entity Relationship
model for a database for Office Wizard.

Office Wizard is a local company which specialises in supplying office equipment and
stationeries to corporate customers.  After years of managing the records manually, the
company decides to computerise the database.  You are tasked to design the conceptual
database design for Office Wizard’s database based on the business requirements provided
in this document.

Your lecturer will act as your client and you can query him for any further information and
clarifications.

Business Requirements

Office Wizard supplies a wide variety of office equipment and stationeries such as cabinet,
computer desk, swirl chair, paper, folder, pen, pencil, marker, etc.  The various suppliers
supply these products to Office Wizard who distribute them to its customers. Three areas are
considered in this database for Office Wizard:
 Managing Supplier & Product information
 Managing Sales information
 Managing Employee information

Page 1 of 7
Supplier & Products:
Following information needs to be maintained for products of Office Wizard.
 Product ID (which is unique for each product)
 Product Name
 Manufacturer
 Category (furniture, writing equipment, filing equipment, misc, etc)
 Description
 Quantity description (e.g. a rim of paper, a dozen of pens, a box of 10 markers, etc)
 Unit price (based on the quantity description)
 Status (available, out of stock etc.)
 Available quantity
 Re-order level
 Maximum discount – This is the maximum discount that can be provided on the product.
This is given as a percentage of the Unit Price.

Office Wizard obtains their products from various suppliers.  To ensure product availability,
most products are obtained from multiple suppliers.  Following information to be maintained
for each supplier:
 Supplier ID
 Name
 Address
 Phone number
 Fax number
 Contact person
 Product(s) supplied

Prior to ordering goods from suppliers, a quotation is requested. A quotation includes the
following information:
 Quotation number (this is unique for each quotation)
 Date of quotation
 Validity period (in months)
 Description
 Supplier providing the quotation
 Products, quantities and  unit price quoted for each product
 Employee requesting the quotation

A quotation may result in one or more orders of products from Suppliers (known as Supplier
Orders). The following information needs to be maintained with regard to supplier orders:
 Supplier Order Id (which is unique for each supplier order)
 Date Order was made
 Supplier to whom the order is given
 Description
 Quotation leading to the supplier order
 Products ordered, quantities and unit prices for each product
 Total amount of order
 Status (received, completed etc.)
 Order received date
Page 2 of 7
 Payment date
 Payment reference number (unique for each payment). The payment reference number is
a reference to payments made in the Accounting System.

Customer Sales:
Office Wizard receives orders from customers through its sales staff (in person or through the
phone).  Order information includes:
 Date of order
 Products (at least one product per order)
 Unit price for each product
 Quantity ordered for each product
 Discount given (if any)
 Overdue fees (if any)
 Cancellation fees (if any)
 Total amount due
 Billing date
 Due date
 Status (processing, delivered, awaiting payment, completed, etc.)
 Employee managing the sale
 Description

Product prices vary from time to time.  Therefore, when an order is taken, the product prices
should be based on the effective prices at the time of ordering.

A customer order may be paid partially as multiple customer payments. For instance, a large
order may have a deposit prior to customer order is processed etc. All customer payments for
orders need to be maintained in the database. A customer payment includes:
 Date payment is made
 Amount paid
 Customer order to which the payment is made
 Payment reference number (unique for each payment). The payment reference number is
a reference to payments received in the Accounting System.

Office Wizard maintains a list of customer records which include the following detail:
 Company name
 Address
 Phone number
 Fax number
 Email address
 Contact person name (one contact person per company)
 Gender
 Maximum Allowable Credit. A customer can make orders up to the maximum allowable

credit amount without making a payment. A default value of $5000 is given for normal
customers. For trusted customers the maximum credit limit is increased based on the
manager’s discretion.
Page 3 of 7
Human Resources & Payroll:
The following information about employees is stored in the database:
 Employee ID
 Name
 Gender
 Phone number
 Home address
 Home phone
 Date of birth

There are different positions at Office Wizard. Each position has the following information:
 Position id (which is unique for each position)
 Position title
 Hourly rate

Employees are assigned to only one current position. Each assignment has a start date and
an end date (end date is null for a current position). History information about employees’
positions must be maintained.

Employees of Office Wizard are generally divided into two groups: sales and administration.
Each sales staff is given a sales target to meet each quarter.  The sales targets can be
different for each staff and quarter.  For performance monitoring and data mining purposes,
the database needs to keep the historical records of all sales targets for each sales staff:
 Year
 Quarter
 Sales amount to be met

Employees are paid monthly wages. The base pay is determined by multiplying the hourly
rate of the position by the number of hours for the month. Next, any allowances (such as
bonuses etc.) are included. Finally, taxes are deducted to determine the net pay.

Following is an example of a payslip:

Page 4 of 7
EMPLOYEE ID: 5231921
EMPLOYEE NAME: Mr. B. Samuel
POSITION: Sales Manager

DATE: Jan 31
st
2012
PAY PERIOD: 1/12/2011 – 31/12/2011

HOURLY RATE: 40.00
NUMBER OF HOURS: 160 HOURS

BASE PAY: 40.00 X 160  = 6400.00

ALLOWANCES:
* BONUS (2011 SALES)  = 3000.00

TAXABLE INCOME   = 9400.00
TAX      = 1880.00

NET PAY    = 7520.00

Office Wizard provides a number of allowances to its employees – e.g. End of year bonuses
etc. The following information about different allowance types needs to be maintained.
 Allowance type (e.g. Sales bonus, etc.)
 Description (e.g. end of year sales bonus)
 Frequency (e.g. yearly, monthly etc.)

The following information about taxes is maintained in the database.
 The start amount for tax bracket
 The end amount for tax bracket
 Tax rate as a percentage
 Effective year in which the tax bracket is effective

A payslip contains the following information:
 Employee the payslip belongs to
 Start date of pay period
 End date of pay period
 Number of hours worked for pay period
 Hourly rate at which the employee is paid
 Base pay
 Allowances (if any)
 Taxable income
 Tax
 Net pay

All employees will be given a user account in the new database.  To ensure uniformity and
adherence to good security practices, all usernames must contain 8 to 15 alphanumeric
characters; and passwords must contain 8 to 12 alphanumeric characters.
Page 5 of 7

Based on the business requirements, develop a conceptual database design using EER
model. Your lecturer will act as the client and you can speak to him to clarify any questions
regarding the requirements.

Submission

You are required to submit the following:
1. Requirements Document – The requirements document must capture all requirements
that lead to your conceptual database design. Typically the requirements document
consists of:
 Data Requirements – outlining the major data items
 Transaction requirements – outlining the data manipulation and queries
 Business Rules
Hint: Sample requirements documents are available in appendices A and B of your main
text (Databases Systems – A Practical Approach ti Design, Implementation, and
Management – 5
th
Edition by Connolly and Begg 2010) .

You may interview your client (i.e. lecturer) for clarification and include your interview
questions and responses

2. Documented EER Model: The documented EER model includes both the EER model and
the data dictionary. The EER Model must be shown in UML notation. Data dictionary
defines the entities, relationships and attributes of EER Model. Sample format for
documenting the data dictionary is provided below:

Sample format for documenting entities:

Entity Name Description
Item Resources made available to staff and students

Sample format for documenting relationships:

Entity
name
Multiplicity Relationship Multiplicity Entity
name
Page 6 of 7
Description
Item 1..* CategorisedTo 1..* ItemType An item is
categorised to
many different
item types

Sample format for documenting attributes:

Entity
Name
Attribute Description
Student studentId A unique id given to every student in the university

Method of submission: Both softcopy submission and hardcopy submission are required:
 It must be submitted as a word document via Blackboard  Assignments 
Assignment 1.
 Print the submission that goes through Blackboard, hand in the hardcopy to the
lecturer at the beginning of the course lecture. The hard copy must have on the front a
signed INDIVIDUAL Assessment Cover Sheet.

The assessment RUBRIC is given below:

Requirements
Document & Data
Dictionary
(5)

EER Model
(10)
Excellent Good Satisfactory Poor Fail
(5) (4) (3) (1-2) (0)
All requirements
documented in clear
and complete
manner. The
document includes
data requirements,
transaction
requirements and
business rules.
All constructs in the
EER model is
defined and
documented in a
clear, unambiguous
manner.
All requirements are
outlined clearly.
Some minor
mistakes in
requirements
document and data
dictionary.
Many requirements
outlined. Some
requirements
missing/incorrect.
Data dictionary has
most documentation
but with
missing/errors.
Page 7 of 7
A few requirements
outlined with major
sections missing. No
data dictionary or
major sections
missing.
(9-10) (7-8) (4-6) (2-3) (0-1)
EER model is
complete without
any errors or minor
corrections
EER model has
most constructs
correctly
represented.
EER model has
some constructs
correct with major
errors.
Most constructs
have errors. Poor
EER design with
major flaws in
modelling
requirements.
No requirements
document or data
dictionary.
None or few EER
constructs shown.

SOLUTION

LH44