Chapter 2: Relational Model Flashcards
What is the language of the Relational Model?
Relational Algebra
Why is it simple to access information within a relational model?
It is easy to access information because the Relational Model is built as a grid (table)
How do the operators act in Relational Algebra?
They act horizontally and vertically.
Should all attributes in a relation be atomic or composite and why?
Atomic so everything will be in first normal form. Having attributes being composite leads to issues later down the line.
What is null?
A special value that is an unknown value.
What is a relation (mathematical definition)?
A subset of the complete cartesian product of a set of n-tuples.
What is the domain of the attribute?
a set of allowed values for each attribute
What are attribute values (normally) required to be?
atomic
When is a domain atomic?
When all of its members are atomic
What value is a member of every domain?
null
What is a database (in context of relational models)?
It is a set of tables and relations between tables.
What is the purpose of foreign keys?
They serve as identifiers to the parent.
What must children do in relations and RDB?
The child must know who the parent is in RDB.
What is a foreign key?
The parent’s primary key that the child is holding.
What does the relational algebra select operator do?
It chooses rows/tuples
- operates horizontally
- SQL equivalent: WHERE
What does the relational algebra project operator do?
Displays the contents of a column
- operates vertically
- SQL equivalent: SELECT
What does the relational algebra union operator do?
brings tables together
- operates horizontally
- SQL equivalent: UNION
- UNION ALL gives you a multi-set output
What does the relational algebra set difference operator do?
Removes the items that relation 1 has in common with relation 2 and displays relation 1 after this
- operates horizontally
- relations must be compatible
- SQL equivalent: EXCEPT, MINUS
What does the relational algebra cartesian product operator do?
Joins two tables together
- operates horizontally and vertically
- SQL equivalent: , (a comma)
What does the relational algebra rename operator do?
Renames an attribute within a query
- does not operate horizontally or vertically
- SQL equivalent: AS
What is a set operation?
An operation that only works on sets. They also automatically eliminate all duplicates in the output.
What are the attributes that sets must have?
- uniqueness (no duplicates)
2. no order
Do set operations produce duplicates?
no
What makes relations compatible?
- they have the same arity (number of attributes)
- attribute domains of both relations must be compatible
What relational algebra operations are set operations?
- cartesian-product
- set difference
- intersect
- union
What functions the same as a join operation?
select on a cartesian product of two relations in SQL this looks like SELECT something FROM r,s WHERE r.value=s.value
What does the rename operation do?
Renames the local instance of a specific value, the rename only occurs within the query
What is the rename operation in SQL
AS
What is set difference?
Everything that is common within two relations is removed.
What is the symbol for set difference in relational algebra?
-
What is the representation of set difference in SQL?
EXCEPT
What is intersect in relational algebra and SQL?
Set operation that returns what is common between two sets.
- relational algebra: mathematical symbol for intersect
- sql: INTERSECT
What is natural join?
A join operation that has two implicit conditions:
1) schema for the joined relations must share at least one attribute
2) the relations must share the same values in the common values
What is returned by natural join?
r U s with all of the elements in r followed by all element in s w/o repeats
-this is done row by row
How is natural join written in SQL
NATURAL JOIN
How does the division operation work?
when there is a common column between two relations the following steps are done
1) When the denominator column is found within the numerator relation, remove the denominator column in the numerator relation
2) Collect the unique set of rows remaining in the numerator
3) Take the numerators remaining values and for each value in the unique numerator (result of step 2) go through original numerator (state before step 1) and match unique numerator values with the original numerator values, if the values within the divisor are all present within the original numerator (with none missing and no extra i.e. [Column A, Column B] -> (alpha, 1) (alpha, 2) in ogNumerator and denominator is [Column B] -> (1) (2)) return the unique numerator with that value.
What is generalized projection?
It means you can project not only values but the results of functions as well.
What are aggregate functions and operations?
Functions and operations that summarize what is within a set
What are some common aggregate functions?
avg (returns average of value) min (returns minimum value) max (returns maximum value) sum (returns the sum of values) count (returns the number of values, doesn't ignore null but rest do)
What is an aggregate operation?
GROUP BY
How is group by structured
groupByClause G aggregateFunction(table)
2 G 3(1)
-groupByClause is optional
What is the group by clause if no group by clause is declared?
The entire table that the clause is being utilized on.
What is outer join?
Returns the result of an inner join (natural join) as well as the values that have no match
- non-matched values are in a tuple with null, i.e. (5, null, null, …)
- can be broken down into left, right and full
What is the difference between left, right, and full outer join?
left outer join = natural inner join + values from left table
right outer join = natural inner join + values from right table
full outer join = natural inner join + values from left table + values from right table
How is null accounted from in boolean algebra?
Instead of defaulting to T and F, it can also be null
and/or T F null
T T/T F/T null/T
F F/T F/F null/F
null null/T null/F null/null
What are the database modification operations?
DELETE (operates on rows)
INSERT (operates on rows)
UPDATE (operates on columns)
What is the SQL for deletion?
DELETE
FROM table
WHERE f(x)
What is the SQL for insertion?
INSERT
INTO table
VALUES (…, …., …)
What is the SQL for update?
UPDATE table SET f(x) WHERE g(x)
