Database System BS 2 Semester/Term University Of Sargodha (UOS) 2025

— NS I em

University of Sargodha

App/Bs 2+ Semester {F-24) Examination 2025

Sublet; CSAT/SE Paper PAtabase System (CMPC-5203)

Time Allowed: 02:30 Hours Maximum Marks: 40

Note: Objective part is compulsory, Attempt any three questions from subjective part.

(Mustrate your answer with diagram where needed).

Objective Part (Compulsory)

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

i. What is data independence? Why is i Pp os @°8)

ii. Y 18 iLamportant?

E Differentiate between subtype and supertype.

. co dap vg referential integrity constraint.

sn are the problems caused by data redundancy in unnormali ions?

HW is Sih ? yy ized relations?

RE ?* Define Aggregation. . CO

vii. What's meant by transitive de 4

viii. What's meant by denomaliy } o©

A\

0)

Subjective Part (3*8) RE Co

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.

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

ii) Design an ER diagram including:

* Antributes (e.g., Doctor Name, Specialty, Appointment Date)

e Relationships (¢.g., "makes", "consults”)

e Keys and cardinality

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

| RecordID | EmployeelD | EmployeeName | Department | TripStartDate | I'ripEndDate | 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, AuthorlD, CategorylD, YearPublished)

« Authors(AuthorlD, AuthorName)

« Categories(CategorylD, CategoryName)

« Members(MemberID, MemberName, JoinDate)

» . BookID, MemberID, LoanDate, ReturnDate)

rite the fii five SQL queries:

w is A

Find the most borrowed book in the system

oH Show the number of books smi seh baegory

iv. List all overdue books (where Reta ¢ ta null and loan is older than 15 days). a

v Retrieve names of authors ve more than 3 books in the library. 3 CP

Q.6. Wrile a nole on any of the following two: 2O®

a Open source database system Oo

b. Concurrency controls

¢. Database indexes

ustadni.com