Reading...
Play button
Play button
Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
96 Cards in this Set
- Front
- Back
|
What is a set?
|
A collection of unordered distinct (no duplicates) objects
|
|
|
What is the union of two sets?
|
combining the sets (remember no duplicates!)
|
|
|
What is the intersection of two sets?
|
The set that contains the values that are in both sets
|
|
|
What is a domain?
|
a set of values of some type that you define
|
|
|
What is the Cartesian product?
|
all possible pairings (order matters!)
|
|
|
What is a relation?
|
a subset of the Cartesian product of domains
|
|
|
How do get the values of a relation?
|
You filter the Cartesian products of the domains by the relation
|
|
|
What is a tuple?
|
A list of values
|
|
|
What's the syntax for a relation?
|
relationName ( listOfValuesRelationLooksAt )
|
|
|
What is an instance of a schema?
|
the schema populated with some data
|
|
|
What is the difference between a schema and a function?
|
a function is a miniworld : 1 mapping and a relation is a miniworld : miniworld mapping
|
|
|
What is Cardinality?
|
the number of items in a set (the number of rows in a table)
|
|
|
What is degree?
|
the number of domains in a relation (the number of columns in a table)
|
|
|
What are the three properties of a table?
|
1 )the order of columns DOES matter in a table
2) the order of rows in a table does not matter 3) values in a table must be atomic |
|
|
What is a predicate statement?
|
a logic statement that evaluates to true or false
|
|
|
What is an integrity constraint
|
a predicate that must evaluate to true for every possible database state
|
|
|
Where do you specify integrity constraints?
|
the schema
|
|
|
When are integrity constraints checked?
|
when you update a database
|
|
|
What are the potential remedies for failed integrity constraints?
|
1) cancel update
2) allow update, but warn user of violated integrity constraint 3) let the system repair it (you define how the system repairs it) 4) specify special purpose error handling |
|
|
What constitutes a good integrity constraint?
|
simple, efficient, and quick to enforce
|
|
|
What is a key?
|
a unique index that can identify each row of data values in a table
|
|
|
What are some characteristics of a primary key?
|
1) can only have one in a table
2) a primary key cannot have a null value |
|
|
What is a foreign key?
|
a primary key from a different table added as a regular field to the current table
|
|
|
What is a key constraint?
|
a superkey; a set of attributes that cannot have duplicate values (example: two people can't have the same name)
|
|
|
What is referential integrity?
|
an integrity constraint that says foreign keys must point to valid information
|
|
|
How do you determine if a key is a candidate key?
|
you take away all possible combinations of keys (without leaving it blank), and if the key equals another key, it is not a candidate key
|
|
|
What are some good things to know about keys?
|
1) some DBMS impose that a key must be a single attribute
2) some DBMS impose a restriction that keys must be an integer 3) it is difficult to undo changes in keys once the database is implemented; choose your keys wisely! |
|
|
I have two tables, R1 and R2. R1 has a foreign key that points to R2.
When are the four times when referential integrity is checked? |
1) when you insert into R2
2) when you update R2 3) when you delete from R1 4) when you update R2 |
|
|
What is entity integrity?
|
every key value must be non-null
|
|
|
what is a domain constraint?
|
a value in a column must come from the domain
|
|
|
What is an assertion?
|
a general integrity constraint (meaning, you define your own constraint, you assert something is true)
|
|
|
What is a trigger?
|
an event condition action (when an event occurs, if the condition is met (optional), then you do an action
|
|
|
What is the difference between an integrity constraint and triggers and assertions?
|
integrity constraints are always in force, and you have to work around them if you need to. Assertions and triggers are things you get to define and have full control over
|
|
|
What are the two types of Queries?
|
declarative, procedural
|
|
|
What are declarative queries?
|
you give a specification of the answer you want and the dbms returns the data that fits that criteria
|
|
|
What are procedural queries?
|
a precise sequence of steps the dbms takes to get the data
|
|
|
What type of queries are the basis for all relational DBMS implementations?
|
procedural
|
|
|
What is a selection query?
|
return all the elements in the relation that match the boolean expression specified
|
|
|
I have the following projection query: PIname(Student). What gets returned?
|
all the names of all the students, doing duplicate elimination
|
|
|
What is a join query?
|
gives the cartesian product of two relations, then filters the set by some sort of filter you specify
|
|
|
What are three types of joing?
|
-theta join (any condition)
-equi join (theta is built of equality comparison and boolean AND) -natural join (removes duplicate columns in the join) |
|
|
What is the difference between selection and projection?
|
selection returns all columns, and only rows that match restriction, which projection returns all rows, but only the columns specified in the projection
|
|
|
What is the minimum cardinality of a selection query?
|
0
|
|
|
what is the maximum cardinality of a selection query?
|
the number of rows in the table
|
|
|
what is the degree of a selection query?
|
the number of columns you started with
|
|
|
What is the minimum cardinality of a projection query?
|
the minimum of: the number of rows in the table and 1
|
|
|
What is the maximum cardinaltiy of a projection query?
|
the number of rows in a table
|
|
|
What is the degree of a projection query?
|
the number of columns you specify in the projection query
|
|
|
What is the minimum cardinality and maximum cardinality of a cartesian product query?
|
the number of rows in table 1 * the number of rows in table 2
|
|
|
What is the degree of a cartesian product query?
|
the number of columns in table 1 + the number of columns in table 2
|
|
|
What is the minimum cardinality of a join query?
|
0 (none could match theta)
|
|
|
what is the maximum cardinality of a join query?
|
the # of rows in table 1 * the number of rows in table 2
|
|
|
What is the degree of a join query?
|
the number of columns in table 1 + the number of columns in table 2
|
|
|
What are the three relational queries?
|
1) union
2) intersect 3) take away |
|
|
What is the union query?
|
the set that consists of all tuples in both relations (no duplicates)
|
|
|
What is the intersect query?
|
the set that consists of all tuples that is in both relations
|
|
|
What is the take away query?
|
the set that consists of all tuples that are in the first set, but not the second
|
|
|
What condition has to be met to be able to perform the relational queries?
|
they have to be union compatible
|
|
|
What does it mean to be union compatible?
|
They have to be the same degree and they have to be the same domains
|
|
|
What is the Rho query?
|
changes the name of the relation passed in to NAME.RELATION
|
|
|
What is the minimum cardinality of a natural join?
|
0
|
|
|
What is the maximum cardinality of a natural join?
|
the max of both of the relations
|
|
|
What is the degree of a natural join?
|
max(degree of both columns) <= j <= the number of columns of both relations added together
|
|
|
What is the minimum cardinality of an equi join?
|
0
|
|
|
What is the maximum cardinality of an equi join?
|
number of rows of both relations multiplied together
|
|
|
What is the degree of an equi join?
|
the number of columns in both relations added together
|
|
|
What is the minimum cardinality of a union query?
|
0 (you could be doing the union of two empty relations)
|
|
|
What is the maximum cardinality of a union query?
|
number of rows in both relations added together
|
|
|
What is the degree of a union query, intersect query, and take away query?
|
must be same degree to do the union in the first place, so the same as when the query started
|
|
|
What is the minimum cardinality of a intersect query?
|
0
|
|
|
What is the maximum cardinality of an intersect query?
|
the max of the number of rows in both of the relations
|
|
|
What is the minimum cardinality of a take away query?
|
0
|
|
|
What is the maximum cardinality of a take away query?
|
the max of the number of rows in both of the relations
|
|
|
Relational Algebra is closed. What does this mean?
|
you can't get something that's not a relation when you run two relations through an operator
|
|
|
The following set of operations are complete (intersect, cartesian product, select, project, take away). What does this mean?
|
all other operations are a subset of these operations
|
|
|
What is a semi join? What is the difference between left and right?
|
a semi join is a projection of the natural join. You project either the left relation or the right relation, depending on whether it is a left join or a right join
|
|
|
What is the degree of the division operator?
|
number of columns in left relation minus the number of columns in the right relation
|
|
|
What is the minimum cardinality of a division operation?
|
0
|
|
|
What is the maximum cardinality of a division operation
|
the floor of the number of rows in the left relation divided by the number of rows in the right relation
|
|
|
What is the first limitation of relational algebra?
|
can't modify data (can't change schema)
|
|
|
What is the second limitation of relational algebra?
|
can't modify data values
|
|
|
What is the third limitation of relational algebra?
|
can't aggregate (counting, etc.)
|
|
|
What is the fourth limitation of relational algebra?
|
can't handle missing data
|
|
|
What is the fifth limitation of relational algebra?
|
no loops/recursion (transitive closure)
|
|
|
What is the sixth limitation of relational algebra?
|
can't sort
|
|
|
What operator, added to SQL, handles limitation two of relational algebra (can't modify values?)
|
generalized projection
|
|
|
What is generalized projection?
|
can add math to projected column
|
|
|
Explain what a left outer join is?
|
a left outer join is a natural join that retains all of the records on the "left" relation, filling in missing data will null
|
|
|
What is a right outer join?
|
a right outer join is a natural join that retains all of the records on the "right" relation, filling in missing data with null
|
|
|
What is a full outer join?
|
the union of the left outer join and the right outer join of two relations
|
|
|
How does a basic aggregate work (no groups)?
|
computes the aggregate, and adds a new column with the final aggregate value in each row
|
|
|
What are the five aggregate operators you can use?
|
mix, max, avg, count, sum
|
|
|
What changes in aggregation when you add groups in?
|
you apply the aggregate operator to each separate group (specified by the attribute you choose). The return result is the same as without groups, but each group will have its aggregate value in the column
|
|
|
What assumptions are made in the division operator?
|
1) the left relation's degree is < the right relations's degree
2) the right relation is a subset of the left relation |
|
|
What does the division operator do?
|
if each unique tuple in R - domains in S is paired with every item in S, add it to the return set
|
|
|
What are all the different operations?
|
-selection
-projection -take away -the joins (theta, natural, equi) -left and right semi join -union -intersection -left, right, and full outer joins, -aggregation -division |
