Database System BS/MPhil Computer Science/MS Computer Science 4 Semester/Term UOS — University of Sargodha 2024

University of Sargodha

BS 4 Term Examination 2024

Subject: Computer Science

Paper: Database Svstems (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)

(2*8)

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

1

What is Data Warchouse?

What is database management system?

What is data independence?

What is composite identifier?

What is degree of relationship?

VI

vii.

vilI.

Write down the limitations of hierarchical and network model?

Explain the Multivalued Attribute.

What is data dictionary?

Subjective Part

（3°8）

Q.2.

Explain the functions of each of the following SQL keywords using sample SQL queries (or a single

SQL query if you prefer).

a) COUNT

b) GROUP BY

c) HAVING

d) INNER JOIN

e) IN

f) LIKE

Explain Degree and Cardinality of Relationships with example.

Q.3.

Q.4.

Patient ID

Name

Convert the following table into 3rd normal form:

Address

Visit Date

Physician

Diagnosis

Treatment

p-100809

Ali

32-M Model

Town Lahore

12-02-2007

20-02-2007

29-02-2007

15-03-2007

Dr. Zahid

Dr.Farooq

Dr. Rehan

Dr. Liagat

Chest infection

Cold

Hepatitis-A

Eyes infection

Frec

Free

Paid

Free

P-200145

Naila

9-Jail Road

Lahore

10-01-2007

15-02-2007

25-03-2007

Dr. Liaqat

Dr.Khushi

Dr.A yesha

Bone Fracture

Cough

Flu

Paid

Free

Free

Q.5.

Q.6.

Draw EE-R model of the following:

A person may be employed by one or more organizations, and each organization may be the employer of

one or more persons. An organization can be an internal organizational unit or an external organization.

For persons and organizations, we want to know their ID. name, address, and phone number. For persons,

we want to know their birth date, and for organizations, we want to know their budget number. For each

employment, we want to know the employment date, termination date, and bonus. Employment of a

person by an organization may result in the person holding many positions over time. For each position.

we want to know its title, and each time someone holds that position, we need to know the start date and

termination date and salary. An organization is responsible for each position. It is possible for a person to

be employed by one organization and hold a position for which another organization is responsible.

Write SQL commands for each of the following queries:

Relations:

i) Classes (class, type, country, numGuns, bore, displacement)

b) Ships (name, class, launched)

c) Battles (name, date)

d) Outcomes (ship, battle, result)

Queries:

a) Find the number of battleship classes.

b) Find the average number of guns of battleship elasses.

c) Find the average of guns of battleships. Note the difference between

(b) and

(c);

do we weight a class by the number of ships of that class or not?

d) Find for each class the year in which the first ship of that class was launched.

e) Find for each class the number of ships of that class sunk in battle.

• LK-5434/27-11-24-