Database Engineering

Model Question-3

1. a) What are rules of Normalization?

Normalization is a process used in database design to eliminate data redundancy
and inconsistencies by organizing data into tables with well-defined relationships.
The rules of normalization are often referred to as normal forms. The most
commonly used normal forms are:

1. First Normal Form (1NF): Each table in a database must have a primary key
and each column must contain atomic values, i.e., indivisible data elements.
2. Second Normal Form (2NF): A table is in 2NF if it is in 1NF and all non-key
attributes are fully dependent on the primary key.
3. Third Normal Form (3NF): A table is in 3NF if it is in 2NF and all non-key
attributes are independent of each other.
4. Boyce-Codd Normal Form (BCNF): A table is in BCNF if it is in 3NF and every
determinant is a candidate key.
5. Fourth Normal Form (4NF): A table is in 4NF if it is in BCNF and it has no
multi-valued dependencies.
6. Fifth Normal Form (5NF): A table is in 5NF if it is in 4NF and it has no join
dependencies.

Note that not all databases need to be fully normalized to 5NF, and in some cases, it
may not be practical or necessary to do so. The level of normalization required
depends on the specific needs and requirements of the database application.

b) What do you mean by union operation in RDBMS?

In a Relational Database Management System (RDBMS), the union operation is used
to combine the result sets of two or more SELECT statements into a single result set.
The resulting set contains all the distinct rows that appear in either one of the SELECT
statements or both.
c) Define FD?
In database management systems, a functional dependency is a relationship between
two attributes or columns in a table, where the value of one attribute uniquely
determines the value of another attribute. In other words, if attribute A determines
attribute B, then any given value of A will always correspond to a unique value of B.

For example, in a table that contains information about employees, the employee ID
may uniquely determine the employee name. In this case, we can say that there is a
functional dependency between the employee ID and the employee name.

Functional dependencies are important in database design, because they help to

ensure data integrity and minimize redundancy. By identifying functional
dependencies between attributes, we can organize data into more efficient and
logical tables, reducing the need for redundant data and improving data accuracy.

d). Define fourth normal form?

Ans. A relation will be in 4NF if it is in Boyce Codd normal form and has no multi-valued
dependency.
Fourth Normal Form (4NF) is a level of database normalization that is achieved when
a database table has no multi-valued dependencies.

In other words, a table is in 4NF if and only if it is in 3NF and has no non-trivial multi-
valued dependencies.

A multi-valued dependency occurs when a relationship exists between attributes

such that for a given value of one attribute, there are multiple values for another
attribute.
For example, let's say we have a table of orders, where each order can have multiple
products. If we store the products in a comma-separated list within a single column,
we have a multi-valued dependency. To bring this table to 4NF, we would split the
products into a separate table and establish a relationship between the orders and
the products tables.

4NF is considered an advanced level of normalization and is not always necessary for
all databases. However, it can be useful in certain situations to avoid data duplication
and improve data consistency.

e) What are the two techniques to prevent deadlock?

Ans. 1. Mutual Exclusion

Mutual section from the resource point of view is the fact that a resource can never be
used by more than one process simultaneously which is fair enough but that is the main
reason behind the deadlock. If a resource could have been used by more than one process
at the same time, then the process would have never been waiting for any resource.
However, if we can be able to violate resources behaving in the mutually exclusive manner
then the deadlock can be prevented.
2. Hold and Wait
Hold and wait condition lies when a process holds a resource and waiting for some other
resource to complete its task. Deadlock occurs because there can be more than one
process which are holding one resource and waiting for other in the cyclic order.
However, we must find out some mechanism by which a process either doesn't hold any
resource or doesn't wait. That means, a process must be assigned all the necessary
resources before the execution starts. A process must not wait for any resource once the
execution has been started.

f) What is transaction atomicity?

Ans. The second reason listed for using transactions was atomicity. Atomicity means that
multiple operations can be grouped into a single logical entity, that is, other threads of
control accessing the database will either see all of the changes or none of the changes.

g) What is domain relational calculus? Explain with example?

Ans. Domain Relational Calculus is a non-procedural query language equivalent in power
to Tuple Relational Calculus. Domain Relational Calculus provides only the description of
the query, but it does not provide the methods to solve it. In Domain Relational Calculus,
a query is expressed as,

{< x1, x2, x3, ..., xn > | P (x1, x2, x3, ..., xn ) }

h) Explain insert into, delete form, select form.

Ans. INSERT INTO table2
SELECT * FROM table1.
WHERE condition.

DELETE FROM table_name WHERE condition.

SELECT *FROM student where name .

i) What is lossless join?

Ans. A lossless join is a property of a database schema that ensures that no information
is lost when combining two or more tables using a join operation. In other words, a lossless
join ensures that no redundant data is introduced when tables are joined, and that all the
original data can be retrieved from the joined table.
 2. a) What are the differentiate between external and internal conceptual schema?
External and internal conceptual schemas are two different views of a database that
serve different purposes.

1. External Conceptual Schema: The external conceptual schema, also known as

the external schema or user schema, is the representation of the database as
seen by the users or applications that interact with it. It describes the portion
of the database that is relevant to a particular user or group of users. The
external schema is defined at a higher level of abstraction than the internal
schema and is designed to simplify the user's view of the database by hiding
unnecessary details.
2. Internal Conceptual Schema: The internal conceptual schema, also known as
the internal schema or storage schema, is the representation of the database
as seen by the database management system (DBMS). It describes how the
data is physically stored on disk and how it is accessed and managed by the
DBMS. The internal schema is defined at a lower level of abstraction than the
external schema and is designed to optimize the performance of the database
by providing a detailed view of the storage and retrieval mechanisms.

In summary, the external schema is focused on providing a simplified and user-

friendly view of the database, while the internal schema is focused on optimizing the
performance and efficiency of the database management system.

b) Explain the difference between relational algebra and relational calculus?

Relational algebra and relational calculus are two formal languages used in the field
of relational database management systems (RDBMS) for manipulating and querying
data stored in a relational database.

Relational algebra is a procedural language that defines a set of operations that can
be performed on relations (tables) in a database. These operations include selections,
projections, joins, unions, and differences, among others. The results of these
operations are also relations, which can be further manipulated with other
operations. Relational algebra is used to write queries to extract data from a
database, and it is often used as the foundation for query languages such as SQL.

Relational calculus, on the other hand, is a non-procedural language that defines the
properties of a relation that should be retrieved, without specifying how to retrieve
them. It provides a way to describe the desired data without specifying the
algorithmic steps to compute it. There are two types of relational calculus: tuple
calculus and domain calculus. Tuple calculus specifies the desired result in terms of
the tuples that should be retrieved, while domain calculus specifies the desired result
in terms of the attributes and values that should be retrieved. Relational calculus is
mainly used for theoretical purposes, such as proving the correctness of a database
design or analyzing the properties of a database.

In summary, relational algebra is a procedural language used to manipulate data in a

database, while relational calculus is a non-procedural language used to describe the
desired data without specifying how to retrieve it.

3. What is the difference between BCNF and 3rd normal form? Explain with example?
Both BCNF (Boyce-Codd Normal Form) and 3NF (Third Normal Form) are
normalization techniques used in relational database design to reduce data
redundancy and ensure data integrity. While they have some similarities, there
are also some key differences between them.

BCNF is a higher level of normalization than 3NF, and it eliminates a specific type
of dependency called a functional dependency. A functional dependency exists
when one or more columns (attributes) in a table determine the values of one or
more other columns in the same table. BCNF states that every non-trivial
functional dependency in a table should be a dependency on a candidate key,
which means that any column that is not a part of a candidate key should be
functionally dependent on the key.

On the other hand, 3NF eliminates transitive dependencies, which occur when
a non-key attribute is functionally dependent on another non-key attribute,
rather than on the primary key of the table. In 3NF, all non-key attributes
should be dependent only on the primary key, and not on any other non-key
attributes.
4. a) Explain the advantages and disadvantages of network, hierarchical and relational
data model?
Network, hierarchical, and relational data models are three different ways to organize
and represent data in a database. Each of these models has its own advantages and
disadvantages.

1. Network Data Model Advantages:

 The network data model allows for more complex relationships between data,
such as many-to-many relationships.
 The network model allows for more efficient querying of complex data
structures. Disadvantages:
 The network data model is complex and difficult to understand and
implement.
 The network model is not well-suited to handle ad hoc queries or changes to
the data structure.
2. Hierarchical Data Model Advantages:
 The hierarchical data model is simple and easy to understand and implement.
 The hierarchical model is well-suited to handle data that has a clear and
consistent parent-child relationship. Disadvantages:
 The hierarchical data model is inflexible and cannot handle data with more
complex relationships.
 The hierarchical model can result in redundancy and data duplication.
3. Relational Data Model Advantages:
 The relational data model is flexible and can handle many different types of
data relationships.
 The relational model is well-suited to handle ad hoc queries and changes to
the data structure. Disadvantages:
 The relational data model can be complex and difficult to understand and
implement.

b) What is an object-oriented database? What are its advantages over relational database?
An object-oriented database (OODB) is a type of database that stores data in the
form of objects, which are instances of classes defined in an object-oriented
programming language.

The primary advantage of OODBs over relational databases is that they allow for
more flexible and natural representation of complex data structures, such as those
found in object-oriented programming. OODBs support complex data types and
relationships, and can represent complex data in a more natural way, avoiding the
need for mapping between object-oriented programming constructs and relational
database tables.

Other advantages of OODBs include:

 Support for inheritance, which allows for more efficient data modeling and
reuse of code.
  Ability to store complex data types, including multimedia and spatial data.
 Faster retrieval of complex data structures.
 Better support for complex queries and transactions.
 Support for encapsulation, which can improve security and maintainability.

However, OODBs also have some disadvantages. They are typically more complex
and difficult to implement than relational databases, and may require more
specialized skills to design and maintain. Additionally, they may not be as widely
supported or compatible with existing tools and technologies.

5.a) What sql construct enables the definition of a relation? What construct allow the
modification of relational instances?
In SQL, the construct that enables the definition of a relation is the CREATE TABLE statement. This
statement is used to create a new table in a database, and define the columns or attributes of the
table, as well as any constraints on the data that will be stored in it.

For example, the following SQL statement creates a table named "employees" with columns for
the employee ID, first name, last name, and salary:

The construct that allows the modification of relational instances is the UPDATE statement. This
statement is used to modify existing rows in a table by setting new values for one or more
columns.

Other constructs that allow modification of relational instances include the INSERT statement,
which adds new rows to a table, and the DELETE statement, which removes rows from a table.

b) Explain about inner join and outer join?

Inner join and outer join are two types of operations that can be used to combine
data from two or more tables in a relational database.

Inner join is a type of join that returns only the rows that have matching values in
both tables. In other words, it returns the intersection of the two tables. For example,
if you have two tables: Customers and Orders, you can use an inner join to get a
result set that contains only the customers who have placed an order.

Outer join, on the other hand, is a type of join that returns all the rows from one
table and the matching rows from the other table. If there is no match in the other
table, it will return null values for the columns of that table. There are three types of
outer join: left outer join, right outer join, and full outer join.

 Left outer join returns all the rows from the left table and the matching rows
from the right table. If there is no match in the right table, it will return null
values for the columns of that table.
 Right outer join returns all the rows from the right table and the matching
rows from the left table. If there is no match in the left table, it will return null
values for the columns of that table.
 Full outer join returns all the rows from both tables, and if there is no match in
either table, it will return null values for the columns of the other table.

To summarize, inner join returns only the matching rows between two tables, while
outer join returns all the rows from one table and the matching rows from the other
table (with null values for the non-matching rows).

5. a) Draw the transition diagram and discuss the typical states that a transaction
through during execution?
Assuming you are referring to a transaction in the context of a database system, a
transaction typically goes through several states during its execution. Here is a brief
overview of the typical states of a transaction, along with a transition diagram:

1. Active state: A transaction starts in the active state when it begins execution.
In this state, the transaction is actively reading or modifying data in the
database.
2. Partially committed state: Once the transaction has completed its work and is
ready to commit its changes, it moves to the partially committed state. In this
state, the transaction has executed all of its operations and has locked the
data it has modified, but has not yet been permanently saved to the database.
3. Committed state: Once the transaction has been successfully written to the
database, it enters the committed state. In this state, the transaction is
considered complete and all of its changes are now permanent.
 4. Failed state: If a transaction encounters an error during execution, it moves to
the failed state. In this state, any changes made by the transaction are rolled
back and the transaction is terminated.
5. Aborted state: If the database system fails during transaction execution or if a
deadlock occurs, the transaction enters the aborted state. In this state, any
changes made by the transaction are rolled back and the transaction is
terminated.

Here is a transition diagram that summarizes the typical states and transitions of a
transaction:

b) Explain indexing?
In computing, indexing refers to the process of creating an index or a searchable
database that enables users to quickly and efficiently retrieve data from a large
collection of information. Indexing is commonly used in database management
systems, search engines, file systems, and other applications that require fast and
efficient data retrieval.

The process of indexing typically involves creating an index of keywords, phrases, or

other relevant data that can be used to quickly identify and retrieve specific
information. This index may be stored separately from the original data, and it is
often organized in a way that makes it easier for users to locate the information they
need.

Indexing can help to speed up data retrieval by allowing users to search for
information using specific keywords or criteria, rather than having to manually sift
through large amounts of data. This can be especially useful in situations where time
is a critical factor, such as in scientific research, financial analysis, or other areas
where quick access to information is essential.

Overall, indexing plays a crucial role in many different computing applications, and it
is an essential tool for efficiently managing and retrieving large amounts of data.

6. a) What is B-tree? Construct b+ tree of order 1 with following keys

1,9,5,3,7,11,17,13,15.
7. A B-tree is a type of data structure that is commonly used in computer science
for organizing and storing large amounts of data. B-trees are particularly
useful in database systems and file systems, where they can be used to
efficiently locate and retrieve data.
 8. A B+ tree is a variant of the B-tree that is commonly used in database systems.
In a B+ tree, all data is stored in the leaf nodes of the tree, while the non-leaf
nodes are used to index the data.

9. To construct a B+ tree of order 1 with the keys 1, 9, 5, 3, 7, 11, 17, 13, 15, we
start by creating a root node, which is also a leaf node.

[1, 9, 5, 3, 7, 11, 17, 13, 15]

Since the order of the tree is 1, each node can hold at most one key. Since there
are 9 keys in the list, we need to split the root node into two nodes, and promote
the middle key (7) to become the new root.
[7]
/ \
[1, 3, 5] [9, 11, 13, 15, 17]

The resulting tree has a height of 2, and all leaf nodes are at the same level. Each leaf
node contains at most one key, and the keys are stored in sorted order.

Note that in a B+ tree, the non-leaf nodes do not store the actual data, but only
serve as an index to the data stored in the leaf nodes. The leaf nodes are linked
together in a linked list to allow for efficient range queries.

b) What is tuple calculus?

Tuple calculus is a formal language used to specify relational queries in a relational
database management system (RDBMS). It is a type of declarative language, which
means that users describe what they want the database to do, rather than how to do
it.

Tuple calculus is based on the concept of tuples, which are ordered sets of values
that represent a single row of a table in a database. The language is used to specify
queries that retrieve data from the database based on certain conditions.

There are two main types of tuple calculus: the tuple relational calculus (TRC) and the
domain relational calculus (DRC). TRC is based on tuples, while DRC is based on
domains. Both languages use a set of operators to specify the conditions for the
queries, such as selection, projection, and join operations.

Tuple calculus is often used by database administrators and developers to create

complex queries that retrieve data from multiple tables in a database. It provides a
powerful and flexible way to access data, and can be used with various RDBMS
platforms.