University of Sargodha ADP/BS 2nd Semester (F-24) Examination 2025 Subject: CS/IT/SE Paper: Database System (CMPC-5203) Time Allowed: 02:30 Hours

University of Sargodha

ADP/BS 2nd Semester (F-24) Examination 2025

Subject: CS/IT/SE Paper: Database System (CMPC-5203)

Time Allowed: 02:30 Hours

Maximum Marks: 40

Note:

Objective part is compulsory. Attempt any three questions from subjective part.

(Illustrate your answer with diagram where needed).

Objective Part (Compulsory)

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

What is data independence? Why is it important?

ii.

iii.

iv.

V.

vi.

vii.

viii.

Differentiate between subtype and supertype.

Shortly explain referential integrity constraint.

What are the problems caused by data redundancy in unnormalized relations?

What is anomaly in database?

Define Aggregation.

What's meant by transitive dependency?

What's meant by denormalization?

(2*8)

vein

Subjective Part

(3"8)

Q.2. Explain three level architecture in detail.

Q.3.

Entity

A hospital wants to maintain records of its patients, doctors, and appointments. Each doctor has an ID,

name, specialty, and contact number. Each patient has a unique ID, name, gender, and age. A patient

can make one or more appointments, and each appointment is with one doctor, on a particular date and

time. Prescriptions are issued during appointments.

i) Identify entities (Patient, Doctor, Appointment, Prescription)

11) Design an ER diagram including:

• Attributes (e.g., Doctor Name, Specialty, Appointment Date)

• Relationships (e.g., "makes", "consults")

• Keys and cardinality

Q.4.

A company keeps a record of its employee travel reimbursements in the following table:

| RecordID | EmployeeID | EmployeeName | Department | TripStartDate | TripEndDate | Destination |

ExpenseType | ExpenseAmount |

i. Identify repeating groups and partial / transitive dependencies

ii. Normalize the table step-by-step to 3rd Normal Form

iii. List final relations and indicate primary/foreign keys

Q.5.

Assume the following normalized tables for a small library database:

• Books(BookID, Title, AuthorID, CategoryID, YearPublished)

• Authors(AuthorID, AuthorName)

• Categories(CategoryID, CategoryName)

• MembersMemberID, MemberName, JoinDate)

• Loans(LoanID, BookID, MemberID, LoanDate, ReturnDate)

Write the following five SQL queries:

i. List names of all books borrowed by a specific member.

ii. Find the most borrowed book in the system.

iii.

Show the number of books borrowed in each category.

iv.

List all overdue books (where ReturnDate is null and loan is older than 15 days).

V.

Retrieve names of authors who have more than 3 books in the library.

Q.6.

Write a note on any of the following two:

a. Open source database system

b. Concurrency controls

c. Database indexes

-- LK-9285/13-11-25