Program-data dependence is caused by: file descriptions being stored in each database application. data descriptions being stored on a server. data descriptions being written into programming code. data cohabiting with programs. .
Because applications are often developed independently in file processing systems: the data is always non-redundant. unplanned duplicate data files are the rule rather than the exception. data can always be shared with others. there is a large volume of file I/O. .
A(n) ________ is often developed by identifying a form or report that a user needs on a
regular basis. enterprise view reporting document user view user snapshot .
A user view is: what a user sees when he or she looks out the window. a table or set of tables. a logical description of some portion of the database. a procedure stored on the server. .
All of the following are primary purposes of a database management system (DBMS)
EXCEPT: what a user sees when he or she looks out the window. a table or set of tables. a logical description of some portion of the database. a procedure stored on the server. .
With the database approach, data descriptions are stored in a central location known as
a: server. mainframe. PC. repository.
Which of the following is NOT an advantage of database systems? Redundant data Program-data independence Better data quality Reduced program maintenance .
The most common source of database failures in organizations is: lack of planning. inadequate budget. inadequate hardware failure to implement a strong database administration function. .
In a file processing environment, descriptions for data and the logic for accessing the data
are built into: application programs database descriptors. fields. records .
Which of the following is NOT a cost and/or risk of the database approach? Specialized personnel Cost of conversion Improved responsiveness Organizational conflict
.
Which of the following is software used to create, maintain, and provide controlled
access to databases? Network operating system User view Database management system (DBMS) Attribute .
The three-schema approach includes which of the following schemas? Internal Logical Cross-functional Dissecting.
A key decision in the physical design process is: ignoring the size of the database. selecting structures. deciding on the monitor. deciding the e-r diagrams.
A detailed coding scheme recognized by system software for representing
organizational data is called as : DBMS code. Data type. SQL. DB.
All of the following are objectives when selecting a data type EXCEPT: represent all possible values. improve data integrity. support all data manipulations. use a lot of storage space. .
All of the following are valid datatypes in Oracle 11g EXCEPT: varchar2 Boolean blob number.
The smallest unit of application data recognized by system software is a: field row data type. column.
An appropriate datatype for one wanting a fixed-length type for last name would
include: VarChar Char Blob Date.
An appropriate datatype for adding a sound clip would be: VarChar Char Blob Date.
Which of the following is an objective of selecting a data type? Represent a small number of possible values Maximize storage space Limit security Improve data integrity .
In which data model would a code table appear? Conceptual Logical Physical Data layout .
An integrity control supported by a DBMS is: substitute estimates. security range control. GUI guards .
The value a field will assume unless the user enters an explicit value for an instance of
that field is called a: default value. null value. range control gurand.
A method for handling missing data is to: substitute with random numbers for the missing data. track missing data with special reports. perform insensitivity testing. delete the associated row. .
Sensitivity testing involves: checking to see if your teeth hurt when you brush. seeing how accurate data are. checking to see if missing data will greatly impact results deleting the associated row. .
A contiguous section of disk storage space is called a(n): track sector extent tablespace.
A method to allow adjacent secondary memory space to contain rows from several
tables is called: cluttering clustering concatenating compiling.
A(n) ________ is a technique for physically arranging the records of a file on secondary
storage devices. physical pointer retrieval program file organization update program .
Distributing the rows of data into separate files is called: normalization. horizontal partitioning. vertical partitioning. file allocation. .
Horizontal partitioning makes sense: when different categories of a table's rows are processed separately. when less security is needed when partitions must be organized the same. when only one category is allowed. .
An advantage of partitioning is: efficiency remote optimization extra space and update time increase redundancy. .
A disadvantage of partitioning is: simplicity remote optimization. extra space and update time shorter technology spans. .
________ partitioning distributes the columns of a table into several separate physical
records. Horizontal Crossways Vertical Final.
A form of denormalization where the same data are purposely stored in multiple places
in the database is called: data duplication. data replication. advanced placement. horizontal partitioning. .
Which of the following contains data rows in SQL Server File group Page Partition Block.
For the columns and data content of the employees table: Employee_id 100 l‡ 40000, Employee_id 101 l‡ 10000000000 Employee_id 100 l‡ 20000, Employee_id 101 l‡ 10000 Employee_id 100 l‡ 20000, Employee_id 101 l‡ 10000 Employee_id 100 l‡ 40000, Employee_id 101 l‡ 10000 .
When no data is found in the Data Cache, Sql Server will load _______ from ______ into
the Data Cache: Data ; Database Page ; Data File Block ; Database Data Row ; Data File .
Which of the following statements is not about performance received from filegroup: Read and write tasks in parallel Ability to move indexes and large object data Group the same files Ability to split large data tables .
Note the following structures in your database server:
Q: Which option has the correct arrangement of these structures from the smallest to the
largest? 1,2,3,4,5 2,5,1,4,3 5,2,1,3,4 2,1,5,4,3 .
Note the following functionalities of various background processes:
1: Record the checkpoint information in data file headers.
2: Perform recovery at instance startup.
3: Cleanup unused temporary segments.
4: Free the resources used by a user process when it fails.
5: Dynamically register database services with listeners.
6: Monitor sessions for idle session timeout.
Q: Which option has the correct functionalities listed for a background process? Archiver Process (ARCn): 1, 2, 5 System Monitor Process (SMON): 1, 4, 5 Process Monitor Process (PMON): 4, 5, 6 Database Writer Process (DBWn): 1, 3, 4 .
View the Exhibit and identify the component marked with a question mark.
Exhibit: Checkpoint (CKPT) Process Monitor (PMON) Archiver Processes (ARCn) Recoverer Process (RECO) .
View the Exhibit and identify the component marked with a question mark.
Exhibit: Checkpoint (CKPT) Process Monitor (PMON) Archiver Processes (ARCn) Recoverer Process (RECO) .
Which two statements about the background process of the database writer are true?
(Choose two.) It is possible to have multiple database writers in an Oracle instance. It writes dirty buffers, if any, to data files whenever a checkpoint occurs. It writes dirty buffers, if any, to data files whenever a transaction commits. It writes dirty buffers, if any, to data files before the log writer (LGWR) writes. .
Which three statements are true about logical structures of the Oracle database?
(Choose three.) Each segment contains one or more extents Multiple tablespaces can share a single data file. A data block is the smallest unit of input/output (I/O) in data files. It is possible to have tablespaces of different block sizes in a database. Each data block in the database always corresponds to one operating system block. .
Which two statements correctly describe the relation between a data file and the logical
database structures? (Choose two.) An extent cannot spread across data files. A segment cannot spread across data files. A data file can belong to only one tablespace. A data file can have only one segment created in it. A data block can spread across multiple data files as it can consist of multiple operating
system (OS) blocks. .
. ________ is a technical function responsible for database design, security, and disaster
recovery Data administration Database administration Tech support Operations.
The role of a ________ emphasizes integration and coordination of metadata across
many data sources data warehouse administrator data administrator database administrator data architect.
An information repository supplies information: to database programmers only. to only internal sources. to database management systems. exclusively to data lakes. .
A repository of information about a database that documents data elements of a
database is called a: schema subschema. view data dictionary.
Which of the following is NOT a component of a repository system architecture? An informational model The repository engine A data transformation process The repository database.
The actions that must be taken to ensure data integrity is maintained during multiple
simultaneous transactions are called ________ actions. logging concurrency control transaction authorization multiple management .
A(n) ________ prevents another transaction from reading and therefore updating a
record until it is unlocked record controller exclusive lock authorization rule shared lock .
An optimistic approach to concurrency control is called: versioning denormalization deadlock resolution. Happy Control. .
The ________ occurs when one user reads data that have been partially updated by
another user. inconsistent read problem distant read problem honest dirty data data quality problem .
Guidelines for server security should include all of the following EXCEPT: securing the network between client and server. guidelines for password lengths. password naming conventions. guidelines for frequency of password changes. .
Controls designed to restrict access and activities are called: schemas business rules. encryption controls authorization rules. .
A device to measure or detect fingerprints or signatures is called a(n) ________ device. biometric view ink laser.
The coding or scrambling of data so that humans cannot read them is called: demarcation hiding hiding encryption.
An audit trail of database changes is kept by: change control device. subschema before image. journalizing facility. .
A DBMS periodically suspends all processing and synchronizes its files and journals
through the use of a: checkpoint facility. backup facility. recovery manager. database change log.
A discrete unit of work that must be processed completely or not at all within a
computer system is called a: change control device. transaction before image. journalizing facility .
A ________ is a DBMS module that restores the database to a correct condition when a
failure occurs backup facility recovery manager restart facility transaction logger .
________ is used to undo unwanted database changes. Rollback Rollforward Restart Encryption.
Forward recovery is faster than restore/rerun because: transactions do not have to be repeated. security can be avoided. images are mirrored. systems are more complete. .
The preferred method of fixing an aborted transaction is: repairing the schema. switching duplication of data. backing out the transaction. .
When incorrect data have been introduced, the database is best recovered by: starting with a new ERD. formatting server. restarting from the most recent checkpoint and processing subsequent transactions. reloading RDMS software. .
A transaction that terminates abnormally is called a(n) ________ transaction. terminated aborted completed deleted.
Which of the following is NOT an area of concern when trying to maintain a well-tuned
database? Memory usage CPU usage Input/output contention User interface design .
A(n) ________ is submitted by a DBA to test the current performance of a database or
predict the response time for queries. elusive query performance test heartbeat query HTML.
Most data outages in organizations are caused by: hardware failures. software failures. human error. electrical outages. .
For which database operation would you need the database to be in the MOUNT
state? Renaming the control files Recreating the control files Dropping a user in your database Dropping a tablespace in your database Configuring the database instance to operate in the ARCHIVELOG or NOARCHIVELOG
mode. .
Which statements listed below describe the data dictionary views?
1. These are stored in the SYSTEM tablespace
2. These are the based on the virtual tables
3. These are owned by the SYS user
4. These can be queried by a normal user only if
O7_DICTIONARY_ACCESSIBLILITY parameter is set to TRUE
5. The V$FIXED_TABLE view can be queried to list the names of these views 1 and 3 2,3 and 5 1,2, and 5 2,3,4 and 5.
You have issued a SHUTDOWN ABORT command to bring down your database
instance.
Consider the steps that will be performed later when you open the database:
1 SGA is allocated.
2 Control file is read.
3 Redo log files are read.
4 Instance recovery is started.
5 Background processes are started.
6 Data files are checked for consistency.
7 Server parameter file or the initialization parameter file is read.
Q: Which option has the correct order in which these steps occur? 7,1,5,2,3,6,4 1, 2, 3,7, 5, 6, 4 7,1,4,5,2,3,6 1,7,5,4,2,3,6 .
Which three statements regarding the server parameter file (SPFILE) are true? (Choose
three.) An SPFILE is a binary file An SPFILE cannot reside on a client An SPFILE cannot contain static parameters An SPFILE can store changes persistently across instance restarts.
Given below is a list of scenarios:
1) A user terminates his session abnormally.
2) The connection between the server and the client application terminates because of
a network failure.
3) A schema is accidentally dropped
4) The tablespace is accidentally dropped from the database.
5) The hard disk gets corrupted and the data files in the disk are lost.
6) The database instance abnormally shuts down because of power failure.
Q: Which scenarios require DBA intervention to perform recovery? 1, 3, and 6 4, 5, and 6 3, 4, and 5 1, 2, and 6 .
Which two statements are true regarding listeners? (Choose two.) Listeners use only the TCP/IP protocol. Multiple listener processes can run simultaneously on a host. Multiple database instances can be registered with a single listener. The listener related errors can be traced only at the administrative level. Only one database instance can be registered with a single listener at any time. .
Your database is open and the LISTENER listener is running. The new DBA of the system
st ops the listener by using the command:
LSNRCTL> STOP
What happens to the sessions that are presently connected to the database instance? The sessions are able to perform only queries The sessions are not affected and continue to function normally The sessions are terminated and the active transactions are rolled back The sessions are not allowed to perform any operations till the listener is started .
You executed the following command to create a tablespace called SALES_DATA
SQL> CREATE TABLESPACE sales_data DATAFILE SIZE 100M
SEGMENT SPACE MANAGEMENT AUTO;
Which two statements are true about the SALES_DATA tablespace? (Choose two) The database automatically determines the extentsizing policy for the tablespace. The segments are automatically shrunk when the contents are removed from them. The allocation of extents within the tablespace is managed through the dictionary tables. The space utilization description of the data blocks in segments is recorded in bitmap
blocks. The space utilization description of the data blocks in segments is managed through free
lis. .
Identify two situations in which the block header grows in a data block. (Choose two.) When row directories need more row entries When there is row migration in the data block When there is an increase in the PCTFREE value for the data block When more transaction slots are required than are initially configured .
Examine the command:
SQL>ALTER USR skd ACCOUNT LOCK;
Which two statements are true after the command is executed? The SKD user cannot log in to the database instance The objects owned by the SKD user are not accessible to any user The other users can access the objects owned by the SKD user, on which they have
access The password for the SKD user expires and the user is forced to change the password at
the next log in.
You executed the following command to create a password file in the database server:
$ orapwd file = orapworcl entries = 5 ignorecase=N
Which statement describes the purpose of the above password file? It records usernames and passwords of users when granted the DBA role It contains usernames and passwords of users for whom auditing is enabled It is used by Oracle to authenticate users for remote database administrator It records usernames and passwords of all users when they are added to OSDBA or
OSOPER operating Groups.
After performing a clean shut down of the database instance for maintenance, you
mount the database and then execute a command to open the database:
SQL> ALTER DATABASE OPEN;
Which two statements are true? (Choose two.) The online redo log files and online data files are opened All the online data files headers are checked for consistency Instance recovery is performed before opening the database The path and existence of all the log file members are checked .
Which three statements are correct about temporary tables? (Choose three.) Indexes and views can be created on temporary tables Both the data and structure of temporary tables can be exported Temporary tables are always created in a user's temporary tablespace The data inserted into a temporary table in a session is available to other sessions Data Manipulation Language (DML) locks are never acquired on the data of temporary
tables .
Which two statements are true regarding undo tablespaces? (Choose two.) The database can have more than one undo tablespace. The UNDO_TABLESPACE parameter is valid in both automatic and manual undo
management. Undo segments automatically grow and shrink as needed, acting as circular storage
buffer for their assigned transactions. An undo tablespace is automatically created if the UNDO_TABLESPACE parameter is not
set and the UNDO_MANAGEMENT parameter is set to AUTO during the database instance
start up. .
View the Exhibit and examine the undo tablespace attributes. Your database instance is
experiencing a large volume of transactions from nonDBA users in the last one hour. The
undo tablespace UNDOTBS1 is full with transactions and no transaction was committed
more than one hour ago. The database has two more undo tablespaces. What happens to
the new DML operations in this scenario? The DML commands will fail. The undo data generated by the DML is stored in one of the two other undo
tablespaces. The undo data generated by the DML will overwrite the extents that contain committed
undo data. The undo data generated by the DML is stored in the SYSTEM undo segment of the
SYSTEM tablespace. .
In which situation may the UNDO_RETENTION parameter be ignored, even if it is set to a
value? when the data file of the undo tablespace is autoextensible when there are more than one undo tablespaces available in the database when the undo tablespace is of a fixed size and retention guarantee is not enabled when the undo tablespace is autoextensible and retention guarantee is not enable.
Which two statements are true about the Automatic Database Diagnostic Monitor
(ADDM)? (Choose two.) The ADDM requires at least four AWR snapshots for analysis The ADDM runs after each AWR snapshot is collected automatically by MMON The results of the ADDM analysis are stored in the Automatic Workload Repository
(AWR) The ADDM analysis provides only diagnostics information but does not provide
recommendations E. The ADDM calls other advisors if required, but does not provide
recommendations about the advisors .
In your database instance, the STATISTICS_LEVEL initialization parameter is set to BASIC.
What is the impact of this setting? Optimizer statistics are collected automatically Only timed operating system (OS) statistics and plan execution statistics are collected. The snapshots for the Automatic Workload Repository (AWR) are not generated
automatically. Snapshots cannot be collected manually by using the DBMS_WORKLOAD_REPOSITORY
package. The Oracle server dynamically generates the necessary statistics on tables as part of
query optimization. .
Which statement is true about a whole consistent database backup on a database
running in ARCHIVELOG mode? The backup will consist of used data blocks only. The database must be shut down to accomplish the backup. The backup can be accomplished without shutting down the database. The backup will contain all database files that have never been backed up. .
You perform differential incremental level 1 backups of your database on each working
day and level 0 backup on Sunday to tape:
Which two statements are true about differential incremental backups? (Choose two.) The backup performed on Sundays contains all the blocks that have ever been used in
the database The backup performed on Sundays contains all the blocks that have changed since the
last level 1 backup The backup performed on each working day contains all the blocks that have changed
since the last level 0 backup The backup performed on Monday contains all the blocks that have changed since the
level 0 backup and every other working day contains all the blocks that have changed
since the level 1 backup.
Your test database is configured to run in NOARCHIVELOG mode. One of the data files in
the USERS tablespace is lost due to a media failure. You notice that all the online redo logs
have been overwritten since the last backup.
Q: What would you do to recover the data file? Take the USERS tablespace offline and re-create the lost data file Shutdown the instance, restore the data file from the last consistent backup and restart
the database instance Shutdown the instance, restores all the database files from the last consistent backup
and restart the database instance Take the USERS tablespace offline, restore all the data files of the USERS tablespace from
the last consistent backup and make the tablespace online .
A requirement to begin designing physical files and databases is: normalized relations. physical tables created. implementation complete. all datatypes determined. .
Designing physical files requires ________ of where and when data are used in various
ways. maps descriptions keys hints.
Database access frequencies are estimated from: transaction volumes. user logins. security violations random number generation.
The storage format for each attribute from the logical data model is chosen to maximize
________ and minimize storage space. query design programmer productivity data integrity data integration.
Within Oracle, the named set of storage elements in which physical files for database
tables may be stored is called a(n): extent table tablespace partition.
While Oracle has responsibility for managing data inside a tablespace, the tablespace as a
whole is managed by the: user CEO XML operating system. .
A(n) ________ is a field of data used to locate a related field or record. key index lock pointer.
Which type of file is easiest to update? Sequential Hashed Indexed Clustered.
A factor to consider when choosing a file organization is: inefficient data retrieval. DDL efficient storage. DML.
One field or combination of fields for which more than one record may have the same
combination of values is called a(n): secondary key. index composite key linked key .
In which type of file is multiple key retrieval not possible? Sequential Hashed Indexed Clustered.
A file organization that uses hashing to map a key into a location in an index where there
is a pointer to the actual data record matching the hash key is called a: hashed file organization. hash key. multi-indexed file organization. hash index table .
A file organization where files are not stored in any particular order is considered a: hashed file organization. hash key multi-indexed file organization. heap file organization .
An index on columns from two or more tables that come from the same domain of
values is
called a: bitmap index. multivalued index. join index. transaction index .
A(n) ________ is a routine that converts a primary key value into a relative record
number. record index calculator index pointer program hashing algorithm pointing algorithm.
A method that speeds query processing by running a query at the same time against
several partitions of a table using multiprocessors is called: multiple partition queries perpendicular query processing. parallel query processing. query optimization.
.
A command used in Oracle to display how the query optimizer intends to access indexes,
use parallel servers and join tables to prepare query results is the: explain plan. show optimization. explain query. analyze query .
All of the following are common denormalization opportunities EXCEPT: two entities with a one-to-one relationship. a one-to-many relationship. a many-to-many relationship with nonkey attributes. reference data. .
In most cases, the goal of ________ dominates the design process. efficient data processing security quick pointer updates shorter design times .
All of the following are horizontal partitioning methods in Oracle EXCEPT: key range partitioning. hash partitioning. multivalued partitioning. composite partitioning.
|
