Ghulam Ishaq Khan Institute of
Engineering Sciences and
Technology (GIKI)
Open Ended Lab - Spring 2025
Course: Database Management System Course Code: CS-232L
Class: BSAI Exam Date: 05/05/2025 – Monday (02:30 PM)
Time Allowed: 90 Mins Total Marks: 25
Instructor: Ms. Umm-e-Rubab Hashmi Course Instructor: Muhammad Qasim Riaz
Student Name: Reg. No.:
Note (Read instructions first) .
First, read the instructions and statements of each question carefully then write the queries.
o Ensure all queries are written in PostgreSQL syntax.
o Name of files should contain your roll number, and question number.
o Put all the files in a folder named with your reg number and compress the folder.
CHEATING/COPY CASE will be graded as STRAIGHT ZERO MARKS .
Introduction:
You have recently been appointed as a Database Administrator at City General Hospital, a large medical facility
looking to modernize its patient management system. The hospital currently manages thousands of patient
records, doctor schedules, and appointments using outdated methods.
Your task is to develop and implement a database solution that will streamline the hospital's operations,
improve appointment scheduling efficiency, and enhance data analysis capabilities for better patient care. The
hospital leadership is particularly interested in being able to track appointment patterns, analyze patient
demographics, and optimize doctor scheduling.
As part of your role, you will need to create the database structure, implement business logic through stored
procedures and functions, establish data integrity mechanisms with triggers, and design efficient query patterns
using appropriate indexing strategies.
Complete the following tasks to demonstrate your database management skills and provide City General
Hospital with an effective solution to their data challenges.
Question: 1 Marks: 10 (CLO-1, PLO-3)
Create the new database and the following tables also insert values: (6)
Page # 1
Perform the following queries on the above tables:
1. Write a query using date/time functions to calculate the age of each patient in years and months as of the
current date. (2)
2. Create a PL/pgSQL block using IF-ELSE statements to categorize doctors based on their years of
experience:
- Less than 10 years: "Junior Specialist"
- 10-15 years: "Senior Specialist"
- More than 15 years: "Expert Specialist"
Display the doctor names and their categories. (2)
Question: 2 Marks: 5 (CLO-2, PLO-3)
Perform the following queries using FUNCTIONS, PROCEDURES AND CURSORS: (2.5 + 2.5)
1. Create a function named `get_patient_appointments` that takes a patient's ID as a parameter and returns a
table containing all appointments for that patient including appointment date, time, doctor's name, and status.
2. Create a stored procedure named `schedule_appointment` that takes patient_id, doctor_id,
appointment_date, appointment_time, and reason_for_visit as parameters. The procedure should:
- Check if the doctor is available at the specified time (no overlapping appointments)
- If available, insert the new appointment with status 'Scheduled'
- If not available, return an appropriate message
- Include proper error handling
Question: 3 Marks: 10 (CLO-2, PLO-3)
Perform the following queries using TRIGGERS, INDEXES, VIEWS:
1. Create a view named `upcoming_appointments` that displays all scheduled appointments for the next 30
days including patient name, doctor name, appointment date, time, and reason for visit.
(3)
Page # 2
2. Create a trigger named `appointment_status_log` that logs any changes to appointment status in a new table
named `appointment_status_history`. The log should record the appointment_id, old status, new status, and
timestamp of change. (4)
3. Create appropriate indexes to optimize the following queries:
- Finding appointments by date range
- Searching patients by name
- Filtering doctors by specialization
Explain the rationale behind each index. (3)
Page # 3
