CIND110 – Lab 05
CIND110 – Data Organization for Data Analysts Lab 05 – Relational Algebra Expressions
1 Running the MySQL Service 2
2 The Working Database Schema 3
3 Unary Relational Operations 4
3.1 3.2 3.3
TheSELECTOperation(σ)………………………… 4 ThePROJECTOperation(π)………………………… 7 TheRENAMEOperation(ρ) ………………………… 9
4 Set Theory Operations 11
4.1 TheUNIONOperation(∪)…………………………. 11 4.2 TheINTERSECTOperation(∩) ………………………. 13 4.3 TheMINUSOperation(−)…………………………. 14 4.4 TheCROSSPRODUCTOperation(×) ……………………. 16
5 SQL Operations 18
5.1 TheINNERJOINOperation(./) ……………………… 18 5.2 TheOUTERJOINOperation(./)……………………… 20 5.3 AggregateFunctionsandGrouping…………………….. 21
Running the MySQL Service
• Check the status of the MySQL service using the following Linux command from the Ter- minal application.
sudo service mysql status
• After ensuring that the MySQL service is active, run the following Linux command to start the MySQL service
mysql –u root -p
CIND110 – Lab 05
The Working Database Schema
• In this lab, we will use the UnivDB database schema described with the following settings.
CIND110 – Lab 05
STUDENT = {
ID:number , Name:string
16 , ‘Jack’
17 , ‘Ryan’
18 , ‘Sally’
19 , ‘Jane’
, Dept:string
COURSE = {
Name:string
‘Data Structures’
‘Data Organization’
‘Data Analytics’
‘Python Programming’
, Code:string
, ‘CCPS305’
, ‘CIND110’
, ‘CIND123’
, ‘CIND830’
, Credit:number , Dept:string
, Major:string
, ‘Bioengineering’
, ‘Mechatronics’
, ‘Data Science’
, ‘Software Engineering’ , ‘MIE’
SECTION = {
ID:string, C_Code:string, Term:string , Year:number, Instructor:string
‘YJ5’ ,
‘KJ2’ ,
‘YJ2’ ,
‘YJ3’ ,
‘KJ3’ ,
‘CIND123’ ,
‘CCPS305’ ,
‘CIND110’ ,
‘CIND110’ ,
‘CIND110’ ,
‘Spring’ , 2020 ,
‘Fall’ , 2021 ,
‘Winter’ , 2019 ,
‘Fall’ , 2020 ,
‘Winter’ , 2019 ,
Std_ID:number, Sec_ID:string , Pct_Grade:string , Ltr_Grade:string 16 ,’YJ2′ , 74 , ‘B’
17 ,’YJ2′ , 75 , ‘B’
18 ,’YJ3′ , 65 , ‘C’
17 ,’KJ3′ , 88 , ‘A’
19 ,’YJ3′ , 74 , ‘B’
, 3 , 4 , 2 , 3
• Write a set of queries to create the UnivDB database schema, design the involved tables, and add/insert the respective records.
nivDB – STU…
er tring ring ing
tring ring umber ing
number string
de string de string
bra SQL Group Editor select from where group having order limit
1 SELECT DISTINCT *
2 FROM SECTION
3 WHERE Year > 2019;
Unary Relational Operations
The SELECT Operation (σ)
CIND110 – Lab 05
Relational Alge
Calculator
Feedback Help Imp
• ThefirstunaryrelationalalgebraoperationistheSELECToperationdenotedbythesigma(σ) symbol. The SELECT operation is applied to a single relation and is used to choose a subset of records from a table/relation that satisfies a selection condition. The following example retrieves all the information of the sections conducted after the year of 2019.
SELECT DISTINCT *
FROM SECTION
WHERE Year > 2019;
execute query download history
σ Year > 2019 3 rows
SECTION 5 rows
σ Year > 2019 SECTION
SECTION.ID SECTION.C_Code SECTION.term SECTION.Year SECTION.Instructor
‘YJ5’ ‘CIND123’ ‘Spring’ 2020 ‘Sally’
‘KJ2’ ‘CCPS305’ ‘Fall’ 2021 ‘King’
‘YJ3’ ‘CIND110’ ‘Fall’ 2020 ‘Sandy’
Term ‘Fall’ SECTION
Term ‘Fall’ Year
2019 SECTION
execute query
nivDB – STU…
er tring ring
executedqouwenrlyoad as the SELECT RA operation is commutative.
Major string Dept string
COURSE Code string
Name string
Credit number Dept string
CIND110 – Lab 05 • Theremfsotrlinlgowing two RA expressions can be used interchangeably with the expression above
SECTION ID string
C_Code string Year number
Instructor string
GRADE σ Year > 2019 Std_ID number
Group Edit
Ltr_Grade stringσ
Relational AlgebTrearm = ‘FSalQl’ L
Sec_ID string
Pct_Grade string
select from where group having order limit
1 SELECT DISTINCT * SECTION
2 FROM SECTION
3 WHERE Year > 2019 AND Term =’Fall’;
σ Year > 2019 σ Term = ‘Fall’ SECTION
Term = ‘Fall’
σ Year > 2019 3 rows
SECTION 5 rows
σ Term = ‘Fall’ σ Year > 2019 SECTION
uEmCbeTrION.ID SECTION.mC_oCroedtehanSoEnCeTIcOoNn.TdeirtmionaSlEeCxTpIrOeNss.Yioeanr.TShEeCfToIOllNow.Inisntgrucetxoarmpleretrievesalltheinformation
of the sections conducted after 2019 during the Fall term.
‘King’ ‘Sandy’
string ing
number string
de string de string
• It is also possible to add a logical expression using a logical operator (e.g. AND consisting of
SECTION.ID SECTION.C_Code SECTION.Term SECTION.Year SECTION.Instructor
‘CCPS305’ ‘CIND
‘KJ2’ ‘Fall’ 2021
‘CCPS305’ ‘King’
‘Fall’ 2021
SELECT DISTINCT *
1F1R0’OM SECTIO’NFall’ 2020 WHEeRxEecYueteaqrue>ry 2019 AND Term =’Fall’;
‘Fall’ 2020
‘YJ3’ ‘CIND110’
σ Year > 2019 and Term = ‘Fall’ 2 rows
SECTION 5 rows
σ Year > 2019 and Term = ‘Fall’ SECTION
SECTION.ID SECTION.C_Code SECTION.Term SECTION.Year SECTION.Instructor
‘KJ2’ ‘CCPS305’ ‘Fall’ 2021 ‘King’
‘YJ3’ ‘CIND110’ ‘Fall’ 2020 ‘Sandy’
nivDB – STU…
Relational Algebra SQL Group Editor select from where group having order limit
ring tring umber
string ing
number string
de string de string
ode LIKE ‘CIND%’ AND Term = ‘Fall’ OR C_Code LIKE ‘CCPS%’ AND Term = ‘Fall’
CIND110 – Lab 05
• The following example retrieves all the information of computer science or industrial engi- neering sections conducted during the Fall term.
1 SELECT DI 2 FROM SECT 3 WHERE C_C
SELECT DISTINCT *
FROM SECTION
WHERE C_Code LIKE ‘CIND%’ AND Term = ‘Fall’
C_Code LIKE ‘CCPS%’ AND Term = ‘Fall’ ; execute query
σ C_Code like ‘CIND%’ and Term = ‘Fall’ or C_Code like ‘CCPS%’ and Term = ‘Fall’ 2 rows
SECTION 5 rows
σ C_Code like ‘CIND%’ and Term = ‘Fall’ or C_Code like ‘CCPS%’ and Term = ‘Fall’ SECTION
SECTION.ID SECTION.C_Code SECTION.Term SECTION.Year SECTION.Instructor
‘KJ2’ ‘CCPS305’ ‘Fall’ 2021 ‘King’
‘YJ3’ ‘CIND110’ ‘Fall’ 2020 ‘Sandy’
• Note that the following query gives different result than that of the above query. Why?
SELECT DISTINCT *
FROM SECTION
WHERE C_Code LIKE ‘CIND%’
C_Code LIKE ‘CCPS%’ AND
Term = ‘Fall’ ;
nivDB – STU…
er tring ring ing
tring ring umber ing
number string
de string de string
Relational Algebra SQL Group Editor select from where group having order limit
ISTINCT Instructor TION;
The PROJECT Operation (π)
CIND110 – Lab 05
1 SELECT D 2 FROM SEC
ThesecondunaryrelationalalgebraoperationisthePROJECToperationdenotedbythepi(π) symbol. The PROJECT operation is applied to a single relation and is used to choose a subset of fields/columns from a table/relation. The following example retrieves all the names of the instructors in the SECTION table.
SELECT DISTINCT Instructor
FROM SECTION;
execute query download history
π Instructor 4 rows
SECTION 5 rows
π Instructor SECTION SECTION.Instructor
‘Sally’ ‘King’ ‘Larry’ ‘Sandy’
Youcanseethattherecordthatholdsthestringvalue’King’appearsonlyonce,eventhough this value appears twice in the SECTION relation. The PROJECT operation removes any du-
plicate records, so the result is a set of distinct tuples, and hence a valid relation. Notably, If
duplicates are not eliminated, the result would be a multi-set or bag of records rather than a set.
1 π YeaSrTU(πDETNerTm, Year (SECTION)) ID number
Name string Major string Dept string
COURSE Code str
Name string Credit number Dept string
πσρ←→τγ∧∨¬=≠≥≤∩∪÷-⨯⨝⟕⟖⟗ ⋉ ⋊ ▷ = — /* {}
1 π Year (SECTION)
execute query
CIND110 – Lab 05 • The following two RA expressions can be used interchangeably, as the set of the attributes
of the outer Project RA operation i.e Year is a subset of the set of the attributes of the inner
ID string C_Code string Term s
Year n Instruc
GRADE Std_ID
Sec_ID Pct_G Ltr_Gr
Project RA operation i.e Term, Year.
umber π Yearexecute query
tor string 3 rows
number string
rade string ade string
π Term, Year 4 rows
SECTION 5 rows
π Year ( π Term, Year ( SECTION ) ) SECTION.Year
π Year 3 rows
SECTION 5 rows
π Year ( SECTION ) SECTION.Year
• TheresultoftheProjectoperationincludesonlytheattributesspecifiedinthesameorderas ‹1›
theyappearintheRAexpression.Thedegreeofthe‹resu1lting› relationisequaltothenumber of these attributes. It is also noteworthy that commutativity does not hold for the Project RA operation.
Calculator
Feedback Help
B (InivDB – STU…
Relational Algebra SQL Group Editor ere group having order limit
3 SELECT DISTINCT C.Code, C.Name, C.Dept 4 FROM COURSE AS C
5 WHERE Credit <> 3;
The RENAME Operation (ρ)
or string t string
e string dit number
ode string
r number ructor string
_ID number _ID string
_Grade string Grade string
CIND110 – Lab 05
The third unary RA operation is the RENAME operation, denoted by the Greek rho letter (ρ). The following example renames the COURSE relation before filtering the rows and then choos- ing a subset of attributes.
π C.Code, C.Name, C.Dept 2 rows
σ Credit ≠ 3 2 rows
COURSE 4 rows
π C.Code, C.Name, C.Dept σ Credit ≠ 3 ρ C COURSE
‘CIND110’ ‘CIND123’
‘Data Organization’
‘Data Analytics’
In the previous example, an error will be raised when applying the RA PROJECT operation before applying the RA SELECT operation. Why?
select from wh
SELECT DISTINCT C.Code, C.Name, C.Dept
FROM COURSE AS C
WHERE Credit <> 3;
execute query download history
T, COURSE, …
Calculator Language Feedback H
CIND110 – Lab 05 1 • The RA RENAME operation can be applied to the attributes of a particular relation other than
2 3SELaECpTpDlIySTiInNCgTCi.tCotdoetAhSeCourresel_aCtoidoe,nsthemselves.Thefollowingexamplegivesnewnamestothere-
Relational Algebra SQL Group Editor select from where group having order limit
6 FROM COURSE AS C 7 WHE
C.Name AS Course_Name,
sulting rel
of a degree equals three.
RE Credit <> 3;
SELECT DISTINCT C.Code AS Course_Code,
C.Name AS Course_Name,
C.Dept AS Department FROM COURSE AS C
WHERE Credit <> 3;
execute selection
ρ Course_Code←C.Code, Course_Name←C.Name, Department←C.Dept 2 rows
π C.Code, C.Name, C.Dept 2 rows
σ Credit ≠ 3 2 rows
COURSE 4 rows
ρ Course_Code←C.Code, Course_Name←C.Name, Department←C.Dept π C.Code, C.Name, C.Dept σ Credit ≠ 3 ρ C COURSE
C.Course_Code
‘CIND110’ ‘CIND123’
C.Course_Name
‘Data Organization’
‘Data Analytics’
C.Department
Name string Major string Dept string
Name string Code string Credit number Dept string
TION D string
C_Code string erm string
Year number nstructor string
Std_ID number Sec_ID string Pct_Grade string
tr_Grade string
3 SELECT DISTINCT Name from STUDENT;
Set Theory Operations
Calculator
CIND110 – Lab 05
Feedback Help
The UNION Operation (∪)
• The result of applying the binary UNION set theory operation, denoted by the cup symbol, ∪, is a relation that includes all records that are either in both of the involved relations or in one of them. The following example lists all the records in both of the Name attribute from the STUDENT relation and the Instructor attribute from the SECTION relation.
SELECT DISTINCT Instructor FROM SECTION
SELECT DISTINCT Name FROM STUDENT;
execute query download history
π Instructor 4 rows
SECTION 5 rows
π Name 4 rows
STUDENT 4 rows
π Instructor SECTION ∪ π Name STUDENT SECTION.Instructor
‘Sally’ ‘King’ ‘Larry’ ‘Sandy’ ‘Jack’ ‘Ryan’ ‘Jane’
CIND110 – Lab 05
• The duplicate elimination feature removes any redundancy in the results and displays only the unique values. As you can see, the record contains the string ‘Sally’ was listed only once.
• Two relations are said to be union compatible if they have the same degree. For example, the following UNION operation will raise an error, as the involved relations are of different degrees, and hence they are not unifiable.
SELECT DISTINCT Instructor, Year FROM SECTION UNION
SELECT DISTINCT Name FROM STUDENT;
InivDB – STU…
tring ring
tring tring
umber ring
e string ing mber
tor string
number string
ade string de string
ebra SQL Group Editor e group having order limit
1 SELECT DISTINCT Instructor from SECTION 2 INTERSECT
3 SELECT DISTINCT Name from STUDENT;
The INTERSECT Operation (∩)
CIND110 – Lab 05
Relational Alg
Calculator
Feedback Help Im
select from wher
The result of applying the binary INTERSECT set theory operation, denoted by the cap sym- bol, ∩, is a relation that includes all records that are in both of the involved relations. The following example lists the intersection between the records in the Name attribute from the STUDENT relation and the Instructor attribute from the SECTION relation, displaying only those who are both students and instructors.
SELECT DISTINCT Instructor FROM SECTION
WHERE Instructor IN
(SELECT DISTINCT Name FROM STUDENT);
execute selection download history
π Instructor 4 rows
SECTION 5 rows
π Name 4 rows
STUDENT 4 rows
π Instructor SECTION ∩ π Name STUDENT
SECTION.Instructor
DB – STU…
bra SQL Group Editor select from where group having order limit
CIND110 – Lab 05
On the other hand, the result of applying the binary MINUS or the DIFFERENCE set theory operation, denoted by the minus symbol, −, is a relation that includes all records that are in the left but not in the ration to the right side of the minus operator. The following example lists the difference between the records in the Name attribute from the STUDENT relation and the Instructor attribute from the SECTION relation, displaying only those who are instructors but not students.
Relational Alge
SELECT DISTINCT Instructor FROM SECTION WHERE Instructor NOT IN
(SELECT DISTINCT Name FROM STUDENT);
Calculator
Feedback Help I
1 SELECT DISTINCT Instructor FROM SECTION
3 SELECT DISTINCT Name FROM STUDENT; The MINUS Operation (−)
execute query
π Instructor 4 rows
SECTION 5 rows
π Name 4 rows
STUDENT 4 rows
π Instructor SECTION – π Name STUDENT SECTION.Instructor
‘King’ ‘Larry’ ‘Sandy’
‹1› 14 of 22
B – STU…
Relational Algebra SQL Group Editor select from where group having order limit
TINCT Name FROM STUDENT
3 SELECT DISTINCT Instructor FROM SECTION;
1 SELECT DIS 2 EXCEPT
SELECT DISTINCT Name FROM STUDENT
WHERE Name NOT IN
(SELECT DISTINCT Instructor FROM SECTION);
CIND110 – Lab 05
string tring
• BothoftheUNIONandINTERSECTIONarecommutativeoperations,however,theMINUSoper- ation is not. The following example shows that by swapping the involved relations, the result will differ, as it will display the students who are not instructors rather than the instructors who are not students.
execute query
π Name 4 rows
STUDENT 4 rows
π Instructor 4 rows
SECTION 5 rows
π Name STUDENT – π Instructor SECTION STUDENT.Name
‘Jack’ ‘Ryan’ ‘Jane’
execute selection
download history
SELECT DISTINCT Instructor, Dept FROM COURSE CROSS JOIN SECTION;
CIND110 – Lab 05
Calculator Language Feedback
The CROSS PRODUCT Operation (×)
• The CROSS PRODUCT or the CARTESIAN PRODUCT, denoted by the × symbol, can be applied to non-union-compatible relations and will produce all the possible combinations between the involved relations. The following example shows the result of applying the CROSS PRODUCT operation on two relations that are not union-compatible.
π Instructor, Dept 8 rows
COURSE SECTION 4 rows 5 rows
π Instructor, Dept ( COURSE ⨯ SECTION )
SECTION.Instructor
‘Sally’ ‘King’ ‘Larry’ ‘Sandy’ ‘Sally’ ‘King’ ‘Larry’ ‘Sandy’
COURSE.Dept
‘CS’ ‘CS’ ‘CS’ ‘CS’ ‘MIE’ ‘MIE’ ‘MIE’ ‘MIE’
CIND110 – Lab 05 • Both Course and SECTION relations come with different degrees: 4 and 5. The resulting
Relational Algebra SQL Group Editor select from where group having order limit
SELECT DISTINCT *
FROM STUDENT CROSS JOIN COURSE CROSS JOIN SECTION CROSS JOIN GRADE;
Calculator Language Feedback Hel
1 SELECT DISTINCT *
relation after applying the CROSS JOIN operation is a relation with 4 + 5 = 9 attributes and
2 FROM STUDENT CROSS JOIN COURSE CROSS JOIN SECTION CROSS JOIN GRADE
has one record for each combination of records. In this example, since the COURSE relation
has 4 rows and the SECTION relation has 5 rows, the COURSE × SECTION results in a relation of 4 x 5 = 20 records. As you can see, by selecting only two attributes Instructor and Dept after applying the CROSS JOIN operation, and then remove any redundant records, the result will be a relation with 2 attributes and 8 records.
• The following example shows the number of the resulting rows after cross joining all the four relations in our dataset. Notably, the resulting relation is a rectangular dataset with a dimension of 400 rows and 17 columns. Why?
execute selection
GRADE 5 rows
⨯ 400 rows
STUDENT 4 rows
SECTION 5 rows
COURSE 4 rows
( ( STUDENT ⨯ COURSE ) ⨯ SECTION ) ⨯ GRADE
STUDENT.ID
STUDENT.Name
STUDENT.Major STUDENT.Dept
‘Bioengineering’ ‘CS’
COURSE.Name
‘Data Structures’
COURSE.Code
‘Bioengineering’ ‘CS’
Structures’
mber tring
e string e string
2 FROM STUDENT AS S INNER JOIN GRADE AS G 3 ON S.ID = G.Std_ID;
SQL Operations
select from where group having order limit
1 SELECT distinct *
The INNER JOIN Operation (./)
Calculator
CIND110 – Lab 05
Feedback Help ,theINNER JOINoperation, denoted by the bow tie symbol (./), can be used. The joining attributes should be specified for the INNER JOIN operation to operate. Assume we want to retrieve the name of the student of each listed grade. To get this information, we need to combine each GRADE record with the STUDENT record whose student ID matches the Std_ID in the GRADE relation. This can be achieved using the INNER JOIN operation as follows.
SELECT DISTINCT *
FROM STUDENT AS S INNER JOIN GRADE AS G
ON S.ID = G.Std_ID;
execute query download
⨝ S.ID = G.Std_ID 5 rows
STUDENT 4 rows
GRADE 5 rows
ρ S STUDENT ⨝ S.ID = G.Std_ID ρ G GRADE
16 17 17 18 19
S.Name S.Major S.Dept G.Std_ID
G.Sec_ID G.Pct_Grade
G.Ltr_Grade
‘Ryan’ ‘Ryan’ ‘Sally’
‘Bioengineering’ ‘CS’ 16