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

University of Sargodha

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

Subiect: 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 cach on your answer sheet.

1.

ii.

What is data independence? Why is it important?

Differentiate between subtype and supertype.

ili.

Shortly explain referential integrity constraint.

iv.

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

of What is anomaly in database?

Define Aggregation.

What's meant by transitive dependere COm

vili.

What's meant by denormalization?

(2*8)

ustadni.com

Subjective Part (3*8)

Q.2/ Explain three level architecture in detail.

Q.3.

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.

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

il) Design an ER diagram including:

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

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

• Keys and cardinality

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

RecordID | EmployeelD | 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 SOL. queries:

ughed her names of all books borrowed by a specific member.

Find the most borrowed book in the system

sbow the number of books burowed it el cson.

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

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

Q.6.

Write a note on any of the following two:

a Open source database system

ustadni.com

b. Concurrency controis

c. Database indexes

LK-9285/13-11-25 -

ustadni.com