New! Hire Essay Assignment Writer Online and Get Flat 20% Discount!!Order Now

SIT103 Data and Information Management

Published : 23-Oct,2021  |  Views : 10

Question:

Identify business rules Design the database using Entity Relationship Diagram. Clearly indicate entities, attributes, relationships, cardinalities, primary keys and other special types of attributes relationships (e.g. multivalued attributes/ ISA relationships, etc).Develop relational schemas.  Identify primary and foreign keys and clearly indicate them in the relational schema Perform Normalization (up to 3rd Normal form).Justify the choices you have made What assumptions have you made.Write 5 queries with where clause that would think used by the company. You are required to write what the functionality of each query. You will not be given marks, If 
you failed to explain what each query does.

Answer:

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

Table: PharmaceuticalCompany

Attribute

Type

PharmaID

Varchar2

PharmaName

Varchar2

PharmaAddress

Varchar2

PharmaMail

Varchar2

PharmaPhNumber

Varchar2

 

Table: PharmaDrug

Attribute

Type

DrugID

Varchar2

PharmaID

Varchar2

DrugCost

Varchar2

 

Table: Supervisor

Attribute

Type

SupervisorID

Varchar2

SupervisorName

Varchar2

SupervisorAddress

Varchar2

SupervisorMail

Varchar2

SupervisorPhNumber

Varchar2

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.

Table: PharmaceuticalCompany

Attribute

Type

PharmaID

Varchar2

PharmaName

Varchar2

PharmaAddress

Varchar2

PharmaMail

Varchar2

PharmaPhNumber

Varchar2

 

Table: Drug

Attribute

Type

DrugID

Varchar2

DrugName

Varchar2

DrugFormula

Varchar2

TradeName

Varchar2

DrugCost

Varchar2

 

Table: PharmaDrug

Attribute

Type

DrugID

Varchar2

PharmaID

Varchar2

DrugCost

Varchar2

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).

Get An Awesome Price Quote For Your Paper – Absolutely FREE!
    Add File
    Files Missing!

    Please upload all relevant files for quick & complete assistance.

    Our Amazing Features

    delivery

    No missing deadline risk

    No matter how close the deadline is, you will find quick solutions for your urgent assignments.

    work

    100% Plagiarism-free content

    All assessments are written by experts based on research and credible sources. It also quality-approved by editors and proofreaders.

    time

    500+ subject matter experts

    Our team consists of writers and PhD scholars with profound knowledge in their subject of study and deliver A+ quality solution.

    subject

    Covers all subjects

    We offer academic help services for a wide array of subjects.

    price

    Pocket-friendly rate

    We care about our students and guarantee the best price in the market to help them avail top academic services that fit any budget.

    Getting started with MyEssayAssignmentHelp is FREE

    15,000+ happy customers and counting!

    Rated 4.7/5 based on
    1491 reviews
    ;