ALCCS

 

 

Code: CS33                                                 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.  List two major problems associated with updates performed using views.

 

             b.  Design a relational database for a university registrar’s office. The office maintains data about each class, including the instructor, the number of students enrolled, and the time and place of the class meetings. For each student–class pair, a grade is recorded.

 

             c.  Let R = (A, B, C), and let r1 and r2 both be relations on schema R. Give an expression in SQL that is equivalent to each of the following queries: (a) r1  r2  (b)  ΠAB(r1) x ΠBC(r2).

 

             d. Explain condition-defined, user-defined constraints, disjoint and overlapping constraints with respect to generalization in an E-R diagram.

 

             e. Differentiate between super key, candidate key, primary key and foreign key.

 

             f.   Differentiate between OLTP and OLAP systems.

 

             g. Explain what is meant by redundant information and loss of information. Explain why each of these properties may indicate a bad relational database design.

                                                                                                                                                (7  4)

 

  Q.2     a.  List the major characteristics of database approach. Also mention some disadvantages of it.     

 

             b. Describe the three level schema architecture. Why do we need mappings between       schema levels? How do different schema definition languages support this architecture?

 

             c.  A university registrar’s office maintains data about the following entities: (a) courses, including number, title, credits, syllabus, and prerequisites; (b) course offerings, including course number, year, semester, section number, instructor(s),timings, and classroom; (c) students, including student-id, name, and program; and (d) instructors, including identification number, name, department, and title. Further, the enrollment of students in courses and grades awarded to students in each course they are enrolled for must be appropriately modeled. Construct an E-R diagram for the registrar’s office. Mention all assumptions that you make while drawing the E-R diagram.                                                                                                        (6+6+6)

 

  Q.3     a.  Consider the following four relations :

 

employee (person-name, street, city)

works (person-name, company-name, salary)

company (company-name, city)

manages (person-name, manager-name)

            Answer the following queries in SQL:

1.      Find all the manager’s name along with their company names.

2.      Find names of employees whose salary is greater than the average salary of employees in their company.

3.      Find the names of all employees who live in the same city and the same street as their managers.

4.      Find the names of all employees who earn more than every employee of Small Bank Corporation.

            

             b.  Discuss the entity integrity and referential integrity constraints. What is their significance?                                     

 

             c.  When and why do you provide a null value to an attribute?                                  (12+4+2)

 

  Q.4     a.  Suppose that we decompose the schema R = (A, B, C, D, E) into                                         

R1 (A, B, C)

R2 (A, D, E)

                  Is the decomposition of R into R1 and R2 dependency preserving for the following set F of functional dependencies? If not, decompose it into dependency preserving relations: Justify your answers? 

  A BC

CD E

B D

E A

                 

             b.  Prove or disprove the following inference rules for functional dependencies. A proof    can be made either by a proof argument or by using inference rules IR1 through IR3. A disproof should be done by demonstrating a relation instance that satisfies the conditions   and functional dependencies in the left hand side of the inference rule but do not satisfy the conditions or dependencies in the right hand side.

1.      {A  C, B  D} |= {AB  C }

2.      { B  C, B  A, AC  D} |= {B  D}

3.      {B  D, C  D} |= {B  C}

where the symbol |= stands for “logically infers”

 

   c.  Compute the closure of the following set F of functional dependencies for relation                      schema R = (P, Q, R, S, T).

P →QR

RS →T

Q→ S

T→ P

List the candidate keys for R.                                                                               (6+6+6)             

  Q.5     a.  Discuss the lost update, dirty read and incorrect summary problems associated with transactions.    

 

             b.  What is a timestamp? How does the system generate timestamps? Discuss the timestamp ordering protocol for concurrency control.                                                                                                         

 

       c.   Stable storage cannot be implemented.                                                                                                                     (i)   Explain why it cannot be.

      (ii)  Explain how database systems deal with this problem.                                

                                                                                                                                                          

             d. Assume that immediate modification is used in a system. Show, by an example, how an inconsistent database state could result if log records for a transaction are not output to stable storage prior to data updated by the transaction being written to disk.                                                                       (6+6+3+3)

       

  Q.6     a.  Define a data warehouse. Explain the characteristics or features of a data warehouse.        

 

             b.  Explain knowledge discovery in databases. Discuss the role of data mining in it.                     

 

             c.  Define distributed database. Differentiate between distributed and parallel databases. Also mention some advantages of distributed databases.                                                                     (6+6+6)

       

  Q.7         Write Short notes on any THREE of the following:-                                                       

                                                                                                                                                           

(i)            Lossless join decomposition and dependency preservation.

(ii)          Multivalued dependency and join dependency.

(iii)         ACID properties of Transactions

                  (iv)    Strict and conservative two phase locking protocol.

                   (v)    Database security.                                                                              (6+6+6)