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

90 Cards in this Set

  • Front
  • Back
DBMS capabilities (3)
Persistant storage
Programming interfaces
Transaction management
DBMS should allow (4)
preserves large amounts of data
many users
Wrote a paper describing “relations” in 1970
Ted Codd
data-definition language
data manipulation language
Wrote a 1996 paper "Evolution of Data Management"
Jim Gray
What is a query plan?
Sequence of actions sent from the compiler to the execution engine
What are durable transactions?
Completed only when a crash will not destroy them.
Information components in a database (4)
What does ACID stand for?
Parts of the query compiler (3)
query parser
query preprocessor (semantic checks, algebraic tree)
query optimizer
Transaction processing tasks (3)
Concurrency control
Deadlock resolution
Slow storage of huge amounts of data. (Maybe using an arm to swap DVDs)
tertiary storage
Type of tree indexes use.
A B-tree is much more efficient than a binary-tree search.
Conditions for replacing an entity set with an attribute.
Only "one" relations on E
No dependencies between E's attributes.
No relationship involves E more than once.
Constraint Classes (5)
Single-value constraints
Referential integrity constraints
Domain constraints
General contstraints
Wrote a paper "The Entity-Relationship Model - Toward a Unified View of Data" in 1976
Peter Chen
Give two examples of single-value constraints
many-one relationships
Referencial integrity constraints
Ensure that a reference must exist.
Multiplicity Constraints
Look this up....
How is a Weak Entity Set Notated. (5)
The Weak Entity Set has a double box around it.
Any supporting relationships have a double diamond around them.
Supporting relations must have referential integrity.
Underlined attributes on a weak entity set participate in but are not the complete key.
A weak entity set can have multiple relationships with another entity.
What is the name for the value in a column of a tuple?
A component.
What factors should be considered when choosing between ER, OO, and null-value approaches.
Would a query involve multiple relations? Are there more relations than we want to manage? Are we wasting space?
A functional dependency is what kind of restraint?
single-value restraint
What is the difference between a key and a superkey?
A key is the minimal attributes needed to identify a unique tuple. Every key is a superkey. A superkey can also have extra attributes.
What is the primary key?
A primary key is the default key when there are more than one keys.
What is the difference between a minimal and minimum key?
Minimal = nothing can be thrown out.
Minimum = smallest possible
What is the Object oriented approach?
Each relation clones all of the attributes of the parent.
What is the straight E/R approach?
Each relation includes the parent's key. The data is split across the relations.
What is the null value approach
All entities in one big relation where some values are empty or null.
What is the splitting/combining rule?
If two functional dependecies have the same attributes on the left, the attributes on the right can be combined, and vice-versa for the splitting rule.
Trivial dependencies are?
When the B's are a subset of the A's
Nontrivial dependencies are?
When at least one of the B's is not among the A's.
Completely nontrivial dependencies are?
When no B's are A's. When there is only one B, this is the same as a nontrivial dependencies.
How do you determine the key for a relation based on a many-many relationship?
The keys of both connected entity sets are the key attributes of R.
How do you determine the key for a relation based on a many-one relationship?
Use the key from the entity on the "many" side but not the "one" side.
How do you determine the key for a relation based on a one-one relationship?
The new relation uses the key from either connected entity, it does not have it's own.
What are Armstrong's Axioms?
What is Reflexivity?
The A's can always determine a subset of themselves. (trivial FDs)
What is Augmentation?
We can add something to the right side (B's) if we also add it to the left side (A's).
What is Transitivity?
If A->B and B->C then A->C.
What is the Union rule?
If X->Y and X->Z then X->YZ
What is the Decomposition rule?
If X->YZ then X->Y and X->Z
What is the Pseudo-transitivity rule?
If X->Y and YZ->U then XZ->U
What is a "basis" for a relation?
A minimal set of FD's that determine the complete set of FD's. (There can be more than one)
What are the principle kinds of anomalies that occur when too much information is in one relation?
Update anomalies
Delete anomalies
What is 1st Normal Form?
Atomic attribute values, and each row has the same columns.
What is 2nd Normal Form?
1NF plus all non-key attributes must depend on the primary key.
What is 3rd Normal Form?
left side must be a superkey or the right side must be a member of some key.
What is Boyce-Codd Normal Form?
The left side of the FD must be a superkey. Trivial FD's are allowed.
What is 4th Normal Form?
The left side of the MVD must be a superkey. Trivial MVD's are allowed.
How do you find the closure of functional dependencies?
S+ =
Loop foreach f in S, apply reflexivity and augment.
add the new FDs to S+ foreach pair of FDs in S, apply the
transitivity rule
add the new FD to S+
Until S+ does not change any further
What is the BCNF decomposition algortihm?
Input: relation R, set S of FDs over R
1) Compute S+
2) Compute keys for R (from ER or from S+)
3) Use S+ and keys to check if R is in BCNF, if not:
a) pick a violation FD f: A  B
b) expand B as much as possible, by computing A+
c) create R1 = A union B,R2 = A union (others in R)
d) compute all FDs over R1, using R and S+,
then compute keys for R1.
Repeat similarly for R2
e) Repeat Step 3 for R1 and R2
4) Stop when all relations are BCNF, or are two-attributes
What do you call an attribute that is a member of a key?
A prime attribute.
What is object relational mapping?
A way to simplify SQL statements by putting data into objects. Done by frameworks.
What are the basic operations in relational algebra? (5)
Cartesian Product
Who initially proposed relation algebra as an algebra on sets of tuples.
T. Codd
What is a dangling tuple?
A tuple that fails to pair with the second relation in a natural join.
How do you express the intersection of R and S using only the 5 basic operations?
R - (R - S)
How do you express assignment statements?
T(A,B,C,D) := Project on L(Selection on c(R X S))
Answer(title, year) := Projection on a,b(T)
What is a natural join?
For each tuple in the first set, a new tuple is created in the result for each tuple in the second set which matches on identical attributes.
What is a theta-join?
A cartesian product followed by a selection.
What does the duplicate elimination operator look like?
Greek d?
What does the grouping operator look like?
Dentist pick with a bayonet?
What does the sorting operator look like?
A little hammer? Greek t?
What is the precedence for relational operators?
1. Unary operators (select, project, rename)
2. Products and joins
3. Intersection
4. Union and set difference
Union on bags does what with duplicate values?
Adds the number of occurrences.
Difference on bags does what with them?
Subtracts the number of occurrences.
Intersection on bags does what with them?
Produces the minimum of the number of occurrences.
What do selection and projection do to bags?
Preserve the original number of duplicates.
What do Cartesian product and join do to bags?
No elimination of duplicates.
What is an assertion?
any SQL boolean expression
What is a tuple-based constraint?
relationship among components
How is a foreign key specified?
FOREIGN KEY (<list of attributes>) REFERENCES (<attributes>)

The referenced attributes must be declared primary key or unique.
How can you enforce foreign-key restraints? (3 ways)
Default: Reject modifications
Cascade: Make changes to all tuples with the key
Set NULL: For a changed key, set referencing tuples to NULL
What is the syntax for foreign-key constraints?
bar CHAR(20),
beer CHAR(20),
price REAL,
REFERENCES Beers(name)
What is the symbol for a single letter wild card in a like statement.
_ (underscore)
What are the 3 most common interpretations of NULL?
The arithmetic result when NULL is an operands is always...
The boolean result when NULL in an operand is always...
How do you do boolean operations where a NULL is involved?
AND is the Min
OR is the Max
What are the 3 ways to interpret multi-relation queries?
Nested loops
Parallel assignment
Conversion to relational algebra
What is the SQL term for relational subtraction?
What is the syntax for the 4 operators involving relations?
s IN R
s > ANY
s > ALL
What do you call it when a subquery gets evaluated multiple times?
Correlated subqueries
How is EXCEPT used?
FROM branch_book_list
WHERE branch_name = 'Branch A'
FROM branch_book_list
WHERE branch_name = 'Branch B'
What is the SQL for cartesian product?
What is the SQL for an outerjoin?
How would you tell the DBMS to use bags on a UNION instead of the default sets?
How can you handle circular constraints?
By deferring the constraint check by temporarily using NULLs.