CODEWITHSUNDEEP
sqldatabase
user1852172
user1852172

Reputation: 21

Helping find Database Candidate Keys, Primary Keys

I am trying to work on this model and figure out the following questions about it:

  1. What is (are) the candidate key(s) of PATIENT_VISIT?

  2. If there is more than one candidate key, select a primary key from among the candidate keys.

  3. Based on the primary key chosen, what normal form violations exist in PATIENT_VISIT?

  4. Develop a solution that eliminates the normal form violations.
    Is your solution a lossless-join decomposition?

  5. Is your solution dependency-preserving? If not, how can dependency preservation be achieved? Is this revised solution in BCNF?

  6. Provide a solution that meets all three of the following conditions: (1) is in BCNF, (2) is dependency-preserving, and (3) is a lossless-join decomposition

    +---------+------------+------------+
| Patient |  Hospital  |   Doctor   |
+---------+------------+------------+
| Smith   | Methodist  | D. Cooley  |
| Lee     | St. Luke's | Z. Zhang   |
| Marks   | Methodist  | D. Cooley  |
| Marks   | St. Luke's | W. Lowe    |
| Lou     | Hermann R. | Duke       |
+---------+------------+------------+

In addition, suppose the following semantic rules exist.

Upvotes: 2

Views: 443

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 753675

  1. {Patient, Doctor} is a candidate key. The only other possible key is {Patient, Hospital, Doctor}, but that is not minimal because of the FD Doctor ⟶ Hospital; it is a superkey but not a candidate key.

  2. Moot; there's only one candidate key.

  3. The schema is not in BCNF because of the transitive dependency (FD) Doctor ⟶ Hospital. (Each attribute of the table is functionally determined by the key, the whole key, and nothing but the key.)

  4. PD { Patient, Doctor }, DH { Doctor, Hospital }. This is a lossless decomposition.

  5. This solution is dependency preserving.

  6. See 4.

Upvotes: 1

Related Questions