DATA BASE MANAGEMENT SYSTEM

MODULE 1 :
1) DEFINE DATABASE AND DBMS ? - 3 MARKS
What is Database?
A database is a systematic collection of data. They support electronic
storage and manipulation of data. Databases make data management
easy.

OR

A database is an organized collection of data, so that it can be easily

accessed and managed. You can organize data into tables, rows,
columns, and index it to make it easier to find relevant information.

Database handlers create a database in such a way that only one set of
software program provides access of data to all the users.

The main purpose of the database is to operate a large amount of

information by storing, retrieving, and managing data.

There are many dynamic websites on the World Wide Web nowadays
which are handled through databases. For example, a model that checks
the availability of rooms in a hotel. It is an example of a dynamic website
that uses a database.

There are many databases available like MySQL, Sybase, Oracle,

MongoDB, Informix, PostgreSQL, SQL Server, etc.

Modern databases are managed by the database management system

(DBMS).

SQL or Structured Query Language is used to operate on the data

stored in a database. SQL depends on relational algebra and tuple
relational calculus.
What is a Database Management System (DBMS)?
Database Management System (DBMS) is a collection of programs that
enable its users to access databases, manipulate data, report, and
represent data. It also helps to control access to the database. Database
Management Systems are not a new concept and, as such, had been first
implemented in the 1960s.

OR

Database management System is software which is used to store and

retrieve the database. For example, Oracle, MySQL, etc.; these are some
popular DBMS tools.

o DBMS provides the interface to perform the various operations

like creation, deletion, modification, etc.
o DBMS allows the user to create their databases as per their
requirement.
o DBMS accepts the request from the application and provides
specific data through the operating system.
o DBMS contains the group of programs which acts according to the
user instruction.
o It provides security to the database.

2) ADVANTAGES AND DISADVANTAGES OF DBMS ?

Advantage of DBMS :

Controls redundancy :It stores all the data in a single database file, so it
can control data redundancy.

Data sharing : An authorized user can share the data among multiple
users.

Backup : It providesBackup and recovery subsystem. This recovery

system creates automatic data from system failure and restores data if
required.
Multiple user interfaces :It provides a different type of user interfaces
like GUI, application interfaces.

Disadvantage of DBMS :

Size :It occupies large disk space and large memory to run efficiently.

Cost : DBMS requires a high-speed data processor and larger memory to

run DBMS software, so it is costly.

Complexity : DBMS creates additional complexity and requirements.

3) DBMS VS FILE PROCESSING SYSTEM ?

What is File System?
A File Management system is a DBMS that allows acces to single files or
tables at a time. In a File System, data is directly stored in set of files. It
contains flat files that have no relation to other files (when only one
table is stored in single file, then this file is known as flat file).

DBMS vs. File System

There are following differences between DBMS and File system:

DBMS File System

DBMS is a collection of data. In File system is a collection of data. In this

DBMS, the user is not required system, the user has to write the procedures
to write the procedures. for managing the database.

DBMS gives an abstract view of File system provides the detail of the data
data that hides the details. representation and storage of data.

DBMS provides a crash recovery File system doesn't have a crash mechanism,
mechanism, i.e., DBMS protects i.e., if the system crashes while entering some
the user from the system failure. data, then the content of the file will lost.

DBMS provides a good It is very difficult to protect a file under the

protection mechanism. file system.

DBMS contains a wide variety of File system can't efficiently store and retrieve
sophisticated techniques to the data.
store and retrieve the data.
DBMS takes care of Concurrent In the File system, concurrent access has
access of data using some form many problems like redirecting the file while
of locking. other deleting some information or updating
some information.

Advantages of DBMS over File system : //ESSAY

Data redundancy and inconsistency –
Redundancy is the concept of repetition of data i.e. each data
may have more than a single copy. The file system cannot
control redundancy of data as each user defines and maintains
the needed files for a specific application to run. There may be
a possibility that two users are maintaining same files data for
different applications. Hence changes made by one user does
not reflect in files used by second users, which leads to
inconsistency of data. Whereas DBMS controls redundancy by
maintaining a single repository of data that is defined once
and is accessed by many users. As there is no or less
redundancy, data remains consistent.
Data sharing –
File system does not allow sharing of data or sharing is too
complex. Whereas in DBMS, data can be shared easily due to
centralized system.
Data concurrency –
Concurrent access to data means more than one user is
accessing the same data at the same time. Anomalies occur
when changes made by one user gets lost because of changes
made by other user. File system does not provide any
procedure to stop anomalies. Whereas DBMS provides a
locking system to stop anomalies to occur.
Data searching –
For every search operation performed on file system, a
different application program has to be written. While DBMS
provides inbuilt searching operations. User only have to write
a small query to retrieve data from database.
Data integrity –
There may be cases when some constraints need to be applied
on the data before inserting it in database. The file system
does not provide any procedure to check these constraints
automatically. Whereas DBMS maintains data integrity by
enforcing user defined constraints on data by itself.
System crashing –
In some cases,systems might have crashes due to various
reasons. It is a bane in case of file systems because once the
system crashes, there will be no recovery of the data that’s
been lost. A DBMS will have the recovery manager which
retrieves the data making it another advantage over file
systems.
Data security –
A file system provides a password mechanism to protect the
database but how longer can the password be protected?No
one can guarantee that. This doesn’t happen in the case of
DBMS. DBMS has specialized features that help provide
shielding to its data.

4) EXPLAIN THE ROLE OF DATA BASE ADMINISTRATOR IN

DBMS ?
A database administrator (DBA) is a person or group in charge of
implementing DBMS in an organization. The DBA job requires a high
degree of technical expertise. DBA consists of a team of people rather
than just one person.
The primary role of Database administrator is as follows −
Database design
Performance issues
Database accessibility
Capacity issues
Data replication
Table Maintenance
Responsibilities of DBA :
The responsibilities of DBA are as follows −
Makes the decision concerning the content of the database.
Plans the storage structure and access strategy.
Provides the support to the users.
Defines the security and integrity checks.
Interpreter backup and recovery strategies.
Monitoring the performance and responding to the changes in the
requirements.
5) EXPLAIN DATABASE MODELS IN DBMS ?
#DATA MODELS :
A data model is a collection of conceptual tools for describing the
structure (which includes data type,relationships and constraints that
should hold on data) of a database.

#DATABASE MODEL :
A Database 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.

*Hierarchical Model
*Network Model
*Entity-relationship Model
*Relational Model
*Object-Oriented Data Model
*Object-Relational Data Model

1) Hierarchical Model :
This database model organises data into a tree-like-structure, with a
single root, to which all the other data is linked. The heirarchy starts
from the Root data, and expands like a tree, adding child nodes to the
parent nodes. In this model, a child node will only have a single parent
node. This model efficiently describes many real-world relationships
like index of a book, recipes etc.

2) Network Model :
This is an extension of the Hierarchical model. In this model data is
organised more like a graph, and are allowed to have more than one
parent node.In this database model data is more related as more
relationships are established in this database model. Also, as the data is
more related, hence accessing the data is also easier and fast. This
database model was used to map many-to-many data relationships.

3) Entity-relationship Model :
In this database model, relationships are created by dividing object of
interest into entity and its characteristics into attributes. E-R Models
are defined to represent the relationships into pictorial form to make it
easier for different stakeholders to understand.

4) Relational Model :
In this model, data is organised in two-dimensional tables and the
relationship is maintained by storing a common field.The basic
structure of data in the relational model is tables. All the information
related to a particular type is stored in rows of that table.Hence, tables
are also known as relations in relational model.

5) Object-Oriented Data Model :

The real-world problems are more closely represented through the
object-oriented data model. In this model, both the data and
relationship are present in a single structure known as an object. We
can store audio, video, images, etc in the database which was not
possible in the relational model. In this model, two are more objects
are connected through links. We use this link to relate one object to
other objects.

6) Object-Relational Model :
As the name suggests it is a combination of both the relational model
and the object-oriented model. This model was built to fill the gap
between object-oriented model and the relational model.
6) EXPLAIN DATABASE USERS ? - 3 MARKS OR 5 MARKS
#DATABASE USERS
*Naive
*System Analyst
*Sophisticated Users
*Application Program
*Specialized users
1) Naive :
Parametric End Users are the unsophisticated who don’t have
any DBMS knowledge but they frequently use the data base
applications in their daily life to get the desired results.
2) System Analyst :
System Analyst is a user who analyzes the requirements of
parametric end users. They check whether all the requirements
of end users are satisfied.
3) Sophisticated Users :
Sophisticated users can be engineers, scientists, business
analyst, who are familiar with the database. They can develop
their own data base applications according to their
requirement. They don’t write the program code but they
interact the data base by writing SQL queries directly through
the query processor.
4) Application Program :
Application Program are the back end programmers who writes
the code for the application programs.They are the computer
professionals. These programs could be written in
Programming languages such as Visual Basic, Developer, C,
FORTRAN, COBOL etc.
5)Specialized users
Specialized users are sophisticated users who write specialized
database applications that do not fit into the traditional data-
processing framework. Among these applications are
computer-aided design systems, knowledge base and expert
systems, systems that store data with complex data types (for
example, graphics data and audio data), and environment-
modelling systems.
7) EXPLAIN 3 SCHEMA ARCHITECTURE OF DBMS ? - //ESSAY
(N.B)
Three schema Architecture :
o The three schema architecture is also called ANSI/SPARC
architecture or three-level architecture.
o This framework is used to describe the structure of a specific
database system.
o The three schema architecture is also used to separate the
user applications and physical database.
o The three schema architecture contains three-levels. It breaks
the database down into three different categories.

The three-schema architecture is as follows:

In the above diagram:

o It shows the DBMS architecture.

o Mapping is used to transform the request and response
between various database levels of architecture.
o Mapping is not good for small DBMS because it takes more
time.
 o In External / Conceptual mapping, it is necessary to transform
the request from external level to conceptual schema.
o In Conceptual / Internal mapping, DBMS transform the
request from the conceptual to internal level.

1. Internal Level :

o The internal level has an internal schema which describes the

physical storage structure of the database.
o The internal schema is also known as a physical schema.
o It uses the physical data model. It is used to define that how the
data will be stored in a block.
o The physical level is used to describe complex low-level data
structures in detail.

2. Conceptual Level :

o The conceptual schema describes the design of a database at the

conceptual level. Conceptual level is also known as logical level.
o The conceptual schema describes the structure of the whole
database.
o The conceptual level describes what data are to be stored in the
database and also describes what relationship exists among
those data.
o In the conceptual level, internal details such as an
implementation of the data structure are hidden.
o Programmers and database administrators work at this level.

3. External Level :

o At the external level, a database contains several schemas that

sometimes called as subschema. The subschema is used to
describe the different view of the database.
o An external schema is also known as view schema.
o Each view schema describes the database part that a particular
user group is interested and hides the remaining database from
that user group.
 o The view schema describes the end user interaction with
database systems.

8) EXPLAIN DATA INDEPENDENCE ?

Data Independence :
o Data independence can be explained using the three-schema
architecture.
o Data independence refers characteristic of being able to modify
the schema at one level of the database system without altering
the schema at the next higher level.

There are two types of data independence:

1. Logical Data Independence

o Logical data independence refers characteristic of being able to

change the conceptual schema without having to change the
external schema.
o Logical data independence is used to separate the external level
from the conceptual view.
o If we do any changes in the conceptual view of the data, then
the user view of the data would not be affected.
o Logical data independence occurs at the user interface level.

2. Physical Data Independence

o Physical data independence can be defined as the capacity to

change the internal schema without having to change the
conceptual schema.
o If we do any changes in the storage size of the database system
server, then the Conceptual structure of the database will not be
affected.
o Physical data independence is used to separate conceptual levels
from the internal levels.
o Physical data independence occurs at the logical interface level.
9) EXPLAIN DATA BASE LANGUAGES ?

Database Language :

o A DBMS has appropriate languages and interfaces to

express database queries and updates.
o Database languages can be used to read, store and
update the data in the database.

Types of Database Language :

1. Data Definition Language

o DDL stands for Data Definition Language. It is used to define

database structure or pattern.
o It is used to create schema, tables, indexes, constraints, etc. in the
database.
o Using the DDL statements, you can create the skeleton of the
database.
o Data definition language is used to store the information of
metadata like the number of tables and schemas, their names,
indexes, columns in each table, constraints, etc.

Here are some tasks that come under DDL:

o Create: It is used to create objects in the database.

o Alter: It is used to alter the structure of the database.
o Drop: It is used to delete objects from the database.
o Truncate: It is used to remove all records from a table.
o Rename: It is used to rename an object.
o Comment: It is used to comment on the data dictionary.

These commands are used to update the database schema

that's why they come under Data definition language.

2. Data Manipulation Language

DML stands for Data Manipulation Language. It is used for accessing and
manipulating data in a database. It handles user requests.

Here are some tasks that come under DML:

o Select: It is used to retrieve data from a database.

o Insert: It is used to insert data into a table.
o Update: It is used to update existing data within a table.
o Delete: It is used to delete all records from a table.
o Merge: It performs UPSERT operation, i.e., insert or
update operations.
o Call: It is used to call a structured query language or a
Java subprogram.
o Explain Plan: It has the parameter of explaining data.
o Lock Table: It controls concurrency.

MODULE 2: ( Q.1 AND Q.2 MUST

STUDY )
1 ) EXPLAIN ER MODEL ? CONSTRAINTS , ATTRIBUTES , ENTITY
SETS ? ETC….. (N.B) //ESSAY

ER model :
o ER model stands for an Entity-Relationship model. It is a high-level
data model. This model is used to define the data elements and
relationship for a specified system.
o It develops a conceptual design for the database. It also develops
a very simple and easy to design view of data.
o In ER modeling, the database structure is portrayed as a diagram
called an entity-relationship diagram.

For example, Suppose we design a school database. In this database, the

student will be an entity with attributes like address, name, id, age, etc.
The address can be another entity with attributes like city, street name,
pin code, etc and there will be a relationship between them.

Component of ER Diagram :

1. Entity:
An entity may be any object, class, person or place. In the ER diagram, an
entity can be represented as rectangles.
Consider an organization as an example- manager, product, employee,
department etc. can be taken as an entity.

a. Weak Entity :C++ vs Java

An entity that depends on another entity called a weak entity. The weak
entity doesn't contain any key attribute of its own. The weak entity is
represented by a double rectangle.

We can convert any weak entity set to a strong entity set by simply adding
appropriate attributes

2. Attribute :
The attribute is used to describe the property of an entity. Eclipse is
used to represent an attribute.

For example, id, age, contact number, name, etc. can be attributes of a
student.

a. Key Attribute :
The key attribute is used to represent the main characteristics of an
entity. It represents a primary key. The key attribute is represented by
an ellipse with the text underlined.

b. Composite Attribute :

An attribute that composed of many other attributes is known as a

composite attribute. The composite attribute is represented by an ellipse,
and those ellipses are connected with an ellipse.

c. Multivalued Attribute :

An attribute can have more than one value. These attributes are known
as a multivalued attribute. The double oval is used to represent
multivalued attribute.

For example, a student can have more than one phone number.
d. Derived Attribute :

An attribute that can be derived from other attribute is known as a

derived attribute. It can be represented by a dashed ellipse.

For example, A person's age changes over time and can be derived from
another attribute like Date of birth.

3 .CONSTARINTS :-
Constraints are used for modeling limitations on the relations between
entities.
There are two types of constraints on the Entity Relationship (ER)
model −
* Mapping cardinality or cardinality ratio.
* Participation constraints.
Mapping Cardinality :-
It is expressed as the number of entities to which another entity can be
associated via a relationship set.

3.1. Relationship (Mapping cardinality):

A relationship is used to describe the relation between entities.

Diamond or rhombus is used to represent the relationship.

Types of relationship are as follows:

a. One-to-One Relationship :

When only one instance of an entity is associated with the relationship,

then it is known as one to one relationship.

For example, A female can marry to one male, and a male can marry to
one female.

b. One-to-many relationship

When only one instance of the entity on the left, and more than one
instance of an entity on the right associates with the relationship then
this is known as a one-to-many relationship.

For example, Scientist can invent many inventions, but the invention is
done by the only specific scientist.

c. Many-to-one relationship

When more than one instance of the entity on the left, and only one
instance of an entity on the right associates with the relationship then it
is known as a many-to-one relationship.

For example, Student enrolls for only one course, but a course can have
many students.
d. Many-to-many relationship

When more than one instance of the entity on the left, and more than
one instance of an entity on the right associates with the relationship
then it is known as a many-to-many relationship.

For example, Employee can assign by many projects and project can have
many employees.

3.2. Participation constraints :-

Total participation :
The participation of an entity set E in a relationship set R is said to be
total if every entity in E Participates in at least one relationship in R.
For Example − Participation of loan in the relationship borrower is total
participation.
Partial Participation :
If only some of the entities in E participate in relationship R, then the
participation of E in R is said to be partial participation.
For example − Participation of customers in the relationship borrower is
partial participation.
2 ) WHAT IS DOMAIN ?
In data management and database analysis, a Data Domain refers to all
the valid values which a data element (column) may contain. The rule for
determining the domain boundary may be as simple as a data type with
a list of possible values.
For example, a database table that has information about people, with
one record per person, might have an "age" column. This gender column
might be declared as a SMALLINT data type, and allowed to have a value
between 0 and 120. The data domain for the age column is hence 0 - 120.

3 ) KEYS IN DBMS ? - PRIMARY KEY , SUPER KEY ETC

Keys :
o Keys play an important role in the relational database.
o It is used to uniquely identify any record or row of data from the
table. It is also used to establish and identify relationships
between tables.

For example: In Student table, ID is used as a key because it is unique for

each student. In PERSON table, passport_number, license_number, SSN
are keys since they are unique for each person.

Types of key:

1. Primary key

o It is the first key which is used to identify one and only one
instance of an entity uniquely. An entity can contain multiple keys
as we saw in PERSON table. The key which is most suitable from
those lists become a primary key.
 o In the EMPLOYEE table, ID can be primary key since it is unique for
each employee. In the EMPLOYEE table, we can even select
License_Number and Passport_Number as primary key since they
are also unique.
o For each entity, selection of the primary key is based on
requirement and developers.

2. Candidate key

o A candidate key is an attribute or set of an attribute which can

uniquely identify a tuple.
o The remaining attributes except for primary key are considered as
a candidate key. The candidate keys are as strong as the primary
key.

For example: In the EMPLOYEE table, id is best suited for the primary
key. Rest of the attributes like SSN, Passport_Number, and
License_Number, etc. are considered as a candidate key.
3. Super Key
Super key is a set of an attribute which can uniquely identify a tuple.
Super key is a superset of a candidate key.

For example: In the above EMPLOYEE table, for(EMPLOEE_ID,

EMPLOYEE_NAME) the name of two employees can be the same, but
their EMPLYEE_ID can't be the same. Hence, this combination can also
be a key.

The super key would be EMPLOYEE-ID, (EMPLOYEE_ID, EMPLOYEE-

NAME), etc.

4. Foreign key

o Foreign keys are the column of the table which is used to point to
the primary key of another table.
o In a company, every employee works in a specific department,
and employee and department are two different entities. So we
can't store the information of the department in the employee
table. That's why we link these two tables through the primary key
of one table.
o We add the primary key of the DEPARTMENT table,
Department_Id as a new attribute in the EMPLOYEE table.
o Now in the EMPLOYEE table, Department_Id is the foreign key,
and both the tables are related.
4 ) EXPLAIN E-R FEATURES ? - GENERALIZATION ,
SPECIALIZATION , AGGREGATION - (N.B) //ESSAY

Generalization :-
Generalization is like a bottom-up approach in which two or more
entities of lower level combine to form a higher level entity if they have
some attributes in common.
In generalization, an entity of a higher level can also combine with the
entities of the lower level to form a further higher level entity.
Generalization is more like subclass and superclass system, but the only
difference is the approach. Generalization uses the bottom-up approach.
In generalization, entities are combined to form a more generalized
entity, i.e., subclasses are combined to make a superclass.
For example, Faculty and Student entities can be generalized and create
a higher level entity Person.
Specialization :
o Specialization is a top-down approach, and it is opposite to
Generalization. In specialization, one higher level entity can be
broken down into two lower level entities.
o Specialization is used to identify the subset of an entity set that
shares some distinguishing characteristics.
o Normally, the superclass is defined first, the subclass and its
related attributes are defined next, and relationship set are then
added.

For example: In an Employee management system, EMPLOYEE entity

can be specialized as TESTER or DEVELOPER based on what role they
play in the company.
Aggregation :
In aggregation, the relation between two entities is treated as a single
entity. In aggregation, relationship with its corresponding entities is
aggregated into a higher level entity.

For example: Center entity offers the Course entity act as a single entity
in the relationship which is in a relationship with another entity visitor.
In the real world, if a visitor visits a coaching center then he will never
enquiry about the Course only or just about the Center instead he will
ask the enquiry about both.

5) RELATIONAL MODEL - CONCEPT ?

Relational Model concept :

Relational model can represent as a table with columns and rows. Each
row is known as a tuple. Each table of the column has a name or
attribute.

Domain: It contains a set of atomic values that an attribute can take.

Attribute: It contains the name of a column in a particular table. Each

attribute Ai must have a domain, dom(Ai)

Relational instance: In the relational database system, the relational

instance is represented by a finite set of tuples. Relation instances do
not have duplicate tuples.426
 How to find Nth Highest Salary in SQL

Relational schema: A relational schema contains the name of the

relation and name of all columns or attributes.

Relational key: In the relational key, each row has one or more
attributes. It can identify the row in the relation uniquely.

6 ) RELATIONAL ALGEBRA AND ITS OPERATIONS ?

Relational Algebra :

Relational algebra is a procedural query language. It gives a step

by step process to obtain the result of the query. It uses operators
to perform queries.

Types of Relational operation :

1. Select Operation:

o The select operation selects tuples that satisfy a given

predicate.
o It is denoted by sigma (σ).

Notation: σ p(r)

Where:
σ is used for selection prediction
r is used for relation
p is used as a propositional logic formula which may use
connectors like: AND OR and NOT. These relational can use as
relational operators like =, ≠, ≥, <, >, ≤.

For example: LOAN Relation356

Java Try Catch

BRANCH_NAME LOAN_NO AMOUNT

Downtown L-17 1000

Redwood L-23 2000

Perryride L-15 1500

Downtown L-14 1500

Mianus L-13 500

Roundhill L-11 900

Perryride L-16 1300

Input:

σ BRANCH_NAME="perryride" (LOAN)

Output:

BRANCH_NAME LOAN_NO AMOUNT

Perryride L-15 1500

Perryride L-16 1300

2. Project Operation:

o This operation shows the list of those attributes that we wish

to appear in the result. Rest of the attributes are eliminated
from the table.
o It is denoted by ∏.

Notation: ∏ A1, A2, An (r)

Where

A1, A2, A3 is used as an attribute name of relation r.

Example: CUSTOMER RELATION

NAME STREET CITY

Jones Main Harrison

Smith North Rye

Hays Main Harrison

Curry North Rye

Johnson Alma Brooklyn

Brooks Senator Brooklyn

Input:

∏ NAME, CITY (CUSTOMER)

Output:

NAME CITY

Jones Harrison
Smith Rye

Hays Harrison

Curry Rye

Johnson Brooklyn

Brooks Brooklyn

3. Union Operation:

o Suppose there are two tuples R and S. The union operation

contains all the tuples that are either in R or S or both in R &
S.
o It eliminates the duplicate tuples. It is denoted by ∪.

Notation: R ∪ S

A union operation must hold the following condition:

o R and S must have the attribute of the same number.

o Duplicate tuples are eliminated automatically.

Example:

DEPOSITOR RELATION

CUSTOMER_NAME ACCOUNT_NO

Johnson A-101

Smith A-121

Mayes A-321

Turner A-176

Johnson A-273
Jones A-472

Lindsay A-284

BORROW RELATION

CUSTOMER_NAME LOAN_NO

Jones L-17

Smith L-23

Hayes L-15

Jackson L-14

Curry L-93

Smith L-11

Williams L-17

Input:

∏ CUSTOMER_NAME (BORROW) ∪ ∏ CUSTOMER_NAME (DEPOSI

TOR)

Output:

CUSTOMER_NAME

Johnson

Smith

Hayes

Turner
 Jones

Lindsay

Jackson

Curry

Williams

Mayes

4. Set Intersection:

o Suppose there are two tuples R and S. The set intersection

operation contains all tuples that are in both R & S.
o It is denoted by intersection ∩.

Notation: R ∩ S

Example: Using the above DEPOSITOR table and BORROW table

Input:

∏ CUSTOMER_NAME (BORROW) ∩ ∏ CUSTOMER_NAME (DEPOSIT

OR)

Output:

CUSTOMER_NAME

Smith

Jones

5. Set Difference:

o Suppose there are two tuples R and S. The set intersection

operation contains all tuples that are in R but not in S.
 o It is denoted by intersection minus (-).

Notation: R - S

Example: Using the above DEPOSITOR table and BORROW table

Input:

∏CUSTOMER_NAME(BORROW) - ∏ CUSTOMER_NAME (DEPOSITOR)

Output:

CUSTOMER_NAME

Jackson

Hayes

Willians

Curry

6. Cartesian product

o The Cartesian product is used to combine each row in one

table with each row in the other table. It is also known as a
cross product.
o It is denoted by X.

Notation: E X D

Example:

EMPLOYEE

EMP_ID EMP_NAME EMP_DEPT

1 Smith A
 2 Harry C

3 John B

DEPARTMENT

DEPT_NO DEPT_NAME

A Marketing

B Sales

C Legal

Input:

EMPLOYEE X DEPARTMENT

Output:

EMP_I EMP_NAM EMP_DEP DEPT_N DEPT_NAM

D E T O E

1 Smith A A Marketing

1 Smith A B Sales

1 Smith A C Legal

2 Harry C A Marketing

2 Harry C B Sales

2 Harry C C Legal

3 John B A Marketing
 3 John B B Sales

3 John B C Legal

7. Rename Operation:

The rename operation is used to rename the output relation. It is

denoted by rho (ρ).

Example: We can use the rename operator to rename STUDENT

relation to STUDENT1.

ρ(STUDENT1, STUDENT)

7 ) Difference between Relational Algebra and Relational

Calculus ?
Difference between Relational Algebra and Relational Calculus:

S.NO Relational Algebra Relational Calculus

1. It is a Procedural language. While Relational

Calculus is Declarative
language.

2. Relational Algebra means how While Relational

to obtain the result. Calculus means what
result we have to
obtain.

3. In Relational Algebra, The While in Relational

order is specified in which Calculus, The order is
the operations have to be not specified.
performed.

4. Relational Algebra is While Relation Calculus

independent on domain. can be a domain
dependent.
 5. Relational Algebra is nearer While Relational
to a programming language. Calculus is not nearer
to programming
language.

1 ) EXPLAIN FUNCTIONAL DEPENDENCY & JOIN DEPENDENCY ?

Functional Dependency :
The functional dependency is a relationship that exists between two
attributes. It typically exists between the primary key and non-key
attribute within a table.

X → Y

The left side of FD is known as a determinant, the right side of the

production is known as a dependent.

For example:

Assume we have an employee table with attributes: Emp_Id, Emp_Name,

Emp_Address.

Here Emp_Id attribute can uniquely identify the Emp_Name attribute of

employee table because if we know the Emp_Id, we can tell that
employee name associated with it.

Functional dependency can be written as:

Emp_Id → Emp_Name

We can say that Emp_Name is functionally dependent on Emp_Id.

Types of Functional dependency :

Join Dependency :

o Join decomposition is a further generalization of Multivalued

dependencies.
o If the join of R1 and R2 over C is equal to relation R, then we
can say that a join dependency (JD) exists.
o Where R1 and R2 are the decompositions R1(A, B, C) and
R2(C, D) of a given relations R (A, B, C, D).
o Alternatively, R1 and R2 are a lossless decomposition of R.
o A JD ⋈ {R1, R2,..., Rn} is said to hold over a relation R if R1,
R2,....., Rn is a lossless-join decomposition.
o The *(A, B, C, D), (C, D) will be a JD of R if the join of join's
attribute is equal to the relation R.
o Here, *(R1, R2, R3) is used to indicate that relation R1, R2, R3
and so on are a JD of R.

2 ) WHAT IS NORMALIZATION ? WHY IS IT DONE ?

( 1NF,2NF,3NF,BCNF,4NF,5NF) - //ESSAY

Normalization :

o Normalization is the process of organizing the data in the

database.
o Normalization is used to minimize the redundancy from a
relation or set of relations. It is also used to eliminate the
 undesirable characteristics like Insertion, Update and
Deletion Anomalies.
o Normalization divides the larger table into the smaller table
and links them using relationship.
o The normal form is used to reduce redundancy from the
database table.

Objective of Normalization (WHY ITS DONE)

1. It is used to remove the duplicate data and database anomalies
from the relational table.
2. Normalization helps to reduce redundancy and complexity by
examining new data types used in the table.
3. It is helpful to divide the large database table into smaller tables
and link them using relationship.
4. It avoids duplicate data or no repeating groups into a table.
5. It reduces the chances for anomalies to occur in a database.

Types of Normal Forms :

There are the four types of normal forms:

Normal Description
Form

1NF A relation is in 1NF if it contains an atomic value.

2NF A relation will be in 2NF if it is in 1NF and all non-key
attributes are fully functional dependent on the primary
key.

3NF A relation will be in 3NF if it is in 2NF and no transition

dependency exists.

4NF A relation will be in 4NF if it is in Boyce Codd normal form

and has no multi-valued dependency.

5NF A relation is in 5NF if it is in 4NF and not contains any join

dependency and joining should be lossless.

First Normal Form (1NF) :

o A relation will be 1NF if it contains an atomic value.

o It states that an attribute of a table cannot hold multiple values. It
must hold only single-valued attribute.
o First normal form disallows the multi-valued attribute, composite
attribute, and their combinations.

Example: Relation EMPLOYEE is not in 1NF because of multi-valued

attribute EMP_PHONE.

EMPLOYEE table:

EMP_ID EMP_NAME EMP_PHONE EMP_STATE

14 John 7272826385, UP
9064738238

20 Harry 8574783832 Bihar

12 Sam 7390372389, Punjab

8589830302

The decomposition of the EMPLOYEE table into 1NF has been shown
below:
EMP_ID EMP_NAME EMP_PHONE EMP_STATE

14 John 7272826385 UP

14 John 9064738238 UP

20 Harry 8574783832 Bihar

12 Sam 7390372389 Punjab

12 Sam 8589830302 Punjab

Second Normal Form (2NF)

o In the 2NF, relational must be in 1NF.

o In the second normal form, all non-key attributes are fully
functional dependent on the primary key

Example: Let's assume, a school can store the data of teachers and the
subjects they teach. In a school, a teacher can teach more than one
subject.

TEACHER table

TEACHER_ID SUBJECT TEACHER_AGE

25 Chemistry 30

25 Biology 30

47 English 35

83 Math 38

83 Computer 38

In the given table, non-prime attribute TEACHER_AGE is dependent on

TEACHER_ID which is a proper subset of a candidate key. That's why it
violates the rule for 2NF.
To convert the given table into 2NF, we decompose it into two tables:

TEACHER_DETAIL table:

TEACHER_ID TEACHER_AGE

25 30

47 35

83 38

TEACHER_SUBJECT table:

TEACHER_ID SUBJECT

25 Chemistry

25 Biology

47 English

83 Math

83 Computer

Third Normal Form (3NF) :

o A relation will be in 3NF if it is in 2NF and not contain any
transitive partial dependency.
o 3NF is used to reduce the data duplication. It is also used to
achieve the data integrity.
o If there is no transitive dependency for non-prime attributes, then
the relation must be in third normal form.

A relation is in third normal form if it holds atleast one of the following

conditions for every non-trivial function dependency X → Y.

1. X is a super key.
2. Y is a prime attribute, i.e., each element of Y is part of some
candidate key.
Example:

EMPLOYEE_DETAIL table:

EMP_ID EMP_NAME EMP_ZIP EMP_STATE EMP_CITY

222 Harry 201010 UP Noida

333 Stephan 02228 US Boston

444 Lan 60007 US Chicago

555 Katharine 06389 UK Norwich

666 John 462007 MP Bhopal

Super key in the table above:

{EMP_ID}, {EMP_ID, EMP_NAME}, {EMP_ID, EMP_NAME, EMP_ZIP}

....so on

Candidate key: {EMP_ID}

Non-prime attributes: In the given table, all attributes except

EMP_ID are non-prime.

Here, EMP_STATE & EMP_CITY dependent on EMP_ZIP and

EMP_ZIP dependent on EMP_ID. The non-prime attributes
(EMP_STATE, EMP_CITY) transitively dependent on super
key(EMP_ID). It violates the rule of third normal form.

That's why we need to move the EMP_CITY and EMP_STATE to

the new <EMPLOYEE_ZIP> table, with EMP_ZIP as a Primary key.

EMPLOYEE table:

EMP_ID EMP_NAME EMP_ZIP

222 Harry 201010

 333 Stephan 02228

444 Lan 60007

555 Katharine 06389

666 John 462007

EMPLOYEE_ZIP table:

EMP_ZIP EMP_STATE EMP_CITY

201010 UP Noida

02228 US Boston

60007 US Chicago

06389 UK Norwich

462007 MP Bhopal

Boyce Codd normal form (BCNF) :

o BCNF is the advance version of 3NF. It is stricter than 3NF.

o A table is in BCNF if every functional dependency X → Y, X is the
super key of the table.
o For BCNF, the table should be in 3NF, and for every FD, LHS is
super key.

Example: Let's assume there is a company where employees work in

more than one department.
 EMPLOYEE table:

EMP_I EMP_COUNTR EMP_DEP DEPT_TYP EMP_DEPT_N

D Y T E O

264 India Designing D394 283

264 India Testing D394 300

364 UK Stores D283 232

364 UK Developing D283 549

In the above table Functional dependencies are as follows:

1. EMP_ID → EMP_COUNTRY
2. EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}

Candidate key: {EMP-ID, EMP-DEPT}Difference between JDK, JRE, a

The table is not in BCNF because neither EMP_DEPT nor EMP_ID alone
are keys.

To convert the given table into BCNF, we decompose it into three tables:

EMP_COUNTRY table:

EMP_ID EMP_COUNTRY

264 India

264 India

EMP_DEPT table:

EMP_DEPT DEPT_TYPE EMP_DEPT_NO

 Designing D394 283

Testing D394 300

Stores D283 232

Developing D283 549

EMP_DEPT_MAPPING table:

EMP_ID EMP_DEPT

D394 283

D394 300

D283 232

D283 549

Functional dependencies:

1. EMP_ID → EMP_COUNTRY
2. EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}

Candidate keys:

For the first table: EMP_ID

For the second table: EMP_DEPT
For the third table: {EMP_ID, EMP_DEPT}

Now, this is in BCNF because left side part of both the functional
dependencies is a key.

Fourth normal form (4NF) :

o A relation will be in 4NF if it is in Boyce Codd normal form and has

no multi-valued dependency.
 o For a dependency A → B, if for a single value of A, multiple values
of B exists, then the relation will be a multi-valued dependency.

Example :

STUDENT

STU_ID COURSE HOBBY

21 Computer Dancing

21 Math Singing

34 Chemistry Dancing

74 Biology Cricket

59 Physics Hockey

The given STUDENT table is in 3NF, but the COURSE and HOBBY are two
independent entity. Hence, there is no relationship between COURSE
and HOBBY.

In the STUDENT relation, a student with STU_ID, 21 contains two

courses, Computer and Math and two hobbies, Dancing and Singing. So
there is a Multi-valued dependency on STU_ID, which leads to
unnecessary repetition of data.

So to make the above table into 4NF, we can decompose it into two
tables:

STUDENT_COURSE

STU_ID COURSE

21 Computer

21 Math

34 Chemistry
74 Biology

59 Physics

STUDENT_HOBBY

STU_ID HOBBY

21 Dancing

21 Singing

34 Dancing

74 Cricket

59 Hockey

Fifth normal form (5NF) :

o A relation is in 5NF if it is in 4NF and not contains any join

dependency and joining should be lossless.
o 5NF is satisfied when all the tables are broken into as many tables
as possible in order to avoid redundancy.
o 5NF is also known as Project-join normal form (PJ/NF).

Example

SUBJECT LECTURER SEMESTER

Computer Anshika Semester 1

Computer John Semester 1

Math John Semester 1

Math Akash Semester 2

Chemistry Praveen Semester 1

In the above table, John takes both Computer and Math class for
Semester 1 but he doesn't take Math class for Semester 2. In this case,
combination of all these fields required to identify a valid data.

Suppose we add a new Semester as Semester 3 but do not know about

the subject and who will be taking that subject so we leave Lecturer and
Subject as NULL. But all three columns together acts as a primary key, so
we can't leave other two columns blank.

So to make the above table into 5NF, we can decompose it into three
relations P1, P2 & P3:

P1

SEMESTER SUBJECT

Semester 1 Computer

Semester 1 Math

Semester 1 Chemistry

Semester 2 Math

P2

SUBJECT LECTURER

Computer Anshika

Computer John

Math John

Math Akash

Chemistry Praveen

P3
SEMSTER LECTURER

Semester 1 Anshika

Semester 1 John

Semester 1 John

Semester 2 Akash

Semester 1 Praveen

2 ) MULTIVALUED DEPENDENCY - SHORT ?

Multivalued Dependency :

o Multivalued dependency occurs when two attributes in a table are

independent of each other but, both depend on a third attribute.
o A multivalued dependency consists of at least two attributes that
are dependent on a third attribute that's why it always requires at
least three attributes.

Example: Suppose there is a bike manufacturer company which

produces two colors(white and black) of each model every year.

3 ) EXPLAIN LOSELESS JOIN DECOMPOSITION WITH EXAMPLE ?

Lossless-join decomposition is a process in which a relation is
decomposed into two or more relations. This property guarantees that
the extra or less tuple generation problem does not occur and no
information is lost from the original relation during the decomposition. It
is also known as non-additive join decomposition.
When the sub relations combine again then the new relation must be
the same as the original relation was before decomposition.
Consider a relation R if we decomposed it into sub-parts relation R1 and
relation R2.
The decomposition is lossless when it satisfies the following statement −
* If we union the sub Relation R1 and R2 then it must contain all the
attributes that are available in the original relation R before
decomposition.
* Intersections of R1 and R2 cannot be Null. The sub relation must
contain a common attribute. The common attribute must contain
unique data.
The common attribute must be a super key of sub relations either R1 or
R2.
Here,
R = (A, B, C)
R1 = (A, B)
R2 = (B, C)
The relation R has three attributes A, B, and C. The relation R is
decomposed into two relation R1 and R2. . R1 and R2 both have 2-2
attributes.The common attributes are B.
The Value in Column B must be unique. if it contains a duplicate value
then the Lossless-join decomposition is not possible.
Draw a table of Relation R with Raw Data −
R (A, B, C)

A B C

12 25 34

10 36 09

12 42 30

It decomposes into the two sub relations −

R1 (A, B)

A B

12 25

10 36
 A B

12 42

R2 (B, C)

B C

25 34

36 09

42 30

Now, we can check the first condition for Lossless-join decomposition.

The union of sub relation R1 and R2 is the same as relation R.
R1U R2 = R
We get the following result −

A B C

12 25 34

10 36 09

12 42 30

The relation is the same as the original relation R. Hence, the above
decomposition is Lossless-join decomposition.

1) WHAT ARE AGGREGATE FUNCTIONS IN SQL ? 5 MARKS

An aggregate function allows you to perform a calculation on a set of
values to return a single scalar value. We often use aggregate functions
with the GROUP BY and HAVING clauses of the SELECT statement.

The following are the most commonly used SQL aggregate functions:

AVG – calculates the average of a set of values.

COUNT – counts rows in a specified table or view.

MIN – gets the minimum value in a set of values.

MAX – gets the maximum value in a set of values.

SUM – calculates the sum of values.COUNT function

To get the number of products in the products table, you u see the
COUNT function as follows:

SELECT

COUNT(*)

FROM

product

AVG function :

To calculate the average units in stock of the products, you use the
AVG function as follows:

SELECT

AVG(unitsinstock)

FROM

products;

SUM function :

To calculate the sum of units in stock by product category, you use the
SUM function with the GROUP BY clause as the following query:

SELECT

categoryid, SUM(unitsinstock)

FROM

products
GROUP BY categoryid;

MIN function :

To get the minimum units in stock of products in the products table,

you use the MIN function as follows:

SELECT

MIN(unitsinstock)

FROM

products;

MAX function :

To get the maximum units in stock of products in the products table,

you use the MAX function as shown in the following query:

SELECT

MAX(unitsinstock)

FROM

products;

2) WHAT ARE BASIC QUERIES IN SQL ?

 SELECT - extracts data from a database
 UPDATE - updates data in a database
 DELETE - deletes data from a database
 INSERT INTO - inserts new data into a database
 CREATE DATABASE - creates a new database
 ALTER DATABASE - modifies a database
 CREATE TABLE - creates a new table
 ALTER TABLE - modifies a table
 DROP TABLE - deletes a table
 CREATE INDEX - creates an index (search key)
 DROP INDEX - deletes an index

3) EXPLAIN WITH EXAMPLES 3 DIFFERENT TYPES OF SQL

COMMANDS USED TO MODIFY A DATABASE ? (N.B) //ESSAY
(DML , DCL , DDL)

SQL Commands :

SQL commands are instructions. It is used to communicate with the

database. It is also used to perform specific tasks, functions, and
queries of data.

SQL can perform various tasks like create a table, add data to tables,
drop the table, modify the table, set permission for users.

Types of SQL Commands :

There are five types of SQL commands: DDL, DML, DCL

1. Data Definition Language (DDL) :

o DDL changes the structure of the table like creating a table,

deleting a table, altering a table, etc.
o All the command of DDL are auto-committed that means it
permanently save all the changes in the database.

Here are some commands that come under DDL:

o CREATE
o ALTER
 o DROP
o TRUNCATE

a. CREATE It is used to create a new table in the database.

Syntax:

CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);

Example:

CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHAR2(100),

DOB DATE);

b. DROP: It is used to delete both the structure and record stored in the
table.

Syntax:

DROP TABLE table_name;

Example

DROP TABLE EMPLOYEE;

c. ALTER: It is used to alter the structure of the database. This change

could be either to modify the characteristics of an existing attribute or
probably to add a new attribute.

Syntax:

To add a new column in the table :

ALTER TABLE table_name ADD column_name COLUMN-definition;

To modify existing column in the table:

ALTER TABLE table_name MODIFY(column_definitions....);

EXAMPLE :

ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR2(20));

ALTER TABLE STU_DETAILS MODIFY (NAME VARCHAR2(20));

 d. TRUNCATE: It is used to delete all the rows from the table and free the
space containing the table.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE EMPLOYEE;

2. Data Manipulation Language :

o DML commands are used to modify the database. It is responsible

for all form of changes in the database.
o The command of DML is not auto-committed that means it can't
permanently save all the changes in the database. They can be
rollback.

Here are some commands that come under DML:

o INSERT
o UPDATE
o DELETE

a. INSERT: The INSERT statement is a SQL query. It is used to insert data

into the row of a table.

Syntax:

1. INSERT INTO TABLE_NAME

2. (col1, col2, col3,.... col N)
3. VALUES (value1, value2, value3, .... valueN);

Or

1. INSERT INTO TABLE_NAME

2. VALUES (value1, value2, value3, .... valueN);

For example:
1. INSERT INTO TABLE_NAME (Author, Subject) VALUES ("Sonoo", "D
BMS");

b. UPDATE: This command is used to update or modify the value of a

column in the table.

Syntax:

UPDATE table_name SET [column_name1= value1,...column_nameN = val

ueN] [WHERE CONDITION]

For example:

1. UPDATE students
2. SET User_Name = 'Sonoo'
3. WHERE Student_Id = '3'

c. DELETE: It is used to remove one or more row from a table.

Syntax:

DELETE FROM table_name [WHERE condition];

For example:

1. DELETE FROM table_name

2. WHERE Author="Sonoo";

3. Data Control Language :

DCL commands are used to grant and take back authority from any
database user.

Here are some commands that come under DCL:

o Grant
o Revoke

a. Grant: It is used to give user access privileges to a database.

Example
GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

b. Revoke: It is used to take back permissions from the user.

Example

REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

4) EXPLAIN INTEGRITY CONSTRAINTS ?

Integrity Constraints :

o maintain the quality of information.

o 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.
o Thus, integrity constraint is used to guard against accidental
damage to the database.
o Integrity constraints are a set of rules. It is used to guard against
accidental damage to the database.

5) DIFFERNTIATE B/W SQL DML AND SQL DDL ?

Difference between DDL and DML:
DDL DML
It stands for Data Definition It stands for Data Manipulation
Language. Language.

It is used to create database schema It is used to add, retrieve or

and can be used to define some update the data.
constraints as well.

It basically defines the column It add or update the row of the

(Attributes) of the table. table. These rows are called as
tuple.

It doesn’t have any further It is further classified into

classification. Procedural and Non-Procedural
 DML.

Basic command present in DDL are BASIC command present in

CREATE, DROP, RENAME, ALTER etc. DML are UPDATE, INSERT,
MERGE etc.

DDL does not use WHERE clause in its While DML uses WHERE clause
statement. in its statement.

6) EXPLAIN SQL CLAUSES - (GROUP BY , HAVING , ORDER BY)

CLAUSES ? - 5 MARKS
SQL Clauses :

The following are the various SQL clauses:

1. GROUP BY :

o SQL GROUP BY statement is used to arrange identical data into

groups. The GROUP BY statement is used with the SQL SELECT
statement.
o The GROUP BY statement follows the WHERE clause in a SELECT
statement and precedes the ORDER BY clause.
o The GROUP BY statement is used with aggregation function.

Syntax

1. SELECT column
2. FROM table_name
3. WHERE conditions
4. GROUP BY column
5. ORDER BY column

Example:

1. SELECT COMPANY, COUNT(*)

2. FROM PRODUCT_MAST
3. GROUP BY COMPANY

2. HAVING :

o HAVING clause is used to specify a search condition for a group

or an aggregate.
o Having is used in a GROUP BY clause. If you are not using GROUP
BY clause then you can use HAVING function like a WHERE clause.

Syntax:

1. SELECT column1, column2

2. FROM table_name
3. WHERE conditions
4. GROUP BY column1, column2
5. HAVING conditions
6. ORDER BY column1, column2;

Example:

1. SELECT COMPANY, COUNT(*)

2. FROM PRODUCT_MAST
3. GROUP BY COMPANY
4. HAVING COUNT(*)>2;

3. ORDER BY

o The ORDER BY clause sorts the result-set in ascending or

descending order.
 o It sorts the records in ascending order by default. DESC keyword
is used to sort the records in descending order.

Syntax:

1. SELECT column1, column2

2. FROM table_name
3. WHERE condition
4. ORDER BY column1, column2... ASC|DESC;

Where:

ASC: It is used to sort the result set in ascending order by expression.

DESC: It sorts the result set in descending order by expression.

EXAMPLE :

1. SELECT *
2. FROM CUSTOMER
3. ORDER BY NAME ASC OR DESC;

1) EXPLAIN TRIGGER , ITS PARTS ETC ? //ESSAY

(N.B) 5TH MODULE ESSAY QSTN

Triggers :
Triggers are the SQL statements that are automatically
executed when there is any change in the database. The
triggers are executed in response to certain events(INSERT,
UPDATE or DELETE) in a particular table. These triggers help in
maintaining the integrity of the data by changing the data of
the database in a systematic fashion.
Syntax :

create trigger Trigger_name(before | after)

[insert | update | delete]
on [table_name]
[for each row]
[trigger_body]

1. CREATE TRIGGER: These two keywords specify that a triggered

block is going to be declared.
2. TRIGGER_NAME: It creates or replaces an existing trigger with
the Trigger_name. The trigger name should be unique.
3. BEFORE | AFTER: It specifies when the trigger will be initiated i.e.
before the ongoing event or after the ongoing event.
4. INSERT | UPDATE | DELETE: These are the DML operations and
we can use either of them in a given trigger.
5. ON[TABLE_NAME]: It specifies the name of the table on which
the trigger is going to be applied.
6. FOR EACH ROW: Row-level trigger gets executed when any row
value of any column changes.
7. TRIGGER BODY: It consists of queries that need to be executed
when the trigger is called.

Example :
Suppose we have a table named Student containing the
attributes Student_id, Name, Address, and Marks.
Now, we want to create a trigger that will add 100 marks to
each new row of the Marks column whenever a new student is
inserted to the table.

The SQL Trigger will be:

CREATE TRIGGER Add_marks

BEFORE
INSERT
ON Student
FOR EACH ROW
SET new.Marks = new.Marks + 100;

The new keyword refers to the row that is getting

affected.

After creating the trigger, we will write the query for inserting a new
student in the database.

INSERT INTO Student(Name, Address, Marks) VALUES('Alizeh',

'Maldives', 110);
The Student_id column is an auto-increment field and will be
generated automatically when a new record is inserted into
the table.

To see the final output the query would be:

SELECT * FROM Student;

 Advantages of Triggers:

1. Triggers provide a way to check the integrity of the data. When

there is a change in the database the triggers can adjust the
entire database.
2. Triggers help in keeking User Interface lightweight. Instead of
putting the same function call all over the application you can
put a trigger and it will be executed.

Disadvantages of Triggers:

1. Triggers may be difficult to troubleshoot as they execute

automatically in the database. If there is some error then it is
hard to find the logic of trigger because they are fired before or
after updates/inserts happen.
2. The triggers may increase the overhead of the database as they
are executed every time any field is updated.
2 . EXPLAIN ACID PROPERTIES ?
ACID Properties in DBMS :

DBMS is the management of data that should remain integrated when

any changes are done in it. It is because if the integrity of the data is
affected, whole data will get disturbed and corrupted. Therefore, to
maintain the integrity of the data, there are four properties described
in the database management system, which are known as
the ACID properties. The ACID properties are meant for the transaction
that goes through a different group of tasks, and there we come to see
the role of the ACID properties

ACID Properties :

The expansion of the term ACID defines for:

1) Atomicity: The term atomicity defines that the data remains atomic.
It means if any operation is performed on the data, either it should be
performed or executed completely or should not be executed at all. It
further means that the operation should not break in between or
execute partially. In the case of executing operations on the
transaction, the operation should be completely executed and not
partially.
2) Consistency: The word consistency means that the value should
remain preserved always. In DBMS, the integrity of the data should be
maintained, which means if a change in the database is made, it should
remain preserved always. In the case of transactions, the integrity of
the data is very essential so that the database remains consistent
before and after the transaction. The data should always be correct
3) Isolation: The term 'isolation' means separation. In DBMS, Isolation
is the property of a database where no data should affect the other
one and may occur concurrently. In short, the operation on one
database should begin when the operation on the first database gets
complete. It means if two operations are being performed on two
different databases, they may not affect the value of one another. In
the case of transactions, when two or more transactions occur
simultaneously, the consistency should remain maintained. Any
changes that occur in any particular transaction will not be seen by
other transactions until the change is not committed in the memory.

4) Durability: Durability ensures the permanency of something. In

DBMS, the term durability ensures that the data after the successful
execution of the operation becomes permanent in the database. The
durability of the data should be so perfect that even if the system fails
or leads to a crash, the database still survives. However, if gets lost, it
becomes the responsibility of the recovery manager for ensuring the
durability of the database. For committing the values, the COMMIT
command must be used every time we make changes.

ALL THE BEST