University of Sargodha
ADP/BS 2nd 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 each on your answer sheet.
ustaci
iii.
iv.
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?
vi.
vii.
viii.
Define Aggregation.
What’s meant by transitive dependency?
What’s meant by denormalization?
ustadni.com
(2*8)
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 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)
il) Design an ER diagram including:
• Attributes (e.g., Doctor Name, Specialty, Appointment Date)
• Relationships (c.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
iii. List final relations and indicate primary/foreign keys
Q.5. Assume the following normalized tables for a small library database:
• BooksBookID, Title, AuthorID, CategoryID, YearPublished)
• Authors(AuthorID, AuthorName)
• Categories(CategoryID, CategoryName)
• MembersMemberID, MemberName, JoinDate)
• Loans(LoanID, BookID, MemberID, LoanDate, ReturnDate)
Write the following five SQL queries:
i. List names of all books borrowed by a specific member.
i. Find the most borrowed book in the system.
i Show the number of books borrowed in each category.
Retrieve names of authors who have more than 3 books in the library.
0.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 —-
ustadni.com