- Super key =
-
a set of attributes
in a relation R
such that
no 2 tuples will have the same values
(for that set of attributes)
Mathematically speaking:
A set of attribute SK is a superkey if and only if: -
ForAll t1, t2 in R:
t1[SK] != t2[SK]
- Key:
is a minimal
set of attributes in a relation R
such that no 2 tuples will have the same values (for that
set of attributes)
I.e., a key is a minimal superkey....
- Candidate key:
is any key.
- Primary key:
is the
key that is chosen to be used to identify tuples
in a relation.
You must use the primary key throughout the database (or else, you database career life will be very difficult)
- Foreign key:
a set of attributes of a
relation R1
which is
the
primary key of another relation R1
Example: Grade report
- Student:
(ID, SSN, Name, Address, Phone, GPA)
- Keys: ID and SSN
- Course:
(CourseID, CourseNumber,
CourseName, #Hours)
- Keys: CourseID and CourseNumber
- GradeReport:
(ID, CourseNumber, Semester, Grade)
- Key: (ID, CourseNumber)
- Foreign keys: ID (to relation Student) and CourseNumber (to relation Course)
Pictorailly:
- Student:
(ID, SSN, Name, Address, Phone, GPA)
- Foreign key join:
- A 2-way join R1 * R2 is called a foreign key join if the joining attribute is a foreign key in R1 (and thus a key in R2)
- Source Relation
- The relation R1 that contains the foreign key is called the source relation of the foreign key join
- Example:
- GradeReport * Student
is a foreign key join
- ID is a key of Student
- Source relation is GradeReport
- GradeReport * Course
is a foreign key join
- CID is a key of Student
- Source relation is GradeReport
- GradeReport * Student
is a foreign key join
- k-way foreign key join:
- A k-way join
R1 * R2
* ... * Rk
is a
foreign key join
if there is an
ordering of
the relations such that:
- S1 = R1 * R2 is a foreign key join with R1 as source relation
- S2 = S1 * R2 is a foreign key join with S1 as source relation
- S3 = S2 * R2 is a foreign key join with S2 as source relation
- And so on...
- A k-way join
R1 * R2
* ... * Rk
is a
foreign key join
if there is an
ordering of
the relations such that:
- Example:
- Student * GradeReport * Course
is a 3-way foreign key join
because:
- S1 = GradeReport * Student
is a foreign key join
on foreign key
Student.ID
- S2 = S1 * Course is a foreign key join on foreign key Grade.CourseNo
- S1 = GradeReport * Student
is a foreign key join
on foreign key
Student.ID
- Student * GradeReport * Course
is a 3-way foreign key join
because:
- Consider the following database model
of a company:
- We can express the
foreign key relationships
using a graph
Example:
- You can see from the
graph that:
- works_on * Project is a foreign key join (foreign key is project number)
- Project * Department
is a foreign key join
(foreign key is department number)
- works_on * Project * Department
is a 3-way foreign key join !
- But:
-
Dependent * Employee * Department
is not
a 3-way foreign key join !
- Rule of thumb:
- A connected subgraph with a single root node is a k-way foreign key join
- Lemma 4.2:
- There is a 1-1 correspondence
between tuples in:
- the source relation R1 and
- the k-way foreign key join relation
- the source relation R1 and
- There is a 1-1 correspondence
between tuples in:
- That is very easy to see
in an example:
Works_on * Employee
Sample data:
Works_on: (source relation) ESSN PNO HOURS --------- ---------- ---------- 123456789 1 32.5 123456789 2 7.5 333445555 10 10 333445555 3 10 333445555 2 10 333445555 20 10 453453453 2 20 453453453 1 20 666884444 3 40 888665555 20 1 987654321 30 20 987654321 20 15 987987987 10 35 987987987 30 5 999887777 30 30 999887777 10 10
Employee: SSN FNAME LNAME --------- ---------- ---------- 123456789 John Smith 333445555 Frank Wong 453453453 Joyce English 666884444 John Doe 888665555 James Borg 987654321 Jack Wallace 987987987 Jake Jones 999887777 Alice Miller
Worsk_on * Employee:
Works_on * Employee: ESSN PNO HOURS SSN FNAME LNAME --------- ---------- ---------- --------- ---------- ---------- 123456789 1 32.5 123456789 John Smith 123456789 2 7.5 123456789 John Smith 333445555 10 10 333445555 Frank Wong 333445555 3 10 333445555 Frank Wong 333445555 2 10 333445555 Frank Wong 333445555 20 10 333445555 Frank Wong 453453453 2 20 453453453 Joyce English 453453453 1 20 453453453 Joyce English 666884444 3 40 666884444 John Doe 888665555 20 1 888665555 James Borg 987654321 30 20 987654321 Jack Wallace 987654321 20 15 987654321 Jack Wallace 987987987 10 35 987987987 Jake Jones 987987987 30 5 987987987 Jake Jones 999887777 30 30 999887777 Alice Miller 999887777 10 10 999887777 Alice Miller
- The 1-1-correspondence is
caused by the fact that
the join-attribute
is a
key in the second relation.
The key will identify one unique tuple in the second relation.
- This fact can be exploited to
compute join synopses:
- A uniform sample
of the source relation
can be used to compute a
join-sypnopses (summary)
In other words:
-
Weighted samples is
not necessary
to construct join synopses
in foreign key joins
- A uniform sample
of the source relation
can be used to compute a
join-sypnopses (summary)
- Maximum foreign key join:
- A maximum foreign key join is a foreign key join using the largest possible number of relations
- Example: the
maximum foreign key join
involving the relation Works_on:
Works_on * Project * Department * Employee
- Notice the
1-1 correspondence between
the tuples in
the source relation
Works_on and
the
join
Works_on * Project * Department
* Employee
Example database content:
Works_on ESSN PNO HOURS --------- ---------- ---------- 123456789 1 32.5 123456789 2 7.5 333445555 2 10 333445555 3 10 333445555 10 10 333445555 20 10 999887777 30 30 999887777 10 10 987654321 30 20 987654321 20 15 666884444 3 40 453453453 1 20 453453453 2 20 987987987 30 5 987987987 10 35 888665555 20 1
Project PNUMBER PNAME DNUM ---------- --------------- ---------- 1 ProductX 5 2 ProductY 5 3 ProductZ 5 10 Computerization 4 20 Reorganization 1 30 Newbenefits 4Department DNUMBER DNAME MGRSSN ---------- --------------- --------- 5 Research 333445555 4 Administration 987654321 1 Headquarters 888665555Employee: SSN FNAME LNAME --------- ---------- ---------- 123456789 John Smith 333445555 Frank Wong 453453453 Joyce English 666884444 John Doe 888665555 James Borg 987654321 Jack Wallace 987987987 Jake Jones 999887777 Alice Miller
- Output of
Works_on * Project * Department
* Employee:
ESSN PNO HOURS PNUM PNAME DNUM DNUM DNAME MGRSSN SSN FNAME LNAME --------- --- ----- ---- -------- ---- ---- ------------ --------- --------- ----- ----- 123456789 1 32.5 1 ProductX 5 5 Research 333445555 333445555 Frank Wong 123456789 2 7.5 2 Product 5 5 Research 333445555 333445555 Frank Wong 333445555 2 10 2 ProductY 5 5 Research 333445555 333445555 Frank Wong 333445555 3 10 3 ProductZ 5 5 Research 333445555 333445555 Frank Wong 333445555 10 10 10 Computer. 4 4 Administr. 987654321 987654321 Jack Wall. 333445555 20 10 20 Reorgani. 1 1 Headquart. 888665555 888665555 James Borg 999887777 30 30 30 Newbenef. 4 4 Administr. 987654321 987654321 Jack Wall. 999887777 10 10 10 Computer. 4 4 Administr. 987654321 987654321 Jack Wall. 987654321 30 20 30 Newbenef. 4 4 Administr. 987654321 987654321 Jack Wall. 987654321 20 15 20 Reorgani. 1 1 Headquart. 888665555 888665555 James Borg 666884444 3 40 3 ProductZ 5 5 Research 333445555 333445555 Frank Wong 453453453 1 20 1 ProductX 5 5 Research 333445555 333445555 Frank Wong 453453453 2 20 2 ProductY 5 5 Research 333445555 333445555 Frank Wong 987987987 30 5 30 Newbenef. 4 4 Administr. 987654321 987654321 Jack Wall. 987987987 10 35 10 Computer. 4 4 Administr. 987654321 987654321 Jack Wall. 888665555 20 1 20 Reorgani. 1 1 Headquart. 888665555 888665555 James Borg
- Overview of the material so far:
- Each node in the
model graph
corresponds to a relation
- Each relation can
be the source relation
in a k-way foreign key join
involving
relations that are
descendent noded
in the model graph
- For each relation R, there is some maximum foreign key join that have the largest number of relations with relation R as the source relation
- Each node in the
model graph
corresponds to a relation
- Definition 4.2 - Join Synopses:
- Let x = a node in the
data model graph G
Let node x corresponds to the relation R1
- Define J(R1):
- J(R1) = R1 * R2 * ... * Rk
the maximum foreign key join with the source relation R1
- Let SR1 =
uniform (random) sample
of the relation R1
- The
join-synopses (summary) J(SR1)
of J(R1) is
defined as to:
- J(SR1) = SR1 * R2 * ... * Rk
- Let x = a node in the
data model graph G
- Theorem 4.3:
- Let J(R1) = R1 * R2 * ... * Rk (join result)
- Let SJ(R1) =
uniform (random) sample
of the join result J(R1)
- Let SR1 = uniform (random) sample of the relation R1
- Let J(SR1) =
SR1
* R2 * ... * Rk
Then:
-
J(SR1) =
SR1
* R2 * ... * Rk
is a
uniform (random) sample
of the join result
I.e.: J(SR1) and SJ(R1) have the same distribution
- Example:
consider the
maximum foreign key join
Works_on * Project * Department
* Employee:
ESSN PNO HOURS PNUM PNAME DNUM DNUM DNAME MGRSSN SSN FNAME LNAME --------- --- ----- ---- -------- ---- ---- ------------ --------- --------- ----- ----- 123456789 1 32.5 1 ProductX 5 5 Research 333445555 333445555 Frank Wong 123456789 2 7.5 2 Product 5 5 Research 333445555 333445555 Frank Wong 333445555 2 10 2 ProductY 5 5 Research 333445555 333445555 Frank Wong 333445555 3 10 3 ProductZ 5 5 Research 333445555 333445555 Frank Wong 333445555 10 10 10 Computer. 4 4 Administr. 987654321 987654321 Jack Wall. 333445555 20 10 20 Reorgani. 1 1 Headquart. 888665555 888665555 James Borg 999887777 30 30 30 Newbenef. 4 4 Administr. 987654321 987654321 Jack Wall. 999887777 10 10 10 Computer. 4 4 Administr. 987654321 987654321 Jack Wall. 987654321 30 20 30 Newbenef. 4 4 Administr. 987654321 987654321 Jack Wall. 987654321 20 15 20 Reorgani. 1 1 Headquart. 888665555 888665555 James Borg 666884444 3 40 3 ProductZ 5 5 Research 333445555 333445555 Frank Wong 453453453 1 20 1 ProductX 5 5 Research 333445555 333445555 Frank Wong 453453453 2 20 2 ProductY 5 5 Research 333445555 333445555 Frank Wong 987987987 30 5 30 Newbenef. 4 4 Administr. 987654321 987654321 Jack Wall. 987987987 10 35 10 Computer. 4 4 Administr. 987654321 987654321 Jack Wall. 888665555 20 1 20 Reorgani. 1 1 Headquart. 888665555 888665555 James Borg
Imagine you need to pick 3 tuples randomly from the above result.
And just suppose you happen to pick the first 3:
ESSN PNO HOURS PNUM PNAME DNUM DNUM DNAME MGRSSN SSN FNAME LNAME --------- --- ----- ---- -------- ---- ---- ------------ --------- --------- ----- ----- 123456789 1 32.5 1 ProductX 5 5 Research 333445555 333445555 Frank Wong 123456789 2 7.5 2 Product 5 5 Research 333445555 333445555 Frank Wong 333445555 2 10 2 ProductY 5 5 Research 333445555 333445555 Frank Wong
You would get the same result if you pick the first 3 tuples in the source relation and then perform the join:
Works_on ESSN PNO HOURS --------- ---------- ---------- 123456789 1 32.5 123456789 2 7.5 333445555 2 10
Project PNUMBER PNAME DNUM ---------- --------------- ---------- 1 ProductX 5 2 ProductY 5 3 ProductZ 5 10 Computerization 4 20 Reorganization 1 30 Newbenefits 4Department DNUMBER DNAME MGRSSN ---------- --------------- --------- 5 Research 333445555 4 Administration 987654321 1 Headquarters 888665555Employee: SSN FNAME LNAME --------- ---------- ---------- 123456789 John Smith 333445555 Frank Wong 453453453 Joyce English 666884444 John Doe 888665555 James Borg 987654321 Jack Wallace 987987987 Jake Jones 999887777 Alice Miller
- Theorem 4.4
- From a single join synopses
for a node whose
maximum foreign key join
has k relations,
we can construct between:
-
k-1 and
2k-1 - 1
different
join synopses
- From a single join synopses
for a node whose
maximum foreign key join
has k relations,
we can construct between:
- Example:
The Maximum foreign key join Works_on * Project * Department * Employee:
ESSN PNO HOURS PNUM PNAME DNUM DNUM DNAME MGRSSN SSN FNAME LNAME --------- --- ----- ---- -------- ---- ---- ------------ --------- --------- ----- ----- 123456789 1 32.5 1 ProductX 5 5 Research 333445555 333445555 Frank Wong 123456789 2 7.5 2 Product 5 5 Research 333445555 333445555 Frank Wong 333445555 2 10 2 ProductY 5 5 Research 333445555 333445555 Frank Wong 333445555 3 10 3 ProductZ 5 5 Research 333445555 333445555 Frank Wong 333445555 10 10 10 Computer. 4 4 Administr. 987654321 987654321 Jack Wall. 333445555 20 10 20 Reorgani. 1 1 Headquart. 888665555 888665555 James Borg 999887777 30 30 30 Newbenef. 4 4 Administr. 987654321 987654321 Jack Wall. 999887777 10 10 10 Computer. 4 4 Administr. 987654321 987654321 Jack Wall. 987654321 30 20 30 Newbenef. 4 4 Administr. 987654321 987654321 Jack Wall. 987654321 20 15 20 Reorgani. 1 1 Headquart. 888665555 888665555 James Borg 666884444 3 40 3 ProductZ 5 5 Research 333445555 333445555 Frank Wong 453453453 1 20 1 ProductX 5 5 Research 333445555 333445555 Frank Wong 453453453 2 20 2 ProductY 5 5 Research 333445555 333445555 Frank Wong 987987987 30 5 30 Newbenef. 4 4 Administr. 987654321 987654321 Jack Wall. 987987987 10 35 10 Computer. 4 4 Administr. 987654321 987654321 Jack Wall. 888665555 20 1 20 Reorgani. 1 1 Headquart. 888665555 888665555 James Borg
The Join Synopsis of the Maximum foreign key join Works_on * Project * Department * Employee:
ESSN PNO HOURS PNUM PNAME DNUM DNUM DNAME MGRSSN SSN FNAME LNAME --------- --- ----- ---- -------- ---- ---- ------------ --------- --------- ----- ----- 123456789 1 32.5 1 ProductX 5 5 Research 333445555 333445555 Frank Wong 123456789 2 7.5 2 Product 5 5 Research 333445555 333445555 Frank Wong 333445555 2 10 2 ProductY 5 5 Research 333445555 333445555 Frank Wong
- We can obtain the following
join synopes from the
Maximum foreign key join
synopsis:
- Join synopsis of
Works_on * Project * Department:
ESSN PNO HOURS PNUM PNAME DNUM DNUM DNAME MGRSSN --------- --- ----- ---- -------- ---- ---- ------------ --------- 123456789 1 32.5 1 ProductX 5 5 Research 333445555 123456789 2 7.5 2 Product 5 5 Research 333445555 333445555 2 10 2 ProductY 5 5 Research 333445555
- Join synopsis of
Works_on * Project:
ESSN PNO HOURS PNUM PNAME DNUM --------- --- ----- ---- -------- ---- 123456789 1 32.5 1 ProductX 5 123456789 2 7.5 2 Product 5 333445555 2 10 2 ProductY 5
- Join synopsis of
Works_on * Project * Department:
- The number of synopses
that you can extract
from the
maximum foreign key join
synopses depends of
the structure of the model graph
-
The smallest number of
foreign key join
synopes are extracted when the
model graph is a path graph:
In this case, we can extract the following join synopses from the maximum foreign key join synopses:
- R1 * R2
- R1 * R2 * R3
- R1 * R2 * R3 * R4
- ...
- R1 * R2 * R3 * ... * Rk
- The largest number of
foreign key join
synopes are extracted when the
model graph is a balanced graph:
In this case, we can extract the following join synopses from the maximum foreign key join synopses:
- R1 * R2
- R1 * R2
- R1 * R2 * R3 !!
- R1 * R2 * R4
- R1 * R2 * R5
- R1 * R2 * * R4 * R5 !!
- R1 * R2 * * R4 * R5 * R3 !!
- ...
- R1 * R2 * R3 * ... * Rk
-
The smallest number of
foreign key join
synopes are extracted when the
model graph is a path graph:
- This method maintains:
- One maximum foreign join synopsis
for every relation R
- The maximum foreign join synopsis for R is constructed with relation R as source relation
- One maximum foreign join synopsis
for every relation R
- When tuples are inserted
or deleted from a
relation,
the maximum foreign key join synopses
must also be
updated
to reflect the change.
- Synopsis insert algorithm: (page 281)
Execute the following algorithm when a tuple t is inserted in relation R
Notations: R = source relation SR = ramdom sample of R J(SR) = join synopsis of relation R R * R2 * R3 * .... * Rk is the maximum foreign key join
| SR | p = ------ // p = selection probability | R | if ( random() < p ) { x = t * R2 * R3 * ... * Rk; add x to J(SR); /* ------------------------------------- Trim synopses if it gets too large ------------------------------------- */ if ( | J(SR) | > Maximum ) { r = random( 1 .. | J(SR) | ) delete the rth tuple from J(SR); } }
- Synopsis delete algorithm: (page 281)
Execute the following algorithm when a tuple t is deleted from relation R
Notations: R = source relation SR = ramdom sample of R J(SR) = join synopsis of relation R R * R2 * R3 * .... * Rk is the maximum foreign key join
x = t * R2 * R3 * ... * Rk; if ( x ∈ J(SR) ) { delete x from J(SR); /* ------------------------------------------------ Re-populate synopsis when it gets too small... ------------------------------------------------ */ if ( | J(SR | < Minimum ) { Use random sample algorithm to re-popuplate J(SR; } }
- There are different allocation strategies
to divide the disk storage space
among the
different relations
- Allocation Strategies:
- EqJoin:
-
Divide the space allotted equally
among all relation
No matter how much space the join synopsis of source relation R uses, the amount of space for R is the same as other source relations
- PropJoin:
- Divide the allotted space up
proportionally to the
length of the join synopsis tuple
- In this scheme, more space are allocated to join synopses using a source relation that is "higher" up in the tree.
- Divide the allotted space up
proportionally to the
length of the join synopsis tuple
- EqJoin:
- Performance result:
- Accuracy: (compute average price)
- Running time:
(Note:
logorithmic scale !)
- Accuracy: (compute average price)