Q1) Attempt any EIGHT of the following (Out of
TEN) : [8 × 2 = 16]
What is difference between DBMS and RDBMS?
What is timestamp?
A timestamp is the current time of an event that a
computer records. A timestamp is a unique identifier that is being created by the
DBMS when a transaction enters into the system.
Define Transaction.
A transaction can be defined as a group of tasks.
A single task is the minimum processing unit which cannot be divided further.
Let’s take an example of a simple transaction.
Suppose a bank employee transfers Rs 500 from A's account to B's account. This
very simple and small transaction involves several low-level tasks.
A’s Account
Open_Account(A)
Old_Balance = A.balance
New_Balance = Old_Balance - 500
A.balance = New_Balance
Close_Account(A)
B’s Account
Open_Account(B)
Old_Balance = B.balance
New_Balance = Old_Balance + 500
B.balance = New_Balance
Close_Account(B)
What is cursor?
When
a SQL statement is executed in Oracle, the temporary context area is created.
This area contains all the relevant information relating to the statement and
its execution. The cursor is a pointer to this context area and allows the
PL/SQL program to control this area.
Define serializability.
Serializability is a way to check if the execution of two or more
transactions are maintaining the database consistency or not.
What is deadlock?
A deadlock is a situation in which two
or more transactions are waiting for one
another to give up locks.
What is atomicity?
Atomicity ensures that a transaction is treated as a single, indivisible unit of work. Either all the operations within the transaction are completed successfully, or none of them are. If any part of the transaction fails, the entire transaction is rolled back to its original state, ensuring data consistency and integrity.
What is procedure?
The PL/SQL stored procedure or
simply a procedure is a PL/SQL block which performs one or more specific tasks.
It is just like procedures in other programming languages.
The procedure contains a header and a body.
o
Header: The header contains the name
of the procedure and the parameters or variables passed to the procedure.
o
Body: The body contains a declaration
section, execution section and exception section similar to a general PL/SQL
block.
What is trigger?
A Trigger in Structured Query Language is a set of procedural statements which
are executed automatically when there is any response to certain events on the
particular table in the database. Triggers are used to protect the data
integrity in the database.
An operator is a symbol that tells the compiler to perform specific mathematical or logical manipulation.
A lock is defined as a data variable associated with a single data item.
o It is also known as a Read-only lock. In a shared lock, the data item can only read by the transaction.
o It can be shared between the transactions because when the transaction holds a lock, then it can't update the data on the data item.
2. Exclusive lock:
o In the exclusive lock, the data item can be both reads as well as written by the transaction.
o
This lock is exclusive, and in this lock, multiple transactions do not modify the same data simultaneously.
A transaction can be in one of the
following state −
· Active − Transaction is executing.
· Failed − Transaction fails to complete successfully.
· Abort − changes made by transaction are cancelled (roll back).
· Partially commit − Final statement of transaction is executed.
· Commit − Transaction completes its execution successfully.
· Terminated − Transaction is finished.
Explain advantages of concurrent
execution.
Advantages of
Concurrency:
·
Running of multiple applications-It enable to run multiple applications at the same time.
·
Better resource utilization –It enables that the resources that are unused by one application can be used for other applications.
·
Better average response time –Without concurrency, each application has to be run to completion before the next one can be run.
·
Better performance –It enables the better performance by the operating system. When one application uses only the
processor and another application uses only the disk drive then the time to run
both applications concurrently to completion will be shorter than the time to
run each application consecutively.
List the failure types.
Failure
Classification
To find that where the problem has occurred, we generalize a
failure into the following categories:
1. Transaction failure
2. System crash
3. 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.
Logical errors: If a transaction cannot complete due to some code error or an internal error condition, then the logical error occurs.
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.
Write the syntax for trigger.
Trigger
is invoked by Oracle engine automatically whenever a specified event
occurs.Trigger is stored into database and invoked repeatedly, when specific
condition match.
Benefits of Triggers
Triggers can be written for the following purposes −
1. Generating some derived column values automatically
2. Enforcing referential integrity
3. Event logging and storing information on table access
4. Auditing
5. Synchronous replication of tables
6. Imposing security authorizations
7. Preventing invalid transactions
Define : (i) Upgrading (ii) Downgrading
1.
Upgrading means installing a newer version of firmware than the one that is running; Downgrading means installing an older firmware version.
2. It provides a mechanism for conversion from shared lock to exclusive lock is known as upgrade.
3. It provides a mechanism for conversion from exclusive lock to shared lock is known as downgrade.
Undo allows you to take back data
entry changes by storing a collection of data maintenance actions. Redo lets
you restore the change that was undone.
Undo is a record of a transaction before it is
committed in Oracle RDBMS. In contrast, an entry in redo log files that holds a
group of change vectors in Oracle RDBMS.
What is PL/SQL ? Draw a block diagram
PL/SQL
program units organize the code into blocks. A block without a name is known as
an anonymous block. The anonymous block is the simplest unit in PL/SQL. It is
called anonymous block because it is not saved in the Oracle database.
[DECLARE]
Declaration statements;
BEGIN
Execution statements;
[EXCEPTION]
Exception
handling statements;
END;
/
PL/SQL Block Structure
The anonymous block has three basic sections that are the declaration, execution, and exception handling. Only the execution section is mandatory and the others are optional.
The declaration
section allows you to define data types, structures, and variables.
You often declare variables in the declaration section by giving them names,
data types, and initial values.
The execution section is required in a block structure and it must have at least one statement. The execution section is the place where you put the execution code or business logic code. You can use both procedural and SQL statements inside the execution section.
The exception handling section is starting with the EXCEPTION keyword.
The exception section is the place that you put the code to handle exceptions.
You can either catch or handle exceptions in the exception section.
.
