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. Relations produced from an E-R model will always be
(A) First normal form. (B) Second normal form.
(C) Third normal form. (D) Fourth normal form.
b. Manager salary details are hidden from the employee .This is
(A) Conceptual level data hiding.
(B) External level data hiding.
(C) Physical level data hiding.
(D) None of these.
c. Which of the following is true for network structure?
(A) It is a physical representation of the data.
(B) It allows many to many relationship.
(C) It is conceptually simple.
(D) It will be the dominant database of the future.
d. Which two files are used during operation of the DBMS?
(A) Query languages and utilities
(B) DML and query language
(C) Data dictionary and transaction log
(D) Data dictionary and query language
e. A list consists of last names, first names, addresses and pin codes. If all people in the list have the same last name and same pin code a useful key would be
(A) the pin code
(B) the last name
(C) the compound key first name and last name
(D) all of these
f. In b-tree the number of keys in each node is ____ than the number of its children.
(A) one less (B) same
(C) one more (D) half
g. The drawback of shadow paging technique are
(A) commit overhead (B) Data fragmentation
(C) Garbage collection (D) All of these
h. Which normal form is considered adequate for normal relational database design?
(A) 2NF (B) 5NF
(C) 4NF (D) 3NF
i. Which of the following addressing modes permits relocation without any change over in the code?
(A) indirect addressing (B) indexed addressing
(C) PC relative addressing (D) Base register addressing
j. In a multi-user database, if two users wish to update the same record at the same time, they are prevented from doing so by
(A) jamming (B) password
(C) documentation (D) record lock
Answer any FIVE Questions out of EIGHT Questions.
Each question carries 16 marks.
Q.2 a. Explain the steps for reduction of E-R model into relational model. (8)
b. Construct an E-R diagram for a car insurance company that has a set of customers, each of whom owns one or more cars. Each car has associated with it zero to any number of recorded accidents. (8)
Q.3. a. Consider the following relations (8)
SALESPERSON(SSN, Name, Start_Year, Dept_No)
TRIP(SSN, From_City, To_City, Departure_Date, Return_Date, Trip_ID)
EXPENSE(Trip_ID, Account#, Amount)
Write queries in relational algebra
(i) Give the details (all attributes to TRIP relation) for the trips that exceeded $2000 in expenses.
(ii) Print the SSN of salesman who took trips to ‘
(iii) Print the total trip expenses incurred by the salesman with SSN = ‘234-56-7890’.
b. How Relational Calculus is different from Relational Algebra? What do understand by TRC queries and DRC queries? (8)
Q.4 a. What is DDL? Make a list of commands with short description used in DDL. (8)
b. Consider the insurance database, where the primary keys are underlined.
person (ss#, name, address)
car (license, year, model)
accident (date, driver, damage-amount)
owns (ss#, license)
log (license, date, driver)
Construct the following SQL queries for this relational database.
(i) Find the total number of people whose cars were involved in accidents in 1989.
(ii) Find the number of accidents in which the cars belonging to “John Smith” were involved.
(iii) Add a new customer to the database.
(iv) Add a new accident record for the
Q.5 a. Explain Boyce-Codd Normal Form with example and also Compare BCNF and 3NF. (8)
b. What are the reasons of bucket overflow? Explain any two methods for solving this problem. (8)
Q.6 a. What is irreducible set of dependencies? Relation R with attributes A, B, C, D, and FDs,
A ® BC
B ® C
A ® B
AB ® C
AC ® D
compute an irreducible set of FDs that is equivalent to this given set. (8)
b. What is indexed sequential file organization? What are the applications of this organization? (8)
Q.7 a. What are the General Transformation Rules for Relational Algebra Operations? (5)
b. How does a query tree represent a relational algebra expression? (5)
c. Differentiate between static hashing and dynamic hashing. (6)
Q.8 a. What is the two-phase locking protocol? How does it guarantee serializability? (6)
b. Explain the lost update problem and dirty read problem. (6)
c. What are deadlocks? How can they be avoided? (4)
Q.9 Explain the following:
(i) Normalization.
(ii) Joins in relational algebra.
(iii) Role of a database administrator.
(iv) B+ tree index files. (4x4=16)