BICTE: DBMS

1. What is relational algebra? Explain select, project, and rename operation with
algebra expression.
Relational Algebra is procedural query language, which takes relation as input and generate
relation as output. Relational algebra mainly provides theoretical foundation for relational
databases and SQL. It is used in the design of transaction and forms the conceptual basis
for SQL
The fundamental operations in the relational algebra are :
• Select,
• project,
• union,
• set difference,
• Cartesian product and
• rename.
Select(σ) operation:
Purpose: To select required tuples(rows) of the relations which satisfies a given condition. It is
denoted by sigma (σ).
▪ Syntax: condition relation
▪ Example: List all staff with a salary greater than 10,000.
σ salary > 10000 (Staff)
Where ,
σ (Select predicate)
r(relation) – Staff
p(proposition logic) – salary>10000
Result- returning the list of staff whose salary is greater than 10000
▪ Project(π) Operation
Purpose: To project or display or get the required column data from the relation. It is
denoted by symbol pi(π).
▪ Syntax:  attribute list (Relation)
▪ Example: Find name and salary
 name, salary (employee)

▪ Rename((ρ) Operation
▪ Purpose: To rename the output relation. It is denoted by rho (ρ).
▪ Syntax: ρ(RelationNew, RelationOld)
▪ Example: We can use the rename operator to rename STUDENT relation to STUDENT1.
ρ(STUDENT1, STUDENT)
2. What is relational algebra? Describe the types of join operations with example.
Relational Algebra is procedural query language, which takes relation as input and generate
relation as output. Relational algebra mainly provides theoretical foundation for relational
databases and SQL. It is used in the design of transaction and forms the conceptual basis
for SQL.

Full Join
Right Join
Natural Join(⋈)
Natural Join is a join which is performed if there is a common attribute between the relations. It does not
use any comparison operator for join condition. It joins the table only when the two tables have at least one
common attribute with same name and domain.
Syntax : R1 ⋈ R2
where R1 and R2 are two relations.
Example: Suppose,We have two tables of Student(S_id, Name, Class, Age, C_id) and Courses(C_id,
C_name). Now, we will perform natutral join on both the tables i.e

Outer Join:
In an outer join, along with tuples that satisfy the matching criteria, we also include some or all
tuples that do not match the criteria. So, the problem of data loss can be overcome by this outer
join
There are 3 types of outer join:
✓ Left outer join(A B)
✓ Right outer join( A B)
✓ Full outer join ( A B)
Example:
3. Write about Hierarchical, Network and Relational data model.
Data model:
▪ A Data model defines the logical design and structure of a database and defines how data
will be stored, accessed and updated in a database management system. It is a collection of
concepts and rules for implementing Abstraction and Instances and Schema
▪ Some of the data models in DBMS are:
▪ Hierarchical Model
▪ Network Model
▪ Relational Model
▪ E-R Model
▪ Object oriented data Model
▪ Hierarchical Model: This database model organizes data into a tree-like-structure, with a
single root, to which all the other data is linked. The hierarchy starts from the Root data,
and expands like a tree, adding child nodes to the parent nodes.

▪ In hierarchical model, data is organized into tree-like structure with one one-to-many
relationship between two different types of data, for example, one department can have
many courses, many professors and of-course many students.
▪ Advantages of Hierarchical model:
▪ Fast and efficient data retrieval
▪ Easy to add and delete information
▪ Good performance
▪ Efficient storage of data
Network Model:
▪ This model is designed with the concept of parents and children with their features and
relationship. In this model data is organized more like a graph, and are allowed to have
more than one parent node. Represents both one-to-many and many-to-many relationship.

▪ In network we can have multiple relationship with other. This feature makes the network
Data Model more flexible. This network models are mainly used on a large digital
computers.
▪ Advantages of Network Model:
▪ Capability of handling more relationship types
▪ Ease of data access
▪ Conceptual simplicity
▪ Data integrity
Relational database Model:
Relational model is the most popular model and the most extensively used model. In this model
the data can be stored in the tables and this storing is called as relation, the relations can be
normalized and the normalized relation values are called atomic values. Each row in a relation
contains unique value and it is called as tuple, each column contains value from same domain and
it is called as attribute.
 Advantages:
• Ease of use because of rows an columns
• High security
• Data accuracy
• Flexibility
4. Why database recovery is needed? Suggest the best recovery technique.
Database recovery is the process of restoring the database to a correct (consistent) state in
the event of a failure.
Database Recovery is needed in DBMS
• to ensure data consistency,
• integrity, and availability in the face of various types of failures and errors.
The various database recovery technique are:
✓ Recovery techniques baes on Deferred Update
✓ Recovery techniques based on immediate Update
✓ Shadow paging
✓ Recovery in multi database transactions
✓ Log based recovery
In my opinion,
Shadow paging database recovery technique is the best as it offers the various
advantages:
✓ No need for log records.
✓ No undo/ Redo algorithm.
✓ Recovery is faster
Shadow Paging is a recovery technique that provides atomicity and durability in database system.
Shadow Paging recovery technique is also known as On-Write Technique. Shadow Paging
maintains 2-page table. A current page table and shadow page table. When a transaction begins,
all the entries of the current page table are copied to the shadow page table and the current table is
updated for each write operation. The shadow page is never changed during the life of the
transaction. When the current transaction is committed, the shadow page entry becomes a copy of
the current page table entry and the disk block with the old data is released. The shadow page table
is stored in non-volatile memory. If the system crash occurs, then the shadow page table is copied
to the current page table.
The above figure illustrates the concepts of shadow and current directories. For pages updated by
the transaction, two versions are kept. The old version is referenced by the shadow directory and
the new version by the current directory.
5. What is ER-Diagram? Draw the ER-Diagram for car insurance company
Entity Relationship Diagram, also known as ERD, ER Diagram or ER model, is a type of
structural diagram for use in database design. ER Diagram is a visual representation of data
that describes how data is related to each other.

Fig: ER-Diagram for Car insurance company

6. Describe integrity constraint.

Integrity constraints are a set of rules. It is used to maintain the quality of information.
Integrity constraints ensure that the data insertion, updating, and other processes have to
be performed in such a way that data integrity is not affected. Thus, integrity constraint is
used to guard against accidental damage to the database.

Types of Integrity Constraint:

Domain Constraint:

Domain constraints can be defined as the definition of a valid set of values for an attribute.

For example:

Entity Integrity Constraint

The entity integrity constraint states that primary key value can't be null. This is because
the primary key value is used to identify individual rows in relation and if the primary key
has a null value, then we can't identify those rows.

For example:

Referential integrity: A referential integrity constraint is specified between two tables. In

the Referential integrity constraints, if a foreign key in Table 1 refers to the Primary Key
of Table 2, then every value of the Foreign Key in Table 1 must be null or be available in
Table 2.
o Key constraint: Keys are the entity set that is used to identify an entity within its entity
set uniquely.

Compiled by: LB Gurung