AMIETE – CS/IT (NEW SCHEME) – Code: AC61 / AT61

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: (210)

a. The various copies of the same data may no longer agree is called

(A) Data inconsistency (B) Data redundancy

(C) Data isolation (D) Data abstraction

b. Declarative DMLs also referred to as

(A) Procedure DMLs (B) Non procedural DMLs

(C) Query Language (D) DDL

c. ________ is a set of one or more attributes that, taken collectively allow us to identify uniquely a tuple in the relation.

(A) Super key (B) Secondary key

(C) Candidate key (D) Foreign key

d. To remove a relation from a SQL database, we use the

(A) Delete table (B) Alter table

(C) Update table (D) Drop table

e. A query in the tuple relational calculus is expressed as

(A) { P(t)| t) (B) { <x₁,x₂….x_n>| P(x₁,x₂,…x_n)}

(C) { t| P(t)} (D) {t = P(t)}

f. _________ writes an updated buffer at a different location, to maintain multiple versions of data items.

(A) Caching (B) Shadowing

(C) Buffering (D) Spooling

g. The process of designating sub groupings within an entity set is called

(A) Specialization (B) Generalization

(C) Inheritance (D) Aggregation

h. _________ is the fastest and most costly form of storage.

(A) Hard disk (B) RAM

(C) Cache (D) ROM

i. An index record appears for only some of the search key values is

called

(A) Dense index (B) Sparse index

(C) Hash index (D) Ordered index

j. A transaction that complete its execution successfully is said to be

(A) Aborted (B) Active

(C) Failed (D) Committed

Answer any FIVE Questions out of EIGHT Questions.

Each question carries 16 marks.

Q.2 a. What are the five main functions of database administrator? (6)

b. Differentiate schema and instance. (4)

c. How does 2 tier architecture differs from 3 tier architecture? (6)

Q.3 a. Explain the following terms: (6)

(i) Entity integrity

(ii) Referential integrity

(iii) Foreign key

b. Let R=(A,B,C) and let r1 and r2 both be relations on schema R. Give an expression in the domain relational calculus that is equivalent to each of the following:

(i) П _A (r₁)

(ii) σ _B=17 (r₁)

(iii) r₁ U r₂

(iv) r₁ – r₂

(v) П _A,B (r₁) П _B,C (r₂) (10)

Q.4 a. Draw E-R diagram for car insurance company with a set of customer, each of whom owns a number of cars. Each car has a number of recorded accident associated with it. (8)

b. Give an example for the following SQL queries:

(i) Join (ii) Any two aggregate functions

(iii) Update (iv) Order By (8)

Q.5 a. Suppose we decompose the schema R=(A,B,C,D,E) into R1 = (A,B,C) and R2 = (A,D,E). Show that this decomposition is a lossless decomposition if the following set F of functional dependencies holds:

A→ BC

CD → E

B → D

E → A (8)

b. Explain Third Normal Form (3NF) and Boyce-Codd Normal Form (BCNF). (8)

Q.6 a. Write an algorithm to search for a record with search key field value K, using a B⁺- tree. (8)

b. Explain various types of indexing structures for files. Give their applications. (8)

Q.7 a. Explain desirable properties of transactions also known as ACID properties.

(4)

b. Give an example of serial and non-serial schedule involving any two transactions and . (6)

c. Describe various types of locks used in concurrency control. (6)

Q.8 a. Define the following:

(i) Transaction rollback (ii) Cascading rollback (8)

b. Explain the terms: steal and no-steal approach in standard DBMS recovery schemes. (8)

Q.9 a. Define query optimization and explain its significance in DBMS. (8)

b. Explain the algorithms for the following:-

(i) Project and set operations.

(ii) Outer Join. (8)