S.Y.B.Sc. I.T.

DBMS Solution Set, October 2018

1a What is the role of a DBMS, and what are its advantages? What are its disadvantages?
Ans. A database management system (DBMS) is a collection of programs that manage the
database structure and controls access to the data stored in the database”.
The DBMS serves as the intermediate between the user and the database. The
database structure is stored as a collection of files. These data can be accessed in those
files through the DBMS. The DBMS hides much of the database’s internal complexity
from the application programs and users.

Advantages of DBMS

The database management system has promising potential advantages, which are
explained below:

1. Controlling Redundancy: In file system, each application has its own private files,
which cannot be shared between multiple applications. 1: his can often lead to
considerable redundancy in the stored data, which results in wastage of storage space.
By having centralized database most of this can be avoided. It is not possible that all
redundancy should be eliminated. Sometimes there are sound business and technical
reasons for· maintaining multiple copies of the same data. In a database system,
however this redundancy can be controlled.

2. Integrity can be enforced: Integrity of data means that data in database is always
accurate, such that incorrect information cannot be stored in database. In order to
maintain the integrity of data, some integrity constraints are enforced on the database.
A DBMS should provide capabilities for defining and enforcing the constraints.
3. Inconsistency can be avoided : When the same data is duplicated and changes are
made at one site, which is not propagated to the other site, it gives rise to inconsistency
and the two entries regarding the same data will not agree. At such times the data is said
to be inconsistent. So, if the redundancy is removed chances of having inconsistent data
is also removed.
4. Data can be shared: As explained earlier, the data about Name, Class, Father
__name etc. of General_Office is shared by multiple applications in centralized DBMS
as compared to file system so now applications can be developed to operate against the
same stored data. The applications may be developed without having to create any new
stored files.
5. Standards can be enforced : Since DBMS is a central system, so standard can be
enforced easily may be at Company level, Department level, National level or
International level. The standardized data is very helpful during migration or
interchanging of data. The file system is an independent system so standard cannot be
easily enforced on multiple independent applications.
6. Restricting unauthorized access: When multiple users share a database, it is likely
that some users will not be authorized to access all information in the database. For
example, account office data is often considered confidential, and hence only authorized
persons are allowed to access such data. In addition, some users may be permitted only
to retrieve data, whereas other are allowed both to retrieve and to update. Hence, the
type of access operation retrieval or update must also be controlled. Typically, users or
user groups are given account numbers protected by passwords, which they can use to
gain access to the database. A DBMS should provide a security and authorization
subsystem, which the DBA uses to create accounts and to specify account restrictions.
The DBMS should then enforce these restrictions automatically.
7. Solving Enterprise Requirement than Individual Requirement: Since many
types of users with varying level of technical knowledge use a database, a DBMS should
provide a variety of user interface. The overall requirements of the enterprise are more
important than the individual user requirements. So, the DBA can structure the database
system to provide an overall service that is "best for the enterprise".
For example: A representation can be chosen for the data in storage that gives fast access
for the most important application at the cost of poor performance in some other
application. But, the file system favors the individual requirements than the enterprise
requirements
8. Providing Backup and Recovery: A DBMS must provide facilities for recovering
from hardware or software failures. The backup and recovery subsystem of the DBMS
is responsible for recovery. For example, if the computer system fails in the middle of
a complex update program, the recovery subsystem is responsible for making sure that
the .database is restored to the state it was in before the program started executing.
9. Cost of developing and maintaining system is lower: It is much easier to respond
to unanticipated requests when data is centralized in a database than when it is stored
in a conventional file system. Although the initial cost of setting up of a database can
be large, but the cost of developing and maintaining application programs to be far
lower than for similar service using conventional systems. The productivity of
programmers can be higher in using non-procedural languages that have been
developed with DBMS than using procedural languages.
10. Data Model can be developed : The centralized system is able to represent the
complex data and interfile relationships, which results better data modeling properties.
The data madding properties of relational model is based on Entity and their
Relationship, which is discussed in detail in chapter 4 of the book.
11. Concurrency Control : DBMS systems provide mechanisms to provide concurrent
access of data to multiple users.

Disadvantages of DBMS
The disadvantages of the database approach are summarized as follows:
 1. Complexity : The provision of the functionality that is expected of a good DBMS
makes the DBMS an extremely complex piece of software. Database designers,
developers, database administrators and end-users must understand this functionality to
take full advantage of it. Failure to understand the system can lead to bad design
decisions, which can have serious consequences for an organization.
2. Size : The complexity and breadth of functionality makes the DBMS an extremely
large piece of software, occupying many megabytes of disk space and requiring
substantial amounts of memory to run efficiently.
3. Performance: Typically, a File Based system is written for a specific application,
such as invoicing. As result, performance is generally very good. However, the DBMS
is written to be more general, to cater for many applications rather than just one. The
effect is that some applications may not run as fast as they used to.
4. Higher impact of a failure: The centralization of resources increases the
vulnerability of the system. Since all users and applications rely on the ~vailabi1ity of
the DBMS, the failure of any component can bring operations to a halt.
5. Cost of DBMS: The cost of DBMS varies significantly, depending on the
environment and functionality provided. There is also the recurrent annual maintenance
cost.
6. Additional Hardware costs: The disk storage requirements for the DBMS and the
database may necessitate the purchase of additional storage space. Furthermore, to
achieve the required performance it may be necessary to purchase a larger machine,
perhaps even a machine dedicated to running the DBMS. The procurement of additional
hardware results in further expenditure.
7. Cost of Conversion: In some situations, the cost oftlle DBMS and extra hardware
may be insignificant compared with the cost of converting existing applications to run
on the new DBMS and hardware. This cost also includes the cost of training staff to use
these new systems and possibly the employment of specialist staff to help with
conversion and running of the system. This cost is one of the main reasons why some
organizations feel tied to their current systems and cannot switch to modern database
technology.

1b Explain Storage system and query processor components of database structure.

Ans. Storage Manager
The storage manager is the component of a database system that provides the interface
between the low-level data stored in the database and the application programs and
queries submitted to the system. The storage manager is responsible for the interaction
with the file manager. The raw data are stored on the disk using the file system
provided by the operating system. The storage manager translates the various DML
statements into low-level file-system commands.
 Thus, the storage manager is responsible for storing, retrieving, and updating data in the
database. The storage manager components include:
• Authorization and integrity manager, which tests for the satisfaction of integrity
constraints and checks the authority of users to access data.
• Transaction manager, which ensures that the database remains in a consistent
(correct) state despite system failures, and that concurrent transaction executions
proceed without conflicting.
• File manager, which manages the allocation of space on disk storage and the data
structures used to represent information stored on disk.
• Buffer manager,which is responsible for fetching data from disk storage into main
memory, and deciding what data to cache in main memory. The buffer manager is a
critical part of the database system, since it enables the database to handle data sizes
that are much larger than the size of main memory.
The storage manager implements several data structures as part of the physical system
implementation:
• Data files, which store the database itself.
• Data dictionary, which stores metadata about the structure of the database,
in particular the schema of the database.
• Indices, which can provide fast access to data items.
The Query Processor
The query processor components include:
• DDL interpreter, which interprets DDL statements and records the definitions In the
data dictionary.
• DML compiler, which translates DML statements in a query language into an
Evaluation plan consisting of low-level instructions that the query evaluation Engine
understands. A query can usually be translated into any of a number of alternative
Evaluation plans that all give the same result. The DMLcompiler also performs
Query optimization; that is, it picks the lowest cost evaluation plan from
Among the alternatives.
• Query evaluation engine, which executes low-level instructions generated by the
DML compiler.

1c What is a business rule, and what is its purpose in data modeling?

Ans. When database designers go about selecting or determining the entities,
attributes,andrelationships that will be used to build a data model, they might start by
gaining thorough understanding of what types of data exist in an organization, how the
data is used, and in what time frames it is used. But such data and information do not,
by themselves, yield the required understanding of the total business. From a database
point of view, the collection of data becomes meaningful only when it reflects properly
defined business rules. A business rule is a brief, precise, and unambiguous description
of a policy, procedure, or principle within a specific organization. In a sense, business
 rules are misnamed: they apply to any organization, large or small—a business, a
government unit, a religious group, or a research laboratory—that stores and uses data
to generate information. Business rules derived from a detailed description of an
organization’s operations help to create and enforce actions within that organization’s
environment. Business rules must be rendered in writing and updated to reflect any
change in the organization’s operational environment.
Properly written business rules are used to define entities, attributes, relationships, and
constraints. Any time you see relationship statements such as “an agent can serve many
customers, and each customer can be served by only one agent,” business rules area
work. To be effective, business rules must be easy to understand and widely
disseminated to ensure that every person in the organization shares a common
interpretation of the rules. Business rules describe, in simple language, the main and
distinguishing characteristics of the data as viewed by the company. Examples of
business rules are as follows:
• A customer may generate many invoices.
• An invoice is generated by only one customer.
• A training session cannot be scheduled for fewer than 10 employees or for more than
30 employees.
Note that those business rules establish entities, relationships, and constraints. For
example, the first two business rules establish two entities (CUSTOMER and
INVOICE) and a 1: M relationship between those two entities. The third business rule
establishes a constraint (no fewer than 10 people and no more than 30 people), two
entities
(EMPLOYEE and TRAINING), and also implies a relationship between EMPLOYEE
And TRAINING.
When database designers selecting / determining the entities, Attributes, relationships
etc. That are used to build a Data model with a thorough understands.
What types of data are used in an organization.
How the data are used.
3. What reports that they need after processing?
From the database point of view, the collection of data becomes meaningful only when it
reflects properly defined business rules.
Thus, a business rule is a brief, precise and unambiguous description of a Policy,
Procedure, or Principle within a specific organization.Business rules, thus, derived from
a detailed description of an Organization’s operations, that help to create and enforce
actions within that organization’s environment.To be effective, Business rules must be
in written, properly defined, easy to understand to every person in the Organization.,
shares a common interpretation of the rules.Some of the Example of Business rules are,
i) A customer may generate many invoices
ii) An invoice is generated by only one customer
 iii) A training session cannot be scheduled for fewer than 10 employees or for more than
30 employees., etc.
Purpose of Business Rules:
The business rules are essential to Database designer for several reasons, such as
a) They help standardize the Company's view of data
b) They can be a communication tool in between users and designers
c) They allow the designer to understand the nature, role and scope of Data
d)They allow the designer to understand business processes
e) They allow the DB designer to understand to develop appropriate relationship
participation rules and constraints to create an accurate data model....so on.

1d Give comparison between hierarchical, network & relational model.

Ans.
The hierarchical model is not like the other two models you asked about. In the
hierarchical model, data is stored in a defined hierarchy. For instance, a company is
made of departments and each department has employees. So a tree strucutre is
created with the company at the root of the tree. To get to all employees in the
company, one would have to traverse the entire tree. Click here for more information
on the hierarchical model. The hierarchical model does exist today, but typically in
legacy applications.

The network model was the first attempt to address the inefficiencies of the
hierarchical model. In the network model, you could create a network showing how
data related to each other. The network model never caught on, and was eventually
replaced by the relational model. Click here for more information on the network
model.

The relational model has proven to be the most efficient and most flexible database
model in use today. There are many advantages of the relational model over the other
models, which is why the most popular databases in use today employ this
methodology. Click here for more information on the relational model.
1 e List and explain Codd’s rule in detail.
Ans
.
1f Explain ER diagram and its components. Give the distinction between disjoint,
overlapping, total and partial constraints. Draw E-R diagram for the following
situations that correctly models this domain and its constraints.
A small racing league want a database to keep track of teams, drivers, races and
scores in the league. The league is run for teams, which are identified by their names.
Each team has one or more drivers signed up, and each driver is registered with the
league and has a unique league licence number. First and last names of the drivers
should also be included. A driver may only participate for a single team throughout
the season. Races are identified simply by the dates when they are run. For each race,
the league also wants to store the venue where it took place. Drivers participate in
races, and for each participating driver the database should store the total race time
for that driver, and the league score they got from that race.
Ans. An E-R diagram can express the overall logical structureofa databasegraphically. E-R
diagramsaresimpleand clear—qualities that may well account in large part for the
widespreaduse of the E-R model.
An E-R diagram consists of the following major components:
Rectangles divided into two parts represententity sets.The firstpart,which
inthistextbookisshadedblue,containsthenameoftheentityset.Thesecond part contains
the names of all the attributes of the entity set.
•Diamonds represent relationship sets.
• Undivided rectangles represent the attribute so far elationshipset.Attributes that are
part of the primary key are underlined.
• Lines link entitysets to relationship sets.
• Dashed lines link attributes of a relationship set to the relationship set.
• Double lines indicate total participation of an entity in a relationship set.
• Double diamonds represent identifying relationship sets linked to weak entitysets.

Disjoint. A disjointness constraint requires that an entity belong to no more than one
lower-level entity set. In our example, student entity can satisfy only one condition
for the student type attribute; an entity can be either a graduate student or an
undergraduate student, but cannot be both.
• Overlapping. In overlapping generalizations, the same entity may belong to More
than one lower-level entity set within a single generalization. For an e.g., consider the
employee work-team example, and assume that Certain employees participate in
more than one work team. Given employee May therefore appear in more than one of
the team entity sets that are lower level Entity sets of employee. Thus, the
generalization is overlapping.
Final constraint, the completeness constraint on a generalization or specialization,
Specifies whether or not an entity in the higher-level entity set must belong
to at least one of the lower-level entity sets within the generalization/specialization.
This constraint may be one of the following:
• Total generalization or specialization. Each higher-level entity must belong
to a lower-level entity set.
 • Partial generalization or specialization. Some higher-level entities may not
Belong to any lower-level entity set.

2a Why are entity integrity and referential integrity important in a database? Explain in
detail.
Ans. Referential and entity integrity are crucial to preserving valid relationships between
tables and data within a database. SQL queries will begin to fail if the data keys that
connect the dots between their relationships do not match. If an entity or table is relying
on the keys in another entity or table, then relationships between the two can be lost if
bad data is entered into one location. For instance, referential integrity can be used to
ensure foreign key values are valid. For instance, a database table listing all the parts
installed on a specific aircraft should have referential integrity connecting the part
numbers to a table listing valid part numbers for that aircraft so that in the event of a bad
part number being “fat-fingered” into the database, the RBDMS will return an error
concerning the bad data .
 INTEGRITY RULES

ENTITY INTEGRITY DESCRIPTION

2b Explain why normalization is necessary in database system & also explain database
anomalies in detail.
You are given the following set of functional dependencies for a relation
R(A,B,C,D,E,F),
F ={AB→C,DC →AE,E →F }
a. What are the keys of this relation?
b. Is this relation in BCNF? If not, explain why by showing one violation.
c. Is the decomposition (A, B, C, D) (B, C, D, E, F) a dependency preserving
decomposition? If not, explain briefly.

Ans. Database normalization is the process of organizing the attributes and tables of a
relational database to minimize data redundancy.
If a database design is not perfect it may contain anomalies, which are like a bad
dream for database itself. Managing a database with anomalies is next to impossible.
Normalization is a method to remove all these anomalies and bring database to
consistent state and free from any kinds of anomalies.
At a basic level, normalization is the simplification of any bulk quantity to an optimum
value. In the digital world, normalization usually refers to database normalization
which is the process of organizing the columns (attributes) and tables (relations) of a
relational database to minimize data repetition. In the process of database creation,
normalization involves organizing data into optimal tables in such a way that the
results obtained are always unambiguous and clear in concept.
 Though database normalization can have the effect of duplication of data, it
completely removes data redundancy. This process can be considered as a refinement
process after the initial identification of data objects that are to be included in the
database. It involves identification of the relationship between the data objects and
defining the tables required and the columns to be added within each table.
If a database design is not done properly, it may cause several anomalies to occur in it.
Normalization is essential for removing various anomalies like:
Anomalies in Database
1) Update Anomalies: When several instances of the same data are scattered across the
database without proper relationship/link, it could cause strange conditions where a
few of the instances will get updated with new values whereas some of them will not.
This leaves the database in an inconsistent state.
2) Deletion Anomalies: Incomplete deletion of a particular data section which leaves
some residual instances. The database creator remains unaware of such unwanted data
as it is present at a different location.
3) Insertion Anomalies: This occurs when an attempt to insert data into a non-existent
record.
Paying attention to these anomalies can help to maintain a consistent database.

2c Write short note on Cartesian product with its syntax and example.
Ans. The Cartesian-product operation, denoted by a cross (×), allows us to combine
information from any two relations. We write the Cartesian product of relations r1 and
r2 as r1 × r2.A relation is by definition a subset of a Cartesian product of a setof
domains. From that definition, we should already have an intuition about thedefinition
of the Cartesian-product operation. However, since the same attributename may appear
in both r1 and r2, we need to devise a naming schema todistinguish between these
 attributes. We do so here by attaching to an attributethe name of the relation from
which the attribute originally came.
For example,the relation schema for r = instructor × teaches is:
(instructor.ID, instructor.name, instructor.dept name, instructor.salary
teaches.ID, teaches.course id, teaches.sec id, teaches.semester, teaches.year)
With this schema, we can distinguish instructor.ID from teaches.ID. For those
attributes that appear in only one of the two schemas, we shall usually drop the relation-
name prefix. This simplification does not lead to any ambiguity. We canthen write the
relation schema for r as:
(instructor.ID, name, dept name, salary
teaches.ID, course id, sec id, semester, year)
This naming convention requires that the relations that are the arguments of the
Cartesian-product operation have distinct names.

SYNTAX:-- (QUERY1) * (QUERY2)

Let there be relation A(A1,A2) and relation B(B1,B2)
THE CARTESIAN PRODUCT C of A and B which is A * B is
C=A * B
C= (A1B1 , A1B2 , A2B1 , A2B2)

2d Explain SET operators in details along with example.

Ans. Set operators allows combine results from two or more SELECT statement.SQL set
operators combine rows from different queries with strong preconditions-all involved
SELECTS must:--
i. Retrieve the same number of columns and
ii. The datatypes of corresponding columns in each involved SELECT must be
compatible.
iii. Set operators are:-- UNION, INTERSECT, DIFFERENCE OR MINUS

Union Operation (∪)

It performs binary union between two given relations and is defined as −

r ∪ s = { t | t ∈ r or t ∈ s}
Notation − r U s

Where r and s are either database relations or relation result set (temporary relation).

For a union operation to be valid, the following conditions must hold −

 • r, and s must have the same number of attributes.
• Attribute domains must be compatible.
• Duplicate tuples are automatically eliminated.
∏ author (Books) ∪ ∏ author (Articles)

Intersect operator ( ):

The intersect operators is denoted by the symbol ( ). The sql intersect operator is used
to combine two select statement but retrurns rows only from the first select statement
that are identical to a row in the second select statement. This means INTERSECT
returns only common rows returned by the two select statement.

Notation − r s

∏ author (Books) ∏ author (Articles)

Set Difference (−)

The result of set difference operation is tuples, which are present in one relation but are
not in the second relation.

Notation − r − s

Finds all the tuples that are present in r but not in s.

∏ author (Books) − ∏ author (Articles)

2 e Explain formal definitions with safety of expressions of tuples relational calculus.
Ans
Formal Definition :
 Safety of Expressions :

2.f State the difference between Relational algebra and calculus.

 BASIS FOR
RELATIONAL ALGEBRA RELATIONAL CALCULUS
COMPARISON

Basic Relational Algebra is a Procedural Relational calculus is

language. Declarative language.

States Relational Algebra states how to Relational Calculus states what

obtain the result. result we have to obtain.

Order Relational Algebra describes the order Relational Calculus does not

in which operations have to be specify the order of operations.

performed.

Domain Relational Algebra is not domain Relation Calculus can be

dependent. domain dependent.

Related It is close to a programming language. It is close to the natural

language.

3a What are constraints? What are the different types of constraints? Explain.
Ans. SQL constraints are used to specify rules for the data in a table.
Constraints are used to limit the type of data that can go into a table. This ensures the
accuracy and reliability of the data in the table. If there is any violation between the
constraint and the data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a
column, and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

• NOT NULL - Ensures that a column cannot have a NULL value.

E.g. CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar (255) NOT NULL,
FirstName varchar (255) NOT NULL,
Age int
);

• UNIQUE - Ensures that all values in a column are different.

E.g. CREATE TABLE Persons (

ID int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);

• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely

identifies each row in a table.

E.g. CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar (255) NOT NULL,
FirstName varchar (255),
Age int,
PRIMARY KEY (ID)
);

• FOREIGN KEY - Uniquely identifies a row/record in another table.

E.g. CREATE TABLE Orders (

OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
 FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

• CHECK - Ensures that all values in a column satisfies a specific condition.

• DEFAULT - Sets a default value for a column when no value is specified.

Explanation with example excepted.

3b When can a view be updated? Explain the syntax of updating a view. And also state the
difference between views and table.
An A view be updated: ---
s.
1. The view is defined based on one and only one table.

2. The view must include the PRIMARY KEY of the table based upon which the view
has been created.

3. The view should not have any field made out of aggregate functions.

4. The view must not have any DISTINCT clause in its definition.

5. The view must not have any GROUP BY or HAVING clause in its definition.

6. The view must not have any SUBQUERIES in its definitions.

7. If the view you want to update is based upon another view, the later should be
updatable.

8. Any of the selected output fields (of the view) must not use constants, strings or value
expressions.

Syntax:

UPDATE < view_name > SET<column1>=<value1>,<column2>=<value2>,.....

WHERE <condition>;

view_name Name of the virtual table or view where data will be

modified.

column1,column2 Name of the columns of the table.

value1,value2 Values for the columns which are going to be updated.

 condition Condition or criteria.

Parameters:

Updating a view must affect the rows in the corresponding table.

E.g. create view vw_emp

As

Select * from emp

Where deptno=10;

VIEWS TABLES

1. There is one type of relation which is not a part 1. A base relation is a relation that is not a derived
of the physical database. relation.
2. It has no direct or physical relation with the 2. While it can manipulate the conceptual or
database. relations stored in the data.
3. Views can be used to provide security 3. It does not provide security.
mechanism.
4. Modification through a view (e.g. insert, update, 4. Modification may be done with a base relation.
delete) not permitted).

3c Consider the relations :

Worker
(WORKER_ID,FIRST_NAME,LAST_NAME,SALARY,JOINING_DATE,DEPART
MENT)
Write the SQL queries for the following:

a. Write An SQL Query To Print The FIRST_NAME And LAST_NAME From

Worker Table Into A Single Column COMPLETE_NAME. A Space Char
Should Separate Them.
b. Write An SQL Query That Fetches The Unique Values Of DEPARTMENT
From Worker Table And Prints Its Length.
c. Write An SQL Query To Print First Three Characters Of FIRST_NAME From
Worker Table.
d. Write An SQL Query To Fetch Worker Names With Salaries >= 50000 And <=
100000.
 e. Write An SQL Query To Fetch The No. Of Workers for Each Department in the
Descending Order.

Ans a. Select CONCAT(FIRST_NAME, ' ', LAST_NAME) AS 'COMPLETE_NAME'

. from Worker;
b. Select distinct length(DEPARTMENT) from Worker;
c. Select substring(FIRST_NAME,1,3) from Worker;
d. SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) As Worker_Name, Salary
FROM worker
WHERE WORKER_ID IN
(SELECT WORKER_ID FROM worker
WHERE Salary BETWEEN 50000 AND 100000);
e. SELECT DEPARTMENT, count(WORKER_ID) No_Of_Workers
FROM worker
GROUP BY DEPARTMENT
ORDER BY No_Of_Workers DESC;

3d Write in brief about SQL with its advantages and also explain NULL value concept.
How NULL values are different from EMPTY values.
Ans. SQL is Structured Query Language, which is a computer language for storing,
manipulating and retrieving data stored in a relational database.

SQL is the standard language for Relational Database System. All the Relational
Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase,
Informix, Postgres and SQL Server use SQL as their standard database language. SQL
is widely popular because it offers the following advantages −

• Allows users to access data in the relational database management systems.

• Allows users to describe the data.

• Allows users to define the data in a database and manipulate that data.

• Allows to embed within other languages using SQL modules, libraries & pre-
compilers.

• Allows users to create and drop databases and tables.

• Allows users to create view, stored procedure, functions in a database.

• Allows users to set permissions on tables, procedures and views.

 NULL value concept:--

The NULL is the term used to represent a missing value. A NULL value in a table is a
value in a field that appears to be blank.

A field with a NULL value is a field with no value. It is very important to understand
that a NULL value is different than a zero value or a field that contains spaces.

Syntax
SQL> CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);
Here, NOT NULL signifies that column should always accept an explicit value of the
given data type. There are two columns where we did not use NOT NULL, which means
these columns could be NULL.

A field with a NULL value is the one that has been left blank during the record creation.
NULL values are different from EMPTY values:--
An empty string is a value, but is just empty. NULL is special to a database. NULLhas
no bounds, it can be used for string , integer , date , etc. fields in a database. NULL isn't
allocated any memory, the string with NULL value is just a pointer which is pointing
to nowhere in memory. A NULL value represents the absence of a value for a record in
a field (other software calls it a missing value). An empty value is a "field-formatted"
value with no significant data in it. Null has no bounds, it can be used for string, integer,
date, etc. fields in a database. Empty string is just regarding a string. If you have no
value for a field, use null, not an empty string.

3e Define Join and List its type and explain any two in details. Consider the following
relation and solve the below query:
Sample table: departments
( DEPARTMENT_ID,DEPARTMENT_NAME ,MANAGER_ID , LOCATION_ID )
Sample table: employees
 (EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER
,HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID ,
DEPARTMENT_ID )

i) Write a query in SQL to display the first name, last name, department number, and
department name for each employee.
ANS An SQL join clause combines columns from one or more tables in a relational
database. It creates a set that can be saved as a table or used as it is. A JOIN is a means
for combining columns from one (self-join) or more tables by using values common to
each.
Oracle proprietary joins are:
• Equijoin
• Non-equijoin
• Outer join
• Self join
ANSI-standard SQL specifies five types of JOIN: INNER, LEFT OUTER, RIGHT
OUTER, FULL OUTER and CROSS. As a special case, a table (base table, view, or
joined table) can JOIN to itself in a self-join.
Defining Joins
When data from more than one table in the database is required, a join condition is
used. Rows in one table can be joined to rows in another table according to common
values existing in corresponding columns, that is, usually primary and foreign key
columns.
To display data from two or more related tables, write a simple join condition in the
WHERE clause.
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
In the syntax:
table1.column denotes the table and column from which data is
retrieved
table1.column1 = is the condition that joins (or relates) the tables together
table2.column2
• When writing a SELECT statement that joins tables, precede the column name with
the table name for clarity and to enhance database access.
• If the same column name appears in more than one table, the column name must
be prefixed with the table name.
• To join n tables together, you need a minimum of n-1 join conditions. For example,
to join four tables, a minimum of three joins is required. This rule may not apply if
your table has a concatenated primary key, in which case more than one column is
required to uniquely identify each row.

Explanation of each type of join expected in brief.

SELECT E.first_name , E.last_name ,

E.department_id , D.department_name
 FROM employees E
JOIN departments D
ON E.department_id = D.department_id;

3f Differentiate between ANY and ALL operators with example & also explain
hierarchical query.
ANS ANY ALL
:
1. The ANY operator returns TRUE if any of the The ALL operator returns TRUE if all of th
subquery values meet the condition. subquery values meet the condition.
2. Evaluates to FALSE if the query returns no 2. Evaluates to TRUE if the query returns no rows
rows.
ANY compares a value to each value in a list or results from a query and evaluates to
true if the result of an inner query contains at least one row. ANY must be preceded
by comparison operators. Suppose using greater than ( >) with ANY means greater
than at least one value.

Syntax:

SELECT [column_name... | expression1 ]

FROM [table_name]
WHERE expression2 comparison_operator {ALL | ANY | SOME} ( subquery )
Parameters:

Name Description

column_name Name of the column of the table.

expression1 Expression made up of a single constant, variable, scalar function, or column

can also be the pieces of a SQL query that compare values against other value
perform arithmetic calculations.

table_name Name of the table.

 WHERE Compares a scalar expression until a match is found for ANY operator. One o
expression2 rows must match the expression to return a Boolean TRUE value for the ANY

comparison_operator Compares the expression to the subquery. The comparison must be a standard
comparison operator (=, <>, !=, >, >=, <, or <=).

ALL is used to select all records of a SELECT STATEMENT. It compares a value to

every value in a list or results from a query. The ALL must be preceded by
the comparison operators and evaluates to TRUE if the query returns no rows. For
example, ALL means greater than every value, means greater than the maximum value.
Suppose ALL (1, 2, 3) means greater than 3.

Syntax:

SELECT [column_name... | expression1 ]

FROM [table_name]
WHERE expression2 comparison_operator {ALL | ANY | SOME} ( subquery )

HIERARCHAIL QUERIES:--

A hierarchial query is the one that works on tree like relationship.It loops
through a result set and returns rows in a hierarchial sequence.It is used when a parent
has a child element and each child elememt may have one or more child elements.

Syntax:--

SELECT column_list

FROM tablename

[WHERE Search_condition]

START WITH row_specification

CONNECT BY PRIOR connect_expression

[ORDER BY list]

STARTWITH specifies the root rows of the hierarchy.

 CONNECTBY specifies the relationship between parent rows and child rows of the
hierarchy.

e.g. SELECT empid,ename,mgr

FROM emp

Where empid<7500

CONNECT BY PRIOR empid=mgr

4a List the ACID properties. Explain the usefulness of each.

ANS • Atomicity. Either all operations of the transaction are reflected properly in
the database, or none are.
• Consistency. Execution of a transaction in isolation (that is, with no other
transaction executing concurrently) preserves the consistency of the database.
• Isolation. Even though multiple transactions may execute concurrently, the
system guarantees that, for every pair of transactions Ti and Tj , it appears to Ti
that either Tj finished execution before Ti started or Tj started execution after
Ti finished. Thus, each transaction is unaware of other transactions executing
concurrently in the system.
• Durability. After a transaction completes successfully, the changes it has
made to the database persist, even if there are system failures.

4b Explain the concept of serializability and explain in detail view serializability.

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

View Serializability
o A schedule will view serializable if it is view equivalent to a serial schedule.
o If a schedule is conflict serializable, then it will be view serializable.
o The view serializable which does not conflict serializable contains blind writes.
View Equivalent

Two schedules S1 and S2 are said to be view equivalent if they satisfy the following
conditions:

1. Initial Read

An initial read of both schedules must be the same. Suppose two schedule S1 and S2.
In schedule S1, if a transaction T1 is reading the data item A, then in S2, transaction T1
should also read A.

Above two schedules are view equivalent because Initial read operation in S1 is done
by T1 and in S2 it is also done by T1.

2. Updated Read

In schedule S1, if Ti is reading A which is updated by Tj then in S2 also, Ti should

read A which is updated by Tj.
Above two schedules are not view equal because, in S1, T3 is reading A updated by T2
and in S2, T3 is reading A updated by T1.

3. Final Write

A final write must be the same between both the schedules. In schedule S1, if a
transaction T1 updates A at last then in S2, final writes operations should also be done
by T1.

Above two schedules is view equal because Final write operation in S1 is done by T3
and in S2, the final write operation is also done by T3.

Example:

Schedule S

With 3 transactions, the total number of possible schedule

1. = 3! = 6
2. S1 = <T1 T2 T3>
3. S2 = <T1 T3 T2>
4. S3 = <T2 T3 T1>
5. S4 = <T2 T1 T3>
6. S5 = <T3 T1 T2>
7. S6 = <T3 T2 T1>

Taking first schedule S1:

Schedule S1

Step 1: final updation on data items

In both schedules S and S1, there is no read except the initial read that's why we don't
need to check that condition.

Step 2: Initial Read

The initial read operation in S is done by T1 and in S1, it is also done by T1.

Step 3: Final Write

The final write operation in S is done by T3 and in S1, it is also done by T3. So, S and
S1 are view Equivalent.

The first schedule S1 satisfies all three conditions, so we don't need to check another
schedule.

Hence, view equivalent serial schedule is:

1. T1 → T2 → T3
4c What are concurrent transaction? Explain in details the main features of concurrent
execution.
ANS The database system must control the interaction among the concurrent transaction to
prevent them from destroying the consistency of the database is termed as concurrent
control scheme.
The main features of the concurrent execution are given below:-
1.Improved throughput and resources utilization
2.Reducing waiting time

Example & explanation in detail is excepted.

4d What are some disadvantages of time stamping methods for concurrency control?
And also explain timestamp ordering protocol in detail.
ANS
The locking protocols that we have described thus far determine the order between
every pair of conflicting transactions at execution time by the first lock that both
members of the pair request that involves incompatible modes. Another method for
determining the serializability order is to select an ordering among transactions
inadvance.The mostcommon method fordoingsoistouse a timestamp-ordering
scheme.
15.4.1 Timestamps With each transaction T i in the system, we associate a unique
fixed timestamp, denoted by TS(T i). This timestampis assigned by the database
systembefore the transaction T i starts execution. If a transaction T i has been
assigned timestamp TS(T i), and a new transaction Tj enters the system, then TS(T i)
< TS(Tj). There are two simple methods for implementingthis scheme:
1. Use the value of thes ¯
ystem clock as the timestamp; that is, a transaction’s timestampisequaltothe
valueoftheclockwhenthetransaction entersthe system. 2. Use a logical counter that is
incremented after a new timestamp has been assigned; that is, a transaction’s
timestamp is equal to the value of the counter when the transaction enters the system.

The timestamps of the transactions determine the serializability order.

Thus, if TS(T i) < TS(Tj), then the system must ensure that the produced schedule is
equivalenttoaserialscheduleinwhichtransaction T i appearsbeforetransaction Tj.
Toimplementthisscheme,weassociatewitheachdataitemQtwotimestamp values:
• W-timestamp(Q) denotes the largest timestamp of any transaction that executed
write(Q) successfully. • R-timestamp(Q) denotes the largest timestamp of any
transaction that executed read(Q) successfully.
These timestamps are updated whenever a new read(Q) orwrite(Q) instruction is
executed.
15.4.2 The Timestamp-Ordering Protocol The timestamp-ordering protocol ensures
that any conflicting read and write operations are executed in timestamp order. This
protocol operatesas follows:
1. Suppose that transaction T i issues read(Q). a. If TS(T i) < W-timestamp(Q), then
T i needs to read a value of Q that was alreadyoverwritten.Hence, the readoperationis
rejected,and T i is rolled back. b. If TS(T i)≥W-timestamp(Q), then the read
operation is executed, and R-timestamp(Q)issettothemaximumofR-
timestamp(Q)andTS(T i). 2. Suppose that transaction T i issues write(Q). a. If TS(T i)
< R-timestamp(Q), then the value of Q that T i is producing
wasneededpreviously,andthesystemassumedthatthatvaluewould never be produced.
Hence, the system rejects the write operation and rolls T i back. b. IfTS(T i) < W-
timestamp(Q),then T i isattemptingtowriteanobsolete value of Q. Hence, the system
rejects this write operation and rolls T i back. c. Otherwise, the system executes the
write operation and sets W-timestamp(Q) to TS(T i).
If a transaction T i is rolled back by the concurrency-control scheme as result of
issuanceofeitherareadorwriteoperation,thesystemassignsitanewtimestamp and
restartsit. To illustrate this protocol, we consider transactions T25 and T26.
Transaction T25 displaysthe contents of accounts A and B:
T25: read(B); read(A); display(A + B).
Transaction T26 transfers $50 from account B to account A, and then displays the
contents of both:
T26: read(B); B :=B−50; write(B); read(A); A := A + 50; write(A); display(A + B).
In presenting schedules under the timestamp protocol, we shall assume that a
transactionisassignedatimestampimmediatelybeforeitsfirstinstruction.Thus,
inschedule3ofFigure15.17,TS(T25)<TS(T26),andthescheduleispossibleunder the
timestamp protocol. We notethat theprecedingexecutioncan alsobeproducedbythe
two-phase locking protocol. There are, however, schedules that are possible under the
twophase locking protocol, but are not possible under the timestamp protocol, and
vice versa(see Exercise 15.29). The timestamp-ordering protocol ensures conflict
serializability. This is because conflicting operations are processed in timestamp
order. Theprotocolensuresfreedomfromdeadlock,sincenotransactioneverwaits.
However, there is a possibility of starvation of long transactions if a sequence of
conflicting short transactions causes repeated restarting of the long transaction. If a
transaction is suffering from repeated restarts, conflicting transactions need to be
temporarilyblocked to enable the transaction to finish.

T25 T26 read(B) read(B) B := B−50 write(B) read(A) read(A) display(A+B ) A :=

A+50 write(A) display(A+B).
The protocol can generate schedules that are not recoverable.However,it can be
extended to make the schedules recoverable, in one of several ways:
• Recoverability and cascadelessness can be ensured by performing all writes
together at the end of the transaction. The writes must be atomic in the
followingsense:Whilethewritesareinprogress,notransactionispermitted to access any
of the data items that have been written. •
Recoverabilityandcascadelessnesscanalsobeguaranteedbyusingalimited form of
 locking, whereby reads of uncommitted items are postponed until the transaction that
updated the item commits
• Recoverability alone can been sured by tracking uncommitted writes,and allowing
a transactionT i tocommitonlyafterthecommitofanytransactionthat wrote a value that
T i read.

4e What benefit does rigorous two-phase locking provide? How does it compare with
other forms of two-phase locking?
ANS Rigorous two-phase locking has the advantages of strict 2PL. In addition
it has the property that for two conflicting transactions, their commit order
is their serializability order. In some systems users might expect this behavior.
Also, it's implementation is easier than strict 2PL.
In rigorous two-phase locking protocol a transaction is not allowed to release any
lock ( shared and exclusive until it commit ). This means that until the transaction
commits , other transactions might aquired a shared lock on a data items , on which
the un-commited transaction has shared lock but can not aquire any lock on an data
items , on which the uncommited transaction has an exclusive lock.
• Two-Phase Locking (2PL) is a concurrency control method which divides the
execution phase of a transaction into three parts.
• It ensures conflict serializable schedules.
• If read and write operations introduce the first unlock operation in the transaction,
then it is said to be Two-Phase Locking Protocol.

This protocol can be divided into two phases,

1. In Growing Phase, a transaction obtains locks, but may not release any lock.
2. In Shrinking Phase, a transaction may release locks, but may not obtain any
lock.

• Two-Phase Locking does not ensure freedom from deadlocks.

Types of Two – Phase Locking Protocol

Following are the types of two – phase locking protocol:

1. Strict Two – Phase Locking Protocol

2. Rigorous Two – Phase Locking Protocol
3. Conservative Two – Phase Locking Protocol

1. Strict Two-Phase Locking Protocol

 • Strict Two-Phase Locking Protocol avoids cascaded rollbacks.
• This protocol not only requires two-phase locking but also all exclusive-locks
should be held until the transaction commits or aborts.
• It is not deadlock free.
• It ensures that if data is being modified by one transaction, then other transaction
cannot read it until first transaction commits.
• Most of the database systems implement rigorous two – phase locking protocol.
2. Rigorous Two-Phase Locking
• Rigorous Two – Phase Locking Protocol avoids cascading rollbacks.
• This protocol requires that all the share and exclusive locks to be held until the
transaction commits.
3. Conservative Two-Phase Locking Protocol
• Conservative Two – Phase Locking Protocol is also called as Static Two – Phase
Locking Protocol.
• This protocol is almost free from deadlocks as all required items are listed in
advanced.
• It requires locking of all data items to access before the transaction starts.

4f If deadlock is avoided by deadlock-avoidance schemes, is starvation still possible?

Explain your answer.
ANS
A transaction may become the victim of deadlock-prevention rollback arbitrarily
many times, thus creating a potential starvation situation.

Explanation in details expected

5a What is the use of % TYPE attributes and how it is beneficial while declaring the
variable?
Ans. The %TYPE attribute let’s use the data type of a field, record, nested table, database
column, or variable in your own declarations, instead of hardcoding the type names.
You can use the %TYPE attribute as a datatype specified when declaring constants,
variables, fields, and parameters. If the types that you reference change, your
declarations are automatically updated. This technique saves you from making code
changes when, for example, the length of a VARCHAR2 column is increased.

Syntax

Keyword and Parameter Description

collection_name
A nested table, index-by table, or varray previously declared within the current scope.

cursor_variable_name
A PL/SQL cursor variable previously declared within the current scope. Only the value
of another cursor variable can be assigned to a cursor variable.

db_table_name.column_name
A table and column that must be accessible when the declaration is elaborated.

object_name
An instance of an object type, previously declared within the current scope.
record_name
A user-defined or %ROWTYPE record, previously declared within the current scope.
record_name.field_name
A field in a user-defined or %ROWTYPE record, previously declared within the current
scope.
variable_name
A variable, previously declared in the same scope.
 The %TYPE attribute is particularly useful when declaring variables, fields, and
parameters that refer to database columns. Your code can keep working even when the
lengths or types of the columns change.The NOT NULL column constraint is not
inherited by items declared using %TYPE.Examples

DECLARE
EMPLOYEEID EMP.EMPID%TYPE;
BEGIN
SELECT EMPID INTO EMPLOYEEID FROM EMP
WHERE ENAME = ‘KING’;
DBMS_OUTPUT.PUT_LINE(EMPLOYEEID);
END;
/

5b Illustrate the attributes of Implicit cursor with examples.

Ans. Implicit cursors are automatically created by Oracle whenever an SQL statement is
Executed, when there is no explicit cursor for the statement. Programmers cannot
control
The implicit cursors and the information in it.
In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which
Always has attributes such as %FOUND, %ISOPEN, %NOTFOUND, and
%ROWCOUNT. The SQL cursor has additional attributes, %BULK_ROWCOUNT
and
%BULK_EXCEPTIONS, designed for use with the FORALL statement. The
following
Table provides the description of the most used attributes:
Attribute
%FOUND: -- Returns TRUE if an INSERT, UPDATE, or DELETE statement affected
one or more rows or a SELECT INTO statement returned one or more rows. Otherwise,
it returns FALSE.

%NOTFOUND:-The logical opposite of %FOUND. It returns TRUE if an INSERT,

UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement
returned no rows. Otherwise, it returns FALSE.

%ISOPEN:--Always returns FALSE for implicit cursors, because Oracle closes the
SQL cursor automatically after executing its associated SQL statement.

%ROWCOUNT:--Returns the number of rows affected by an INSERT, UPDATE, or

DELETE statement, or returned by a SELECT INTO statement.
Any SQL cursor attribute will be accessed as sql%attribute_name as shown below in
the example.
The following program will update the table and increase the salary of each customer
by
 500 and use the SQL%ROWCOUNT attribute to determine the number of rows
affected:
DECLARE
Total_rows number (2);
BEGIN
UPDATE customers
SET salary = salary + 500;
IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%found THEN
total_rows:= sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected ');
END IF;
END;
/

5c Explain the function Raise_Application_Error () with example.

Ans. The raise_application_error is actually a procedure defined by Oracle that allows the
developer to raise an exception and associate an error number and message with the
procedure. This allows the application to raise application errors rather than just Oracle
errors. Error numbers are defined between -20,000 and -20,999.
RAISE_APPLICATION_ERROR ( )

RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to

display the user-defined error messages along with the error number whose range is in
between -20000 and -20999.Whenever a message is displayed using
RAISE_APPLICATION_ERROR, all previous transactions which are not committed
within the PL/SQL Block are rolled back automatically (i.e. change due to INSERT,
UPDATE, or DELETE statements).

RAISE_APPLICATION_ERROR raises an exception but does not handle

it.RAISE_APPLICATION_ERROR is used for the following reasons,

a) to create a unique id for an user-defined exception.

b) To make the user-defined exception look like an Oracle error.

The General Syntax to use this procedure is:

RAISE_APPLICATION_ERROR (error_number, error_message);

 • The Error number must be between -20000 and -20999
• The Error_message is the message you want to display when the error occurs.

Steps to be followed to use RAISE_APPLICATION_ERROR procedure:

1. Declare a user-defined exception in the declaration section.
2. Raise the user-defined exception based on a specific business rule in the execution
section.
3. Finally, catch the exception and link the exception to a user-defined error number in
RAISE_APPLICATION_ERROR.

DECLARE
Huge_quantity EXCEPTION;
CURSOR product_quantity is
SELECT p.product_name as name, sum (o.total_units) as units
FROM order_tems o, product p
WHERE o.product_id = p.product_id;
Quantity order_tems.total_units%type;
Up_limit CONSTANT order_tems.total_units%type: = 20;
Message VARCHAR2 (50);
BEGIN
FOR product_rec in product_quantity LOOP
Quantity: = product_rec. Units;
IF quantity > up_limit THEN
RAISE huge_quantity;
ELSIF quantity < up_limit THEN
v_message:= 'The number of unit is below the discount limit.’
END IF;
Dbms_output.put_line (message);
END LOOP;
EXCEPTION
WHEN huge_quantity THEN
raise_application_error (-2100, 'The number of unit is above the discount
limit.');
END;
/

5d List & explain the various features of PL/SQL & also differentiate between
Anonymous blocks and Subprograms.
Ans. High performance: PL/SQL is high performance transaction processing language.
• Code Re-usability: It support code re-usability, it means no need to write code
again and again same like SQL.
• Error Handling: when any error are occurred then it return user friendly error
message.
• Procedural Language Capability: It consists of procedural language constructs
such as conditional statements (if...else) and loops statements (for loop)
• Block Statement: It consists of blocks of code, which can be nested within each
other. Each and every block forms a unit of a task or a logical module.
• Better performance: oracle engine processes multiple SQL statements
simultaneously as a single block, due to this reducing network traffic.
• Declare variable: It give you control to declare variable and access them within
the block.

• It support looping and conditional statements

• Reduce Network Traffic: oracle engine processes multiple SQL statements
simultaneously as a single block, due to this reducing network traffic.
• Portable application: Applications are written in PL/SQL are portable in any
available Operating system.

Differences between Anonymous Blocks and Subprograms

Anonymous blocks Subprograms
Unnamed PL/SQL blocks Named PL/SQL blocks
Compiled every time Compiled only once
Does not store in database Stores in database
Cannot be invoked by other applications These are named and therefore can be
invoked by other applications
Do not return values Subprogram called functions must return
values
Cannot take parameters Can take parameters

5e What are packages in PL/SQL? List and explain the various advantages of packages.
Create a package to display the employee name and salary.
Ans. Packages bundle related PL/SQL types, items, and subprograms into one container. For
example, a Human Resources package can contain hiring and firing procedures,
commission and bonus functions, and tax exemption variables.
A package usually has a specification and a body, stored separately in the database.
The specification is the interface to your applications. It declares the types, variables,
constants, exceptions, cursors, and subprograms available for use. The package
specification may also include PRAGMAs, which are directives to the compiler.
The body fully defines cursors and subprograms, and so implements the specification.
The package itself cannot be called, parameterized, or nested. Still, the format of a
package is similar to that of a subprogram. Once written and compiled, the contents can
be shared by many applications.
When you call a packaged PL/SQL construct for the first time, the whole package is
loaded into memory. Thus, later calls to constructs in the same package require no disk
input/output (I/O).

Advantages of package:--
-----------------------
A. Modularity:--Encapsulate related constructs.

B. Easier Application Design: -- Code and compile specification and body separately.

C. Hiding Information: -- Only the declarations in the package specification are

visible and accessible to application. Private constructs in the package body are
hidden and inaccessible.
- All coding is hidden in the package body.

D. Added Functionality: - Persistency of variables and coursors.

E. Better Performance:--The entire package is loaded into memory when the package
is first referenced. There is only one copy in memory for all users. The dependency
hierarchy is simplified.

F. Overloading:--Multiple subprograms of the same name.

Example to create package to display name and salary must be given.

5f What are triggers? Explain the syntax for creating a trigger in PL/SQL.List the
benefits of creating trigger in PL/SQL.
Ans. Triggers are stored programs, which are automatically executed or fired when some
events occur. Triggers are, in fact, written to be executed in response to any of the
following events –
• A database manipulation (DML) statement (DELETE, INSERT, or
UPDATE)

• A database definition (DDL) statement (CREATE, ALTER, or DROP).

• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or

SHUTDOWN).
Triggers can be defined on the table, view, schema, or database with which the event
is associated.
Creating Triggers
The syntax for creating a trigger is −

CREATE [OR REPLACE ] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF }

{INSERT [OR] | UPDATE [OR] | DELETE}

[OF col_name]

ON table_name

[REFERENCING OLD AS o NEW AS n]

[FOR EACH ROW]

WHEN (condition)

DECLARE

Declaration-statements

BEGIN

Executable-statements

EXCEPTION

Exception-handling-statements

END;

Where,
 • CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an
existing trigger with the trigger_name.

• {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will be
executed. The INSTEAD OF clause is used for creating trigger on a view.

• {INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML

operation.

• [OF col_name] − This specifies the column name that will be updated.

• [ON table_name] − This specifies the name of the table associated with the
trigger.

• [REFERENCING OLD AS o NEW AS n] − This allows you to refer new and

old values for various DML statements, such as INSERT, UPDATE, and
DELETE.

• [FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will be
executed for each row being affected. Otherwise the trigger will execute just
once when the SQL statement is executed, which is called a table level trigger.

• WHEN (condition) − This provides a condition for rows for which the trigger
would fire. This clause is valid only for row-level triggers.

Benefits of Triggers

• Generating some derived column values automatically

• Enforcing referential integrity
• Event logging and storing information on table access
• Auditing
• Synchronous replication of tables
• Imposing security authorizations
• Preventing invalid transactions
• Serializability is a concurrency scheme where the concurrent transaction is
equivalent to one that executes the transactions serially.
• A schedule is a list of transactions.
• Serial schedule defines each transaction is executed consecutively without any
interference from other transactions.
• Non-serial schedule defines the operations from a group of concurrent
transactions that are interleaved.
• In non-serial schedule, if the schedule is not proper, then the problems can
arise like multiple update, uncommitted dependency and incorrect analysis.
• The main objective of serializability is to find non-serial schedules that allow
transactions to execute concurrently without interference and produce a
database state that could be produced by a serial execution.