Test Gestión y Administración de base de datos (Inglés)

The information independence implies: If the physical information model is modified then the logical information model must be accordingly modified. The logical information model modification does not necessarily imply modifying the physical information model. Any modification in a BBDD will necessarily imply the modification of the applications that her gain access. The information independence is established only between the physical and logical layer of the BBDD.

With regard to the variables in PL/SQL: They can be primitive type and of anchor type. Those of anchor type can be of the same type as a column of a table. Those of anchor type can store several lines of a table. Those of anchor type can store a only line of a cursor.

With regard to the stored procedures: It is a procedure that is executed in the DBMS. It is a procedure that is stored in the DBMS but it is executed in the client that invokes it. It is a procedure that is executed in the DBMS but it is stored in the client that invokes it. It allows to share code among different sessions.

Let the SQL sentece: INSERT X SET X.a=X.a+1 and a declaration of the trigger “CREATE TRIGGER TRG_UPD_X_A AFTER INSERT FOR EACH ROW. It will be executed once in any case. It will be executed once per updated line of the table X. It will not have access to the variables :new nor :old. It will have reading access to the table X.

Let the SQL sentence INSERT X SET X.a=X.a+1. A declared trigger “ONE BELIEVE TRIGGER TRG_UPD_X_A AFTER INSERT”. It will be executed once in any case. It will be executed once per updated line of the table X. It will not have access to the variables :new nor :old. It will have reading access to the table X.

A mutant table: It cannot be read by a row trigger. It cannot be modified by a row trigger. It can be read by a sentence trigger. It is a radioactive table.

The data dictionary: It is predefined by the DBMS, which keeps updated such a data dictionary. A user is able to make read-only operations. It describes, among other things, the logical model of information of the DDBB. It describes, among other things, the physical model of information of the DDBB.

In Oracle it is possible to have: A only database and several instances. Several databases and a only instance. A database replicated in several machines. The database and the instance in a only machine.

In Oracle, the groups of files redolog: Among other things, they allow Oracle to continue a strategy of management of the data buffer of type “steal / no force”. There is only a group of redolog files for database. There is only a group of active redolog files for database. There is only a group of on-line redolog files for database.

In Oracle, it is said that the groups of redolog files are multiplexed if and only if: There are several groups of on-line redolog files. There is more than one member per group. The ARCHIVELOG mode is activated. There are no offline files redo log.

In Oracle, about the tablespaces: They are no part of any instance. They are organized in segments, extensions and storage blocks. They are managed by the database writter (dbwr) process. They are stored in one or more files.

In Oracle, a table can be stored physically. In a only segment. In several extensions. In several tablespaces. In a only file.

In Oracle, about the pctfree parameter: It must have a high value for tables which are expected to be rarely updated. It is related to the management of the storage blocks. It is relevant for operations of type DELETE. It is relevant for operations of type UPDATE.

In Oracle, about the pctused parameter: It must have a high value for tables which are expected to be rarely updated. It is related to the management of the storage blocks. It is relevant for operations of type DELETE. It is relevant for operations of type UPDATE.

In Oracle, for every new session client, it is possible to setup the DBMS so that: A new server process is created. A new instance is created. A server process is shared with other clients. The instance is shared with other clients.

In the terminology of Oracle, some of the elements regarding with the database are: Data files. Instance. Redo log files. Control files.

In Oracle, the following elements are a part of an instance: The system global memory (SGA). SQL*Plus. The database writer process (DBWR). The archiver process (ARCH).

In Oracle, the following elements are a part of the global memory of the system. The process global memory. The buffer of the log. The data buffer. The tablespace.

On the physical design of the database: It is a direct consequence of the logical design of data. Its main aim is to improve the integrity of the data. Its main aim is to improve the performance of the database. Any modification of the physical design of the database bears the modification of the logical data model.

If it is created an index associated with a column of a certain table. It will improve the access time to this table attending to the index-linked column. It will improve the insertions of new lines in this table. It implies that the column will not be able to store void values. It implies that the column necessary is a primary or foreign key.

An index-organized table…. stores the whole row in the primary key index. is that whose key is index-linked. optimizes the access to tables that are defined with prime attributes only. is that whose attributes are prime.

Regarding clusters defined in the physical design of the database: The rows of a set of tables that share one or more columns are stored physically together. It optimizes natural join queries attending to the attributes used to define the cluster. It is a type of physical organization of tables where the whole row is stored in the primary key index structure. A table is possible to belong to several clusters simultaneously.

Regarding partitions defined in the physical design of the database, it is possible to affirm that: The rows of a set of tables that share one or more columns are stored physically together. All partitions of the same table are stored in the same tablespace. It distributes a table among several segments. Every segment stores only some of the columns of the table. Its main use is the optimization of updates of data.

The Oracle privileges management : It stores the moment when a privilege was granted to a user. It is possible that several users grant the same privilege to the same user. If a user A revokes a privilege P to user B, then necessary user B loses the privilege P and, in turn, all those users granted by B with the privilege P. A user can be granted with any number of privileges.

In Oracle, the privileges type object: are necessary to create a table. can be revoked exclusively by the same user who transmitted it. it is not possible to eliminate them in cascade. can be assigned to a role.

In Oracle, the privileges of type system: are necessary to create a table. can be revoked exclusively by the same user who transmitted it. it is not possible to eliminate them in cascade. can be assigned to a role.

A Oracle user is able to receive: several roles and profiles. several roles and a only profile. an only one role and an only profile. an only one role and several profiles.

In Oracle, a serializable transaction: It can generate an exception if it tries to modify a data that has been modified in another transaction T2, after beginning T1. It can read not updated data. It is the minimal level of necessary isolation to guarantee that there will not be dirty reads. It is the minimal level of necessary isolation to guarantee that there will not be phantom reads.

Given two transactions T1 and T2 and a schedule P, it is possible to guarantee that: If P is serializable, it is guaranteed that there are not conflict operations. If P is serial, it is guaranteed that that there are not conflict operations. If P2 is an alternative schedule to P, P2 is serial, it is known that both obtain the same database state at the end of the execution plan. If P is serial then it is strict.

A planner that implements the protocol of strict 2PL. It guarantees that any schedule will be serializable. It guarantees that any schedule will be recoverable. It guarantees that there will not be deadlocks. It guarantees that no transaction will stay in indefinite waiting.

A planner that guarantees concurrence, the serializability, prevents deletes in cascade, is not free from of deadlocks and does not know the data that every transaction is going to modify to the beginning of this one, might implement a strategy: Serial. Conservative 2PL. Strict 2PL. Rigorous 2PL.

With regard to Oracle SERIALIZABLE isolation level, it is posible to affirm: It prevents lost updates. It prevents cascade deletes. It prevents deadlocks. It is a 2PL strategy.

With regard to Oracle READ COMMITED isolation level, it is possible to affirm: It prevents lost updates. It prevents erasures in cascade. It prevents deadlocks. It prevents dirty readings.

The algorithm of fail recovery of a DBMS must be provided necessary with the UNDONE operation if. The algorithm of management of the buffer of memory is of type “no steal page”. The algorithm of management of the buffer of memory is of type “no force writing. Concurrent transactions are allowed. Long transactions are allowed.

The algorithm of fail recovery of a DBMS must be provided necessary with the REDONE operation if. The algorithm of management of the buffer of memory is of type “no steal page”. The algorithm of management of the buffer of memory is of type “no force writing”. Concurrent transactions are allowed. Long transactions are allowed.

A DBMS that implements immediate update of dirty pages, the first rule of Write-Ahead Loging, WAL#1: It is necessary to guarantee the atomicity. It is necessary if the algorithm of management of the buffer of memory is of type “steal page”. It allows the implementation of the operation UNDONE. He does not prevent committed transactions from being able to get lost.

With Regard to ARIES, it is posible to affirm that: The dirty pages are recorded in the data file In the checkpoints. It is an algorithm of fail recovery adapted for a strategy of the data buffer of type “force”. It is an algorithm of fail recovery adapted for a strategy of the data buffer of type “steal”. During the undo step, it is required to back up to reaching the first event that did that some page were noted down as dirty.

Regarding the redo phase in ARIES: It redoes only and exclusively those events that still have not been stored in the data file. It redoes both those events of committed and not committed transactions. It uses the lastLSN value stored in every trace to redo more efficiently. "undo" is the previous step.

Regarding the undo in ARIES. It redoes only and exclusively those events that still have not been stored in the data file. It redoes both those events of committed and not committed transactions. It uses the lastLSN value stored in every trace to redo more efficiently. “analysis" is the previous step.

Regarding ARIES, It is possible to affirm that the database is exactly in the state that was in the moment of the fail: At the end of the analysis phase. At the end of the "redo“ phase. At the end of “undo” phase. Never.