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 best alternative in the following: (2x10)
a. A database schema is specified by a set of definitions expressed by a language called _______________
(A) Procedural language
(B) Data manipulation language
(C) Data definition language
(D) Data query language
b. Domain constraints and referential-integrity constraints are special forms of
(A) Triggers
(B) Cursors
(C) Views
(D) Assertions
c. If α 
β holds and γ β
δ holds, then α γ δ holds. This rule is
(A) Transitivity rule
(B) Pseudo transitivity rule
(C) Augmentation rule
(D) Reflexivity rule
d. The _______________________ operation between two relations ‘r’ and ‘s’ produces a relation with tuples which are there in ‘r’ but not in ‘s’ is
(A) SET DIFFERENCE
(B) SET UNION
(C) DIVISION
(D) CARTESIAN PRODUCT
e. Given set of FDs {A BC, B CA, C AB}, the candidate keys are
(A) {A}
(B) {A}, {B}
(C) {C}
(D) {A}, {B}, {C}
f. Memory-style error-correcting-code (ECC) organization refers to
(A) RAID level 0
(B) RAID level 1
(C) RAID level 2
(D) RAID level 3
g. The organization in which the records of several different relations can be stored in the same file refers to
(A) Heap file organization
(B) Hashing file organization
(C) Clustering file organization
(D) B+ Tree file organization
h. The process of selecting the most efficient query evaluation plan for a query is known as
(A) Query optimization
(B) Query processing
(C) Parsing
(D) Translation
i. The protocol that ensures the resulting schedules will be conflict-serializable, cascade-less and recoverable is
(A) Graph-Based locking protocol
(B) Strict-Two-Phase locking protocol
(C) Time-Stamp-Ordering protocol
(D) Multiple-Granularity protocol
j. The technique that ensures transaction atomicity by recording all database modifications in the log, but delaying the execution of all write operations of a transactions until the transaction partially commits refers to
(A) Log-Record Buffering
(B) Deferred Database Modification
(C) Shadow Paging
(D) Immediate Database Modification
Answer any FIVE Questions out of EIGHT Questions.
Each question carries 16 marks.
Q.2 a. What do you mean by Data Abstraction? Explain the difference between Physical level, Logical level and View level of data abstraction? (6)
b. A university registrar’s office maintains data about the following entities: (i) courses, including number, title, credits, syllabus, and prerequisites; (ii) course offerings, including course number, year, semester, section number, instructor(s), timings, and classroom; (iii) students, including student-id, name, and program; and (iv) instructors, including identification number, name, department, and title. Further, the enrollment of students in courses and grades awarded to students in each course they are enrolled for must be appropriately modeled.
Construct an E-R diagram for the registrar’s office. Document all assumptions that you make about the mapping constraints. (10)
Q.3 a. What do you mean by Entity and Relationship in ER model? Explain how a relationship set is defined? (6)
b. Consider the following relational database
employee (employee-name, street, city)
works (employee-name, company-name, salary)
company (company-name, city)
manages (employee-name, manager-name)
Give an expression in the tuple calculus to express each of the following queries:
(i) Find the names and cities of residence of all employees who work for XYZ Bank.
(ii) Find the names, street address, and cities of residence of all employees who work for XYZ Bank and earn more than Rs.10,000 per annum.
(iii) Find the names of all employees in this database who live in the same city as the company for which they work.
(iv) Find the names of all employees who live in the same city and on the same street as do their managers. (10)
Q.4 a. Explain the following relational algebra operations with their notations by suitable example
(i) INTERSECTION
(ii) PROJECT
(iii) ASSIGNMENT
(iv) SELECT (8)
b. Let the following relation schemas be given:
R = (A,B,C)
S = (D,E, F)
Let relations r(R) and s(S) be given. Give an expression in SQL that is equivalent to each of the following queries.
(i) ΠA(r)
(ii) σB =33(r)
(iii) r X s
(iv) ΠA,F (σC =D(r X s)) (8)
Q.5 a. What is referential integrity? What is the need for triggers and how are they implemented in SQL? Give Example. (8)
b. A
functional dependency α β is called partial dependency if
there is a proper subset γ of α such that γ β. We say that β is
partially dependent on α. A relation scheme R is in second normal form
(2NF) if each attribute A in R meets one of the following:
§ It appears in a candidate key
§ It is not partially dependent on a candidate key.
Show that every 3NF schema is in 2NF. (8)
Q.6 a. Consider a relation schema R = (A,B,C,D,E) with the following set of functional dependencies holding on it
A
BC
CD
E
B
D
E
A
Determine closure F+ and list the candidate keys for R. (8) 8
b. Let
relations r1(A,B,C) and r2(C,D,E)
have the following properties: r1 has 20,000 tuples, r2
has 45,000 tuples, 25 tuples of r1 fit on one block, and 30
tuples of r2 fit on one block. Estimate the number of block
accesses required, using each of the following join strategies for r1 
r2:
(i) Nested-loop join
(ii) Block nested-loop join
(iii) Merge join
(iv) Hash join (8)
Q.7 a. Discuss the various built-in domain types supported by SQL standard? What do you mean by Embedded SQL? (10)
b. What are the causes of bucket overflow in a hash file organization? What can be done to reduce the occurrence of bucket overflow? (6)
Q.8 a. What is “Two-Phase locking protocol”? Explain with the help of example of a schedule, how the protocol ensures a schedule to be conflict-serializable, but not Cascade-less? (8)
b. Define Deadlock. What are the necessary four conditions for a deadlock to occur. Discuss the different methods that can be used for deadlock prevention. (8)
Q.9 Explain the followings:
(i) Equivalence Rules of Query Processing
(ii) ACID properties
(iii) RAID level 4
(iv) Transaction State (4x4)