ALCCS

Code: CS33                                                 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. List any four advantages of database management system over file-processing system?

b. Discuss the different levels of Abstraction in Database Management Systems?

             c.  Define Data Independence?  How is it different from program-data-independence? 

             d.  Differentiate between primary key, candidate key, super key and foreign key?

             e.  Define Domain, Key, Entity and Referential Integrity constraint?

             f.   Discuss the different types of cardinality ratios in an E-R diagram?  Give examples.

             g. Define a schedule, a serial schedule, a recoverable schedule and a cascadeless schedule? (7  4)

Q.2            For the following problem definition:

The musical company wants to store information about the musicians who perform on its albums. Each musician has a musician id, a name, an address, and a phone number. Some musicians may have the same address and some of them may have more then one phone number. Each musician may play several instruments and an instrument and may be played by several musicians. Each instrument has name and a musical key. The album recorded has a title, a copyright date, a format and an album identifier. Each album has a number of songs where a song has a title and an author. Each song may be performed by several musicians and a musician may perform a number of songs. One of the musicians of the song acts as a producer. A producer may produce several albums.

                  (i)   Draw an E-R diagram.

                  (ii)  Transform the E-R diagram to a Relational Schema.                                        (10 + 8)

Q.3     a.  Define relational completeness? List five basic relational algebra operations. Why are they termed as basic operations? Express natural join between two relations R1(A, B, C) and R2(B,C, D) in terms of basic relational algebra operations.

             b.  Consider the following relational database schema

                  ITEM (Item_No, Item_Name, Item_Type, Item_Price, Item_Stock)

                  CUSTOMER(Customer_No, Customer_Name, Customer_Job, Customer_Address)

                  BILL(Bill_No, Bill_Date, Customer_No, Item_No, Quantity)

                  Express the following queries in Relational Algebra

(i) Find the customer names who have the same job as the customer named John.

(ii) Find the customer names who have bought atleast two items.

(iii) Find the item names bought by customer whose job is Engineer.

(iv) Find the customer names who have bought all toy type items.                                                 (6 + 12)

  Q.4     a.  For the following relational schema

Employee(EmpId, EmpName, Street, City)

                                    Works(EmpId, CompId, Salary)

                                    Company(CompId, CompName, City)

(i) Give an SQL DDL definition of this database.

(ii) Give an expression in SQL for each of the following queries.

(a) Find the names of employees whose salary is more than the average salary of all employees of their company.

(b) Find the names of companies that has the largest number of employees.

(c) Find names of employees whose salary is more than that of every employee of company named ‘ABCD’.

(d) Find names of companies located in the city in which company ‘ABCD’ is located.

(e) Give all employee a 20 percent raise in salary whose city is different from the city they work in.

(f) Delete the company that is paying the lowest salary to an employee. (3+15)

Q.5     a.  Define Normalization. Discuss with the help of an example how the update anomaly is caused due to bad relational database design.

             b.  State and compare 3NF and BCNF.

c.  Consider the relation schema R(P, Q, R, S, T) and the functional dependencies 

                  PQ ® R, R ® S and  S ®T holding on R. Decompose the relation R, if necessary, into collections of relations that are in BCNF.

             d.  Verify whether the decomposition R={P, Q, R, S, T} into R1={P, Q, R} and R2={P, S, T} is Lossless or Lossy for the given set of  functional dependencies F={P ® QR, RS ® T, Q ® S, T ® P). Also verify whether the decomposition is dependency preserving.

             e.  Consider relation schema R(P, Q, R, S) and the functional dependencies  PQ ®R,      R ®S and S ®P holds on R . List all the keys of relation R.                                                             (3+3+4+4+4)

Q.6     a.  Discuss the ACID properties of transactions in databases?

             b.  Define conflict serializability and a conflict serializable schedule? Determine whether the following schedule is conflict serializable or not

                  T1:R(X), T2:R(X), T1:W(X), T2:R(Y), T3:R(Z), T2:W(Z), T3:W(X), T3:W(Y), T2:W(Y), T3:W(Z)

             c.  Differentiate between Strict two-phase locking protocol and conservative two-phase locking protocol for concurrency control in databases with the help of an example?

             d.  Compare the deferred modification and immediate modification technique of the log-based recovery scheme for concurrent transactions? Why and how checkpoints are used to perform such log-based recovery.  (4+4+4+6)

Q.7          Write short notes on any THREE of the following

(i) Multivalued dependency and Fourth Normal Form

(ii) Join Dependency and Fifth Normal Form

(iii) Distributed Databases

(iv) OLTP and OLAP

(v) Database Security                                                                                                                (6+6+6)