Two Marks Questions with Answers

Q.1 What is Database Management System? Why do we need a DBMS? AU: May-05, Dec- 08

Ans:

• A Database Management System (DBMS) is collection of interrelated data and various programs that are used to
handle the data.

• The primary goal of DBMS is to provide a way to store and retrieve the required information from the database in
convenient and efficient manner.

Q.2 What is the purpose of database management system?

Ans:The purpose of database management system is -

• Define the structure for storage of information.

• Provide mechanism for manipulation of information.

• In addition, the database systems must ensure the safety of information stored.

Q.3 List any two advantages of database systems.

Ans. Following are the advantages of DBMS-

1) DBMS removes the data redundancy that means there is no duplication of data in database.

2) DBMS allows to retrieve the desired data in required format.

3) Data can be isolated in separate tables for convenient and efficient use.

4) Data can be accessed efficiently using a simple query language.

Q.4 Define data abstraction. AU: May-05

Ans:Data abstraction means retrieving only required amount of information of the system and hiding background
details.

Q.5 What are three levels of data abstraction?

Ans:The three levels of data abstraction are -

1. Physical Level

2. Logical Level

3. View Level

Q.6 Is it possible for several attributes to have same domain? Illustrate your answer with SLO suitable example.
AU: Dec- 04, 15

Ans:A domain is the set of legal values that can be assigned to an attribute. Each attribute in a database must have a
well-defined domain; we can't mix values from different domains in the same attribute. Hence it is not possible for
several attributes to have same domain.

For example - Student domain has attributes RollNo, Name, Address. Similarly Employee domain has EmpID,
Ename,Salary,Address. We can not define the same domain for defining several attributes.

Q.7 Write the characteristic that distinguish the database approach with File based approach. AU: May-15, Dec.-
16,19
OR What are main differences between file processing system and a DBMS ? AU: May-06, Dec.-06

Ans: Refer section 1.2

Q.8 Discuss briefly three major disadvantages of keeping organizational information in a file processing system.

Ans: Refer section 1.2

1) Data redundancy: Data redundancy means duplication of data at several places. Since different programmers
create different files and these files might have different structures, there are chances that some information may
appear repeatedly in some or more format at several places.
2) Data inconsistency: Data inconsistency occurs when various copies of same data may no longer get matched. For
example changed address of an employee may be reflected in one department and may not be available (or old
address present) for other department.
3) Difficulty in accessing data: The conventional file system does not allow to retrieve the desired data in efficient
and convenient manner.
4) Data isolation: As the data is scattered over several files and files may be in different formats, it becomes to
retrieve the desired data from the file for writing the new application.

Q.9 What is data model? AU: Dec.-11, May-19

Ans:

• It is a collection of conceptual tools for describing data, relationships among data, bro semantics (meaning) of data
and constraints.

• Data model is a structure below the database.

Q.10 What are different types of data models?

Ans:Various types of data models are –

(1) Relational Data Model

(2) Entity Relational Data Model

(3) Object Based Data Model

(4)Semi-structured Data Model

Q.11 Name the categories of SQL commands.

Ans: The categories of SQL commands are -

(1) Data Definition Language (DDL)

(2) Data Manipulation Language (DML)

(3) Data Control Language (DCL)

Q.12 What is data definition language? Give example.

Ans:

• Data Definition Language (DDL) is a specialized language used to specify a mondatabase schema by a set of
definitions.

• It is a language which is used for creating and modifying the structures of tables, views, indexes and so on.

• Some of the common commands used in DDL are -CREATE, ALTER, DROP.

Q.13 Give brief description of DCL command.

Ans: DCL stands for Data Control Language. It includes commands such as GRANT and REVOKE which mainly deals
with the rights, permissions and other controls of the database system.

Q.14 Define the term tuple.

Ans: Tuple means a row present in the table

Q.15 Why does SQL allow duplicate tuples in a table or in a query result?

Ans:

• Data can be the same. Two people may have the same name. Since SQL is a database where you store your data
and data can be duplicate.

• But we can apply primary key constraints, Unique constraints or Distinct keyword to identify the record uniquely

Q.16 Why key is essential? Write the different types of keys.

Ans:

• Keys are used to specify the tuples distinctly in the given relation.

• Various types of keys used in relational model are - Superkey, Candidate Keys, primary keys, foreign keys.

Q.17 Define primary key. Give example.

Ans:

• The primary key is a candidate key chosen by the database designer to identify the tuple in the relation uniquely.

• For example - Consider a Student database as Student (RollNo,Name,Address). The primary key for this database is
RollNo. The primary is underlined.
Q.18 Define foreign key. Give example.

Ans:

• Foreign key is a single attribute or collection of attributes in one table that refers to the primary key of other table.

• For example - Consider a Student database as Student (RollNo,Name,Address) and Course(CourseId, CourseName,
RollNo). Here RollNo is a foreign key

Q.19 What is the difference between primary key and foreign key?

Ans:

Q.20 What is referential integrity?

Ans:

• The referential integrity rule states that "whenever a foreign key value is used it must reference a valid, existing
primary key in the parent table".

• Example: Consider the situation where you have two tables : Employees and Managers. The Employees table has a
foreign key attribute entitled ManagedBy, which points to the record for each employee's manager in the Managers
table.

Q.21 What is domain integrity? Give example. AU: Dec.-08

Ans: Domain integrity ensures that all the data items in a column fall within a defined set of valid values. Each
column in a table has a defined set of values, such as the set of all numbers for zip (five-digit), the set of all character
strings for name.

Q.22 What are different types of integrity constraints used in designing relational databases? AU: Dec.-07

Ans:Different types of integrity constraints are

(1) Entity Integrity Constraint

(2) Referential Integrity Constraint

(3) Domain Integrity Constraint

(4) Key Integrity Constraint

Q.23 List the reasons why null value might be introduced into the database. AU: May-06

Ans: NULL is a special value provided by database in two cases -

i) When field values of some tuples are unknown(For e.g. city name is not assigned and

ii) inapplicable(For e.g. middle name is not present).

Q.24 List various operators used in relational algebra.

Ans:Various operators used in Relational algebra are

(1) Selection Operator

(2) Projection Operator(II)

(3) Cartesian Product(x).

(4) Rename Operator (ρ)

Q.25 Describe briefly any two undesirable properties that a database design may have.

Ans: The two undesirable properties that a database design may have

(1)Repetition of data

(2) In-ability of representation of certain information in database.

Q.26 Specify with suitable examples, the different types of keys used in database management system.

Ans: Refer section 1.10

1) Super Key(SK)

2) Candidate Key(CK)

3) Primary Key(PK)

4) Alternate Key

5) Foreign key

Q.27 Define data independence.

Ans: Data independence is an ability by which one can change the data at one level without affecting the data at
another level. Here level can be physical, conceptual or external.

Q.28 Distinguish between physical and logical data independence.

Ans: Refer section 1.6

1. Physical Independence: This is a kind of data independence which allows the modification of physical schema
without requiring any change to the conceptual schema. For example - if there is any change in memory size of
database server then it will not affect the logical structure of any data object.
2. Logical Independence: This is a kind of data independence which allows the modification of conceptual schema
without requiring any change to the external schema. For example - Any change in the table structure such as
addition or deletion of some column does not affect user views.

By these data independence the time and cost acquired by changes in any one level can be reduced and abstract
view of data can be provided to the user.

Q.29 What is meant by instance and Schema of the database?

Ans:

• When information is inserted or deleted from the database then the database gets changed. The collection of
information at particular moment is called instances.

• The overall design of the database is called schema.

Q.30 Differentiate between Dynamic SQL and Static SQL. AU: Dec.-14,15,16,17, May-15

Ans:

Q.31 List any eight applications of DBMS. AU: May-19

Ans: Refer section 1.1

1) Accounting: Database systems are used in maintaining information employees, salaries, and payroll taxes.
2) Manufacturing: For management of supply chain and tracking production of items in factories database systems
are maintained.
3) For maintaining customer, product and purchase information the databases are used.
4) Banking: In banking sector, for customer information, accounts and loan and for performing banking applications
the DBMS is used.
5) For purchase on credit cards and generation of m`onthly statements database systems are useful.
6) Universities: The database systems are used in universities for maintaining student information, course
registration, and accounting.
7) Reservation systems: In airline/railway reservation systems, the database is used to at maintain the reservation
and schedule information.
8) Telecommunication: In telecommunications for keeping records of the calls made, generating monthly bills,
maintaining balances on prepaid calling cards, and storing information about communication networks the database
systems are used.

Q.32 Expression in Relational algebra, the division operation(/) using the project, cartesian product and minus
operations. Give a simple example. AU: Dec.-19
Ans: Let, R(A, B) and S(B) be two relations. R÷ S is denoted as - ΠA(R) - ΠA (ΠA (R) × S - R)

Two Marks Questions with Answers

Q.1 Explain entity relationship model.
Ans.
• The ER data model specifies enterprise schema that represents the overall logical structure of a database.
• The E-R model is very useful in mapping the meanings and interactions of real- 19 world entities onto a conceptual
schema.
Q.2 Give the limitations of E-R model? How do you overcome this?
Ans:
1) Loss of information content: Some information be lost or hidden in ER model
2) Limited relationship representation: ER model represents limited relationship as compared to another data
models like relational model etc.
3) No representation of data manipulation: It is difficult to show data manipulation in ER model.
4) Popular for high level design: ER model is very popular for designing high level design.
Q.3 List the design phases of Entity Relationship model.
Ans:
1) Requirement Analysis 2) Conceptual Database Design
3) Logical Database Design 4) Schema Refinement
5) Physical Database Design 6) Application and Security Design.
Q.4 What is an entity?
Ans: • An entity is an object that exists and is distinguishable from other objects.
• For example - Student named "Poonam" is an entity and can be identified by her name. Entity is represented as a
box, in ER model.
Q.5 What do you mean by derived attributes?
Ans:
• Derived attributes are the attributes that contain values that are calculated from other attributes.
• To represent derived attribute there is dotted ellipse inside the solid ellipse. For example -Age can be derived from
attribute DateOfBirth. In this situation, DateOfBirth might be called Stored Attribute.
Q.6 What is a weak entity? Give example.
Ans: Refer section 2.3.4
A weak entity is an entity that cannot be uniquely identified by its attributes alone. The entity set which does not
have sufficient attributes to form a primary key is called as weak entity set.

Q.7 What are the problems caused by redundancy? AU: Dec.-16, May-18
Ans:Problems caused by Redundancy: Following problems can be caused by redundancy -
i) Redundant Storage: Some information is stored repeatedly.
ii) Update Anomalies: If one copy of such repeated data is updated then inconsistency is created unless all other
copies are similarly updated.
iii) Insertion Anomalies: Due to insertion of new record repeated information get added to the relation schema.
iv) Deletion Anomalies: Due to deletion of particular record some other important information associated with the
deleted record get deleted and thus we may lose Isbom some other important information from the schema.
Q.8 Define functional dependency. AU: Dec 04,05, May 05,14,15
Ans: Let P and Q be sets of columns, then : P functionally determines Q, written P→ Q if and only if any two rows
that are equal on (all the attributes in) P must be equal on (all the attributes in) Q.
In other words, the functional dependency holds if
T1.P = T2.P, then T1.Q=T2.Q
Where notation T1.P projects the tuple T1 onto the attribute in P.
Q.9 Why certain functional dependencies are called trivial functional dependencies? AU: May-06,12
Ans:
• A functional dependency FD: XY is called trivial if Y is a subset of X. This kind of dependency is called trivial because
it can be derived from common sense. If one "side" is a subset of the other, it's considered trivial. The left side is
considered the determinant and the right the dependent.
• For example - {A,B} -> B is a trivial functional dependency because B is a subset of A,B. Since (A,B) -> B includes B,
the value of B can be determined. It's a trivial functional dependency because determining B is satisfied by its
relationship to A,B
Q.10 Define normalization. AU: May-14
Ans: Normalization is the process of reorganizing data in a database so that it meets two basic requirements:
1) There is no redundancy of data (all data is stored in only one place), and
2) Data dependencies are logical (all related data items are stored together)
Q.11 State anomalies of 1NF. AU: Dec.-15
Ans: All the insertion, deletion and update anomalies are in 1NF relation.
Q.12 What is multivalued dependency? AU: Dec.-06
Ans: A table is said to have multi-valued dependency, if the following conditions are true,
1) For a dependency A → B, if for a single value of A, multiple values of B exists, then the table may have multi-values
dependency.
2) Also, a table should have at-least 3 columns for it to have a multi-valued dependency.
3) And, for a relation R(A,B,C), if there is a multi-valued dependency between, A and B, then B and C should be
independent of each other.
Q.13 Describe BCNF and describe a relation which is in BCNF.
Ans: Refer section 2.12.
Boyce and Codd Normal Form is a higher version of the Third Normal form. This form deals with certain type of
anomaly that is not handled by 3NF.
A 3NF table which does not have multiple overlapping candidate keys is said to be in BCNF.
Or in other words, For a table to be in BCNF, following conditions must be satisfied:
i) R must be in 3rd Normal Form
ii) For each functional dependency (X → Y), X should be a super Key. In simple words if Y is a prime attribute then X
can not be non prime attribute.
Q.14 Why 4NF in normal form is more desirable than BCNF?
Ans:
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 A redundancy is reduced.
Q.15 Give an example of a relation schema R and set of dependencies such that R is in BCNF but not in 4NF. AU:
May-12
Ans: Consider relation R (A,B,C,D) with dependencies
AB→C
ABC→D
AC→B
Here the only key is AB. Thus each functional dependency has superkey on the left. But MVD has non-superky on its
left. So it is not 4NF.
Q.16 Show that if a relation is in BCNF, then it is also in 3NF. Ans:AU: Dec.-12
• Boyce and Codd Normal Form is a higher version of the Third Normal form.
• A 3NF table which does not have multiple overlapping candidate keys is said to ove be in BCNF. When the table is
in BCNF then it doesn't have partial functional dependency as well as transitive dependency.
• Hence it is true that if relation is in BCNF then it is also in 3NF.
Q.17 Why it is necessary to decompose a relation? AU: May-07
Ans:
• Decomposition is the process of breaking down one table into multiple tables.
• The decomposition is used for eliminating redundancy.
Q.18 Explain atleast two desirable properties of decomposition. AU: May-03,17,19, Dec.-05
Ans:There are two properties associated with decomposition and those are -
1) Loss-less Join or non Loss Decomposition: When all information found in the original database is preserved after
decomposition, we call it as loss less or nonloss decomposition.
2) Dependency Preservation: This is a property in which the constraints on the wied original table can be maintained
by simply enforcing some constraints on each of the smaller relations.
Q.19 Explain with simple example lossless join decomposition. AU: May-03
Ans: Refer section 2.10.1.
i) Union of attributes of R1 and R2 must be equal to attribute of R. Each attribute of R must be either in R1 or in R2.
Att(R1) U Att(R2) = Att(R)
ii) Intersection of attributes of R1 and R2 must not be NULL.
Att(R1) Att(R2) ≠ Φ
iii)Common attribute must be a key for at least one relation (R1 or R2)
Att(R1) Att(R2) -> Att(R1)
Att (R1) Att (R2) -> Att (R2)
Q.20 Define the terms entity set and relationship set. AU: May-19
Ans: Refer section 2.1.2.
Entity set: The entity set is a set of entities of the same types. For example - All students studying in class X of the
School. The entity set need not be disjoint. Each entity in entity set have the same set of attributes and the set of
attributes will distinguish it from other entity sets. No other entity set will have exactly the same set of attributes.

Relationship Sets: Relationship is an association among two or more entities.

The relationship set is a collection of similar relationships. For example - Following Fig. 2.1.2 shows the relationship
works for for the two entities Employee and Departments.
The association between entity sets is called as participation. That is, the entity sets E1, E2,..., En participate in
relationship set R.
The function that an entity plays in a relationship is called that entity's role.
Q.21 'Boyce-Codd normal form is found to be stricter than third normal form Justify the statement. AU: Dec.-19
Ans.:
(i) Every relation which is in BCNF is also in 3NF but every relation which is in 3NF is not necessarily be in BCNF.
(ii) BCNF non-transitionally depends on individual candidate key but there is no such requirement in 3NF.
Hence BCNF is stricter than 3NF.
Q.22 What is the significance of "participation role name" in the description of relationship types? AU: Dec.-19
Ans: Each entity type that participates in a relationship type plays a particular role in the relationship. The role name
signifies the role that a participating entity of an entity plays in each relationship instance. In PREPARED BY
relationship type, EMPLOYEE plays the role of document creator and voucher plays the role of document created.
Entity TEACHER and Entity STUDENT are related with a relationship TEACHER-teach-STUDENT. The teaches is a
participating role in the entity set TEACHER and STUDENT.
Two Marks Questions with Answers
Q.1 What is a transaction? AU: May-04, Dec.05
Ans: A transaction can be defined as a group of tasks that form a single logical unit.
Q.2 What does time to commit mean? AU: May-04
Ans:
• The COMMIT command is used to save permanently any transaction to database.
• When we perform, Read or Write operations to the database then those changes can be undone by rollback
operations. To make these changes permanent, we should make use of commit
Q.3 What are the various properties of transaction that the database system maintains to ensure integrity of
data. AU: Dec.04
OR
Q.4 What are ACID properties? AU: May-05,06,08,13,15, Dec-07,14,17
Ans: In a database, each transaction should maintain ACID property to meet the consistency and integrity of the
database. These are
(1) Atomicity (2) Consistency (3) Isolation (4) Durability
Q.5 Give the meaning of the expression ACID transaction. AU: Dec.08
Ans: The expression ACID transaction represents the transaction that follows the ACID Properties.
Q.6 State the atomicity property of a transaction. AU: May-09,13
Ans:This property states that each transaction must be considered as a single unit and must be completed fully or
not completed at all.
No transaction in the database is left half completed.
Q.7 What is meant by concurrency control ? AU: Dec.15
Ans:
A mechanism which ensures that simultaneous execution of more than one transactions does not lead to any
database inconsistencies is called concurrency control mechanism.
Q.8 State the need for concurrency control. AU: Dec.17
OR
Q.9 Why is it necessary to have control of concurrent execution of transactions? How is it made possible? AU:
Dec.-02
Ans: Following are the purposes of concurrency control-
• To ensure isolation
• To resolve read-write or write-write conflicts
• To preserve consistency of database
Q.10 List commonly used concurrency control techniques. AU: Dec.11
Ans: The commonly used concurrency control techniques are -
i) Lock
ii) Timestamp
iii) Snapshot Isolation
Q.11 What is meant by serializability? How it is tested? AU: May-14,18, Dec.-14,16
Ans.: Serializability is a concept that helps to identify which non serial schedule and find the transaction equivalent
to serial schedule.
It is tested using precedence graph technique.
Q.12 What is serializable schedule? AU: May-17
Ans.: The schedule in which the transactions execute one after the other is called serial schedule. It is consistent in
nature. For example : Consider following two transactions T1 and T2

All the operations of transaction T1 on data items A and then B executes and then in transaction T2 all the operations
on data items A and B execute. The R stands for Read operation and W stands for write operation.
Q.13 When are two schedules conflict equivalent? AU: Dec.08
Ans.: Two schedules are conflict equivalent if :
• They contain the same set of the transaction.
• every pair of conflicting actions is ordered the same way.
For example -
Schedule S2 is a serial schedule because, in this, all operations of T1 are performed before starting any operation of
T2. Schedule S1 can be transformed into a serial schedule by swapping non-conflicting operations of S1.
Hence both of the above the schedules are conflict equivalent.
Q.14 Define two phase locking. AU: May-13
Ans.: The two phase locking is a protocol in which there are two phases:
i) Growing Phase (Locking Phase): It is a phase in which the transaction may obtain locks but does not release any
lock.
ii) Shrinking Phase (Unlocking Phase): It is a phase in which the transaction may release the locks but does not
obtain any new lock.
Q.15 What is the difference between shared lock and exclusive lock? AU: May-18
Ans.:

Q.16 What type of lock is needed for insert and delete operations. AU: May-17
Ans.: The exclusive lock is needed to insert and delete operations.
Q.17 What benefit does strict two-phase locking provide? What disadvantages result? AU: May-06,07, Dec.07
Ans.:
Benefits:
1. This ensure that any data written by an uncommitted transaction are locked in exclusive mode until the
transaction commits and preventing other transaction from reading that data.
2. This protocol solves dirty read problem.
Disadvantage:
1. Concurrency is reduced.
Q.18 What is rigorous two phase locking protocol ? AU: Dec.-13
Ans.: This is stricter two phase locking protocol. Here all locks are to be held until the transaction commits.
Q.19 Differentiate strict two phase locking and rigourous two phase locking protocol. AU: May-16
Ans. :
• In Strict two phase locking protocol all the exclusive mode locks be held until the transaction commits.
• The rigourous two phase locking protocol is stricter than strict two phase locking protocol. Here all locks are to be
held until the transaction commits
Q.20 Define deadlock. AU: May-08,09,14
Ans.: Deadlock is a situation in which when two or more transactions have got a lock and waiting for another locks
currently held by one of the other transactions.
Q.21 List four conditions for deadlock. AU: Dec-16
Ans.: 1. Mutual exclusion condition
2. Hold and wait condition
3. No preemption condition
4. Circular wait condition
Q.22 Why is recovery needed? AU: May-09
Ans. :
• A recovery scheme that can restore the database to the consistent state that existed before the failure.
• Due to recovery mechanism, there is high availability of database to its users.
Q.23 What are states of transaction? AU: May-19
Ans.: Various states of transaction are - (1) Active, (2) Partially Committed (3) Failed (4) Aborted (5) Committed.
Q.24 What is meant by log based recovery?
Ans.: Log is a most commonly used data structure for recording the modifications that can be made to actual
database.
Log based recovery is a technique in which a log of each transaction is maintained in some stable storage so that if
failure occurs then it can be recovered from there.
Q.25 List the responsibilities of a DBMS has whenever a transaction is submitted to the system for execution.
AU: Dec.-19
Ans.: The system is responsible for making sure that - (1) Either all the operations in the transaction are completed
successfully and effect is recorded permanently in the database. (2) The transaction, has no effect whatsoever on the
database or on the database or on any other transaction.
Q.26 Brief any two violations that may occur if a transaction executes a lower isolation level than serializable.
AU: Dec.-19
Ans.: (1) For non-repeatable Read the phantom read is allowed.
For read committed non-repeatable reads and phantom reads are allowed.
Two Marks Questions with Answers

Q.1 What is the need for RAID? AU: May-13

Ans.: Refer section 4.1

• RAID is a technology that is used to increase the performance.
• It is used for increased reliability of data storage.
• An array of multiple disks accessed in parallel will give greater throughput than a single disk.
• With multiple disks and a suitable redundancy scheme, your system can stay up and running when a disk fails, and
even while the replacement disk is being installed and its data restored.
Q.2 Define Software and hardware RAID systems. AU: May-16

Ans.: Hardware RAID: The hardware-based array manages the RAID subsystem independently from the host. It
presents a single disk per RAID array to the host. Software RAID: Software RAID implements the various RAID levels
in the kernel disk code. It offers the cheapest possible solution, as expensive disk controller cards.

Q.3 What are ordered indices? AU: June-09, Dec. -11,17, May-14

Ans.: This is type of indexing which is based on sorted ordering values. Various ordered indices are primary indexing,
secondary indexing.

Q.4 What are the two types of ordered indices? AU: Dec.-06

Ans.: Two types of ordered indices are - Primary indexing and secondary indexing. The primary indexing can be
further classified into dense indexing and sparse indexing and single level indexing and multilevel indexing.

Q.5 Give the comparison between ordered indices and hashing. AU: Dec-06

Ans.:

(1) If range of queries are common, ordered indices are to be used.

(2) The buckets containing records can be chained in sorted order in case of ordered indices.

(3) Hashing is generally better at retrieving records having a specified value of the key.

(4) Hash function assigns values randomly to buckets. Thus, there is no simple notion of "next bucket in sorted
order."

Q.6 What are the causes of bucket overflow in a hash file organization?

Ans.:Bucket overflow can occur for following reasons -

(1) Insufficient buckets: For the total number of buckets there are insufficient number of buckets to occupy.

(2) Skew: Some buckets are assigned more records than are others, so a bucket might overflow even while other
buckets still have space. This situation is known as bucket skew.

Q.7 What can be done to reduce the occurrences of bucket overflows in a hash file organization? AU: May-07,
June-09, Dec.-12

Ans.:

(1) A bucket is a unit of storage containing one or more records (a bucket is typically a disk block).

(2) The file blocks are divided into M equal-sized buckets, numbered bucket0, bucket... bucketM-1. Typically, a
bucket corresponds to one (or a fixed number of) disk block.

(3) In a hash file organization we obtain the bucket of a record directly from its search- key value using a hash
function, h (K).

(4) To reduce overflow records, a hash file is typically kept 70-80% full.

(5) The hash function h should distribute the records uniformly among the buckets; otherwise, search time will be
increased because many overflow records will exist.

Q.8 Distinguish between dense and sparse indices. AU: May-08, June-09

Ans.:Refer section 4.7.

1) Dense index:
• An index record appears for every search key value in file.

• This record contains search key value and a pointer to the actual record.

2) Sparse index:

• Index records are created only for some of the records.

• To locate a record, we find the index record with the largest search key value less than or equal to the search key
value we are looking for.

• We start at that record pointed to by the index record, and proceed along the pointers in the file (that is,
sequentially) until we find the desired record

Q.9 When is it preferable to use a dense index rather than a sparse index? Explain your answer. AU: Dec. -11

Ans.: 1. It is preferable to use a dense index instead of a sparse index when the file is not sorted on the indexed field.

2. Or when the index file is small compared to the size of memory.

Q.10 How does B-tree differs from a B+ tree? Why is a B+ tree usually preferred as an access structure to a data
file? AU: Dec.-08

Ans.: Refer section 4.9.

Q.11 What are the disadvantages of B tree over B+ tree? AU: Dec.-16

Ans.:

(1) Searching of a key value becomes difficult in B-tree as data can not be found in the leaf node.

(2) The leaf node can not store linked list and thus wastes the space.

Q.12 Mention different hashing techniques. AU: May-12

Ans.: Two types of hashing techniques are - i) Static hashing ii) Dynamic hashing.

Q.13 List the mechanisms to avoid collision during hashing. AU: Dec.-16

Ans.: Collision Resolution techniques are: (1) Separate chaining

(2) Open addressing techniques: (i) Linear probing (ii) Quadratic probing
Q.14 What is the basic difference between static hashing and dynamic hashing? AU: May-13, Dec.-14,15

Ans.: Refer section 4.12.

Q.15 What is the need for query optimization? AU: May-15

Ans.: Query optimization is required for fast execution of long running complex

Q.16 Which cost component are used most commonly as the basis for cost function. AU: May-17, Dec.-19

Ans.: Disk access or secondary storage access is considered most commonly as a basis for cost function.

Q.17 What is query execution plan? AU: May-17

Ans.: To specify fully how to evaluate a query, we need not only to provide the relational-algebra expression, but
also to annotate it with instructions specifying how to evaluate each operation. This annotated structure is called
query execution plan.

Q.18 Mention all the operations of files.

Ans. Various file operations are - (1) Creation of file (2) Insertion of data (3) Deletion of data (4) Searching desired
data from the file. ba

Q.19 Define dense index. AU: May-19

Ans.:Refer section 4.7.

• An index record appears for every search key value in file.
• This record contains search key value and a pointer to the actual record.
• For example:
Q.20 How do you represent leaf node of a B+ tree of order p?
Ans.: To retrieve all the leaf pages efficiently we have to link them using page pointers. The sequence of leaf pages is
also called as sequence set. Refer Fig. 4.8.1.

Two Marks Questions with Answers

Q.1 Define distributed database management system. AU: May-08,18, Dec.-16
Ans.: A distributed database system consists of loosely coupled sites (computer) that share no physical components
and each site is associated a database system.
Q.2 What are two approaches to store a relation in the distributed database? AU: May-04
Ans.: (1) Replication: System maintains multiple copies of data, stored in different sites, for faster retrieval and fault
tolerance.
(2) Fragmentation: Relation is partitioned into several fragments stored in distinct sites.
Q.3 What are various fragmentations? State various fragmentations with example. AU: Dec.-17
Ans.: There are two types of fragmentations - Horizontal fragmentation and vertical fragmentation.
Example - Refer section 5.1.3.2.

• Horizontal fragmentation: In this approach, each tuple of r is assigned to one or more fragments. If relation R is
fragmented in r1 and r2 fragments, then to bring these fragments back to R we must use union operation. That
means R=r1ur2
• Vertical fragmentation: In this approach, the relation r is fragmented based on one or more columns. If relation R
is fragmented into r1 and r2 fragments using vertical fragmentation then to bring these fragments back to original
relation R we must use join operation. That means R= r1 r2
Q.4 What are the advantages of distributed databases?
Ans.:
(1) There is fast data processing as several sites participate in request processing.
(2) Reliability and availability of this system is high.
(3) It possess reduced operating cost.
(4) It is easier to expand the system by adding more sites.
(5) It has improved sharing ability and local autonomy.
Q.4 List out the reasons for development of distributed databases.
Ans.: Following are the reasons for development of distributed databases –
(1) To control the data present at geographically different sites.
(2) To obtain highly available and reliable data processing systems.
Q.5 Difference between homogeneous and heterogeneous schema.
Ans. :

Q.6 What are the advantages of fragmentation?

Ans.: (1) It allows parallel processing on fragments of a relation.
(2) It allows a relation to be split so that tuples are located where they are most frequently accessed.
Q.7 Give an example of two phase commit protocol. AU: Dec.-15
Ans.: Refer section 5.1.4.3.
• The atomicity is an important property of any transaction processing. What is this atomicity property? This
property means either the transaction will execute completely or it won't execute at all.
• The commit protocol ensures the atomicity across the sites in following ways -
i) A transaction which executes at multiple sites must either be committed at all the sites, or aborted at all the sites.
ii) Not acceptable to have a transaction committed at one site and aborted at another.
• There are two types of important sites involving in this protocol -
• One Coordinating site
• One or more participating sites.