Question

Normalizing the Relational Model for the Student Project and
Creating a Normalized SQL Database

Patient (patientNo, name, address, phone, dateOfBirth, sex, insuranceCo, policyNo, relationshipToInsured)

Appointment(patientNo, apptdate, appttime, reason, staffNo, visitNo)

Visit(visitNo, patientNo, visitdate, visittime, duration, reason, visitType, visitCost, staffNo, roomNo)

Staff(staffNo, name, title, specialty, address, phone)

Availability(staffNo, availDate, startTime, endTime)

InsurancePolicy (company, policyNo, insuredName, policytype, medicalCoPay, labCoPay, pharmacyCoPay, startDate, endingDate)

DiagnosisMenu(diagCode, diagName)

ProcedureMenu(procCode, procName, cost)

Room(roomNo, roomType, condition)

PrescriptionScript(scriptNo, visitNo, dateWritten, itemPrescribed, quantityPrescribed, directions, numberRefills)

LabTest(testNo, prescriptionNo, testype, testDate, testTime, cost, result)

Diagnosis(visitNo, diagCode, dateOnset, symptons, severity, prognosis)

Referral(refNo, visitNo, refTo, reason)

Bill(invoiceNo, billDate, totalAmount, dueDate, patientNo, amountPaid)

Charge(invoiceNo, serviceType, serviceDate, amountCharged)

Payment(invoiceNo, date, amountPaid, patientPayer, insuranceCoPayer, insurancePolNoPayer)

Task 1:

Begin with the list of the tables that the entities and relationships from the E-R diagram mapped to naturally. For each table on the list, identify the functional dependencies. List the functional dependencies in a Microsoft Word document.

Task 2:

Normalize the relations to BCNF, placing the normalized relations in your Word document.

Task 3:

Then decide whether the resulting tables should be implemented in that form. If not, explain why in your Word document.

Task 4:

For each table, write the table name and write out the names, data types, and sizes of all the data items, Identify any constraints, using the conventions of the DBMS you will use for implementation. Add this information to your Word document.

Task 5:

Write and execute SQL statements to create all the tables needed to implement the design. Note that you will need to name your database something different then the name you previously used.

Task 6:

Create indexes for foreign keys and any other columns that will be used most often for queries.

Task 7:

Insert about five records in each table, preserving all constraints. Put in enough data to demonstrate how the database will function.

Task 8:

Write SQL statements that will process five non-routine requests for information from the database just created. For each, write the request in English, followed by the corresponding SQL command.

Task 9:

Create at least one trigger and write the code for it.

Answer 1

Functional Dependencies

Patient (patientNo, name, address, phone, dateOfBirth, sex, insuranceCo, policyNo, relationshipToInsured)

Fd:   patientNo -> name,address,phone, dateOfBirth,sex ,insuranceCo, policyNo, relationshipToInsured

Appointment(patientNo, apptdate, appttime, reason, staffNo, visitNo)

Fd : patientNo -> apptdate ,appttime ,reason ,staffNo ,visitNo

Visit(visitNo, patientNo, visitdate, visittime, duration, reason, visitType, visitCost, staffNo, roomNo)

Ed :visitNo-> patientNo,visitdate,visittime, duration, reason, visitType, visitCost, staffNo, roomNo

Staff(staffNo, name, title, specialty, address, phone)

Fd: staffNo-> name, title, specialty, address, phone

Availability(staffNo, availDate, startTime, endTime)

staffNo-> availDate, startTime, endTime

InsurancePolicy (company, policyNo, insuredName, policytype, medicalCoPay, labCoPay, pharmacyCoPay, startDate, endingDate)

policyNo-> company, insuredName, policytype, medicalCoPay, labCoPay, pharmacyCoPay, startDate, endingDate

DiagnosisMenu(diagCode, diagName)

diagCode-> diagName

ProcedureMenu(procCode, procName, cost)

procCode-> procName, cost

Room(roomNo, roomType, condition)

roomNo-> roomType, condition

PrescriptionScript(scriptNo, visitNo, dateWritten, itemPrescribed, quantityPrescribed, directions, numberRefills)

scriptNo-> visitNo, dateWritten, itemPrescribed, quantityPrescribed, directions, numberRefills

LabTest(testNo, prescriptionNo, testype, testDate, testTime, cost, result)

testNo-> prescriptionNo, testype, testDate, testTime, cost, result

Diagnosis(visitNo, diagCode, dateOnset, symptons, severity, prognosis)

diagCode-> visitNo, dateOnset, symptons, severity, prognosis

Referral(refNo, visitNo, refTo, reason)

refNo->visitNo, refTo, reason

Bill(invoiceNo, billDate, totalAmount, dueDate, patientNo, amountPaid)

invoiceNo->billDate, totalAmount, dueDate, patientNo, amountPaid

Charge(invoiceNo, serviceType, serviceDate, amountCharged)

invoiceNo->serviceType, serviceDate, amountCharged

Payment(invoiceNo, date, amountPaid, patientPayer, insuranceCoPayer, insurancePolNoPayer)

invoiceNo-> date, amountPaid, patientPayer, insuranceCoPayer, insurancePolNoPayer