Database Management Systems - May 2016

Computer Science Engg. (Semester 5)

TOTAL MARKS: 100
TOTAL TIME: 3 HOURS (1) Question 1 is compulsory.
(2) Attempt any four from the remaining questions.
(3) Assume data wherever required.
(4) Figures to the right indicate full marks. 1(a) What does defining, manipulating and sharing of a database mean?(6 marks) 1(b) Discuss the main characteristics of the database approach and how it differs from traditional file systems.(8 marks) 1(c) Describe the three ' schema architecture. What is the difference between logical and physical data Independence?(6 marks) 2(a) Differentiate the following :
i) Entity and Attribute ii) Entity type and Entity set iii) Strong and weak Entity iv) Recursive relationship and Indentifying relationship.(8 marks) 2(b) A database is being to keep track of the teams and games of a sports league. A team has a number of players, not all of whom participate in each game. It is desired to keep track of the players participating in each game team, the position they played they played in that game and the result of the game. Design an ER diagram for this application, starting any assumptions you make. Choose your favourite sport (e.g Cricket, Base ball, Football).(12 marks) 3(a) Explain the entity integrity and referential integrity constraints. Why each is considered important?(6 marks) 3(b) Briefly discuss the different types of update operations on a relation.(6 marks) 3(b) Briefly the entity integrity and referential integrity constraints. Why each is considered important?(6 marks) 3(c) Consider the following relational schema.
Emp (eid, ename, age, sal)
Works_fol (eid, pid, #hrs)
Proj (pid, pname)
Write the Queries in Relational algebra for the following:
i) Retrieve Employee ID and Name of the employees who work for all the projects.
ii) Retrieve Name and Age of employees whose salary > 10000.
iii) For each employee, het the number of projects and number of hours worked projects.
iv) Retrieve Names of employees working on 'CSE' project.(8 marks) 4(a) Give the complete syntax of Select statement in SQL and discuss all the classes with examples.(5 marks) 4(b) What are Aggregate functions in SQL? Explain with example.(5 marks) 4(c) Consider the following Relational schema:
Lives (Name, Street, City)
Works (Name, Cname, Salary)
Located (Cname, City)
Manager (Name =, MGR_Name)
Write the Queries in SQL for the following:
i) Find the people who earn more than every employee of 'Canara Bank'.
ii) Find the company employing the most people.
iii) Find the Name and city of all the people who work for 'SBI' and earn more than 55000 rupees.
iv) Show 20% raise in salary of all managers.
v) Find the companies located in every city in which 'ICICI' is located.(10 marks) 5(a) Explain Insert, Delete and Update statements in SQL with examples.(6 marks) 5(b) What are views in SQL? Show how views are specified in SQL. List the advantages of views.(6 marks) 5(c) Differentiate: i) Trigger and Assertion ii) Embedded and Dynamic SQL iii) Outer join and Self Join iv) Super key and Candidate key.(8 marks) 6(a) What are the informal guidelines for reaction schema? Explain.(6 marks) 6(b) Define 1NF, 2NF and 3NF? Prove that the relation with two attributes is always in BCNF.(8 marks) 7(a) What do you mean by MVD? When does it arise? Define 4NF.(5 marks) 7(b) Define Join Dependency and 5NF.(5 marks) 7(c) Let R = {SSN, Ename, Pnumber, Pname, Plocn, Hrs}
and D = {R₁, R₂, R₃} where
R₁ = Emp = {SSN, Ename}
R₂ = Proj = {Pnumber, Pname, Hrs}
R₃ = Work_ON = {SSN, Pnumber, Hrs}
The following dependencies hold on relation R.
F = {SSN → Ename;
Pnumber → Pname, Plocn};
{SSN, Pnumber} → Hrs}.
Prove that above decomposition of relation 'R' has the lossless join property.(10 marks) 8(a) What are the problems with concurrency? Explain each with an example.(6 marks) 8(b) With a neat state transition diagram, discuss the different states of a transaction.(6 marks) 8(c) Write and explain the Two-phase locking protocol for concurrency control.(8 marks)