UNIT – I

RELATIONAL DATABASES

PART-A

1. Define database management system (remembering)

Database management system (DBMS) is a collection of interrelated data and a set of
programs to access those data.
Examples: Microsoft Access, MySQL, Microsoft SQL Server, Oracle and FileMaker Pro are all
examples of database management systems.

2. What is the purpose of DBMS? (remembering) (NOV/DEC 2014)

A database management system is a software tool that makes it possible to organize data in a
database.

3. Write the characteristics that distinguish the database approach with the file-based
approach. (creating) (APR/MAY 2015)
- Organized/Related
- Shared
- Permanent or Persistence
- Validity/integrity/Correctness
- Security
- Consistency
- Non-redundancy
- Easily Accessible
- Independence
- Recoverable
- Flexible to change

4. List out few applications of DBMS. (analyzing)(APR/MAY 2019)

a) Banking
b) Airlines
c) Universities
d) Credit card transactions
e) Tele communication

5. What are the disadvantages of file processing system? (understanding)(MAY/JUNE 2016)

The disadvantages of file processing systems are
a) Data redundancy and inconsistency
b) Difficulty in accessing data
c) Data isolation
d) Integrity problems
 e) Atomicity problems
f) Concurrent access anomalies

6. What are the advantages of using a DBMS? (remember)

The advantages of using a DBMS are
a) Controlling redundancy
b) Restricting unauthorized access
c) Providing multiple user interfaces
d) Enforcing integrity constraints.
e) Providing backup and recovery

7. Define instance and schema? (remembering)

Instance: Collection of data stored in the data base at a particular moment is called an
Instance of the database.
Schema: The overall design of the data base is called the data base schema.

8. State the level of abstraction in dbms(Nov/Dec17)

Physical level: This is the lowest level of data abstraction. It describes how data is actually stored
in database. You can get the complex data structure details at this level.
Logical level: This is the middle level of 3-level data abstraction architecture. It describes what data
is stored in database.
View level: Highest level of data abstraction. This level describes the user interaction with database
system.

9. what are the problem caused by redundancy? (Nov/Dec17)

Due to insertion of some data, some dummy data has to be inserted which creates inconsistency.

8. Define data model? (understanding) (APR/MAY 2011)

A data model is a collection of conceptual tools for describing data, data relationships, data
semantics and consistency constraints.

9. What is storage manager? (understanding)

A storage manager is a program module that provides the interface between the low level
data stored in a database and the application programs and queries submitted to the system.

10. What is a data dictionary? (remember)

A data dictionary is a data structure which stores meta data about the structure of the database ie.
The schema of the database.

11. What is an entity relationship model? (evaluation)(MAY/JUNE 2016)

The entity relationship model is a collection of basic objects called entities and relationship
among those objects. An entity is a thing or object in the real world that is distinguishable
from other objects.

12. What are attributes? Give examples. (remember)

An entity is represented by a set of attributes. Attributes are descriptive properties possessed by each
member of an entity set.
Example: possible attributes of customer entity are customer name, customer id, Customer Street,
customer city.

13. Define the terms i) Entity set ii) Relationship set (remembering)(APR/MAY 2019)
Entity set: The set of all entities of the same type is termed as an entity set.

Relationship set : The set of all relationships of the same type is termed as a relationship set.

14. Define single valued and multivalued attributes. (remembering)

Single valued attributes: attributes with a single value for a particular entity are called single valued
attributes.
Multivalued attributes : Attributes with a set of value for a particular entity are called
multivalued attributes.

15. What are stored and derived attributes? (understand) (NOV/DEC 2011)
Stored attributes: The attributes stored in a data base are called stored attributes.
Derived attributes: The attributes that are derived from the stored attributes are called derived
attributes.

16. Define null values. (remembering)

In some cases a particular entity may not have an applicable value for an attribute or if we do not
know the value of an attribute for a particular entity. In these cases null value is used.

17. Define weak and strong entity sets? (remembering)

Weak entity set: entity set that do not have key attribute of their own are called weak entity
sets.
Strong entity set: Entity set that has a primary key is termed a strong entity set.

18. Define Atomicity in transaction management. (Remembering) (APRIL / MAY 2013)

Atomicity states that database modifications must follow an “all or nothing” rule. Each
transaction is said to be “atomic.” If one part of the transaction fails, the entire transaction fails. It is
critical that the database management system maintain the atomic nature of transactions in spite of
any DBMS, operating system or hardware failure.

19. Give example for one to one and one to many relationships.(remember) (APRIL / MAY
2013)
One to One : College – Principle
One to Many : Manage – Employee

20. List four significant differences between a file-processing system and a DBMS. (analyzing)
( MAY/JUNE 2012)(NOV/DEC 2016)
A database coordinates the physical and logical access to the data; a file-processing system only
coordinates physical access to the data
o A DBMS reduces the amount of data duplication
o A DBMS is designed to allow flexibility in what queries give access to the data, where a
file-processing system only allows pre-determined access to data (by specific compiled programs)
o A DBMS is designed to coordinate and permit multiple users to access data at the same
time. A file processing system is much more restrictive in simultaneous data access
6 Department of CSBS
 UNIT – I

RELATIONAL DATABASES

Part A

[Link] are the different types of Data Models? (remembering) (MAY/JUNE 2012/2019)
 Relational Model
 Hierarchical Model
 Network Model
 Object Oriented Model

[Link] is a data model?(remembering) (NOV/DEC 2011)(APR/MAY 2019)

A Database model defines the logical design of data. The model describes the relationships between
different parts of the data. In history of database design, three models have been in use.
 Hierarchical Model
 Network Model
 Relational Model

[Link] is a DBA? What are the responsibilities of a DBA? (remembering)(APR/MAY 2011)

A database administrator (short form DBA) is a person responsible for the installation,
configuration, upgrade, administration, monitoring and maintenance of databases in an
[Link] role includes the development and design of database strategies, system
monitoring and improving database performance and capacity, and planning for future expansion
requirements. They may also plan, co-ordinate and implement security measures to safeguard the
database

[Link] is a candidate key? (remembering)

Minimal super keys are called candidate keys.

[Link] are primary key constraints? (remembering) (APRIL / MAY 2013)

A primary key is a constraint defined on a relational database table that prevents users
fromentering duplicate records into the table. i.e, UNIQUE & NOT NULL

[Link] is a super key? (remembering)

A super key is a set of one or more attributes that collectively allows us to identify
uniquely an entity in the entity set.

[Link] is a SELECT operation? (remembering)

The select operation selects tuples that satisfy a given predicate. We use the lowercase letter ss
to denote selection. ss

[Link] query language? (remembering)

A query is a statement requesting the retrieval of information. The portion of DML that
involves information retrieval is called a query language.

[Link] is foreign key? (remembering)

A relation schema r1 derived from an ER schema may include among its attributes the
primary key of another relation schema [Link] attribute is called a foreign key from r1
referencing r2.

[Link] are the parts of SQL language? (remembering)

The SQL language has several parts:
Data - Definition language
Data Manipulation language
View definition
Transaction control
Embedded SQL
Integrity
Authorization

[Link] are the three classes of SQL expression? (remembering)

SQL expression consists of three clauses: Select , From and Where

[Link] tuple variable? (remembering)

Tuple variables are used for comparing two tuples in the same relation. The tuple
Variables are defined in the from clause by way of the as clause.

[Link] the string operations supported by SQL? (analyzing)

1) Pattern matching Operation
2) Concatenation
3) Extracting character strings
4) Converting between uppercase and lower case letters.

[Link] the set operations of SQL? (analyzing)

1) Union
2) Intersect operation
3) The except operation

[Link] is the use of Union and intersection operation? (remembering)

Union: The result of this operation includes all tuples that are either in r1 or in r2 or in both r1 and
[Link] tuples are automatically eliminated.
Intersection: The result of this relation includes all tuples that are in both r1 and r2.

[Link] are aggregate functions? And list the aggregate functions supported by SQL?
(remembering)
Aggregate functions are functions that take a collection of values as input and return a single
value.
Aggregate functions supported by SQL are
Average: AVG
Minimum: MIN
Maximum: MAX
Total: SUM
Count: COUNT

[Link] is the use of group by clause? (remembering)

Group by clause is used to apply aggregate functions to a set of tuples. The attributes given
in the group by clause are used to form groups. Tuples with the same value on all attributes
in the group by clause are placed in one group.

[Link] the SQL domain Types? (analyzing)

SQL supports the following domain types.
1) Char(n) 2) varchar(n) 3) int 4) numeric(p,d)

5) float(n) 6) date.

[Link] is the use of integrity constraints? (remembering)

Integrity constraints ensure that changes made to the database by authorized users do not result in a
loss of data consistency. Thus integrity constraints guard against accidental damage to the database.

[Link] is trigger? (remembering) (APRIL / MAY 2013)

Triggers are statements that are executed automatically by the system as the side effect of a
modification to the database.
Triggers are useful mechanisms for alerting humans or for starting certain tasks automatically
when certain conditions are met.

[Link] the six fundamental operations of relational algebra and their symbols.
(Remembering)
(NOV/DEC 2012)
 SELECT (symbol: ∑ (sigma))
 PROJECT (symbol: Π (pi))
 RENAME (symbol:  (rho))
 UNION ( U )
 INTERSECTION ( ∩ )
 DIFFERENCE (or MINUS, – )

[Link] two reasons why null values might be introduced into the database. (NOV/DEC 12)
(analyzing)
Nulls may be introduced into the database because the actual value is either unknown or does not
exist. For example, an employee whose address has changed and whose new address is not yet
known should be retained with a null address. If employee tuples have a composite attribute
dependents, and a particular employee has no dependents, then that tuple’s dependents attribute
should be given a null value.

9 Department of CSBS
[Link] a circumstance in which you would choose to use embedded SQL rather than
using SQL alone. (analyzing) (MAY/JUNE 2012)
Writing queries in SQL is typically much easier than coding the same queries in a general-purpose
programming language. However not all kinds of queries can be written in [Link] no declarative
actions such as printing a report, interacting with a user, or sending the results of a query to a
graphical user interface cannot be done from within SQL. Under circumstances in which we want
the best of both worlds, we can choose embedded SQL or dynamic SQL, rather than using SQL
alone or using only a general-purpose programming language. Embedded SQL has the advantage of
programs being less complicated since it avoids the clutter of the ODBC or JDBC function calls,
but requires a specialized preprocessor.

[Link] an example explain what a derived attribute is?(applying) (NOV/DEC 2011)

A attribute that’s value derived from stored attribute. For example Age and its value is
derived from stored attribute Date of Birth.

[Link] are the steps involved in query processing? (remembering)

The basic steps are:
Parsing and translation
Optimization and Evaluation

[Link] is embedded SQL? What are its advantages? (remembering) (APR/MAY2011)

Embedded SQL is a method of combining the computing power of a programming language and
the database manipulation capabilities of SQL. Embedded SQL statements are SQL statements
written inline with the program source code of the host language. The embedded SQL statements
are parsed by an embedded SQL preprocessor and replaced by host-language calls to a code library.
The output from the preprocessor is then compiled by the host compiler. This allows programmers
to embed SQL statements in programs written in any number of languages such as: C/C++, COBOL
and FORTRAN.

[Link] Static SQL and Dynamic SQL.(NOV/DEC 2014) (NOV/DEC2015)

(APR/MAY 2015)(NOV/DEC 2016) (NOV/DEC 2017) (analyzing)

STATIC SQL DYNAMIC SQL

In static SQL how database will be accessed is In dynamic SQL, how database will be
predetermined in the embedded SQL statement. accessed is determined at run time.

SQL statements are run at compile time SQL statements are run at execution time

Parsing, validation, optimization, and generation Parsing, validation, optimization, and

of application plan are done at compile time. generation of application plan are done at run
time.

[Link] between key and super key.(Understand)(Apr/May 2017)

A SuperKey, SK,.specifies a uniqueness constraint that no two distinct tuples in any state r of R can
have the same value for SK.
A Key ,K.,of R is a superkey of R with additional property that removing any attribute A form k
leaves set of attributes that is not a superkey any more.

PART-B

1. Explain all types of data models (16) (evaluating) [NOV/DEC 2014] (April/May 2008/2019)
2. Define relational algebra. With suitable example. (16) (remembering) (Nov 2008 &
MAY/JUNE 2012)
3. Write about the structure of database system architecture with block diagram. (view the
structure)(APRIL / MAY 2013)(creating)
4. With help of a neat block diagram explain architecture of a database management system.(8)
(understand)
5. What are the advantages of having a centralized control of data? Illustrate your answer with
suitable example.(8) .(NOV/DEC 2015) (remembering)
6. Briefly explain about Database system architecture.(May/June 2016) (understand)
7. Briefly explain about Views of data. (May/June 2016) (understand)
[Link] select,project and Cartesian product operations in relational algebra with an
example.(Nov/Dec 2016) (understand)
[Link] between foreign key constraints and referential integrity constrainsts with suitable
example. (understand)(Nov/Dec 2017)

10. State and explain the command DDL, DML, DCL with suitable example.(remember)
[Link] about the static and dynamic SQL in detail.(Understand)(APR/MAY 2019)

PART –C

1. Justify the need of embedded SQL. Consider the relation student (studentno, name, mark and
grade). Write embedded dynamic SQL statements in C language to retrieve all the students’ records
whose mark is more than 90.(create)
2.i)Explain the overall architecture of the database system in detail.(Remember)
ii)List the operations of relational algebra and purpose of each with example.(Apr/May 2017)
[Link] the need of embedded [Link] the relation student(Reg No, Name,mark and
grade).Wwrite embedded dynamic SQL program in C language to retrieve all the students’records
whose mark is more than 90.(Analyse)(Apr/May 2017)
4. Write the DDL,[Link] commands for the students database. Which contains student
details:name,id,DOB,branch,[Link] details: course name,course id,stud id,faculty
name,id,marks.(Remember)(Nov/Dec 2017)
5. What are the various components of database systems? Explain in detail. (remembering)

ASSIGNMENT QUESTIONS

1. Consider the relation schema given in below fig. Design and draw an ER diagram that capture the
information of this schema.(Analyse)(Apr/May 2017)
Employee(empno,name,office,age)
Books(isbn,title,authors,publisher)
Loan(empno,isbn,date)
 Write the following queries in relational algebra and SQL.
(i) Find the names of employees who have borrowed a book published by McGraw-Hill.
(ii) Find the names of employees who have borrowed all books published by McGraw-Hill.

2. Consider a student registration database comprising of the below given table schema.
(Remember)
Student file: Student number, student name, Address, telephone
Course file: course no, description, hours, professor number.
Professor file: professor number, name, office.
Registration file:student number, course number, date. Consider a suitable example of tuple/
records for the above mentioned tables and write DML statement to answer for the queries listed
below.

(i) Which courses does a specific professor teach?

(ii) What courses are taught by two specific professors?
(iii) Who teaches a specific course and where is his\her office?
(iv) For the specific student number, in which courses is the student registered and what is his\her
name? (v) Who are the professors for specific student?
(vi) Who are the students registered in a specific course?

[Link] the following table. (Understand)

Degree(degcode,name,subject)
Candidate(seat no,degcode,name,semester,month,year,result)
Marks(seatno, degcode,name,semester,month,year,papcode,result)
Degcode-degcode,Name-name of the degree([Link])
Subject – subject of the course [Link],Pap code –paper code eg.A1.
Solve the following queries using SQL
(1) Write a SELECT statement to display all the degree codes which are there in the candidate table
but not present in degree table in the order of degcode. (4)
(2) Write a SELECT statement to display the name of all the candidates who have got less than 40
marks in exactly 2 subjects. (4)
(3) Write SELECT statement to display the name, subject and number of candidates for all degrees
in which there are less than 5 candidates.(4)
(4) Write a SELECT statement to display the name of all the candidates who have got highest total
marks in Ms.,(Maths).

UNIT II
DATABASE DESIGN
 PART-A

1. What is meant by functional dependencies? (remembering)

Consider a relation schema R and a C R and ß C R. The functional dependency a ß holds on
relational schema R if in any legal relation r(R), for all pairs of tuples t1 and t2 in r such that
t1 [a] =t1 [a], and also t1 [ß] =t2 [ß].

2. What is meant by computing the closure of a set of functional dependency? (remembering)

+ The closure of F denoted b y F is the set of functional dependencies logically implied by F.

3. What is meant by normalization of data? (remembering)

It is a process of analyzing the given relation schemas based on their Functional
Dependencies (FDs) and primary key to achieve the properties Minimizing redundancy, Minimizing
insertion, deletion and updating anomalies

4. Define Boyce codd normal forms(evaluating) (APRIL / MAY 2013)

A relation schema R is in BCNF with respect to a set F of functional + dependencies if, for
all functional dependencies in F of the form. a->ß, where a

5. Define normalization. (remembering)

Normalization of data is a process during which unsatisfactory relation schemas are decomposed by
breaking up their attributes into smaller relation schemas that possess desirable properties

6. What is 1NF? (remembering)

The domain of attribute must include only atomic (simple, indivisible) values.

7. What is 2NF? (remembering)

A relation schema R is in 2NF if it is in 1NF and every non-prime attribute An in R is fully
functionally dependent on primary key.

8. What is 3NF? (remembering)

A relation shema R is in 3NF if it is in 2NF and no nonprime attribute of R is transitively dependent
on the primary key. A funtional dependency X  Y in arelation shema R is a transitive dependency
if ther is a set of attributes Z that is not a subset of any key of R, and both XY and ZY hold.

9. Define multivalue dependency. (remembering) (NOV/DEC 2012)

Multivalued dependencies: Multivalued dependencies are a result of 1NF which disallowed an
attribute in a tuple to have a set of values. If we have two or more multivalued independent
attributes in the same relation schema, we get into the problem of having to repeat every value of
one of the attributes with every value of the other attributes with every value of the other attribute to
keep the relation instances consistent. A multivalued dependency X->->Y specified on relation
schema R where X and Y are subsets of R specifies the following constraint on any relation r of R:

10. What is need for normalization? (remembering) (APRIL / MAY 2013)

To ensure that the update anomalies do not occur.
 Normal forms provide a formal frame work for analyzing relation schemas based on their
keys and on the functional dependencies among their attributes.
 A series of tests that can be carried out on individual relation schemas so that the relation
 database can be normalized to any degree.
 When a test fails, the relation violating that test must be decomposed into relations that
individually meet the normalization tests.

11. In what way BCNF is different from 3NF? (remembering)

A relation schema R is in BCNF if whenever a functional dependency XY holds in R,then X is a
superkey of [Link] only difference between BCNF and 3NF : the 3NF allows A to be a prime if X is
not a superkey, is absent from BCNF.

12. Show that, if a relational database is in BCNF, then it is also in 3NF. (understanding)
(NOV/DEC 2012)
This is because in many cases, there exists no database schema that is both BCNF and dependence
preserving. If one prefers to have a dependence preserving database schema, then one have to
choose a normal form, such as 3NF, that is weaker than BCNF.

13. Why are certain functional dependencies called as trivial functional dependencies?
(remembering) (MAY/JUNE 2012)
Because the right hand side is a subset of the left hand side. Therefore it is obvious that the right
hand side is dependent on the left hand side. More unclear book definition: An FD is trivial if it is
satisfied by all instances of a relation.

14. Define Denormalization(remembering)

Denormalization is a strategy that database managers use to increase the performance of a database
infrastructure. It involves adding redundant data to a normalized database to reduce certain types of
problems with database queries that combine data from various tables into a single table. The
definition of denormalization is dependent on the definition of normalization, which is defined as
the process of organizing a database into tables correctly to promote a given use.

15. Why 4NF is more desirable than BCNF? (remembering) (NOV/DEC 2014)
4NF is more desirable than BCNF because it reduces the repetition of information. If we consider a
BCNF schema not in 4NF, we observe that decomposition into 4NF does not lose information
provided that a lossless join decomposition is used, yet redundancy is reduced.

16. Define: Functional dependency. (remembering) (APR/MAY 2015)

A functional dependency is a constraint between two sets of attributes in a relation from
a database. In other words, functional dependency is a constraint that describes the relationship
between attributes in a relation.

17. State the anomalies of 1NF. (clarity) (NOV/DEC 2015)

*INSERT anomalies
*UPDATE anomalies
*DELETE anomalies

18. Is it possible for several attributes to have the same domain? Illustrate your answer with
suitable examples.(analyzing) (NOV/DEC 2015)
Yes , It is possible for several attributes to have the same domain. The attributes indicate different
roles, for the domain. For example, in the STUDENT relation, the same domain
 Local_phone_numbers plays the role of Home_phone referring to the home phone of a student and
the role of office phone, referring to the office phone of the student.
[Link] is weak entity?Give example.(remembering) (Nov/Dec 2016)

Weak entity is an entity that depends on another entity. Weak entity doen't have key attribute of their
own. Double rectangle represents weak entity.

LOAN INSTALMENT

20. What are the desirable properties of decomposition?(Rememeber)(Apr/May 2017/2019)

1. Lossless Decomposition.
2. Dependency Preservation- Dependency is an important constraint on the database.
3. Lack of Data Redundancy - Lack of Data Redundancy is also known as a Repetition of
Information.
PART-B

1. What is meant by Relational calculus? Query examples for tuple and domain relational
Calculus? (remember) [Nov/Dec 2011 & April 2012]
2. Explain E-R Model concept and extended E-R model. (evaluating)
3. A car rental company maintains a database for all vehicles in its current fleet. For all vehicles it
includes the vehicle identification number license number, manufacturer, model, date of purchase
and color. Special data are included for certain types of vehicles. (understand)

Trucks:Cargo capacity
Sports Cars: horsepower, renter age requirement
Vans: number of passengers
Off-road vehicles: ground clearance, drivetrain (four-or two-wheel drive)
Construct an ER model for the car rental company database. (NOV/DEC 2015)
4. Distinguish between lossless-join decomposition and dependency reserving
decomposition(April/May2017)(Understanding)
[Link] and explain the architecture of DBMS. Draw the ER diagram for banking
systems.(Home loan applications)(Remember)(Nov/Dec 2017)
[Link] is normalization? Explain in detail about all Normal forms.(Remember)(APR/MAY 2019)
[Link] discuss about the functional dependency concepts.(Understand)(APR/MAY 2019)

PART-C

1. Look at this database, which is in 1NF, to see what you must do to put that database in DK/NF.
2. Discuss the correspondence between the ER model construct and the relational model constructs.
Show how each ER model construct can be mapped to the relational model. Discuss the option for
mapping EER model construct.(Analyse)(Nov/Dec 2017)

ASSIGNMENT QUESTIONS

1. Consider the universal relation R={A,B,C,D,E,F,G,H,I} 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 role for R?
Decompose R in to 2NF then 3NF relations(analyzing)

2. Construct an ER diagram for a car insurance company whose customers own one or more cars
[Link] car has associated with it zero to any number of recorded [Link] insurance
policy covers one or more cars, and has one or more preminum payments associated with [Link]
payment is for a particular period of time,and has an associated due date and date when the payment
was received.(Nov/Dec 2016)(Apply)

UNIT III
TRANSACTIONS

PART-A
1. What is transaction? (remembering)
Collections of operations that form a single logical unit of work are called transactions.

2. What are the two statements regarding transaction? (remembering)

The two statements regarding transaction of the form:
o Begin transaction 2. End transaction

3. What are the properties of transaction? Define ACID properties. (APRIL / MAY 2013)
(remembering) (NOV/DEC 2014)(MAY/JUNE 2016)
The properties of transactions are:
Atomicity
Consistency
Isolation
Durability

4. What is recovery management component? (remembering)

Ensuring durability is the responsibility of a software component of the base system called the
recovery management component.
5. When is a transaction rolled back? (remembering)
Any changes that the aborted transaction made to the database must be undone. Once the
changes caused by an aborted transaction have been undone, then the transaction has been
rolled back.

6. What are the states of transaction? (remembering)(APR/MAY 2019)

The states of transaction are
Active
Partially committed
Failed
Aborted
Committed
Terminated

7. List out the statements associated with a database transaction? (analyzing)

Commit work
Rollback work

8. What is a shadow copy scheme? (remembering)

It is simple, but efficient, scheme called the shadow copy schemes. It is based on making copies
of the database called shadow copies that one transaction is active at a time. The scheme also
assumes that the database

9. Give the reasons for allowing concurrency? (understanding)

The reasons for allowing concurrency is if the transactions run serially, a short transaction
may have to wait for a preceding long transaction to complete, which can lead to unpredictable
delays in running a transaction. So concurrent execution reduces the unpredictable delays in running
transactions.

10. What is average response time? (remembering)

The average response time is that the average time for a transaction to be completed after it
has been submitted.

11. What are the two types of serializability? (NOV/DEC 2014) (remembering)
The two types of serializability is
1. Conflict serializability, 2. View serializability

12. Define lock? (remembering)

Lock is the most common used to implement the requirement is to allow a transaction to
access a data item only if it is currently holding a lock on that item.

13. What are the different modes of lock? (remembering)

The modes of lock are:
1. Shared Lock
2. Exclusive Lock

14. Define deadlock? (remembering)

Neither of the transaction can ever proceed with its normal execution. This situation is called
deadlock.

15. Define the phases of two phase locking protocol. (remembering) (APRIL / MAY 2013)
Growing phase: A transaction may obtain locks but not release any lock.
Shrinking phase: A transaction may release locks but may not obtain any new locks.

16. What are the two methods for dealing deadlock problem? (remembering)
The two methods for dealing deadlock problem is deadlock detection and deadlock recovery.

17. Differentiate strict two phase locking protocol and rigorous two phase locking
protocol. (MAY/JUNE 2016) (evaluating)
In strict two phases locking protocol all exclusive mode locks taken by a transaction is held until that
transaction commits.
Rigorous two phase locking protocol requires that all locks be held until the transaction commits.

18. What benefit does strict two-phase locking provide? What disadvantages result?
(remembering) (MAY/JUNE 2012)
Because it produces only cascadeless schedules, recovery is very easy. But the set of schedules
obtainable is a subset of those obtainable from plain two phase locking, thus concurrency is
reduced.

19. List the two commonly used Concurrency Control techniques. (analyzing)(NOV/DEC
2011)
1. Two Phase Locking
2. Serialization

20. List the SQL statements used for transaction control. (analyzing) (NOV/DEC 2011)
COMMIT
ROLLBACK
SAVEPOINT
SET TRANSACTION
SET CONSTRAINT
All transaction control statements, except certain forms of the COMMIT and ROLLBACK
commands, are supported in PL/SQL.

21. Write the ACID properties of Transaction.(APR/MAY 2015) (creating)

Atomicity
Consistency
Isolation
Durability

22. What is meant by concurrency control?(NOV/DEC 2015) (remembering)

Concurrency control is a database management systems (DBMS) concept that is used to address
conflicts with the simultaneous accesses.

23. Give an example of Two phase commit protocol.(NOV/DEC2015) (evaluating)

Two-phase commit is a standard protocol in distributed transactions for achieving ACID
 properties. Each transaction has a coordinator who initiates and coordinates the transaction.
For example, participants will block resource processes while waiting for a message from the
coordinator. If for any reason this fails, the participant will continue to wait and may never resolve its
transaction. Therefore the resource could be blocked indefinitely. On the other hand, a coordinator
will also block resources while waiting for replies from participants. In this case, a coordinator can
also block indefinitely if no acknowledgement is received from the participant.

24. What is serializability?(NOV/DEC 2016) (remembering)

Serializability is the classical concurrency scheme. It ensures that a schedule for executing
concurrent transactions is equivalent to one that executes the transactions serially in some order. It
assumes that all accesses to the database are done using read and write operations.

25. List the four conditions for deadlock.(NOV/DEC 2016) (analyzing)

 Mutual Exclusion
 Hold and Wait
 No Preemption
 Circular Wait

26. Why DBMS needs a concurrency control? NOV 2017

In general, concurrency control is an essential part of Transaction management. It is a
mechanism for correctness when two or more database transactions that access the same data or
data set are executed concurrently with time overlap. According to [Link], if multiple
transactions are executed serially or sequentially, data is consistent in a database. However, if
concurrent transactions with interleaving operations are executed, some unexpected data and
inconsistent result may occur. Data interference is usually caused by a write operation among
transactions on the same set of data in DBMS.

27. What is isolation Level?(Remember)

Isolation levels defines the degree to which a transaction must be isolated from the data
modifications made by any other transaction in the database system.
A transaction isolation level are defined by the following phenomena –

 Dirty Read
 Non-Repatable read
 Phantom Read
Based on these phenomena, The SQL standard defines four isolation levels :
 Read Uncommitted
 Read Committed
 Repeatable Read
 Serializable
28. What do you mean by phantom problem? (Remember)
If transactions operate at less than the maximum isolation level is the so-called phantom problem.

29. What is the need for save points? (Understand)

It might be possible for a transaction to establish intermediate save points while it is executing,
and subsequently to roll back to a previously established save point, if required, instead of having to roll
 back all the way to the beginning.

30. What are the three problems that any concurrency control mechanism must address? (R)
The three problems are:

 The lost update problem

 The uncommitted dependency problem
 The inconsistent analysis problem
31. What is the last update problem? (Understand)
Transaction A retrieves some tuple t at time t1; transaction B retrieves that same tuple t at time t2;
transaction A updates the tuple at time t3; and transaction B updates the same tuple at time t4;
Transaction A’s update is lost at time t4, because transaction B overwrites it with-out even looking at it.

32. What is the uncommitted dependency problem? (Understand)

The uncommitted dependency problem arises if one transaction is allowed to retrieve-or, worse,
update-a tuple that has been updated by another transaction but not yet committed by that other
transaction.

33. Define atomicity and consistency.(Remember)

Atomicity means that you can guarantee that all of a transaction happens, or none of it does; you
can do complex operations as one single unit, all or nothing, and a crash, power failure, error, or
anything else won't allow you to be in a state in which only some of the related changes have
happened.
Consistency means that you guarantee that your data will be consistent; none of the constraints you
have on related data will ever be violated.

34. Define isolation and durability.(Remember)

Isolation means that one transaction cannot read data from another transaction that is not yet
completed. If two transactions are executing concurrently, each one will see the world as if they
were executing sequentially, and if one needs to read data that is written by another, it will have to
wait until the other is finished.
Durability means that once a transaction is complete, it is guaranteed that all of the changes have
been recorded to a durable medium (such as a hard disk), and the fact that the transaction has been
completed is likewise recorded.

35. What is serializable schedule? (Understand) (Apr/May 2017)

A schedule is called serializable whenever executing the transactions sequentially, in some order, could
have left the database in the same state as the actual schedule. Serializability is the commonly accepted
criterion for correctness.

36. What type of locking needed for insert and delete operations?(Understand) (Apr/May 2017)
There are two types of Locks
1. Shared lock
2. Exclusive lock
37. What is meant by log based Recovery?(APR/MAY 2019)
1. The log is a sequence of records. Log of each transaction is maintained in some stable
storage so that if any failure occurs, then it can be recovered from there.
2. If any operation is performed on the database, then it will be recorded in the log.

20 Department of CSBS
 3. But the process of storing the logs should be done before the actual transaction is applied in
the database.
There are two approaches to modify the database:
 Deferred database modification
 Immediate database modification

PART-B
1. a) How Transactions are possible in Distributed database? Explain briefly (8) (remembering)
b) What is Transaction state and its ACID properties? (8) (remembering) [Nov /Dec 2008].
2. a) How can we achieve concurrency control achieved in DBMS through
Serializability? (8) (remembering) [Nov /Dec 2008/April 2008.]
3. a) What is deadlock prevention and dead lock detection method (10) [May 2009]
(remembering)
b) Explain the deadlock recovery technique (6)
4. Explain the following protocols for concurrency control (understanding) [Nov /Dec
2008/April 2008.]
i) Lock based protocols (8)
ii) Time stamp based protocols DATABASE MANAGEMENT SYSTEM (8)
5. Explain the concepts of serializability. (understanding) (APR/MAY 2011) (8)
6. (i) Explain Two-phase locking protocol. (understanding) (APR/MAY 2011) (8)
(ii) Describe about the deadlock prevention schemes. (8)
7. (i) Define a transaction. Then discuss the following with relevant examples:(remembering)
(8)
(1) A read only transaction (NOV/DEC 2011)
(2) A read write transaction
(3) An aborted transaction
(ii) With a neat sketch discuss the states a transaction can be in. (4)
(iii) Explain the distinction between the terms serial schedule and serializable schedule. Give
relevant example. (4) (understanding)
8. Write down in detail about Deadlock and Serializability. (MAY/JUNE 2012) (creating)
(16)
9. Discuss in detail about transaction concepts and two phase commit protocol. (creating) (16)
(NOV/DEC 2012) &(APRIL / MAY 2013)
10. Write down in detail about intent locking and isolation levels.(16) (creating) (NOV/DEC
2012)
11. Illustrate dead lock and conflict serializability with suitable example. (understanding)(APRIL
/ MAY 2013)
12. What is Concurrency? Explain it in terms of locking mechanism and two phase locking
protocol. (NOV/DEC 2014) (remembering)
13. Write a short notes on (i) Transaction Concept (ii) Deadlock (NOV/DEC 2014) (creating)
14. Explain the Two- Phase commit and Three –Phase commit Protocols.(APR/MAY 2015)(16)
(understanding)
15. Consider the following schedules. The actions are listed in the order they are scheduled and
prefixed with the transaction name.

S1:T1:R(X),T2:R(X),T1:W(Y),T2:W(Y),T1:R(Y),T2:R(Y)
S2:T3:W(X),T1:R(X),T1:W(Y),T2:R(Z),T2:W(Z),T3:R(Z)
 For each of the schedule answer the following questions:
(i) What is the precedence graph for the schedule? 2)
(ii) Is the schedule conflict-serializable?If so, what are all the conflict equivalent
serial schedules? (7)
(iii) Is the schedule view – serializable? If so, what are all the view equivalentserial
schedules? (7) (APR/MAY 2015) (evaluating)

16. (i) What is concurrency control? How is it implemented in DBMS? Illustrate with a
suitable example. (remembering)
(ii) Discuss view serializability and conflict serializability. (APR/MAY2 015)(understanding)
17. What is deadlock? How does it occur? How transactions be written to (remembering)
(i) Avoid deadlock.
(ii) Guarantee correct execution. Illustrate with suitable example(APR/MAY 2015)
18. Briefly explain about Two phase commit.(May/June 2016)(Remember)
[Link] about Locking Protocols.(May/June 2016)(understanding)
20.(i) Consider the following two transactions:( Nov/Dec 2016)
T1: read(A);
read(B);
if A = 0 then B := B + 1;
write(B).
T2: read(B);
read(A);
if B = 0 then A := A + 1;
write(A).
Add lock and unlock instructions to transactions T1 and T2, so that they onserve the two-phase
locking [Link] the execution of these transactions result in a deadlock? (creating)
(ii) Consider the following extension to the tree-locking protocol, which allows both shared and
exclusive locks:
• A transaction can be either a read-only transaction, in which case it can request only shared locks,
or an update transaction, in which case it can request only exclusive locks.
• Each transaction must follow the rules of the tree protocol. Read-only transactions may lock any
data item first, whereas update transactions must lock the root first. Show that the protocol ensures
serializability and deadlock freedom. (analyzing)

21.(i) Illustrate two phase locking protocol with an example.(Nov/Dec 2016) (evaluating)
(ii) Outline deadlock handling mechanisms.
22. What is concurrency control? How is it implemented in DBMS? Explain. (Remember)(DEC
2007)
23. Explain various recovery techniques during transaction in detail. (Remember)(MAY 2017)

24. State and explain the lock based concurrency control with suitable
example.(Remember)(NOV/DEC 2017)
25. When does deadlock occurs?Explain two-phase commit protocol with suitable example.

22 Department of CSBS
(NOV/DEC 2017)
27. Explain the catalog information for cost estimation for selection and sorting operation in
database. (Remember)( (NOV/DEC 2017)
28. Discuss the violations caused by each of the following:dirty read,non-repeatable read and
phantoms with suitable example.(Analyse)(Apr/May 2017)
29. Explain why timestamp-based concurrency control allows schedules that are not
[Link] how it can be modified through buffering to disallow such schedules. .
(Remember) (Apr/May 2017)
30. What is meant by semantic query optimization? How does it differe from with
example.(Understand)(Apr/May 2017)
31. Discuss in detail about the testing of serializability.(Analyse)(APR/MAY 2019)
[Link] deferred and immediate modification versions of the log based recovery scheme.
(Remember)( APR/MAY 2019)

PART –C
1. Consider the following two transactions:( Nov/Dec 2016) (analyzing)
T1: read(A);
read(B);
if A = 0 then B := B + 1;
write(B).
T2: read(B);
read(A);
if B = 0 then A := A + 1;
write(A).
Add lock and unlock instructions to transactions T1 and T2, so that they on serve the two-phase
locking [Link] the execution of these transactions result in a deadlock?
[Link] in detail about the ACID properties of a transaction.(Remember) )(APR/MAY 2019)
[Link] is concurrency control? How it is implemented in DBMS?Briefly elaborate with suitable
diagrams and examples. (Remember) (Apr/May 2019)

ASSIGNMENT QUESTIONS

1. Consider the following schedules. The actions are listed in the order they are scheduled and
prefixed with the transaction name.(Remember)
S1:T1:R(X),T2:R(X),T1:W(Y),T2:W(Y),T1:R(Y),T2:R(Y)
S2:T3:W(X),T1:R(X),T1:W(Y),T2:R(Z),T2:W(Z),T3:R(Z)
For each of the schedule answer the following questions:
(i) What is the precedence graph for the schedule? (2)
(ii) Is the schedule conflict-serializable?If so, what are all the conflict equivalent
serial schedules? (7)
(iii) Is the schedule view – serializable? If so, what are all the view equivalent serial
schedules? (7)
[Link] the following extension to the tree-locking protocol, which allows both shared and
exclusive locks.
• A transaction can be either a read-only transaction, in which case it can request only shared locks,
or an update transaction, in which case it can request only exclusive locks. (Remember)
• Each transaction must follow the rules of the tree protocol. Read-only transactions may lock any
data item first, whereas update transactions must lock the root first. Show that the protocol ensures
serializability and deadlock freedom. (Understand)
UNIT-IV IMPLEMENTATION TECHNIQUES
PART-A
1. Draw the storage device hierarchy?(Knowledge)

Storage-device hierarchy

2. Define seek time?(Knowledge)

Seek time is the time to reposition the head and increases with the distance that the head must
move. Seek times can range from 2 to 30 milliseconds. Average seek time is the average of all seek
times and is normally one-third of the worst-case seek time.

3. Define RAID and its types? (Remember) (APRIL / MAY 2013)

RAIDs are Redundant Arrays of Inexpensive Disks. There are six levels of organizing these disks:
 0 -- Non-redundant Striping 
 1 -- Mirrored Disks
 2 -- Memory Style Error Correcting Codes
 3 -- Bit Interleaved Parity
 4 -- Block Interleaved Parity
 5 -- Block Interleaved Distributed Parity
 6 -- P + Q Redundancy
4. What is buffer manager? (Knowledge)
Programs in a DBMS make requests (that is, calls) on the buffer manager when they need a block
from a disk. If the block is already in the buffer, the requester is passed the address of the block in
main memory. If the block in not in the buffer, the buffer manager first allocates space in the buffer
for the block, through out some other block, if required, to make space for the new block. If the
block that is to be thrown out has been modified, it must first be written back to the disk. The
internal actions of the buffer manager are transparent to the programs that issue disk-block requests

5. Differentiate fixed length and variable length records?(Apply)

A file where all the records are of the same length is said to have fixed length records.
Advantage: Access is fast because the computer knows where each record starts. Eg If each record
is 120 bytes long then the 1st record starts at [Start of File] + 0 bytes the 2nd record starts at [Start
of File] + 120 bytes the 3rd record starts at [Start of File] + 240 bytes etc.....
Disadvantage: Using Fixed length records, the records are usually larger and therefore need more
storage space and are slower to transfer (load or save).

One or more of the fields can be of differing lengths in each record, called variable length records
Advantages: the records will be smaller and will need less storage space the records will load faster
Disadvantages: The computer will be unable determine where each record starts processing the
records will be slower.

6. What is heap file and sequential file organization? (Remember)

Heap File Organization
Any record can be placed anywhere in the file. There is no ordering of records and there is a single
file for each relation.
Sequential File Organization
Records are stored in sequential order based on the primary key.

7. Define data dictionary? (Knowledge)

A RDBMS needs to maintain data about the relations, such as the schema. This is stored in a data
dictionary (sometimes called a system catalog):
 Names of the relations
 Names of the attributes of each relation
 Domains and lengths of attributes
 Names of views, defined on the database, and definitions of those views
 Integrity constraints
 Names of authorized users
 Accounting information about users
 Number of tuples in each relation
 Method of storage for each relation (clustered/non-clustered)
 Name of the index
 Name of the relation being indexed
 Attributes on which the index in defined
 Type of index formed

8. Explain indexing and hashing. (Understand)

Indexing mechanisms used to speed up access to desired data. E.g., author catalog in library
Search Key - attribute to set of attributes used to look up records in a file. An index file consists of
records (called index entries) of the form. Index files are typically much smaller than the original
file
Two basic kinds of indices:
Ordered indices: search keys are stored in sorted order
Hash indices: search keys are distributed uniformly across “buckets” using a “hash
function”.

9. What is dense index? (Remember)(APR/MAY 2019)

Dense index — Index record appears for every search-key value in the file.

10. What is sparse index? (Knowledge)

Sparse Index Files
Index records for some search-key values. To locate a record with search-key value K we:
Find index record with largest search-key value < K
Search file sequentially starting at the record to which the index record points
Less space and less maintenance overhead for insertions and deletions. Generally slower than dense
index for locating records.
Good tradeoff: sparse index with an index entry for every block in file, corresponding to least
search-key value in the block.

Example of Sparse Index Files

11. Explain primary and secondary index. (Understand)

Primary index A primary index is an index on a set of fields that includes the unique primary key
for the field and is guaranteed not to contain duplicates. Also Called a Clustered index. eg.
Employee ID can be Example of it.
Secondary index A Secondary index is an index that is not a primary index and may have
duplicates. eg. Employee name can be example of it. Because Employee name can have similar
[Link] primary index contains the key fields of the table. The primary index is automatically
created inthe database when the table is activated. If a large table is frequently accessed such that it
is not possible to apply primary index sorting, you should create secondary indexes for the table.
The indexes on a table have a three-character index ID. '0' is reserved for the primary index.
Customers can create their own indexes on SAP tables; their IDs must begin with Y or Z.

12. What are the 3 alternatives for data entries k* in B+ tree? (Remember)
Data record with key value k
<k, rid of data record with search key value k>
<k, list of rids of data records with search key k>

13. What are the advantages and disadvantages of B+ tree? (Knowledge)

Advantage of B+-tree index files:
Automatic self-reorganization with small, local, changes, in the face of insertions and deletions.
Reorganization of entire file is not required
Disadvantage of B+-trees:
Extra insertion and deletion overhead, space overhead

14. What are the properties of B- Trees? (Remember)

B-tree properties:
each node, in a B-tree of order n :
 key order
 at most n pointers
 at least n/2 pointers (except root)
 all leaves at the same level
 if number of pointers is k, then node has exactly k-1 keys

15. Comparison between static hashing and dynamic hashing? (Analyze)

(APRIL / MAY 2013)(NOV/DEC 2015)
Static hashing
Static hashing uses a h ash function in which the set of bucket adders is fixed. Such hash functions
cannot easily accommodate databases that grow larger over time.
Dynamic hashing
Dynamic hashing allows us to modify the hash function dynamically. Dynamic hashing copes with
changes in database size by splitting and coalescing buckets as the database grows and shrinks.

16. When does bucket split cause directory doubling? (Remember)

Before insert, local depth of bucket = global depth. Insert causes local depth to become > global
depth; directory is doubled by copying it over and `fixing’ pointer to split image page.

17. What is called query processing? (remembering)

Query processing refers to the range of activities involved in extracting data from a database.

18. What are the steps involved in query processing? (remembering)

The basic steps are:
27 Department of CSBS
 Parsing and translation
Optimization and Evaluation

19. Define query optimization. (MAY/JUNE 2016) (remembering)

Query optimization refers to the process of finding g the lowest –cost method of evaluating a given
query.

20. What are the disadvantages of B Tree over B+ Tree? (Knowledge) (NOV/DEC 2016)

Disadvantages of B+ Tree

 This method is less efficient for static tables. 

 Main disadvantage is that performance degrades as file size grows for lookups.

 B+ Tree indexing maintain efficiency despite insertion and deletion of data.

Disadvantages of B Tree

 Leaf and non-leaf nodes are of different size (complicates storage)

 Deletion may occur in a non-leaf node (more complicated)

21. What is mirroring?(Remember)

The simplest approach to introducing redundancy is to duplicate every disk. This
technique is called mirroring.

22. Write the nested-loop join algorithm.(Understand)

For each tuple tr in r do begin
For each tuple ts in s do begin
Test pair (tr, ts) to see if they satisfy the join condition
If they do, add [Link] to the result
End
End
23. Write the block nested-loop join and its algorithm.(Understand)
Block nested-loop join, which is a variant of the nested – loop join where every block of the
inner relation is paired with every block of the outer relation.
For each block Br of r do begin
For each block Bs of s do begin
For each tuple tr in Br do begin
For each tuple ts in Bs do begin
Test pair (tr, ts) to see if they satisfy the join condition
If they do, add [Link] to the result.
End
End
End
End

24. What is an indexed nested-loop join?(Understand)

Indexed nested loop join can be used with existing indices, as well as with temporary indices
created for the sole purpose of evaluating the join.
25. What is a merge join?(Understand)
The merge-join algorithm can be used to compute natural joins and equi-joins.

26. What is a hash-join?(Understand)

The hash-join algorithm can be used to implement natural joins and equi-joins.

27. What is a histogram?(Understand)

In histogram the values for the attribute are divided into a number of ranges, and with each
range the histogram associates the number of tuples whose attribute value lies in that range.

28. What is an index record?(Understand)

An index record, or index entry, consists of a search – key vale and pointers to one or more
records with that value as their search-key value.

29. Define mean time to failure(MTTF)(Remember)

Mean time to failure is a measure of the reliability of the disk. The mean time to failure of
a disk is the amount of time that, on average, we can expect the system to run continuously
without any failure.

30. What is query Execution plan?(Rememeber)(Apr/May 2017)

The Query Execution Plans describe the steps and the order used to access or modify data in the
database. Once you have this information you can identify what parts of the query are slow.

SQL Server can create execution plans in two ways:

 Actual Execution Plan - (CTRL + M) - is created after execution of the query and contains
the steps that were performed
 Estimated Execution Plan - (CTRL + L) - is created without executing the query and
contains an approximate execution plan

Execution plans can be presented in these three ways and each option offers benefits over the other.

 Text Plans
 Graphical Plans
 XML Plans

When beginning to work with execution plans, the graphical plan is usually the easiest place to start
unless your plan is very complex, then the text plans are sometimes easier to read.

31. Which cost component are used most often as the basis for cost function?
(Rememeber)(Apr/May 2017)
The cost component are

29 Department of CSBS
i)Access cost to secondary storage
ii)Memory usage cost
iii)storage cost

32. Define query language? (remembering)

A query is a statement requesting the retrieval of information. The portion of DML that involves
information retrieval is called a query language.

33. What is called query processing? (remembering)

Query processing refers to the range of activities involved in extracting data from a database.

34. What are the steps involved in query processing? (remembering)

The basic steps are:
Parsing and translation
Optimization and Evaluation

35. Define query optimization. (MAY/JUNE 2016) (remembering)

Query optimization refers to the process of finding g the lowest –cost method of evaluating a given
query.

36. State the need for Query Optimization.(APR/MAY 2015) (evaluating)

The Query Optimizer examines all algebraic expressions that are equivalent to the given query
and chooses the one that is estimated to be the cheapest. We can expect an improved efficiency
during the evaluation of the query.
Eg., EMP(Emp#, Name, Salary, Dept$)
DEPT(Dept#, DeptName, Mgr)

37. Why does SQL allow duplicate tuples in a table or in a query result? (NOV/DEC 2015)
(remembering)
SQL does permit duplicate "tuples" in its tables
* Duplicate elimination is an expensive operation.
* The user may want to see duplicate tuples in the result of a query.

38. Mention all operations in files. (Remember) (APR/MAY 2019)

Operations on database files can be broadly classified into two categories
 Update -Update operations change the data values by insertion, deletion, or update.
 Retrieval - Retrieval operations, on the other hand, do not alter the data but retrieve them
after optional conditional filtering.
Creation and deletion of a file, there could be several operations, which can be done on files.
 Open
 Locate
 Read
 Write
 Close
 39. Compare the Sequential access device versus Random access device with an
example.(Analyse)(APR/MAY 2019)
Random Access
Function (DISK) Sequential Access (FILE)
Storage space Disk blocks Volumes
allocation and tracking

Concurrent volume A volume can be A volume can be accessed concurrently by

access accessed concurrently different operations
by different operations

Client restore One session per restore Multiple concurrent sessions access different
operations volumes simultaneously on both the server and
the storage agent. Active versions of client
backup data is collocated in active-data pools.

PART-B

1. a) What is RAID? List the different levels in Raid technology and explain its features.
(Knowledge) (8) (APR/MAY 2011/2019)& (NOV/DEC 2012)(NOV/DEC 2014)
b) Describe the different method of implementing variable length records(Understand) (8)
(APR/MAY 2011)
2. Explain static and dynamic Hashing Techniques? (Understand) (16) (Nov /Dec 2008)
3. Briefly describe about B+ tree index and B tree structure. (Understand) (16) (Nov /Dec 2008)
(NOV/DEC 2014)
4. Explain different properties of indexes in detail.(Remember) (16) (Nov /Dec 2008)
5. Construct B+ tree to insert the following (order of the tree is 3)(Apply) (16)
26, 27, 28, 3, 4, 7, 9, 46, 48, 51, 2, 6. (APRIL / MAY 2013) & (MAY/JUNE 2012)
6. Write down in detailed notes on ordered indices and B – Tree index files. (Understand)
(NOV/DEC 2012)
7. Describe in detail about how records are represented in a file and how to organize them
in a file. (Understand) (MAY/JUNE 2012) (16)
8. Explain magnetic disk with a neat diagram.(Remember)

8. With suitable diagrams, discuss about the Raid Levels(Level 0,Level 1,Level 0+1,Level 3,Level
4 and Level 5) (Create) (APRIL / MAY 2015) (16)
9. (i)What is RAID?List the different level in RAID techonology and explain its features.(Knowledge)
(ii) Illustrate indexing and hashing techniques with suitable examples. (Understand) (APRIL /
MAY 2015)
11. Briefly explain RAID and RAID levels. (Remember) (May/June 2016)
[Link] explain about B+ tree index file with example.(Knowledge) (May/June 2016)
(NOV/DEC 2017)
13.(i)Explain the architecture of a distributed database system.(Nov/Dec 2016) , (NOV/DEC 2017)
(Remember)
(ii) Explain the concept of RAID. (Remember)
 14. Explain the distinction between static and dynamc [Link] the relative merites of each
technique in database applications. (Remember) (NOV/DEC 2017)
15. Explain what a RAID system [Link] does it improve performance and [Link] the
level 3 and level 4 of RAID.(Remember)(Apr/May 2017)
16. Describe the structure of B+tree and give the algorithm for search in the
B+tree with example.(Understand)(APR/MAY 2019)

PART-C

1. Construct B+ tree to insert the following (order of the tree is 3) (Apply)

26, 27, 28, 3, 4, 7, 9, 46, 48, 51, 2, 6.
2. Illustrate indexing and hashing techniques with suitable examples. (Understand)
[Link] benefits and drawbacks of a source driven architecture for gathering of data at a
datawarehouse, as compared to a destination driven architecture. (Understand)
4. Briefly explain about Query processing. (evaluating)(May/June 2016)
5. Consider two relations R1(A, B, C) and R2( C, D, E) that have the following properties: R1 has
20,000 tuples, R2 has 45,000 tuples. 25 tuples of R1 fit in one block and 30 tuples of R2 fit in one
block. Estimate the number of block transfers and seeks required, using each of the following join
strategies for R1 join R2. .(Analyze)
(i) Nested-loop join
(ii) Block nested-loop join
(iii) Merge join
(iv) Hash join

ASSIGNMENT QUESTIONS

[Link] about the Join order optimization and Heuristic optimization algorithms. (creating)
(APR/MAY2015) (16)
[Link] query optimization with an example.(Remember)(Nov/Dec 2016)

UNIT V

ADVANCED TOPICS

1. What is external RAID?(understand)

An External RAID is a data storage technology that enables the user to connect two or more hard
drives in the machine. It makes the array look like a single volume for better storage and superior
performance.
2. What is data external storage?(understand)
External storage, in computing terms, refers to all of the addressable data that is not stored on a
drive internal to the system. It can be used as a backup, to store achieved information or to transport
data. External storage is not part of a computer's main memory or storage, hence it is called secondary
or auxiliary storage.

3. Define File organizations?(understand)

File Organization refers to the logical relationships among various records that constitute the file,
particularly with respect to the means of identification and access to any specific record. In simple terms,
Storing the files in certain order is called file Organization.
4. What is indexing and hashing?(understand)
Indexing uses data reference that holds the address of the disk block with the value corresponding to
the key while hashing uses mathematical functions called hash functions to calculate direct locations of
data records on the disk. Hence, this is also a major difference between indexing and hashing.

5. What is B + tree and B – tree indexing files?(Analyse)

B + Tree :
The B+ tree is a balanced binary search tree. It follows a multi-level index format.
In the B+ tree, leaf nodes denote actual data pointers. B+ tree ensures that all leaf nodes remain at the same
height.
In the B+ tree, the leaf nodes are linked using a link list. Therefore, a B+ tree can support random access as
well as sequential access.
B- Tree:
B Tree is a specialized m-way tree that can be widely used for disk access. A B-Tree of order m
can have at most m-1 keys and m children. One of the main reason of using
B tree is its capability to store large number of keys in a single node and large key values by keeping the
height of the tree relatively small.
A B tree of order m contains all the properties of an M way tree. In addition, it contains the following
properties.
Every node in a B-Tree contains at most m children.
Every node in a B-Tree except the root node and the leaf node contain at least m/2 children.
The root nodes must have at least 2 nodes.
All leaf nodes must be at the same level.
6. What is Hashing?(understand)
Bucket − A hash file stores data in bucket format. Bucket is considered a unit of storage. A bucket
typically stores one complete disk block, which in turn can store one or more records.

Hash Function − A hash function, h, is a mapping function that maps all the set of search-
keys K to the address where actual records are placed. It is a function from search keys to bucket
addresses.
7. Difference between static and dynamic ?(understand)
Static Hashing :
In static hashing, when a search-key value is provided, the hash function always computes the same
address. For example, if mod-4 hash function is used, then it shall generate only 5 values. The output
address shall always be same for that function. The number of buckets provided remains unchanged at all
times.
Dynamic Hashing :
The problem with static hashing is that it does not expand or shrink dynamically as the size of the
database grows or shrinks. Dynamic hashing provides a mechanism in which data buckets are added and
removed dynamically and on-demand. Dynamic hashing is also known as extended hashing.
[Link] is query processing?
Query Processing includes translations on high level Queries into low level expressions that can
be used at physical level of file system, query optimization and actual execution of query to get the
actual result.
9. What is query optimization?(understand)
Query optimization is of great importance for the performance of a relational database, especially
for the execution of complex SQL statements. A query optimizer decides the best methods for
implementing each query.
The query optimizer selects, for instance, whether or not to use indexes for a given query, and which join
methods to use when joining multiple tables. These decisions have a tremendous effect on SQL
performance, and query optimization is a key technology for every application, from operational Systems
to data warehouse and analytical systems to content management systems.

10. What is MONGO DB?(understand)

MongoDB is an open-source document-oriented database that is designed to store a large scale of
data and also allows you to work with that data very efficiently. It is categorized under the NoSQL (Not
only SQL) database because the storage and retrieval of data in the MongoDB are not in the form of
tables.

[Link] is Data model MONGO DB?(understand)

Data modeling in MongoDB is different from structured query language databases, in SQL
databases we have defined the structure of database and tables but in MongoDB, there is no need to define
any structure of database or [Link] data models are basically divided into two types i.e.
normalized data model and embedded data model. Based on the structure and requirement we can use the
data models, at the time of database creation.

[Link] is CURD operation?(understand)

A CRUD operations act as the foundation of any computer programming language or technology.
So before taking a deeper dive into any programming language or technology, one must be proficient in
working on its CRUD operations

PART - B
[Link] about RAID Models and its types ?(understand)
[Link] about Indexing and Hashing?(understand)
[Link] Tree? Discuss about B + Tree and B – Tree indexing files?(Create)
[Link] about Hashing ?(understand)
[Link] about Query processing and Query Optimization ?(understand)
[Link] about the MONGO DB ?(understand)
[Link] about the architecture of MONGO DB ?(understand)
[Link] about the CURD operation?(understand)
 PART -C

[Link] we want to search 65 in the below B+ tree structure. First we will fetch for the
intermediary node which will direct to the leaf node that can contain record for 65. So we find
branch between 50 and 75 nodes in the intermediary node. Then we will be redirected to the third
leaf node at the end. Here DBMS will perform sequential search to find 65. Suppose, instead of 65,
we have to search for 60. What will happen in this case? .(Remember)

No insertions /update/delete is allowed during the search in B+ tree. Discuss it.(Analyze)