Database System ADP/BS 2 Semester/Term UOS — University of Sargodha 2025

. Ld E h a

University of Sargodha

T : 3 02

ADP/BS 2"¢ Semester (Fs xamination 2 pe

€ -52

Subject: CS/IT/SE Paper: D e System (CME Maxi Marks: 40

Z aximum :

Time Allowed: 02:30 Hours |

: 2 TEs i art.

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

(Illustrate your answer with diagram where rigeded).

Objective Party > (Compulsory)

Q.L. Write a of the following in 2-3 lines each on your answer sheet. (2*3)

ad t is data independence? Why is it ihporant?

2

i. Differentiate between subtype and sup :

Jo"

ii. Shortly explain referential integrity constraint. on)

iv. What are the problems caused By data dundancy 1n unnormalized relations:

v. What is anomaly in database? |

vi. Define Aggregation. i

vii. What's meant by transitive dependency? x

viii. What's meant by denormalization?

Subjective Part (3*8)

Q.2. Explain three level architecture in detail. ;

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

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

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

Time Allowed: 02:30 Hours 3 at

Note: Objective part is compulsory. Attempt any three questions {Us gubjectiye ua:

(Illustrate your answer with diagram where needed).

Objective Part) (Compulsory)

Q.1 + Write Jighotavers of the following i in 2-3 lines each on your answer sheet. (2*8)

oS tis data independence? Why is it ithportant?

Differentiate between subtype and sup

\ ov Shortly explain referential integrity con: int.

iv. What are the problems caused By data redundancy in unnormalized relations?

Vv. What is anomaly in database?

vi. Define Aggregation. |

vii. What's meant by transitive dependency?

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 1D,

name, specialty, and contact number. Each pati a unique ID, name, génder, and age. A patient

can make one or more appointments, and each a ointment is with one dogs®, ona PoTiCRL. date and

time. Prescriptions are issued during appointmén Why Ee A

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

i1) Design an ER diagram including:

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

e Relationships (e.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 | TripEndDate | Destination |

ExpenseType | ExpenseAmount | %

i. Identify repeating groups and partial / transitive dependencies

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

iii. List final relations and indicate primary.

Q.5. Assume the following normalized tables for

ign keys

all library database:

« Books(BookID, Title, AuthorID, CategorylD, YearPublished)

. Authors : hol: AuthorName) £5

e Cs es 1D, CategoryN:

SATE : , MemberName, JoinDate)

> > y BookID, MemberID, Lo ReturnDate)

Write the Following five SQL queries:

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

i. Find the most borrowed Boi i fs. 5 13 ©

iii. ~~ Show the number of books bor

iv. List all overdue books (wher

Vv. Retrieve names of authors wh

Q.6. Write a note on any of the following two:

a. Open source database system ay

b. Concurrency controls

¢. Database indexes

wed in each category.

mDate is null and loan is older than'ts days).

- than 3 books in the library.

oils ustadni.com