Explore topic-wise InterviewSolutions in Current Affairs.

There are many locks AVAILABLE for the database system to have likeIntent SHARED, Shared, INTENT exclusive, exclusive and Shared Intent exclusive.

Locking granularity refers to the size and hence the number of locks USED to ensure the consistency of a database during multiple concurrent updates.

There are many locks available for the database system to have likeIntent Shared, Shared, Intent exclusive, exclusive and Shared Intent exclusive.

Locking granularity refers to the size and hence the number of locks used to ensure the consistency of a database during multiple concurrent updates.

transaction ensures that one or more OPERATIONS EXECUTE as an ATOMIC unit of WORK. If one of the operations within a transaction fails, then all of them are rolled-back so that the application is returned to its prior state. The boundaries that define a group of operations DONE within a single transaction.

transaction ensures that one or more operations execute as an atomic unit of work. If one of the operations within a transaction fails, then all of them are rolled-back so that the application is returned to its prior state. The boundaries that define a group of operations done within a single transaction.

ACID MEANS Atomic, Consistency, Isolation, Durability, so when any transaction happen it should be Atomic that is it should either be complete or fully incomplete. There should not be anything like Semi complete. The DATABASE State should remain CONSISTENT after the completion of the transaction. If there are more than one Transaction then the transaction should be scheduled in such a fashion that they remain in Isolation of one another.Durability means that Once a transaction commits, its effects will persist even if there are SYSTEM failures.

ACID means Atomic, Consistency, Isolation, Durability, so when any transaction happen it should be Atomic that is it should either be complete or fully incomplete. There should not be anything like Semi complete. The Database State should remain consistent after the completion of the transaction. If there are more than one Transaction then the transaction should be scheduled in such a fashion that they remain in Isolation of one another.Durability means that Once a transaction commits, its effects will persist even if there are system failures.

One of the reason to define rights is the security in the database system. If any user is ALLOWED to define the data or alter the data then the database would just be of no USE and so PROCESSING rights and responsibilities are clearly defined in any database system. The RESPOSIBILITIES are enforced using the table space provided by the database system.

One of the reason to define rights is the security in the database system. If any user is allowed to define the data or alter the data then the database would just be of no use and so processing rights and responsibilities are clearly defined in any database system. The resposibilities are enforced using the table space provided by the database system.

If we REPROCESS the transaction then the database can be made to come to a STATE where the database is consistent and so reprocessing the log can recover the database. Reprocessing is not very FEASIBLE for a very simple reason that its very costly from TIME point of view and requires lots of REWORK and many transaction are even rollback giving more and more rework.

If we reprocess the transaction then the database can be made to come to a state where the database is consistent and so reprocessing the log can recover the database. Reprocessing is not very feasible for a very simple reason that its very costly from time point of view and requires lots of rework and many transaction are even rollback giving more and more rework.

ROLLBACK :- Undoing the CHANGES made by a transaction before it commits or to cancel any changes to a DATABASE made during the current transaction.

RollForward :- Re-doing the changes made by a transaction after it commits or to overwrite the chnaged calue again to ENSURE CONSISTENCY.

Rollback :- Undoing the changes made by a transaction before it commits or to cancel any changes to a database made during the current transaction.

RollForward :- Re-doing the changes made by a transaction after it commits or to overwrite the chnaged calue again to ensure consistency.

The most IMPORTANT objective to write the log before the database is changed is if there is any need to ROLLBACK or rollforward any TRANSACTION then if the log are not present then the rollback rollforward cannot be done ACCURATELY.

The most important objective to write the log before the database is changed is if there is any need to rollback or rollforward any transaction then if the log are not present then the rollback rollforward cannot be done accurately.

Database ADMINISTRATOR :- A person (or group of people) responsible for the maintenance and performance of a database and responsible for the PLANNING, implementation, configuration, and administration of relational database management systems.

Data Administrator :- The individual or organization responsible for the specification, acquisition, and maintenance of data management software and the design, VALIDATION, and security of files or databases. The DA is in CHARGE of the data dictionary and data model.

Database Administrator :- A person (or group of people) responsible for the maintenance and performance of a database and responsible for the planning, implementation, configuration, and administration of relational database management systems.

Data Administrator :- The individual or organization responsible for the specification, acquisition, and maintenance of data management software and the design, validation, and security of files or databases. The DA is in charge of the data dictionary and data model.

A foreign key is a FIELD in a table that refers to parent records in another table. The references are represented by the primary key values of the corresponding parent records. A foreign key field is the same data TYPE as the primary key field of the referenced table. Usually, a foreign key field is named the same as the primary key field of the parent table. This very BENEFICIAL convention is called key migration in data modeling terminology. Child table foreign key references to parent table primary keys embody database relationships.

A foreign key is a field in a table that refers to parent records in another table. The references are represented by the primary key values of the corresponding parent records. A foreign key field is the same data type as the primary key field of the referenced table. Usually, a foreign key field is named the same as the primary key field of the parent table. This very beneficial convention is called key migration in data modeling terminology. Child table foreign key references to parent table primary keys embody database relationships.

CANDIDATE key is a COLUMN in a table which has the ability to become a PRIMARY key.

candidate key is a column in a table which has the ability to become a primary key.

ADDITION, DELETION and MODIFICATION.

Addition, deletion and modification.

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.

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

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.

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.

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.

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.

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.

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.

The well-known inference RULES for FDs
• Reflexive rule :
If Y is subset or equal to X then X Y.
• Augmentation rule:
If X Y then XZ YZ.
• TRANSITIVE rule:
If {X Y, Y Z} then X Z.
• Decomposition rule :
If X YZ then X Y.
• Union or ADDITIVE rule:
If {X Y, X Z} then X YZ.
• Pseudo Transitive rule :
If {X Y, WY Z} then WX Z.
Of these the FIRST three are known as Amstrong Rules. They are sound because it is enough if a set of FDs satisfy these three. They are CALLED complete because using these three rules we can generate the rest all inference rules.

The well-known inference rules for FDs
• Reflexive rule :
If Y is subset or equal to X then X Y.
• Augmentation rule:
If X Y then XZ YZ.
• Transitive rule:
If {X Y, Y Z} then X Z.
• Decomposition rule :
If X YZ then X Y.
• Union or Additive rule:
If {X Y, X Z} then X YZ.
• Pseudo Transitive rule :
If {X Y, WY Z} then WX Z.
Of these the first three are known as Amstrong Rules. They are sound because it is enough if a set of FDs satisfy these three. They are called complete because using these three rules we can generate the rest all inference rules.

MINIMAL key is one which can identify each tuple of the given relation schema uniquely. For finding the minimal key it is required to find the closure that is the set of all attributes that are dependent on any given set of attributes under the given set of functional dependency.
Algo. I Determining X+, closure for X, given set of FDs F
1. Set X+ = X
2. Set Old X+ = X+
3. For each FD Y Z in F and if Y BELONGS to X+ then add Z to X+
4. Repeat steps 2 and 3 until Old X+ = X+
Algo.II Determining minimal K for relation schema R, given set of FDs F
1. Set K to R that is making K a set of all attributes in R
2. For each ATTRIBUTE A in K
a. Compute (K – A)+ with respect to F
b. If (K – A)+ = R then set K = (K – A)+

Minimal key is one which can identify each tuple of the given relation schema uniquely. For finding the minimal key it is required to find the closure that is the set of all attributes that are dependent on any given set of attributes under the given set of functional dependency.
Algo. I Determining X+, closure for X, given set of FDs F
1. Set X+ = X
2. Set Old X+ = X+
3. For each FD Y Z in F and if Y belongs to X+ then add Z to X+
4. Repeat steps 2 and 3 until Old X+ = X+
Algo.II Determining minimal K for relation schema R, given set of FDs F
1. Set K to R that is making K a set of all attributes in R
2. For each attribute A in K
a. Compute (K – A)+ with respect to F
b. If (K – A)+ = R then set K = (K – A)+

Given a relation R and a set of FDS F, dependency preservation STATES that the CLOSURE of the UNION of the projection of F on each decomposed relation Ri is equal to the closure of F. i.e.,
((PR1(F)) U … U (PRn(F)))+ = F+
if decomposition is not dependency preserving, then some dependency is lost in the decomposition.

Given a relation R and a set of FDs F, dependency preservation states that the closure of the union of the projection of F on each decomposed relation Ri is equal to the closure of F. i.e.,
((PR1(F)) U … U (PRn(F)))+ = F+
if decomposition is not dependency preserving, then some dependency is lost in the decomposition.

It is an ASSOCIATION AMONG TWO or more ENTITIES.

It is an association among two or more entities.

It is the NUMBER of ATTRIBUTE of its RELATION SCHEMA.

It is the number of attribute of its relation schema.

A relation Schema denoted by R(A1, A2, …, An) is MADE up of the relation name R and the list of ATTRIBUTES Ai that it contains. A relation is DEFINED as a set of tuples. LET r be the relation which contains set tuples (T1, t2, t3, ..., tn). Each tuple is an ordered list of n-values t=(v1,v2, ..., vn).

A relation Schema denoted by R(A1, A2, …, An) is made up of the relation name R and the list of attributes Ai that it contains. A relation is defined as a set of tuples. Let r be the relation which contains set tuples (t1, t2, t3, ..., tn). Each tuple is an ordered list of n-values t=(v1,v2, ..., vn).

It is a PARTICULAR PROPERTY, which DESCRIBES the ENTITY.

It is a particular property, which describes the entity.

An entity set MAY not have sufficient attributes to form a primary KEY, and its primary key compromises of its partial key and primary key of its parent entity, then it is said to be WEAK Entity set.

An entity set may not have sufficient attributes to form a primary key, and its primary key compromises of its partial key and primary key of its parent entity, then it is said to be Weak Entity set.

The collections of ENTITIES of a PARTICULAR ENTITY TYPE are grouped TOGETHER into an entity set.

The collections of entities of a particular entity type are grouped together into an entity set.

It is a collection of all ENTITIES of particular ENTITY type in the database.

It is a collection of all entities of particular entity type in the database.

It is a COLLECTION (SET) of ENTITIES that have same ATTRIBUTES.

It is a collection (set) of entities that have same attributes.

This model is based on collection of objects. An OBJECT contains VALUES STORED in instance variables with in the object. An object also contains bodies of code that operate on the object. These bodies of code are called METHODS. Objects that contain same TYPES of values and the same methods are grouped together into classes.

This model is based on collection of objects. An object contains values stored in instance variables with in the object. An object also contains bodies of code that operate on the object. These bodies of code are called methods. Objects that contain same types of values and the same methods are grouped together into classes.

This data model is based on real WORLD that consists of basic objects CALLED entities and of RELATIONSHIP among these objects. Entities are described in a database by a set of attributes.

This data model is based on real world that consists of basic objects called entities and of relationship among these objects. Entities are described in a database by a set of attributes.

A COLLECTION of conceptual TOOLS for DESCRIBING data, data relationships data SEMANTICS and constraints.

A collection of conceptual tools for describing data, data relationships data semantics and constraints.

A view may be thought of as a VIRTUAL table, that is, a table that does not really exist in its own right but is instead derived from ONE or more underlying base table. In other words, there is no stored file that direct represents the view instead a DEFINITION of view is stored in data dictionary. GROWTH and RESTRUCTURING of base tables is not reflected in views. Thus the view can insulate users from the effects of restructuring and growth in the database. Hence accounts for logical data independence.

A view may be thought of as a virtual table, that is, a table that does not really exist in its own right but is instead derived from one or more underlying base table. In other words, there is no stored file that direct represents the view instead a definition of view is stored in data dictionary. Growth and restructuring of base tables is not reflected in views. Thus the view can insulate users from the effects of restructuring and growth in the database. Hence accounts for logical data independence.

Data independence means that “the application is independent of the STORAGE structure and access strategy of data”. In other words, The ability to modify the SCHEMA definition in one level should not affect the schema definition in the next higher level.
Two types of Data Independence:
• PHYSICAL Data Independence: MODIFICATION in physical level should not affect the logical level.
• Logical Data Independence: Modification in logical level should affect the view level.

Data independence means that “the application is independent of the storage structure and access strategy of data”. In other words, The ability to modify the schema definition in one level should not affect the schema definition in the next higher level.
Two types of Data Independence:
• Physical Data Independence: Modification in physical level should not affect the logical level.
• Logical Data Independence: Modification in logical level should affect the view level.

Unlike Relational systems in System R
• DOMAINS are not supported
• Enforcement of candidate key uniqueness is OPTIONAL
• Enforcement of ENTITY integrity is optional
• Referential integrity is not ENFORCED

Unlike Relational systems in System R
• Domains are not supported
• Enforcement of candidate key uniqueness is optional
• Enforcement of entity integrity is optional
• Referential integrity is not enforced

System R was designed and developed over a period of 1974-79 at IBM SAN Jose Research Center. It is a prototype and its PURPOSE was to demonstrate that it is possible to build a Relational System that can be USED in a real life environment to solve real life PROBLEMS, with performance at least comparable to that of existing system. Its two subsystems are
• Research Storage
• System Relational Data System.

System R was designed and developed over a period of 1974-79 at IBM San Jose Research Center. It is a prototype and its purpose was to demonstrate that it is possible to build a Relational System that can be used in a real life environment to solve real life problems, with performance at least comparable to that of existing system. Its two subsystems are
• Research Storage
• System Relational Data System.

Extension -
It is the NUMBER of TUPLES present in a table at any instance. This is time dependent.
Intension -
It is a constant VALUE that gives the NAME, structure of table and the constraints laid on it.

Extension -
It is the number of tuples present in a table at any instance. This is time dependent.
Intension -
It is a constant value that gives the name, structure of table and the constraints laid on it.

• Data REDUNDANCY & INCONSISTENCY.
• DIFFICULT in accessing data.
• Data ISOLATION.
• Data integrity.
• Concurrent access is not possible.
• SECURITY Problems.

• Data redundancy & inconsistency.
• Difficult in accessing data.
• Data isolation.
• Data integrity.
• Concurrent access is not possible.
• Security Problems.

• REDUNDANCY is CONTROLLED.
• Unauthorised access is restricted.
• Providing MULTIPLE USER interfaces.
• Enforcing integrity constraints.
• Providing backup and recovery.

• Redundancy is controlled.
• Unauthorised access is restricted.
• Providing multiple user interfaces.
• Enforcing integrity constraints.
• Providing backup and recovery.

The database and DBMS software together is CALLED as Database SYSTEM.

The database and DBMS software together is called as Database system.

It is a collection of programs that ENABLES user to create and maintain a DATABASE. In other words it is general-purpose SOFTWARE that provides the users with the PROCESSES of defining, CONSTRUCTING and manipulating the database for various applications.

It is a collection of programs that enables user to create and maintain a database. In other words it is general-purpose software that provides the users with the processes of defining, constructing and manipulating the database for various applications.

A homogenous database is one that uses the same DBMS at each NODE. Either each node can WORK independently or a central DBMS may coordinate database activities. A heterogeneous database is one that may have a different DBMS at each node. It may support some or all of the functionality of one logical database. It may support FULL Distributed DBMS functionality or PARTIAL Distributed DBMS functionality.

A homogenous database is one that uses the same DBMS at each node. Either each node can work independently or a central DBMS may coordinate database activities. A heterogeneous database is one that may have a different DBMS at each node. It may support some or all of the functionality of one logical database. It may support full Distributed DBMS functionality or partial Distributed DBMS functionality.

A distributed database is a single LOGICAL database that is SPREAD ACROSS more than one node or locations that are all connected via some communication link. It requires multiple DBMSs, RUNNING at each remote site. A distributed database can be either HOMOGENOUS (same DBMS at each node) or heterogeneous (different DBMS at some nodes).

A distributed database is a single logical database that is spread across more than one node or locations that are all connected via some communication link. It requires multiple DBMSs, running at each remote site. A distributed database can be either homogenous (same DBMS at each node) or heterogeneous (different DBMS at some nodes).

Horizontal partitioning is where some rows of a table are placed into the base relations at ONE site and other rows are placed at ANOTHER site. Vertical partitioning is where some columns of a table are placed into the base relations at one site and other columns are placed at another site but each all of these relations MUST share a common DOMAIN.

Horizontal partitioning is where some rows of a table are placed into the base relations at one site and other rows are placed at another site. Vertical partitioning is where some columns of a table are placed into the base relations at one site and other columns are placed at another site but each all of these relations must share a common domain.

Concurrency transparency is where each transaction in a distributed database is treated as if it is the only one in the SYSTEM. Therefore If several transactions are running at one time, the RESULTS will be the same as If each transaction was RUN in serial order. The transaction manager helps to provide concurrency CONTROL. The three methods that may be used are locking, versioning, and time stamping.

Concurrency transparency is where each transaction in a distributed database is treated as if it is the only one in the system. Therefore If several transactions are running at one time, the results will be the same as If each transaction was run in serial order. The transaction manager helps to provide concurrency control. The three methods that may be used are locking, versioning, and time stamping.

Snapshot REPLICATION can be used when an APPLICATION does not require that the data always be CURRENT. These applications can be updated by periodic table copying or snapshots. As part of the snapshot effort, all of the data to be INCLUDED in the snapshot is collected at a primary point. Then a read-only snapshot is taken and the snapshot is sent to each site so that the update can be made.

Snapshot replication can be used when an application does not require that the data always be current. These applications can be updated by periodic table copying or snapshots. As part of the snapshot effort, all of the data to be included in the snapshot is collected at a primary point. Then a read-only snapshot is taken and the snapshot is sent to each site so that the update can be made.

The installation of the database is an IMPORTANT issue since the better the installation, the better the performance could be. Memory usage is learning how the DBMS USES MAIN memory and then using that KNOWLEDGE to enable better performance. I/O is usually very intense in a DB, so understanding how users will use the data will help to prepare the database better. CPU usage and application tuning are also important considerations.

The installation of the database is an important issue since the better the installation, the better the performance could be. Memory usage is learning how the DBMS uses main memory and then using that knowledge to enable better performance. I/O is usually very intense in a DB, so understanding how users will use the data will help to prepare the database better. CPU usage and application tuning are also important considerations.

Locking is denying OTHERS the ability to update a record until someone completes the update or releases the record. Locking can occur on many different LEVELS in a database. It can occur at the database, table, record, or field LEVEL. A lock can be shared (another can read the record while an update is in PROGRESS) or exclusive (no one can read the record while an update is in progress).

Locking is denying others the ability to update a record until someone completes the update or releases the record. Locking can occur on many different levels in a database. It can occur at the database, table, record, or field level. A lock can be shared (another can read the record while an update is in progress) or exclusive (no one can read the record while an update is in progress).

Concurrency control is the process managing simultaneous operations against a database so that database integrity is not COMPROMISED. There are two approaches to concurrency control. The pessimistic APPROACH involves LOCKING and the optimistic approach involves VERSIONING.

Concurrency control is the process managing simultaneous operations against a database so that database integrity is not compromised. There are two approaches to concurrency control. The pessimistic approach involves locking and the optimistic approach involves versioning.

One of the FEATURES includes the use of views which allows the presentation of only data needed by someone and limits the CAPABILITY of database UPDATES. The use of integrity controls includes such things as domains, assertions, and checks. Also authorization rules, user-defined PROCEDURES, ENCRYPTION, authentication schemes, and backups are important.

One of the features includes the use of views which allows the presentation of only data needed by someone and limits the capability of database updates. The use of integrity controls includes such things as domains, assertions, and checks. Also authorization rules, user-defined procedures, encryption, authentication schemes, and backups are important.

Data administration is responsible for the overall management of data RESOURCES. Some of the core roles include the creation of data polices, procedures and standards, resolve data ownership issues, and manage the INFORMATION REPOSITORY. Database administration is physical database OVERSIGHT. Some of the core duties include the selection of the DBMS and software tools, the installation and upgrade of the DBMS, and database performance tuning.

Data administration is responsible for the overall management of data resources. Some of the core roles include the creation of data polices, procedures and standards, resolve data ownership issues, and manage the information repository. Database administration is physical database oversight. Some of the core duties include the selection of the DBMS and software tools, the installation and upgrade of the DBMS, and database performance tuning.