Database Design Assignment
1. General Information
The purpose of this assignment is to provide you with experience in analysing and designing a database 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 your assignment to the Moodle site of this subject. Marked assignments will be available from Moodle website of this subject.
2. Problem Description
The Canberra Health Insurance (CHI) was established recently. It has three branches in Canberra. CHI requires your team to design a database system to enable more smooth operation of the CHI by providing timely information to the CHI staff. The details of CHI branches are:
|
Branch name
|
Address
|
Telephone numbers
|
|
CHI Dickson
|
150 Camilla Way, Dickson, 2662, ACT
|
62016161
62016162
|
|
CHI Page
|
12 Page St, Page, 2614, ACT
|
62016163
|
|
CHI City
|
121A Future Ave, Braddon 2612, ACT
|
62016163
62016164
|
CHI database will record and store the data about each of its clients including first and last name, email address, postal address and home address (street number, street name, suburb, post-code, city, state), gender, date of birth, and type of health insurance a client has. 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 are also stored in CHI database.
CHI offers several types of health insurance. Health insurance policies are managed under development section of CHI. There exist several types of health insurances. These are namely Budget health insurance, Full cover health insurance, Extra plus health insurance. A client can take only one type of health insurance from CHI.
CHI health insurance s price per year is different for normal clients and senior clients of CHI. A senior client is a person who is over 65 years old. The details and cost of CHI health insurances are:
|
Health Insurance ID
|
Health insurance type
|
Cost (normal client)
|
Cost (senior client)
|
|
M1
|
Budget
|
$800
|
$400
|
|
M2
|
Full cover health
|
$1200
|
$600
|
|
M3
|
Extra plus health
|
$1500
|
$800
|
Each client is required to make a payment for their health insurance each year. Health insurance payments can be performed via the CHI branches or CHI website. Before a client takes a health insurance she/he is required to attend an interview with a staff member of CHI to discuss their health insurance needs. The interview takes only ten minutes.
Each client needs to book an appointment for their interview before they can take a health insurance by calling a CHI branch to book an appointment. The timetable and availability of each CHI staff member is recorded in CHI database and are available on the CHI website.
When a client takes a health insurance she/he is assigned a client number and her/his details are recorded. Every client is provided with a client number and the rules of CHI as well as the details of their health insurance.
CHI has several staff members in each branch. For each staff member the following data is stored in the database system of CHI: staff first and last name, staff number, position, gender, date of birth, email address, name of the section she/he works in, internal telephone number and branch number.
Each branch of CHI consists of three sections. These sections are: administrator section, development section and finance section. The information about each section is stored in the CHI database. The information about each section includes section name, location, email address, telephone and fax number.
CHI provides a gym for the use of their clients. The use of CHI gym is free of charge for CHI clients. CHI gym provides several classes. Each client needs to book for each class beforehand by calling the CHI on 1800811811 or using the GHI website to book a class. The timetable and availability of each class and the name of class instructor is recorded in CHI database and are available on the CHI website.
CHI gym has several instructors in its Gym. For each instructor the following data is stored in the CHI database system: instructor 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 or Yoga classes), internal telephone number, CHI gym number and CHI gym address.
Each instructor is limited to teach a maximum of three types of classes at any particular day. 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, details of the clients for each instructor) are stored in the CHI database.
CHI 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:
|
Monday
|
Tuesday
|
Wednesday
|
Thursday
|
Friday
|
Saturday
|
|
9:00-10:00am
Body Balance Instructor: Mary Douglas
|
9:00-10:00am
Body Balance Instructor:
Mary Douglas
|
9:00-10:00am
Yoga
Instructor:
Mary Douglas
|
9:00-10:00am
Yoga
Instructor:
Mary Douglas
|
9:00-10:00am
Body Balance Instructor:
Jim Lee
|
9:00-10:00am
Body Balance Instructor:
Arif Khan
|
|
10:00-11:00am
Yoga Instructor: John Bossy
|
10:00-11:00am
Yoga
Instructor:
John Bossy
|
10:00-11:00am
Body Pump Instructor:
Sue Jones
|
10:00-11:00am
Body Pump
Instructor:
John Bossy
|
10:00-11:00am
Yoga
Instructor: Andrew Low
|
10:00-11:00am
Yoga
Instructor:
John Bossy
|
|
1:00-2:.00pm
Body Pump Instructor: Jane Pontiac
|
1:00-2:.00pm
Body Pump Instructor:
Jane Pontiac
|
1:00-2:.00pm
Body Balance
Instructor:
David Holden
|
1:00-2:.00pm
Body Balance
Instructor:
Jane Pontiac
|
1:00-2:.00pm
Body Pump
Instructor:
Jane Pontiac
|
1:00-2:.00pm
Body Pump
Instructor:
Helen Ford
|
|
5:00-6:00pm
Yoga Instructor:
Ali Khan
|
5:00-6:00pm
Yoga
Instructor:
Ali Khan
|
5:00-6:00pm
Yoga
Instructor:
Jane Jones
|
5:00-6:00pm
Yoga
Instructor:
Jane Jones
|
5:00-6:00pm
Yoga
Instructor:
Helen Ford
|
5:00-6:00pm
Yoga
Instructor:
John Bossy
|
|
7:00-8:00pm
Body Balance Instructor: Maggie Smith
|
7:00-8:00pm
Body Balance Instructor: Maggie Smith
|
7:00-8:00pm
Body Balance Instructor: Maggie Smith
|
7:00-8:00pm
Body Balance Instructor:
Jim Jones
|
7:00-8:00pm
Body Balance Instructor: Maggie Smith
|
7:00-8:00pm
Body Balance Instructor:
Ron Boltzman
|
CHI keeps track of maintenance schedules for each of their gym equipment. The gym equipment details and their service history are recorded in CHI database. Service history details are: service history number, equipment details, date of maintenance, description of maintenance.
CHI asks their instructors to record their name, details of each class and any comments about training equipment used in their class after each class. All gym training equipment are serviced once every three weeks and the service details are recorded in CHI database.
CHI contacts gym training equipment suppliers as soon as a fault is report for any gym training equipment by an instructor or a client to repair the equipment. Training equipment is replaced every 18 months by CHI. Details of all faulty equipment is recorded in CHI database with the details of the person who has reported the fault. Equipment fault report details are: equipment details, comments about faults, client or instructor detail who have reported the equipment fault, date and time of fault report. Details of all supplier of all gym equipments are stored in CHI database. Supplier details are: supplier number, supplier name, address, telephone number.
CHI has 60 gym training equipment. Four new gym training equipment is added to the existing CHI's gym training equipment every month.
CHI gym has three seminar rooms. CHI clients and staff can book CHI seminar rooms. Details of CHI clients that book seminar rooms are stored in CHI database. These details are: client number, client first and last name, seminar room number and date and time for which a seminar room is booked.
Details of staff that book a seminar room for a CHI client is also recorded in CHI database. These details are: staff number, staff first and last name, position of staff, date and time for which a seminar room is booked. Hiring of seminar rooms is free for client and staff of CHI.
Requirement: For the scenario in the problem above:
(a) Identify entity types and their attributes, including the primary keys. You need to submit the list of your entities, with all attributes of each entity in the following format in a Word for windows file. You need to underline the primary key/s of each of your entities. Example: Section(SectionID, SectionName, ........)
(b) Compile the E-R of the system in third normal form. (state all assumptions that you have made). Note: If you make any assumptions, they should be explained clearly.
Submit your E-R diagram, all assumptions with any other documents with your assignment to Moodle site of Database Design(5915)/Database Design G (6672) on the due date specified above.