0

I have an EER diagram of the hospital database. I am currently working on the normalization process. As I understand the partial dependency is when an attribute is dependent on only the part of the composite primary key. And this should be removed.

I've applied the rules on my database but still want to make sure that these tables doesn't include partial dependency.

Table1: In examinationo table the composite key consists of inpatient_no, doct_no and lab_no. In my opinion diagnosis and conducted_test attributes depend on all three of them. Is this correct?

Table2: This table has composite key of inpatient_no, doct_no and surgery_no. The attributes date and time convey information when the inpatient will undergo the surgery. Is this a partial dependncy?

I'm very new to databases, so my quesitions can be quite easy.

philipxy
  • 14,867
  • 6
  • 39
  • 83
iaaninita
  • 27
  • 1
  • 7
  • What entities do `surgery_surg_no` and `lab_no` refer to? – O. Jones Jan 03 '21 at 14:59
  • 2
    `In my opinion diagnosis and conducted_test attributes depend on all three of them. Is this correct?` I'm sorry, but this is not a programming question, you need to understand your business and your data to answer this question. – Shadow Jan 03 '21 at 15:37
  • Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. – philipxy Jan 03 '21 at 17:53
  • Re "is this right": Show the steps of your work following your reference/textbook, with justification--not all terms/notations are standard & we don't know exactly what algorithm/method you are following & we want to check your work but not redo it & we need your choices when an algorithm allows them & otherwise we can't tell you where you went right or wrong & we don't want to rewrite your textbook. Please see [ask], hits googling 'stackexchange homework' & the voting arrow mouseover texts. – philipxy Jan 03 '21 at 17:56
  • PS You don't give sufficient information to determine NFs or to normalize. Even though you use the term "dependent on". PS "partial dependency is when an attribute is dependent on only the part of the composite primary key"--Wrong in numerous ways. [Partial Dependency(Databases)](https://stackoverflow.com/a/25827210/3404097) CKs matter to relational theory, not PKs; CKs matter to 2NF, but not to FD partiality; composite or simple matters to FD partiality, not composite. PS "As I understand it" is very often a euphemism for "I don't understand it but maybe". If so, one should inform oneself. – philipxy Jan 03 '21 at 18:02

1 Answers1

1

It looks to me like your table called inpatient_undergoes_surgery represents an entity, in this case an event. The table has one row for each surgical appointment. It has date and time as attributes. It also has one-to-one relationships to an inpatient, doctor, and surgery.

This table appears to be normalized to me. The others might not be. In particular, it's possible that your surgery table duplicates the information in this table.

Pro tip It's best to use a DATETIME or TIMESTAMP data type to represent the kind of date and time in your table. There's no need to use separate columns for date and time. Understanding that the date and time taken together are actually a single attribute of your entity helps clarify your design process.

O. Jones
  • 103,626
  • 17
  • 118
  • 172