ALCCS - NEW SCHEME
Code: CT13 Subject: DATABASE MANAGEMENT SYSTEMS
Time: 3
Hours
Max. Marks: 100
NOTE:
· Question 1 is compulsory and carries 28 marks. Answer any FOUR questions from the rest. Marks are indicated against each question.
· Parts of a question should be answered at the same place.
Q.1 a. Define a database, database schema, database instance and database model.
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 =17 (r)
(iii) r × s (iv) ΠA,F (σC =D(r × s))
c. Define primary key, candidate key, super key and foreign key.
d. Justify the following statements:
(i) Relation must have a key.
(ii) Weak entities do not have their own key attributes.
e. Justify the following statements:
(i) Every recoverable schedule is a cascadeless schedule
(ii) A relation in BCNF is also in 3NF
f. Write down the advantages and disadvantages of distributed databases.
g. Compute the closure of the following set F of functional dependencies for relation schema R = (A, B, C, D, E).
A →BC
CD →E
B→ D
E→ A
List the candidate keys for R. (7
4)
Q.2 For the following problem definition:
The book club has members. The book club sells books to its members. The members places orders for books, which the book club fulfils. Each order contains one or more than one books. The books are written by author(s). The publisher publishes the book. An author can write more than one book and a book can have more than one author. A book is published by a publisher, but a publisher publishes many books. A member can place more than one order. The member also can choose not to place an order. The book club sells many books.
(i) Draw an E-R diagram
(ii) Map the ER diagram to Relational model (9+9)
Q.3 a. Consider the given schemas and answer the following questions:
Branch = (Branch_name, assets, Branch_city)
Customer = (Customer_name, street, customer_city)
Deposit = (Branch_name, account_number, customer_name,balance)
Borrow = (Branch_name, loan_number, customer_name, amount)
Client = (Customer_name, banker_name)
(i) Write a relational algebra query that finds customers who have a balance over 1000.
(ii) Write a relational algebra query that finds the clients of banker Agassi and the city they live in.
(iii) Write a query in tuple calculus to find all customers who have a loan amount of more than 1200.
(iv) Write a query in domain calculus to find all customers who have a loan amount of more than 1200.
b. Discuss the following integrity constraints
(i) Domain (ii) Entity (iii) Referential integrity (12+6)
Q.4 a. Consider the following tables:
WORKS (Pname, Cname, salary)
LIVES (Pname, Street, City)
LOCATED (Cname,City)
MANAGER (Pname, Mname)
Write a query in SQL for the following:
(i) List the names of the people who work for the company Wipro along with the cities they live in.
(ii) Find the people who work for the company ‘Infosys’ having salary greater than Rs. 50000/-.
(iii) List the names of the people, along with the street and city addresses.
(iv) Find the persons whose salaries are more than that of all of the ‘oracle’ employees.
(v) Find the name of the persons who do not work in ‘infosys’.
(vi) Find the average salary of the employee in company named ‘accenture’.
(vii) Create a table for the above relations using SQL-DDL
(viii)Create a view consisting of the person name along with their manager name and company name (18)
Q.5 a. Discuss the anomalies due to insertion, updation and deletion in a relation that is not in 2NF. Illustrate with the help of an example.
b. Consider the F and G sets of functional dependencies where
F= {A ->C, AC ->D,E ->AD, E ->H} and G = {A ->CD, E ->AH}. Check whether they are equivalent or not.
c. Consider the relation R(A, B, C, D, E), and the set of functional dependencies:
F={A -> D, {A,B} -> C, D -> E}.
(i) Which of the following is a candidate key?
(i) {A}
(ii) {A,B}
(iii) {A,E}
(ii) Consider the decomposition of R into R1(A,B,C) and R2(A,D,E)}. Is this decomposition lossless? Justify? (6+6+3+3)
Q.6 a. Discuss wait-die and wound-wait protocols for deadlock prevention.
b. Distinguish deferred update and immediate update log based recovery techniques.
c. What
are the advantages and disadvantages of strict two-phase locking and
conservative two-phase locking protocol. (3
6)
Q.7 a. Write a short note on any THREE of the following
(i) Heuristics based optimization
(ii) Web databases
(iii) Distributed databases
(iv) Shadow Paging (3
6)