AMIETE –
CS/IT (OLD SCHEME)
Code: AC14 / AT11 Subject: DATABASE MANAGEMENT SYSTEMS
Time: 3 Hours
Max. Marks: 100
NOTE: There are 9 Questions in all.
· Question 1 is
compulsory and carries 20 marks. Answer to Q.1 must be written in the space
provided for it in the answer book supplied and nowhere else.
· Out of the remaining
EIGHT Questions, answer any FIVE Questions. Each question carries 16 marks.
· Any required data not
explicitly given, may be suitably assumed and stated.
Q.1 Choose the correct or the best alternative in the following: (2
10)
a.
A 'tuple' is a ___________
(A)
A row or record in a database table
(B) Another name for a
table in an RDBMS
(C)
An attribute attached to a record
(D) Another name for
the key linking different tables in a database.
b.
An
E-R modelling for given application leads to _________
(A)
Conceptual data model (B)
External data model
(C) Internal data model (D) Logical data model.
c.
If
an entity appears in N relationships then it is
(A)
a 1:1 relationship (B)
a 1:N relationship
(C)
a N:1 relationship (D)
a N:M relationship
d.
Null
values indicate_________
(A) True and known (B) Zero or blank
(C)
Unknown and non existent (D)
False and known
e.
A
3NF is converted to BCNF by:
(A)
Removing composite keys
(B) Removing multivalued dependencies
(C)
Dependent attributes of overlapping composite keys are put in a separate relation
(D)
Dependent non key attributes are put in a separate table
f.
Data
integrity is ensured by
(A)
Good data editing
(B) Propagating data changes to all data items
(C)
Preventing unauthorized access
(D) Preventing data duplication.
g. Designing physical model of DBMS does NOT
requires information on
(A)
Data volume (B)
Frequency of access to data
(C)
Programming language used (D)
Secondary memory characteristics
h.
Which
of the following intersection operation on sets S and R is correct:
(A)
(R U S) - (R-S) U(S-R) (B)
R-S
(C)
(R+S)/(R-S) (D)
None of above.
i.
The
cost of query evaluation can be measured in terms of ___________
(A)
CPU time.
(B) Cost of communication.
(C)
Number of blocks transfers from disk.
(D) All of above
j.
Any
operation that can be implemented by sorting can also be implemented by hashing.
(A)
True (B)
False
Answer any FIVE
Questions out of EIGHT Questions.
Each question carries
16 marks.
Q.2 a. What are the
disadvantages of using file system over Database system? (6)
b. Admission procedure in
a university is as follows:
An advertisement is issued
giving essential qualification for the course, the last date for receipt of application,
and the fee to be enclosed with the application. A clerk in Registrar’s office checks the
received applications to see if mark sheet and fee are enclosed and send valid
applications to the concerned academic department. The department checks the
application in detail and decide the applicants to be admitted, those to be put
in the waiting list, and those rejected. Appropriate letters are sent to the
Registrar’s office which intimates the applicant.
Obtain an E-R diagram and a
set of relations for the above statement. (10)
Q.3. a. What is Data Dictionary? What type of information is
stored in Data Dictionary? (8)
b. What is a relationship? In what ways it is different
from an entity? (4)
c. Normalize the
relation:
DRIVER
(License no., name, address, type,
validity, date of license)
DRIVES (License no., registration no.)
CAR (Registration no., manufacturer, model, year, horse power, chassis
no., body no., no. of seats) (4)
Q.4 a. Explain the
significance of Normalisation. (4)
b. What is Armstrong axiom? Show that the following
inference rules are derivable from Armstrong’s axioms
(i)
(ii) Decomposition: If X ŕ Y Z, then X ŕ Y and X ŕ Z. (4)
c. Given the
database schema R (a, b, c), and a relation r on the
schema R, write an SQL
query to test whether the functional dependency b → c holds on relation r. Also
write an SQL assertion that enforces the functional dependency. Assume that no
null values are present. (4+4)
Q.5 a. What are constraints?
Explain different types of constraints. (2+4)
b. What
is an index? What are the types of indexes? How many clustered indexes can be
created on a table? Suppose we create a separate index on each column of a
table. What are the advantages and disadvantages of this approach?
(2+2+2+4)
Q.6 a. XYZ Coporation’s
Director of Human Resources receives a monthly report providing salary and
reporting information for each company employee. Perform the following queries
in SQL
(i) Create an employee table.
(ii) Which employee has the highest salary?
(iii) Compute the total number of employees
and average salary for each Department.
(iv) Delete
all employees from department number 13.
(v) Add one more column 5)
b. (i)
Briefly
explain the steps involved in processing a query.
(ii) Consider the following SQL query for our bank
database
select balance
from account
where balance<5000
Write an efficient relational
algebra expression that is equivalent to this query. Justify your choice. (3+3)
Q.7 a. What is a deadlock and
what is a live lock? How will you go about resolving deadlocks? (8)
b. During its execution,
a transaction passes through several states, until it finally commits or
aborts. List all possible sequences of states through which a transaction may
pass? Explain why each state transition may occur? (8)
Q.8 a. Why Concurrent Control is needed in executing concurrent transactions? (4)
b.
Consider the following two
transactions:
T31: read(A);
read(B);
if
A=0 then B := B + 1;
write(B).
T32: read(B);
read(A);
if
B =0 then A := A + 1;
write(A).
Add lock and unlock
instructions to transactions T31 and T32, so that they observe the two-phase
locking protocol. Can the execution of these transactions result in a deadlock? (12)
Q.9 Differentiate between the
following:
(i) DDL and DML.
(ii) RAID level 3 and RAID level 4.
(iii) Ordered indexing and
hashing. (16)