University of Sargodha ADP/BS 2"d Semester (F-24) Examination 2025 Subiest: SAT/S Paper: Database System (CMPC-5203) Time Allowed: 02:30 Hours

University of Sargodha

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

Subiest: SAT/S Paper: Database System (CMPC-5203)

Time Allowed: 02:30 Hours

Note:

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

(Illustrate your answer with dingram where needed).

Objective Part (Compulsory)

Q.1.

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

1.

ii.

ili.

iv.

V.

vi.

Vil.

vili.

What is data independence? Why is it important?

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?

Maximum Marks: 40

(2*8)

Subjective Part

(3*8)

Q.2.

Q.3.

Explain three level architecture in detail.

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

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)

ii) 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

ili. 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)

• Members(MemberID, MemberName, JoinDate)

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

Write the following five SQL queries:

List names of all books borrowed by a specific member.

it Find the most borrowed book ta 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 -