logo image

5138 Final Exam

__ requires that all operations of a transaction be completed.
Atomicity
A consistent database is __.
one in which all data integrity constraints are satisfied
A transaction is a ____ unit of work that must be either entirely completed or aborted.
logical
____ means that data used during the execution of a transaction cannot be used by a second transaction until the first one is completed.
Isolation
The DBMS __ the SQL query and chooses the most efficient access/execution plan.
parses
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 ____.
extends
A distributed ____ allows a transaction to reference several different remote database processing sites.
transaction
__ fragmentation refers to the division of a relation into subsets of tuples.
Data
Data is captured from the OLTP system and placed on the ____ on a near-real time basis.
data warehouse
From the data analyst’s point of view, decision support data differ from operational data in three main areas: time span, granularity, and ____.
dimensionality
The basic star schema has four components: facts, __, attributes, and attribute hierarchies.
dimensions
The __ object is the in-memory representation of the data in the database.
DataSet
Another name for a database server-side extension is ___.
Web-to-database middleware
__ are written instructions that describe a series of steps to be followed during the performance of a given activity.
Procedures
Instructions to create a password are an example of a __.
procedure
Explain the transaction log, and what it is used for.
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.
What are the three basic techniques to control deadlocks?
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.

List and describe some typical DBMS processes.
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.

What is SQL performance tuning?
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.

What is the DBA’s managerial role?
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.

What is a data cube?
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.
Which of the following tables is NOT a main table for e-commerce database?
PROMOTION
What is the defining characteristic of an object?
Unique identity
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

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.
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.
The end of a program is successfully reached.
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.
The program is abnormally terminated.
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.
transaction log
A ____ lock prevents the use of any tables in the database from one transaction while another transaction is being processed.
database-level
A ____ lock locks the entire table preventing access to any row by a transaction while another transaction is using the table.
table-level
A ____ lock allows concurrent transactions to access different rows of the same table.
row-level
A(n) ____ condition occurs when two or more transactions wait for each other to unlock data.
deadlock
All transactions must display ____.
atomicity, durability, serializability, and isolation
A single-user database system automatically ensures ____ of the database, because only one transaction is executed at a time.
serializability and isolation
The ANSI has defined standards that govern SQL database transactions. Transaction support is provided by two SQL statements: ____ and ROLLBACK.
COMMIT
____ are required to prevent another transaction from reading inconsistent data.
Schedules
Lock ____ indicates the level of lock use.
granularity
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.
SQL performance
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.
DBMS performance
All data in a database are stored in ____ files.
data
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 ____.
extends
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.
after, before
To generate database object statistics manually, you could use the following syntax:____.
ANALYZE object_name COMPUTE STATISTICS;
The DBMS ____ the SQL query and chooses the most efficient access/execution plan.
parses
The DBMS ____ the SQL query using the chosen execution plan.
executes
The DBMS ____ the data and sends the result set back to the client.
fetches
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.
Data sparsity
____ is/are the central activity during the parsing phase in query processing.
Query optimization
The majority of primary memory resources will be allocated to the ____ cache.
data
The ____ cache stores the most recently executed SQL statements.
SQL
The ____ cache is used as a temporary storage area for ORDER BY or GROUP BY operations, as well as for index-creation functions.
sort
The ____ table space is used to store the data dictionary tables.
system
The ____ table space is used as a temporary storage area for merge, sort, or set aggregate operations.
temporary
____ is a disadvantage of a DDBMS.
Lack of standards
____ is a disadvantage of a DDBMS.
Security
____ is an advantage of a DDBMS.
Reduced operating cost
A database is composed of several parts known as database ____.
fragments
The ____ processor is the software component found in each computer that requests data. It receives and processes the application’s data requests.
transaction
The ____ processor is the software component residing on each computer that stores and retrieves data located at the site.
data
Under the ____ scenario, multiple processes run on different computers sharing a single data repository.
multiple-site processing, single-site data
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
____ distributed database systems integrate different types of centralized DBMSs over a network.
Heterogeneous
____ transparency allows a distributed database to be treated as a single logical database.
Distribution
____ transparency allows a transaction to update data at several network sites.
Transaction
____ is the highest level of transparency. The end user or programmer does not need to know that a database is partitioned.
Fragmentation
A ____ contains the description of the entire database as seen by the database administrator.
distributed data dictionary
A distributed ____ allows a transaction to reference several different remote database processing sites.
transaction
____ query optimization takes place at compilation time.
Static
____ query optimization takes place at execution time.
Dynamic
____ fragmentation allows you to break a single object into two or more segments or fragments.
Data
____ fragmentation refers to the division of a relation into subsets of tuples.
Horizontal
____ fragmentation refers to the division of a relation into attribute subsets.
Vertical
A disadvantage of a client/server application is ____.
An increase in the number of users and processing sites often paves the way for security problems.
Analyzing decision support data to generate information is part of ____.
business intelligence
Data mining is part of the “____” section of the business intelligence framework.
Processes
A data mart is part of the “____” section of the business intelligence framework.
Governance
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.
governance
The ____ contains business data extracted from the operational database and from external data sources.
data store
The ____ is in charge of presenting the data to the end user in a variety of ways.
data visualization tool
Data is captured from the OLTP system and placed on the ____ on a near-real time basis.
data warehouse
BusinessObjects is a(n) ____ tool.
OLAP
From the data analyst’s point of view, decision support data differ from operational data in three main areas: time span, granularity, and ____.
dimensionality
A characteristic of operational data is ____.
Highly normalized
In 2008, Wal-Mart, the world’s largest company, had more than four ____ of data in its data warehouses.
petabytes
____ can serve as a test vehicle for companies exploring the potential benefits of data warehouses.
Data marts
First-generation enterprise data warehouse using RDBMS were implemented using a ____ model.
star schema
One of the main characteristics of an OLAP system is ____ .
They support client/server architecture
Decision support data tend to be non-normalized, ____, and pre-aggregated.
duplicated
The ____ schema is designed to optimize data query operations rather than data update operations.
star
Conceptually, MDBMS end users visualize the stored data as a three-dimensional cube known as a ____.
data cube
A characteristic of ROLAP is ____.
It uses the star schema
The basic star schema has four components: facts, ____, attributes, and attribute hierarchies.
dimensions
Computed or derived facts are sometimes called ____ to differentiate them from stored facts.
metrics
In a star schema, dimensions are normally stored in ____.
dimension tables
The ____ dimension of a data cube provides a framework from which sales patterns can be analyzed and possibly predicted.
time
The attribute hierarchy provides a top-down data organization that is used for two main purposes: ____ and drill-down/roll-up data analysis.
aggregation
Fact and dimension tables are related by ____ keys.
foreign
A ____ schema is a type of star schema in which the dimension tables can have their own dimension tables.
snowflake
____ is a collection of technologies used to access any type of data source and manage the data through a common interface.
UDA
ODBC works on the ____ operating system.
Windows
In the ODBC architecture, a(n) ____ is in charge of managing all database connections.
driver manager
Based on Microsoft’s Component Object Model (COM), ____ is database middleware that adds object-oriented functionality for access to relational and nonrelational data.
OLE-DB
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.
DataReader
The ____ object is the in-memory representation of the data in the database.
DataSet
____ is the most specialized object in the ADO.NET framework.
DataAdapter
A server-side extension ____.
provides its services to the Web server in a way that is totally transparent to the client browser
Another name for a database server-side extension is ____.
Web-to-database middleware
Of the following,____ is the first step in the exchange between a Web browser and a database.
The Web browser requests a page from a Web server.
Of the following,____ is a well-defined Web-server interface.
Common Gateway Interface
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.
HTTP
Client-side extensions are available in various forms, such as ____.
Java and JavaScript
A ____ is an external application that is automatically invoked by the browser when needed.
plug-in
A plug-in is ____.
operating system specific
_____ is a Web application server that provides database access to the Web.
ColdFusion
A feature of a Web application server is ____.
It provides an integrated development environment with session management and support for persistent application variables.
The XML metalanguage allows the definition of new ____, such as , to describe the data elements used in an XML document.
tags
A(n) ____ file provides the composition of the database’s logical model and defines the syntax rules for an XML document.
DTD
Using ____, you can extract data from an XML document and convert it into a text file.
XSLT
When introducing a database into an organization, a(n) ____ impact is likely because the database approach creates a more controlled and structured information flow.
cultural
The person responsible for the control of the centralized and shared database is the database ____.
administrator
It is common practice to define the DBA function by dividing the DBA operations according to the ____ phases.
DBLC
The ____ administrator is responsible for strategic planning.
data
As a manager, the DBA must concentrate on the ____ dimensions of the database administration function.
control and planning
____ are more detailed and specific than policies and describe the minimum requirements of a given DBA activity.
Standards
“All users must have passwords” is an example of a ____.
policy
Instructions to create a password are an example of a ____.
procedure
Procedures and standards must be revised at least ____ to keep them up-to-date.
annually
____ activities cover all tasks directly related to the day-to-day operations of the DBMS and its applications.
System support
____ management is designed to limit access to the database.
User access
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
If the user named SYSTEM creates a VENDOR table, the table will belong to the SYSTEM ____.
schema
A ____ is a uniquely identifiable object that allows a given person to log on to the database.
user
A ____ is a named collection of database access privileges that authorize a user to connect to the database and use the database system resources.
role
A ____ is a named collection of settings that control how much of the database resource a given user can use.
profile
Describe the need for convenient and safe backup storage.
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?
OOPLs were developed to ____.
provide a powerful software modeling tool for application development
An object that contains at least one attribute that refers to another object is referred to as a ____ object.
compound
An object that is used to represent a relationship between two or more objects is referred to as a(n) ____ object.
associative
Which is NOT a rule to create an OO system?
The system must be able to remember data locations.
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.
object
A(n) ____ has components and relationships but lacks manipulative ability.
entity
The object ____ is the set of values that the object’s attributes have at a given time.
state
A(n) ____ is the code that performs a specific operation on the object’s data.
method
Which of the following does NOT describe an abstract data type?
It allows direct access to its internal data representation.
E-commerce styles CANNOT be classified as ____.
C2G
____ Web pages are adequate to display information such as product catalogs or contact information.
Static
____ Web pages are suited to e-commerce applications such as online ordering with product customization options.
Dynamic
The ____ was originally created with the objective of sharing data easily, rather than securely.
World Wide Web
____ can be used to create meta-dictionaries or vocabularies for databases.
XML
Orbitz.com is an example of a ____ e-commerce site.
B2C
Ebay.com is an example of a(n) ____ e-commerce site.
intra-business
What is the biggest and fastest-growing segment of the e-commerce market?
B2B
What is the standard protocol used by the Web browser and the Web server to send requests and replies between servers and browsers?
HTTP
A ____ is a private entity or company that certifies that the user or vendor is who (s)he claims to be.
certification authority
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
_________________________

CREATE TRIGGER CheckBMW
ON CAR
FOR Insert
AS

BEGIN 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

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 ___________;

CREATE PROCEDURE NewEmployee
( @EMP_LNAME VCHAR,
@EMP_FNAME VCHAR,
@EMP_SSN int,
@EMP_DOB date,
@CAR_VIN int,
@CAR_MAKE VCHAR)
AS

INSERT 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;

Need essay sample on "5138 Final Exam"? We will write a custom essay sample specifically for you for only $ 13.90/page

Hire Writer
×
Sorry, but copying text is forbidden on this website. If you need this or any other sample, please register

Already on Businessays? Login here

No, thanks. I prefer suffering on my own
Sorry, but copying text is forbidden on this website. If you need this or any other sample register now and get a free access to all papers, carefully proofread and edited by our experts.
Sign in / Sign up
No, thanks. I prefer suffering on my own
Not quite the topic you need?
We would be happy to write it
Join and witness the magic
Service Open At All Times
|
Complete Buyer Protection
|
Plagiarism-Free Writing

Emily from Businessays

Hi there, would you like to get such a paper? How about receiving a customized one? Check it out https://goo.gl/chNgQy

We use cookies to give you the best experience possible. By continuing we’ll assume you’re on board with our cookie policy