(d)_ Prove or disprove the following using inference rules :— @ (W>Y,X 32} [= (WK > Y} (ii) (K SY, X 3 W, WX 9 Z} |= {XK > 2}. ()_ Define and explain BCNF. How is it more strict than 3 normal form ? () Define the fourth normal form when is it violated. Why is it useful ? 4. Attempt any four parts of the following — _(5*4=20) (a) Describe the problem faced when concurrent transactions are executing in uncontrolled manner. Give an example and explain (b) Describe the UNDO/REDO and the UNDO/NO-REDO algorithms for recovery with immediate update. (©) Describe the shadow paging recovery technique. Under what circumstances does it not require a log ? (d)_ Test the serializability of the following schedule :-— @ 1,005 1,095 w,005 1,00; w,09 Gi) 1,005 1,005 W,005 7,005 w,00- (©) What is mixed fragmentation in distributed database design ? Explain in brief. (£) What is meant by data allocation in distributed database design ? Describe the criteria to allocate the database units over the various sites. 5. Attempt any two parts of the following — (10: (a) What is two-phase locking technique for concurrency control ? How does it guarantee serializability ° Explain. (b) Describe the essential conditions for the occurrence of deadlock. Discuss an algorithm to avoid the deadlock. (c) Describe granularity locking. How does granularity of data items affect the performance of concurrency control ? ECSH2/PUR-+10I1(Re) 4 $25 Printed Pages—4 ECS402 your Answer Book) B. Tech. (SEM. IV) THEORY EXAMINATION 2011-12 DATABASE MANAGEMENT SYSTEMS. Time : 3 Hours Total Marks : 100 Note :— (1) All questions are compulsory. (2) All questions carry equal marks. 1, Attempt any four parts of the following :— _(Sx4=20) (@) Outline the advantages of implementing DBMS in an organization. (b) Describe the three level architecture of DBMS and describe the significant of each level. (c) Describe the different types of database languages and their functions in database system. (d)_ Draw an ER diagram for an Institute having the entities faculty, students, department and classroom, assume suitable attributes of entities and relation among them. (©) Define and describe the concept of generalization and specialization. (f) How does a category differ from, a regular shared subclass ? Describe the use of category. 2. Attempt any ¢wo parts of the following :— _ (10*2=20) (a) Consider the following schema :— Suppliers (sid: integer, sname: string, address: string) Parts (pid: integer, pname: string, color: string) Catalog (sid: integer. pid: integer, cost: real) ‘The key attributes are underlined, and the domain of each attribute is given after the attribute name. The ECS402/PUR-41011(Re) 1 [Turn Over(b) © Catalog relation fists the prices charged for parts by Suppliers. Write the following queries in relational algebra -— (i) Find the sids and sname of suppliers who supply some red or green part. (ii) Find the sids of suppliers who supply some red part and some green part. (iii) .Find the sids of suppliers who supply every part. (iv) Find the pids of parts that are supplied by at least two different suppliers (v) Find the pids of the most expensive parts supplied by suppliers named Yosemite Sham. Consider the following relational schema -— Student(name, roll number, address, main) Admission(roll_ number, course, semester) Faculty(course, faculty, semester) Offering(branch, course) Assume suitable assumption if you need and write the following queries in SQL :-— (The name of students admitted in a particular course in a given semester. (ii) Students who have taken all courses offered by the faculty ‘Raghunanadan’ Gili) All the courses taken by student ‘Amit’, (iv) Name all the faculty who had taught student ‘Amit’. (v) Find the names of all students who are studying same courses. Consider the following relational schema. An employee ccan Work in more than one department; the pet time attribute ECS402/PUR-41011(Re) 2 of the works relation shows the percentage of time that a given employee works in a given department : Emp(eid: integer, ename: string, age: integer, salary: real) Works(eid: integer, did: integer, pct rime: integer) Dept(did: integer, budget: real, managerid: integer) The key attributes are underlined, and the domain of each attribute is given after the attribute name. Write the SQL statements required to create the above relations, including appropriate versions of all primary and foreign key integrity constraints and add the following constraints in schema: Employees must make a minimum salary of $1,000, Every manager must also be an employee. The total percentage of all appointments for an employee must be under 100%. A manager must always have a higher salary than any employee of department that he or she manages. ‘Attempt any four parts of the following :— _ (5x4=20) (a) Consider the relation r(X, Y, Z, W, P, Q) and the set of FDs F = {XY > W, XW > PB, PQ>Z, XY + Q} Determine whether the decomposition RAZ, P,Q, RG Y, Z, P,Q is lossy or lossless. (b) What is functional dependency ? Explain its role in database design. Describe the inference rules for functional dependencies. (©) Consider the following relation and set FDs :— 1(A, B, C, D, E, F) and set F = {A B, C> DF, AC > E, D > F} determine the key for relation. Normalize the relation upto 3% normal form and justify your answer. ECS402/PUR-41011(Re) 3 [Turn Over