Database Management System

Question Bank
UNIT – I
1. List four significant differences between a file-processing system and a DBMS
2. What is data independence? Differentiate between physical and logical data
independence.
3. Describe the architecture of Database system and explain each component in the
system.
4. Explain main functions of database administrator.
5. List six major steps that you would take in setting up a database for a particular
enterprise
6. Define and explain following terms associated with E-R model
(i) Entity
(ii) Attribute, single valued, multivalued attributes
(iii) Entity set
(iv) Domain
7. Construct an E-R diagram of a banking environment where in the customer,
borrows a loan from the bank. Reduce the E-R schema to a table.
8. Design an E-R diagram for keeping track of the exploits of your favorite sports
team. You should store the matches played, the score in each match, the players in
each match, and individual player statistics for each match. Summary should be
modeled as derived attributes.
9. Consider a database used to record the marks that students get in different exams
of different course offerings. Construct a E-R diagram that models exams as
entities, and uses a ternary relationship, for the above database
10. Construct an E-R diagram for a car insurance company whose customers own one
or more cars each. Each car has associated with it zero to any number of recorded
accidents.
11. Explain the following terms with proper examples
a. Weak entity set
b. Derived attribute
 c. Primary key
d. Aggregation
12. Define the concept of aggregation. Give an example where this concept is useful.

UNIT – II
1. Explain the following joins with suitable example:
i) Inner join ii) Left outer join
iii) Natural inner join iv) Full outer join
2. Let R = (A,B,C) and let r1 and r2 both be relations on R. Give an expression in the
domain relational calculus that is equivalent to each of the following:
i) r1  r2 ii) r1  r2
iii) r1 – r2 iv) A,B (r1)  B,C
3. Let R = (A,B) and S = (A,C) and let r(R) and s(S) be relations. Write relational
expressions equivalent to the following domain-relational calculus expressions:
i) {<a> |  b ( <a,b,c>  r  b = 17)}
ii) {<a, b,c> | <a,b>  r  <a,c>  s}
1. Explain fundamental operations of Relational Algebra.
2. Describe the difference in meaning between the terms relation and relation
schema with example.
6. Explain different join operations in SQL with an example
7. Suppose that we have a relation marks(student-id, score) and we wish to assign
grades to students based on the score 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. Write
SQL queries to do the following
a. Display the grade for each student, based on the marks relation
b. Find the number of students with each grade. (7)

8. Consider the employee database where the primary keys are underlined.
Construct the SQL queries for this relational database:
employee(employee-name, street, city)
works(employee-name, company-name, salary)
 company(company-name, city)
manages(employee-name, manager-name)
1. Find the names and cities of residence of all employees who work for First Bank
Corporation.
1. Find the name, street, addresses, and cities of residence of all employees who
work for First Bank Corporation and earn more than $10,000.
2. Find all employees in the database who do not work for First Bank Corporation.
3. Assume that the companies may be located in several cities. Find all companies
located in every city in which Small Bank Corporation is located.
4. Find the company that has the most employees.
5. Find those companies whose employees earn a higher salary, on average, than the
average salary at First Bank Corporation.
6. Give all managers of First Bank Corporation a 10-percent raise unless the salary
becomes greater than $100,000; in such cases, give only a 3-percent raise.

UNIT – III
1. What is role? Explain authorization grant graph.
2. Define trigger. Explain need for trigger with example.
3. Consider the following relational database;
employee(employee-name, street, city)
works(employee-name, company-name, salary)
company(company-name, city)
manages(employee-name, manager-name)
Give an SQL DDL definition of this database. Identify referential integrity
constraints that should hold, and include them in DDL definition.
4. Explain different encryption techniques.
5. Define the term Normalization. Why is it necessary to decompose the relation into
several relations? With an example state the anomalies are removed by decompositions
6. Explain with suitable example the concept of functional dependencies and transitive
dependencies..
7. List and explain with a suitable example the pitfalls in relational database design.
8. Explain with appropriate example, a relation is said to be in 1NF, 2NF and 3NF
9. Explain with suitable example a relation is said to be in 2NF, BCNF and DKNF
10. What is encryption list? Explain different encryption techniques
11. Write an SQL trigger to carry out the following action: On delete of an account, for
each owner of the account, check if the owner has any remaining accounts, and if she
does not, delete her from the depositor relation.

UNIT – IV
1) Consider the schema Account(account-number, branch-name, balance) and
Branch(branch-name, branch-city, assets). Give the optimize queries for the following:
a) Write a nested query on the relation account to find for each branch with name
starting with ‘B’, all accounts with maximum balance at the branch.
b) Rewrite the preceding query, without using nested subquery, that is,
decorrelate the query.
2) Describe the steps involved in query processing. Explain the functionality of each step
3) Define materialized view. Give an example explaining view maintenance.
4) What is meant by Query Optimization? How is it achieved?
5) Define external sort. Explain external sort merge algorithm
6) Give and explain Merge Join Algorithm for computing the join operation
7) Explain structure of Query optimization.
8) Explain cost-based optimization.

UNIT – V
1) Describe the ACID properties. Explain the usefulness of each
2) Justify the following statement: Concurrent execution of transaction is more important
when data must be fetched from (slow) disk or when transactions are long, and is less
important when data is in memory and transactions are very short.
3) What is serializability? Explain the distinction between serial schedule and serializable
schedule
4) What is cascadeless schedule? Why is cascadelessness of schedules desirable? Are
there any circumstances under which it would be desirable to allow noncascadeless
schedules? Explain your answer with example
5) How atomicity and durability of a transaction is implemented by the recovery
management?
6) What are the different states that every transaction enters into, and if a transaction
aborts? What action a system initiates?
7) Explain view serializability .
8) List advantages and disadvantages of two-phase locking
9) What is serializability of a schedule and how a conflict of operations are removed?
10) What is deadlock? Under what conditions is it less expensive to avoid deadlock than
to allow deadlock to occur and then to detect them.

UNIT-VI
1) Explain two-phase locking with and example
2) Explain how lock requests are implemented.
3) Consider the following two transactions:
T31: read(A);
read(B);
if A= 0 then B:= B+1;
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?
4) What is deadlock? Explain deadlock detection and recovery.
5) Explain Timestamp-Based protocol
6) Show by example that there are schedules possible under the tree protocol that are
possible under the two-phase locking protocol, and vice-versa
7) Compare the deferred and immediate-modification versions of the log-based recovery
scheme in terms of ease of implementation and overhead cost
8) Draw architecture of remote backup system and explain several issues in designing a
remote backup system.
9) Explain the shadow paging crash recovery technique.
10) Explain multiple granularity mechanism of locking.
11) What are checkpoints? What are the advantages of using checkpoints?
12) Explain log-based recovery scheme deferred database modification