$2$a$.$ State the functional dependencies. $($Assume that different patients can have the

same zip code $-$ PatZip$)$

$2$b$.$ Transform this table into one or more tables in BCNF$.$ State the primary keys,

candidate keys, foreign keys, and referential integrity constraints.

For the following Customer and Order tables, $(3$a$)$ first describe the problem with each

table, then $(3$b$)$ explain the fixes needed to comply with BCNF$.$

Given the following sheet $($APPOINTMENT$)$

Suppose that the data of the above sheet shows all possible functional dependencies. Note

surgeryNo is the code of treatment$/$procedure a patient receives for the appointment of visit. The

co$-$pay for each visit$/$procedure received depends on the patients and their primary insurance

policy.

$(4$a$)$ List a set of functional dependencies based on your analysis on the information in the above

table.

$(4$b$)$ Based on your answer to $4$a$,$ define the key of the table

APPOINTMENT $($StaffNo$,$ DentistName, patientNo, patientName, AppointmentDate$/$time$,$

SurgeryNo, PatientInsurance, Co$-$pay$)$

$(4$c$)$ What normal form above table is in and WHY? $($Note you need to correctly answer questions

$4$ a and $4$ b in order to answer this question correctly$)$

$(4$d$)$ What modification anomalies this table suffers $($be as specific as possible, refer to the

textbook's examples$)$

$(4$e$)$ Normalize $($in BCNF$)$ table APPOINTMENT based on FDs you listed. Show the result of

normalization. Clearly indicate keys $($underline$)$ and foreign keys $($italicize$,$ if there is any$).$