Database Management System 2 (Oracle 10g Admin)

 IT 6203 Database Management System 2 (Oracle 10g Admin)

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