- What are database files, control
files and log files. How many of these files should a database have at least?
Why?
Database Files
The database
files hold the actual data and are typically the largest in size. Depending on
their sizes, the tables (and other objects) for all the user accounts can go in
one database file—but that's not an ideal situation because it does not make
the database structure very flexible for controlling access to storage for
different users, putting the database on different disk drives, or backing up
and restoring just part of the database.
You must have at least one
database file but usually, more than one files are used. In terms of accessing
and using the data in the tables and other objects, the number (or location) of
the files is immaterial.
The database
files are fixed in size and never grow bigger than the size at which they were
created
Control Files
The
control files and redo logs support the rest of the architecture. Any database
must have at least one control file, although you typically have more than one
to guard against loss. The control file records the name of the database, the
date and time it was created, the location of the database and redo logs, and
the synchronization information to ensure that all three sets of files are
always in step. Every time you add a new database or redo log file to the database,
the information is recorded in the control files.
Redo
Logs
Any database
must have at least two redo logs. These are the journals for the database; the
redo logs record all changes to the user objects or system objects. If any type
of failure occurs, the changes recorded in the redo logs can be used to bring
the database to a consistent state without losing any committed transactions.
In the case of non-data loss failure, Oracle can apply the information in the
redo logs automatically without intervention from the DBA.
The redo log
files are fixed in size and never grow dynamically from the size at which they
were created.
The ROWID is a
unique database-wide physical address for every row on every table. Once
assigned (when the row is first inserted into the database), it never changes
until the row is deleted or the table is dropped.
The ROWID
consists of the following three components, the combination of which uniquely
identifies the physical storage location of the row.
Ø
Oracle database file number, which contains the
block with the rows
Ø
Oracle block address, which contains the row
Ø
The row within the block (because each block can
hold many rows)
The ROWID is
used internally in indexes as a quick means of retrieving rows with a particular
key value. Application developers also use it in SQL statements as a quick way
to access a row once they know the ROWID
- What is Oracle Block? Can two
Oracle Blocks have the same address?
Oracle
"formats" the database files into a number of Oracle blocks when they
are first created—making it easier for the RDBMS software to manage the files
and easier to read data into the memory areas.
The block size should be a
multiple of the operating system block size. Regardless of the block size, the
entire block is not available for holding data; Oracle takes up some space to
manage the contents of the block. This block header has a minimum size, but it
can grow.
These Oracle
blocks are the smallest unit of storage. Increasing the Oracle block size can
improve performance, but it should be done only when the database is first
created.
Each Oracle
block is numbered sequentially for each database file starting at 1. Two blocks
can have the same block address if they are in different database files.
- What is database Trigger?
A database trigger
is a PL/SQL block that can defined to automatically execute for insert, update,
and delete statements against a table. The trigger can e defined to execute
once for the entire statement or once for every row that is inserted, updated,
or deleted. For any one table, there are twelve events for which you can define
database triggers. A database trigger can call database procedures that are
also written in PL/SQL.
- Name two utilities that Oracle
provides, which are use for backup and recovery.
Along with the
RDBMS software, Oracle provides two utilities that you can use to back up and
restore the database. These utilities are Export and Import.
The Export utility dumps the definitions and data for the specified part of the
database to an operating system binary file. The Import utility reads the
file produced by an export, recreates the definitions of objects, and inserts
the data
If Export and
Import are used as a means of backing up and recovering the database, all the
changes made to the database cannot be recovered since the export was
performed. The best you can do is recover the database to the time when the
export was last performed.
- What are stored-procedures? And
what are the advantages of using them.
Stored
procedures are database objects that perform a user defined operation. A stored
procedure can have a set of compound SQL statements. A stored procedure
executes the SQL commands and returns the result to the client. Stored
procedures are used to reduce network traffic.
- How are exceptions handled in
PL/SQL? Give some of the internal exceptions' name
PL/SQL exception
handling is a mechanism for dealing with run-time errors encountered during
procedure execution. Use of this mechanism enables execution to continue if the
error is not severe enough to cause procedure termination.
The exception
handler must be defined within a subprogram specification. Errors cause the
program to raise an exception with a transfer of control to the
exception-handler block. After the exception handler executes, control returns
to the block in which the handler was defined. If there are no more executable
statements in the block, control returns to the caller.
User-Defined
Exceptions
PL/SQL enables
the user to define exception handlers in the declarations area of subprogram
specifications. User accomplishes this by naming an exception as in the
following example:
ot_failure EXCEPTION;
In this case, the exception name
is ot_failure. Code associated with
this handler is written in the EXCEPTION specification area as follows:
EXCEPTION
when OT_FAILURE then
out_status_code := g_out_status_code;
out_msg := g_out_msg;
The following is an example of a subprogram
exception:
EXCEPTION
when NO_DATA_FOUND then
g_out_status_code := 'FAIL';
RAISE ot_failure;
Within this exception is the RAISE statement that
transfers control back to the ot_failure exception handler. This technique of
raising the exception is used to invoke all user-defined exceptions.
System-Defined
Exceptions
Exceptions
internal to PL/SQL are raised automatically upon error. NO_DATA_FOUND is a
system-defined exception. Table below gives a complete list of internal
exceptions.
PL/SQL internal exceptions.
Exception
Name
|
Oracle
Error
|
CURSOR_ALREADY_OPEN
|
ORA-06511
|
DUP_VAL_ON_INDEX
|
ORA-00001
|
INVALID_CURSOR
|
ORA-01001
|
INVALID_NUMBER
|
ORA-01722
|
LOGIN_DENIED
|
ORA-01017
|
NO_DATA_FOUND
|
ORA-01403
|
NOT_LOGGED_ON
|
ORA-01012
|
PROGRAM_ERROR
|
ORA-06501
|
STORAGE_ERROR
|
ORA-06500
|
TIMEOUT_ON_RESOURCE
|
ORA-00051
|
TOO_MANY_ROWS
|
ORA-01422
|
TRANSACTION_BACKED_OUT
|
ORA-00061
|
VALUE_ERROR
|
ORA-06502
|
ZERO_DIVIDE
|
ORA-01476
|
In addition to
this list of exceptions, there is a catch-all exception named OTHERS that traps all errors for which
specific error handling has not been established.
- Does PL/SQL support
"overloading"? Explain
The concept of overloading in PL/SQL relates to the
idea that you can define procedures and functions with the same name. PL/SQL
does not look only at the referenced name, however, to resolve a procedure or
function call. The count and data types of formal parameters are also
considered.
PL/SQL
also attempts to resolve any procedure or function calls in locally defined
packages before looking at globally defined packages or internal functions. To
further ensure calling the proper procedure, you can use the dot notation.
Prefacing a procedure or function name with the package name fully qualifies
any procedure or function reference.
- Tables derived from the ERD
a) Are totally unnormalised
b) Are always in 1NF
c) Can be further denormalised
d) May have multi-valued attributes
(b) Are always in 1NF
- Spurious tuples may occur due to
i. Bad
normalization
ii.
Theta joins
iii.
Updating tables from join
a) i & ii b) ii & iii
c) i & iii d) ii &
iii
(a) i &
iii because theta joins are joins made on keys that are
not primary keys.
- A B C is a set of attributes. The
functional dependency is as follows
AB ->
B
AC
-> C
C -> B
a) is in 1NF
b) is in 2NF
c) is in 3NF
d) is in BCNF
(a) is in 1NF since (AC)+ = { A, B,
C} hence AC is the primary key. Since
C -> B is a FD given, where
neither C is a Key nor B is a prime attribute, this it is not in 3NF. Further B
is not functionally dependent on key AC thus it is not in 2NF. Thus the given FDs is in 1NF.
a) entities in ERD should correspond
to an existing entity/store in DFD
b) entity in DFD is converted to
attributes of an entity in ERD
c) relations in ERD has 1 to 1
correspondence to processes in DFD
d) relationships in ERD has 1 to 1
correspondence to flows in DFD
(a) entities
in ERD should correspond to an existing entity/store in DFD
- A dominant entity is the entity
a) on the N side in a 1 : N
relationship
b) on the 1 side in a 1 : N
relationship
c) on either side in a 1 : 1
relationship
d) nothing to do with 1 : 1 or 1 : N
relationship
(b)
on the 1 side in a 1 : N relationship
- Select 'NORTH', CUSTOMER From CUST_DTLS Where REGION
= 'N' Order By
CUSTOMER Union Select 'EAST', CUSTOMER From CUST_DTLS Where REGION
= 'E' Order By CUSTOMER
The
above is
a) Not an error
b) Error - the string in single
quotes 'NORTH' and 'SOUTH'
c) Error - the string should be in
double quotes
d) Error - ORDER BY clause
(d) Error - the ORDER BY clause. Since ORDER BY
clause cannot be used in UNIONS
It is a program
module that provides the interface between the low-level data stored in
database, application programs and queries submitted to the system.
It is a program
module, which is responsible for fetching data from disk storage into main
memory and deciding what data to be cache in memory.
- What is Transaction Manager?
It is a program
module, which ensures that database, remains in a consistent state despite
system failures and concurrent transaction execution proceeds without
conflicting.
It is a program
module, which manages the allocation of space on disk storage and data
structure used to represent information stored on a disk.
- What is Authorization and Integrity manager?
It is the
program module, which tests for the satisfaction of integrity constraint and
checks the authority of user to access data.
- What are stand-alone procedures?
Procedures that
are not part of a package are known as stand-alone because they independently
defined. A good example of a stand-alone procedure is one written in a
SQL*Forms application. These types of procedures are not available for
reference from other Oracle tools. Another limitation of stand-alone procedures
is that they are compiled at run time, which slows execution.
- What are cursors give different
types of cursors.
PL/SQL uses cursors for all
database information accesses statements. The language supports the use two
types of cursors
Ø Implicit
Ø Explicit
- What is cold backup and hot
backup (in case of Oracle)?
Ø
Cold
Backup:
It is copying the three sets of files
(database files, redo logs, and control file) when the instance is shut down.
This is a straight file copy, usually from the disk directly to tape. You must
shut down the instance to guarantee a consistent copy.
If a cold
backup is performed, the only option available in the event of data file loss
is restoring all the files from the latest backup. All work performed on the
database since the last backup is lost.
Ø
Hot
Backup:
Some sites
(such as worldwide airline reservations systems) cannot shut down the database
while making a backup copy of the files. The cold backup is not an available
option.
So different
means of backing up database must be used — the hot backup. Issue a SQL command
to indicate to Oracle, on a tablespace-by-tablespace basis, that the files of
the tablespace are to backed up. The users can continue to make full use of the
files, including making changes to the data. Once the user has indicated that
he/she wants to back up the tablespace files, he/she can use the operating
system to copy those files to the desired backup destination.
The database
must be running in ARCHIVELOG mode for the hot backup option.
If a data loss
failure does occur, the lost database files can be restored using the hot
backup and the online and offline redo logs created since the backup was done.
The database is restored to the most consistent state without any loss of
committed transactions.
