Database System BS 2 Semester/Term UOS — University of Sargodha 2026

University of Sargodha

AD/BS 2nd Semester Examination 2026

Paper: Database Systems (CMPC-5203)

Time Allowed: 2:30 Hours

Maximum Marks: 40

Note: Objective Part is compulsory. Attempt any three Questions from subjective part.

Objective Part (Compulsory) (2*8)

Q.1. Write short answers of the following in 2-3 lines each on your answer sheet.

i. What is the purpose of a "lock" in concurrency control?

ii. What is a NoSQL database, and how does it differ from a relational database?

iii. What is a projection in relational databases?

iv. How does the Cartesian Product operation work in relational algebra? Provide an example.

v. What is an index in a database, and why it is used?

vi. What is the union operation in relational algebra? Provide an example.

vii. In relational databases, what is the purpose of foreign keys

viii. What is referential integrity in the context of relational databases

Subjective Part (3*8)

Q.2. Compare and contrast File-Based Systems and Database Management Systems (DBMS). Explain the limitations of file-based

systems that led to the development of database systems.

Q.3. Define backup and recovery in the context of database systems and explain why they are essential. Describe different types of

backups Explain recovery techniques Differentiate between system crash recovery and transaction failure recovery.

Q.4. Design an Entity-Relationship Diagram (ERD) for a Hospital Management System based on the following scenario.

• A hospital needs to maintain information about doctors, patients, and the appointments scheduled between them.

• Each doctor has a unique DoctorID, Name, Specialization, and PhoneNumber.

• Each patient has a unique PatientID, Name, Gender, DateOfBirth, and PhoneNumber.

• A doctor can have many appointments with different patients, and a patient can have appointments with multiple doctors.

• Each appointment has a unique AppointmentID, Date, the Time, and the RoomNumber.

• The hospital also stores prescriptions associated with each appointment. A prescription includes MedicineName, Dosage, and

Duration.

Your task:

1) Identify the entities in the system

2) Define relationships between the entities

3) Define the cardinality of each relationship

4) Identify attributes for each entity and relationship.

5) Draw an ERD to represent the system. Make sure to include primary keys, foreign keys, and the relationships between the

entities.

Q.5.

StudentID StudentName CourseID CourseName InstructorID InstructorName InstructorPhone Credits

101 Alice CS101 Data Science 101 John Smith 555-1234 3

102 Bob CS102 AI Jane Doe 555-5678 4

103 Charlie CS103 Database 103 Mike Brown 555-9876 3

104 Alice CS103 Database 103 Mike Brown 555-9876 3

Q.6.

Customer Table

CustomerID CustomerName City

1 Alice New York

2 Bob Los Angeles

3 Charlie Chicago

4 Diana Miami

Order Table

OrderID CustomerID OrderDate TotalAmount

101 1 2024-01-10 250.00

102 2 2024-03-12 180.00

103 1 2024-02-15 300.00

104 3 2024-04-01 120.00

Product table

ProductID ProductName Price

P01 Laptop 1000

P02 Mouse 20

P03 Keyboard 50

1) Convert this table into First Normal Form (1NF), if it's not already in 1NF.

2) Convert the table into Second Normal Form (2NF).

3) Convert the table into Third Normal Form (3NF).

4) Provide the final schema after the normalization process to 3NF.

a. Write an SQL query to Display the customer name, order ID, and total amount for all orders placed.

b. Write an SQL query to Find the total quantity of each product sold.

c. Write an SQL query to List all products that have never been ordered.

d. Write an SQL query to List the customers from "New York" who have ordered a "Laptop".

--------------------------------LK-9285/13-11-2026-----(F-25)-----