[This question paper contains 12 printed pages.
]
2006Rs 7000)
Your Roll No......
Sr. No. of Question Paper : 1388 A
Unique Paper Code : 32341403
Name of the Paper : Database Management Systems
Name of the Course : B.Sc. (H) Computer Science
(2019 onwards)
Semester : IV
Duration: 3 Hours Maximum Marks : 75
Instructions for Candidates
1. Write your Roll No. on the top immediately on receipt
of this question paper.
2. Section A is compulsory.
3. Attempt any FOUR questions from Section B.
4. Parts of questions must be answered together.
5. Marks are indicated against each question.
SECTION A
1. (a) Explain the three-schema architecture with the
help of a diagram. How does the three-schema
architecture relate to data independence?
(3+1)
P.T.O.
1388 2
(b) Consider the following database :
(4)
Employee (D. Name, Address)
Supervises (EmployeelD, SupervisorID)
Illustrate the concept of Recursive Closure
the relational algebraic query to retrieve using
the
supervisors of an employee with Name
Name = Sonia
Verma'. Show the query for the first 2 1levels of
the recursive closure using the following data :
Employee
ID Name Address
101 Rajesh Singh Delhi
102 Viren Garg Indore
103 Farah Khan Mumbai
201 Vini Bala Delhi
202 Ritika Madan Delhi
205 Satish Meena Delhi
302 Sonia Verma Mambai
304 Nilesh Kumar Mumbai
1388 3
Manapes Supeiee
EmployeeID SupervisorID
101 102
302 102
102 103
201 103
205 202
103 304
304 205
(c) Draw the query tree to show a possible order
of execution for the following relational expression:
RXS - T(R R.P=S.P AND R.Q=s.T (3)
(d) Consider the following two interleaved transactions
(T1, T2) executed concurrently in a railway
reservation system. W denotes the number of
vacant seats on a train. Assume that the initial
value of W is 10.
P.T.O.
4
1388
T1 T2
1. read (W) ;
2. W := W- 2
3. read (W);
4. W := W - 3;
5. write (W) ;
6. Write (W);
Compute the value of W after the given schedule
NG
is executed. Is this value of W porrect? If we
remove the interleaving between the transactions
(T1, T2), what will be the value of W. (4)
(e) Consider arelation R (X, Y, Z, W). Prove the
correctness of the following inference rule:
{X ’ Y, YW ’Z} |= WX’Z (3)
() Discuss the problem of spurious tuples and how
we may prevent it. Ilustrate using an example.
(3)
(g)Consider a disk with block size B = 500 bytes.
A block pointer is P=14 bytes long. The records
01 the relation B0OK are stored in a file. The
1388 5
file has r= 50,000 records of fixed length. Each
record has the following fields: ISBN (16 bytes),
Title (35 bytes), Publisher (20 bytes), Author
(20 bytes), Publication_Date (8 bytes). An
additional byte is used as a deletion marker.
Calculate the record size R in bytes, the blocking
factor bfr and the number of file blocks b, assuming
an unspanned organization. (3)
(h) Compare and contrast Naive Users and Casual
Users based on their interaction with the database.
(2)
(i) Why is it said that good database design in a
relational database model is characterized by
minimal redundancy? (3)
() Draw an EER diagram that shows the entity
type(s), attribute(s), relationship(s), and
specialization(s) for the following SPORTS
COMPLEX database :
(i) A complex has a location, chief organizing
individual, total occupied area, the number
of facilities needed, budget, and information
on the planned events.
P.T.O.
6
1388
(ii) For each event there is a planned
duration, number of
date,
of officials.
participants, number
(ii) Asports complex can be a one-sport or a
multi-sport complex.
(iv) Multi-sport complexes have areas of the
complex designated for each sport with a
location indicator, namely. Fast, West.
South, North, Center. (3)
) Consider the following relation for a STUDENT
database:
STUDENT (SSN, Name, Major, BirthDate)
Which of the three update anomalies may be
violáted by the command:
"Insert a record in the STUDENT table"
Justify your answer. (3)
1388
SECTION B
2. ka) Draw an ER diagram for the following case study :
Consider a company where the employees take
satgtuce
orders for parts from customers. The employees
are identified by arunique employee number, first
and last name, and location code> Each customer
of the company is identified by a unique customer
number, first and (last name and location code.
Each part sold by the company is identified by a
unique part number,a part name, price, and
quantity in stock.Each order placed by a customer
is taken by an employee and is given a unique
order number. Each order contains specified
quantities of one or more parts, has a date of
receipt as well as an expected shipment, date.
The actual shipment date is also recorded.
Specify primary key, cardinality ratio and
participation constraints in the diagram clearly.
(7)
K6) Consider the following relation that represents the
courses taught in a University: (3)
P.T.0.
1388 8
UNIVCLASS(Course#, UnivSection#, Instructor
Name, Semester,
BuildingCode, Room#.
TimePeriod, Weekdays, CreditHours)
In the given relation, the attribute
takes unique values. UnivSection#
Identify any three candidate keys, and Write in
your oWn words the conditions or
assumptions
under which each candidate key would be valid
3. An Alumni data file with enrolment number as the
kev field includes records with the following enrolment
number values :
25, 66, 64, 90, 50, 60, 19, 20, 88, 39,
(a) Suppose that the key field values are inserted in
an initially empty B+ tree of order p =4 and
P,,=3.Show how the tree will appear whenever
anode is split.
(b) Show how the tree would appear on deletion of
each of the key field values 19, 20.
(c) What will be the expected number of blocks to be
read from the hard disk while searching for a
record in the above dynamic indexing structure?
Justify your answer. (10)
1388
(a) For a relation R(A, B, C, D, E) consider the
4
following sets of functional dependencies :
F ={A ’ C, AC ’ D, E ’ ADH}
G = {A ’ CD, E ’ AH}.
Show that F and G are equivalent. (4)
(b) Design a relation schema for the ternary
relationship given below so that it can be
represented in a relational database. (6)
Semester Yeas
TAUGHT_DUAING
Lnante Sem year
INSTRUCTOR OFFERS SEMESTER
CAN_TEACH OFFERED DURING
Cnumber
COURSE
5. (a) What are the three interpretation of a NULL
value? Why should NULL values in a relation be
avoided? (3)
P.T.0.
1388 10
(b) Consider the following two relations R and S:
P 0D P BC
a5 10 b 5
25b8 25 1
|5 a6
a 10
Showthe result of the following relational queries :
() gc=p(S X R)
(ii) SMINUS (R UNION S)
(ii) Pa(Pe.p) (Tp.o (R) * (S)) (7)
6. Consider a relation R (A, B, C, D, E). The
corresponding functional dependency set is given as
follows :
F = {AB ’ E, AE ’ D, C ’ AD}
(a) Find the primary key showing the methodto arrive
at the result.
11
1388
Assuming that the given relation is in 1NF, find
(b)
the relation satisfies.
.he highest Normal Form that
conclusion.
Show the steps for reaching the
indicating the
(c) Normalize the relation up to 3NF
decomposition of the relations at each step.
(10)
relation
Consider the following relations (key of each
is underlined) :
SALESPERSON (SNo, SName, Commission)
PRODUCT (Pld, Pname)
CUSTOMER (CNo, CName, CAddress)
SALE (Date, CNo, SNo, Pld, Quantity)
queries :
Write SQL statements for the following
TABLE command for the
(i) Write the CREATE
following
SALEtable in SQL ensuring that the
Integer. String,
concepts are used at least once:
NULL constraint.
and Date data type, NOT
KEY constraint.
CHECK constraint. PRIMARY
ON DELETE
FOREIGN KEY constraints (with
NULL and ON UPDATE CASCADE
SET
constraints, if applicable).
P.T.0.
1388 12
(ii) Get the names of the SALESPERSON who sold
the product with Pld = 56.
(ii) Get the names of CUSTOMERS who bought
product "Mixie".
(iv) Get the total number of PRODUCTS sold on
"25-02-2022".
(v) Get the total number of PRODUCTS purchased
by each CUSTOMER. (10)
Ne Lost
Upolate
(1500)
