Reference no: EM131438798

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.