C192 Study Cards

Réussis tes devoirs et examens dès maintenant avec Quizwiz!

Which is not a DML statement? CREATE TABLE SELECT INSERT DELETE

CREATE TABLE

A transaction must transform the database from one consistent state to another consistent state

Consistency

_____________________________ distributed database systems integrate different types of centralized DBMSs over a network.

Heterogeneous

The data cache or ____ is a shared, reserved memory area that stores the most recently accessed data blocks in RAM. a. buffer cache b. procedure cache c. SQL cache d. permanent storage

buffer cache

A(n) __________________ model is independent of both hardware and software conceptual external developmental logical

conceptual

A(n) ________________________ model is independent of both hardware and software

conceptual

The ERD is used to graphically represent the _________________ database model condensed physical logical conceptual

conceptual

The ERD is used to graphically represent the __________________ database model - condensed - physical - logical - conceptual

conceptual

The ERD is used to graphically represent the ________________________ database model

conceptual

Database models can be grouped into two categories:

conceptual models and implementation models

deadlock

condition occurs when two transactions wait for each other to unlock data

A _____________ is a named collection of database access privileges that authorize a user to connect to a database and use the database system resources user role profile manager

role

The location of each data value in the data cube is a function of the x, y, and z axes in a ____ space. a. one-dimensional c. three-dimensional b. two-dimensional d. multidimensional

three-dimensional

The location of each data value in the data cube is a function of the x,y,and z axes in a ___________________________ space

three-dimensional

Given the importance and universality of this dimension from a data analysis perspective, many vendors have added automatic ____ dimension management features to their data warehousing products. a. space c. location b. time d. product

time

The most common algorithms for concurrency control are locks, _________________ stamping, and optimistic methods date time hour minute

time

The ____ approach to scheduling concurrent transactions assigns a global unique stamp to each transaction. a. scheduled b. table-locking c. unique d. time-stamping

time-stamping

The ________________________ approach to scheduling concurrent transactions assigns a global unique stamp to each transaction

time-stamping

___________________________ occur when a transaction calculates some summary (aggregate) function over a set of data while other transactions are updating the data

inconsistent retrievals

The property of _____________ enables an entity subtype to inherit the attributes and relationships of the supertype subtype discriminator inheritance specialization hierarchy entity supertype

inheritance

When using a(n) __________________ join, only rows that meet the given criteria are returned full inner outer set

inner

Which of the following joins compares the tables in the FROM clause and lists only those rows that satisfy the condition in the WHERE clause? inner outer nested product

inner

The components of a data warehouse are ___________________

integrated

A specialization hierarchy can have _______________ levels of supertype/subtype relationships none only one one or many many

many

Multidimensional data analysis refers to the processing of data such that data are viewed as part of the ____ structure. a. semidimensional b. multidimensional c. one-dimensional d. two-dimensional

multidimensional

Multidimensional data analysis refers to the processing of data such that data are viewed as part of the ____ structure. a. semidimensional c. one-dimensional b. multidimensional d. two-dimensional

multidimensional

Multidimensional data analysts refer to data such that data are viewed as part of the ________________________ structure

multidimensional

MOLAP's premise is that __________________________ are best suited to manage, store, and analyze multidimensional data

multidimensional databases

The ___________________________________________ scenario describes a fully distributed DBMS with support for multiple data processors and transaction processors at multiple sites

multiple-site processing, multiple-site data

A ___________ is a named collection of database access privileges that authorize a user to connect to a database and use the database system resources

role

All transactions are controlled and executed by the ________________ to guarantee database integrity

DBMS

The components of a data warehouse are ____. a. integrated b. volatile c. object-oriented d. decentralized

integrated

A(n) ____ lock exists when concurrent transactions are granted read access on the basis of a common lock. a. shared b. exclusive c. binary d. two-phase

common lock

The key's role is based on a concept known as _______________________

determination

A(n) ____ backup of the data is when only the last modifications done to the database are copied. a. differential b. complete c. partial d. incomplete

differential

____________________ are qualifying characteristics that provide additional perspectives to a given fact

dimensions

The data warehouse and OLAP are two interrelated and complementary ____. a. environments c. systems b. programs d. warehouses

environments

One measure that determines the need for an index is the __________________ of the column you want to index. Data sparsity refers tot he number of different values a column could possibly have

data sparsity

MOLAP extends OLAP functionality to multidimensional __________________ management systems

database

Which database design phase uses normalization techniques to eliminate anomalies in the data model? XML database design Physical database design Logical database design Conceptual database design

Logical database design

What is one of the three most common data integrity and consistency problems? a. Lost updates b. Disk failures c. User errors d. Deadlocks

Lost updates

The ____ function is used to find the highest value in a table column. a. MAX b. TOTAL c. SUM d. TOP

MAX

The _________________ function is used to find the highest value in the table column

MAX

In which DSS architectural style does the data store contain lightly aggregated data in an RDBMS? a. Traditional mainframe-based OLTP b. MIS with 3GL c. First-generation departmental DSS d. Second-generation data warehouse using MDBMS

MIS with 3GL

Which technique is used to test the correctness of a logical database design model? Prototyping Normalization Logical grouping Extensibility

Normalization

Which of the following is NOT a technique used to optimize data warehouse design? a. Normalization of dimensional tables b. Multiple fact tables representing different aggregation levels c. Table partitioning and replication d. Normalization of fact tables

Normalization of fact tables

Multidimensional data analysis requires some type of multidimensional data representation, which is normally provided by the ____ engine. a. VLDB c. OLAP b. RDBMS d. ROLAP

OLAP

____ is defined as an advanced data analysis environment that supports decision making, business modeling, and an operation's researching activities. a. VLAD c. OLAP b. ROLAP d. DSS

OLAP

Which database management systems (DBMS) architecture emerged in the third generation of DBMSs? Choose 2 answers Object structured Object oriented Object stacking Object relational

Object stacking Object relational

A database architect is tasked with designing and implementing a data warehouse using a star schema. Which design approach should the architect follow? One fact table surrounded by normalized dimension tables One dimension table surrounded by normalized fact tables One dimension table surrounded by normalized and denormalized fact tables One fact table surrounded by denormalized dimension tables

One fact table surrounded by denormalized dimension tables

What is a consistent database? One in which all tables have foreign keys One in which all data integrity constraints are satisfied One in which all tables are normalized One in which all SQL statements only update one at a time

One in which all data integrity constraints are satisfied

What is a consistent database? a. One in which all tables have foreign keys b. One in which all data integrity constraints are satisfied c. One in which all tables are normalized d. One in which all SQL statements only update one table at a time

One in which all data integrity constraints are satisfied

____ is a measurement of the density of the data held in the data cube. a. Compactness c. Mass b. Concentration d. Sparsity

Sparsity

Which characteristic does static optimization have been compared with dynamic optimization? Static optimization is done at run time, whereas dynamic optimization is done prior to run time. Static optimization requires more runtime overhead, whereas dynamic optimization has less overhead. Static optimization is optimized prior to execution, whereas dynamic optimization is optimized during run time. Static optimization is continually up to date, whereas dynamic optimization is updated once.

Static optimization is optimized prior to execution, whereas dynamic optimization is optimized during run time

What describes a specialized grouping of entity type occurrences that must be represented in a data model? Superclass Baseclass Relationship Subclass

Subclass

Which of the following is a characteristic of an OLAP? a. Uses single dimensional data analysis techniques b. Provides introductory database support and complex end user interfaces c. Provides introductory end-user interfaces d. Supports client/server architecture

Supports client/server architecture

Which of the following is a characteristic of an OLAP? a. Uses single dimensional data analysis techniques b. Provides introductory database support and complex end user interfaces c. Provides introductory end-user interfaces d. Supports client/server architecture

Supports client/server architecture

When this SQL code runs, all associated objectives will also be removed. DROP SCHEMA testingHouse CASCADE What happens if any of these sub drops fail? The DROP SCHEMA fails. The DROP SCHEMA reruns failed drops. The DROP SCHEMA skips failed drops. The DROP SCHEMA deadlocks.

The DROP SCHEMA fails

Which data representation concept is associated with online analytical processing (OLAP)? The concept of "slice and dice" The concept of "associate arrays" The concept of "hash tables" The concept of "facts"

The concept of "facts"

COUNT

is used to tally the number of specific "values" of an attribute

The scheduler facilitates data ____ to ensure that two transactions do not update the same data element at the same time. a. durability b. isolation c. atomicity d. serializability

isolation

Database models can be grouped into two categories: conceptual models and _________________ models implementation logical physical query

implementation

The scheduler facilitates data _____________ to ensure that two transactions do not update the same data element at the same time durability isolation atomicity serializability

isolation

An ________________________ approach is based on the assumption that the majority of the database operations do not conflict

optimistic

The ____ approach is based on the assumption that the majority of the database operations do not conflict. a. default b. basic c. scheduled d. optimistic

optimistic

A ____ lock locks the entire table preventing access to any row by a transaction while another transaction is using the table. a. database-level b. table-level c. page-level d. row-level

table-level

A(n) ____ lock defines how transactions acquire and relinquish locks. a. shared b. exclusive c. binary d. two-phase

two-phase

Changes are permanently applied to the database during the ____ phase of a transaction. a. commit b. write c. input d. output

write

Changes are permantently applied to the database during the _________________ phase of a transaction commit write input output

write

During the ____ phase, the changes are permanently applied to the database. a. read b. validation c. write d. shared

write

The ____ command is used to modify the table by deleting a column. a. DROP b. ALTER c. DELETE d. ERASE

DROP

For Optimizer Hints, ____ instructs the optimizer to minimize the overall execution time, that is, to minimize the time it takes to return all rows in the query result set. This hint is generally used for batch mode processes. a. ALL_ROWS b. FIRST_ROWS c. INDEX(name) d. OPTIMIZATION_ROWS

ALL_ROWS

At the implementation level, the supertype and its subtype(s) depicted in the specialization hierarchy maintain a ____________________ relationship - none - 1:1 - 1:M - M:N

1:1

What type of relationship is expressed with the phrase "Employee manages Store"? 1:M 1:1 M:1 M:N

1:1

What describes a package in PL/SQL? Organized SQL statements, DBMS, encryption algorithms, and keys The culmination of database schema, design, and log files A collection of procedures, functions, variables, and SQL statements A grouping of indexes, truples, and attributes

A collection of procedures, functions, variables, and SQL statements

What is the difference between a conceptual model and a logical model? A conceptual model is refined based on the network data model, whereas the logical model is based on a multidimensional model. A conceptual model assumes details of the underlying DBMS, whereas the logical model assumes details based on an abstraction. A conceptual model is independent of all implementation details, whereas the logical model is based on a specific database model. A conceptual model is dependent upon the programming language used to manage the data, whereas the logical model is dependent on implementation details.

A conceptual model is independent of all implementation details, whereas the logical model is based on a specific database model

The privilege ___________________ can be granted to change the table structure

ALTER

What is an example of the correct syntax for creating a new column in an existing table? ALTER TABLE person ADD email VARCHAR(200) NULL MODIFY TABLE person CREATE COLUMN email VARCHAR(200) NULL MODIFY TABLE person ADD email VARCHAR(200) NULL EDIT TABLE person ADD COLUMN email VARCHAR(200) NULL

ALTER TABLE person ADD email VARCHAR(200) NULL

The CREATE VIEW command includes the words CREATE VIEW followed by the name of the view, the word ______, and then a query

AS

The CREATE VIEW command includes the words CREATE VIEW, followed by the name of the view, the word _______________, and then a query AC SA AS IN

AS

A transaction is an indivisible unit that is either performed in its entirety or is not performed at all

Atomicity

____ requires that all operations of a transaction be completed. a. Specificity b. Atomicity c. Durability d. Time stamping

Atomicity

________________ requires that all operations of a transaction be completed Specificity Atomicity Durability Time stamping

Atomicity

________________ query optimization means that the DDBMS finds the most cost-effective access path without user intervention Static Dynamic Automatic Commit

Automatic

___________________ query optimization means that DDBMS finds the most cost effective access path without user intervention

Automatic

Which data type is considered a non-interpreted byte stream for a database management system (DBMS)? Float Character Bit BLOB XML

BLOB

Use the given DDL statement to answer the following question: CREATE ASSERTION StaffNotHandlingTooMuch CHECK (NOT EXISTS (SELECT staffNo FROM PropertyForRent GROUP BY staffNo HAVING COUNT(*) > 100)) Which action is this statement performing? Creating a trigger Creating a stored procedure Creating a constraint Creating an index

Creating a constraint

Use the given DDL statement to answer the following question: CREATE ASSERTION PhysicianNotOverbooked CHECK (NOT EXISTS (SELECT physician_number FROM scheduled_exams GROUP By physician_number HAVING COUNT (*) > 20)); Which action is this statement performing? Creating a trigger Creating a stored procedure Creating an index Creating a constraint

Creating a constraint

Which of the following is a rule that defines the data warehouse? a. Environments are integrated b. Data warehouse contains historical data over a long time horizon c. Data are object-oriented d. Data are subject-oriented

Data are subject-oriented

The ____ must be set large enough to permit as many data requests to be serviced from cache as possible. a. Data cache b. SQL cache c. Sort cache d. Optimizer mode

Data cache

_____________________ allows us to break a single object into two or more segments or fragments

Data fragmentation

____ data must conform to uniform structures and formats to avoid data conflicts. a. Data mart b. Database c. Data warehouse d. Data analysis

Data warehouse

What is the most restrictive lock granularity? a. Database b. Table c. Page d. Row

Database

How does dimensional modeling differ from entity-relationship modeling? Dimensional modeling is used to identify relationships among entities Dimensional modeling's major goal is to remove redundancy in data Dimensional modeling cannot efficiently and easily support ad-hoc queries Dimensional modeling can model common business situations

Dimensional modeling can model common business situations

Which of the following is NOT a basic component of a star schema? a. Attribute hierarchy c. Domain b. Fact d. Dimension

Domain

The effects of a successfully completed transaction are permanently recorded in the database

Durability

________________ query optimization takes place at execution time Automatic Static Manual Dynamic

Dynamic

__________________ query optimization takes place at execution time

Dynamic

What is the process of loading data extracted from different OLTP data sources into an enterprise data warehouse? EER Fragmentation Replication ETL

ETL

Which conceptual design step defines entries, attributes, and relationships?

Entity relationship modeling and normalization

Which of the conceptual design steps defines entries, attributes, and relationships? Database analysis and requirements Entity relationship modeling and normalization Data model verification Distributed database design

Entity relationship modeling and normalization

Use the given code to answer the following question: SELECT branchNo, COUNT(staffNo) AS myCount, SUM(salary) AS mySumFROM staffGROUP BY branchNoORDER BY branchNo; What does this code do? Finds the total number of branches Orders the staff table by the ascending order of branchNo Finds the number of staff and the sum of their salaries in each branch Orders the staff by ascending order of salary

Finds the number of staff an the sum of their salaries in each branch

Use the given code to answer the following question: SELECT branchNo, COUNT(staffNo) AS myCount, SUM (salary) AS mySum FROM staff, GROUP BY branchNo ORDER By branchNo; What does this code do? Orders the staff table by the ascending order of branchNo Orders the staff by ascending order of salary Finds the number of staff and the sum of their salaries in each branch Finds the total number of branches

Finds the number of staff and the sum of their salaries in each branch

Which aggregate functions can be applied to both numeric and nonnumeric fields? Choose 2 answers Finds the total number of branches Orders the staff table by the ascending order of branchNo Finds the number of staff and the sum of their salaries in each branch Orders the staff by ascending order of salary

Finds the number of staff and the sum of their salaries in each branch

The main mechanism for providing access to a database is the ______________ command PERMIT ACCESS GRANT REVOKE

GRANT

The main mechanism for providing access to a database is the _________________ command

GRANT

Which DCL Command gives Manager the privilege to SELECT on column AccountBalance of the table Customer? GRANT SELECT (Customer) ON AccountBalance TO Manager; GRANT SELECT (AccountBalance) ON Customer TO Manager; PERMIT Manager SELECT ON Customer (AccountBalance); ALLOW Manager SELECT AccountBalance FROM Customer;

GRANT SELECT (AccountBalance) ON Customer TO Manager;

Which of the following is a problem with a centralized database?

High cost

Which of the following is a problem with a centralized database? High cost Good performance from remote locations Poor performance from the main location Lack of storage

High cost

_________________ distributed database systems integrate only one type of centralized DBMS over a network Homogenous Heterogeneous Fully heterogeneous Fully homogenous

Homogenous

______________________ distributed database systems integrate only one type of centralized DBMS over a network

Homogenous

ANSI-standard SQL allows the use of special operators in conjunction with the WHERE clause. A special operator used to check whether an attribute value is null is ____. a. BETWEEN b. IS NULL c. LIKE d. IN

IS NULL

What is an advantage of using XML when presenting database management system (DBMS) data? It provides data redundancy It implements intrinsic data types It supports data integration It combines content and presentation

It supports data integration

What feature is a DDBMS disadvantage? Data is located near the "greatest demand" site Growth facilitation Reduced operating cost Lack of standards

Lack of standards

What feature is a DDBMS advantage? Greater difficulty in managing the data environment Less danger of a single-point failure Increased-training cost Complexity of management and control

Less danger of a single-point failure

Most DBMSs operate in one of two optimization modes: cost-based or rule-based. Others automatically determine the ____ based on whether database statistics are available. a. Data cache b. SQL cache c. Sort cache d. Optimization mode

Optimization mode

____ splits a table into subsets of rows or columns and places the subsets close to the client computer to improve data access time. a. Partitioning c. Aggregation b. Replication d. Denormalizing

Partitioning

______________________ splits a table into subsets of rows or columns and places the subsets close to the client computer to improve data access time

Partitioning

____ provides information about the timespan of the data stored in the table. a. Periodicity c. Replication b. Partitioning d. Aggregation

Periodicity

________________________ provides information about the timespan of the data stored in the table

Periodicity

What is the concurrency control technique of locking? Validation Pessimistic Optimistic Reading

Pessimistic

What feature is a DDBMS advantage? Reduced operating cost Complexity of management and control Increased storage requirements Increased training cost

Reduced operating cost

Which query will output the table contents when the value of V_CODE is less than or equal to 21344? a. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <> 21344; b. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <=21344; c. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE => 21344; d. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344;

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <=21344;

What feature is a DDBMS disadvantage? Processor independence User-friendly interface Security Faster data access

Security

Which step would be seen in the conceptual phase of data model design? Specification of entities and relationships Specification of trigger definitions Specification of storage size Specification of programming language to use

Specification of entities and relationships

______________________________ contains the description of the entire database as seen by the database administrator

The distributed database dictionary

But, in a subquery, which query is executed first?

The innermost query

Which of the following is a benefit of using a DBMS? - They provide full security to data using private/public key encryption - They create automatic backups - They help create an environment for end users to have access to more data - They provide seamless Internet access to database data

They provide full security using private/public key encryption

Which of the following rules applies to the two-phase locking protocol? a. Two transactions cannot have conflicting locks. b. No unlock operation can precede a lock operation in a different transaction. c. No data is affected until all locks are released. d. No data is affected until the transaction is in its locked position.

Two transactions cannot have conflicting locks

The SQL command that enables you to make changes in the data is ____. a. INSERT b. SELECT c. COMMIT d. UPDATE

UPDATE

The privilege ______________ can be granted to change data WRITE UPDATE ALTER READ AND WRITE

UPDATE

Which command would you use when making corrections to the PRODUCT table? a. CHANGE PRODUCT SET P_INDATE = '01/18/2004' WHERE P_CODE = '13-Q2/P2'; b. ROLLBACK PRODUCT SET P_INDATE = '01/18/2004' WHERE P_CODE = '13-Q2/P2'; c. EDIT PRODUCT SET P_INDATE = '01/18/2004' WHERE P_CODE = '13-Q2/P2'; d. UPDATE PRODUCT SET P_INDATE = '01/18/2004' WHERE P_CODE = '13-Q2/P2';

UPDATE PRODUCT SET P_INDATE = '01/18/2004' WHERE P_CODE = '13-Q2/P2';

If you wish to perform an inner join based on only one common attribute, you might use the ____ clause. a. OF b. USING c. HAS d. JOIN ON

USING

The entity integrity rule requires that

all primary keys are unique

Because an RDBMS hides the system's complexity from the user/designer, ____________________ - it does not exhibit data independence - it does not exhibit structural independence - data management is easier - data management is very difficult

data management is easier

One measure that determines the need for an index is the ____ of the column you want to index. ____ refers to the number of different values a column could possibly have. a. database Statistics b. data sparsity c. primary key d. query optimization

data sparsity

The DSS database is called the ____. a. OLAP c. DBMS b. data store d. RDSS

data store

A database is composed of several parts known as a database ________________ sections fragments partitions parts

fragments

When you read from and/or write to a database, you have created a(n) ____. a. transaction b. back up c. update d. queue

transaction

The number of affected records is not stored in the ______________________

transaction log

A ________________ is a uniquely identifiable object that allows a given person to log into the new database

user

What is a reason for creating a data mart versus a data warehouse? A data mart provides subject-oriented and time-variant data. A data mart improves end-user response time. A data mart stores infrequently analyzed data. A data mart provides a superset of data.

A data mart improves end-user response time

What is teh difference between a transaction with a shared lock and a transaction with an exclusive lock? A shared lock cannot access the item. A shared lock can read the item. A shared lock can delete the item. A shared lock can update the item.

A shared lock can read the item

What is true of a starflake schema? A starflake schema has multiple fact tables. A starflake schema must use denormalized dimension tables and natural keys. A starflake schema uses a network of hierarchical fact tables. A starflake schema uses both normalized and denormalized dimension tables.

A starflake schema uses both normalized and denormalized dimension tables

A consistent database is one where all data integrity constraints are satisfied

A transaction acquires a lock prior to data access

For optimizer hints, ________________________ instructs the optimizer to the overall execution time, that is, the minimize the time it takes to return all the rows in the query result set

ALL_ROWS

The privilege _____________ can be granted to change the table structure WRITE UPDATE ALTER MODIFY

ALTER

The ANSI has defined standards that govern SQL database transactions. Transaction support is provided by two SQL statements: ____ and ROLLBACK. a. RETRIEVE b. ASSIGN c. UPDATE d. COMMIT

COMMIT

The basic SQL aggregate function that gives the number of rows containing not null values for the given column is ____. a. COUNT b. MIN c. MAX d. SUM

COUNT

_______ is used to tally the number of specific "values" of an attribute SUM TOTAL COUNT ADD

COUNT

Which statement produces a PL/SQL stored procedure? INSERT OR SET PROCEDURE ProcessReport AS BEGIN SELECT * FROM sales END CREATE OR REPLACE PROCEDURE ProcessReport AS BEGIN SELECT * FROM sales END ADD OR UPDATE PROCEDURE ProcessReport AS BEGIN SELECT * FROM sales END INSERT OR UPDATE PROCEDURE ProcessReport AS BEGIN SELECT * FROM sales END

CREATE OR REPLACE PROCEDURE ProcessReport AS BEGIN SELECT * FROM sales END

What statement would produce a PL/SQL stored procedure?

CREATE OR REPLACE PROCEDURE ProcessReport AS BEGIN SELECT * FROM sales END

Which statement produces a PL/SQL stored procedure? ADD OR UPDATE PROCEDURE dataAbstraction AS BEGIN SELECT * FROM employees END CREATE OR REPLACE PROCEDURE dataAbstraction AS BEGIN SELECT * FROM employees END INSERT OR UPDATE PROCEDURE dataAbstraction AS BEGIN SELECT * FROM employees END INSERT OR SET PROCEDURE dataAbstraction AS BEGIN SELECT * FROM employees END

CREATE OR REPLACE PROCEDURE dataAbstraction as BEGIN SELECT * FROM employees END

To create a stored procedure in Oracle, use the _____________ command CREATE ROUTINE CREATE SCRIPT CREATE PROCEDURE CREATE EXECUTABLE

CREATE PROCEDURE

To create a stored procedure in Oracle, use the ________________________________ command.

CREATE PROCEDURE

Which DDL statement should be used to enforce referential integrity? CREATE TRIGGER AFTER GRANT CREATE TRIGGER BEFORE UPDATE CREATE TRIGGER AFTER SELECT CREATE TRIGGER BEFORE DROP

CREATE TRIGGER BEFORE UPDATE

Which DDL statement should be used to reinforce referential integrity? CREATE TRIGGER AFTER GRANT CREATE TRIGGER BEFORE UPDATE CREATE TRIGGER BEFORE DROP CREATE TRIGGER AFTER SELECT

CREATE TRIGGER BEFORE UPDATE

Which DDL statement creates an index that could be used as a candidate primary key? CREATE VIEW INDEX CREATE UNIQUE INDEX CREATE PRIMARY INDEX CREATE DROP INDEX

CREATE UNIQUE INDEX

Which concept does the SQL statement "SELECT * FROM Contacts, Employer" demonstrate? Difference Cartesian product Selection Intersection

Cartesian product

Use the given SQL to answer the following question: SELECT staff_id, first_name, last_name, position, salary FROM Staff WHERE salary > 4000 AND position = 'Supervisor' What are the search conditions used by this statement? Choose 2 answers Range Comparison Pattern Match Set membership

Comparison Pattern Match

____ control is the management of concurrent transaction execution. a. Concurrency b. Lock c. Transaction d. Database

Concurrency

________________ backups take place while the user is working on the database Full Incremental Concurrent Sequential

Concurrent

________________________ backups take place while the user is working on the database

Concurrent

What are the strengths of database management systems (DBMS)? Size of software Cost of DBMS Control of data redundancy Increased performance Improved data integrity

Control of data redundancy Improved data integrity

Which security strategy should be used for creating roles in a database? Creating groupings of users with different data access privileges Creating groupings of privileges for a specific user in the database Creating groupings of users with the same data access privileges Creating groupings of privileges by database transactions

Creating groupings of users with the same data access privileges

Which type of data will be found by utilizing data mining? Customer sales reports Customer refund reports Customer address lists Customer loyalty identification

Customer loyalty identification

All transactions are controlled and executed by the ____ to guarantee database integrity. a. scheduler b. DBMS c. transaction log d. DBA

DBMS

What are the methods for replicating data to mobile environments? Choose 2 :D DBMS installed in the mobile device Store a subset of data in the mobile device Store logs to dynamically generate data on a mobile device Mobile device using a wired connection Mobile device acting as a master replica

DBMS installed in the mobile device Store a subset of data in the mobile device

The correct command to allow user Thompson to delete rows in the CUSTOMER table would be: GRANT _______________ ON CUSTOMER TO THOMPSON DROP REMOVE UPDATE DELETE

DELETE

To delete a row from the PRODUCT table, use the ____ command. a. KILL b. DELETE c. COMMIT d. ERASE

DELETE

Which command would be used to delete the table row where the P_Code = '2238/QPD'? - DELETE FROM PRODUCT WHERE P_CODE = '2238/QPD' - REMOVE FROM PRODUCT WHERE P_CODE = '2238/QPD' - ERASE FROM PRODUCT WHERE P_CODE = '2238/QPD'

DELETE FROM PRODUCT WHERE P_CODE = '2238/QPD' *** Wow this is really easy

Which statement will delete the staff member who is a manager with the staff ID "SG15"? DELETE FROM staff WHERE position='Manager', staffNo='SG15' DELETE WHERE position=Manager AND staffNo=SG15 DELETE FROM staff WHERE position='Manager' AND staffNo='SG15' DELETE WHERE position='Manager' AND staffNo='SG15'

DELETE FROM staff WHERE position='Manager' AND staffNo='SG15'

___________ fragmentation allows us to break a single object into two or more segments or fragments Horizontal Vertical Data Mixed

Data

The _____________________, must be set large enough to permit as many data requests to be serviced from cache as possible

Data Cache

What are the high-level components that compose a DSS? a. Data store, data extraction, and data filtering b. End user query tool and end user presentation tool c. Data store, data extraction and data filtering, end user query tool and end user presentation tool d. Data store, and end user query tool and end user presentation tool

Data store, data extraction and data filtering, end user query tool and end user presentation tool

____ data must conform to uniform structures and formats to avoid data conflicts. a. Data mart c. Data warehouse b. Database d. Data analysis

Data warehouse

Which factor must be considered during physical database design? Determination of the specific DBMS vendor product to use Specification of table definitions Specification of entities and relationships between entities Determination of the type of DBMS to be used

Determination of specific DBMS vendor product to use

What is a common component of encoding data using asymmetric encryption? Same encryption and decryption keys Uni-directional encryption Different encryption and decryption keys Identical encryption and decryption algorithms

Different encryption and decryption keys

Which model represents the end user's view of the database?

External

Which model represents the end user's view of the database? Internal Conceptual Physical External

External

For Optimizer Hints, ____ instructs the optimizer to minimize the time it takes to process the first set of rows, that is, to minimize the time it takes to return only the first set of rows in the query result set. This hint is generally used for interactive mode processes. a. ALL_ROWS b. FIRST_ROWS c. INDEX(name) d. OPTIMIZATION_ROWS

FIRST_ROWS

____ are numeric measurements or values that represent a specific business aspect or activity. a. Tables c. Facts b. Schemas d. Attributes

Facts

__________________ transparency ensures that the system will continue to operate in the event of a node failure Transaction Distribution Failure Performance

Failure

________________________ ensures that the system will continue to operate in the event of a node failure

Failure transparency

Which lock granularity has the highest overhead? a. Database b. Table c. Row d. Field

Field

Which DCL command gives Director the privilege to SELECT on column Salary of the table Staff? ALLOW Director SELECT Salary FROM Staff; GRANT SELECT(Salary) ON Staff TO Director; GRANT SELECT(Staff) ON Salary TO Director; PERMIT Director SELECT ON Staff(Salary);

GRANT SELECT(Salary) ON Staff TO Director

____ transparency allows the integration of several different local DBMSs under a common, or global, schema. a. Transaction b. Performance c. Distribution d. Heterogeneity

Heterogeneity

_______________ distributed database systems integrate different types of centralized DBMSs over a network Homogenous Heterogeneous Fully homogeneous Fully heterogeneous

Heterogeneous

________________________ transparency allows the integration of several different local DBMSs under a common, or global, schema

Heterogeneous

Which factors should be considered in designing a backup plan? Choose two answers How often to test by restoring backup data How to process application logic Where to store backup data Where to run query optimization How to combine relations during backup to save space

How often to test by restoring backup data Where to store backup data

____ means that data used during the execution of a transaction cannot be used by a second transaction until the first one is completed. a. Serializability b. Atomicity c. Isolation d. Time stamping

Isolation

Use the given DDL to answer the following question: CREATE VIEW Manager3StaffAS SELECT *FROM StaffWHERE branch_number = 3WITH CHECK OPTION; What does this code do? It provides access to the entire Staff table It restricts the visible columns in the Staff table It creates a view that prohibits migrating rows It prevents insertions into Staff with branch_number = 3

It creates a view that prohibits migrating rows

What is a benefit of distributed database management systems (DDBMS)? It reduces the skills required of the database administrator. It removes the requirement for global applications. It stops fragments from replicating. It helps resolve the islands of information problem.

It helps resolve the islands of information problem

____ occur when a transaction calculates some summary (aggregate) functions over a set of data while other transactions are updating the data. a. Lost updates b. Uncommitted data c. Transaction failures d. Inconsistent retrievals

Inconsistent retrievals

____________________ occur when a transaction calculates some summary (aggregate) function over a set of data while other transactions are updating the data Lost updates Uncommitted data Transaction failures Inconsistent retrievals

Inconsistent retrievals

In a subquery, which query is executed first? Leftmost Rightmost Innermost Outermost

Innermost

In a subquery, which query is executed first? a. Leftmost b. Rightmost c. Innermost d. Outermost

Innermost

Use the given statement to answer the following question: INSERT INTO Production Values('Item123', 'Round'); Which function is performed in this statement? Inserting a table into the database Inserting a single row of data Inserting data into random columns Inserting multiple rows of data

Inserting a single row of data

Transactions execute independently of one another

Isolation

____ makes a copy of a table and places it in a different location, to improve access time. a. Periodicity c. Partitioning b. Aggregation d. Replication

Replication

________________ makes a copy of a table and places it in a different location, to improve access time. Periodicity Aggregation Partitioning Replication

Replication

Which criterion is used to conduct a usability evaluation as part of database system testing? Administration Robustness Redundancy Concurrency

Robustness ***haha this sounds so silly

A transaction is committed before a failure occurs that prevents buffers from writing to secondary storage Which action should be taken to ensure durability of this transaction? Roll forward Roll back Partial undo Flush

Roll forward

The privilege ____________ can be granted to retrieve data SELECT UPDATE RETRIEVE READ

SELECT

The privilege _________________ can be granted to retrieve data

SELECT

Use the given statement to answer the following question: CREATE VIEW CarSales(dealerNo, salesNo, sales)AS SELECT s.dealerID, s.salesID, COUNT(*)FROM salesman s, monthlySales m WHERE s.salesID = m.salesID GROUP BY s.dealerID, s.salesID Which queries will properly run? Choose 2 answers SELECT * FROM CarSales WHERE dealerNo > 10 SELECT * FROM CarSales GROUP BY sales SELECT * FROM CarSales WHERE COUNT(sales) > 5 SELECT * FROM CarSales WHERE salesNo > 3 SELECT COUNT(sales), dealerNo FROM CarSales

SELECT * FROM CarSales WHERE dealerNo > 10 and SELECT * FROM CarSales WHERE salesNo > 3

To list all the contents of the PRODUCT table, you would use ____. a. LIST * FROM PRODUCT; b. SELECT * FROM PRODUCT; c. DISPLAY * FROM PRODUCT; d. SELECT ALL FROM PRODUCT;

SELECT * FROM PRODUCT;

Which SQL statement will select all the data from the branch table, whether or not there are entries in the employee table? SELECT * FROM branch LEFT JOIN employee ON branch.location = employee.location SELECT * FROM employee LEFT JOIN branch ON employee.location = branch.location SELECT * FROM branch INNER JOIN employee ON branch.location = employee.location SELECT * FROM employee INNER JOIN branch ON employee.location = branch.location

SELECT * FROM branch LEFT JOIN employee ON branch.location = employee.location

Which command is used to select partial table contents? a. SELECT <column(s)> FROM <Table name> WHERE <Item>; b. LIST <column(s)> FROM <Table name> WHERE <Conditions>; c. SELECT <column(s)> FROM <Table name> WHERE <Conditions>; d. LIST<column(s)> FROM <Table name> WHERE <Item>;

SELECT <column(s)> FROM <Table name> WHERE <Conditions>;

Which command is used to select partial table contents? SELECT <column(s)> FROM <Table name> WHERE <Item>; LIST <column(s)> FROM <Table name> WHERE <Conditions>; SELECT <column(s)> FROM <Table name> WHERE <Conditions>; LIST <column(s)> FROM <Table name> WHERE <Item>;

SELECT <column(s)> FROM <Table name> WHERE <Conditions>; ** super super easy :D

What is the command used to list the P_CODE, P_DESCRIPT, P_INDATE, and P_PRICE fields from the Product table in ascending order by P_PRICE? a. SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT SEQUENCE BY P_PRICE; b. SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT LIST BY P_PRICE; c. SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT ORDER BY P_PRICE; d. SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT ASCENDING BY P_PRICE;

SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT ORDER BY P_PRICE;

What is the command used to list the P_CODE, P_DESCRIPT, P_INDATE, and P_PRICE fields from the Product table in ascending order by P_PRICE? a. SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT SEQUENCE BY P_PRICE; b. SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT LIST BY P_PRICE; c. SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT ORDER BY P_PRICE; d. SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT ASCENDING BY P_PRICE;

SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT ORDER BY P_PRICE;

What is the SQL syntax requirement to list the table contents for either V_CODE = 21344 or V_CODE = 24288? a. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE <= 24288 b. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE => 24288 c. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE > 24288 d. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE = 24288

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE = 24288

Which query will output the table contents when the value of V_CODE is equal to 21344? a. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <> 21344; b. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <= 21344; c. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344; d. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE => 21344;

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344;

What is the command to join the P_DESCRIPT and P_PRICE fields from the PRODUCT table and the V_NAME, V_AREACODE, V_PHONE, and V_CONTACT fields from the VENDOR table where the value of V_CODE match? a. SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE <> VENDOR.V_CODE; b. SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE; c. SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE <= VENDOR.V_CODE; d. SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE => VENDOR.V_CODE;

SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE;

What is the command to join the P_DESCRIPT and P_PRICE fields from the PRODUCT table and the V_NAME, V_AREACODE, V_PHONE and V_CONTACT fields from the VENDOR table, where the value of V_CODE match and the output is ordered by the Price? a. SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE <> VENDOR.V_CODE; ORDER BY P_PRICE; b. SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE => VENDOR.V_CODE; ORDER BY P_PRICE; c. SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE <= VENDOR.V_CODE; ORDER BY P_PRICE; d. SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE; ORDER BY P_PRICE;

SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE; ORDER BY P_PRICE;

What is the syntax for a left outer join? a. SELECT column-list FROM table1 OUTER JOIN table2 LEFT WHERE join-condition b. SELECT column-list FROM table1 LEFT [OUTER] JOIN table2 ON join-condition c. SELECT column-list WHERE LEFT table1 = table 2 d. SELECT column-list FROM table1 LEFT table2 [JOIN] WHERE join-condition

SELECT column-list FROM table1 LEFT [OUTER] JOIN table2 ON join-condition

Syntax for a left outer join?

SELECT column-list from table1 LEFT [OUTER] JOIN table2 ON join-condition

Use the given query results from the following table to answer the question below: staffNofNamelNamesalary SL21JohnWhite30000.00 SG5SusanBrand24000.00 SG14DavidFord18000.00 SG37AnnBeech12000.00 SA9MaryHowe 9000.00 SL41JulieLee 9000.00 Which query should be executed to produce that results as displayed in the table? SELECT staffNo, fName, lName, salary FROM Staff ORDER BY 4 ASC SELECT staffNo, fName, lName, salary FROM Staff ORDER BY fName DESC SELECT staffNo, fName, lName, salary FROM Staff ORDER BY salary DESC SELECT staffNo, fName, lName, salary FROM Staff ORDER BY salary ASC

SELECT staffNo, fName, salary FROM Staff ORDER BY salary DESC

UPDATE tablename ***** [WHERE conditionlist] What command replaces the ******* in the above statement? SET columnname = expression columnname = expression expression = columnname LET columnname = expression

SET columnname = expression

UPDATE tablename ***** [WHERE conditionlist]; What command replaces the ***** in the above statement? a. SET columnname = expression b. columnname = expression c. expression = columnname d. LET columnname = expression

SET columnname = expression

The _____________________ stores the most recently executed SQL statements (after the SQL statements have been parsed by the optimizer)

SQL Cache

The ____ stores the most recently executed SQL statements (after the SQL statements have been parsed by the optimizer). a. Data cache b. SQL cache c. Sort cache d. Optimizer mode

SQL cache

What is the benefit of using SQL? SQL is a standard used to define user interfaces. SQL is a standard used for data interchange between enterprise systems. SQL is recognized by all operating systems. SQL is the strategic choice of many large and influential organizations.

SQL is the strategic choice of many large and influential organizations

The basic SQL aggregate function that gives the total of all values for a selected attribute in a given column is __________________ COUNT MIN MAX SUM

SUM

What is the term used to describe operations in transactions performed in order, without interleaved operations from other transactions? Nonserial schedule Exclusive schedule Serial schedule Sanitized schedule

Serial schedule

Which analytical processing (OLAP) operations can be used for driving business decisions on data cubes? Slicing Equijoining Pivoting Cascading Validating

Slicing Pivoting

A DBA needs to implement a data warehouse where avoiding redundant data is preferred over query performance What is the appropriate multidimensional data model that should be chosen for the database design? Starflake schema Network schema Star schema Snowflake schema

Snowflake schema

The ____________________ is used as a temporary storage area for ORDER BY or GROUP BY operations, as well as index-creation functions

Sort Cache

The ____ is used as a temporary storage area for ORDER BY or GROUP BY operations, as well as for index-creation functions. a. Data cache b. SQL cache c. Sort cache d. Optimizer mode

Sort cache

Which of the following is NOT stored in the transaction log? a. The type of operation performed b. A record for the beginning of a transaction c. The name of the table d. The number of affected records

The number of affected records

What is the primary objective of database design? To create the most efficient database possible To create complete, normalized, nonredundant, and fully integrated conceptual, logical, and physical database models To create the fastest queries To be able to add data quickly

To create complete, normalized, nonredundant, and fully integrated conceptual, logical, and physical database models

________________ transparency allows a transaction to update data at several network sites Transaction Distribution Failure Performance

Transaction

_______________________ allows a transaction to update data at several network sites

Transaction transparency

Which statement describes timestamping? Transactions with newer dates get priority over older dates. Locking is used to achieve serialization and to prevent conflicts. Timeouts used on lock requests wait as a method of deadlock prevention. Transactions involved in a conflict can be rolled back and restarted

Transactions involved in a conflict can be rolled back and restarted

Which statement must be used to increment staff salary by 4%? UPDATE staff SET salary = salary * 1.04 UPDATE staff SET salary = salary * 4% UPDATE staff SET salary = salary * 0.04 UPDATE staff SET salary = salary * 104

UPDATE staff SET salary = salary * 1.04

If you wish to perform an inner join based on only one common attribute, you might use the __________________ clause.

USING

Classifying users into ____, according to common access needs, facilitates the DBA's job of controlling and managing the access privileges of individual users. a. user groups b. sections c. authorization schemes d. policy definitions

User groups

Which phase is part of optimistic concurrency control protocol for read-only transactions? Shrinking Write Growing Validation

Validation

You can join tables by using a candidate in the __________________ clause

WHERE

You can join tables by using a condition in the _______________ clause FROM WHERE SELECT JOIN

WHERE

When is the implicit beginning of a transaction? When the database is started When a table is accessed for the first time When the first SQL statement is encountered When the COMMIT command is issued

When the first SQL statement is encountered

When is the implicit beginning of a transaction? a. When the database is started b. When a table is accessed for the first time c. When the first SQL statement is encountered d. When the COMMIT command is issued

When the first SQL statement is encountered

Which algorithms proposed by Rosenkrantz et al. are used for deadlock prevention? Choose 2 answers Wound-wait Wait-die Conservative 2PL Indefinite wait Wait-for graph

Wound-wait Wait-die

Does OLAP support client/server architecture?

Yes

If there is no index, the DBMS will perform ____. a. a loop scan b. a range scan c. a row ID table access scan d. a full table scan

a full table scan

If there is no index, the DBMS will perform _____________________________

a full table scan

The entity integrity rule requires that __________________ all primary keys are unique a part of the key must be null foreign key values do not reference primary key values duplicate object values are allowed

all primary keys are unique

row-level lock

allows concurrent transactions to access different rows of the same table

During the data ____ phase of data mining, common characteristics or patterns are identified. a. analysis c. extraction b. preparation d. acquisition

analysis

During the data ________________ phase of data mining, common characteristics or patterns are identified analysis preparation extraction acquisition

analysis

During the data _________________ phase of data mining, common characteristics or patterns are identified

analysis

Many of the OLAP interface features are "borrowed" from previous generations of data ____ tools. a. module c. filtering b. processing d. analysis

analysis

Many of the OLAP interface features of "previous" generations of data _______________ tools

analysis

The end user GUI might be a custom-made program or a plug-in module that is integrated with Lotus 123, Microsoft Excel, or other third party data ____ and query tools. a. analysis c. processing b. base d. integration

analysis

The DSS tools that use multidimensional data analysis techniques are known as online ____ processing. a. analysis c. statistical b. analytical d. computational

analytical

A complete data warehouse ____ includes support for a DSS data store, a data extraction and integration filter, and a specialized presentation interface. a. database c. file b. cycle d. architecture

architecture

Client/server ____ provides a framework within which new systems can be designed, developed, and implemented. a. interface c. processing b. architecture d. support

architecture

When the OLAP GUI runs on the client workstations, and the OLAP engine runs on a shared computer, the ____ is more practical than running both on the same computer. a. interface c. architecture b. processing d. support

architecture

All transaction properties must display ____. a. atomicity, serializability, and durability b. durability and isolation c. serializability, durability, and isolation d. atomicity, durability, serializability, and isolation

atomicity, durability, serializability, and isolation

All transaction properties must display:

atomicity, durability, serializability, and isolation

The ____ hierarchy provides a top-down data organization that is used for two main purposes: aggregation and drill-down/roll-up data analysis. a. relational c. multidimensional b. dimensional d. attribute

attribute

The _____________ hierarchy provides a top-down data organization that is used for two main purposes: aggregation and drill-down/roll-up data analysis relational dimensional multidimensional attribute

attribute

The ________________ hierarchy provides a top-down data organization that is used for two main purposes: aggregation and drill-down/roll-up data

attribute

User-access management is a subset of __________________ password protection authorization management data integrity managerial control

authorization management

User-access management is a subset of _____________________________

authorization management

A ____ lock has only two states. a. shared b. binary c. database d. row-level

binary

A ____________ relationship exists when two entities are associated unary binary ternary weak

binary

A ___________________ relationship exists when two entities are associated

binary

A(n) ____ lock has only two stages (0 & 1). a. shared b. exclusive c. binary d. two-phase

binary

The data cache or _______________________ is a shared, reserved memory area that stores the most recently accessed data blocks in RAM

buffer cache

A transaction that changes the contents of the database must alter the database from one ____ state to another. a. consistent b. dependent c. independent d. inconsistent

consistent

As a manager, the DBA must be concentrated on the ________________________________ dimension of the database administration funciton

control and planning

As a manger, the DBA must concentrate on the ____________ dimension of the database administration function control and planning conflict and problem-resolution policies and standards security and privacy

control and planning

Most DBMSs operation in one of two optimization modes: ______________________ or _________________________. Other automatically determine the optimization mode based on whether database statistics are available

cost-based rule-based

What are parts of the database design phase?

creating the conceptual design selecting the DBMS software creating the logical design creating the physical design

The ability to capture the data ____ in memory provides faster response times, but it also makes the MDBMS more resource-intensive than its relational counterpart. a. base c. array b. cube d. cache

cube

Changes are permanently applied to a database during the _________________ phase of a transaction

write

Data warehouse data must conform to uniform structures and formats to avoid _____________________

data conflicts

Conceptually, MDBMS end users visualize the stored data as a three-dimensional cube known as a(n) ____. a. analytical cube c. data cube b. hypercube d. sparse cube

data cube

MOLAP extends OLAP functionality to multidimensional ____ management systems. a. online c. processing b. database d. data

database

Creating the conceptual design, selecting DBMS software, creating the logical design, and creating the physical design are all part of the _____________ phase database and initial study database design implementation and loading testing and evaluation

database design

A ____ lock prevents the use of any tables in the database from one transaction while another transaction is being processed. a. database-level b. table-level c. page-level d. row-level

database-level

A(n) ____ condition occurs when two transactions wait for each other to unlock data. a. deadlock b. exclusive lock c. binary lock d. two-phase lock

deadlock

two-phase lock

defines how transactions acquire and relinquish locks. Two transactions cannot have conflicting locks

The key's role is based on a concept known as _________________ consistency availability determination uniqueness

determination

____ are qualifying characteristics that provide additional perspectives to a given fact. a. Views c. Dimensions b. Attributes d. Fact Tables

dimensions

A diskpage, or a page, is equivalent to a ____________________

diskblock

A diskpage, or page, is the equivalent of a ____. a. database table b. disk sector c. database schema d. diskblock

diskblock

The ______________ contains the description of the entire database as seen by the database administrator distributed global dictionary distributed data dictionary distributed global schema distributed data schema

distributed data dictionary

The ________________ contains the description of the entire database as seen by the database administrator distributed global dictionary distributed data dictionary distributed global schema distributed data schema

distributed data dictionary

The extended entity relationship model (EERM) is sometimes referred to as the ____________. entity relationship model enhanced entity relationship model entity clustering relationship model extended entity relationship diagram

enhanced entity relationship model

Data cubes can grow to n-number of dimensions, thus becoming ____. a. memory cubes c. hypercubes b. dimensional cubes d. star data cubes

hypercubes

The extended entity relationship model (EERM) is sometimes referred to as the ____________________ entity relationship model enhanced relationship model entity clustering relationship model extended entity relationship diagram

enhanced relationship model

isolation

ensures that two transactions do not update the same data element at the same time

The referential integrity rule requires that

every non-null foreign key value reference an existing primary key value

The referential integrity rule requires that _________________ every null foreign key value must reference an existing primary key value an attribute have a corresponding value every non-null foreign key value reference an existing primary key value you delete a row in one table whose primary key does not have a matching foreign key value in another table

every non-null foreign key value reference an existing primary key value

A(n) ____ specifically reserves access to the transaction that locked the object. a. shared lock b. exclusive lock c. binary lock d. deadlock

exclusive lock

common lock

exists when concurrent transactions are granted read access on the basis of a common lock

A database is composed of several points known as database ____________________

fragments

In a(n) _______________ join, all rows from both tables are included regardless of whether they match rows from the other table normal inner full outer combined

full outer

Lock ____ indicates the level of lock use. a. granularity b. shrinking c. growing d. serializability

granularity

binary lock

has only two stages (0 & 1)

The property of ______________ enables an entity subtype to inherit the attributes and relationships of the supertype - subtype discriminator - inheritance - specialization hierarchy - entity supertype

inheritance

During the ____ phase of data warehouse implementation, the designer must identify and interview key users, define main subjects, and identify operational data models among other tasks. a. initial data gathering c. loading and testing b. design and mapping d. rollout and feedback

initial data gathering

Which of the joins compares the tables in the FROM clause and lists only those rows that satisfy the condition in the WHERE clause?

inner

The components of a data warehouse are ____. a. integrated c. object-oriented b. volatile d. decentralized

integrated

You can define ____ to help the DBMS ensure that the database remains in a consistent state. a. integrity constraints b. business rules c. lock-level transactions d. stored procedures

integrity constraints

You can define _______________________ to help the DBMS ensure that the database remains in a consistent state

integrity-constraints

In a ________________, all rows from the table on the left (the table listed first in the query) will be included regardless of whether they match rows from the table on the right (the table listed second in the query) left inner join left outer join right inner join right outer join

left outer join

A transaction acquires a ____ prior to data access. a. grain b. timestamp c. lock d. key

lock

A transaction acquires a _______________ prior to data access grain timestamp lock key

lock

A transaction requires a ______________ prior to data access

lock

The ____ manager is responsible for assigning and policing the locks used by the transactions. a. transaction b. database c. lock d. schedule

lock

The ____ is responsible for assigning and policing the locks used by the transactions. a. lock manager b. scheduler c. DBA d. transaction log

lock manager

The _____________________ is responsible for assigning and policing the locks used by the transactions

lock manager

The most common algorithms for concurrency control are:

locks time stamping optimistic methods

page-level lock

locks the entire diskpage

table-level lock

locks the entire table preventing access to any row by a transaction while another transaction is using the table

A transaction is a ____ unit of work that must be either entirely completed or aborted. a. timed b. practical c. logical d. physical

logical

A specialization hierarchy can have ____________ levels of supertype/subtype relationships

many

Using multidimensional jargon the ability to focus on slices of the cube to perform a more detailed analysis is known as ___________________________

microscoping

Using multidimensional jargon, the ability to focus on slices of the cube to perform a more detailed analysis is known as ____. a. star cuts c. microscoping b. slice and dice d. TPL processing

microscoping

MOLAP's premise is that ____ databases are best suited to manage, store, and analyze multidimensional data. a. multidimensional c. two-dimensional b. one-dimensional d. relational

multidimensional

The _____________ scenario describes a fully distributed DBMS with support for multiple data processors and transaction processors at multiple sites multiple-site processing, single-site data single-site processing, multiple-site data single-site processing, single-site data multiple-site processing, multiple-site data

multiple-site processing, multiple-site data

Under the ____________ scenario, multiple processes run on different computers sharing a single data repository single-site processing, single-site data multiple-site processing, single-site data single-site processing, multiple-site data multiple-site processing, multiple-site data

multiple-site processing, single-site data

Under the _________________________________________ scenario, multiple processes run on different computers sharing a single data repository

multiple-site processing, single-site data

The ____ rule requires that all copies of data fragments be identical. a. shared fragment b. mutual consistency c. horizontal fragmentation d. replication

mutual consistency

The ______________________ rule requires that all copies of data fragments be identical

mutual consistency

Every subtype can have ________________ supertype to which it is directly related none only one one or many many

only one

In subquery terminology, the first query in the SQL statement is known as the ________________ query. outer left inner base

outer

In subquery terminology, the first query in the SQL statement is known as the _________________ query

outer

A ____ lock locks the entire diskpage. a. transaction-level b. table-level c. page-level d. row-level

page-level

When you specify a foreign key, the table referenced by the foreign key is the _______________ super sibling parent child

parent

With ____ data allocation, the database is divided into several disjointed parts and stored at several sites. a. centralized b. decentralized c. partitioned d. replicated

partitioned

With _____________________ data allocation, the database is divided into several disjointed parts and stored at several sites

partitioned

During the data ____ phase of data mining, the main data sets to be used by the data- mining operation are identified and cleansed of any data impurities. a. analysis c. classification b. preparation d. acquisition

preparation

During the data _____________________ phase of data mining the main data sets to be used by the data mining operation are identified and cleansed of any data impurities

preparation

database level lock

prevents the use of any tables in the database from one transaction, while another transaction is being processed

One important inheritance characteristic is that all entity subtypes inherit their ____ attribute from their supertype. primary key natural key foreign key Surrogate key

primary key

In contrast to the traditional DSS tools, data mining is ____. a. proactive c. automatic b. protective d. costly

proactive

In contrast to the traditional DSS tools, data mining is ___________________

proactive

During the ____ phase of data mining, the data-mining findings are used to predict future behavior and forecast business outcomes. a. analysis and classification c. prognosis b. preparation d. knowledge acquisition

prognosis

During the ______________ phase of data mining, the data-mining findings are used to predict future behavior and forecast business outcomes analysis and classification preparation prognosis knowledge acquisition

prognosis

During the __________________ phase of data mining, the data-mining findings are used to predict future behavior and forecast business outcomes

prognosis

At the level of top management, the database must be able to ___________________ represent and support the company operations as closely as possible deliver the data necessary for tactical decisions and planning produce query results within specified performance levels provide access to external and internal data to identify growth opportunities and to chart the direction of such growth

provide access to external and internal data to identify growth opportunities and to chart the direction of such growth

____ is the central activity during the parsing phase in query processing. a. database Statistics b. data sparsity c. SQL query d. query optimization

query optimization

_________________________ is the central activity during the parsing phase in query processing

query optimization

Since the data cube is predefined with a set number of dimensions, the addition of a new dimension requires that the entire data cube be ____. a. modified c. deleted b. updated d. re-created

re-created

Since the data cube is predefined with a set number of dimensions, the addition of a new dimension requires that the entire data cube be ________________

re-created

During the ____ phase, the transaction scans the database, executes the needed computations, and makes the updates to a private copy of the database values. a. read b. validation c. write d. shared

read

Data _______________ refers to the storage of data copies at multiple sites served by a computer network replication fragmentation persistence optimization

replication

Data ____________________ refers to the storage of data copies at multiple data sites served by a computer network

replication

In a ________________, all rows from the table on the right will be included regardless of whether they match the rows from the table on the left. left inner join left outer join right outer join right inner join

right outer join

During the ____ phase of data warehouse implementation, tasks include system maintenance and system expansion. a. initial data gathering c. loading and testing b. design and mapping d. rollout and feedback

rollout and feedback

A ____ lock allows concurrent transactions to access different rows of the same table. a. database-level b. table-level c. page-level d. row-level

row-level

The ____ establishes the order in which the operations within concurrent transactions are executed. a. transaction log b. timer c. lock manager d. scheduler

scheduler

The ______________ establishes the order in which the operations within concurrent transactions are executed. transaction log timer lock manager scheduler

scheduler

The ________________ determines the order in which the operations with concurrent transactions are executed

scheduler

By it's nature, a single user database system automatically ensures ____________________________ of the database because only one transaction is executed at a time

serializability

By its nature, a single-user database system automatically ensures ____ of the database, because only one transaction is executed at a time. a. serializability and durability b. atomicity and isolation c. serializability and isolation d. atomicity and serializability

serializability and isolation

A(n) ____ phase in a two-phase lock is when a transaction releases all locks and cannot obtain any new lock. a. growing b. shrinking c. locking d. unlocking

shrinking

A(n) ________________ phase in a two-phase lock is when a transaction releases all locks and cannot obtain any new lock growing shrinking locking unlocking

shrinking

A(n) ______________________ phase in a two-phase lock is when a transaction releases all locks and cannot obtain any new lock

shrinking

Using multidimensional jargon, the ability to focus on slices of the cube to perform a more detailed analysis is known as _________________. star cuts slice and dice microscoping TPL processing

slice and dice

___________________________ - using multidimensional jargon, is the ability to focus on slices of the cube to perform a more detailed analysis

slice and dice

Multidimensional data analysis is also affected by how the database system handles ____. a. main memory c. warehouses b. hypercubes d. sparsity

sparsity

The _______________ depicts the arrangement of higher-level entity supertypes (parent entities) and lower-level entity subtypes (child entities) subtype descriminator inhertance specialization hierarchy entity supertype

specialization hierarchy

The ____ schema creates the near equivalent of a multidimensional database schema from the existing relational database. a. star c. OLAP b. square d. ROLAP

star

The _______________ schema creates the near equivalent of a multidimensional database schema from the existing relational database star square OLAP ROLAP

star

The _________________ schema creates the near equivalent of a multidimensional database schema from the existing relational database star square OLAP ROLAP

star

The __________________ schema creates the near equivalent of a multidimensional database schema from the existing relational database

star

____ schemas yield an easily implemented model for multidimensional data analysis, while still preserving the relational structures on which the operational database is built. a. Fact c. Dimensional b. Star d. Operational

star

A __________________ based query optimization algorithm uses statistical information about the database statistically dynamic rule manual

statistically

Every subtype can have __________________ supertype to which it is directly related

supertype

Decision ____ is a methodology designed to extract information from data and to use such information as a basis for decision making. a. management c. mining b. support d. analysis

support

Which MIME type is used to transfer a regular ASCII file hosted in a database management system (DBMS) to a web client? text/plain application/pdf text/html image/gif

text/plain

The most common algorithms for concurrency control are locks, ____ stamping, and optimistic methods. a. date b. time c. hour d. minute

time

The information stored in the ____ is used by the DBMS for a recovery requirement triggered by a ROLLBACK statement, a program's abnormal termination, or a system failure such as a network discrepancy or a disk crash. a. data dictionary b. metadata c. rollback manager d. transaction log

transaction log

The information stored in the _____________________ is used by the DBMS for a recovery ROLLBACK statement, a program's abnormal termination, or a system failure such as a network discrepancy or a disk crash

transaction log

The hierarchical database model is based on a ___________________ - tree structure - lack of a parent segment - lack of a child segment - matrix

tree structure

The hierarchical database model is based on a ____________________ tree structure lack of a parent segment lack of a child segment matrix

tree structure

A(n) ______________ is a procedure that is executed automatically in response to an associated database operation cursor trigger waypoint fetchpoint

trigger

A(n) ____________________ is a procedure that is executed automatically in response to an associated database operation

trigger

A ________________ relationship exists when an association is maintained within a single entity unary ternary binary weak

unary

A __________________ relationship exists when an association is maintained within a single entity

unary

The phenomenon of ____ occurs when two transactions, T1 and T2, are executed concurrently and the first transaction (T1) is rolled back after the second transaction (T2) has already accessed the uncommitted data—thus violating the isolation property of transactions. a. lost updates b. uncommitted data c. transaction failure d. inconsistent retrieval

uncommitted data

The phenomenon of _____________ occurs when two transactions, T1 and T2, are executed concurrently and the first transaction (T1) is rolled back after the second transaction (T2) has already accessed the uncommitted data -- thus violating the isolation property of transactions lost updates uncommitted data transaction failure inconsistent retrieval

uncommitted data

The phenomenon of __________________________ occurs when two transactions T1 and T2 are executed concurrently and the first transaction (T1) is rolled back after the second transaction (T2) has already accessed the uncommitted data -- thus violating the isolation property of transactions

uncommitted data

As long as two transactions, T1 and T2, access ____ data, there is no conflict, and the order of execution is irrelevant to the final outcome. a. shared b. common c. unrelated d. locked

unrelated

As long as two transactions, T1 and T2, access _________________ data, there is no conflict and the order of execution is irrelevant to the final outcome

unrelated

A __________________ is a uniquely identifiable object that allows a given person to log on to the new database user role profile manager

user

Classifying users into _________________, according to common access needs, facilitates the DBA's job of controlling and managing the access privileges of individual users

user groups

During the ____ phase, the transaction is confirmed to ensure that the changes made will not affect the integrity and consistency of the database. a. read b. validation c. write d. shared

validation

Data cubes are created by extracting data from the operational database or from the data ____. a. base c. processing system b. management system d. warehouse

warehouse

Data cubes are created by extracting data from the operational database or from the data ___________________

warehouse

When is the implicit beginning of a transaction?

when the first SQL statement is encountered

To provide better performance, some OLAP systems merge the data warehouse and data mart approaches by storing small extracts of the data warehouse at end user ____. a. workstations c. schemas b. queries d. servers

workstations

To provide better performance, some OLAP systems merge the data warehouse and data mart approaches by storing small extracts of the data warehouse at end user _________________. workstations queries schemas servers

workstations


Ensembles d'études connexes

CPH - Social and Behavioral Sciences

View Set

Barron's 500 SAT Vocabulary Words

View Set

Personal Career Development Exam 1

View Set

BUSINESS PRINCIPLES - CHAPTER 2 - 5

View Set

Computer Science 105 Chapter 1 Study Guide

View Set

Chapter 1: Modern Project Management

View Set