University of Sargodha ADP/BS 2"d Semester (F-24) Examination 2025 Jaweri@Gull ADP (IT)

University of Sargodha

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

Jaweri@Gull

ADP (IT)

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)

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

What is data independence? Why is it important?

Differentiate between subtype and supertype. g

Shortly explain referential integrity constraint. 2

What is anomaly in database?

*vi.

sh vil.

*viii.

Define Aggregation. 2

What's meant by transitive dependency??

What's meant by denormalization??

(2*8)

.3.

70.4.

Usta:

.6.

Subjective Part

(3*8)

Explain three level architecture in detail.

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)

ii) Design an ER diagram including:

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

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

• Keys and cardinality

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

→1NF

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

Rules

iii. List final relations and indicate primary/foreign keys

Assume the following normalized tables for a small library database:

→2 NF

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

Authors(AuthorID, AuthorName)

Rules

Categories(CategoryID, CategoryName)

MembersMemberID, MemberName, JoinDate)

=3 NF

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

Rules.

Write the following five SQL queries:

List names of all books borrowed by a specific member.

Find the most borrowed book in the system.

com

Show the number of books borrowed in each category.

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

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

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

ustadni.com