Study your flashcards anywhere!

Download the official Cram app for free >

  • Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off

How to study your flashcards.

Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key

Up/Down arrow keys: Flip the card between the front and back.down keyup key

H key: Show hint (3rd side).h key

A key: Read text to speech.a key


Play button


Play button




Click to flip

35 Cards in this Set

  • Front
  • Back
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).
Used to end a successful transaction and make changes “permanent”
Used to “undo” changes from an aborted transaction
May be done automatically when failure occurs
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.
Concurrency control
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
Size of data items chosen as unit of protection by concurrency control protocol.
Ranging from coarse to fine:
Entire database.
Page (block)
Field value of a record
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.
Point of synchronization between database and log file. All buffers are force-written to secondary storage.
What are the purposes of "commit" and "rollback" in transaction processing?
At commit, check is made to determine whether conflict has occurred.
If there is a conflict, transaction must be rolled back and restarted.
Describe the two valid outcomes of a transaction.
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.
Name and describe the ACID properties of transactions.
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.
What three criteria must be met in order for two operations to be considered conflicting?
1) are from different transactions
2) access the same item
3) at least one of the transactions does a write operation to that item
Contrast serial and serializable transactions
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
What mechanism is used to ensure serializability?
Locking mechanism
Lost Update, example
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.
Temporary update, example
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.
Incorrect summary,example
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.
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.
Rollback points, rollforward points
List six items that a log file transaction record should contain.
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.
Requirements for 1NF
Every relation has a unique name.
Every attribute value is atomic (single-valued).
Every row is unique.
Attributes in tables have unique names.
Requirements for 2NF
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.
Requirements for 3NF
2NF and no transitive dependencies
Transitive dependency: a functional dependency between two or more non-key attributes.
Requirements for BC-NF
3NF and every determinant is a candidate key.
Describe the basic problem data warehousing is intended to address.
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
Define data warehouse and data warehousing.
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)
Name four characteristics of data warehousing.
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)
Describe the three-tier data warehousing architecture.
External Data sources, ERP, other internal data sources > Data Warehouse > Reporting & analysis tools
Contrast data warehouses and data marts.
Data marts are small-scale, specialized, segmented data warehouses
Contrast fact and dimension tables.
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)
Name and briefly describe the four steps in data warehousing.
Identify source data systems
Design the data warehouse
E/T/L process
DW utilization by end-users
Briefly describe four threats to data security.
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).
Contrast authentication and authorization.
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.
Name and briefly describe four computer-based countermeasures to security threats.
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.
Name and briefly describe three non-computer-based countermeasures to security threats
Security policy and contingency plan
Personnel controls
Secure positioning of equipment
Escrow agreements
Maintenance agreements
Physical access controls
Given a narrative and/or set of forms and reports, be able to produce an ER diagram.
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.)