Assignment
A. Read the case study and develop an Entity-Relationship Diagram using the conventions for this unit. This diagram needs to describe all of the business rules in the case study.
B. Using this unit's Entity-Relationship to Relational Schema Algorithm, convert your Entity- Relationship Diagram into a relational schema.
C. Using MySQL create the database based upon your relational schema.
D. Enter two records into each of the relations that you have created in your MySQL database.
E. Correctly and appropriately comment your code so that it can be maintained by your client.
F. If you are stuck at any stage - speak to your tutor or email your Unit Coordinator for help.
Case Study
Ageing with Style Home Care is managed by Jane Thomas, a local philanthropist, and has been providing home and community care services (including personal care, gardening and domestic cleaning assistance) since 2012 from its Hobart office. The business has proven to be a popular choice among elderly Hobart residents in the 6 years it has been operating, however due to rising costs of inner-city office rentals in Hobart the business has decided to abandon its headquarters in favour of moving the operations to an online business model.
Ageing with Style Home Care provides personal care, gardening services and domestic cleaning services. Clients can access the services after receiving a diagnosis from their medical practitioner.
Potential clients of the business are assigned an ID when they enter their name into the web-based system. Each client registers their address, consisting of a street number, street name, suburb and postcode. Each client has one and only one address, but each address may have more than one client.
Each client is met by zero or more staff members before they leave the service, and each staff member will meet one or more clients. Staff members are identified by their Staff ID. Staff names, sex and age are also collected. Because this is a small organisation, each of the staff members are managed by zero or one staff members, except Jane Thomas who does not have a manager. The staff members may manage zero or more staff members.
Each diagnosis will affect zero or more clients, and each client may be affected by zero or more diagnoses. Diagnoses are identified with a diagnosis code, the medical practitioner ID and a description of the diagnosis.
Each diagnosis accesses one or more services from this business, and each service is used for one and only one diagnosis.
Each staff member holds one and only one qualification, and each qualification may have zero or more staff members qualified by it. Qualifications are uniquely identified by their name, but the database also holds some details about the qualification.
Given that each of the staff members specialise in a different area, each service is conducted by one and only one staff member, but a staff member may conduct many services or none at all. Each service has a service code and some general details about the service which should include what kind of service it is.
Ageing with Style Home Care hopes that these business rules will help you design and implement a database that includes some sample data that is appropriately commented so that Ageing with Style's in- house staff can further implement and develop the database.