Transcript Relational Query Operators (Algebra)

Relational Query Operators
(Algebra)
Relational Query Operators
• Set operations: Union, intersection,
difference, Cartesian product
• Relational operations: Selection,
projection, join, division
Union
• Set1={A, B, C}
• Set2={C, D, E}
• Union: Members in Set 1 or in Set 2
– Set1 U Set 2 = {A, B, C, D, E}
• Logical operator OR
– Major = ‘CIS’ OR Major = ‘Acct’
– JobSkill = ‘Java’ OR JobSkill = ‘VB’
Intersect
• Members in Set 1 and in Set 2
– Set1 ∩ Set2={C}
• Logical operator AND
– Major = ‘CIS’ AND GPA > 3.0
– JobSkill = ‘Java’ AND Experience > 5
– Note: Major = ‘CIS’ AND Major = ‘Acct’ is
nonsense
Difference
• Set1={A, B, C}
• Set2={C, D, E}
• Set1 – Set2: Members in Set1 but not in
set2 = {A,B}
• Set2 – Set1:Members in Set2 but not in
set1 = {D, E}
• Set1-Set2 ≠ Set2 – Set1
• Logical operator: NOT
Union Compatibility
• Two relations that have the same number
of attributes and same type of attributes.
• Union, Intersect and difference operators
require the two relations to be union
compatible.
Union Compatibility Examples
• File 1:
– SID – 9 characters
– Sname – 25 characters
• File 2:
– SSN – 9 characters
– Ename – 25 characters
• File 3:
– Ename – 25 characters
– EID – 9 characters
• File 1 and file 2 are union compatible; file 1 and file 3 are
not; file 2 and file 3 are not.
Use Union and Difference to
Simulate Intersect
• Set1 ∩ Set2 = Set1 – (Set1 – Set2)
Venn Diagram
Set 1: Young: Age<30
Set 2: Rich: Income>100,000’
Set 3: Smart: IQ > 150
Files as Sets
• Business students’ file: BusSt
• Science student’s file: SciSt
– BusSt U SciSt:
– BusSt ∩ SciSt
– BusSt – SciSt
• Spring 06 Student file: S06St
• Fall 06 Student file: F06St
– S06St – F06St
– F06St – S06St
Product
• Set1 = {a, b, c}
• Set2 = {X, Y, Z}
• Set1 X Set2 = {aX, aY aZ, bX, bY, bZ, cX,
cY, cZ}
• Faculty File:
• FID Fname
• F1 Chao
• F2 Smith
• Student File:
•
•
•
•
SID
S1
S2
S3
Sname
Peter
Paul
Smith
FID
F1
F2
F1
• Faculty X Student:
Selection
• Selection operation works on a single
relation and defines a relation that
contains records that satisfy the criteria.
– σ criteria ( Relation)
– σ Major = ‘Bus’ AND GPA > 3.0 (Student)
Projection
• Projection operation works on a single
relation and defines a vertical subset of
the relation, extracting the values of
specified attributes and eliminating
duplicates.
• π a1, a2, … (Relation)
• π sid, sname (Student)
• Student file:
•
•
•
•
•
•
SID, Sname Sex
S1 Peter
S2 Paul
S3 Mary
S4 Nancy
S5 Peter
Major
M
M
F
F
M
Bus
Art
Bus
Sci
Art
• π sid, sname (Student)
• π sname, sex (Student)
• π sname, sex (σ Major = ‘Bus’ (Student))
– Does not equal to:
• σ Major = ‘Bus’ (π sname, sex (Student))
Duplications due to Projection
• WorkLog file:
•
•
•
•
•
•
EID
E1
E1
E2
E2
E3
PjID
P2
P1
P2
P1
P1
Hours
5
4
6
8
4
• π eid (WorkLog)
• Relation contrains: no duplication
• Eliminating duplicates may cause problems:
– π Hours (σ PjID = ‘P1 (WorkLog))
• In practice, users determine whether to eliminate
duplicates:
– SELECT DISTINCT EID FROM WorkLog;
– SELECT HOURS FROM WorkLog WHERE PjID = ‘P1’;
Natural Join
• The two relations must have common
attributes.
• Combines two relations to form a new
relation where records of the two relations
are combined if the common attributes
have the same value. One occurrence of
each common attribute is eliminated.
Faculty File:
FID
F1
F2
Student File:
SID
S1
S2
S3
Fname
Chao
Smith
Sname
Peter
Paul
Smith
FID
F1
F2
F1
Faculty Join Student =
π All except the duplicated attributes (σ Faculty.FID = Student.FID (Faculty X Student))
Note: Use RelationName.FieldName to make a field name unique.
Examples
• University database:
– Student: SID, Sname, Sex, Major, GPA, FID
– Account: SID, Balance
– Faculty: FID, Fname, Phone
– Course: CID, Cname, Credits, FID
– StudentCourse: SID, CID
Questions
• Q1: Display College of Business students’ ID and name.
• Q2: Display students’ ID and name who owe university more than
$2000.
• Q3: Display faculty’s name and phone if the student’s GPA is lower
than 2.0.
• Q4: Display faculty’s name and phone if the faculty advises at least
one student.
• Q5: Display faculty’s name and phone if the faculty does not advise
any student.
• Q6: Display students’ ID and name who are taking at least one
course.
• Q7: Display students’ ID and name who do not take any course.
• Q8: Display students’ ID and name who are taking 464 and GPA <
2.0
• Q9: Display students’ ID and name who are taking Chao’s courses.
• Q10: Display students’ ID and name who are taking 464 and 363.