How to normalize this UNF table to 3NF given these FDs - staff, patient, date, time, surgery

Question

The following table is not normalized:

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

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):

What can I do to fully normalize the original table?

Answer 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.

.