Data Management Final

Single Table View

-1-1 correspondence between view rows and base table rows -view includes PK of base table -view includes all required columns -DOESN'T include GROUP BY or DISTINCT

Transaction Processing Services

-2 services to enforce 4 properties -Concurrency control -Recovery management -Service Characteristics-transparent, consume significant resources, significant cost component

ACID- Transaction Properties

-Atomic- all or nothing -Consistent- integrity maintained -Isolated- no unwanted interference from other users -Durable- DB changes are permanent

Failure Types

-Device Failure-hard disk dies, affects all transactions, no intelligent recovery -Local Failure- client computer crashes, outstanding transaction rollback -System Failure-DB server/software crash, affects all active transactions, less common

Mandatory Access Control (MAC)

-assign classification levels to DB objects -assign clearance levels to users -update and deletion the classification and clearance levels need to be the same -for reading DB objects need same or higher clearance level

View Rules

-change/delete data only if data are visible through view -change/insert data even though data will not be visible through the view after the change -CAN'T change/delete data if data are NOT visible through view

Transaction

-collection of database operations -must include at least one modification op. -one unit of work -no lost data -no interference among multiple users -failures must be able to recover so data isn't lost

At checkpoint time:

-contents are written in the DB

View

-derived table -stored "SELECT" query -ADV. - reduces impact of DB definition changes, simplify DB usage, DB security -DISADV.-performance penalty on complex views

Concurrency Control Task

-eliminate mult. transactions happening simultaneously -maximize work performance measured as throughput while no interference

Timing of DB Writes

-immediate update- as soon as update occurs --finished before failure redo from checkpoint --not yet finished undo backwards from most recent log record -deferred update- end of the transaction --finished before failure redo from first log record --not yet finished nothing happens

Data Administrator (DA)

-middle or upper management -broad view of information resources -CIO, data planner -devolops enterprise data model (rough data model that defines scope) -establishes inter DB standards -negotiates contractual terms

Database Administrator (DBA)

-support role; technical -emphasis on individual databases and DBMSs -DB development tasks -consults on application development -implements software/DBMS -evaluates DBMS capabilities and features

Concurrency Problems

3 types of problems -locking mechanism to address concurrency problems -deadlock resolution needed -locking mechanism causes deadlock resolution Problem 1: Lost Update Problem 2: Uncommitted Dependency Problem 3: Incorrect Summary

Which of the following is not considered to be a failure affecting database management system transactions?

A data entry error resulting in wrong data in the database

The primary key of a table is chosen among:

All candidate keys

For complex integrity constraints beyond the capability of CHECK constraints, SQL:2011 provides for ____________________, which can be used for constraints involving multiple tables and statistical calculations.

Assertions

Julia has a shared lock on the savings account and Bill is trying to perform an action requesting an exclusive lock on the same data:

Bill's transaction must wait until Julia's lock is released

To cause the same action to be performed on referenced rows when a record is updated, you would specify the clause "ON UPDATE ________" when creating the table.

CASCADE

The ACID properties are insured by the following services of the database management system:

Concurrency transparency and Recovery transparency

Discretionary Access Control (DAC)

DB admin assigns access rights or privileges to users

The ability to add the new column without requiring changes to all of the application programs strongly relates to the following feature of modern database systems:

Data Independence

Deadlock Resolution

Detection Timeout-checks for outstanding transaction

Two forms of authorization controls are _________ and ____________?

Discretionary and Mandatory Access Control

Data is said to be persistent when it resides on stable storage and is never removed or archived for the life of the business.

False

If X is a foreign key of table T referencing the same table T's primary key A, then X must be unique.

False

In the early database management systems, there was not a very close connection between the database and the computer programs that accessed it, so as a result the database definition was separate from the computer programs that accessed the database.

False

Since most database management systems are very good at managing database dependencies, the database administrator does not usually need to be knowledgeable or concerned about dependency maintenance for actions on referenced objects.

False

Suppose A1 and A2 are attributes of a table. If (A1, A2) is the composite primary key of the table, then A1 must be unique in the table.

False

The objective of concurrency control is to minimize transaction throughput while preventing interference among multiple users.

False

The referential integrity rule prohibits foreign keys to have null values.

False

When an update to the database takes place, the time of the update, the values of the data before and after the update, and the type of lock used are recorded in the transaction log.

False

The goal of information resource management is to use information technology as a tool for processing, distributing, and integrating information for isolated functions within an organization.

False -information resource management (IRM): a broad management philosophy that seeks to use information technology as a tool for processing, distributing, and integrating information throughout an organization

The concept of data persistency in a database does not depend on relevance of intended usage of data. It rather depends on the cost of storing the data. That is, even though data is no longer relevant, it should not be removed or archived if the cost of storage is low.

False data persistency DOES depend on relevant intended usage

Which one of the following is the least likely to be viewed as a transaction?

Retrieving an employee hiring date

Which of the following will cause a deadlock?

Transaction T1 places an exclusive lock on data A, then requests one on data B. Simultaneously, transaction T2 places an exclusive lock on data B, then requests one on data A

A language such as SQL allowing the user to specify the parts of a database rather than to code a complex procedure is called a nonprocedural database language.

True

A procedural language interface is necessary because nonprocedural access and application development tools of database software are sometimes not powerful enough or do not provide the level of control necessary for significant application development.

True

An example of concurrency transparency would be that multiple students could register for the same course offering at the same time, without interference from each other.

True

Data is said to be interrelated when data stored as separate units can be connected to provide a whole picture.

True

Database management systems used in highly sensitive areas such as national defense and intelligence gathering must be able to support mandatory access controls.

True

Development of an enterprise data model is one of the most important responsibilities of the data administrator.

True

If X is a foreign key of table T1 referencing table T2, then X must be the primary key (or a candidate key) of the table T2.

True

Nonprocedural languages do not include looping statements.

True

One of the responsibilities of the data administrator is to establish standards for data naming conventions for the databases in the enterprise.

True

The retrieval of summarized data to support long-range decisions is a dominant feature of data warehousing, whereas a mixture of updating and retrieving data is characteristic of databases that support the day to day operations of an organization.

True

To restore a database using the immediate update approach, both undo and redo operations may be necessary, whereas with the deferred update approach only redo operations are used.

True

When two transactions are in a deadlock, one of the transactions will eventually be cancelled or forced to re-start to let the other one continue execution.

True

With discretionary access control, users may be individually given access rights to specific data, or these access privileges may be granted to groups of users.

True

In the set-oriented database terminology, a tuple is synonymous with a row.

True Table Oriented-Set Oriented-Record Oriented Table-Relation-Record Type, File Row-Tuple-Record Column-Attribute-Field

Transaction Log

Undo: revert to previous state- write old value -backward direction Redo: use new state- write new value -forward direction

Common data that multiple users try to change simultaneously is called:

a hot spot

For each column in a table, the ___________________ defines a set of values and permissible operations on those values.

data type

Authorization Rules

define authorized users, allowable operations, and accessible parts of a database

BCNF

every determinant must be CK -part of key->part of key -nonkey->part of key -highly specialized cases

2NF

every nonkey column depends on all CKs, not a subset of any CK -violations- part of key->nonkey, combined keys ex. combined key PK:(StdNo, OfferNo)

3NF

every nonkey column depends only on CKs -violations-Nonkey->Nonkey -A ->B, B -> C then A -> C is transitive dependency

Functional Dependencies

means of expressing that the value of one particular attribute is associated with a specific single value of another attribute x->y x funct. determines y for each x value -> at most one y value

Normalization

process of designing a DB w/ well-structured tables by eliminating redundancies causing anomalies 1. identify FDs 2. determine whether FDs meet normal form 3. split table to meet normal form if there's a violation

1NF

starting point, no repeating groups, flat rows

Anomalies

unexpected side effect when we insert, modify, delete more data than desired