Database Final

Front 1

Transaction

Back 1

Action or series of actions, that access or change the contents of a database.
Logical unit of work on the database.
Transforms database from one consistent state to another (or at least that is the goal).

Front 2

Commit

Back 2

Used to end a successful transaction and make changes “permanent”

Front 3

Rollback

Back 3

Used to “undo” changes from an aborted transaction
May be done automatically when failure occurs

Front 4

Serial schedule

Back 4

An ordering of operations of the transactions so that there is no interleaving
Doesn’t allow for as much concurrency as we’d like.

Front 5

Concurrency control

Back 5

Two operations conflict if they 1) are from different transactions, 2) access the same item, and 3) at least one of the transactions does a write operation to that item

Front 6

Granularity

Back 6

Size of data items chosen as unit of protection by concurrency control protocol.
Ranging from coarse to fine:
Entire database.
File
Page (block)
Record
Field value of a record

Front 7

Deadlock

Back 7

When two (or more) transactions are each waiting for locks held by the other to be released.
Only one way to break deadlock: abort one or more of the transactions.

Front 8

Checkpoint

Back 8

Point of synchronization between database and log file. All buffers are force-written to secondary storage.

Front 9

What are the purposes of "commit" and "rollback" in transaction processing?

Back 9

At commit, check is made to determine whether conflict has occurred.
If there is a conflict, transaction must be rolled back and restarted.

Front 10

Describe the two valid outcomes of a transaction.

Back 10

Success - transaction commits and database reaches a new consistent state.

Failure - transaction aborts, and database must be restored to a consistent state. Such a transaction is rolled back or undone.

Front 11

Name and describe the ACID properties of transactions.

Back 11

Atomicity: Transaction is either processed in its entirety or not at all
Consistency preservation: Execution of a transaction takes the DB from one consistent state to another (no constraints violated)
Isolation: Should appear as though it is being executed in isolation of all other transactions
Durability: Changes to DB made by committed transaction must persist.

Front 12

What three criteria must be met in order for two operations to be considered conflicting?

Back 12

1) are from different transactions
2) access the same item
3) at least one of the transactions does a write operation to that item

Front 13

Contrast serial and serializable transactions

Back 13

Serial Schedule:
-An ordering of operations of the transactions so that there is no interleaving
-Doesn’t allow for as much concurrency as we’d like.
Serializable Schedules:
-Those that are equivalent to serial schedules
-Produces same final result as serial schedule

Front 14

What mechanism is used to ensure serializability?

Back 14

Locking mechanism

Front 15

Lost Update, example

Back 15

Two transactions that access the same item have operations interleaved in a way that makes the value of the item incorrect
example:Update from T1 is lost because T2 read QOH before T1 was complete.

Front 16

Temporary update, example

Back 16

A trans updates an item, then fails. The item is access by another transaction before rollback.
-example:Update from T1 was rolled back, but not until after T2 read QOH. T2 doesn’t know about the rollback, so total is incorrect.

Front 17

Incorrect summary,example

Back 17

One transaction is calculating an aggregate function on some records while another transaction is updating some of these records. The aggregate function may calculate some values before updating and some after
-example:Problem comes in when some data are read before they’re changed and other data is read after they’re changed.

Front 18

Given a set of transactions along with failure points, be able to indicate what actions (if any) should be taken for each transaction upon database failure.

Back 18

Rollback points, rollforward points

Front 19

List six items that a log file transaction record should contain.

Back 19

Transaction identifier.
Type of log record, (transaction start, insert, update, delete, abort, commit).
Identifier of data item affected by database action (insert, delete, and update operations).
Before-image of data item.
After-image of data item.
Log management information.

Front 20

Requirements for 1NF

Back 20

Every relation has a unique name.
Every attribute value is atomic (single-valued).
Every row is unique.
Attributes in tables have unique names.

Front 21

Requirements for 2NF

Back 21

1NF and no partial functional dependencies
Partial functional dependency: when one or more non-key attributes are functionally dependent on part of the primary key.

Front 22

Requirements for 3NF

Back 22

2NF and no transitive dependencies
Transitive dependency: a functional dependency between two or more non-key attributes.

Front 23

Requirements for BC-NF

Back 23

3NF and every determinant is a candidate key.

Front 24

Describe the basic problem data warehousing is intended to address.

Back 24

Traditionally, databases have supported transactions.
So, DBs are often optimized for transaction processing.
Today, we also need DBs for decision support
Transaction processing design may not be good for decision support

Front 25

Define data warehouse and data warehousing.

Back 25

Data warehousing:
is a process
Goal is to gain value from “informational assets”
Using data warehouses
Data warehouse:
Copy of transactional data formatted so that it’s useful for query and analysis (decision support)

Front 26

Name four characteristics of data warehousing.

Back 26

Collection of DBs designed for decision support
DBs are subject-oriented
Organized around particular subjects
Data in DW are integrated from a variety of internal and external sources
Data are usually transformed from original format
Data are non-volatile (in theory)

Front 27

Describe the three-tier data warehousing architecture.

Back 27

External Data sources, ERP, other internal data sources > Data Warehouse > Reporting & analysis tools

Front 28

Contrast data warehouses and data marts.

Back 28

Data marts are small-scale, specialized, segmented data warehouses

Front 29

Contrast fact and dimension tables.

Back 29

Fact table: Lots of records, but each record is “thin”, consists of PKs from dimension tables
Dimension table:Fewer records, but each record is “fat” (lots of big columns)

Front 30

Name and briefly describe the four steps in data warehousing.

Back 30

Identify source data systems
Design the data warehouse
E/T/L process
DW utilization by end-users

Front 31

Briefly describe four threats to data security.

Back 31

Accidental losses attributable to:
Human error.
Software failure.
Hardware failure.
Theft and fraud.
Improper data access:
Loss of privacy (personal data).
Loss of confidentiality (corporate data).
Loss of data integrity.
Loss of availability (through, e.g. sabotage).

Front 32

Contrast authentication and authorization.

Back 32

Authorization - The granting of a right or privilege, which enables a subject to legitimately have access to a system or a system’s object.
Authentication - A mechanism that determines whether a user is, who he or she claims to be.

Front 33

Name and briefly describe four computer-based countermeasures to security threats.

Back 33

Authorization - The granting of a right or privilege, which enables a subject to legitimately have access to a system or a system’s object.
Authentication - A mechanism that determines whether a user is, who he or she claims to be.
Views – Is the dynamic result of one or more relational operations operating on the base relations to produce another relation.
Encryption - The encoding of the data by a special algorithm that renders the data unreadable by any program without the decryption key.

Front 34

Name and briefly describe three non-computer-based countermeasures to security threats

Back 34

Security policy and contingency plan
Personnel controls
Secure positioning of equipment
Escrow agreements
Maintenance agreements
Physical access controls

Front 35

Given a narrative and/or set of forms and reports, be able to produce an ER diagram.

Back 35

DO NOT start diagramming first!
Write down the entities.
Write down the attributes for each entity.
Select the identifying attribute(s) for each entity.
Draw a rough draft of the E-R diagram. Show entities and relationships only.
Add cardinalities.
Add attributes.
Note: An entity typically must have at least one non-key attribute. (Unless it’s an associative entity.)