ALCCS

 

 

Code: CS33                                                 Subject: DATABASE MANAGEMENT SYSTEMS

Flowchart: Alternate Process: MARCH 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.  Discuss the difference between database systems and file based systems.

 

             b.  Explain the difference between a weak and a strong entity set.

                                                                                                                                                                       

             c.  Define foreign key. Explain its significance? 

 

             d.  Explain the working of GROUP BY clause. What is the difference between the WHERE and HAVING clause in SQL?

 

             e.  Discuss the role of a Database Administrator.

 

             f.   Define data warehousing and data mining.

 

             g. List all the functional dependencies that holds true for the following relation                        

                 

A

B

C

 

(7  4)

Q.2       a.  Construct an E-R diagram for the following problem definition:

                  Each company operates four departments, and each department belongs to one company. Each department employs one or more employees, and each employee works for one department. Each of the employees may or may not have one or more dependants, and each dependant belongs to one employee.

 

             b.  What are the different types of constraints imposed on Generalization in an E-R diagram?

 

             c.  How would you map the following ER constructs into relations. Give suitable example?

                  (i)  Aggregation (ii)  Ternary relationship  (iii) Inheritance

                                                                                                                                           (6 3)                            

  Q.3     a.  What is an outer join. Discuss the different types of outer joins with the help of examples.

 

             b.  Consider the following relations for a database that keeps track of business trips of salespersons in a sales office:

                  SALESPERSON (SSN, Name, Start_Year, Dept_No)

                  TRIP (SSN, From_City, To_City, Departure_Date, Return_Date, Trip_ID)

                  EXPENSE (Trip_ID, Account#, Amount)

                  Specify the following queries in relational algebra:

(i)  Find the details (all attributes of TRIP relation) for trips whose expenses exceeds $2000.

(ii)  Find the SSN of salesman who took trips to ‘Honolulu’.

(iii)  Find the total trip expenses incurred by the salesman with SSN = ‘234-56-7890’.

 

             c.  Define a view? How is it different from a  table? Write the SQL syntax for creating a view. (5+9+4)

 

  Q.4     a.  Use Armstrong’s axioms to prove the soundness of the union, decomposition and pseudotransitivity rule with respect to a set of functional dependencies.

 

             b.  Consider the following relation for published books:

                  BOOK (Book_title, Authorname, Book_type, Listprice, Author_affil, Publisher)

                  Author_affil referes  to the affiliation of the author. Suppose the following dependencies exist:

                  Book_title -> Publisher, Book_type

                  Book_type -> Listprice

                  Author_name -> Author_affil

                  (i)   What normal form is the above relation is in? Justify?

                  (ii)  Normalize the above  relation to its highest normal form.

 

             c.  Consider the relation schema EMP_DEPT and the following set G of functional dependencies on EMP_DEPT:

                  G = {SSN -> {ENAME, BDATE,ADDRESS, DNUMBER} ,

                  DNUMBER ->{DNAME, DMGRSSN} }. Calculate the closures {SSN} + and {DNUMBER} + with respect to G.                                                                                                          (6 3)

 

  Q.5     a.  Discuss the different possible states of a transaction with the help of a diagram.                   

 

             b.  Consider the precedence graph of a schedule given below. Is the schedule conflict serializable? Justify?       

 
 

 

 

 

 

 

 

 

 

 

 


             c.  Explain the difference between volatile, non-volatile and stable storage in terms of I/O cost.

 

             d.  Compare the shadow-paging recovery scheme with the log-based recovery schemes in respect to ease of implementation and overhead cost.                                                                   (6+5+3+4)

 

  Q.6     a.  What is OLAP, ROLAP, MOLAP and HOLAP.                                                             

 

             b.  Discuss the key control measures that are used to provide security to data in databases.     

 

             c.  What is a fragment of a relation? What are the main types of fragments? Why is fragmentation used in distributed database design?                                                                                                              

 

             d.  Mention any three data mining techniques?                                                        (6+4+4+4)

       

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

 

                  (i)     Lossless Join decomposition and Dependency Preservation.

                  (ii)   Granularity of data items.

                  (iii)  Canonical Cover.

                  (iv)   3NF and BCNF.                                                                                           (6 3)