1. General Information
The purpose of this
assignment is to provide you with experience in analysing and designing adatabase for a given problem. It will help you to understand the nature and
purpose of database analysis and design.
This assignment is an individual assignment. There are no
restrictions on the use of word processors or similar tools for the production
of submissions for this assignment.
Be sure to
maintain regular back-ups for any models or material prepared with the aid of
software. Loss of files will not be accepted as an excuse for non-completion of
this assignment.
Submit assignment to the Moodle site of this subject. Marked assignments will be
available from Moodle website of this subject.
2. Problem Description
The Magic Fitness Centre (MFC) was
established recently. It has two branches in Canberra. MFC requires your team
to design a database system to enable more smooth operation of the gym by
providing timely information to the MFC staff. The details of the branches of MFC
are:
|
Branch
name
|
Address
|
Telephone
number
|
|
CF Dickson
|
150
Camilla Way, Dickson, 2662, ACT
|
62126666,
62127777
|
|
CF City
|
121A
Nortbourne Ave, Braddon 2612, ACT
|
62612222
|
MFC database will record and store the data
about each of its clients and gym instructors including first and last name, email
address, home address (street number, street name, suburb, post-code, city,
state), gender, date of birth and type of gym classes a client is enrolled in.
The name (first and last name), contact telephone number and address (street
number, street name, suburb, post-code, city, state) of client’s next-of-kin is
also stored in MFC database.
MFC offers several types of gym classes. Gym classes are managed under
class development section of MFC. There exist several types of gym classes.
These are namely Body Balance, Body Pump and Yoga classes. A client can be
enrolled in up to five different classes at anytime. Full gym membership price
for a year is $800 for all normal clients and $400 for senior clients of MFC. Senior
client is a person who is over 65 years old. There are also three and six
months Gym memberships. The details and cost of MFC gym memberships are:
|
Gym
membership ID
|
Gym
membership type
|
Cost (normal
client)
|
Cost
(senior client)
|
|
M1
|
12 Months
|
$800
|
$400
|
|
M2
|
6 Months
|
$400
|
$200
|
There are casual rates for non-members of MFC too attend a class. Casual
class rates are:
|
Class
type
|
Cost per
hour
|
|
Body Balance
|
$10 per
class
|
|
Body Pump
|
$10 per
class
|
|
Yoga
|
$8 per
class
|
If a casual client wants to attend
a class then the client should make a payment for his/her class before he/she
can attend that class.
The payment for causal clients is
made at MFC branches. Before the first class, a client is required to attend an
interview with his/her instructor to access the client’s needs. The interview
takes 10 minutes before the class.
When a client joins MFC he or she
is assigned a client number and his/her details are recorded. Every client is
provided with a client number and the rules of MFC.
Clients are not required to book
for each class. However the detail of each client attending each class is
stored by MFC. These details are Class type, Date, Time, Client number as well
as the client first and last name.
MFC has several staff members in
each branch. For each staff member the following data is stored in the database system of MFC: staff first and last name, staff number, position, gender, date
of birth, name of the section he/she works in, branch telephone numbers and
branch number.
For each instructor the following
data is stored in MFC database: staff first and last name, instructor number,
position, gender, date of birth, type of classes that an instructor can teach
(i.e. Body Balance, Body Pump, Aqua aerobics, Yoga), branch telephone number
and branch number of all branches in which the instructor teaches a class.
The information about each branch:
branch name, address, telephone and fax number. Class time tables are stores in
MFC database.
The instructor’s identity is
established with their ID number and it is used to retrieve the details of the
classes they teach. The details of each class (class number, Instructor ID
number, date and time) are stored in the MFC database.
MFC website provides the class
timetable and instructor availability facilities where a client can check the
availability of a certain classes with the details of the class instructors
teaching that class. The weekly timetable of classes is:
|
|
|
|
MFC keeps track of maintenance schedules for each of their gym equipment.
A MFC staff contacts gym equipment suppliers as soon as a fault is report for any
gym equipment to repair the equipment. The details of the faulty equipments as
well as the staff umber of MFC staff that reported the fault to a supplier,
with the date and time of equipment fault report is stored in MFC database. MFC has several gym equipment
suppliers. For each supplier the following data is stored in the databasesystem of MFC: supplier name, supplier number, address, telephone number.
Equipment details are also stored in MFC database. These details are: equipment
number, equipment name, cost and date of purchase as well as supplier number.
MFC has two seminar rooms. These seminar rooms
are rented to any customer that wants to organise a seminar. The cost of hire
of a seminar room is $400 per day. Seminar room details are stored in MFC database.
The seminar room details are: room number, room size, room location and rental
price per day. Detail of customers that hire a seminar room is also stored in MFC
database. These details are: customer number, customer first and last name,
dates for which a seminar room is hired (i.e. start date and end date). MFC provides food if required for any seminar booked at
MFC. The details stored about all seminar rooms hired and food provided is
recorded in MFC database. For each seminar the number of guest are recorded in
the MFC database and the type and price of food ordered for each seminar is
also recorded in the database of MFC.
Requirement:
For the scenario in the problem above:
(a)
Identify entity types and their
attributes, including the primary keys. (20 Marks)
(b)
Compile the E-R of the system in
third normal form. (state all assumptions that you have made) (10 Marks)
Note: If you make any assumptions, they
should be explained clearly.
Submit your list of entities and their attributes, including
the primary keys, your E-R diagram and all assumptions you have made to Moodle
site of Database Design(5915)/Database Design G (6672) on the due date
specified above. The first page of your assignment should include the following
information:
No comments:
Post a Comment