4. Normalization (20 pts) Patient! Patient Visit Doctor | Doctor | Checkin | Che

Question

4. Normalization (20 pts) Patient! Patient Visit Doctor | Doctor | Checkin | Checkout! TF Treatment Unit Price Date ID 100156-ro-8290-0927 Name Number Name Lee 10/1/2016 10/1/2016 101 X-Ray 02 Aspirin 103 Shot 80 30 30 10 10 83513 0908 Blake 10/22016 10/5/2016 103 Shot 204 Hypnosis1 312 Water 100845 Betty 81563 0908 800 Bae 10/3/2016 1014/2016 03Shot 312 Water 0024 Smth 1082016 1082018 101 xRay 100 5 022 Tylenol1005 The General Hospital has many patients. Each patient has a Patient ID and a Patient Name. No two patients have the same patient ID, but patients may have the same name. Patient may visit the hospital many times. Each time he/she visits, there is Visit Number. For each visit, the patient is assigned a doctor, who has a Doctor ID and Doctor Name. The doctors were assigned on an availability basis, so a patient may be assigned to a different doctor each time. Each visit has a Checkin Date and Checkout Date. A patient may have multiple visits on the same day. In each visit, the patient may receive multiple treatments. Each treatement has a Treatment ID and Treatment Description. For instance, on Andy's 82903 visit, he received I unit of X-Ray, 80 units of Aspirin, and 3 shots. Each treatment always has the same unit price. For instance, X-Ray Test is always $200 per unit. Patient Patient Viit Doctor Doctor Checkin CneckoutTr Treatment 1. Identify a candidate key of this table. 2. Identify all functional dependencies in the format of Note 1. Since it is very difficult to read drawings, I will only consider your written dependencies for your grade. 2. I will also look at dependencies that do NOT exist. Penalties will apply to excessive dependencies that do NOT exist. 3. You do NOT need to normalize. page 6 of 7

Explanation / Answer

1.       Candidate key uniquely identifies the database. It can be a column or a set of columns. Here in the given database, Candidate key is {Patient ID, Tr ID}

The primary key is Patient ID.

2.       Functional dependencies are:

Patient ID -> Patient Name

Doctor ID -> Doctor Name

Tr ID -> {Treatment Description, Qty}

Visit Number -> {CheckIn date, CheckOut date}

Treatment Description -> Unit Price

Related Questions

Navigate

• Browse (All)
• Browse 4
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.