ALCCS   -   NEW SCHEME

 

Code: CT13                                                 Subject: DATABASE MANAGEMENT SYSTEMS

Flowchart: Alternate Process: SEPTEMBER 2010Time: 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 Normalization. Mention the problems addressed by normalization.

 

             b.  List the major responsibilities of a general purpose Database Manager (DBM).  What problems may arise if these responsibilities are not met by DBM?

       

             c.  Let the following relation schemas be given:

                  R = (X, Y, Z)

                  Give an expression in the tuple and domain relational calculus that is equivalent to ΠX(r).

 

             d.  Define the following: - Attribute, Tuple, Relation and View.

                 

             e.  Consider the following set F of functional dependencies: F = {S → {E, B, A, D}, D → {DN, DM}}. Compute the closures {S}+  and {D}+ with respect to F.

       

             f.   Discuss strong entity set and a weak entity set with the help of an example.

 

             g. Compare and contrast the database management system with the traditional file based system.          (7  4)

 

Q.2       a.  Discuss Domain Integrity Constraint, Key Integrity Constraint, Entity Integrity Constraint and Referential Integrity Constraint.

 

             b.  Design a generalization–specialization hierarchy for a motor-vehicle sales company. The company sells motorcycles, passenger cars, vans, and buses. Justify your placement of attributes at each level of the hierarchy. Explain why they should not be placed at a higher or lower level?

 

             c.  Differentiate between the following:

                  (i)    Simple (Atomic) and Composite Attributes.

                  (ii)   Single-valued and multi-valued attributes.

                  (iii)  Stored and Derived Attributes.                                                                        (6 3)

 

  Q.3     a.  Discuss the mapping of the following into relational database schema.

                  (i)    Aggregation

                  (ii)   Generalization / Specialization

                  (iii)  Strong / weak entity sets

                  (iv)   Ternary Relationship

                 

       

             b.  Consider the following LIBRARY relational schema

                  Book (Book_id, Title, Publisher_name)

                  Book_Authors (Book_id, Author_name)

                  Publisher (Name, Address, Phone)

                  Book_copies (Book_id, Branch_id, No_of_copies)

                  Book_loans (Book_id, Branch_id, Card_no, Date_out, Due_date)

                  Library_branch (Branch_id, Branch_name, Address)

                  Borrower (Card_no, Name, Address, Phone)

                 

                  Write the relational algebra expression for the following queries:

                  (i)   Find the number of copies of the book titled TLT owned by the library branch whose name is "S"?

                  (ii)   Find the number of copies of the book titled TLT owned by each library branch?

                  (iii)  Find the names of all borrowers who do not have any books checked out.

                  (iv) Find the book title, the borrower's name, and the borrower's address for each book that is loaned out from branch named "S" and whose DueDate is today.

                  (v)  For each library branch, find the branch name and the total number of books loaned out from that branch.                                                                                                                              (8+10)

       

  Q.4     a.  Consider the following two sets of functional dependencies:

                  F1= {X→Z, XZ →P, Q→XP, Q→R} and F2 = {X →ZP, Q→XR}. Check whether they are equivalent or not equivalent.                                                                                                                  

            

             b.  Prove that any relation schema with two attributes is in BCNF.

 

             c.  Suppose that we decompose the schema A = (P, Q, R, S, T) into

(P, Q, R)

                                                                       (P, S, T)

                  Compute Q+ and show that this decomposition is lossless-join decomposition if the following set F of functional dependencies holds:                                                                                              (6+6+6)

    P →QR

                                                                    RS →T

Q→ S

T→ P

 

  Q.5     a.  Consider the following relation schema for a COMPANY database                                                Employee (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)

                  Department (Dname, Dnumber, Mgr_ssn, Mgr_start_date)

                  Dept_locations (Dnumber, Dlocation)

                  Project (Pname, Pnumber, Plocation, Dnum)

                  Works_on (Ssn, Pno, Hours)

                  Dependent (Ssn, Dependent_name, Sex, Bdate, Relationship)

                  Write a query in SQL for the following:

                  (i)   Find the names of employees in department 5 who work more than 10 hours per week on the project named 'ProductX'.

                  (ii)   Find the names of employees who have a dependent with the name same as the first name of that employee.

                  (iii)  Find the names of employees who are supervised by employee with first name 'Franklin’ and last name ‘Wong'.

                  (iv) For each project, find the project names and the total hours per week (spent by all employees) on the project.

                  (v)   Find the names of employees who work on every project.

                  (vi)  For each department, Find the department name and the average salary of employees working in that department.                                                                                                                      

                   

             b.  Consider the following relation:

                  CAR_SALE (Car#, Date_sold, Salesman#, Commission %, Discount_amt) with {CAR#, SALESMAN#} as the primary key. Let Date_sold →Discount_amt and Salesman# → commission% Is the relation CAR_SALE in 1NF, 2NF, or 3NF? Justify?  Normalize the relation CAR_SALE to BCNF relation. (12+6)

       

  Q.6     a.  What is the two-phase locking protocol? Discuss strict and conservative two-phase locking protocol?  Which of them is preferred and why?                                                                                               

 

             b.  Discuss why during system recovery from crash; the log records for transactions on the undo list must be processed in the backward direction and log records for transactions on the redo list are processed in the forward direction.

 

             c.  Define conflict serializability and view serialiability of schedules.  Determine whether the following schedule is conflict serializable or not:

                  S1: r2(Z), r2(Y), w2(Y); r3(Y), r3(Z); r1(X), w1(X); w3(Y), w3(Z); r2(X); r1(Y), w1(Y); w2(X);           (6+6+6)

 

  Q.7     a.  Write a short note on any THREE of the following                                                           

 

                  (i)    Heuristics based optimization

                  (ii)   Granularity of Data items

                  (iii)  Distributed databases

                  (iv)  Web databases                                                                                                 (63)