KIT102 Introduction to Data Science- Entity Relationship

1. 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.

3. Using MySQL create the database based upon your relational schema.

4. Enter two records into each of the relations that you have created in your MySQL database.

5. Correctly and appropriately comment your code so that it can be maintained by your client.

6. In a Word Document or PDF, submit your Entity-Relationship Diagram and relational schema including all of the steps involved in the conversion algorithm to MyLO. Using Visio is recommended for the E-R Diagram, but any diagramming package, or a hand-drawn diagram included as an image in your Word Document or PDF is acceptable.

9. 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.

10. 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. 

11. 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.

12. 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.

13. Each medical practitioner provides one or more diagnoses, and each diagnosis is provided by one and only one medical practitioner. Medical practitioners are given a unique ID and the database also holds their name.

14. Each diagnosis accesses one or more services from this business, and each service is used for one and only one diagnosis.

15. 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.

16. 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.

17. Each service must be approved by the one and only one funding body. The funding body must approve one or more services. The funding body is identified with the funder ID, and some details are collected about the funding body.

18. A mobility test may be requested by one or more clients, but each client needs only request one and only one mobility test. Mobility tests are identified by a test ID and some test details are also recorded.

19. A mobility testing facility will be identified by its Facility Code. Test details are also recorded. Each mobility test is sent to one and only one mobility testing facility, and that mobility testing facility may receive zero or more mobility tests.

20. The mobility testing facility generates zero or more mobility test results and mobility test results are generated by one and only one mobility testing facility.

21. 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.