Database Design

**_Winterbourne Nursing Home_** The Winterbourne Nursing Home provides care services for older people with mental health and physical health problems within the local community. A consultant refers potential residents to the home. Residents are allocated a Primary Nurse who is responsible for ensuring a detailed assessment of the needs of the resident is carried out. The Primary Nurse is part of a team of carers who develop a special caring relationship with the resident so that care needs are met. Over a period of time the residents are assessed for their mobility, their manual handling, dietary needs and personal hygiene needs. Records are presently kept using a paper based system. However the home would now like to have a partially computerised system in order to provide a baseline against which improvement and deterioration may be judged. The home also has day care patients who are also cared for by a Primary Nurse. Day care patients will come to the home on certain days in the week in order to give their families a break from care. These day care patients are not monitored in the same way as the residents and are dropped off in the morning and picked up in the evening. The information relevant to the computerised system is as follows (see deliverables). ## Deliverables _**Winterbourne Nursing Home**_ The Winterbourne Nursing Home provides care services for older people with mental health and physical health problems within the local community. A consultant refers potential residents to the home. Residents are allocated a Primary Nurse who is responsible for ensuring a detailed assessment of the needs of the resident is carried out. The Primary Nurse is part of a team of carers who develop a special caring relationship with the resident so that care needs are met. Over a period of time the residents are assessed for their mobility, their manual handling, dietary needs and personal hygiene needs. Records are presently kept using a paper based system. However the home would now like to have a partially computerised system in order to provide a baseline against which improvement and deterioration may be judged. The home also has day care patients who are also cared for by a Primary Nurse. Day care patients will come to the home on certain days in the week in order to give their families a break from care. These day care patients are not monitored in the same way as the residents and are dropped off in the morning and picked up in the evening. The information relevant to the computerised system is as follows. **All Patients:** * Nursing Home Number - unique for each patient * National Insurance Number * Name * Date of Birth * Religion * The patient’s next of kin. The home would like to keep up to three names with addresses and phone numbers. * Each patient has a set of base line details. This is a recording of the patient's weight, pulse, temperature, blood pressure and the date these were recorded. The patient's will have these base line details taken once a month. Past readings will have to be kept in order to trace a patient's development over time. * The patient's doctor (G.P.) outside the home, with the address and telephone number. * The Primary Care Nurse allocated to the patient. **Day Care Patients:** * Day care patients will have a permanent address and phone number recorded. * Social Worker Name * Weekdays they are at the home ??" this does not vary from week to week * Arrival Time * Collection Time **Resident Patients:** The residents have a range of resident handling assessment criteria which they are assessed against. These are:- a) Their sleeping habits need to be recorded. These can be either insomnia, restless, sleepwalks, normal (only one is recorded). b) Their mental awareness is recorded on a scale of 1-5. With 1 being poor mental awareness. c) Their ability to dress themselves is recorded. These are either needs complete help, some help, or can dress unaided. d) Their mobility is recorded on a scale of 1.5. With 1 being poor mobility. e) Their personal hygiene is recorded on a scale of 1-5. With 1 being poor personal hygiene. The home needs to be able to change the current assessment criteria values if the patient improves or deteriorates and to be able to record the date they were changed. **Staff:** * The staff number (unique) * Address and Phone number * Job type (primary care nurses, care assistants and administrative staff) * The member of staff's next of kin. The home would like to keep up to three names with addresses and phone numbers. _REQUIREMENTS_ **1. ** **Draw an Enhanced Entity Relationship (EER) diagram for the Winterbourne Nursing home (40%)** Show the structural constraints using the minimum and maximum number notation. Explain any assumptions you have made. ** Hand-in:** The model (on one or two pages with assumptions if necessary). The model can be hand-drawn or completed with a tool. No extra marks are awarded for using a drawing tool. **2. ** **Map the diagram to a set of relations (40%)** Using the Enhanced ER to Relational mapping algorithm outlined in attached file (Presentation 1) map the diagram to a set of relations. Hand in the final list of tables. Show the process you went through to achieve these tables (that is, include the steps undertaken). Comment on any design decisions taken. **3. ** **Design preliminary SQL CREATE TABLE statements for the relations. Include the necessary referential, entity and integrity constraints (20%).** This is a test to establish whether the tables will meet the system’s requirements. Exact syntax is not required, e.g., you will not be penalised for missing commas, or semi-colons, but the statements must be recognisable as SQL. There are no marks awarded for creating the tables in any package, although you may wish to use Oracle to do this in order to test if your statements are valid. Make appropriate assumptions over the data types; generally use VARCHAR2 for character fields, NUMBER for numeric fields and DATE for any date fields. **Note:** **The diagram can be hand-drawn; alternatively, if you have access to software such as SmartDraw or CorelDraw, these can be used to draw Enhanced ER Diagrams. SmartDraw is easier to use since it has built-in symbols for this approach.** **Note, SmartDraw offers a free 1-week download from this site: [[login to view URL]][1]** **If you copy and paste the diagram into a Word document, it will include a banner on your diagram to indicate that it is a trail copy, but this is acceptable.** **Alternatively, you can try using the symbols found in Word or PowerPoint, but you may not be able to represent all the notation required, which you will need to add by hand.** ## Platform Part 3 should be completed using Oracle.