Relational Query Operators Algebra Relational Query Operators Set

Relational Query Operators (Algebra)

Relational Query Operators • Set operations: Union, intersection, difference, Cartesian product • Relational operations: Selection, projection, join, division

Union • Set 1={A, B, C} • Set 2={C, D, E} • Union: Members in Set 1 or in Set 2 – Set 1 U Set 2 = {A, B, C, D, E} • Logical operator OR – Major = ‘CIS’ OR Major = ‘Acct’ – Job. Skill = ‘Java’ OR Job. Skill = ‘VB’

Intersect • Members in Set 1 and in Set 2 – Set 1 ∩ Set 2={C} • Logical operator AND – Major = ‘CIS’ AND GPA > 3. 0 – Job. Skill = ‘Java’ AND Experience > 5 – Note: Major = ‘CIS’ AND Major = ‘Acct’ is nonsense

Difference • Set 1={A, B, C} • Set 2={C, D, E} • Set 1 – Set 2: Members in Set 1 but not in set 2 = {A, B} • Set 2 – Set 1: Members in Set 2 but not in set 1 = {D, E} • Set 1 -Set 2 ≠ Set 2 – Set 1 • 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 • Set 1 ∩ Set 2 = Set 1 – (Set 1 – Set 2)

Venn Diagram Set 1: Young: Age<30 Set 2: Rich: Income>100, 000’ Set 3: Smart: IQ > 150

Files as Sets • Business students’ file: Bus. St • Science student’s file: Sci. St – Bus. St U Sci. St: – Bus. St ∩ Sci. St – Bus. St – Sci. St • Spring 06 Student file: S 06 St • Fall 06 Student file: F 06 St – S 06 St – F 06 St – S 06 St

Product • Set 1 = {a, b, c} • Set 2 = {X, Y, Z} • Set 1 X Set 2 = {a. X, a. Y a. Z, b. X, b. Y, b. Z, c. X, c. Y, c. Z}

• Faculty File: • FID Fname • F 1 Chao • F 2 Smith • Student File: • • SID S 1 S 2 S 3 Sname Peter Paul Smith FID F 1 F 2 F 1 • 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. • π a 1, a 2, … (Relation) • π sid, sname (Student)

• Student file: • • • SID, Sname Sex S 1 Peter S 2 Paul S 3 Mary S 4 Nancy S 5 Peter Major M M F F M Bus Art Bus Sci Art • π sid, sname (Student) • π sname, sex (σ Major = ‘Bus’ (Student)) – Does not equal to: • σ Major = ‘Bus’ (π sname, sex (Student))

Duplications due to Projection • Work. Log file: • • • EID E 1 E 2 E 3 Pj. ID P 2 P 1 P 1 Hours 5 4 6 8 4 • π eid (Work. Log) • Relation contrains: no duplication • Eliminating duplicates may cause problems: – π Hours (σ Pj. ID = ‘P 1 (Work. Log)) • In practice, users determine whether to eliminate duplicates: – SELECT DISTINCT EID FROM Work. Log; – SELECT HOURS FROM Work. Log WHERE Pj. ID = ‘P 1’;

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 F 1 F 2 Student File: SID S 1 S 2 S 3 Fname Chao Smith Sname Peter Paul Smith FID F 1 F 2 F 1 Faculty Join Student = π All except the duplicated attributes (σ Faculty. FID = Student. FID (Faculty X Student)) Note: Use Relation. Name. Field. Name 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 – Student. Course: SID, CID

Questions • Q 1: Display College of Business students’ ID and name. • Q 2: Display students’ ID and name who owe university more than $2000. • Q 3: Display faculty’s name and phone if the student’s GPA is lower than 2. 0. • Q 4: Display faculty’s name and phone if the faculty advises at least one student. • Q 5: Display faculty’s name and phone if the faculty does not advise any student. • Q 6: Display students’ ID and name who are taking at least one course. • Q 7: Display students’ ID and name who do not take any course. • Q 8: Display students’ ID and name who are taking 464 and GPA < 2. 0 • Q 9: Display students’ ID and name who are taking Chao’s courses. • Q 10: Display students’ ID and name who are taking 464 and 363.