Q3) Attempt any FOUR of the following (Out
of FIVE) : [4 × 4 = 16]
1. What is exception handling? Explain
predefined exceptions.
2.
What is exception handling ? Explain system
defined exceptions.
An error occurs during the program execution
is called Exception in PL/SQL.
PL/SQL facilitates programmers to catch such
conditions using exception block in the program and an appropriate action is
taken against the error condition.
There are two type of exceptions:
1.System-defined Exceptions (Pre-Defined Exception)
2.User-defined
Exceptions
Syntax for exception handling:
Following is a general syntax for exception
handling:
1.
DECLARE
2.
<declarations
section>
3.
BEGIN
4.
<executable
command(s)>
5.
EXCEPTION
6.
<exception handling goes
here >
7.
WHEN exception1 THEN
8.
exception1-handling-statements
9.
WHEN exception2
THEN
10.
exception2-handling-statements
11.
WHEN exception3 THEN
12.
exception3-handling-statements
13.
........
14.
WHEN others THEN
15.
exception3-handling-statements
16.
END;
PL/SQL Pre-defined Exceptions
There are many pre-defined exception in PL/SQL which are executed when any
database rule is violated by the programs.
For example: NO_DATA_FOUND is a pre-defined exception which is raised when a SELECT INTO
statement returns no rows.
Following is a list of some important
pre-defined exceptions:
Exception Description
1.
ACCESS_INTO_NULL - It is raised when a NULL object is
automatically assigned a value.
2.CASE_NOT_FOUND - It is raised when none of
the choices in the "WHEN" clauses
of a CASE statement is selected, and there is no else clause.
3. DUP_VAL_ON_INDEX :- It is raised when
duplicate values are attempted to be
stored in a column with unique index.
4.INVALID_CURSOR
:- It is raised when attempts are made to make a cursor operation that is not
allowed, such as closing an unopened
cursor.
5. INVALID_NUMBER - It is raised when the
conversion of a character string into a number fails because the string does
not represent a valid number.
6.NO_DATA_FOUND:- It is raised when a select
into statement returns no rows
7. ZERO_DIVIDE:- It is raised when an attempt
is made to divide a number by zero.
8. TOO_MANY_ROWS :- It is raised when a
SELECT INTO statement returns more than one row.
9. VALUE_ERROR :- It is raised when an
arithmetic, conversion, truncation, or size-constraint error occurs.
Explain failure classification in detail.
Failure Classification
To find that where the problem has occurred,
we generalize a failure into the following categories:
System crash
Disk failure
1. Transaction failure
The transaction failure occurs when it fails
to execute or when it reaches a point from where it can't go any further. If a
few transaction or process is hurt, then this is called as transaction failure.
Syntax error: It occurs where the DBMS itself
terminates an active transaction because the database system is not able to
execute it. For example, The system aborts an active transaction, in case of
deadlock or resource unavailability.
2. System Crash
System failure can occur due to power failure
or other hardware or software failure. Example: Operating system error.
Fail-stop assumption: In the system crash,
non-volatile storage is assumed not to be corrupted.
3. Disk Failure
It occurs where hard-disk drives or storage
drives used to fail frequently. It was a common problem in the early days of
technology evolution.
Disk failure occurs due to the formation of
bad sectors, disk head crash, and unreachability to the disk or any other
failure, which destroy all or part of disk storage.
What is log? Explain log based recovery.
1.The log is a sequence of records. Log of each transaction is
maintained in some stable storage so that if any failure occurs, then it
can be recovered from there.
2.If any operation is performed on the database, then it will be recorded
in the
log.
3.But the process of storing the logs should be done before the actual
transaction
is applied in the database.
Deferred database modification
It modifies the database after completion of
transaction. The database modification is deferred or delayed until the last
operation of the transaction is executed. Update log records maintain the new
value of the data item.
Recover system uses one operation which is as
follows −
Redo(Ti) − All data items updated by the transaction Ti
are set to a new value.
Immediate database modification
It modifies the database after a write
operation, database modification is immediately done when a transaction
performs an update/ write operation. Update log records maintain both old and
new values of data items.
The recovery system uses two operations,
which are as follows −
Undo(Ti) − All data items updated by the
transaction Ti, are set to old value.
Redo(Ti) − All data items updated by the transaction Ti are set to a new
value.
A series of operation from one transaction to another
transaction is known as a schedule.
Serial Schedule-The serial schedule is a type of schedule where one
transaction is executed completely before starting another transaction
Non-serial Schedule- If interleaving of operations is allowed, then there
will be non-serial schedule.
Serializable schedule —The serializability of schedules is used to find
non-serial schedules that allow the transaction to execute concurrently without
interfering with one another.
Recoverable Schedule:-Schedules in which
transactions commit only after all transactions whose changes they read commit
are called recoverable schedules.
Cascadeless Schedule:-Schedules in which
transactions read values only after all transactions whose changes they are
going to read commit are called cascadeless schedules.
What is serializability ?
Explain types with example.
Serializability is a term
that is a property of the system that describes how the different process
operates the shared data.
View serializability
A schedule is
view-serializability if it is viewed equivalent to a serial schedule.
The rules it follows are as follows −
·T1 is reading the initial
value of A, then T2 also reads the initial value of A.
· T1 is the reading value
written by T2, then T2 also reads the value written by T1.
· T1 is writing the final
value, and then T2 also has the write operation as the final value.
Conflict serializability
· It orders any conflicting
operations in the same way as some serial execution. A pair of operations is
said to conflict if they operate on the same data item and one of them is a
write operation.
That means
·
Readi(x) readj(x) - non
conflict read-read operation
·
Readi(x) writej(x) -
conflict read-write operation.
·
Writei(x) readj(x) -
conflict write-read operation.
·
Writei(x) writej(x) -
conflict write-write operation.
List RDBMS packages. Explain any one in detail
A relational database
management system (RDBMS) stores data in a tabular form where a column
represents a property and each row in a table represents a record. RDBMS allows
Create, Read, Update, and Delete (CRUD) operations. Structured Query Language
(SQL) is the language that is used to query, update, and delete data in
relational database management systems (RDBMS). SQL is a standard query
language. SQL language queries are also known as SQL commands or SQL
statements.
Microsoft Access
Microsoft Access is a
relational database management system (RDBMS) that is part of the Microsoft
Office suite of applications. It is widely used by small and medium-sized
businesses as well as individuals for managing data and creating custom
applications.
Benefits of Microsoft
Access:
User-friendly: One of the
key benefits of Microsoft Access is its user-friendliness. It provides a
user-friendly interface that allows users to create and manage databases
without requiring any specialized knowledge or training.
SQL Server
SQL Server is a relational
database management system (RDBMS) developed by Microsoft Corporation.
Businesses and organizations of all sizes use it to store, organize, and
retrieve large amounts of data efficiently and securely.
Some of the key features
of SQL Server include:
·
Scalability: SQL Server is
designed to scale from small systems to large enterprise deployments, providing
support for multiple processors, storage systems, and network configurations.
·
High Availability: The database
has built-in mechanisms for data protection and disaster recovery, including
backup and recovery, replication, and clustering technologies, which ensure
that data is always available.
·
Security: SQL Server
provides various security features to protect data, such as encryption, access
controls, and auditing, ensuring that data is kept safe from unauthorized
access and tampering.
·
Performance: The database
is optimized for high performance, providing advanced caching, indexing, and
query optimization technologies, as well as support for in-memory processing
and parallel execution.
MySQL
MySQL is a free and
open-source relational database management system (RDBMS) that is widely used
in web applications and other software. It was developed by Swedish company
MySQL AB, which was later acquired by Oracle Corporation.
Some of the key features
of MySQL include:
·
Scalability: MySQL is designed
to scale from small systems to large enterprise deployments, providing support
for multiple processors, storage systems, and network configurations.
·
High Availability: The
database has built-in mechanisms for data protection and disaster recovery,
including backup and recovery, replication, and clustering technologies, which
ensure that data is always available.
·
Security: MySQL provides
various security features to protect data, such as encryption, access controls,
and auditing, ensuring that data is kept safe from unauthorized access and
tampering.
·
Performance: The database
is optimized for high performance, providing advanced caching, indexing, and query
optimization technologies, as well as support for in-memory processing and
parallel execution.
What is deadlock ? Explain deadlock
detection.
Deadlock Detection
In a database, when a transaction waits
indefinitely to obtain a lock, then the DBMS should detect whether the
transaction is involved in a deadlock or not. The lock manager maintains a Wait
for the graph to detect the deadlock cycle in the database.
Wait for Graph
This is the suitable method for deadlock
detection. In this method, a graph is created based on the transaction and
their lock. If the created graph has a cycle or closed loop, then there is a
deadlock.
The wait for the graph is maintained by the
system for every transaction which is waiting for some data held by the others.
The system keeps checking the graph if there is any cycle in the graph.
The wait for a graph for the above scenario is shown below:
What is trigger ? Describe types of trigger.
Triggers are used to protect
the data integrity in the database.
Types of Triggers in SQL
Following are the six types of triggers in SQL:
1.
AFTER INSERT Trigger
This trigger is invoked after the insertion of data in the table.
2. AFTER UPDATE Trigger
This trigger is invoked in SQL after the modification of the data in the table.
3.
AFTER DELETE Trigger
This trigger is invoked after deleting the data from the table.
4. BEFORE INSERT Trigger
This trigger is invoked before the inserting the record in the table.
5.
BEFORE UPDATE Trigger
This trigger is invoked before the updating the record in the table.
6. BEFORE DELETE Trigger
This trigger is invoked before deleting the record from the table.
Syntax of Trigger in SQL
1.
CREATE TRIGGER
Trigger_Name
2.
[ BEFORE | AFTER ] [
Insert | Update | Delete]
3.
ON [Table_Name]
4.
[ FOR EACH ROW | FOR EACH
COLUMN ]
5.
AS
6. Set of SQL Statement
Advantages of Triggers in
SQL
1.
SQL provides an alternate
way for maintaining the data and referential integrity in the tables.
2.
Triggers helps in executing
the scheduled tasks because they are called automatically.
3.
They catch the errors in the
database layer of various businesses.
4. They allow the database users to validate values before
inserting and updating.
Disadvantages of Triggers
in SQL
1.
They are not compiled.
2.
It is not possible to find
and debug the errors in triggers.
3.
If we use the complex code
in the trigger, it makes the application run slower.
4. Trigger increases the high load on the database system.
