Scribd Logo
Upload

Welcome to Scribd!

  • 13 views

    DB - Mid 35-1

    Uploaded by

    rohailabacy01

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    DB - Mid 35-1

    Uploaded by

    rohailabacy01
    13 views3 pages

    Original Title

    DB_MID 35-1

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    13 views3 pages

    DB - Mid 35-1

    Uploaded by

    rohailabacy01

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    You are on page 1of 3

    SUBMITTED BY: AMBER KHALID

    SUBMITTED TO: DR. ADEEL ANJUM


    SEMESTER: 4TH EVENING
    REGISTER #: 04162114035
    DATA BASE STRUCTURE MID 2023

    QUESTION#1:
    Question 02:
    Create a relational model based on the ERD you developed in part a.
    Worker

    Id (primary key)
    First_name
    Last_name
    Middle_name
    Phone number
    Adrdess

    Nurses Doctors Receptionists

    Nurse_ID (primary key) D_ID (primary key) RECP_ID (primary key)


    Shift_ID SHIFT_ID SHIFT_ID

    Shift
    Shift_ID
    Start time
    End time
    Bed

    Nurse_id(foreign key referring


    nurse)
    Shift_ID(foreign key referring key)
    Bed_id(primary key)

    Prescription
    Patient
    Prescription_ID(primary key)
    P_ID (Primary key) Case doctor_ID (foreign key referencing case
    doctor
    Shift_ID
    Patient_ID(foreign key referencing patient)
    Question 03:

    Write SQL queries and their corresponding relational algebra statements for the following.
    .a. Display the list of workers working in first shift Monday.
    SELECT *
    FROM Worker
    WHERE shift_id ={
    SELECT shift_id
    FROM shift
    WHERE shift_day= ‘Monday’ AND shift_time= ‘first’
    };

    RELATIONAL ALGEBRA:
    π p. first_name, p. last_name, w.worker_type ( Person ⨝ Worker ⨝ WorkerShift ⨝ Shift ( σ s.start_time = 'Monday' AND s.shift_id = 1
    (Shift) ) )

    2) Display the prescriptions for a particular patient admitted to the emergency.


    SQL QUERY:

    SELECT pr.medication_name, pr.dosage, pr.frequency_per_day


    FROM Prescription pr
    JOIN Patient p ON pr.patient_id = p.patient_id
    WHERE p.patient_id = <patient_id>;
    Relational Algebra:
    π pr.medication_name, pr.dosage, pr.frequency_per_day ( Prescription ⨝ σ p.patient_id = <patient_id> (Patient) )

    You might also like