Transcript Revision for Midterm 3 revision 3 Prof. Sin-Min Lee Department of Computer Science

Revision for Midterm 3 revision
3
Prof. Sin-Min Lee
Department of Computer Science
Set Operators
• Relation is a set of tuples, so set operations
should apply: , ,  (set difference)
• Result of combining two relations with a set
operator is a relation; hence all its elements
must be tuples having the same structure
• Hence, scope of set operations limited to
union compatible relations
Union Compatible Relations
• Two relations are union compatible if
– Both have same number of columns
– Names of attributes are the same in both
– Attributes with the same name in both relations
have the same domain
• Union compatible relations can be
combined using union, intersection, and set
difference
Example
Tables:
Person (SSN, Name, Address, Hobby)
Professor (Id, Name, Office, Phone)
are not union compatible.
But
 Name (Person) and  Name (Professor)
are union compatible so
 Name (Person)
makes sense.
-
 Name (Professor)
Cartesian Product
• If R and S are two relations, R  S is the set of all
concatenated tuples <x,y>, where x is a tuple in R and y is
a tuple in S (but see naming problem next)
• R  S is expensive to compute:
– Factor of two in the size of each row
– Quadratic in the number of rows
A B
x1 x2
x3 x4
C D
y1 y2
y3 y4
R
S
A
x1
x1
x3
x3
B C
x2 y1
x2 y3
x4 y1
x4 y3
R S
D
y2
y4
y2
y4
Renaming in Cartesian Product
Result of expression evaluation is a relation.
Attributes of relation must have distinct names.
So what do we do if they don’t?
E.g., suppose R(A,B) and S(A,C)
and we wish to compute R  S .
One solution is to rename the attributes of the answer:
R  S( R.A, R.B, S.A, S.C)
Although only A needs to be renamed, it is“cleaner” to
rename them all.
Renaming Operator
• Previous solution is used whenever possible but it
won’t work when R is the same as S.
• Renaming operator resolves this. It allows to
assign any desired names, say A1, A2,… An , to the
attributes of the n column relation produced by
expression expr with the syntax
expr [A1, A2, … An]
Example
Transcript (StudId, CrsCode, Semester, Grade)
Teaching (ProfId, CrsCode, Semester)
 StudId, CrsCode (Transcript)[StudId, CrsCode1]
  ProfId, CrsCode(Teaching) [ProfId, CrsCode2]
This is a relation with 4 attributes:
StudId, CrsCode1, ProfId, CrsCode2
Derived Operation: Join
A (general or theta) join of R and S is the expression
R
join-condition S
where join-condition is a conjunction of terms:
Ai oper Bi
in which Ai is an attribute of R; Bi is an attribute of S; and oper is
one of =, <, >,  , .
The meaning is:
 join-condition´ (R  S)
where join-condition and join-condition´ are the same, except for
possible renamings of attributes caused by the Cartesian product.
Theta Join – Example
Employee(Name,Id,MngrId,Salary)
Manager(Name,Id,Salary)
Output the names of all employees that earn
more than their managers.
Employee.Name (Employee
MngrId=Id AND Salary>Salary
Manager)
The join yields a table with attributes:
Employee.Name, Employee.Id,
Employee.Salary, Employee.MngrId
Manager.Name, Manager.Id, Manager.Salary
Relational Algebra
• Relational algebra operations operate on relations and
produce relations (“closure”)
f: Relation -> Relation
Relation -> Relation
• Six basic operations:
–
–
–
–
–
–
Projection
Selection
Union
Difference
Product
(Rename)
A (R)
 (R)
R1 [ R2
R1 – R2
R1 £ R2
A>B (R)
f: Relation x
Equijoin Join - Example
Equijoin: Join condition is a conjunction of equalities.
Name,CrsCode(Student
Id=StudId
Grade=‘A’ (Transcript))
Student
Id
111
222
333
444
Transcript
Name
Addr
Status
StudId
CrsCode Sem
Grade
John
Mary
Bill
Joe
…..
…..
…..
…..
…..
…..
…..
…..
111
222
333
CSE305 S00
CSE306 S99
CSE304 F99
B
A
A
Mary
Bill
CSE306
CSE304
The equijoin is used very
frequently since it combines
related data in different relations.
Natural Join
• Special case of equijoin + a special projection
– join condition equates all and only those attributes with the
same name (condition doesn’t have to be explicitly stated)
– duplicate columns eliminated (projected out) from the
result
Transcript (StudId, CrsCode, Sem, Grade)
Teaching (ProfId, CrsCode, Sem)
Teaching =
Transcript
StudId, Transcript.CrsCode, Transcript.Sem, Grade, ProfId
( Transcript
)
[StudId, CrsCode, Sem, Grade, ProfId ]
CrsCode=CrsCode AND Sem=Sem Teaching
Natural Join (cont’d)
• More generally:
R
S = attr-list (join-cond (R × S) )
where
attr-list = attributes (R)  attributes (S)
(duplicates are eliminated) and join-cond has
the form:
A1 = A1 AND … AND An = An
where
{A1 … An} = attributes(R)  attributes(S)
Natural Join Example
• List all Ids of students who took at least two
different courses:
StudId ( CrsCode  CrsCode2 (
Transcript
Transcript [StudId, CrsCode2, Sem2, Grade2] ))
We don’t want to join on CrsCode, Sem, and Grade
attributes, hence renaming!
Example Data Instance
STUDENT
Takes
sid
sid
exp-grade
name
COURSE
cid
cid
subj
sem
1
Jill
1
A
550-0103
550-0103
DB
F03
2
Qun
1
A
700-1003
700-1003
AI
S03
3
Nitin
3
A
700-1003
501-0103
Arch
F03
4
Marty
3
C
500-0103
4
C
500-0103
PROFESSOR
Teaches
fid
name
fid cid
1
Ives
1
550-0103
2
Saul
2
700-1003
8
Roth
8
501-0103
Natural Join and Intersection
Natural join: special case of join where  is
implicit – attributes with same name must be
equal:
STUDENT ⋈ Takes ´
STUDENT ⋈STUDENT.sid = Takes.sid Takes
Intersection: as with set operations, derivable
from difference
AB
A-B
A
B-A
B
Division
• A somewhat messy operation that can be
expressed in terms of the operations we
have already defined
• Used to express queries such as “The fid's
of faculty who have taught all subjects”
• Paraphrased: “The fid’s of professors for
which there does not exist a subject that
they haven’t taught”
Division Using Our Existing
Operators
• All possible teaching assignments: Allpairs:
fid,subj (PROFESSOR £ subj(COURSE))
• NotTaught, all (fid,subj) pairs for which
professor fid has not taught subj:
Allpairs - fid,subj(Teaches ⋈ COURSE)
• Answer is all faculty not in NotTaught:
fid(PROFESSOR) - fid(NotTaught)
´ fid(PROFESSOR) - fid(
fid,subj (PROFESSOR £ subj(COURSE)) fid,subj(Teaches ⋈ COURSE))
Division: R1  R2
• Requirement: schema(R1) ¾ schema(R2)
• Result schema: schema(R1) – schema(R2)
• “Professors who have taught all courses”:
fid (fid,subj(Teaches ⋈ COURSE)  subj(COURSE))
• What about “Courses that have been taught
by all faculty”?
Division
• Goal: Produce the tuples in one relation, r,
that match all tuples in another relation, s
– r (A1, …An, B1, …Bm)
– s (B1 …Bm)
– r/s, with attributes A1, …An, is the set of all
tuples <a> such that for every tuple <b> in s,
<a,b> is in r
• Can be expressed in terms of projection, set
difference, and cross-product
Division (cont’d)
Division - Example
• List the Ids of students who have passed all
courses that were taught in spring 2000
• Numerator:
– StudId and CrsCode for every course passed by every
student:
StudId, CrsCode (Grade ‘F’ (Transcript) )
• Denominator:
– CrsCode of all courses taught in spring 2000
CrsCode (Semester=‘S2000’ (Teaching) )
• Result is numerator/denominator
Relational Calculus
• Important features:
– Declarative formal query languages for relational model
– Based on the branch mathematical logic known as
predicate calculus
– Two types of RC:
• 1) tuple relational calculus
• 2) domain relational calculus
– A single statement can be used to perform a query
Tuple Relational Calculus
• based on specifying a number of tuple
variables
• a tuple variable refers to any tuple
Generic Form
• {t | COND (t)}
– where
– t is a tuple variable and
– COND(t) is Boolean expression involving t
Simple example 1
• To find all employees whose salary is
greater than $50,000
– {t| EMPLOYEE(t) and t.Salary>5000}
• where
• EMPLOYEE(t) specifies the range of tuple variable
t
– The above operation selects all the attributes
Simple example 2
• To find only the names of employees
whose salary is greater than $50,000
– {t.FNAME, t.NAME| EMPLOYEE(t) and
t.Salary>5000}
•
•
•
•
The above is equivalent to
SELECT T.FNAME, T.LNAME
FROM EMPLOYEE T
WHERE T.SALARY > 5000
Elements of a tuple calculus
• In general, we need to specify the following
in a tuple calculus expression:
– Range Relation (I.e, R(t)) = FROM
– Selected combination= WHERE
– Requested attributes= SELECT
More Example:Q0
• Retrieve the birthrate and address of the
employee(s) whose name is ‘John B. Smith’
• {t.BDATE, t.ADDRESS| EMPLOYEE(t)
AND t.FNAME=‘John’ AND t.MINIT=‘B”
AND t.LNAME=‘Smith}
Formal Specification of tuple
Relational Calculus
• A general format:
• {t1.A1, t2.A2,…,tn.An |COND ( t1 ,t2 ,…, tn,
tn+1, tn+2,…,tn+m)}
–
–
–
–
where
t1,…,tn+m are tuple var
Ai : attributeR(ti)
COND (formula)
• Where COND corresponds to statement about the
world, which can be True or False
Elements of formula
• A formula is made of Predicate Calculus atoms:
–
–
–
–
–
–
an atom of the from R(ti)
ti.A op tj.B op{=, <,>,..}
F1 And F2 where F1 and F2 are formulas
F1 OR F2
Not (F1)
F’=(t) (F) or F’= (t) (F)
•  Y friends (Y, John)
• X likes(X, ICE_CREAM)
•
Example Queries Using the
Existential Quantifier
• Retrieve the name and address of all
employees who work for the ‘ Research ’
department
• {t.FNAME, t.LNAME, t.ADDRESS|
EMPLOYEE(t) AND ( d)
(DEPARTMENT (d) AND
d.DNAME=‘Research’ AND
d.DNUMBER=t.DNO)}
More Example
• For every project located in ‘Stafford’,
retrieve the project number, the controlling
department number, and the last name,
birthrate, and address of the manger of that
department.
Cont.
• {p.PNUMBER,p.DNUM,m.LNAME,m.BD
ATE, m.ADDRESS|PROJECT(p) and
EMPLOYEE(M) and
P.PLOCATION=‘Stafford’ and ( d)
(DEPARTMENT(D) AND
P.DNUM=d.DNUMBER and
d.MGRSSN=m.SSN))}
Safe Expressions
• A safe expression R.C:
– An expression that is guaranteed to generate a
finite number of rows (tuples)
• Example:
– {t | not EMPLOYESS(t))} results values not
being in its domain (I.e., EMPLOYEE)
Examples
• Retrieve the birthdates and address of the
employee whose name is ‘John B. Smith’
• {uv| (q)(r)(s) (EMPLOYEE(qrstuvwxyz)
and q=‘John’ and r=‘B’ and s=‘Smith’
Alternative notation
• Ssign the constants ‘John’, ‘B’, and ‘Smith’
directly
• {uv|EMPLOYEE
(‘John’, ’B’, ’Smith’ ,t ,u ,v ,x ,y ,z)}
More example
• Retrieve the name and address of all
employees who work for the ‘Reseach’
department
• {qsv | ( z) EMPLOYEE(qrstuvwxyz) and
( l) ( m) (DEPARTMENT (lmno) and
l=‘Research’ and m=z))}
More example
• List the names of managers who have at
least on e dependent
• {sq| ( t) EMPLOYEE(qrstuvwxyz) and ((
j)( DEPARTMENT (hijk) and (( l) |
(DEPENTENT (lmnop) and t=j and t=l))))}
Relational Calculus: A Logical Way
of
Expressing Query Operations
• First-order logic (FOL) can also be thought of
as a query language, and can be used in two
ways:
– Tuple relational calculus
– Domain relational calculus
– Difference is the level at which variables are used:
for attributes (domains) or for tuples
• The calculus is non-procedural (declarative) as
compared to the algebra
– More like what we’ll see in SQL
– More convenient to express certain things
More Complex Predicates
Starting with these atomic predicates, build up new
predicates by the following rules:
– Logical connectives: If p and q are predicates, then so are
pq, pq, p, and pq
• (x>2) (x<4)
• (x>2)  (x>0)
– Existential quantification: If p is a predicate, then so is
x.p
• x. (x>2) (x<4)
– Universal quantification: If p is a predicate, then so is
x.p
• x.x>2
• x. y.y>x
Logical Equivalences
• There are two logical equivalences that will
be heavily used:
– pq  p  q
(Whenever p is true, q must also be true.)
– x. p(x)  x. p(x)
(p is true for all x)
• The second can be a lot easier to check!
Normalization
 Review on Keys
•
•
•
•
•
•
superkey: a set of attributes which will uniquely
identify each tuple in a relation
candidate key: a minimal superkey
primary key: a chosen candidate key
secondary key: all the rest of candiate keys
prime attribute: an attribute that is a part of a
candidate key (key column)
nonprime attribute: a nonkey column
Normalization
 Functional Dependency Type by Keys
‘whole (candidate) key  nonprime attribute’: full
FD (no violation)
• ‘partial key  nonprime attribute’: partial FD
(violation of 2NF)
• ‘nonprime attribute  nonprime attribute’:
transitive FD (violation of 3NF)
• ‘not a whole key  prime attribute’: violation of
BCNF
•
Perform lossless-join decompositions of each of the following
scheme into BCNF schemes: R(A, B, C, D, E) with dependency set
{AB  CDE, C  D, D  E}
A B
C D
C D
A B
A B C E
D
E
A B C
D
C D
A B C D
E
C D
A B C
Given the FDs {B  D, AB  C, D  B} and the relation {A,
B, C, D}, give a two distinct lossless join decomposition to
BNCF indicating the keys of each of the resulting relations.
A B
B D
C D
A
B
A B
C
B D
C D
A
C
D
Definition of MVD
• A multivalued dependency (MVD)
X ->->Y is an assertion that if two tuples of
a relation agree on all the attributes of X,
then their components in the set of attributes
Y may be swapped, and the result will be
two tuples that are also in the relation.
Example
• The name-addr-phones-beersLiked example
illustrated the MVD
name->->phones
and the MVD
name ->-> beersLiked.
Picture of MVD X ->->Y
X
Y
equal
exchange
others
MVD Rules
• Every FD is an MVD.
– If X ->Y, then swapping Y ’s between two tuples that
agree on X doesn’t change the tuples.
– Therefore, the “new” tuples are surely in the
relation, and we know X ->->Y.
• Complementation : If X ->->Y, and Z is all the
other attributes, then X ->->Z.
Fourth Normal Form
• The redundancy that comes from MVD’s is
not removable by putting the database
schema in BCNF.
• There is a stronger normal form, called
4NF, that (intuitively) treats MVD’s as FD’s
when it comes to decomposition, but not
when determining keys of the relation.
4NF Definition
•
A relation R is in 4NF if whenever
X ->->Y is a nontrivial MVD, then X is a
superkey.
–
“Nontrivial means that:
1. Y is not a subset of X, and
2. X and Y are not, together, all the attributes.
–
Note that the definition of “superkey” still
depends on FD’s only.
BCNF Versus 4NF
• Remember that every FD X ->Y is also an
MVD, X ->->Y.
• Thus, if R is in 4NF, it is certainly in
BCNF.
– Because any BCNF violation is a 4NF
violation.
• But R could be in BCNF and not 4NF,
because MVD’s are “invisible” to BCNF.
Normalization
 Good Decomposition
•
dependency preserving decomposition
- it is undesirable to lose functional dependencies
during decomposition
• lossless join decomposition
- join of decomposed relations should be able to
create the original relation (no spurious tuples)
Decomposition and 4NF
•
If X ->->Y is a 4NF violation for relation
R, we can decompose R using the same
technique as for BCNF.
1. XY is one of the decomposed relations.
2. All but Y – X is the other.
Example
Drinkers(name, addr, phones, beersLiked)
FD:
MVD’s:
name -> addr
name ->-> phones
name ->-> beersLiked
• Key is {name, phones, beersLiked}.
• All dependencies violate 4NF.
Example, Continued
• Decompose using name -> addr:
1. Drinkers1(name, addr)
 In 4NF, only dependency is name -> addr.
2. Drinkers2(name, phones, beersLiked)
 Not in 4NF. MVD’s name ->-> phones and
name ->-> beersLiked apply. No FD’s, so all
three attributes form the key.
Example: Decompose Drinkers2
• Either MVD name ->-> phones or
name ->-> beersLiked tells us to
decompose to:
– Drinkers3(name, phones)
– Drinkers4(name, beersLiked)