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.
The Canberra Health Services (CHS) was established recently. It has three medical centres in Canberra. CHS requires your team to design a database system to enable more smooth operation of their medical centres by providing timely information to the CHS staff. The details of the CHS medical centres are stored in CHS database and they are: Branch number, address, telephone number.
|Branch number||Address||Telephone number|
|B10||150 Camilla Way, Dickson, 2662, ACT||CHS Dickson|
|B11||12 Market Place, Gungahlin, 2911, ACT||CHS Gungahlin|
|B12||66A Rosedale Ave, Braddon 2612, ACT||CHS City|
CHS 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), sex and date of birth. 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 CHS database.
CHS offers several types of medical services. CHS activities are managed under service development section of CHS in each branch. There exist several types of services. The services offered by CHS are namely: doctor consultation, consultation follow ups, general test and consultation.
The information about services includes: service name, service number, service type, cost as well as full name of the doctor that can provide such service.
A client can use several services at any given time. Services are of different prices. Price of all services provided at CHS is stored in CHS database.
When a client uses a service of CHS then the client should make a payment for his/her service at CHS medical centres. At first visit to CHS, a client is required to attend a 5 minutes interview with one of the CHS nurses to access the client’s needs. Details of client interviews are recorded in CHS database. These details include: staff name, client name, date and time of interview, client special needs.
Each client needs to book for each appointment beforehand by calling the CHS on 1800811811.
When a client makes an appointment at CHS, he or she is assigned an appointment number and his/her details are recorded. Every client is provided with a client number. Clients use their client number to book appointments.
Details of all appointments made by all clients are stored in CHS database. These details are: appointment number, client number, data and time for which an appointment is booked, name of the staff who will see the client at the appointment.
Details of all payments by all clients for an appointment are stored in CHS database. These details are: payment number, service number, client name, amount paid, payment type (credit, cash, cheque), date of payment.
After each appointment the details of the treatments recommended for each client is stored in CHS database in CHS database. If there are any prescriptions given to a client then the prescription number for the client is stored in CHS database. These details are: Doctor name, appointment data and time, client name and address, prescription number, appointmentNo.
CHS has three sections in each of branches. The details of sections are: section number, section name, branch number. CHS has several staff members in each medical centre. For each staff member the following data is stored in the database system of CHS: staff first and last name, staff number, position, specialisation (i.e Nurse, doctor, office staff) sex, date of birth, name of the section he/she works in, internal telephone number and medical centre number, section number.
CHS sends a reminder SMS to each client 24 hours before their appointment. The details of all reminders sent as SMS are stored in CHS database. Reminder SMS sent to clients contain client name, their appointment date and time, and the address of the medical centre at which the appointment is held as well as the name of the medical staff who will examine the client.
CHS has three seminar rooms in each branch. They are used by CHS staff to organise seminars. These seminar rooms are used to deliver seminars about health and well beings. The hiring of a seminar room is free of charge for staff. Seminar room details are stored in CHS database. Seminar room details are: seminar room number, room size, room location. Detail of staff that books a seminar room is stored in CHS database. These details are: staff number, staff first and last name, date and time for which a seminar room is booked, seminar room number.
The details of all clients that attend each seminar are stored in CHS database. These details are: client number and client name, seminar room number, seminar date and time.
For the scenario in the problem above:
- (a) Identify entity types and their attributes, including the primary keys (in third normal form).
- (b) Compile an E-R diagram for the above scenario based on your solution part (a) above. (state all assumptions that you have made)
Note: If you make any assumptions, they should be explained clearly.
Submit the list of your entities and attributes including primary keys of your solution with its E-R diagram and all assumptions to Moodle site of Database Design(5915)/Database Design G (6672) on the due date specified above.