Question #$1$

has $4$ sub$-$questions.

The following relational schema $($relational model$)$ describes a daily process at Irvine Medical

Center.

For each of the relations:

$($i$)$ Identify whether each relation needs to be normalized or not $($answer either Yes or No$)$

$($ii$)$ State the reason why it needs to be normalized.

Example answer: This relation has two partial dependencies.

PD $1$: write PD$1$ here.

PD $2$: write PD$2$ here.

$($iii$)$ Convert the relation into the $3$NF if necessary. When stating the reason for the

relation's highest normal form, you should be able to provide non$-$full functional

dependencies that cause the relation to be in that normal form $($this only applies when a relation

is in the $1$ NF or the $2$ NF $).$

$(1)$ PHYSICIAN$($physID$,$ physName, physOfficeNum, physLicenseNum, physSpecialty$)$

$(2)$ TREATEMENT$($equipmentID$,$ procedureID, treatmentCost, equipmentName,

equipmentDescription, procedureName, procedureDescription$)$

$(3)$CUSTOMER$($custEmail$,$

custName,

custPhoneNum,

custInsuranceNum,

custInsuranceLevel$)$

$(4)$ VISIT$($physID$,$ custEmail, equipmentID, procedureID, startTime, endTime, treatmentCost$)$

Functional Dependencies $($are provided as below for each question $(1)-(4).$

Refer to the given FDs when attempting to solve each question.

$(1)$ physID $$ physName, physOfficeNum, physLicenseNum, physSpecialty

phyLicenseNum $$ physSpecialty

$(2)($equipmentID$,$ procedureID$)$ treatmentCost

equipmentID $$ equipmentName, equipmentDescription

procedureID $$ procedureName, procedureDescription

$(3)$ custEmail $$ custName, custPhoneNum, custInsuranceNum, custInsuranceLevel

$(4)($physID$,$ custEmail, equipmentID, procedureID$)$ start Time, endTime

$($equipmentID$,$ procedureID$)$ treatmentCost