COIT20247 Database Design And Development

1. Mapping ERD into a set of 3NF relations

PATIENT (PatientId, PatientName, PatientContectNumber, PatientAddress, MedicalHistory)

ALLERGY (AllergyId, patientID, AllergyDescription)

Foreign Key (PatientID) references (PATIENT.PatientID)

PATIENT_TYPE (PtypeNO, PatientId, PatientType)

Foreign Key (PatientID) references (PATIENT.PatientId)

QUALIFICATION (QualificationId, QualificationTitle)

SPECIALIST (SpecialistID, specialistName, ContactNumber, YearSpecialised)

SPECIALIST_QUALIFICATION (SpecQualifyId, SpecialistID, QualificationID)

Foreign Key (SpecialistID) references (SPECIALIST.SpecialistId)

Foreign Key (QualificationId) references (QUALIFICATION.QualificationId)

GP (MedicationProviderNumber, GPName, GPContactNumber)

REFERAL (ReferalId, ReferalDate, Notes, MedicationProviderNumber)

Foreign Key (MedicationProviderNumber) References (GP.MedicationProviderNumber)

APPOINTMENT (AppointmentID, ReferalID, PatientId, specialistID, AppointmentDate, AppointmentTime, AppointmentType,TotalCharges)

Foreign Key (ReferalID) References (REFERAL.ReferalID)

Foreign Key (PatientID) References (PATIENT.PatientId)

Foreign key (specialistID) References (SPECIALIST.SpecialistID)

PAYMENT (PaymentId, AppointmentId, PaymentDate, Amount)

Foreign Key (AppointmentId) references (APPOINTMENT.AppointmentId)

DISCOUNT (DiscountID, AppointmentID, DiscountAmount)

Foreign Key (AppointmentID) references (APPOINTMENT.AppointmentID)

TEST_PROCEDURE (TestID, TestName, Charges)

APPOINTMENT_TEST_PROCEDURE (AppointTestId, AppointmentId, testID)

Foreign Key (AppointId) References (APPOINTMENT.AppointmentId)

Foreign Key (testID) references (TEST_PROCEDURE.TestID)

PRESCRIPTION (PrescriptionNo, AppointmentID, PrescriptionDate)

Foreign Key (AppointmentID) references (APPOINTMENT.AppointmentId)

MEDICINE (MedicineName, Strength, Instructions)

MEDICATION (MedicationID, MedicineName, PrescriptionNo, PatientId, Dosage)

Foreign Key (MedicineName) references (MEDICINE.MedicineName)

Foreign key (PrescriptionNo) references (PRESCRIPTION.PrescriptionNo)

Foreign key (PatientId) references (PATIENT.PatientId)

1. Normalizing two set of relations
2. APPOINTMENT (AppointmentID, ReferalID, PatientId, AppointmentDate, AppointmentTime, AppointmentType,TotalCharges)

Foreign Key (ReferalID) References (REFERAL.ReferalID)

Foreign Key (PatientID) References (PATIENT.PatientId)

Functional dependencies

AppointmentID à ReferalID, PatientId, AppointmentDate, AppointmentTime, AppointmentType,TotalCharges

(AppointmentID, ReferalID) à  PatientId, AppointmentDate, AppointmentTime, AppointmentType,TotalCharges

Normalization proof

AppointmentID is used as the unique identifier of the appointment record. There exist no repeating groups since the referral comes in as foreign key but is only written once for every patient appointment record. The combination of referral and patient ID brings in a new data row record. Appointment time, type and amount cannot be used as key attributes. Therefore our table is in 1NF.

Since our non-key attributes i.e. appointment date, time, type and total charges fully depends on our key Attribute Appointment ID, our table qualifies to be in 2NF for there is no case of partial dependency.

Since there only exist cases of functional dependencies and no transitive dependencies in our table relation, our table qualifies to be in 3NF.

1. PAYMENT (PaymentId, AppointmentId, PaymentDate, Amount)

Foreign Key (AppointmentId) references (APPOINTMENT.AppointmentId)

Functional dependencies:

PaymentId à AppointmentId, PaymentDate, Amount

Normalization proof:

The PatientID is used as a primary key to identify each payment made for the appointment. Even though Appointment can be seen to be a key, it cannot be used as identifier for the payment. This makes the AppointmentID to be repeated many times in our table especially if one appointment had splitted payments. The AppointmentID fails to be a key in this table. Therefore our table qualifies to be in 1NF.

From the table relation, the non-key attributes AppointmentId, PaymentDate and Amount fully depends on the payment id. This eliminates the cases of partial dependencies and therefore our table relation is in 2NF.

Our table has no transitive dependencies since each non-key attribute fully depends on the key attribute. This makes our table qualify to be in 3NF.

Sql queries:

1. SELECT * FROM SPECIALIST WHERE SpecialistID NOT IN (SELECT SpecialistID FROM APPOINTMENT) ;
2. SELECT APPOINTMENT.AppointmentDate, APPOINTMENT.AppointmentTime, APPOINTMENT.AppointmentType, APPOINTMENT.TotalCharges, PATIENT.PatientName, PAYMENT.Amount

FROM (PATIENT INNER JOIN APPOINTMENT ON PATIENT.[PatientId] = APPOINTMENT.[PatientId]) INNER JOIN PAYMENT ON APPOINTMENT.[AppointmentId] = PAYMENT.[AppointmentId];

1. SELECT SPECIALIST.SpecialistName, Count( APPOINTMENT.SpecialistID) AS CompletedAppointments

FROM SPECIALIST INNER JOIN APPOINTMENT ON SPECIALIST.[SpecialistID] = APPOINTMENT.[SpecialistID]

GROUP BY SPECIALIST.SpecialistName;

1. SELECT PATIENT.PatientName, APPOINTMENT.AppointmentDate, APPOINTMENT.AppointmentTime, APPOINTMENT.TotalCharges, DISCOUNT.DiscountAmount, (APPOINTMENT.TotalCharges- DISCOUNT.DiscountAmount) AS AmountPayable

FROM (PATIENT INNER JOIN APPOINTMENT ON PATIENT.[PatientId] = APPOINTMENT.[PatientId]) INNER JOIN DISCOUNT ON APPOINTMENT.[AppointmentId] = DISCOUNT.[AppointmentID];

1. SELECT PATIENT.PatientName, APPOINTMENT.AppointmentDate, APPOINTMENT.AppointmentTime, APPOINTMENT.TotalCharges, DISCOUNT.DiscountAmount, (APPOINTMENT.TotalCharges- DISCOUNT.DiscountAmount) as AmountPayable
2. FROM (PATIENT INNER JOIN APPOINTMENT ON PATIENT.[PatientId] = APPOINTMENT.[PatientId]) INNER JOIN DISCOUNT ON APPOINTMENT.[AppointmentId] = DISCOUNT.[AppointmentID];
3. SELECT * FROM PATIENT WHERE PatientName LIKE '*more*';