grantweddell - david r. cheriton school of computer...
TRANSCRIPT
![Page 1: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/1.jpg)
Relational Algebra
Grant Weddell
Cheriton School of Computer ScienceUniversity of Waterloo
CS 348Introduction to Database Management
Winter 2017
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 1 / 15
![Page 2: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/2.jpg)
Database Schema Used in Examples
RespEmpDeptNoProjNo
EmPTime
Project
EmEnDate
Emp_Act
EmpNoMajProj
MidInitLastName
Employee
WorkDeptHireDateSalary
FirstName
EmpNo
DeptNameMgrNoAdmrDept
DeptNo
Department
ProjNoActNoEmStDate
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 2 / 15
![Page 3: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/3.jpg)
Relational Algebra
• the relational algebra consists of a set of operators• relational algebra is closed
• each operator takes as input zero or more relations• each operator defines a single output relation in terms of its input
relation(s)• relational operators can be composed to form expressions that
define new relations in terms of existing relations.
• Notation:R is a relation name; E is a relational algebra expression
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 3 / 15
![Page 4: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/4.jpg)
Primary Relational Operators
• Relation Name: R
• Selection: �condition(E)• result schema is the same as E ’s• result instance includes the subset of the tuples of E that each
satisfies the condition• Projection: �attributes(E)
• result schema includes only the specified attributes• result instance could have as many tuples as E , except that
duplicates are eliminated
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 4 / 15
![Page 5: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/5.jpg)
Primary Relational Operators
• Relation Name: R• Selection: �condition(E)
• result schema is the same as E ’s• result instance includes the subset of the tuples of E that each
satisfies the condition
• Projection: �attributes(E)• result schema includes only the specified attributes• result instance could have as many tuples as E , except that
duplicates are eliminated
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 4 / 15
![Page 6: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/6.jpg)
Primary Relational Operators
• Relation Name: R• Selection: �condition(E)
• result schema is the same as E ’s• result instance includes the subset of the tuples of E that each
satisfies the condition• Projection: �attributes(E)
• result schema includes only the specified attributes• result instance could have as many tuples as E , except that
duplicates are eliminated
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 4 / 15
![Page 7: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/7.jpg)
Primary Relational Operators (cont’d)
• Rename: �(R(F );E)• F is a list of terms of the form oldname ! newname• returns the result of E with columns renamed according to F .• remembers the result as R for future expressions
• Product: E1 � E2• result schema has all of the attributes of E1 and all of the attributes
of E2• result instance includes one tuple for every pair of tuples (one from
each expression result) in E1 and E2• sometimes called cross-product or Cartesian product• renaming is needed when E1 and E2 have common attributes
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 5 / 15
![Page 8: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/8.jpg)
Primary Relational Operators (cont’d)
• Rename: �(R(F );E)• F is a list of terms of the form oldname ! newname• returns the result of E with columns renamed according to F .• remembers the result as R for future expressions
• Product: E1 � E2• result schema has all of the attributes of E1 and all of the attributes
of E2• result instance includes one tuple for every pair of tuples (one from
each expression result) in E1 and E2• sometimes called cross-product or Cartesian product• renaming is needed when E1 and E2 have common attributes
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 5 / 15
![Page 9: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/9.jpg)
Cross Product Example
RAAA BBBa1 b1a2 b2a3 b3
SCCC DDDc1 d1c2 d2
R � SAAA BBB CCC DDDa1 b1 c1 d1a2 b2 c1 d1a3 b3 c1 d1a1 b1 c2 d2a2 b2 c2 d2a3 b3 c2 d2
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 6 / 15
![Page 10: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/10.jpg)
Select,Project,Product Examples
• Note: Use Emp to mean the Employee relation, Proj the projectrelation
• Find the last names and hire dates of employees who make morethan $100000.
�LastName ;HireDate(�Salary>100000(Emp))
• For each project for which department E21 is responsible, find thename of the employee in charge of that project.
�ProjNo;LastName(�DeptNo=E21(�RespEmp=EmpNo(Emp � Proj )))
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 7 / 15
![Page 11: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/11.jpg)
Select,Project,Product Examples
• Note: Use Emp to mean the Employee relation, Proj the projectrelation
• Find the last names and hire dates of employees who make morethan $100000.
�LastName ;HireDate(�Salary>100000(Emp))
• For each project for which department E21 is responsible, find thename of the employee in charge of that project.
�ProjNo;LastName(�DeptNo=E21(�RespEmp=EmpNo(Emp � Proj )))
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 7 / 15
![Page 12: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/12.jpg)
Select,Project,Product Examples
• Note: Use Emp to mean the Employee relation, Proj the projectrelation
• Find the last names and hire dates of employees who make morethan $100000.
�LastName ;HireDate(�Salary>100000(Emp))
• For each project for which department E21 is responsible, find thename of the employee in charge of that project.
�ProjNo;LastName(�DeptNo=E21(�RespEmp=EmpNo(Emp � Proj )))
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 7 / 15
![Page 13: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/13.jpg)
Select,Project,Product Examples
• Note: Use Emp to mean the Employee relation, Proj the projectrelation
• Find the last names and hire dates of employees who make morethan $100000.
�LastName ;HireDate(�Salary>100000(Emp))
• For each project for which department E21 is responsible, find thename of the employee in charge of that project.
�ProjNo;LastName(�DeptNo=E21(�RespEmp=EmpNo(Emp � Proj )))
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 7 / 15
![Page 14: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/14.jpg)
Joins
• Conditional join: E1 oncondition E2• equivalent to �condition (E1 � E2)• special case: equijoin
Proj on(RespEmp=EmpNo) Emp
• Natural join (E1 on E2)• The result of E1 on E2 can be formed by the following steps
1 form the cross-product of E1 and E2 (renaming duplicate attributes)2 eliminate from the cross product any tuples that do not have
matching values for all pairs of attributes common to schemas E1
and E2
3 project out duplicate attributes• if no attributes in common, this is just a product
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 8 / 15
![Page 15: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/15.jpg)
Joins
• Conditional join: E1 oncondition E2• equivalent to �condition (E1 � E2)• special case: equijoin
Proj on(RespEmp=EmpNo) Emp
• Natural join (E1 on E2)• The result of E1 on E2 can be formed by the following steps
1 form the cross-product of E1 and E2 (renaming duplicate attributes)2 eliminate from the cross product any tuples that do not have
matching values for all pairs of attributes common to schemas E1
and E2
3 project out duplicate attributes• if no attributes in common, this is just a product
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 8 / 15
![Page 16: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/16.jpg)
Example: Natural Join
• Consider the natural join of the Project and Department tables,which have attribute DeptNo in common
• the schema of the result will include attributes ProjName, DeptNo,RespEmp, MajProj, DeptName, MgrNo, and AdmrDept
• the resulting relation will include one tuple for each tuple in theProject relation (why?)
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 9 / 15
![Page 17: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/17.jpg)
Set-Based Relational Operators
• Union (R [ S):• schemas of R and S must be “union compatible”• result includes all tuples that appear either in R or in S or in both
• Difference (R � S):• schemas of R and S must be “union compatible”• result includes all tuples that appear in R and that do not appear
in S• Intersection (R \ S):
• schemas of R and S must be “union compatible”• result includes all tuples that appear in both R and S
• Union Compatible:• Same number of fields.• ’Corresponding’ fields have the same type
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 10 / 15
![Page 18: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/18.jpg)
Set-Based Relational Operators
• Union (R [ S):• schemas of R and S must be “union compatible”• result includes all tuples that appear either in R or in S or in both
• Difference (R � S):• schemas of R and S must be “union compatible”• result includes all tuples that appear in R and that do not appear
in S
• Intersection (R \ S):• schemas of R and S must be “union compatible”• result includes all tuples that appear in both R and S
• Union Compatible:• Same number of fields.• ’Corresponding’ fields have the same type
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 10 / 15
![Page 19: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/19.jpg)
Set-Based Relational Operators
• Union (R [ S):• schemas of R and S must be “union compatible”• result includes all tuples that appear either in R or in S or in both
• Difference (R � S):• schemas of R and S must be “union compatible”• result includes all tuples that appear in R and that do not appear
in S• Intersection (R \ S):
• schemas of R and S must be “union compatible”• result includes all tuples that appear in both R and S
• Union Compatible:• Same number of fields.• ’Corresponding’ fields have the same type
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 10 / 15
![Page 20: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/20.jpg)
Relational Division
XA B Ca1 b1 c1a1 b1 c2a1 b2 c2a2 b1 c1a2 b1 c2a2 b2 c2a2 b3 c3a3 b1 c1
SB Cb1 c1b1 c2b2 c2
X =SAa1a2
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 11 / 15
![Page 21: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/21.jpg)
Division is the Inverse of Product
RAa1a2
SB Cb1 c1b1 c2b2 c2
R � SA B Ca1 b1 c1a1 b1 c2a1 b2 c2a2 b1 c1a2 b1 c2a2 b2 c2
(R � S)=SAa1a2
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 12 / 15
![Page 22: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/22.jpg)
Summary of Relational Operators
E ::= Rj �condition(E)
j �attributes(E)
j �(R(F );E)
j E1 � E2j E1 oncondition E2j E1 on E2j E1 [ E2j E1 \ E2j E1 � E2j E1 = E2
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 13 / 15
![Page 23: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/23.jpg)
Algebraic Equivalences
• This:
�ProjNo;LastName(�DeptNo=E21(�RespEmp=EmpNo(E � P)))
• is equivalent to this:
�ProjNo;LastName(�DeptNo=E21(E onRespEmp=EmpNo P))
• is equivalent to this:
�ProjNo;LastName(E onRespEmp=EmpNo �DeptNo=E21(P))
• is equivalent to this:
�ProjNo;LastName( ( �LastName;EmpNo(E)) onRespEmp=EmpNo
( �ProjNo;RespEmp(�DeptNo=E21(P))))
• More on this topic later when we discuss database tuning. . .
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 14 / 15
![Page 24: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/24.jpg)
Algebraic Equivalences
• This:
�ProjNo;LastName(�DeptNo=E21(�RespEmp=EmpNo(E � P)))
• is equivalent to this:
�ProjNo;LastName(�DeptNo=E21(E onRespEmp=EmpNo P))
• is equivalent to this:
�ProjNo;LastName(E onRespEmp=EmpNo �DeptNo=E21(P))
• is equivalent to this:
�ProjNo;LastName( ( �LastName;EmpNo(E)) onRespEmp=EmpNo
( �ProjNo;RespEmp(�DeptNo=E21(P))))
• More on this topic later when we discuss database tuning. . .
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 14 / 15
![Page 25: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/25.jpg)
Algebraic Equivalences
• This:
�ProjNo;LastName(�DeptNo=E21(�RespEmp=EmpNo(E � P)))
• is equivalent to this:
�ProjNo;LastName(�DeptNo=E21(E onRespEmp=EmpNo P))
• is equivalent to this:
�ProjNo;LastName(E onRespEmp=EmpNo �DeptNo=E21(P))
• is equivalent to this:
�ProjNo;LastName( ( �LastName;EmpNo(E)) onRespEmp=EmpNo
( �ProjNo;RespEmp(�DeptNo=E21(P))))
• More on this topic later when we discuss database tuning. . .
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 14 / 15
![Page 26: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/26.jpg)
Algebraic Equivalences
• This:
�ProjNo;LastName(�DeptNo=E21(�RespEmp=EmpNo(E � P)))
• is equivalent to this:
�ProjNo;LastName(�DeptNo=E21(E onRespEmp=EmpNo P))
• is equivalent to this:
�ProjNo;LastName(E onRespEmp=EmpNo �DeptNo=E21(P))
• is equivalent to this:
�ProjNo;LastName( ( �LastName;EmpNo(E)) onRespEmp=EmpNo
( �ProjNo;RespEmp(�DeptNo=E21(P))))
• More on this topic later when we discuss database tuning. . .
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 14 / 15
![Page 27: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/27.jpg)
Algebraic Equivalences
• This:
�ProjNo;LastName(�DeptNo=E21(�RespEmp=EmpNo(E � P)))
• is equivalent to this:
�ProjNo;LastName(�DeptNo=E21(E onRespEmp=EmpNo P))
• is equivalent to this:
�ProjNo;LastName(E onRespEmp=EmpNo �DeptNo=E21(P))
• is equivalent to this:
�ProjNo;LastName( ( �LastName;EmpNo(E)) onRespEmp=EmpNo
( �ProjNo;RespEmp(�DeptNo=E21(P))))
• More on this topic later when we discuss database tuning. . .
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 14 / 15
![Page 28: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/28.jpg)
Relational Completeness
Definition (Relationally Complete)A query language that is at least as expressive as relational algebra issaid to be relationally complete.
• The following languages are all relationally complete:• safe relational calculus• relational algebra• SQL
• SQL has additional expressive power because it captures duplicatetuples, unknown values, aggregation, ordering, . . .
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 15 / 15
![Page 29: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/29.jpg)
Relational Completeness
Definition (Relationally Complete)A query language that is at least as expressive as relational algebra issaid to be relationally complete.
• The following languages are all relationally complete:• safe relational calculus• relational algebra• SQL
• SQL has additional expressive power because it captures duplicatetuples, unknown values, aggregation, ordering, . . .
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 15 / 15
![Page 30: GrantWeddell - David R. Cheriton School of Computer …gweddell/cs348/4a-algebra...DatabaseSchemaUsedinExamples RespEmp DeptNo ProjNo EmPTime Project EmEnDate Emp_Act EmpNo MajProj](https://reader033.vdocuments.us/reader033/viewer/2022051010/5ac161497f8b9a5a4e8d11d3/html5/thumbnails/30.jpg)
Relational Completeness
Definition (Relationally Complete)A query language that is at least as expressive as relational algebra issaid to be relationally complete.
• The following languages are all relationally complete:• safe relational calculus• relational algebra• SQL
• SQL has additional expressive power because it captures duplicatetuples, unknown values, aggregation, ordering, . . .
CS 348 (Intro to DB Mgmt) Relational Algebra Winter 2017 15 / 15