Data Base Management Systems–2012 Question Bank–All Units


Department Of Computer science & Engineering

Sub Code/Name: CS2255/Data Base Management Systems

Question Bank 2012 Edition


UNIT-I INTRODUCTION

PART A

1. Define database management system.

2. Compare database systems with file systems.

3. Compare DBMS versus object oriented DBMS.

4. List Five Responsibilities of the DB manager.

5. Give the limitations of E-R model? How do you overcome this?

6. Give the reasons why null values might be introduced into the database.

7. Define Data independence.

8. With an example explain a weak entity in an ER diagram.

9. What is logical data independence?

10. What are the disadvantages of file processing system?

11. What are the advantages of using DBMS?

12. Give the levels of data abstraction.

13. Define Instance and Schema.

14. List the data structures implemented by the storage manager.

15. Define single valued and multi valued attributes.

16. What are stored and derived attributes?

17. Define weak and strong entity sets.

18. Define two types of participation constraint.

19. Distinguish between primary key and candidate key.

20. List any eight applications of DBMS.

21. Give the levels of data abstraction.

22. Define Data dictionary.

23. Define the terms: 1) entity set. 2) Relational set.

24. What is Meta data? List any two advantages of meta data

25. Write short notes on schema diagram.

26. What is conceptual schema?

27. Write short notes on relational model.

28. Define data model.

29. Define DDL and DML.


PART-B

1. (i) Describe the system structure of database system with neat block diagram. (ii) List out the functions of DBA

2. (i) Illustrate the issues to be considered while developing an ER-diagram. (ii) Compare file system with database system

3. (i) Construct an ER-diagram for hospital with a set of patients and a set of medical doctors.

Associate with each patient a log of the various tests and examinations conducted. (ii) How data models are categorized?

4. (i) Explain ER model in detail.

(ii) Draw an ER diagram for a banking enterprise

5. (i) Draw an ER diagram for Airline Schema

(ii) Draw an ER diagram for Library Management System

6. Explain the design issues of ER-model.

7. Explain the architecture of DBMS.

8. (i) What are the steps involved in designing a database application? Explain with an application.

(ii) List out possible types of relations that may exist between two entities. How would you realize that into tables for a binary relation?

9. (i) Construct an ER diagram for a car insurance company that has a set of customers, each of whom owns one/more cars. Each car has associated with it zero to any number of recorded accidents.

10. (ii) Construct appropriate tables for the above ER diagram.

11. Define data model. Explain the different types of data models with relevant examples.

12. Draw the ER-diagram to design a system for a Publishing Company that produces scientific books on various subjects. The books are written by authors who specialize in one particular subject. The company employs editors who not necessarily being specialists in a particular area, each take sole responsibility for editing one or more publications. A publication covers essentially one of the specialist subjects and is normally written by a single author. When writing a particular book, each author works with editor, but may submit another work for publication to be supervised by other editors. To improve their competitiveness, the company tries to employ a variety of authors, more than one author being a specialist in a particular subject.


UNIT-II

RELATIONAL MODEL


PART-A

1. Give the distinction between primary key, candidate key and super key.

2. Write a SQL statement to find the names and loan numbers of all customers who have a loan at Chennai branch.

3. Define Query language. Give the classification of the query language.

4. Name the different types of joins supported in SQL.

5. What is static SQL? How does it differ from dynamic SQL?

6. What are the different types of integrity constraints used in designing a relational database?

7. Distinguish between primary key and candidate key

8. With an example explain referential integrity.

9. What is domain integrity? Give example.

10. Write the Tuple relational calculus expression to find the number of employees working in Sales department in the given relation Employee.

Employee (SSN-No, Name, Department)

11. Define triggers.

12. What are referential integrity constraints?

13. What is assertion? Mention the forms available.

14. Write short notes on tuple relational calculus.

15. Write short notes on domain relational calculus.

16. Define and give the syntax for SELECT and PROJECT operation.

17. What is distributed database system?

18. Define the terms: 1) Replication. 2) Fragmentation.

19. Differentiate horizontal and vertical fragmentation.

20. What is the use of ‘group by’ and ‘with’ clause?

21. List the SQL domain types.

22. Write the Tuple relational calculus expression to find the number of employees working in Sales department in the given relation Employee.

Employee (SSN-No, Name, Department)

23. What is the use of union and intersection operation?

24. Define tuple variable.

25. What are aggregate functions? And list the aggregate functions supported by SQL.

26. Give the general form of SQL query.

27. Define Views.


PART-B


1.(i) Consider the employee database, where the primary keys are underlined.

Employee (empname , street, city)

Works (empname, companyname, salary) Company (companyname, city)

Manages (empname, managername)

1. Find the names of all the employees who work for First Bank Corporation.

2. Find the names, street addresses, and cities of residence of all employees who work for First Bank Corporation and earn more than 200000 per annum.

3. Find the names of all employees in this database who live in the same city as the companies for which they work.

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


(ii) Discuss the strengths and weaknesses of the trigger mechanism. Compare triggers with other integrity constraints supported by SQL.


2. (i) Discuss on various relational algebra operators with suitable example.

(ii) What are the relational algebra operations supported is SQL? Write the SQL statement for each operation

3. (i) Discuss about triggers. How do triggers offer a powerful mechanism for dealing with the changes to database with suitable example.

(ii) What are nested queries? Explain with example.

4. (i) Illustrate the issues to implement distributed database. (ii) Explain embedded SQL

5. (i) Write short notes on distributed databases

(ii) Discuss about the evolution of distributed database. Compare with client/server mode.

6. With relevant examples discuss the following in SQL. (i) Data Definition Language.

(ii) Data Manipulation Language

(iii) Data Control Language

(iv) Views

7. Consider the relational table given below and answer the following SQL queries.

Employee (SSN-No, Name, Department, Salary)

(i) List all the employees whose name starts with the letter 'L'.

(ii) Find the maximum salary given to employees in each department. (iii) Find the number of employees working in 'accounts' department. (iv) Find the second maximum salary from the table.

(v) Find the employee who is getting the minimum salary.


UNIT-III

DATABASE DESIGN


PART-A

1. What is multivalued dependency?

2. When is a functional dependency said to be trivial?

3. State the various pitfalls in relational database.

4. Define functional dependency.

5. Why it is necessary to decompose a relation?

6. What is multivalued dependency?

7. Define Boyce Codd normal form.

8. What is first normal form?

9. What are the uses of functional dependencies?

10. What are axioms?

11. Explain trivial dependency.

12. What is meant by normalization of data?

13. What is meant by computing the closure of a set of functional dependency?

14. What is 2NF?

15. Explain the desirable properties of decomposition.

16. Differentiate 3NF and BCNF.

17. When is a functional dependency said to be trivial?

PART-B

1. (i) What is normalization? Explain the various normalization techniques with suitable example.

(ii)Give the comparison between BCNF and 3 NF

2. (i) Justify the need for normalization with examples.

(ii) What is normalization? Explain 1NF, 2NF, 3NF and BCNF with simple example.

3. Explain different normal forms in detail.

4. What is normalization? Explain normalization techniques using functional dependencies with example.

5. (i) What is FD? Explain the role of FD in the process of normalization. (ii) Explain functional dependency.

6. Explain briefly about Armstrong rules on functional dependency and write the algorithm to compute functional dependency closure.


UNIT-IV

TRANSACTIONS


PART-A

1. Give the ACID properties.

2. What is shadow paging?

3. State the benefits of strict two-phase locking.

4. What benefits is provided by strict two-phase locking? What are the disadvantages results?

5. What is transaction?

6. What are the properties of transaction?

7. What is recovery management component?

8. When is a transaction roll backed?

9. What are the states of transaction?

10. Give the reasons for allowing concurrency?

11. What are the types of serializability?

12. Define lock.

13. What are the two modes of lock?

14. Define dead lock.

15. Define the phases of two phase locking protocol.

16. What are the two methods for deadlock problem?

17. What is a recovery scheme?

18. What is meant by log-based recovery?

19. Differentiate strict two phase locking protocol and rigorous two phase locking protocol.

20. What are the facilities available in SQL for Database recovery?


PART B

1. (i) What is concurrency control? How it is implemented in DBMS?

(ii) Explain various recovery techniques during transaction in detail.

2. (i) Explain the four important properties of transaction that a DBMS must ensure. (ii) Write notes on conflict and view serializability.

3.(i) Describe about the testing of serializability. (ii) Discuss on two phase locking protocol.

4.(i) Explain the deferred and immediate modification versions of the log based protocol. (ii) Write short notes on shadow paging.

6. (i) How deadlocks can be detected and recovered. (ii) Explain ACID properties.

7. Explain two phase locking in detail.

8.Explain log based recovery in detail.

9.Explain briefly about the working of two phase locking protocol using a sample transaction.

10. (i) When is a transaction said to be deadlocked? Explain the deadlock prevention methods with an example .

(ii) Explain concurrency control mechanisms. Discuss the need with example.


UNIT-V

IMPLEMENTATION TECHNIQUES


PART-A

1. Distinguish between dense and sparse indices.

2. What are the two types of ordered indices?

3. Give any two advantages of sparse index over dense index.

4. Give the measures of quality of a disk.

5. Compare sequential access devices versus random access devices with an example.

6. What are the types of storage devices?

7. Define access time.

8. Define seek time.

9. Define average seek time.

10. What is meant by mean time to failure?

11. What is the use of RAID?

12. What is called bit-level stripping?

13. What is called block-level stripping?

14. What are the factors taken into account when choosing a RAID level?

15. Which level of RAID is best? Why?

16. What is known as heap file organization?

17. What is hashing file organization?

18. What is stripping and mirroring?

19. Differentiate static and dynamic hashing.


PART-B

1. Describe about RAID levels.

2. Explain why allocations of records to blocks affects database system performance significantly.

3. Describe the structure of B+ tree and list the characteristics of a B+ tree

4. Give the comparison between ordered indexing and hashing

5. Explain the different properties of indexes in detail

6. Explain various hashing techniques?

7. What is RAID? List the different levels in RAID technology and explains its features.

8. Explain following with relevant examples: (i) B tree

(ii) B+ tree

(iii) Static and dynamic hashing

9. With a relevant example discuss the steps involved in processing a query.

10. (i)Explain briefly about query processing with examples to perform sort and join operation.

(ii) Discuss the approaches of database tuning.

11. Explain ten various hashing techniques in detail with suitable example.

12. (i)Differentiate primary index and secondary index. (ii)Write about hash functions.

13. Explain File organization in detail.