Aim:

To give you practical experience in using Entity-Relationship and Relational Database modelling

techniques.

Project Specification

The proprietors of BigM have approached you and asked if you could design a database to help them

manage their business. The management has commissioned you (in your capacity as a Database

Management System consultant) to analyse, design and develop an appropriate conceptual data model

and relational database schema, based on the following information gathered about the current

business activities.

BigM operates stores in many cities in Australia. Stores are referenced by store number. BigM

also keeps store name, phone, fax, email for each store. Each store has a postal address

containing street address, city, state and postcode.

Each store has a number of departments, for example, finance, accounts, sales, customer

service etc. For each department, there is a department ID, department name, phone and e-mail

address.

Each department has a number of employees. For each employee, BigM keeps a record of their

employee ID, first name, last name, phone, date of birth, start date of his/her work and tax file

number.

BigM also stores postal and residential addresses of their employees. For each address they

need to store street address, city, state and postcode.

An employee can be employed as casual or fulltime basis. For a casual staff, his or her job type

(for example, accountant, sales, customer service etc.), and hourly rate is recorded. For a

fulltime staff, his or her job type and annual salary is recorded.

Each store is managed by an employee as a store manager and each department is supervised

by an employee. The department supervisor is also the supervisor for all the staffs within that

department.

Each store may be assigned a supervising store where all training, payroll, server application

and help desk are located.

Each supervising store generates pay slips for all staffs (in this store and other stores being

supervised) on a fortnightly basis. For each pay slip, the store records a pay ID, supervising

store ID, employee ID, pay date, number of hours and the gross payment.

BigM sales different products like CDs, cloths, computers etc. For each CD it keeps a record of

CD number, title, length, number of tracks, release date, category description, price and reorder

level.

BigM would like to maintain information for each artist in the CD. They would like to include first

name, last name and date of birth and website address, if they have one. If possible, BigM also

wants to store number of tracks where a particular artist appears in a given CD.

For cloths and other products it stores product number, product description, brand, product size

and price.

An inventory of the number of each particular product in each store is kept. BigM keeps track of the quantity of each product that is on order, as well as the number currently available in each

store.

Customer may place orders in the store. Customer details are always taken at each order. A

customer is referenced by a customer number, customer first & last names, phone number and

postal address, if available. For each address they need to store street address, city, state and

postcode.

A customer may order more than one product at a time, and they may order multiple copies of

the same product. BigM also records the date a product arrives and the date when it is picked up

by the customer. Note that these dates may be different for each product.

BigM understands that they may not have provided you with sufficient information. If you need to

make assumptions about their organisation please ensure that you record them.