databases Online Quiz - 201

Database triggers execute within the context of the triggering DML statement's transaction. Using transactional control statements like COMMIT or ROLLBACK inside a trigger would attempt to end the parent transaction, causing runtime errors.

A cursor IS a named private SQL area that holds temporary results and IS used for retrieving multiple rows. Option B claims it's 'not correct' to say a cursor holds temporary results, which is false - that's precisely what a cursor does.

In PL/SQL, Data Manipulation Language (DML) statements like SELECT...INTO, UPDATE, and DELETE are natively supported. However, Data Definition Language (DDL) statements like CREATE cannot be executed directly and must be run using dynamic SQL (EXECUTE IMMEDIATE).

To answer this question, users need to understand the concept of transaction management in the database.

A transaction is a sequence of operations performed on a database that must be treated as a single unit of work. It has four main properties - Atomicity, Consistency, Isolation, and Durability. In case, a transaction is not committed, it is rolled back, which means all changes made by the transaction are undone.

Now, let's go through each option:

A. Only when it is Committed: This option is incorrect because a transaction can end in two ways: by committing or by rolling back. If the transaction is committed, the changes made by it are made permanent in the database.

B. Only when it is Rolledback: This option is incorrect because a transaction can end either by committing or by rolling back. If a transaction is rolled back, the changes made by it are undone, and the database is restored to its original state before the transaction began.

C. When it is Committed or Rolledback: This option is correct because a transaction can either be committed or rolled back. Once a transaction is committed or rolled back, it gets over, and the database moves to the next transaction.

D. None of the above: This option is incorrect because, as explained above, a transaction ends either by committing or by rolling back.

Hence, the correct answer is:

The Answer is: C. When it is Committed or Rolledback.

System crashes cause automatic rollback of uncommitted transactions to maintain database ACID properties. The UNDO logs are used to reverse any uncommitted changes. Normal SQL commands like GRANT, ALTER, or EXIT do not trigger rollbacks.

If multiple rows are inserted using SYSDATE in rapid succession (within the same second), they would have identical timestamp values, violating the UNIQUE constraint. The UNIQUE constraint prevents any duplicate values, including those from SYSDATE.

Full-table scans on large tables read every row, which is expensive. Identifying these scans is critical because they can often be optimized by creating or using appropriate indexes. Indexes allow the database to read only relevant rows instead of scanning the entire table.

IDMS (Integrated Database Management System) is a Network Model database. The Network model allows many-to-many relationships between records through set relationships, unlike the Hierarchical model (tree structure) or Relational model (tabular structure with foreign keys).

IDMS stands for Integrated Database Management System (or Integrated Data Management System). Option A contains the correct expansion despite a minor spelling error where 'Intergrated' should be 'Integrated'. The other options (B, C, D) represent incorrect expansions.

In IDMS, the READY command is used to make an AREA ready for access. AREAs are the second-level storage units in the IDMS hierarchy (VOLUME > AREA > PAGE > RECORD). READY must be issued on an AREA before it can be accessed for database operations.

In IDMS, the BIND command is used for RECORD-level operations. BIND establishes a connection to a specific record type, which is the most granular level in the IDMS storage hierarchy (VOLUME > AREA > PAGE > RECORD). This allows programs to access and manipulate specific record types within the database.

In the typical IDMS operation sequence, BIND (for RECORD) comes before READY (for AREA). You must first BIND to establish a connection to the record type, then READY the AREA containing those records. Therefore, READY follows BIND in the operational sequence, making the statement TRUE.

In DB2, you can change a user's password during connection using the syntax: CONNECT TO database USER username USING old_password NEW new_password CONFIRM new_password. This verifies and updates the password in the operating system or external security manager.

BINDADD is the privilege required to create new packages in a DB2 database. CONTROL is a high-level privilege, BIND allows binding packages, and EXECUTE allows running them. BINDADD specifically grants the ability to create packages, which is different from BIND (re-binding existing packages).

SYSCAT.TABAUTH contains grantee information for table and view privileges including REFERENCE. SYSCAT.VIEWS has view metadata, SYSCAT.TABLES has table metadata, and SYSCAT.REFERENCES doesn't exist as a separate catalog view. TABAUTH is the authorization catalog tracking who has what privileges.

Indexes can enforce uniqueness via unique constraints, ensuring no duplicate values. Tables are unordered sets (ordered only via ORDER BY), indexes can be ascending or descending, and views are virtual tables storing no data themselves (queries run on demand).

When a DB2 database is created, the system automatically creates default tablespaces: SYSCATSPACE (for the system catalog), TEMPSPACE1 (for system temporary tables), and USERSPACE1 (the default space for user tables). SYSLOGSPACE is not a default tablespace.

EXTENTSIZE defines the number of pages written to a container before moving to the next. STRIPSIZE is for striping across containers, PREFETCHSIZE is for read-ahead, and SKIPSIZE is not a standard database storage parameter. The extent is the fundamental allocation unit.

To find maximum salary per job category within each department, you must GROUP BY both dept_id and job_cat. MAX(salary) is then calculated for each unique combination. Option A has invalid WHERE syntax, C lacks grouping, D only groups by department.

COUNT() and MIN() are valid aggregate functions on DATE columns - COUNT returns non-null count, MIN returns earliest date. SUM() requires numeric data. AVG() on two dates is invalid syntax. Only two options are correct.