Central Queensland Aged Care Management database system case Study

Objectives

• Todevelop a conceptual data model diagram
• Toperform logical design (optional)

Assignment Specification

In this assignment, you need to perform the following tasks for the given case study:

• Drawan Entity Relationship Diagram (ERD)
• Performlogical design (optional)
• Create3NF Relations (optional)

Case Study: Central Queensland Aged Care Management database system

Central Queensland Aged Care (CQAgCare) is an Australian?owned business located in a large city that specializes in management of aged care and accommodation services for elderly people. The management of CQAgCare has approached you to build a relational database that will be used as the most critical backend software component in their enterprise management system so that the relevant business data will be recorded appropriately, and so that further information query can be processed efficiently. The proposed database system must efficiently record and track all listed rooms in the accommodation, residents, staff and relevant care activities. Considering the following user requirements carefully, design a database conceptual schema (i.e. E-R model) to support such an application.

The major business of CQAgCare is to provide care services and accommodation services to elderly people. The types of care could be Respite Care, Permanent Residential, or Specialised Dementia Care. CQAgCare not only manages residents (elderly people) by providing care services but also manages the accommodation in which these residents stay. There are several types of staff offering

services in CQAgCare such as personal carers, nurses and care managers. The care managers oversee the care program for residents, lead a team of personal carers and nurses and manage the accommodation of the residents. The accommodation could be one of three types of rooms namely single room, double room and double room with ensuite. Each room should be described and recorded with the information including the room type, room no., size of the room, photo of the room, cost of the room per day, as well as the available date. When a resident wishes to utilise an accommodation, he/she is required to make an application, which will be processed by a care manager. If an application is approved, the contract start date, end date, applicant’s details,

applicant’s care needs and daily rental etc. must be recorded in the system. Usually, a room is managed by one care manager while a care manager could take care of multiple rooms. A care manager also can supervise other staff. From time to time, the rooms in the facility may need some maintenance service or minor repair jobs (for example additional fixtures based on care needs, electrical fault, plumbing issues etc.). CQAgCare has contracted the maintenance tasks to a qualified external employee, who will undertake the repair jobs as requested. The external employee could be a licensed electrician, plumber or handyman. The details of the repair job and date of completion of the job must be entered in the system.

The people that the system needs to record include the residents, care managers of CQAgCare, nurses, personal carers and the contracted external employees. Their names, addresses, and contact phones are essential data in the database system. In addition, you should consider one or two extra attribute(s) to describe the residents, care managers and the external employee.

The specification of above application scenario may be not presented explicitly with every detail on the entities or relationships in the required E?R model because one of your tasks for this assignment is to analyze the requirement of users, identify necessary entities and relationships, and build the E?R data model for the case study. You may need to do some research on the data and possible model in general aged care management with complete attributes or under some assumptions, which should make sense for such an application scenario.

Assignment Requirements

In this assignment, you need to create an Entity Relationship diagram (ERD) relevant to the above case study and optionally perform logical design to produce appropriate 3NF Relations. The attributes for various entities have not been purposely described in the case study so that the students are encouraged to further research and list the pertinent attributes in addition to the required identifier for each entity in their ERD.

1 Draw an appropriate ER diagram

Use the symbols as prescribed in your unit textbook to draw the Entity Relationship diagram (ERD) for the above case study.

Your ERD must

• showall necessary entities, attributes and relationships
• showidentifiers/identifying attributes as necessary
• showmulti-valued attributes, if any
• showparticipation and cardinality
• showassociative entities, if appropriate
• showweak entities, if any
• use thenotation described in the set text
• useconsistent and appropriate naming for entities and attributes as specified in the text 

Some business rules or other aspects of the case study may not be clear to you when you read the case study. If this is the case, then you should either approach your lecturer or tutor for clarification, or you may simply make an assumption and then develop your ERD accordingly.

For example, the case study might not mention all relevant participation information (also called minimum cardinalities). If so, you may make an assumption about what the minimum cardinalities might reasonably be, and then show them in your ERD accordingly.

You should justify each assumption in terms of the business, for example: if E-R model for customers placing an order it is assumed that each customer must have at least one order because it is assumed that the business does not record customer details until the customer places an order. To get yourself started, ask yourself, 'If I were running this business, what things do I need to keep a list of?' Write those things down. For each thing, what information would you need to record about it? How can it be identified? The answers to these questions will help you to develop your ERD.

2 Assumptions and Business rules

Provide assumptions and business rules relevant to your ERD.

3 Create Logical Design (Optional):

If you would like to perform this optional component of this assignment, you can map your Entity Relationship Diagram (ERD) into relations and make sure that all the relations are in 3NF. Provide all the relations in the following format (for example):

Customer (CustomerId, CustomerName, Street, Suburb, State, PostCode, Email)

Invoice (InvoiceNumber, InvoiceDate,Total, CustomerId)

foreign key (CustomerId) references Customer(CustomerId) How and what to submit:

You must make an electronic submission for this assignment using the appropriate assignment submission link on the unit web-site.

You should submit a Microsoft Word document containing:

• A copyof your 
• Alist of business rules and assumptions including their 
• 3NFRelation - Optional

Your document should contain appropriate identifying information (your student number, name, unit number, etc.) and should contain appropriate headings for each section. Note: We recommend that you use Microsoft Visio to draw the Entity Relationship diagram (ERD) and then copy & paste it to the Word document.

Important points

• Youmust work individually on this 
• The model solution to this assignment will be approximately TWO (2) weeks after the due
• Assignments that are released submitted after the release of the model solution will receivezero marks
• You must state in your assignment (at the end under the heading Acknowledgements is agood place) all materials and sources you used. This may be as simple as 'This assignment was completed using only the COIT20247 unit materials' to mention that you have used only the items provided by the unit COIT20247
• Before submitting your assignment, review the marking criteria that are available in theassignment  Ensure that all the criteria are addressed in your submission.
• After submitting your assignment, please verify that your submission was successful, i.e.download your submitted files to ensure that they are 
• Penaltyrelated to late submission and plagiarism is handled as per the university policies.