E-R Diagram Hospital Management

Databases
1

Could someone please assist with enhanced E-R Diagram given the following information:

  1. A hospital is composed of 10 wards; each ward has up to 30 rooms.
  2. Each ward is uniquely identified by a unique number (e.g. W1) and name (e.g.“Obstetrics”).
  3. There are two main groups of stakeholders: hospital staff and patients.
  4. Hospital staff is exhaustively divided into care (GPs, nurses, social carers) and administration (administrative staff).
  5. Every hospital staff member is necessarily also a member in one of the subgroups.
  6. Some carers do both social care and nursing.
  7. The hospital provides two basic services to patients: treatments and
    medication
  8. GPs administer medicine and treat patients. Nurses do only administer
    medication. Social cares only provide treatment.
  9. Each treatment has a name and one of three priority levels: low, medium,
    high. Medications have a dosage and times when they should be administered.
  10. Each patient has a patient admittance number and name written on a wrist band and stays in a room. A room has up to ten beds, but a patient with private health insurance has their own room. The private health insurance number is recorded for all such patients. It is important to know which beds are free in cases of emergency (e.g., flu outbreak)
  11. A patient’s address comprises house/unit number, street, city and postcode.

Could you create the database and entities, relational model. As having trouble understanding the concept.

:)Thank you so much, in advance.:slight_smile:

2

sorry, we don’t do homework assignments here at sitepoint

you must make an effort yourself – show us what you’ve done, and ask questions if you think something isn’t right

3

wasn’t asking to do the assignment for me.just to give me some ideas on were to begin.

4

i would start by modelling your main entities – staff, rooms, patients, wards, services, etc.

5

I have a question with the above ^ response.

You said start by modeling the main entities.

main entities – staff, rooms, patients, wards, services, etc.

Why would you write in rooms as a main entity?

10 wards with unique names/numbers each has up to 30 rooms. Wouldn’t the rooms come underneath the wards?

I’ve got this task to do too, unfortunately I’m not very experienced in this topic either :P.

6

no, not “underneath” – “within” :cool:

the idea is that wards and rooms are related – and relationships is what an ER model is all about

7

So the “rooms” entity is “within” the “ward” entity? It’s an…N:N type relationship isn’t it? Multiple wards with multiple rooms?

Sorry I’m hijacking this thread almost :(. Didn’t want to make another one for the same question.

8

not really :slight_smile:

rooms are within wards (in the real world), but entities are not “within” entitites

what you can say is that the rooms entity is related to the wards entity, and the nature of the relationship is “within”-ness

and i don’t think it’s many-to-many, because a single room is likely not to belong to more than one ward

9

Just spent a while working on it, think I’ve worked it out now.

Thanks :).

10

i find it interesting that you two guys both have the same homework assignment

what course is this? is it an online school?

11

No, I am sure we’re doing the same subject though. It’s a first year Uni IT course called Organizational Databases, the problem was we only had like an hour or two of class to explain the E-R concept/modeling before we moved on to other topics.

I spent a few hours yesterday working out a rough draft and checked with my lecturer that I was doing alright though so :tup:

12

yeah, we are in the same course.have got around doing some of the diagram.will post to see if im on the right track.

13

We don’t have to do aggregations, by the way.

I have a question about the Admin relation. Do we just leave it as “Admin” or something related to Hospital Staff? I’ve got “Admin” -> “Administrative staff”, but it looks rather stupid having it there.