Code: A-30 Subject: DATABASE MANAGEMENT SYSTEMS

Time: 3 Hours Max. Marks: 100

NOTE: There are 11 Questions in all.

· Question 1 is compulsory and carries 16 marks. Answer to Q. 1. must be written in the space provided for it in the answer book supplied and nowhere else.

· Answer any THREE Questions each from Part I and Part II. Each of these questions carries 14 marks.

· Any required data not explicitly given, may be suitably assumed and stated.

Q.1 Choose the correct or best alternative in the following: (2x8)

a. In a relational model, degree is termed as

(A) number of tables. (B) number of attributes.

(C) number of rows. (D) number of candidate keys of the

table.

b. The database schema is written in

(A) DML. (B) HLL.

(C) DDL. (D) DCL.

c. The model which uses plex structure as its basic structure is

(A) Relational Model. (B) Network Model.

(C) Hierarchical Model. (D) None of the above.

d. The Third Normal Form (3NF) removes

(A) functional dependency. (B) transitive dependency.

(C) multivalued dependency. (D) fully functional dependency.

e. Relationship in an E-R diagram is represented by

(A) rectangle. (B) ellipse.

(C) triangle. (D) None of the above.

f. Which of the following is not an ACID property of transactions

(A) atomicity. (B) concurrency.

(C) isolation. (D) durability.

g. The term deadlock is related to

(A) Database recovery. (B) Database security.

(C) Concurrency Management. (D) Database transaction.

h. QBE stands for

(A) Query by Enterprise. (B) Query by Evaluation.

(C) Query by Example. (D) Query by Execution.

PART I

Answer any THREE Questions. Each question carries 14 marks.

Q.2 a. What is normalization? Explain upto 3NF with a suitable example each. (8)

b. Given a relation R (A, B, C, D) and the set of FDs on R is given by . The relation R is decomposed into relations and . Is this decomposition lossless and dependency preserving. (6)

Q.3 a. Explain the difference between physical and logical data independence. (7)

b. Explain the difference between a file-oriented system and a database oriented system. (7)

Q.4 a. Consider the relations

Person (name, address)

Car (reg_no, cost, model)

Own (pname, reg_no)

Write SQL Queries for the following :-

(i) Get the names of persons who live in ‘Delhi’.

(ii) Get the models of cars which cost more than Rs.2,00,000/-.

(iii) Get the names of persons who do not own cars.

(iv) Get the names of persons, the reg_no of cars they own and the models of the cars. (3 x 4=12)

b. For the relation Car in (a) above express the constraint in SQL that cars cannot cost more than Rs.20,00,000/- as a check clause. (2)

Q.5 a. What is locking? Explain the advantages and disadvantages of two phase locking. (7)

b. What is a deadlock? What is wait-for graph and how can it be used for deadlock detection. (7)

Q.6 a. Discuss the ACID properties of a database transaction. (6)

b. Define primary key, candidate key, foreign key and super key with an example. (8)

PART II

Answer any THREE Questions. Each question carries 14 marks.

Q.7 a. Explain serial, serializable and non-serializable schedule with the help of an example. (7)

b. What is relation algebra? Explain the set operations with an example each. (7)

Q.8 a. Given the relations

Supplier (S#, SNAME, STATUS, CITY)

Part (P#, PNAME, COLOR, WEIGHT, CITY)

Supply (S#, P#, CITY)

Write QBE for the following :

(i) Get the supplier numbers of suppliers who supply both part P1 and P2.

(ii) Get the supplier numbers of suppliers who are located in Delhi or have status > 20 (or both).

(iii) Get the supplier names of suppliers who supply at least one blue part. (3 x 3=9)

b. What is a Data Dictionary? Explain how this concept is useful. (5)

Q.9 a. Describe the three level architecture of DBMS. Explain how it is useful for achieving data independence. (7)

b. What are the responsibilities of DBA? Explain briefly. (7)

Q.10 a. Explain the search algorithm for index sequential access method. (7)

b. Compare the relational model with the network model. (7)

Q.11 Write short notes on any FOUR of the following :

(i) Triggers.

(ii) DBTG data structures.

(iii) Multivalued - dependency.

(iv) Group by clause in SQL.

(v) Advantages and disadvantages of a DBMS. (3.5 x 4 =14)