question

__ requires that all operations of a transaction be completed.

answer

Atomicity

question

A consistent database is __.

answer

one in which all data integrity constraints are satisfied

question

A transaction is a ____ unit of work that must be either entirely completed or aborted.

answer

logical

question

____ means that data used during the execution of a transaction cannot be used by a second transaction until the first one is completed.

answer

Isolation

question

The DBMS __ the SQL query and chooses the most efficient access/execution plan.

answer

parses

question

A DBA determines the initial size of the data files that make up the database; however, as required, the data files can automatically expand in predefined increments known as ____.

answer

extends

question

A distributed ____ allows a transaction to reference several different remote database processing sites.

answer

transaction

question

__ fragmentation refers to the division of a relation into subsets of tuples.

answer

Data

question

Data is captured from the OLTP system and placed on the ____ on a near-real time basis.

answer

data warehouse

question

From the data analyst's point of view, decision support data differ from operational data in three main areas: time span, granularity, and ____.

answer

dimensionality

question

The basic star schema has four components: facts, __, attributes, and attribute hierarchies.

answer

dimensions

question

The __ object is the in-memory representation of the data in the database.

answer

DataSet

question

Another name for a database server-side extension is ___.

answer

Web-to-database middleware

question

__ are written instructions that describe a series of steps to be followed during the performance of a given activity.

answer

Procedures

question

Instructions to create a password are an example of a __.

answer

procedure

question

Explain the transaction log, and what it is used for.

answer

A DBMS uses a transaction log to keep track of all transactions that update the database. The information stored in this log 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.

question

What are the three basic techniques to control deadlocks?

answer

Deadlock Prevention A transaction requesting a new lock is aborted if there is a possibility that a deadlock may occur. If the transaction is aborted, all the changes made by this transaction are rolled back and all locks are released. The transaction is then re-scheduled for execution. Deadlock prevention works because it avoids the conditions that lead to deadlocking.Deadlock Detection The DBMS periodically tests the database for deadlocks. If a deadlock is found, one of the transactions (the "victim") is aborted (rolled back and rescheduled) and the other transaction continues. Note particularly the discussion in Section 9.4.1, Wait/Die and Wound/Wait Schemes. Deadlock Avoidance The transaction must obtain all the locks it needs before it can be executed. This technique avoids rollback of conflicting transactions by requiring that locks be obtained in succession. However, the serial lock assignment required in deadlock avoidance increases the response times.Deadlock Prevention A transaction requesting a new lock is aborted if there is a possibility that a deadlock may occur. If the transaction is aborted, all the changes made by this transaction are rolled back and all locks are released. The transaction is then re-scheduled for execution. Deadlock prevention works because it avoids the conditions that lead to deadlocking.

question

List and describe some typical DBMS processes.

answer

Listener. The listener process listens for clients' requests and handles the processing of the SQL requests to other DBMS processes. Once a request is received, the listener passes the request to the appropriate user process.User. The DBMS creates a user process to manage each client session. Therefore, when you log on to the DBMS, you are assigned a user process. This process handles all requests you submit to the server. There are many user processes—at least one per each logged-in client. Scheduler. The scheduler process organizes the concurrent execution of SQL requests. Lock manager. This process manages all locks placed on database objects, including disk pages. Optimizer. The optimizer process analyzes SQL queries and finds the most efficient way to access the data. You will learn more about this process later in the chapter.

question

What is SQL performance tuning?

answer

SQL performance tuning is evaluated from the client perspective. Therefore, the goal is to illustrate some common practices used to write efficient SQL code. A few words of caution are appropriate: 1. Most current-generation relational DBMSs perform automatic query optimization at the server end. 2. Most SQL performance optimization techniques are DBMS-specific, and therefore, are rarely portable, even across different versions of the same DBMS. Part of the reason for this behavior is the constant advancement in database technologies.

question

What is the DBA's managerial role?

answer

As a manager, the DBA must concentrate on the control and planning dimensions of database administration. Therefore, the DBA is responsible for:Coordinating, monitoring, and allocating database administration resources: people and data. Defining goals and formulating strategic plans for the database administration function.

question

What is a data cube?

answer

Refers to the multidimensional data structure used to store and manipulate data in a multidimensional DBMS. The location of each data value in the data cube is based on the x-, y-, and z-axes of the cube. Data cubes are static (must be created before they are used), so they cannot be created by an ad hoc query.

question

Which of the following tables is NOT a main table for e-commerce database?

answer

PROMOTION

question

What is the defining characteristic of an object?

answer

Unique identity

question

Consider the following SQL Server function. Explain what this function does, what value(s) it returns and what the purpose of parameters and variables declared in this function are. CREATE FUNCTION Mystery(@x int) RETURNS int AS BEGIN DECLARE @y AS int SELECT @y = COUNT(CAR_MAKE) FROM CAR WHERE CAR_EMP_SSN = @x RETURN @y END

answer

The function goes through all the Employee and Vehicle records and is meant to count the number of makes of Vehicles an employee owns. The function does this by comparing an employee's SSN (EMP_SSN) with the car's CAR_EMP_SSN entry and if these two match it then displays a count of all the make of vehicles belonging a particular employee. The relationship being that one employee can own zero or several makes of cars i.e. he or she can be the owner of several vehicles of different make. Therefore the @x entry could be an employee's SSN.

question

ANSI defines four events0 that signal the end of a transaction. Of the following events, ____ is defined by ANSI as being equivalent to a COMMIT.

answer

The end of a program is successfully reached.

question

ANSI defines four events that signal the end of a transaction. Of the following events ____ is defined by ANSI as being equivalent to a ROLLBACK.

answer

The program is abnormally terminated.

question

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.

answer

transaction log

question

A ____ lock prevents the use of any tables in the database from one transaction while another transaction is being processed.

answer

database-level

question

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

answer

table-level

question

A ____ lock allows concurrent transactions to access different rows of the same table.

answer

row-level

question

A(n) ____ condition occurs when two or more transactions wait for each other to unlock data.

answer

deadlock

question

All transactions must display ____.

answer

atomicity, durability, serializability, and isolation

question

A single-user database system automatically ensures ____ of the database, because only one transaction is executed at a time.

answer

serializability and isolation

question

The ANSI has defined standards that govern SQL database transactions. Transaction support is provided by two SQL statements: ____ and ROLLBACK.

answer

COMMIT

question

____ are required to prevent another transaction from reading inconsistent data.

answer

Schedules

question

Lock ____ indicates the level of lock use.

answer

granularity

question

On the client side, the objective is to generate a SQL query that returns the correct answer in the least amount of time, using the minimum amount of resources at the server end. The activities required to achieve that goal are commonly referred to as ____ tuning.

answer

SQL performance

question

On the server side, the DBMS environment must be properly configured to respond to clients requests in the fastest way possible, while making optimum use of existing resources. The activities required to achieve that goal are commonly referred to as ____ tuning.

answer

DBMS performance

question

All data in a database are stored in ____ files.

answer

data

question

A DBA determines the initial size of the data files that make up the database; however, as required, the data files can automatically expand in predefined increments known as ____.

answer

extends

question

The data cache is where the data read from the database data files are stored ____ the data have been read or ____ the data are written to the database data files.

answer

after, before

question

To generate database object statistics manually, you could use the following syntax:____.

answer

ANALYZE <TABLE/INDEX> object_name COMPUTE STATISTICS;

question

The DBMS ____ the SQL query and chooses the most efficient access/execution plan.

answer

parses

question

The DBMS ____ the SQL query using the chosen execution plan.

answer

executes

question

The DBMS ____ the data and sends the result set back to the client.

answer

fetches

question

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.

answer

Data sparsity

question

____ is/are the central activity during the parsing phase in query processing.

answer

Query optimization

question

The majority of primary memory resources will be allocated to the ____ cache.

answer

data

question

The ____ cache stores the most recently executed SQL statements.

answer

SQL

question

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

answer

sort

question

The ____ table space is used to store the data dictionary tables.

answer

system

question

The ____ table space is used as a temporary storage area for merge, sort, or set aggregate operations.

answer

temporary

question

____ is a disadvantage of a DDBMS.

answer

Lack of standards

question

____ is a disadvantage of a DDBMS.

answer

Security

question

____ is an advantage of a DDBMS.

answer

Reduced operating cost

question

A database is composed of several parts known as database ____.

answer

fragments

question

The ____ processor is the software component found in each computer that requests data. It receives and processes the application's data requests.

answer

transaction

question

The ____ processor is the software component residing on each computer that stores and retrieves data located at the site.

answer

data

question

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

answer

multiple-site processing, single-site data

question

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

answer

multiple-site processing, multiple-site data

question

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

answer

Heterogeneous

question

____ transparency allows a distributed database to be treated as a single logical database.

answer

Distribution

question

____ transparency allows a transaction to update data at several network sites.

answer

Transaction

question

____ is the highest level of transparency. The end user or programmer does not need to know that a database is partitioned.

answer

Fragmentation

question

A ____ contains the description of the entire database as seen by the database administrator.

answer

distributed data dictionary

question

A distributed ____ allows a transaction to reference several different remote database processing sites.

answer

transaction

question

____ query optimization takes place at compilation time.

answer

Static

question

____ query optimization takes place at execution time.

answer

Dynamic

question

____ fragmentation allows you to break a single object into two or more segments or fragments.

answer

Data

question

____ fragmentation refers to the division of a relation into subsets of tuples.

answer

Horizontal

question

____ fragmentation refers to the division of a relation into attribute subsets.

answer

Vertical

question

A disadvantage of a client/server application is ____.

answer

An increase in the number of users and processing sites often paves the way for security problems.

question

Analyzing decision support data to generate information is part of ____.

answer

business intelligence

question

Data mining is part of the "____" section of the business intelligence framework.

answer

Processes

question

A data mart is part of the "____" section of the business intelligence framework.

answer

Governance

question

Had ____ been as pivotal to business operations a few years back, crises precipitated by the likes of Enron, WorldCom, and Arthur Andersen might have been avoided.

answer

governance

question

The ____ contains business data extracted from the operational database and from external data sources.

answer

data store

question

The ____ is in charge of presenting the data to the end user in a variety of ways.

answer

data visualization tool

question

Data is captured from the OLTP system and placed on the ____ on a near-real time basis.

answer

data warehouse

question

BusinessObjects is a(n) ____ tool.

answer

OLAP

question

From the data analyst's point of view, decision support data differ from operational data in three main areas: time span, granularity, and ____.

answer

dimensionality

question

A characteristic of operational data is ____.

answer

Highly normalized

question

In 2008, Wal-Mart, the world's largest company, had more than four ____ of data in its data warehouses.

answer

petabytes

question

____ can serve as a test vehicle for companies exploring the potential benefits of data warehouses.

answer

Data marts

question

First-generation enterprise data warehouse using RDBMS were implemented using a ____ model.

answer

star schema

question

One of the main characteristics of an OLAP system is ____ .

answer

They support client/server architecture

question

Decision support data tend to be non-normalized, ____, and pre-aggregated.

answer

duplicated

question

The ____ schema is designed to optimize data query operations rather than data update operations.

answer

star

question

Conceptually, MDBMS end users visualize the stored data as a three-dimensional cube known as a ____.

answer

data cube

question

A characteristic of ROLAP is ____.

answer

It uses the star schema

question

The basic star schema has four components: facts, ____, attributes, and attribute hierarchies.

answer

dimensions

question

Computed or derived facts are sometimes called ____ to differentiate them from stored facts.

answer

metrics

question

In a star schema, dimensions are normally stored in ____.

answer

dimension tables

question

The ____ dimension of a data cube provides a framework from which sales patterns can be analyzed and possibly predicted.

answer

time

question

The attribute hierarchy provides a top-down data organization that is used for two main purposes: ____ and drill-down/roll-up data analysis.

answer

aggregation

question

Fact and dimension tables are related by ____ keys.

answer

foreign

question

A ____ schema is a type of star schema in which the dimension tables can have their own dimension tables.

answer

snowflake

question

____ is a collection of technologies used to access any type of data source and manage the data through a common interface.

answer

UDA

question

ODBC works on the ____ operating system.

answer

Windows

question

In the ODBC architecture, a(n) ____ is in charge of managing all database connections.

answer

driver manager

question

Based on Microsoft's Component Object Model (COM), ____ is database middleware that adds object-oriented functionality for access to relational and nonrelational data.

answer

OLE-DB

question

The ____ object is a specialized object that creates a read-only session with the database to retrieve data sequentially (forward only) in a very fast manner.

answer

DataReader

question

The ____ object is the in-memory representation of the data in the database.

answer

DataSet

question

____ is the most specialized object in the ADO.NET framework.

answer

DataAdapter

question

A server-side extension ____.

answer

provides its services to the Web server in a way that is totally transparent to the client browser

question

Another name for a database server-side extension is ____.

answer

Web-to-database middleware

question

Of the following,____ is the first step in the exchange between a Web browser and a database.

answer

The Web browser requests a page from a Web server.

question

Of the following,____ is a well-defined Web-server interface.

answer

Common Gateway Interface

question

Each time the end user clicks a hyperlink, the browser generates a(n) ____ GET page request that is sent to the designated Web server, using the TCP/IP Internet protocol.

answer

HTTP

question

Client-side extensions are available in various forms, such as ____.

answer

Java and JavaScript

question

A ____ is an external application that is automatically invoked by the browser when needed.

answer

plug-in

question

A plug-in is ____.

answer

operating system specific

question

_____ is a Web application server that provides database access to the Web.

answer

ColdFusion

question

A feature of a Web application server is ____.

answer

It provides an integrated development environment with session management and support for persistent application variables.

question

The XML metalanguage allows the definition of new ____, such as , to describe the data elements used in an XML document.

answer

tags

question

A(n) ____ file provides the composition of the database's logical model and defines the syntax rules for an XML document.

answer

DTD

question

Using ____, you can extract data from an XML document and convert it into a text file.

answer

XSLT

question

When introducing a database into an organization, a(n) ____ impact is likely because the database approach creates a more controlled and structured information flow.

answer

cultural

question

The person responsible for the control of the centralized and shared database is the database ____.

answer

administrator

question

It is common practice to define the DBA function by dividing the DBA operations according to the ____ phases.

answer

DBLC

question

The ____ administrator is responsible for strategic planning.

answer

data

question

As a manager, the DBA must concentrate on the ____ dimensions of the database administration function.

answer

control and planning

question

____ are more detailed and specific than policies and describe the minimum requirements of a given DBA activity.

answer

Standards

question

"All users must have passwords" is an example of a ____.

answer

policy

question

Instructions to create a password are an example of a ____.

answer

procedure

question

Procedures and standards must be revised at least ____ to keep them up-to-date.

answer

annually

question

____ activities cover all tasks directly related to the day-to-day operations of the DBMS and its applications.

answer

System support

question

____ management is designed to limit access to the database.

answer

User access

question

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

answer

user groups

question

If the user named SYSTEM creates a VENDOR table, the table will belong to the SYSTEM ____.

answer

schema

question

A ____ is a uniquely identifiable object that allows a given person to log on to the database.

answer

user

question

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

answer

role

question

A ____ is a named collection of settings that control how much of the database resource a given user can use.

answer

profile

question

Describe the need for convenient and safe backup storage.

answer

There must be multiple backups of the same data, and each backup copy must be stored in a different location. The storage locations must include sites inside and outside the organization. (Keeping different backups in the same place defeats the purpose of having multiple backups in the first place.) The storage locations must be properly prepared and may include fire-safe and quakeproof vaults, as well as humidity and temperature controls. The DBA must establish a policy to respond to two questions: (1) Where are the backups to be stored? (2) How long are backups to be stored?

question

OOPLs were developed to ____.

answer

provide a powerful software modeling tool for application development

question

An object that contains at least one attribute that refers to another object is referred to as a ____ object.

answer

compound

question

An object that is used to represent a relationship between two or more objects is referred to as a(n) ____ object.

answer

associative

question

Which is NOT a rule to create an OO system?

answer

The system must be able to remember data locations.

question

A(n) ____ is an abstract representation of a real-world entity that has a unique identity, embedded properties, and the ability to interact with other objects and act upon itself.

answer

object

question

A(n) ____ has components and relationships but lacks manipulative ability.

answer

entity

question

The object ____ is the set of values that the object's attributes have at a given time.

answer

state

question

A(n) ____ is the code that performs a specific operation on the object's data.

answer

method

question

Which of the following does NOT describe an abstract data type?

answer

It allows direct access to its internal data representation.

question

E-commerce styles CANNOT be classified as ____.

answer

C2G

question

____ Web pages are adequate to display information such as product catalogs or contact information.

answer

Static

question

____ Web pages are suited to e-commerce applications such as online ordering with product customization options.

answer

Dynamic

question

The ____ was originally created with the objective of sharing data easily, rather than securely.

answer

World Wide Web

question

____ can be used to create meta-dictionaries or vocabularies for databases.

answer

XML

question

Orbitz.com is an example of a ____ e-commerce site.

answer

B2C

question

Ebay.com is an example of a(n) ____ e-commerce site.

answer

intra-business

question

What is the biggest and fastest-growing segment of the e-commerce market?

answer

B2B

question

What is the standard protocol used by the Web browser and the Web server to send requests and replies between servers and browsers?

answer

HTTP

question

A ____ is a private entity or company that certifies that the user or vendor is who (s)he claims to be.

answer

certification authority

question

Complete the following SQL Server trigger that prevents an employee from owning more than two BMW's. CREATE TRIGGER CheckBMW ON _________ FOR ______________ AS BEGIN TRANSACTION DECLARE ______________ DECLARE ______________ DECLARE ______________ Set @ErrorMSG = 'Not more than two BMWs' SELECT _____________________________ FROM INSERTED i SELECT _____________________________ FROM ________ WHERE ______________________________ IF ________ BEGIN RAISERROR (_____________) __________________________ END ELSE _________________________

answer

CREATE TRIGGER CheckBMW ON CAR FOR Insert ASBEGIN TRANSACTION DECLARE @CarMakeCount AS INT DECLARE @CarMake AS VARCHAR(100) DECLARE @ EmpSSN AS INT DECLARE @ErrorMSG AS CHAR(100) Set @ErrorMSG = 'Not more than two BMWs' SELECT @EmpSSN = i.Emp_SSN FROM INSERTED i SELECT @CarMakeCount = Count(Car_Make) FROM Car C WHERE C.Car_Emp_SSN = @EmpSSN And C.Car_Make = 'BMW' IF (@CarMakeCount >2) BEGIN RAISERROR (@ErrorMSG, 16,1) RollBack Transaction END ELSE COMMIT TRANSACTION

question

Complete the following SQL Server stored procedure. It is designed to create a new person who owns a car. CREATE PROCEDURE NewEmployee ( @EMP_LNAME ___________, @EMP_FNAME ___________, @EMP_SSN ___________, @EMP_DOB ___________, @CAR_VIN ___________, @CAR_MAKE ___________) AS INSERT INTO EMPLOYEE ___________; VALUES ___________; INSERT INTO CAR ___________; VALUES ___________; RETURN ___________;

answer

CREATE PROCEDURE NewEmployee ( @EMP_LNAME VCHAR, @EMP_FNAME VCHAR, @EMP_SSN int, @EMP_DOB date, @CAR_VIN int, @CAR_MAKE VCHAR) ASINSERT INTO EMPLOYEE EMPLOYEE.EMP_LNAME, EMPLOYEE.EMP_FNAME, EMPLOYEE.EMP_SSN, EMPLOYEE.EMP_DOB; VALUES Richards, Sue, 6542342345, 1960-02-02; INSERT INTO CAR CAR.CAR_VIN CAR.CAR_MAKE; VALUES 12345678, Audi A4; RETURN NewEmployee;

5138 Final Exam – Flashcards

Unlock all answers in this set

Haven't found what you were looking for?

Search for samples, answers to your questions and flashcards