Description
case-1 : In a single word document, each group to submit a minimum of 10 queries (show the queries and the results of running each query) to generate the following:
1. Two stored procedures to populate transactional tables
2. Two basic select statements including WHERE clauses
3. One query with ORDER BY command
4. Two queries with aggregate functions
5. One query with Having command
6. Two use of Subqueries
case-2 : Find the names of all patients who had visits on a date. Hint: Use EXISTS command (look for patients who have a record in the visit table).
Data : Entities and Attributes:
Patient
PatientID (PK)
Practitioner
PractitionerID (PK)
PractitionerType
PractitionerName
Administrator
AdminID (PK)
AdminDetails
Receptionist
ReceptionistID (PK)
ReceptionistDetails
Bookkeeper
BookkeeperID (PK)
BookkeeperDetails
Medical Technician
TechnicianID (PK)
TechnicianDetails
Pharmacist
PharmacistID (PK)
PharmacistDetails
Medical Record
RecordID (PK)
PatientID (FK)
Appointment
AppointmentID (PK)
PatientID (FK)
Visit
VisitID (PK)
PatientID (FK)
Prescription
PrescriptionID (PK)
VisitID (FK)
PatientID (FK)
Laboratory Test
LabTestID (PK)
VisitID (FK)
Surgery
SurgeryID (PK)
Delivery
DeliveryID (PK)
Recovery Room
RoomID (PK)
Billing
BillingID (PK)
VisitID (FK)
PatientID (FK)
…
Insurance Company
InsuranceID (PK)
Government
GovernmentID (PK)
Relationships:
Patient-Appointment: One-to-Many
Practitioner-Appointment: One-to-Many
Practitioner-Visit: One-to-Many
Patient-Visit: One-to-Many
Visit-Medical Record: One-to-One
Visit-Prescription: One-to-Many
Visit-Laboratory Test: One-to-Many
Visit-Billing: One-to-One
Patient-Billing: One-to-Many
Prescription-Billing: One-to-One
Pharmacy-Prescription: One-to-Many
Surgery-Practitioner: Many-to-Many
Surgery-Patient: Many-to-One
Delivery-Practitioner: Many-to-One
Delivery-Patient: Many-to-One
Submission Type : Word Document and oracle text file code Data Description:
