Explore topic-wise InterviewSolutions in .

Proactive Update:
The UPDATES that are applied to DATABASE before it BECOMES effective in real world .

Retroactive Update:
The updates that are applied to database after it becomes effective in real world.

SIMULTANEOUS Update:
The updates that are applied to database at the same time when it becomes effective in real world .

Proactive Update:
The updates that are applied to database before it becomes effective in real world .

Retroactive Update:
The updates that are applied to database after it becomes effective in real world.

Simultaneous Update:
The updates that are applied to database at the same time when it becomes effective in real world .

PL/SQL uses CURSORS for all DATABASE information ACCESSES STATEMENTS. The LANGUAGE supports the use two types of cursors.

•  Implicit
•  Explicit

PL/SQL uses cursors for all database information accesses statements. The language supports the use two types of cursors.

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.

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.

It is the program module, which TESTS for the satisfaction of integrity constraint and checks the authority of user to ACCESS data.

It is the program module, which tests for the satisfaction of integrity constraint and checks the authority of user to access data.

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.

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.

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 ensures that database, remains in a consistent state despite system failures and concurrent transaction execution proceeds without conflicting.

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.

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.

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 that provides the interface between the low-level data stored in database, application programs and queries submitted to the system.

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.

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.

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.

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.

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.

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.

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 be 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.

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 be 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.

The four Oracle system PROCESSES that must ALWAYS be up and running for the database to be useable include DBWR (Database Writer), LGWR (LOG Writer), SMON (System Monitor), and PMON (Process Monitor).

The four Oracle system processes that must always be up and running for the database to be useable include DBWR (Database Writer), LGWR (Log Writer), SMON (System Monitor), and PMON (Process Monitor).

You COMMUNICATE with an RDBMS USING STRUCTURED Query Language (SQL).

You communicate with an RDBMS using Structured Query Language (SQL).

DETERMINES an OPTIMAL ACCESS path to store or retrieve the DATA.

determines an optimal access path to store or retrieve the data.

The information in the data dictionary validates the existence of the OBJECTS, provides ACCESS to them, and maps the ACTUAL physical storage location.

The information in the data dictionary validates the existence of the objects, provides access to them, and maps the actual physical storage location.

DATA dictionary is a set of tables and DATABASE objects that is stored in a SPECIAL AREA of the database and maintained EXCLUSIVELY by the kernel.

Data dictionary is a set of tables and database objects that is stored in a special area of the database and maintained exclusively by the kernel.

Two important pieces of RDBMS architecture are the kernel, which is the software, and the data DICTIONARY, which consists of the system-level data structures used by the kernel to manage the DATABASE
You might think of an RDBMS as an operating system (or set of subsystems), designed specifically for controlling data access; its primary functions are storing, RETRIEVING, and securing data. An RDBMS maintains its own list of authorized users and their associated privileges; manages memory caches and paging; controls locking for concurrent resource usage; dispatches and schedules user requests; and manages SPACE usage within its table-space structures.

Two important pieces of RDBMS architecture are the kernel, which is the software, and the data dictionary, which consists of the system-level data structures used by the kernel to manage the database
You might think of an RDBMS as an operating system (or set of subsystems), designed specifically for controlling data access; its primary functions are storing, retrieving, and securing data. An RDBMS maintains its own list of authorized users and their associated privileges; manages memory caches and paging; controls locking for concurrent resource usage; dispatches and schedules user requests; and manages space usage within its table-space structures.

No.
PRODUCT: CONCATENATION of every ROW in ONE relation with every row in another.
JOIN: Concatenation of rows from one relation and related rows from another.

No.
PRODUCT: Concatenation of every row in one relation with every row in another.
JOIN: Concatenation of rows from one relation and related rows from another.

PROJECTION and SELECTION.

PROJECTION and SELECTION.

‘EDIT’ BUFFER.

‘Edit’ Buffer.

ADDITION, DELETION and MODIFICATION.

Addition, deletion and modification.

Subqueries, or nested queries, are used to bring back a set of rows to be used by the parent query. Depending on how the subquery is written, it can be executed once for the parent query or it can be executed once for each row returned by the parent query. If the subquery is executed for each row of the parent, this is CALLED a correlated subquery.
A correlated subquery can be EASILY identified if it contains any REFERENCES to the parent subquery columns in its WHERE clause. Columns from the subquery cannot be referenced anywhere else in the parent query. The following example demonstrates a non-correlated subquery.
E.g. Select * From CUST Where '10/03/1990' IN (Select ODATE From ORDER Where CUST.CNUM = ORDER.CNUM)

Subqueries, or nested queries, are used to bring back a set of rows to be used by the parent query. Depending on how the subquery is written, it can be executed once for the parent query or it can be executed once for each row returned by the parent query. If the subquery is executed for each row of the parent, this is called a correlated subquery.
A correlated subquery can be easily identified if it contains any references to the parent subquery columns in its WHERE clause. Columns from the subquery cannot be referenced anywhere else in the parent query. The following example demonstrates a non-correlated subquery.
E.g. Select * From CUST Where '10/03/1990' IN (Select ODATE From ORDER Where CUST.CNUM = ORDER.CNUM)

Network SCHEMA uses a graph data structure to ORGANIZE RECORDS example for such a DATABASE management SYSTEM is CTCG while a hierarchical schema uses a tree data structure example for such a system is IMS.

Network schema uses a graph data structure to organize records example for such a database management system is CTCG while a hierarchical schema uses a tree data structure example for such a system is IMS.

It is ONE, which keeps its PHYSICAL STRUCTURE HIDDEN from user.

It is one, which keeps its Physical Structure hidden from user.

It is a DATABASE in which there are no programs or user ACCESS LANGUAGES. It has no cross-file capabilities but is user-friendly and provides user-interface MANAGEMENT.

It is a database in which there are no programs or user access languages. It has no cross-file capabilities but is user-friendly and provides user-interface management.

DIFFERENT phases are

•  ANALYSIS PHASE
•  Redo Phase
•  Undo phase

Different phases are

A Checkpoint is like a snapshot of the DBMS state. By TAKING CHECKPOINTS, the DBMS can REDUCE the amount of WORK to be done during restart in the event of subsequent CRASHES.

A Checkpoint is like a snapshot of the DBMS state. By taking checkpoints, the DBMS can reduce the amount of work to be done during restart in the event of subsequent crashes.

In DISTRIBUTED deadlock DETECTION, the delay in propagating local information might cause the deadlock detection algorithms to identify deadlocks that do not REALLY exist. Such situations are CALLED phantom deadlocks and they lead to unnecessary aborts.

In distributed deadlock detection, the delay in propagating local information might cause the deadlock detection algorithms to identify deadlocks that do not really exist. Such situations are called phantom deadlocks and they lead to unnecessary aborts.

Atomicity:
EITHER all actions are CARRIED out or none are. Users should not have to worry about the effect of incomplete transactions. DBMS ensures this by undoing the actions of incomplete transactions.
Aggregation:
A concept which is used to model a relationship between a collection of entities and RELATIONSHIPS. It is used when we need to express a relationship AMONG relationships.

Atomicity:
Either all actions are carried out or none are. Users should not have to worry about the effect of incomplete transactions. DBMS ensures this by undoing the actions of incomplete transactions.
Aggregation:
A concept which is used to model a relationship between a collection of entities and relationships. It is used when we need to express a relationship among relationships.

Once the DBMS informs the user that a transaction has successfully completed, its effects should persist EVEN if the system crashes before all its changes are reflected on DISK. This PROPERTY is called DURABILITY.

Once the DBMS informs the user that a transaction has successfully completed, its effects should persist even if the system crashes before all its changes are reflected on disk. This property is called durability.

Join Dependency:

A Join dependency is generalization of Multivalued dependency.A JD {R1, R2, ..., Rn} is said to HOLD over a relation R if R1, R2, R3, ..., Rn is a lossless-join decomposition of R . There is no SET of SOUND and complete inference rules for JD.

Inclusion Dependency:

An Inclusion Dependency is a statement of the form that some columns of a relation are contained in other columns. A FOREIGN key constraint is an example of inclusion dependency.

Join Dependency:

A Join dependency is generalization of Multivalued dependency.A JD {R1, R2, ..., Rn} is said to hold over a relation R if R1, R2, R3, ..., Rn is a lossless-join decomposition of R . There is no set of sound and complete inference rules for JD.

Inclusion Dependency:

An Inclusion Dependency is a statement of the form that some columns of a relation are contained in other columns. A foreign key constraint is an example of inclusion dependency.

The PHASE that identifies an efficient execution plan for evaluating a QUERY that has the least estimated COST is referred to as query OPTIMIZATION.

The phase that identifies an efficient execution plan for evaluating a query that has the least estimated cost is referred to as query optimization.

A RDBMS maintains a description of all the data that it contains, information about every relation and index that it contains. This information is stored in a collection of RELATIONS maintained by the SYSTEM CALLED METADATA. It is also called data dictionary.

A RDBMS maintains a description of all the data that it contains, information about every relation and index that it contains. This information is stored in a collection of relations maintained by the system called metadata. It is also called data dictionary.

Indexing is a TECHNIQUE for determining how quickly specific DATA can be found.
Types:

•  Binary SEARCH style indexing
•  B-Tree indexing
•  Inverted LIST indexing
•  MEMORY resident table
•  Table indexing

Indexing is a technique for determining how quickly specific data can be found.
Types:

A relation is said to be in DKNF if all CONSTRAINTS and dependencies that should hold on the constraint can be ENFORCED by SIMPLY enforcing the DOMAIN constraint and key constraint on the relation.

A relation is said to be in DKNF if all constraints and dependencies that should hold on the constraint can be enforced by simply enforcing the domain constraint and key constraint on the relation.

A RELATION schema R is said to be 5NF if for every join dependency {R1, R2, ..., Rn} that holds R, one the following is true

•  Ri = R for some i.
•  The join dependency is implied by the set of FD, over R in which the left SIDE is key of R.

A Relation schema R is said to be 5NF if for every join dependency {R1, R2, ..., Rn} that holds R, one the following is true

A relation SCHEMA R is said to be in 4NF if for every Multivalued dependency X Y that HOLDS over R, one of following is true

•  X is subset or equal to (or) XY = R.
•  X is a super key.

A relation schema R is said to be in 4NF if for every Multivalued dependency X Y that holds over R, one of following is true

A relation schema R is in BCNF if it is in 3NF and satisfies an additional constraint that for every FD X A, X must be a candidate KEY.

A relation schema R is in BCNF if it is in 3NF and satisfies an additional constraint that for every FD X A, X must be a candidate key.

A RELATION SCHEMA R is in 3NF if it is in 2NF and for EVERY FD X A EITHER of the following is true

•  X is a Super-key of R.
•  A is a prime attribute of R.

In other words, if every non prime attribute is non-transitively dependent on primary key.

A relation schema R is in 3NF if it is in 2NF and for every FD X A either of the following is true

In other words, if every non prime attribute is non-transitively dependent on primary key.

A relation schema R is in 2NF if it is in 1NF and every non-prime attribute A in R is fully functionally DEPENDENT on PRIMARY KEY.

A relation schema R is in 2NF if it is in 1NF and every non-prime attribute A in R is fully functionally dependent on primary key.

The domain of ATTRIBUTE must INCLUDE only atomic (SIMPLE, INDIVISIBLE) values.

The domain of attribute must include only atomic (simple, indivisible) values.

It GUARANTEES that the spurious TUPLE GENERATION does not occur with respect to relation SCHEMAS after decomposition.

It guarantees that the spurious tuple generation does not occur with respect to relation schemas after decomposition.

It is an applied predicate CALCULUS specifically tailored for RELATIONAL databases proposed by E.F. Codd.
E.g. of languages based on it are DSL ALPHA, QUEL.

It is an applied predicate calculus specifically tailored for relational databases proposed by E.F. Codd.
E.g. of languages based on it are DSL ALPHA, QUEL.

It is procedural QUERY language. It CONSISTS of a set of OPERATIONS that TAKE ONE or two relations as input and produce a new relation.

It is procedural query language. It consists of a set of operations that take one or two relations as input and produce a new relation.

The High level or Non-procedural DML can specify and RETRIEVE MANY RECORDS in a SINGLE DML statement. This retrieve of a record is said to be Set-at-a-time or Set-oriented.

The High level or Non-procedural DML can specify and retrieve many records in a single DML statement. This retrieve of a record is said to be Set-at-a-time or Set-oriented.

The LOW level or Procedural DML can specify and RETRIEVE each record from a SET of records. This retrieve of a record is SAID to be Record-at-a-time.

The Low level or Procedural DML can specify and retrieve each record from a set of records. This retrieve of a record is said to be Record-at-a-time.

It interprets DDL statements and RECORD them in TABLES containing METADATA.

It interprets DDL statements and record them in tables containing metadata.

It EXECUTES low-level INSTRUCTION GENERATED by COMPILER.

It executes low-level instruction generated by compiler.

It TRANSLATES DML statements in a query language into low-level INSTRUCTION that the query EVALUATION engine can UNDERSTAND.

It translates DML statements in a query language into low-level instruction that the query evaluation engine can understand.