Database Management System

Question Bank

UNIT 1

1. Design an ER diagram for keeping track of the exploits of your favorite sports team. You should
store the matches played, the scores in each match, the players in each match, and individual player
statistics for each match. Summary statistics should be modeled as derived attributes.

3. Construct E-R diagram for a hospital with set of patients and set of medical doctors. Associate with
each patient to a log of the various tests and examinations conducted

2. What is data independence? Differentiate between physical & logical data independence

2. Explain the advantages of Database management system over file processing system

4. Explain main functions of database administrator

5. Explain how a database management system is more advantageous than file processing system.

8. Describe the architecture of Database System and explain each component in the system.

9. Explain the major difference between a weak and strong entity set. Explain giving example

10. Enlist four significant differences between a file processing system and a DBMS

UNIT 2

1. Consider the database where primary keys all underlined Construct SQL queries for the following.

employee (emp-Name, street, city)

Company (Comp-Name, city)

Manager (emp-Name, manager-Name)

Works (emp-Name, Comp-Name, salary)

i) Find the names of all employees who work for first Bank corporation.

ii) Find the names and cities of residence of all employees who work for first Bank corporation.

iii) Find the names and cities of residence of all employees who work for first Bank corporation and
earn more than $10,000.

iv) Find all employees in the database who live in the same cities as the companies for which they
work.

v) Find the company that has the most employees

2. Explain fundamental operations of relational algebra

3. Explain aggregate functions in SQL with example.

4. Explain the fundamental operations of Relational Algebra

5. Consider the following schema:

sailors (sid, sname, rating, age)

boats (bid, bname, color)

reserves (sid, bid, day)

Write down the SQL expressions for

i) Find the names of sailors who have reserved a red and green boat.

ii) Find the names of sailors who have reserved at least one boat.

iii) Find all sids of sailors who have a rating of 10 or have reserved boat 104

iv) Find sailors whose rating is better than some sailor called Horatio.

6. Explain different join operations in SQL with an example.

7. Consider the employee database where the primary keys are underlined. Construct the relational
algebra queries for this relational database:

employee (employee-name, street, city)

company (company-name, city)

manages (employee-name, manager-name)

works (employee-name, company_name, salary)

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

ii) Find the names and cities of residence of all employees who work for First Bank Corporation.

iii) Find the names, street addresses, and cities of residence of all employees who work for First Bank
Corporation and earn more than $10,000.

iv) Find all employees in the database who live in the same cities as the companies for which they
work.

8. Suppose that we have a relation marks (student-id, score) and we wish to assign grades to student
based on the scores as follows:

grade F if score < 40,

grade C if 40 < = score < 60,

grade B if 60 < = score < 80,

and grade S if 80 < = score

UNIT 3

1. Define trigger, Explain need for trigger with example

2. Define the term Normalization. Why it is necessary to decompose the relation into several
relations? Explain with example

3. What is assertion? Write an assertion for:

The sum of all loan amounts for each branch must be less than the sum of all account balances at the
branch

Explain the concept of functional dependencies with an example

4. What is assertion? Give SQL construct for assertion and explain with examples.

5. Explain with example the concept of functional dependencies.

6. Consider the following relational database,

loan(loan-number, branch-name, amount)

borrower(customer-name, loan_number)

Give SQL DDL definitions for above relations. Identify referential-integrity constraints that should be
hold and include them in DDL definition (Assume Banking database).

7. Explain 3NF and BCNF with example

8. What are the advantages of encrypting data stored in database

9. Explain with suitable example a relation is said to be in INF, 2NF & BCNF

UNIT 4

1. Give and explain merge join Algorithm for computing the join operation

2. What is meant by Query optimization? How it is achieved?

3. Explain cost based optimization

4. Write and explain Nested-loop join algorithm

5. Describe the steps involved in query processing. Explain the functionality of each step.

6. What are the various measures of query? Explain.

7. Explain the two basic algorithms to implement the selection operations

 UNIT 5

1. Describe the ACID properties, Explain the usefulness of each

2. What is serializability? Explain the distinction between serial schedule and serializable schedule.

3. How atomicity and durability of a transaction is implemented by the recovery management?

4. Define Transaction. What are the properties of transaction? Explain with the help of example.

5. What are the advantages of concurrent execution of a transaction? Explain.

5. What are the different states that every transaction enters into, and if a transaction aborts? What
action a system initiates?

7. What is recoverable schedule? Why is recoverability of schedules desirable?

UNIT 6

1. Explain Timestamp-Based protocol

2. Describe the use of locks in concurrency control.

3. Draw the architecture of remote backup system and explain several issues in designing a remote
backup system

4. Explain Two-phase Locking Protocol with an example

5. What is deadlock? Explain deadlock detection and recovery.

6. Explain graph based protocols.

7.Explain log-based recovery scheme deferred database modification

8. Describe the use of locks in concurrency control

9. Consider the following two transactions:

T31: read(A);

read (B);

if A = 0 then B: = B + l;

write (B)

T32: read (B);

read (A);

if B = 0 then A :=A + 1;

write (A)

Add lock and unlock instructions to transactions T31 and T32, so that they observe the two phase
locking protocol. Can the execution of these transactions result in deadlock.