Database Management System 2
| Redo Log Buffer | It holds information about changes made to the database | |
| System and Syshaux | Which of the following are mandatory tablespaces that are created at the time of database creation | |
| Checkpoint Process (CKPT) | It is a type of process structure that records checkpoint information in Control file on each data file header | |
| Database Server | It is the key in solving the problems of information management. | |
| Large Pool | Also known as Oracle Database backup and restore operations | |
| Oracle Database | It is a collection of data treated as a unit with the main purpose of storing and retrieving related information in the database. | |
| Log Writer Process (LGWR) | It is a type of process structure that writes the redo log buffer to a redo log file on disk | |
| Recoverer Process | It is a type of process structure that automatically connects to other databases involved in in- doubt distributed transactions | |
| Session | It is also known as specific connection of a user to an instance through a user process | |
| Connection | It is a communication between a user process and an instance | |
| Database Writer Process (DBWn) | It is a type of process structure that writes modified (dirty) buffers in the database buffer cache asynchronously while performing other processing to advance the checkpoint | |
| Java Pool | It is part of the System Global Area which is used to provide memory for Oracle Streams processes | |
| Data Buffer Cache | It is part of the System Global Area (SGA) that hold copies of data blocks that are read from data files | |
| Recoverer Process | It is a type of process structure that can collect transaction, redo data and transmit that data to standby destinations | |
| Process Monitor Process (PMON) | It is a type of process structure that cleans up the database buffer cache | |
| Dba | Which of the following is not included in the optional requirement for operating system of oracle database? | |
| 3GB for the ASM instance | Which of the following is not included in the memory requirements of oracle database. | |
| Evaluating the database server hardware Installing the Oracle software Planning the database and security strategy Creating, migrating, and opening the database Backing up the database Enrolling system users and planning for their Oracle Network access. Implementing the database design Recovering from database failure Monitoring database performance | Which of the following follows the correct order of designing, implementing and mainting and Oracle Database. | |
| Connection | It is a communication pathway between a user process and an Oracle Database instance. | |
| ALTER USER ANNA IDENTIFIED BY AN01; | Which of the following is the correct example of changing a password to user ANNA from ANNA01 to AN01? | |
| Managing user environment | Which of the following system privileges is not part of the task by the database admin.? | |
| sysdba | Which of the following is not included in the required operating system users and groups? | |
| REVOKE INSERT, UPDATE, DELETE ON EMPLOYEES TO ANNA; | Which of the following is the correct example revoking a priviledge to INSERT, UPDATE and DELETE to USER ANNA? | |
| System | It is a type of privilege that gain access to the database | |
| Checkpoint Process (CKPT) | It is a type process structure that records checkpoint information in Control file on each data file header. | |
| Oracle_instance | Which of the following is not included in the oracle environment? | |
| GRANT SELECT, INSERT ON EMPLOYEES TO STUDENT; | Which of the following is the correct example of granting SELECT and INSERT on table EMPLOYEES to roleSTUDENT? | |
| session | It represents the state of a current user login to the database instance. | |
| Server process | This is use to connect to the Oracle instance and is started when a user establishes a session | |
| ORACLE_HOME | It is a type a Oracle environment where Oracle Products run. | |
| Oracle database | It is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. | |
| CREATE ROLE STUDENT; | Which of the following is the correct example of creating a role STUDENT? | |
| REVOKE INSERT, UPDATE, DELETE ON SYSTEM.EMPLOYEES TO ANNA; | Which of the following is the correct example revoking a privilege to INSERT, UPDATE and DELETE to user ANNA from user BEN? | |
| CREATE USER ANNA IDENTIFIED BY ANN01; | Which of the following is the correct example of creating a user ANNA with password ANN01? | |
| Checkpoint Process (CKPT) | It is a type process structure that automatically connects to other databases involved in in- doubt distributed transactions. | |
| GRANT INSERT, UPDATE, DELETE ON EMPLOYEES TO ANNA WITH GRANT OPTION; | Which of the following is the correct example of granting INSERT, UPDATE and DELETE to user ANNA with an option to pass the privilege to other user? | |
| GRANT STUDENT TO ANNA; | Which of the following is the correct example of granting role STUDENT to USER ANNA? | |
| Object | It is a type of privilege that is used to manipulating the content of the database objects. | |
| Redo Log Buffer | Holds information about changes made to the database | |
| Data Buffer Cache | It is part of the System Global Area (SGA) that hold copies of data blocks that are read from data files. | |
| 3.8 GB | Which of the following is the correct Disk space requirement when installing Oracle? | |
| GRANT CREATE SESSION, CREATE TABLE TO ANNA; | Which of the following is the correct example of granting create session and create table to user ANNA? | |
| 3 GB for the preconfigured database (required) | Which of the following is not included in the disk requirements of oracle database? | |
| Any Oracle version will do even when multiple products are involved. | In planning to install Oracle which of the following is not important to consider. | |
| Process Monitor Process (PMON) | It is a type process structure that cleans up the database buffer cache | |
| SELECT * FROM SYSTEM.STUDENT; | Which of the following is the correct example of selecting all columns from employees table using the user BEN? | |
| Database server | It is the key to solving the problems of information management. | |
| session | Also known as the application or tool that connects to the Oracle Database Processes | |
| Schema | This is also known collection of objects such as tables, views, and sequences. | |
| REVOKE | This is used to remove a privilege granted to a user. | |
| CREATE INDEX | Which of the following System Privileges is not part of SYSTEM privilege? | |
| Role | It is a named group of related privileges that can be granted to the user. This method makes it easier to revoke and maintain privileges. | |
| CREATE USER hr IDENTIFIED BY hr; | Which of the following is the correct syntax for creating a new user? | |
| GRANT INSERT, UPDATE ON EMPLOYEES TO HR WITH GRANT OPTION; | Which of the following is the correct example of passing a privilege (INSERT and UPDATE) to user HR coming from a SYSTEM? | |
| GRANT INSERT, UPDATE ON SYSTEM.EMPLOYEES TO HR; | Which of the following is the correct example of passing a privilege (INSERT and UPDATE) to user HR coming from a user scott? | |
| The correct answers are: CREATE TABLE is issued, The user closes the isqlplus, The user type in Commit, The user encounter system failure, The user type in Rollback | A transaction ends when either of the following occurs. (Choose 5) | |
| COMMIT | Which one (1) DCL (Data Control Langauge) is considered as part of Database Transaction | |
| CREATE TABLE | Which one (1) Data Definition Language is considered as part of Database Transaction. | |
| The correct answers are: The user encounter system failure, Closes the sqlplus normally by closing he isqlplus using x button | An automatic rollback is issued by the oracle under the following circumstances. (Choose 2) | |
| The correct answers are: INSERT, UPDATE, DELETE | Which three (3) of thefollowing is the three Data Manipulation Langauge that is considered as part of database transaction. | |
| DML | A transaction start when the first ____________ is issued. | |
| The correct answers are: CREATE TABLE is issued, Closes the sqlplus normally by typing exit | An automatic commit is issued by the Oracle Server under the following circumstances. (Choose 2) | |
| Archiver Processes (ARCn) | It is a type of process structure that can collect transaction, redo data and transmit that data to standby destinations. | |
| File Manager | Which of the following is not part or Oracle Installation System Requirement. | |
| Redundancy | Which of the following transaction rules is not included in the group? | |
| Exclusive Lock (X Lock) | A type of lock that is acquired when an application updates, inserts, or deletes a row. | |
| Deadlock | It occurs when two or more applications wait indefinitely for a resources. | |
| Logical Data Independence | This is a kind of mechanism, which liberalizes itself from actual data stored on the disk. | |
| Entity -- Entity | Which of the following is the correct representation of many-to-many relation? | |
| Sharelock (S locks) | A type of lock that is acquired when an application wants to read and prevent others from updating the same row. | |
| Which of the following is the correct example of derived attribute? | ||
| COMMIT | A type of transaction that is ends the current transactions by making all pending data changes permanent. | |
| Commit; | Which of the following is the correct example of saving changes made on the table? | |
| The manager_id that is equal to null has been updated to IT. | Supposed that a user performs the query as shown below, what will happen to the data? | |
| A foreign key in one table points to a foreign key in another table. | Which of the following statement is not true about Foreign Key constraint | |
| CREATE TABLE BOOKS( BOOK_ID NUMBER, TITLE VARCHAR(10), YEAR NUMBER(4), CONSTRAINT BK_ID CHECK (BOOK_ID > 10)); | Which of the following is the correct example of adding a check constraint on column BOOK_ID to accept value greater that 10? | |
| ALTER TABLE AUTHORS ADD CONSTRAINT AUTHOR_ID PRIMARY KEY(NUMBER); | Which of the following is the correct example of adding primary key constraint? | |
| Changes are made permanently. | Supposed that a user performs the query as shown below, what will happen to the data? | |
| Entity Relationship Diagram | This is the fundamental entity which introduces abstraction in a Database Management System (DBMS). | |
| ALTER TABLE AUTHORS DROP PRIMARY KEY CASCADE; | Which of the following is the correct example of dropping on delete cascade? | |
| Database Instance | It is a state of operational database with data at any given time. | |
| Lock | This is acquired automatically as needed to support a transaction based on isolation levels. | |
| CREATE TABLE BOOKS( BOOK_ID NUMBER PRIMARY KEY NOT NULL, TITLE VARCHAR(10), YEAR NUMBER(4), CONSTRAINT BK_ID FOREIGN KEY(BOOK_ID) REFERENCES AUTHORS (BOOK_ID)); | Which of the following is the correct example of referencing the BOOKS table to AUTHORS? | |
| Which of the following is the correct representation of one-to-one relation? | ||
| Atomicity | A state of the data where a transaction must be fully complete, saved (committed) or completely undone (rolled back). | |
| Lost Update | Which of the following is not possible causes of lock. | |
| Transaction | This gives the user more flexibility and control when changing data and they ensure data consistency in the event of user process or system failure. | |
| Lockwait | A process where an application waits indefinitely to obtain any needed locks. | |
| KOCHAR, NENA and LEX, DE HAAN | Based on the table EMPLOYEES, DEPARTMENTS and LOCATIONS. | |
| SELECT FIRSTNAME, SALARY + 1000 AS BONUS, DEPARTMENT_NAME, D.DEPARTMENT_ID FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID; | Which of the following is the correct query that will create a report that will display the following: FIRTSNAME, SALARY with additional 1000 in employees salary, rename this column as BONUS, then get the DEPARTMENT_NAME and DEPARTMENT_ID. Join the table using ON condition. | |
| Natural Join and Using clause | Which of the following joins are mutually exclusive. | |
| All of the choices | Which of the following is not true about complex view when using INSERT statement? | |
| 0 rows are deleted in Workers table | Supposed that table: Workers and Employees is consists of the following values. | |
| INSERT INTO WORKERS (SELECT * FROM EMPLOYEES WHERE JOB_ID = ‘ST_CLERK’); | Which of the following is the correct example of creating a subquery that will copy all values from employees table to workers where job_id is equal to ST_CLERK; | |
| 2014 JABBERWACKY | Based on the table EMPLOYEES, DEPARTMENTS and LOCATIONS. | |
| All of the choices | Which of the following is not true about complex view when using DELETE statement? | |
| The record of book OS will be removed in the COPY_AUTHORS view since its YR_PUBLIHED is updated to 2016 | Refer to the table AUTHORS and COPY_AUTHORS (Note during the creation of view the condition added is YR_PUBLISHED=2010). Supposed that the user update the YR_PUBLISHED of book OS to from 2010 to 2010 as shown below what is/are the possible output on both table and view? | |
| The correct answers are: CREATE TABLE COURSE (CODE CHAR(4) PRIMARY KEY, TITLE VARCHAR(20), CONSTRAINT CODE_ck CHECK (CODE LIKE ‘BS%’)); b, ‘BS’)); | Which of the following is the correct example of adding a CHECK constraint where CODE should start with the character ‘BS’. | |
| Foreign key | A type of constraint that ensures the relationship between tables remain preserved as data is inserted, deleted and modified. | |
| DROP CASCADE | This is used to delete a primary key constraint in a parent table and will automatically drop the foreign key constraint in the child table. | |
| The given statement is not a valid join condition or is incorrect | SELECT EMPLOYEES.EMPLOYEE_ID, EMPLOYEES.LASTNAME,EMPLOYEES.DEPARTMENT_ID,DEPARTMENTS.DEPARTMENT_ID,DEPARTMENTS.LOCATION_ID FROM EMPLOYEES JOIN DEPARTMENTS USING (DEPARTMENT_ID); | |
| Foreign key and Primary Key | What are the two forms of key integrity constraint? | |
| Check | A type of constraint that is used to limit the value range that can be placed in a column. | |
| Using | This is a join clause used when the columns in two or more tables have the same but of different data type. | |
| SELECT EMPLOYEE_ID, SALARY FROM EMPLOYEES WHERE SALARY =(SELECT MIN(SALARY) FROM EMPLOYEES; | Which of the following is the correct example of multiple row subquery? | |
| CREATE OR REPLACE VIEW WORKERS (EMPLOYEE_ID, SURNAME, FIRSTNAME, MANAGER_ID) AS (EMPLOYEE_ID, LASTNAME, FIRSTNAME, MANAGER_ID) FROM EMPLOYEES WHERE MANAGER_ID IS NULL); | Which of the following is the correct example of modifying a view where salary manager_id is null? | |
| All of the choices | Which of the following is not true about complex view when using UPDATE statement? | |
| Unique | A type constraint that uniquely identifies each record in a database table and can be applied on one or more column. | |
| MAX | Which of the following is not part of multiple row subqueries? | |
| Cause an error because the JOIN condition is omitted. | In the given complex view example what will be the possible output if this code is run? | |
| Table Prefixes | This is used to quality ambiguous column when joining two or more tables. | |
| ON DELETE CASCADE | This is used to delete child row when a parent key is deleted. | |
| CREATE TABLE COURSE (CODE CHAR(4) PRIMARY KEY, TITLE VARCHAR(20), CONSTRAINT CODE_df DEFAULT(CODE 'BSIT')); | Which of the following is the correct example of create a default constraint. | |
| ENABLE CONSTRAINT | This is used to enable a currently disable constraint in the table definition. | |
| ALTER TABLE STUDENTS DISABLE CONSTRAINT usn_pk; | Which of the following is the correct example of disabling an integrity constraint? | |
| SELECT STREET_ADDRESS, CITY, DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS JOIN LOCATIONS USING (LOCATION_ID); | Which of the following is the correct query that will create a report that will display the following: STREET_ADDRESS, CITY, DEPARTMENT_NAME and LOATION_ID? Use using clause to get the data from two tables. | |
| Unique constraint is violated on AUTHORS table | Refer to the table AUTHORS and COPY_AUTHORS (Note ID column is with Primary Key constraint). Supposed that the user insert the following values to COPY_AUTHORS view as shown below what is/are the possible output on both table and view? | |
| Primary key | A type of constraint uniquely identifies each record in a database table. | |
| Consistency | A state of the data where a transaction must be fully compliant with the state of the database as it was prior to the transaction. | |
| Transaction | This is also known as unit of work. | |
| IN | Which of the following is not part of single to subqueries? | |
| durability | A state where a transaction data changes must be available, even in the event of database failure. | |
| Domain Intergrity | It is used to ensures that data values inside a database follow defines rules for values, range and format | |
| DISABLE CONSTRAINT | This is used to deactivate an integrity constraint. | |
| Default | A type of constraint that is used to insert a default value into a column. | |
| The correct answer is: | Which of the following is the correct representation of one-to-one relation? | |
| The manager_id that is equal to null is now change to IT and the job_id of employees under department_id 50,90 or salary greater that 500 have been updated to secretary. | Supposed that a user performs the query as shown below, what will happen to the data? | |
| ALTER TABLE BOOKS ENABLE CONSTRAINT STUD_ID; | Which of the following is the correct example of enabling constraint? | |
| The correct answer is: | Which of the following is the correct example of derived attribute? | |
| ROLLBACK TO SAVEPOINT | A type of transaction that rolls back the current transaction to the specified savepoint, thereby discarding any changes or savepoint created after the savepoint to which you are rolling back. | |
| A foreign key in one table points to a foreign key in another table. | Which of the following statement is not true about Foreign Key constraint? | |
| INSERT INTO EMPLOYEES(ID, LASTNAME, DEPARTMENT) SELECT ID, LASTNAME, DEPARTMENT FROM WORKERS WHERE STATUS = ‘Regular’; | Supposed that table: Workers and Employees is consists of the following values. | |
| This will retrieve the record of Mortos and Santiago | Supposed that table: Workers and Employees is consists of the following values. | |
| The lastname of Cruz from Employees table will be set to NULL | Supposed that table: Workers and Employees is consists of the following values. | |
| The given statement is Outer Join | SELECT EMPLOYEES.EMPLOYEE_ID, EMPLOYEES.LASTNAME,EMPLOYEES.DEPARTMENT_ID,DEPARTMENTS.DEPARTMENT_ID,DEPARTMENTS.LOCATION_ID | |
| All Workers department will be set to NULL | Supposed that table: Workers and Employees is consists of the following values. | |
| The correct answer is: | Which of the following is the correct representation of many-to-many relation? | |
| USN_ID | Which of the following is the correct example of an attributes? | |
| The correct answer is: | Which of the following is the correct representation of one-to-many relation? | |
| Domain Intergrity | It is used to ensure that data values inside a database follow defines rules for values, range and format | |
| CREATE VIEW WORKERS (EMPLOYEE_ID, SURNAME, FIRSTNAME) AS (EMPLOYEE_ID, LASTNAME, FIRSTNAME FROM EMPLOYEES WHERE SALARY LASTNAME LIKE '%S'); | Which of the following is the correct example of creating a view where LASTNAME ends with letter S rename LASTNAME to SURNAME? | |
| It restricts the users to add a value where course is equal to BSCS only | If WITH CHECK OPTION is added on the view (see sample code below) what is/are the restriction? | |
| Isolation | A state of the data where a transaction data must not be available to other transactions until the original transaction is committed or rolled back. | |
| INSERT INTO WORKERS(ID, NAME, POSITION) SELECT (ID, NAME, POSITION FROM EMPLOYEES); | Which of the following is the correct example of creating a subquery that copy the values from employees table to workers table? | |
| Database Schema | It is the skeleton structure that represents the logical view of the entire database. | |
| No changes made on employees table. | Supposed that a user performs the query as shown below, what will happen to the data? | |
| NO DML operations allowed on this view. | If WITH READ ONLY is added on the view (see sample code below) what is/are the restriction? | |
| Simple Attribute | This is a type of attribute that is atomic value, which cannot be divided further. | |
| Composite Attribute | This is a type of attribute that is made of more than one simple attribute. | |
| Logical Database Schema | This is a schema that pertains to the actual storage of data and its form of storage like files, indices. | |
| Physical Data Independence | This is a type of schema that has the power to change the physical data without impacting the schema or logical data. | |
| Tuple | A single row of a table, which contains a single record for that relation. | |
| Key | It is an attribute or collection of attributes that uniquely identifies an entity among entity set. | |
| USN_ID | Which of the following is the correct example of an attribute? | |
| Derived Attribute | A type of that do not exist in the physical database, but their values are derived from other attributes present in the database. | |
| Workers | Which of the following is the correct example of an entity. | |
| Entity Relationship Model | A type of model that is based on the notion of real-world entities and relationships among them. | |
| Data Model | This is the fundamental entity which introduces abstraction in a Database Management System (DBMS). | |
| Logical Database Schema | This is a type of schema defines all the logical constraints that need to be applied on the data stored. It defines tables, views, and integrity constraints. | |
| Using | This is a join clause used when the columns in two or more tables have the same but of different data types. | |
| ON | This is a join clause used to specify arbitrary conditions of specify columns to join. | |
| Natural Join | A join condition that is based on all the columns in two or more table that have the same name. | |
| Inner Join | It is a join of two or more table that returns only matched rows. | |
| Outer Join | It is join between two tables that return the result of an Inner Join as well as the results of Left and Right joins. | |
| Cartesian Product | It is formed when a join condition is omitted. | |
| Cartesian Product | This is a join clause that produces a cross-product of two or more tables. | |
| Self-Join | A join condition used when a table has columns with match values. |