CentralQueenslandAgedCareManagementdatabasesystem case Study

Objectives

• Todevelop aconceptualdatamodeldiagram
• Toperformlogicaldesign(optional)

AssignmentSpecification

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

• DrawanEntityRelationshipDiagram(ERD)
• Performlogicaldesign(optional)
• Create3NFRelations (optional)

CaseStudy:CentralQueenslandAgedCareManagementdatabasesystem

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

The major business of CQAgCare is to provide care services and accommodation services to elderlypeople. The types of care could be Respite Care, Permanent Residential, or Specialised DementiaCare. CQAgCare not only manages residents (elderly people) by providing care services but alsomanagesthe accommodation inwhichtheseresidentsstay.Thereareseveraltypesofstaffoffering

services in CQAgCare such as personal carers, nurses and care managers.The care managersoversee the care program for residents, lead a team of personal carers and nurses and manage theaccommodation of the residents. The accommodation could be one of three types of rooms namelysingle room, double room and double room with ensuite. Each room should be described andrecorded with the information including the room type, room no., size of the room, photo of theroom, cost of the room per day, as well as the available date. When a resident wishes to utilise anaccommodation, he/she is required to make an application, which will be processed by a caremanager.Ifanapplicationisapproved, the contract start date,enddate, applicants details,

applicants care needs and daily rental etc. must be recorded in the system. Usually, a room ismanaged by one care manager while a care manager could take care of multiple rooms. A caremanager also can supervise other staff. From time to time, the rooms in the facility may need somemaintenance 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 qualifiedexternal employee, who will undertake the repair jobs as requested. The external employee could bea licensed electrician, plumber or handyman. The details of the repair job and date of completion ofthejobmust beentered inthe 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 contactphones are essential data in the database system. In addition, you should consider one or two extraattribute(s)todescribethe residents,caremanagers andthe externalemployee.

The specification of above application scenario may be not presented explicitly with every detail onthe entities or relationships in the required E?R model because one of your tasks for this assignmentis to analyze the requirement of users, identify necessary entities and relationships, and build theE?R data model for the case study. You may need to do some research on the data and possiblemodel in general aged care management with complete attributes or under some assumptions,whichshouldmakesense for suchanapplicationscenario.

AssignmentRequirements

In this assignment, you need to create an Entity Relationship diagram (ERD) relevant to the abovecase study and optionally perform logical design to produce appropriate 3NF Relations. Theattributes for various entities have not been purposely described in the case study so that thestudents are encouraged to further research and list the pertinent attributes in addition to therequiredidentifierforeachentity in theirERD.

1DrawanappropriateERdiagram

Use the symbols as prescribed in your unit textbook to draw the Entity Relationship diagram (ERD)fortheabovecasestudy.

YourERDmust

• showallnecessaryentities,attributesandrelationships
• showidentifiers/identifyingattributesasnecessary
• showmulti-valuedattributes,ifany
• showparticipationandcardinality
• showassociativeentities,ifappropriate
• showweakentities,if any
• use thenotationdescribedinthe set text
• useconsistentandappropriate namingforentities andattributesasspecifiedinthetext

Some business rules or other aspects of the case study may not be clear to you when you read thecase study. If this is the case, then you should either approach your lecturer or tutor for clarification,oryoumay simplymakeanassumptionandthen developyour ERDaccordingly.

For example, the case study might not mention all relevant participation information (also calledminimum cardinalities). If so, you may make an assumption about what the minimum cardinalitiesmightreasonably be,andthen showthem inyour ERDaccordingly.

You should justify each assumption in terms of the business, for example: if E-R model for customersplacing an order it is assumed that each customer must have at least one order because it isassumed that the business does not record customer details until the customer places an order. Toget yourself started, ask yourself, 'If I were running this business, what things do I need to keep a listof?' Write those things down. For each thing, what information would you need to record about it?Howcanit beidentified?Theanswerstothesequestions willhelpyoutodevelopyourERD.

2AssumptionsandBusinessrules

ProvideassumptionsandbusinessrulesrelevanttoyourERD.

3CreateLogicalDesign(Optional):

If you would like to perform this optional component of this assignment, you can map your EntityRelationship Diagram (ERD) into relations and make sure that all the relations are in 3NF. Provide alltherelations inthefollowingformat (forexample):

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

Invoice(InvoiceNumber,InvoiceDate,Total,CustomerId)

foreign key (CustomerId) references Customer(CustomerId)Howandwhattosubmit:

You must make an electronic submission for this assignment using the appropriate assignmentsubmissionlinkontheunitweb-site.

YoushouldsubmitaMicrosoftWorddocumentcontaining:

• A copyofyour
• Alistofbusinessrulesandassumptionsincludingtheir
• 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)andthen copy&pasteittotheWorddocument.

Importantpoints

• Youmustwork individuallyonthis
• 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 receivezeromarks
• 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 assignmentwas completed using only the COIT20247 unit materials' to mention that you have used onlytheitemsprovided bytheunit COIT20247
• Before submitting your assignment, review the marking criteria that are available in theassignmentEnsure thatallthecriteriaare addressedinyoursubmission.
• After submitting your assignment, please verify that your submission was successful, i.e.downloadyour submittedfilestoensurethatthey are
• Penaltyrelatedto latesubmissionandplagiarismishandledaspertheuniversity policies.