SIT103 Data and Information Management

The business rules of the MediRAY chain of pharmacies are as following.

1. Each of the patients must have an digital record if they have visited MediRAY chain of pharmacies
2. The doctors who had worked there and currently working must be registered within the system
3. For different pharmacies different registration must be done
4. A doctor will be seeing at least one patient
5. A patient must be visiting one of the physician
6. Several drugs can be prescribed to many patients at the same time
7. Several doctors may provide prescription to the patient and at least one prescription should be prescribed
8. Several pharmaceutical organizations can contract with pharmacies
9. A contract may be linked with one or more supervisor

Extended Entity Relationship:

As the ISA relationship cannot be represented in the real-world database, the common attributes like name, address, contact and experience will be presented through doctor and physician entity. All the many-to-many relationships has been derived into one-to-many and many-to-one relationships.

2^-ND Normal Form: The Second normal form derives that the database has to be in the first normal form and there should not be any partial dependency within the database. As the EERD in ‘figure 1’ describes, partial dependency within the database.

3^-RD Normal Form: The third normal form derives that the database has to be in the second normal form and there should not be any transitive dependency within the database. As the EERD in ‘figure 1’ describes, transitive dependency within the database.

Therefore it can be safely derived that the database is normalized up to at least third normal form.

Queries:

Query 1: Select DoctorName, DoctorSpecialization

From Doctor INNER JOIN PatientDoctor ON Doctor.DoctorRegistrationNumber = PatientDoctor.DoctorRegistrationNumber

INNER JOIN Patient ON PatientDoctor.PatientNumber = Patient.PatientNumber

WHERE PatientName=“Name of the Patient”

Description: This query will provide names of the doctors who are seeing a specific patient, if patient name collides then other attributes like age and/or address will be used to differentiate.

Query 2: Select PatientName, PhysicianName

From Physician INNER JOIN

Patient ON Physician.PhysicianRegistrationNumber= Patient.PhysicianRegistrationNumber

ORDER BY PatientName

Description: This query will provide names of the patients and associated physicians. The list will be ordered by the patient name.

Query 3: Select DoctorName, DoctorSpecialization, PrescriptionID, PatientName

From Physician INNER JOIN

PatientDoctor ON PatientDoctor ON Doctor.DoctorRegistrationNumber = PatientDoctor.DoctorRegistrationNumber

INNER JOIN Patient ON PatientDoctor.PatientNumber = Patient.PatientNumber

INNER JOIN Patient ON Patient.PatientNumber = Prescription.PatientNumber

ORDER BY DoctorName

Description: This query will provide names of the patients and doctor along with the presrtiption they have prescribed to the patient.

Query 4: Select DrugName, PharmaName

From Drug INNER JOIN

PharmaDrug ON Drug.DrugID = PharmaDrug.DrugID

INNER JOIN PharmaceuticalCompany ON PharmaDrug.PharmaID = PharmaceuticalCompany.PharmaID

ORDER BY DrugName

Description: This query will provide names of the drugs and Pharmaceutical Company that sells that drug.

Query 5: Select SupervisorName, ContractID

From Supervisor INNER JOIN

SupervisorContract ON Supervisor.SupervisorID = SupervisorContract.SupervisorID

INNER JOIN Contracts ON SupervisorContract.ContractID = Contracts.ContractID

Description: This query will provide names of the Supervisor and the ContractID that for which they were hired.

Bibliography:

Bugiotti, F., Cabibbo, L., Atzeni, P. and Torlone, R., 2014, October. Database design for NoSQL systems. In International Conference on Conceptual Modeling (pp. 223-231). Springer, Cham.

Chatr-Aryamontri, A., Breitkreutz, B.J., Oughtred, R., Boucher, L., Heinicke, S., Chen, D., Stark, C., Breitkreutz, A., Kolas, N., O'donnell, L. and Reguly, T., 2014. The BioGRID interaction database: 2015 update. Nucleic acids research, 43(D1), pp.D470-D478.

Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management. Cengage Learning.

Ferreira, B., Faria, L., Ramalho, J.C. and Ferreira, M., 2016, October. Database Preservation Toolkit: A relational database conversion and normalization tool. In iPRES: 13th International Conference on Digital Preservation.

Hernandez, M.J., 2013. Database design for mere mortals: a hands-on guide to relational database design. Pearson Education.

Ryberg, M., Vieira, M.D., Zgola, M., Bare, J. and Rosenbaum, R.K., 2014. Updated US and Canadian normalization factors for TRACI 2.1. Clean Technologies and Environmental Policy, 16(2), pp.329-339.

Zhu, X., Lei, Z., Yan, J., Yi, D. and Li, S.Z., 2015. High-fidelity pose and expression normalization for face recognition in the wild. In Proceedings of the IEEE Conference on Computer Vision and Pattern Recognition (pp. 787-796).