Database System BS 5 Semester/Term University Of Sargodha (UOS) 2024

University of Sargodha

BS 5i Term Examination 2024

Paper: Database Systems (CMPC-301)

Subject: IT

Maximum Marks: 40

Time Allowed: 02:30 Hours

Objective Part

(Compulsory)

ustadni confart is compulsory. Attempt any three questions from subjective part.

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

(2*8)

Differentiate between strong entir 6pe and weak entity type.

What is a view in a database Ne

iii.

Write SOL to create a table hamed "Product" with attributes of "Prodid", Name": COM

iv.

V.

vi.

vil.

VIII.

What type of anomalies can occur in a table with redundant data? Nagg ti

What meant by program data-independence?

What is the purpose of data dictionary in RDBMS?

What is a Data Warehouse?

List any two properties of a "relation".

Subjective Part

(3"8)

Q.2.

Describe database transaction and its properties. Also give an example of a situation where a

transaction might need to be rolled back.

Q.3.

Q.4.

Describe the physical database design process, its objectives, and its deliverables.

A University's course enrollment system initially has two entities for students and courses involving a

binary many-to-many relationship. Courses should have attributes of course codes, titles, and credit

hours. Students should have attributes of roll numbers and names. The students are further

categorized as undergraduate students and graduate students. Undergraduate students have additional

attribute of major whereas graduate students have the attributes of research topic and supervisor.

Draw an EER diagram for this case study. Afterwards, convert it into a set of relations.

Q.5.

Write SQL queries considering the following schema:

CUSTOMER (CustomerID, CustomerName, Address, Phone)

ORDER (OrderID, CustomerID, OrderDate, TotalAmount)

ORDER-DETAIL (OrderID, ProductID, Quantity, UnitPrice)

a)

Show CustomerID and TotalAmount of all those customers who placed an order with OrderID =

1001.

b) Modify the CUSTOMER table to include a new column of "Email".

• are PedeD, Order Dare, and TotalAmount of all orders sorted by OrderDate in ascending.

glad now CustomerD, CustomerName, ant Total Amount of all customers whose total order amount

is greater than 5000.

DoctorlD

Given the following table, decompose it ki COM

of relations that are in 3NF.

DoctorName

Appointmens Appointment-

Fee

PatientID

PatientName

Patt

Date

Number

COTPhone

George ni

023213224

DIOI

D102

D103

Dr. Mark

Dr. Harper

Dr. Scott

04-01-24

12

05-01-24

12

19

2000

1500

1000

POSO

P098

P099

USthie

034328729

Stephen

025213881

12-01-24

- LK-6840/06-06-24-.

visit website: ustadni.com