SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    E-R Diagram Hospital Management

    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    wasn't asking to do the assignment for me.just to give me some ideas on were to begin.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i would start by modelling your main entities -- staff, rooms, patients, wards, services, etc.
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Apr 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    no, not "underneath" -- "within"

    the idea is that wards and rooms are related -- and relationships is what an ER model is all about
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Apr 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by newbie1000 View Post
    So the "rooms" entity is "within" the "ward" entity?
    not really

    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Apr 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just spent a while working on it, think I've worked it out now.

    Thanks .

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i find it interesting that you two guys both have the same homework assignment

    what course is this? is it an online school?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Member
    Join Date
    Apr 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  12. #12
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #13
    SitePoint Member
    Join Date
    Apr 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •