Database System BS/MPhil Computer Science/MS Computer Science 4 Semester/Term University Of Sargodha (UOS) 2021

24436

BS 4 Term Examination 2021

Subject: Computer Science

Paper: Database System (CMPC-202)

Time Allowed: 02:30 Hours

Maximum Marks: 40

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

Objective Part

(Compulsory)

Q.1.

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

(2*8)

What is Multivalued dependency?

Define determinant.

Define Entity cluster.

iV.

Dierentine berveen timiy integrity pific integrity.

Differentiate betveen subtype nudgupertype.

vi.

vii.

viii.

Define Disjointess constrido

Define cluster.

What is the Importance of Entity integrity vs referential integrity?

Subjective Part

(3*8)

ustadni.com

Explain the differences between user views, a conceptualschemn, and an internal schema as different

Q.2.

Q.3.

perspectives ofthe same database

Develop an EER model for the following situation;

a)An INVOICE is written by a SALESREP. Each sales representative can write many invoices, but each

invoice is written by a single sales representative.

b) The INVOICE is written for a single CUSTOMER. However, each customer can have many invoices.

cAn INVOICE can include many detail lines (LINE), each of which describes one product bought by the

customer.

d) The product information is stored in a PRODUCT entity.

e) The product's vender information is found in a VENDOR entity.

Q.4.

Parking Tickets at Millennkum College

Parking Ticket Table

St In

L Name

38249

Brown

Phone No

111-7804

St lic

Ike No

BRY 123

Ticket

15634

Thoman

16017

14337

391-1689

AL

02453 - Green

TRE 141

16293

Data

10/17/10

11/13/10

10/D5/10

11/18/10

12/13/10

Code

2

Fine

$25

$15

$100

$15

$25

17892

Normalize the above patient bill upto 3d NF. Also write down rules which you are going to supposed to

achieve this normalization.

The following tables forms part of a database held in a RDBMS.

STAFF (StaffNo, Name, Address, Gender).

UStadAssIGN (StafNe, DepiNo, Postition).

DEPT (DeptNo, Description, MaxNoStaff, MinNoStaff).

MaxNoStaff indicates the maximum number of-stalla department should have before the department is

considered over-staffed. Staff count below MinNoStaff will therefore indicate under-staffing. Due to the lack

of staff, the database above is design in Such a way that each staff can be assigned to more than 1 department.

Assuming the above, give SQL statements to retrieve the following information:

.Com

a) Names of all the staff.

b) Department description for all departments that could have staff count of 10 (baseil (67l, MaxNoStaff and

MinNoStaff). Assuming that all departments are not over-staffed and not under-staffed?

c) The total number of staff for each department.

d) The total number of staff who had already being assigned departments). You are NOT allowed to use sub-

queries or the group by option.

c) The name of staffs that are not yet assigned any department.

D The staff number of staffs that are assigned to more than 3 departments.

Q.6.

Write a note on any of the following two:

1) Role of Data and Database Administrator.

b) Denormalization and Partitioning data.

c) Database Concurrency Controls.

yisit,website: ustadni.com