Study your flashcards anywhere!
Download the official Cram app for free >
 Shuffle
Toggle OnToggle Off
 Alphabetize
Toggle OnToggle Off
 Front First
Toggle OnToggle Off
 Both Sides
Toggle OnToggle Off
 Read
Toggle OnToggle 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
90 Cards in this Set
 Front
 Back
DBMS capabilities (3)

Persistant storage
Programming interfaces Transaction management 

DBMS should allow (4)

schemas
queries preserves large amounts of data many users 

Wrote a paper describing “relations” in 1970

Ted Codd


DDL

datadefinition language


DML

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)

Data
Metadata Statistics Indexes 

What does ACID stand for?

atomicity
isolation durability consistency 

Parts of the query compiler (3)

query parser
query preprocessor (semantic checks, algebraic tree) query optimizer 

Transaction processing tasks (3)

Logging
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 Btree is much more efficient than a binarytree 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)

Keys
Singlevalue constraints Referential integrity constraints Domain constraints General contstraints 

Wrote a paper "The EntityRelationship Model  Toward a Unified View of Data" in 1976

Peter Chen


Give two examples of singlevalue constraints

keys
manyone 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 nullvalue 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?

singlevalue 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 viceversa 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 manymany 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 manyone 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 oneone relationship?

The new relation uses the key from either connected entity, it does not have it's own.


What are Armstrong's Axioms?

Reflexivity
Augmentation Transitivity 

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 Pseudotransitivity 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?

Redundancy
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 nonkey attributes must depend on the primary key.


What is 3rd Normal Form?

2NF
left side must be a superkey or the right side must be a member of some key. 

What is BoyceCodd 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 twoattributes 

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)

Union
Difference Selection Projection 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 thetajoin?

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 tuplebased 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 foreignkey 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 foreignkey constraints?

CREATE TABLE Sells (
bar CHAR(20), beer CHAR(20), price REAL, FOREIGN KEY(beer) REFERENCES Beers(name) ON DELETE SET NULL ON UPDATE CASCADE ); 

What is the symbol for a single letter wild card in a like statement.

_ (underscore)


What are the 3 most common interpretations of NULL?

Unknown
Inapplicable Witheld 

The arithmetic result when NULL is an operands is always...

NULL


The boolean result when NULL in an operand is always...

UNKNOWN


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 multirelation queries?

Nested loops
Parallel assignment Conversion to relational algebra 

What is the SQL term for relational subtraction?

EXCEPT


What is the syntax for the 4 operators involving relations?

EXISTS R
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?

SELECT *
FROM branch_book_list WHERE branch_name = 'Branch A' EXCEPT SELECT * FROM branch_book_list WHERE branch_name = 'Branch B' 

What is the SQL for cartesian product?

CROSS JOIN


What is the SQL for an outerjoin?

NATURAL FULL OUTER JOIN


How would you tell the DBMS to use bags on a UNION instead of the default sets?

UNION ALL


How can you handle circular constraints?

By deferring the constraint check by temporarily using NULLs.
