COIT20247 Database Design and Development-Normalization Process
- Map the ERD, from the sample solution, into a set of relations in at least Third Normal Form (3NF). You must ensurethat your relations meet 3NF. There is no need to show your
- Select any two (2) of your relations from the previous step, 1a), and perform the following for each ofthose two relations:
- List all the functional dependencies exist in the
- Demonstrate that the relation meets Third Normal Form (3NF).
For your help, an example for a Student relation’s 3NF justification has been provided below:
In implementing the ER model provided, you must complete the following tasks:
- Create all the relations in a Microsoft Access database. Consider each attribute in every tableand make appropriate choices regarding data types & sizes, indexes, required/not requiredandvalidation rules. Your choices should be appropriate for each attribute and should support data integrity. (Note: see the Data Integrity section below for specific data integrity requirements)
- Create relationships as appropriate. Enforce referential integrity for all appropriaterelationships in the database. Apply cascade update and/or deleteoptions wherever necessary.
- Review the default index created by Access for each table. You should ensure that theindexes follow the guidelines given in the unit
- Populate the database with sample data of your own. You must include sufficient sample data to testyour queries and report. Please note that the expected result of query questions may depend on the actual sample data
Answer:
- 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)
- Normalizing two set of relations
- 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.
- 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:
- SELECT * FROM SPECIALIST WHERE SpecialistID NOT IN (SELECT SpecialistID FROM APPOINTMENT) ;
- 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];
- SELECT SPECIALIST.SpecialistName, Count( APPOINTMENT.SpecialistID) AS CompletedAppointments
FROM SPECIALIST INNER JOIN APPOINTMENT ON SPECIALIST.[SpecialistID] = APPOINTMENT.[SpecialistID]
GROUP BY SPECIALIST.SpecialistName;
- 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];
- 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];
- SELECT * FROM PATIENT WHERE PatientName LIKE '*more*';
Buy COIT20247 Database Design and Development-Normalization Process Answers Online
Talk to our expert to get the help with COIT20247 Database Design and Development-Normalization Process Answers to complete your assessment on time and boost your grades now
The main aim/motive of the management assignment help services is to get connect with a greater number of students, and effectively help, and support them in getting completing their assignments the students also get find this a wonderful opportunity where they could effectively learn more about their topics, as the experts also have the best team members with them in which all the members effectively support each other to get complete their diploma assignments. They complete the assessments of the students in an appropriate manner and deliver them back to the students before the due date of the assignment so that the students could timely submit this, and can score higher marks. The experts of the assignment help services at urgenthomework.com are so much skilled, capable, talented, and experienced in their field of programming homework help writing assignments, so, for this, they can effectively write the best economics assignment help services.
