Class: TE(comp) Subject: Database Management System Question Bank UNIT-I (Introduction to DBMS )

1) What is difference between specialization and generalization? Why do we not display this

difference in schema diagrams?

2) A bank has many branches and a large number of customers.A customer can open different

kinds of account with the bank. The bank keeps track of a customers by his SSN,name,address and ph_no. Age is used as a factor to check wheather heis a major or minor. There are different types of loans,each identified by loan number. A customer can take out more than one type of loan and all branches can give loans. Loans have a duration and interest rate. The account holder can enquire about the balance in his account. Draw an ER diagram and schema definition for the bank. 8 3) Explain four significant differences between file-processing system and DBMS. 4 4) What are the enhancements that distinguish the EER model from ER model? Explain with example. 6 5) Explain the distinction between condition defined and user defined constraints. Which of these constraints can the system check automatically ? Explain your answer. 6 6) Specify the CODDs norms to be specified by RDBMS. 7) With the help of neat diagram,explain database system structure and describeits various components. 8) Explain in details a. Specialization and generalization in an ER model b. Different level of data abstraction 9) What is meant by mapping cardinality? For a binary relationship set what are the possible mapping cardinality? Explain with diagrams. 6 10) Explain the distinction between following a) Condition defiend and user defined design constrints.

b) Disjoint and overlapping design constraints. 11) What are superclass and subclass entity types? Explain 4 12) What do you mean by connectivity,cardinality and dependency of a relationship? 4 13) Discuss the entity integrity and referential integrity constrints. 14) Explain function of DBA 15) Consider a two-dimensional integer array of size M*N that is to be used in C programming language.Using the array as an example illustrate difference between the three levels of data abstraction. 16) Explain the difference between physical and logical data independence, 17) Explain the difference between the two-tier and three-tier clirnt/server architecture. 18) Explain with an example how ER diagrams are converted into tables.

UNIT-II (Relational Algebra & SQL) 1) Write short notes on: 1)stored procedures and triggers 2)Dynamic and embedded sql. 2) Consider the relational database Emp(e_name,street_city) Works(e_name,company_name,salary) Company(company_name,city) Managers(e_name,mgr_name) i) ii) Give an sql DDl definition of this database.Identify referential-integrity constraints that should hold,and include them in the DDL definition. Write the SQL expression for the following queries 1)find the company with the most employees 2)find the company with the smallest payroll. 3) Explain with example the concept of referential integrity. Also discuss the situation when referential integrity constraints is getting violated by insert, delete and update operation on the relation.

5)

Comment on safety of expression.

6) What is a view in SQL, and how it is defined? Discuss the problems that may arise when one attempts to update a view. How are views typically implemented.Explain with example. 7) Describe the concept of cursor and how it is used in embedded SQL.Explain in detail 8 8) Explain general syntax of SQL query? Let the following relation schemas be given: R=(A,B,C) S=(D,E,F) Let relation r(R) ans s(S) be given. Give an expression in SQL that is equivalent to each of the following queries: 9) Explain the concept of embedded SQL and dynamic SQL. Describe the circumstances in which you would choose to use embedded SQL rather than SQL alone or only a general purpose programming language. 10) The outer-join operation extend the natural join operation so that tuples from the participating relations are not lost in the result of the join .How the theta join operation can be extended so that tuples from left, right or both relation are not lost from the result of a theta join? Explain. 8 11) explain the concept of cursor and triggers. 4

12) Suppose there are two relation r and s such that the foreign kay B of r references the primary key A of s. describe how the trigger mechanism can be used to implement the on delete cascade option,when a tuple is deleted from s.

UNIT-III (Relational Database Design) 1)Specify Armstrongs axioms. Use Armstrongs axioms to prove the soundness od the psedotransitivity r ule. 8 2)What is decomposition ? Suppose that we decompose the schema R=(A,B,C,D,E) into (A,B,C) And (A,D,E) ,show that this decomposition is a lossless decomposition if the following set F of functional dependencies holds: A-->BC,CD-->E,B-->D,E-->A 8

3)For relation schema R=(A,B,C,D,E),compute the closure f+ and canonical cover Fc of the following set F of functional dependencies (ABC,CDE,BD,EA). 8

4)Consider the relation schema R(A B C ) with FDs ABC and CA Show that the schema R is in 3NF but not in BCNF. Also determine the minimal keys for R. 8 5)Compute closure of the following set F of functional dependencies for the relation schema R=(A,B,C,D,E) F: (ABC,CDE,BD,EA) list candidate keys of R. 6

6)write short note on normalization.

7)which are different fact finding techniques? State advantages and disadvantages of each. 6 8) Show that ,if a relation schema is in BCNF ,then it is also in 3NF. 6

9) use the axioms for functional and multivalued dependencies to show that each of the following rules is sound: i) The intersect rule ii) The difference rule 4

10) Consider a relation R with attributes ABCDE.You are given following dependencies: AB,BCE,EDA i)List all keys for R ii)Is R in 3NF. 8

11) Explain why 4NF is more desirable that BCNF. Rewrite the definition of 4NF and BCNF using the notions of domain constraints and general constraints. 8 12)Let R=(A,B,C,D,E) and let M be the following set of multivalved dependencies. ABC,BCD,EAD List the non trivial dependencies in M+. 8

13) A set of FDs for the relation R{A,B,C,D,E} is ABC,CA,BCD,ACDB,BEC,ECFA,CFBD,DE. Find a minimum cover for this set of FDs. 6 14) Describe the concept of transitive dependency and explain hoe this concept is used to define 3NF. 6 15)what do u understand by physical database design ?What are important factors that influence physical database design? 4 16) Define 1st.2nd and 3rd normal forms when only primary keys are considered. How do the general definitions of 2NF and 3NF,which consider all keys of relation, differ from those that consider only primary keys? 6 17) What are the activities related to database application system life cycle. 4

UNIT-IV (File Systems & Query Processing) 1)Explain Hash join algorithm. 8 8 8

2)Explain Insertion operation on B+ trees with suitable example 3)Differentiate between static and dynamic hashing.

4)Give an example of a database application in which the reserved-space method of representing variable-length records is preferable to the preferable to the pointer method. Explain your answer. 8 5)What are the advantages and disadvantages of hash indices relative to B+ tree indices> How might the type of index available influence the choice of a query-processing strategy? 8 6) List the advantages and disadvantages of each of the following strategies for storing a relational databases: i)Store each relational in one file ii)Store multiple relational in one file. 8

7)What is ordered indices> Explain the types of ordered indices with suitable example.. 8 8)Construct a B+ tree for the following set of key values. (2,3,5,7,11,17,19,23,29,31) Assume order of tree is 4. 8 9)Describe the hash join technique to implement a natural join 4

10) How can we estimate a cost of query? 4 11) Explain basic steps involved in query processing. What is the role of relational algebra in query processing? 8 12)Explain the structure of B+ tree with a suitable example and neat diagram. How does it implement dynamic multilevel index? 8 13)What are the various techniques to handle variable length records? Explain any one detail. 8

UNIT-V (Transaction Management) 1) Compare the deferred and immediate modification version of the log based recovery scheme in terms of ease of implementation and overhead cost. 2) Explain two-phase locking protocol. How does it ensure serializability? 3) what is serializable schedule? Describe with suitable example the type of serializable schedules. Also example significance of precedence graph. 4) When do deadlocks happen? How to prevent them, and how to recover if deadlock takes place. 5)Explain the concept of transaction. Describe ACID properties for transaction. 6) Explain deferred database modification and immediate database medication and their difference in the context of recovery. 7)why concurrent execution or transactions is desirable? support your answer with example. 8) explain time stamp based concurrency control. 4 9) differentiate between serial and serializable schedule. 2 10) explain two phase locking protocol. How does it ensures serializability 11)When do deadlocks happen, how to prevent them, and how to recover if deadlock takes place?

12)Explain Thomas write rule with an example.

13)Explain testing strategy for conflict serializability and view serializability with suitable example.

14)Show by example that there are schedules possible under the tree protocol that are not possible under the two-phase locking protocol, and vice versa.

15)State ACID properties of transaction. Explan implementation of any two of them.

13)Explain recoverable and cascadeless schedules.

14)Write an algorithm to find a cycle in the precedence graph.

15)Explain the purpose of the check point mechanism. How does the frequency of check points affect. i)System performance when no failure occurs. ii)The time it taken to recover from a system crash. iii)The time it taken to recover from a disk crash.

UNIT-VI (Object-Oriented Databases and Database Architectures) 1)Explain how object are stored in the relational databases. 8 2)Specify advantages and disadvantages of distributed system. 8 3)Explain in detail ODMG language constructs for object definition and object manipulation. 8 4)Explain the various issues that decide the time cost of communication between client and server 8 5)What is the difference between persistence programming language and traditional programming languages? 6 6)Explain : Object identity and pointers. 7) Specify advantages and disadvantages of distributed system. 6

8)what is multiple inheritance ? Explain why ambiguity potentions exists with multiple inheritance .Give suitable example. 6 9)State which database system architectures you will prefer for following applications. Support your answer with brief explanation. i)Banking system ii)Airline-reservation system 6 6

10)explain different pointer swizzling techniques.

11) differentiate between persistent and transient object. How persistence is handled in a typical object oriented databases? 8 12) State and explain the advantages of distributed database systems. 13)Explain the need for Backup and Replication. 4 6

14) Explain in detail ODMG language constructs for the object definition and object manipulation. 8 15) How does the concept of an object in the object oriented model differ from the concept of an entity in the ER model. 6 16) Explain how persistent pointer is implemented. 17)explain in brief what is object containment. 2 18)explain why ambiguity potentially exists with multiple inheritance, illustrate with suitable example. 8 19) Explain need for 1)backup ii)Replication. 4 4