C192 Study Cards
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
