Structured Query Language- Assignment

This article discusses the three types of exceptions in SQL, their handling, and the importance of database backup in a company. It also covers the different levels of backups in SQL server. The article cites various sources to support the discussion.

Running head: STRUCTURED QUERY LANGUAGE
Structured Query Language
Name of Student-
Name of University-
Author’s Note-

1STRUCTURED QUERY LANGUAGE
Question 4
There are three different types of exception in SQL. There are predefined defined
exceptions, non-defined exceptions, and user-defined exceptions. The pre-defined exceptions are
the condition of error that are mainly defined by the PL/SQL. The non-defined exceptions
mainly includes standard TimesTen errors and the user-defined exceptions are the exceptions
that are specific for a particular application (Ping, Hong-Wei & Nan, 2014). For the predefined
exceptions can occur with 20 errors and occurs mainly in the PL/SQL code. These errors are not
declared, but are predefined by the TimesTen. The TimesTen implicitly specifies the error. The
non-defined TimesTen error includes other TimesTen errors. These non-defined errors can be
declared in declarative section in the application. The user-defined error can be declared in
declarative section. The developer raises this exception explicitly.
Trapping predefined TimesTen errors are mainly done by referencing the predefined
name of the routine of exception handling. The PL/SQL generally declares the predefined
exception as a STANDARD package. Exception handling can also be done with blocks of TRY
and CATCH. Throw statement allows the user to through exception that are caught in an
exception handling block (Coronel & Morris, 2016). The THROW statement is a different
mechanism that acts similarly as RAISEERROR statement. The TRY / CATCH / THROW
statement can be inserted with the statements in batch or can be used for rolling back statement
group if some error occurs. When THROW returns execution of batch to caller, the content of
the employee remains the same.
TRY and CATCH catches all the execution errors that have high severity level and do not
close the connection of database. The syntax of TRY and CATCH is a TRY block is followed

2STRUCTURED QUERY LANGUAGE
immediately by a CATCH block. The block inside the TRY and CATCH with be analyzed if it
has any syntax or not. One particular TRY and CATCH construct does not span many batches.
There cannot be multiple blocks span of TRANSACT SQL statements and there cannot have If-
else construction (Cherry, 2015). If no errors are found in code that comes in TRY block, and
when the last statement is executed then the control jumps to the CATCH block. If there is no
error I the CATCH block, the control is passed back to statement called stored procedure or fire
is triggered. After that the control is passed to the END CATCH. The errors in the CATCH block
are then not returned to calling application. There are many functions that can be used in the
CATCH block for knowing about the error. ERROR NUMBER () is the function for returning
the total number of error that has occurred in the block. ERROR PROCEDURE () returns name
of the procedure that is stored. ERROR LINE () is a function in CATCH block that returns the
number of line that has error in it.
Question 6
a) The importance of database backup is needed in a company to keep all the information
safe. There always remains a risk of crashing the computer system, facing human error as well as
disasters that might happen to the data. So, it is always recommended to have a backup for the
data. The data backup service can be accomplished by using offsite server or by using separate
drives for storing large amount of data (Mistry & Misner, 2014). Without the database backup,
recovery of data is not possible if the system crashes. The backup of database can be
accomplished by offset server or by using different drives for storing large amount of
information. Without the data backup system, the data recovery becomes perilous situation
where the businesses lose information when worst happens.

3STRUCTURED QUERY LANGUAGE
There are many levels of backups in SQL server. They levels of backups are full backups,
file backups, partial backups, differential backups, file group backups, mirror backups, Copy-
only backup, tail log backup and transaction log backups. In full backup system, the database can
take copy of the full database that includes the part of transaction log file for recovering the full
database file. Backups are generally taken on backup device that are on a local network disk or
on a local tape drive (Freeman & Hart, 2016). The database of differential backup mainly
includes extents that are changed from the full database backup. The Server of SQL mainly looks
in the Differential Changed Map (DCM) page for identifying the extents that were changed with
the when there was fill backup of database and captures them only. So, differential backups are
faster compared to full backup database. The transaction backup can be done with full recovery
mode or with recovery model (Ping, Hong-Wei & Nan, 2014). The transaction log mainly
captures the record of transaction log that grows long significantly. The transaction log mainly
gets truncated. The tail-log backup is if the database gets offline or damaged, the log file
transaction is available. This backup captures many log records that are been taken as backed up.
The last level of database backup is used when the database grows (Jukic, Vrbsky & Nestorov,
2016). When the database is big in size, it is very difficult to make full backup of the database.
The partial backup is mainly done when the database is a big having many file groups and few of
the files keeps on changing. By using partial backup, primary file group can be kept as backup
(Graefe, 2015). The last backup level is the copy only backup. This includes copying the
database maintain the normal sequence of the conventional operation of backup.
b) Database failure mainly occurs when there is system crash, user error, carelessness,
statement failure, corrupting of data intentionally, software errors application, network failure,
physical disasters, and media failures. Sources of database failures comes when the system

4STRUCTURED QUERY LANGUAGE
crashes, the system hang ups, as well as the system are to be rebooted (Zhang & Li, 2017).
Database failure occurs due to hardware malfunction or because of a bug because of the database
software or because of operating system. There can be content storage loss and the transaction
processing stops immediately. A database failure can be stated as inability of database for
executing the SQL statement.

5STRUCTURED QUERY LANGUAGE
References
Cherry, D. (2015). Securing SQL server: protecting your database from attackers. Syngress.
Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management.
Cengage Learning.
Freeman, R. G., & Hart, M. (2016). Oracle Database 12c Oracle RMAN Backup and Recovery.
McGraw-Hill Education Group.
Graefe, G. (2015). Instant recovery for data center savings. ACM SIGMOD Record, 44(2), 29-34.
Jukic, N., Vrbsky, S., & Nestorov, S. (2016). Database systems: Introduction to databases and
data warehouses. Prospect Press.
Mistry, R., & Misner, S. (2014). Introducing Microsoft SQL Server 2014. Microsoft Press.
Ping, Y., Hong-Wei, H., & Nan, Z. (2014, June). Design and implementation of a MySQL
database backup and recovery system. In Intelligent Control and Automation (WCICA),
2014 11th World Congress on (pp. 5410-5415). IEEE.
Zhang, J., & Li, H. (2017, August). Research and Implementation of a Data Backup and
Recovery System for Important Business Areas. In Intelligent Human-Machine Systems
and Cybernetics (IHMSC), 2017 9th International Conference on (Vol. 2, pp. 432-437).
IEEE.