0

The following table is not normalized: UNF Table

Assuming the following functional dependencies are in place, how would we normalize this table?:

Functional Dependencies

I can't seem to find a way to normalize the table while following all of the functional dependencies as well. I have the following (Modeled in Oracle SQL Developer Data Modeler):

enter image description here

What can I do to fully normalize the original table?

Community
  • 1
  • 1
Zampanò
  • 574
  • 3
  • 11
  • 33
  • 2
    Shouldn’t the surgery be related to the appointment, not the patient? – Nathan Hughes Mar 01 '18 at 20:37
  • What do you mean you can't find a way--What reference are you following? What exactly do you mean by "normalize" and what do you mean by "fully normalize"? What step are you stuck at exactly? "Normalize" is used for 2 main things: putting in "1NF" ([which has no single meaning](https://stackoverflow.com/a/40640962/3404097)) and putting into higher NFs. The former does not involve FDs. The latter involves FDs between columns--but your FDs involve non-columns. Please clarify. – philipxy Mar 01 '18 at 20:38
  • 1
    I was also surprised to see the surgery related to the patient. Apart from this, this looks fine to me. What's your problem with your solution? – Thorsten Kettner Mar 01 '18 at 20:39
  • Please [use text, not images/links, for text (including code, tables & ERDs)](https://meta.stackoverflow.com/q/285551/3404097). Use an image only for convenience to supplement text and/or for what cannot be given in text. Please read & act on hits googling 'stackexchange homework'. Reference a process & show your work. – philipxy Mar 01 '18 at 20:41
  • I find it helps visually to lay out diagrams with parents above children, where possible. I would therefore have Staff and Patients above Appointments. As you don't have any information about Surgeries beyond the room number (nothing has a functional dependency on surgery number) there doesn't seem any point having a separate table for it. – William Robertson Mar 03 '18 at 10:36

1 Answers1

1

So Wikipedia’s entry for functional dependency includes this explanation:

a dependency FD: X → Y means that the values of Y are determined by the values of X. Two tuples sharing the same values of X will necessarily have the same values of Y.

So FD1 says, if you know the appointment date time and the staffer, you can determine the individual patient, and likewise for FD5 if you know the appointment dateline and the patient you can determine the staffer.

FD2 is pretty obvious, a staffer Id needs to map to an individual dentist. That is why you have ids.

Then it gets weird. FD3 indicates that from a patient number you can determine a single procedure. So if you’re required to abide by that, the surgery can go on the patient entity. Which is stupid, of course.

FD4 is puzzling too because it says that a staffer can perform only type of procedure in a given day. When you create data models in real life this is the kind of business rule you would not try to enforce through table design, you’d use a constraint, or enforce it with application code. If you did enforce this with tables you would get a weird intersection table with staffer Id, date, and procedure.

Assignments are not going to be totally realistic, but this seems far enough off you should check with your instructor about whether you are on the right track or not.

.

Nathan Hughes
  • 94,330
  • 19
  • 181
  • 276