Question Bank for the Units – I to V

SE00 4th Semester – [Link].

BR00 Information Technology
SU00 20IT403 - Database Management Systems

Part-A (10 x 2 = 20 Marks) K CO

Level Level

1. Define schema and instance of the database. K1 CO1

1. What is logical data independence and physical data independence? K1 CO1
1. Distinguish between Hierarchical data model and Network Model. K2 CO1
1. What is Database Management System? Why do we need a DBMS? K1 CO1
1. Why key is essential? Differentiate between primary key and candidate key. K1 CO1
2. Define Multivalued Attribute and derived attribute? Give example. K1 CO1
2. What is cardinality in relationship? Give example. K1 CO1
2. What are the various data types in SQL? K1 CO1
2. Consider the following relation schema EMP(eno, name, date_of_birth, Basic_pay, K1 CO1
dept).
Develop an SQL query to display the total number of the employees.
2. Distinguish between embedded SQL and Dynamic SQL K1 CO1
3. Define functional dependency with an example. K1 CO2
3. Explain the purpose of normalization. K1 CO2
3. Why are certain functional dependencies called trivial functional dependencies? K1 CO2
3. Define attribute closure. What is the purpose of attribute closure? K1 CO2
3. What is decomposition? List the properties of the decomposition. K1 CO2
4. Consider a relation R(ABCD) with AB as the primary key. Choose a FD such that R K1 CO2
should be in 1NF but not in 2NF from a following FD’s.

{AB - > C } , {AB->D} ,{A->D} ,{AB->CD}

4. Illustrate DKNF with an example. K1 CO2
4. Define partial dependencey. Give an example. K1 CO2
4. What is minimal cover? K1 CO2
4. Describe the basic structure of relational database. K1 CO2
5. What do you mean by conflict instruction? Give an example. K1 CO3
5. List the drawback of locking. Why do we need locking protocols? K1 CO3
5. What is meant by concurrent transactions? K1 CO3
5. Why do we need lock upgradation and degradation in rigorous two-phase locking? K1 CO3
5. List the SQL facilities available for transaction control. K1 CO3
6. What is a recoverable schedule? K1 CO3
6. List the various levels of Isolation. K1 CO3
6. What are the factors to be considered to determine which transaction will have to K1 CO3
rollback to break the deadlock?
6. When does the problem of starvation occur in lock? K1 CO3
6. How to test for conflict serializability? K1 CO3
7. What are the types of failures in the database system? K1 CO5

7. When is it preferable to use a dense index rather than a sparse index? K1 CO4

7. Why do we need a multilevel index? K1 CO4

7. Why do we need RAID technology? K1 CO4

7. List the possible ways of organizing records in files. K1 CO4

8. How is dynamic hashing different from static hashing? K1 CO4

8. What do you mean by a stable storage device? K1 CO5

8. What is log? Give an example. K1 CO5

8. What is the query processing and query execution plan? K1 CO5

8. Define Query Tree and Query Graph. K1 CO5

9. What is transparency in distributed DB? List its types? K1 CO6

9. List the different types of servers. K1 CO6

9. Differentiate parallel system and distributed systems K2 CO6

9. K1 CO6
What is fragmentation and brief about its types?
9. K1 CO6
What is autonomy and its types?
10. List the types of distributed DB and explain the same. K1 CO6

10. Differentiate structured, semi-structured and unstructured data. K1 CO6

10. K1 CO6
List out the Categories in NoSQL Database.
10. K1 CO6
List the characteristics of NoSQL database systems
10. What is Document Database? K1 CO6
Part – B ( 5 x 13 = 65 Marks)

11.a. Illustrate and explain the structure of database system with neat block diagram. (13) K2 CO1

11.a. What are the various components of Database System? Explain in detail with neat K2 CO1
diagram. (13)
11.a. (i) Discuss the characteristics of database system approach. (8) K2 CO1
ii)Explain the 3-schema database system architecture with a neat diagram. (5)
11.b. (i)Write a SQL function to display the salary of a particular employee. (7) K2 CO1
(ii)Write a SQL function to display the grade of a particular student (6)
Assume the relevant information is available in the database.
11.b. (i) Write the syntax of Triggers in SQL. (5) K2 CO1
(ii) Write a Trigger to remove the white space in employee name while inserting a new
record (8)
11.b. i) Explain the use of views in SQL with an example. (6) K2 CO1
ii) Discuss the join operations in SQL with an example. (7)
12.a. i) Describe various notations/symbols used in construction of ER Diagram.(5) K2 CO2
ii) Construct an ER model for Library management systems with relevant constraints.
(8)
12.a. Explain the various Relational Algebra operations with neat syntax and suitable K2 CO2
examples. (13)
12.a. i) Describe various notations/symbols used in construction of ER Diagram.(5) K2 CO2
ii) Construct an ER model for the car rental company database. (8)
12.a. i) Describe various notations/symbols used in construction of ER Diagram.(5) K2 CO2
ii) Construct an ER model for banking systems with relevant constraints. (8)
12.b. State the need for Normalization of a database and explain 1NF, 2NF ,3NF and BCNF K2 CO2
with examples. (13)
12.b. (i) Explain Armstrong’s design axioms. (5) K2 CO2
(ii) Write an algorithm to find the closure of functional dependencies with an example.
(8)
12.b. What are Normal forms? Explain the types of Normal form with an example. (13) K2 CO2

13.a. Why do we apply serializability in transaction management? Explain different types of K2 CO3
serializability with suitable example (13)
13.a. Explain the concept of Serializability with examples. (13) K2 CO3

13.a. (i) Discuss the ACID properties of a transaction with an example. (9) K2 CO3
 (ii) Draw the state diagram of a transaction and explain in detail (4)
13.b. Why do we need concurrency control? Explain the concurrency control techniques K2 CO3
with an example. (13)
13.b. What is a deadlock? How does it occur? Discuss Deadlock Prevention and Deadlock K2 CO3
Detection with suitable examples. (13)
13.b. What is the drawback of locking mechanism? Describe two-phase Locking protocol K2 CO3
and its types with neat examples. (13)
14.a. (i) Explain various types of failure in the database environment. (4) K2 CO4
(ii) Explain in detail about immediate database modification (9)
14.a. Explain with an example how the query is processed internally by a query evaluation K2 CO5
engine with a neat diagram. (5)
(ii) Discuss the selection algorithm in detail. (8)
14.a. (i) Explain in detail about various levels of RAID with example. (8) K2 CO4
(ii) Discuss the various file organization methods used in DBMS. (5)
14.a. (i) Explain various types of failure in the database environment. (4) K2 CO4
(ii) Explain in detail about deferred database modification (9)
14.b. Describe the process of Heuristics Query optimization with suitable example (13) K2 CO5

14.b. What is Query optimization? Explain in detail about cost-based query optimization K2 CO5
with an example. (13)
14.b. What is Query optimization? Explain in detail about Heuristics query optimization K2 CO5
with an example. (13)
14.b. Describe the process of Cost based Query optimization with suitable example (13) K2 CO5

15.a. Describe the process of implementation of concurrent transaction in distributed K2 CO6

database with suitable example (13)

15.a. How are concurrency control techniques involved in distributed databases? Discuss K2 CO6
various concurrency control techniques with suitable examples. (13)
15.b. Explain NoSQL characteristics related to distributed databases and distributed systems K2 CO6

15.b. Discuss the different categories of NOSQL with an example. (13) K2 CO6

15.b. Explain the Client - Server Architecture for DDBMS in detail. (13) K2 CO6

15.b. Depict the need for NoSQL database and list the applications of the same. (13) K2 CO6
(Part C- 1x15=15 marks)

16.a. Consider the following relation: K3 CO1

Customer(cname,street,city)
 Account(accno,branch,balance)
Loan(lno,branch,amount)
Depositor(accno,cname)
Borrower(lno,cname)

(i) Give the SQL DDL statement for customer and account. Include the key constraint
and include the constraint for the minimum balance in the account should 1000 Rs
(i)Give the SQL DDL definition for the Depositor and Borrower schema. Identify the
integrity constraints that should hold and include them in the DDL definition.
(ii)To find all loan numbers for loans made at the Perryridge branch with loan
amounts greater than $1200.
(iii)Find the names of all customers whose street includes the substring “Main”.
(iv)Find all customers who have an account but no loan.
(v)Find the number of depositors in the bank. (15)
16.a. (i) Explain the three data anomalies that are likely to occur as a result of data K3 CO2
redundancy with examples. Can data redundancy be completely eliminated in
database approach? (5)
(ii) Consider the universal relation R={ A,B,C,D,E,F,G,H,I,J} and the set of
Functional dependencies F={(A,B) ->C, A->(D,E), B->F,F->(G,H), D->(I,J) }.
What is the key for R? Decompose R into 2NF and 3NF relations. (10)
16.a. Consider the following relation Employee (ename, company_name, salary). Give the K3 CO1
DDL definition and write SQL query for the following. (15)
• Find the total salary of each company.
• Find the employee name who is getting lowest salary
• Find the company name which has the lowest average salary.
• Find the employee name whose salary is higher than the average salary of
TCS.
• Display the employee details in alphabetical order.
• List the Employees whose name starts with ‘Anu’.
• List the Employees whose name contains ‘raj’ word.
viii)List the Employee whose salary is between 50 K to 1 Lac using between
operator.
16.a. (i) Explain the three data anomalies that are likely to occur as a result of data K3 CO2
redundancy with examples. Can data redundancy be completely eliminated in
database approach? (5)
(ii) Consider the universal relation R={ A,B,C,D,E,F,G,H,I,J} and the set of
Functional dependencies F={(A,B) ->C, A->(D,E), B->F,F->(G,H), D->(I,J) }. What
is the key for R? Decompose R into 2NF and 3NF relations. (10)
16.a. Consider the employee database. K3 CO2
Emp(ename,street,city)
Works(ename,companyname,salary)
Company(companyname,city)
Manages(ename,managername)

Give the expression in SQL to express each of the following queries:

• Find the names and cities of all employees who work for First Bank
Corporation. (2)
• Find all employees in the database who do not work for First Bank
Corporation.(2)
 • Find the names of all employees who earn more than average salary of all
employees of their company.(2)
• Find all employees in the database who live in the same cities and on the same
street as do their managers.(3)
• Find all employees in the database who earn more than every employee of
small bank corporation.(3)
• Give all employees of First Bank Corporation a 10 percent rise. (3)
16.b. Discuss how dynamic hashing supports database indexing. Suppose that we are using K3 CO4
extendible hashing on a file that contains records with the following search-key
values: 2, 3, 5, 7, 11, 17, 19, 23, 29, 31 .
Show the extendable hash structure for this file if the hash function is h(x) = x mod 8
and buckets can hold three records. (15)
16.b. (i)Write an algorithm for Nested-loop join and Block nested-loop join (8) K3 CO5
(ii) Let relations r1(A, B, C) and r2(C,D,E) have the following properties:
r1 has 20,000 tuples, r2 has 45,000 tuples, 25 tuples of r1 fit on one block, and 30
tuples of r2 fit on one block.
Estimate the number of block accesses required, using each of the following join
strategies for r1 1 r2:
a. Nested-loop join b. Block nested-loop join (5)
16.b. Consider the following schedules. The actions are listed in the order they are K3 CO3
scheduled, and prefixed with the transaction name. (15)
(15)
S1: T2:R(Z), T2:R(Y), T2:W(Y), T3:R(Y), T3:R(Z), T1:R(X),
T1:W(X), T3:W(Y),
T3:W(Z), T2:R(X), T1:R(Y) , T1:W(Y), T2:W(X)

S2: T3:R(Y), T3:R(Z), T1:R(X), T1:W(X), T3:W(Y), T3:W(Z),

T2:R(Z), T1:R(Y),
T1:W(Y), T2:R(Y), T2:W(Y), T2:R(X), T2:W(X)

For each of the schedules, answer the following questions:

• What is the precedence graph for the schedule?

• Is the schedule conflict-serializable? If so, what are all the conflict
equivalent serial schedules?

16.b. (i) Explain the concept of B+ tree indexing. (4) K3 CO4

ii) Construct a B+-tree for the following set of key values using the order n=4
(2, 3, 5, 7, 11, 17, 19, 23, 29, 31) (6)
(iii) show the form of the tree after each of the following series of operations: (5)
• Insert 9. b. Insert 10. c. Insert 8. d. Delete 23. e. Delete 19.