0

I need to create an ER diagram using oracle SQL developer, I have created it, but I am struggling to add the constraints, does anyone have any advice on how to do this?

I am developing a hospital data model. Thanks

**enter image description here**

enter image description here

enter image description here

Improve this question
10
  • are you looking for PK/FK or any type? or auto generate based on existing structures? Commented Dec 5, 2018 at 15:52
  • I'm new to SQL and developing models, but I have set my pk, and I'm pretty sure they're correct, I'm a bit confused with fk and also any other kind of constraints I can add. Commented Dec 5, 2018 at 15:59
  • Primary key/Foreign Key; in other words what value (or values) uniquely defines a record in a table for PK and when linking that value to another table the PK becomes a FK to the secondary table. So think of a Invoice with lines on it. Invoice # ties to Invoice Detail using Invoice # but Invoice Detail also has a Line # to make it unique in that table. So while Invoice# and LIne# are PK in Invoice Detail, Invoice # is PK in Invoice Table it's also a FK in Invoice Detail. Commented Dec 5, 2018 at 16:33
  • Thank you for that, that makes it clearer! I have added an image of my diagram, does this seem right to you? Commented Dec 5, 2018 at 16:50
  • So is a patient record a "visit' thus you would be duplicating patient name dob gender each time or do you have patients independent of the visits? To me a patient could have multiple visits to a dr with different complaints/ailments each time and it could be more than 1 per visit. So there should be a table between patient visits and complaints so if multiple exists it's handled. For each Table/Entity you need to ask how does this relate to the other entities. If it could ever be a Many-to-Many then you need an associative table. I'm not sure how you came up with the table structure./ Commented Dec 5, 2018 at 17:09

1 Answer 1

Reset to default
1

So reconsider your entities Think of it in terms of What "physical objects do you have and what can those objects do"

Start off by identifying them all and then combining like ones.

For example staff, dr, GP and patient are all "People" and share like information just There are just different "Types" of people so combine them!

Each ward has staff on it so you have People, Ward, and WardStaff Each person could have address information. A Person can be admitted to a ward A persons who has been admitted can be treated by multiple drs and have multiple ailments

Remember if you have to update information in more than one place to keep it accurate it needs to be consolidated.

Consider Cardinality between the entities: Does a ward HAVE to have nurses assigned? is a Nurce always on a ward? Can a ward have zero, one or many nurses? Can a patient have zero one or many ailments? Can a dr have zero one or many patients? Are dr's limited to wards? Do Dr's have specialties too? (can they have more than one?)

Here's the entities I see after that read:

  • Ward
  • WardType
  • WardStaff
  • Admittance
  • Person
  • PersonType
  • Address
  • PatentAdmittanceAilments
  • AilmentType
  • TreatmentType

And then here's how I see them relate. Re-read the 4 pages and see if this looks right. ask what's wrong and ask what's missing. and is there too much?

Wards

  • WardID (int) PK
  • Name (varchar(10))
  • WardTypeID (int)

WardStaff

  • WardID (int) PK
  • StaffID (Varchar(6)) PK (Unique Constraint) as a nurse can only work in 1 ward
  • LeadEffective Date Shows when the nurse became lead of ward
  • LeadNurseID (varchar(6)) FK to PersonID

Admittance

  • AdmittanceID (int) PK
  • PatientID (VARCHAR(6))
  • WardID (Int) FK to Ward
  • AdmittanceDate (date)
  • DischargeDate (date)

Person

  • PersonID (varchar(6)) PK
  • PersonTypeID (Integer)
  • Name (varchar(30))
  • DOB Date (Date)
  • GPID (6,0)
  • AddressID (int) FK to Address

Address

  • AddressID (Int) PK
  • Address# (varchar(10))
  • BuildingName (varchar(30))
  • Unit# (varchar(10))
  • City (Varchar(50))
  • Street (varchar(50))
  • State (varchar(02))
  • Country (varchar(10))
  • ZipCode (varchar(10))

PatientAdmittanceAilments

  • AdmittanceID (Int) PK_1of3
  • DrID (varchar(06)) FK to Person
  • AilmentID (int) FK to Ailment PK2of3 A trigger needs ot be created to
  • ensure a compliantID
  • AilmentAdditional(varchar(40)) doesn't overlap the DTS/DTE
  • TreatmentID (int) FK to Treatment
  • TreatmentAdditional(varchar(40))
  • DTS (Date) PK3of3
  • DTE (Date)

PersonType

  • PersonTypeID (Int) PK
  • Description (Varchar(30)) (Examples: Staff, Patient, Dr, GP)

WardType

  • WardTypeID (int) PK
  • Description (varchar(20)) (Examples: Orthopaedic, geriatric...)

AilmentType

  • AilmentTypeID (int) PK
  • Description (varchar(40))

Treatments

  • TreatmentTypeID (int) PK
  • Description (varchar(40))
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thank you so much for going through this! I'm just going through it now
Can I just ask how do I highlight constraints for this?
The lines between the documents are the PK/FK constraints... the broken line vs solid lines define optional vs mandatiory relationships and the crows feet denote 1 to many. So they are "hilighted" in that aspect.. I'm not sure what you mean beyond that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.