0

I have newly learned about ER diagrams and how powerful they are.

I am trying to make a very basic ER diagram for hospital and there are few questions and doubts in my mind.

This is the ER diagram that I've made:

er diagram of hospital

I want tests to be stored in hospital database as well as in the patient's report file(all the tests of a specific patient should be stored in his/her report file).

The ternary relationship test should signify that:

  • A patient and a doctor maintains only one report
  • A doctor makes a report for specific patient
  • A patient and his report can have tests from N no of doctors.

Q1- But I am confused how to achive this using ternary relation?

Q2- What is the difference between 3 binary relationship and 1 ternary relationship(and also which of them carrys more information)?

Q3- Can a relationship can have it's own prime(key) attribute?

Q4- If we have concept of surrogate key then why does weak entity exists in first place?

Q5- How to conclude which entity should be made weak and whichshould be not?

Q6- Any good source to learn EER ,advanced topics of database designing(relational algebra,etc..) and about ternary relationships(I have searched youtube and google but none of them was satisfactory)?

Improve this question
3
  • 1
    I'm not sure how powerful Chen diagrams (the type of diagram you provided) are in the real world, I almost never see them outside of someone's CS homework. This question is also overly broad and you may want to revise.
    – user212533
    Commented Jul 24, 2020 at 18:53
  • There is a reason why you have seldom seen Chen diagrams, except in CS homework. This kind of diagram provides a picture of the Subject Matter data, and not a picture of the database design. There is a lot of overlap, but there is a lot of difference as well. It's the difference between analysis and design. There are many professionals who have studied a lot about design, but almost nothing about analysis. This is unfortunate.
    – Walter Mitty
    Commented Jul 25, 2020 at 18:05
  • Ask 1 specific researched non-duplicate question where 1st stuck following a published information modeling & DB design method.
    – philipxy
    Commented Jul 29, 2020 at 5:59

1 Answer 1

Reset to default
1

Q1: Why do you need a ternary relationship? Test is a relationship between a patient and a doctor. What is the third entity you need to specify in order to pin down a specific instance of a test. I assume that a doctor can order more than one test for a given patient.

Q2: They carry different information. One of them is appropriate in any given situation, and the other does not carry the needed information.

Q3: In the Chen ER model, relationships are generally not given a primary key attribute. Instances of the relationship may be identified by identifying each of the participants in the instance.

Q4: There is more than one way to skin a cat.

Q5: Make an entity weak if it can have no existence outside the context of the related strong entity. Invoice detail line is a weak entity. There will never be an invoice detail line without a corresponding invoice.

Q6: There are hundreds of good textbooks to learn more about database design. The ones by CJ Date are classics. There a re a few good sources on EER, but most good sources on ER will incorporate EER concepts. Here is a link to ternary relationships, but it repeats stuff you already know. https://www.tutorialspoint.com/N-ary-Relationship-in-Database#:~:text=N-ary%20Relationship%20in%20Database%201%20Unary%20Relationship.%20When,a%20binary%20relationship.%20...%203%20Ternary%20Relationship.%20 the primary value to you is the terminology of N-ary relationships. This should help you google.

You didn't ask this, but you need to know it anyway. There is a difference between analyzing the subject matter (hospitals), and designing a database to hold the relevant data. People who do not understand the difference often end up producing the right solution to the wrong problem.

Improve this answer