Database Management System 2

 Database Management System 2







Redo Log BufferIt holds information about changes made to the database

System and SyshauxWhich 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 ServerIt is the key in solving the problems of information management.

Large PoolAlso known as Oracle Database backup and restore operations

Oracle DatabaseIt 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 ProcessIt is a type of process structure that automatically connects to other databases involved in in- doubt distributed transactions

SessionIt is also known as specific connection of a user to an instance through a user process

ConnectionIt 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 PoolIt is part of the System Global Area which is used to provide memory for Oracle Streams processes

Data Buffer CacheIt is part of the System Global Area (SGA) that hold copies of data blocks that are read from data files

Recoverer ProcessIt 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

DbaWhich of the following is not included in the optional requirement for operating system of oracle database?

3GB for the ASM instanceWhich 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.

ConnectionIt 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 environmentWhich of the following system privileges is not part of the task by the database admin.?

sysdbaWhich 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?

SystemIt 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_instanceWhich 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?

sessionIt represents the state of a current user login to the database instance.

Server processThis is use to connect to the Oracle instance and is started when a user establishes a session

ORACLE_HOMEIt is a type a Oracle environment where Oracle Products run.

Oracle databaseIt 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?

ObjectIt is a type of privilege that is used to manipulating the content of the database objects.

Redo Log BufferHolds information about changes made to the database

Data Buffer CacheIt is part of the System Global Area (SGA) that hold copies of data blocks that are read from data files.

3.8 GBWhich 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 serverIt is the key to solving the problems of information management.

sessionAlso known as the application or tool that connects to the Oracle Database Processes

SchemaThis is also known collection of objects such as tables, views, and sequences.

REVOKEThis is used to remove a privilege granted to a user.

CREATE INDEXWhich of the following System Privileges is not part of SYSTEM privilege?

RoleIt 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 RollbackA transaction ends when either of the following occurs. (Choose 5)

COMMITWhich one (1) DCL (Data Control Langauge) is considered as part of Database Transaction

CREATE TABLEWhich 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 buttonAn automatic rollback is issued by the oracle under the following circumstances. (Choose 2)

The correct answers are: INSERT, UPDATE, DELETEWhich three (3) of thefollowing is the three Data Manipulation Langauge that is considered as part of database transaction.

DMLA transaction start when the first ____________ is issued.

The correct answers are: CREATE TABLE is issued, Closes the sqlplus normally by typing exitAn 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 ManagerWhich of the following is not part or Oracle Installation System Requirement.

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

DeadlockIt occurs when two or more applications wait indefinitely for a resources.

Logical Data IndependenceThis is a kind of mechanism, which liberalizes itself from actual data stored on the disk.

Entity -- EntityWhich 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?

COMMITA 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 DiagramThis 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 InstanceIt is a state of operational database with data at any given time.

LockThis 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?

AtomicityA state of the data where a transaction must be fully complete, saved (committed) or completely undone (rolled back).

Lost UpdateWhich of the following is not possible causes of lock.

TransactionThis gives the user more flexibility and control when changing data and they ensure data consistency in the event of user process or system failure.

LockwaitA process where an application waits indefinitely to obtain any needed locks.

KOCHAR, NENA and LEX, DE HAANBased 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 clauseWhich of the following joins are mutually exclusive.

All of the choicesWhich of the following is not true about complex view when using INSERT statement?

0 rows are deleted in Workers tableSupposed 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 JABBERWACKYBased on the table EMPLOYEES, DEPARTMENTS and LOCATIONS.

All of the choicesWhich 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 2016Refer 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 keyA type of constraint that ensures the relationship between tables remain preserved as data is inserted, deleted and modified.

DROP CASCADEThis 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 incorrectSELECT 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 KeyWhat are the two forms of key integrity constraint?

CheckA type of constraint that is used to limit the value range that can be placed in a column.

UsingThis 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 choicesWhich of the following is not true about complex view when using UPDATE statement?

UniqueA type constraint that uniquely identifies each record in a database table and can be applied on one or more column.

MAXWhich 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 PrefixesThis is used to quality ambiguous column when joining two or more tables.

ON DELETE CASCADEThis 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 CONSTRAINTThis 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 tableRefer 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 keyA type of constraint uniquely identifies each record in a database table.

ConsistencyA state of the data where a transaction must be fully compliant with the state of the database as it was prior to the transaction.

TransactionThis is also known as unit of work.

INWhich of the following is not part of single to subqueries?

durabilityA state where a transaction data changes must be available, even in the event of database failure.

Domain IntergrityIt is used to ensures that data values inside a database follow defines rules for values, range and format

DISABLE CONSTRAINTThis is used to deactivate an integrity constraint.

DefaultA 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 SAVEPOINTA 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 SantiagoSupposed that table: Workers and Employees is consists of the following values.

The lastname of Cruz from Employees table will be set to NULLSupposed that table: Workers and Employees is consists of the following values.

The given statement is Outer JoinSELECT EMPLOYEES.EMPLOYEE_ID, EMPLOYEES.LASTNAME,EMPLOYEES.DEPARTMENT_ID,DEPARTMENTS.DEPARTMENT_ID,DEPARTMENTS.LOCATION_ID

All Workers department will be set to NULLSupposed 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_IDWhich 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 IntergrityIt 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 onlyIf WITH CHECK OPTION is added on the view (see sample code below) what is/are the restriction?

IsolationA 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 SchemaIt 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 AttributeThis is a type of attribute that is atomic value, which cannot be divided further.

Composite AttributeThis is a type of attribute that is made of more than one simple attribute.

Logical Database SchemaThis is a schema that pertains to the actual storage of data and its form of storage like files, indices.

Physical Data IndependenceThis is a type of schema that has the power to change the physical data without impacting the schema or logical data.

TupleA single row of a table, which contains a single record for that relation.

KeyIt is an attribute or collection of attributes that uniquely identifies an entity among entity set.

USN_IDWhich of the following is the correct example of an attribute?

Derived AttributeA type of that do not exist in the physical database, but their values are derived from other attributes present in the database.

WorkersWhich of the following is the correct example of an entity.

Entity Relationship ModelA type of model that is based on the notion of real-world entities and relationships among them.

Data ModelThis is the fundamental entity which introduces abstraction in a Database Management System (DBMS).

Logical Database SchemaThis 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.

UsingThis is a join clause used when the columns in two or more tables have the same but of different data types.

ONThis is a join clause used to specify arbitrary conditions of specify columns to join.

Natural JoinA join condition that is based on all the columns in two or more table that have the same name.

Inner JoinIt is a join of two or more table that returns only matched rows.

Outer JoinIt is join between two tables that return the result of an Inner Join as well as the results of Left and Right joins.

Cartesian ProductIt is formed when a join condition is omitted.

Cartesian ProductThis is a join clause that produces a cross-product of two or more tables.

Self-JoinA join condition used when a table has columns with match values.