DB- CH6
Relational DB is further divided into
1. Mathematical theory 2. RDB
Q. What are the two types of formal languages in relational model?
1. Relational Algebra 2. Relational Calculus
Relational Calculus is further divided into
1. Tuple RC: varialbles range over tuples 2. Domain RC: variables range over domain of attributes
What are the relational DB Set Operations
1. UNION 2. INTERSECTION 3. SET DIFFERENCE 4. CARTESION PRODUCT
RDB operations
1. Unary : applied to single relation 1.1 SELECT 1.2 PROJECT 2. BINARY: applied to more then one relation 2.1 JOIN
Sequence of operations & Rename operations
1. inline expression 2. Naming intermediate Relation 3. Renaming intermediate Relation 4. Renaming operation
Partially compatible
All tuples from both relations included in the result. The attributes that are type compatible are represented once in the result, and those attributes which are not compatible from either relation are also kept in the result.
Generalized projection
Allows functions of attributes to be included in the projection list
Define a tuple variable in a formula as free or bound.
Any variable that is not bound by a quantifier is said to be free
Relational algebra
Basic set of operations for the relational model. The result of these operations produces new relations. They also produce database query
Any retrieval that can be specified in basic relational algebra
Can also be specified in relational calculus
Aggregate functions and grouping
Common functions applied to collections of numeric values (SUM, COUNT, AVERAGE, MAXIMUM, and MINIMUM).
NATURAL JOIN
Denoted by *, Removes second (unnecessary) attribute in an EQUIJOIN condition, A normal inner join, but using the join condition that attributes with the same names should be equal.
JOIN Operation
Denoted by , Combine related tuples from two relations into single "longer" tuples. General join condition of the form <condition> AND <condition> AND...AND <condition>
The DIVISION Operation
Denoted by ÷, Example: retrieve the names of employees who work on all the projects that 'John Smith' works on, Apply to relations R(Z) ÷ S(X). Attributes of R are a subset of the attributes of S
Truth value of an atom
Evaluates to either TRUE or FALSE for a specific combination of tuples
Additional R O's
Generalized projection Aggregate functions
Relational Calculus
Higher-level declarative language for specifying relational queries
Expression is safe
If all values in its result are from the domain of the expression
SELECT in SQL
In SQL the SELECT condition is specified in the WHERE clause
CARTESIAN PRODUCT
It creates tuples with combined attributes of two relations. In SQL known as CROSS PRODUCT or CROSS JOIN, Denoted by ×, Binary set operation. Relations do not have to be union-compatible. Useful when followed by a selection that matches values of attributes
B R Operations:
JOIN and DIVISION
Outer joins
Keep all tuples in R, or all those in S, or all those in both relations regardless of whether or not they have matching tuples in the other relation
Types
LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN
Formula (Boolean condition)
Made up of one or more atoms connected via logical operators AND, OR, and NOT
UNION, INTERSECTION, and MINUS
Merge the elements of two sets in various ways,
Degree
Number of attributes in <attribute list> and same as the degree of R.
EQUIJOIN
Only = comparison operator used. Always have one or more pairs of attributes that have identical values in every tuple
SELECT
Operation Subset of the tuples from a relation that satisfies a selection condition sigma<select condition>(R) sigma = presents SELECT <select condition> = boolean expression R= relational algebra expression whose result is a relation Boolean expression contains clauses of the form <attribute name> <comparison op> <constant value> or <attribute name> <comparison op> <attribute name>. These are joined by AND, OR, NOT So the result coming from both Select and R both have the same attribute. <selection condition> applied independently to each individual tuple t in R. If condition evaluates to TRUE, tuple selected
Guaranteed to yield a finite number of tuples as its result
Otherwise expression is called unsafe
SET DIFFERENCE (or MINUS)
R - S, Includes all tuples that are in R but not in S
INTERSECTION
R ∩ S, Includes all tuples that are in both R and S
UNION
R ∪ S, Includes all tuples that are either in R or in S or in both R and S. Duplicate tuples eliminated
Tuple variables
Ranges over a particular database relation
Binary operations
Relations must have the same type of tuples
Query tree
Represents the input relations of query as leaf nodes of the tree, Represents the relational algebra operations as internal nodes
PROJECT Operation
Selects columns from table and discards the other columns:
Relational algebra expression
Sequence of relational algebra operations
R A Operations from
Set Theory
A Complete Set of R A O's
Set of relational algebra operations {σ, π, ∪, ρ, -, ×} is a complete set. Any relational algebra operation can be expressed as a sequence of operations from this set
Declarative expression
Specify a retrieval request nonprocedural language
Satisfy COND(t):
Specify: Range relation R of t Select particular combinations of tuples Set of attributes to be retrieved (requested attributes)
The OUTER UNION Operation
Take union of tuples from two relations that have some common attributes Not union (type) compatible
Join attribute
The attribute in both tables used for NATURAL JOIN.
Inner joins
Type of match and combine operation. Defined formally as a combination of CARTESIAN PRODUCT and SELECTION
Cascade SELECT
always combines a sequence. operations into a single operation with AND condition
Recursive Closure Operations
applied to a recursive relationship between tuples of same type
Rename
attributes in intermediate results. In SQL we use word AS in SELECT and FROM clause
Mathematical theory
is set of operations which r applicable bcz each relation is defined as a set of tuples in the formal RM.
Relational Calculus based on
predicate calculus
{T | R(T)}
returns all tuples T such that T is a tuple in relation R.
SELECT operation commutative*
sequence of SELECT can be applied in any order.
OPERATION in SQL
we use DISTINCT key word to get unique values.
THETA JOIN
with a general join condition. Each <condition> of the form Ai θ Bj, Ai is an attribute of R, Bj is an attribute of S, Ai and Bj have the same domain, θ (theta) is one of the comparison operators:{=, <, ≤, >, ≥, ≠} Tuples with Null or false don't appear.
In-line expression:
writing single algebra expression
A logical language with variables ranging over tuples:
{T | Cond} Returns all tuples T that satisfy the condition Cond.
Expressions and Formulas in Tuple Relational Calculus
{T.name | DEPARTMENT(T) AND T.Dname=′ Research′}. Returns the values of name of all department tuples with the value of 'Research'. The variable T is said to be free since it is not bound by a quantifier (for all, exists). Find all possible instances of T that make this statement true.
Universal quantifier (∀)
∀ T ∈ R(Cond) will succeed if Cond succeeds for all tuples in T.
If F is a formula (boolean condition), then so are (∃ t)(F) and (∀ t)(F), where t is a tuple variable.
∀ is called the universal or "for all" quantifier because every tuple in "the universe of" tuples must make F true to make the quantified formula true. ∃ is called the existential or "there exists" quantifier because if any (at least one) tuple exists in "the universe of" tuples that makes F, then the quantified formula is true.
Existential quantifier (∃)
∃ T ∈ R(Cond) will succeed if Cond succeeds for at least one tuple in T.