database management system - gnuopen.gnu.ac.kr/lecslides/2017-2-db/dblec02_relalgebra... · 2017....
Post on 17-Mar-2021
1 Views
Preview:
TRANSCRIPT
DatabaseManagementSystem
Lecture2
RelationalAlgebraandSQL
*SomematerialsadaptedfromR.Ramakrishnan,J.Gehrke
Today’sAgenda• RelationalAlgebra
• ComplexSQL
2DatabaseManagementSystem
RelationalAlgebra
3DatabaseManagementSystem
RelationalDBandAlgebra• SQL
• PracticaldefinitionofrelationalDB• OperatesonTables(bags)
• Operations• Keywords
• Statements:SELECT,FROM,WHERE,…
• RelationalAlgebra
• MathematicaldefinitionofRelationalDB• OperatesonRelations(Sets)
• Operations• set-basedoperations
• Intersection,Union,...
• Thedefaultistoproduceabagofrowsasaqueryresult
• Wantaset,useDISTINCT
4DatabaseManagementSystem
DescribingarelationalDBmathematically• Twoingredients• Arelationisasetoftuples
• Definequeryoperatorsasasetfunctions
5DatabaseManagementSystem
Recap:CrossproductwithSet• LetA={a,b,c}andB={1,2}
• Crossproductinsettheoryisdefinedasorderedpairs(2-tuples)whereeachpairconsistsofanelementfromAandB
• HowaboutA={a,b,c},B={1,2},andC={α,β}?
A× B={(a,1),(b,1),(c,1),(a,2),(b,2),(c,2)}
6DatabaseManagementSystem
DefiningRelations
• Anyinstanceoftherelationisalwaysasubset(⊆) of attributes
• name × sal × num × status
• Eachrelationinstanceisasubsetofthecrossproductofitsdomains
• oneelementofarelationiscalledtuple
• Arelationisalwaysasetbydefinition
Person(name,salary,num,status)name={allpossiblestringsof30characters}salary={realnumbersbetween0and100,000,000}num ={integerbetween0and9999}status={“a”,“b”}
7DatabaseManagementSystem
Recap:SetTheory
• Whatdothesereturn?• A∩B
• A ∪ B
• A– B
• A× B
A={1,3,5,7}B={1,2,3,4}
8DatabaseManagementSystem
RelationalAlgebrahasAdditionalOperations
• Introducingnewoperators(Cforcondition,Lforattributelist,Rforrenamingspecification)• A⋈cB
• A÷B
• 𝝈c(A)
• 𝜋 L(A)
• 𝜌 R(A)
A={1,3,5,7}B={1,2,3,4}
9DatabaseManagementSystem
RelationalAlgebraasaQueryLanguage• Wedon’tnormallyuserelationalalgebradirectly• Productsdon’tallowyoutowriterelationalalgebraqueries
• But,itisusedinternallyinaDBMStorepresentaqueryplan
• Itisalsooftenusedintheoreticalworkondatabases• (althoughfragmentsoffirstorderlogicarefrequentlyusedaswell...)
10DatabaseManagementSystem
RelationalAlgebraQueriesw/outOperators• WhatdoesthefollowingSQLqueryreturn?
• Answer:Student(Itiscalledidentityfunction)
• Arelationnamebyitselfisavalidrelationalalgebraquery
• Listingtherelationnamejustreturnsthetuplesintherelation
SELECT*FROMStudent;
StudentJohnCusackWill Smith
Student
11DatabaseManagementSystem
RelationalAlgebra:Selectionoperator(𝝈)
• Therelationalalgebraquery
• IssimilartotheSQLquery
Number Owner Balance Type7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
Account
𝝈 Balance<3000 (Account)
SELECT*FROMAccountWHEREBalance<3,000,000;
12DatabaseManagementSystem
RelationalAlgebra:Selectionoperator(𝝈)• Select(𝝈)isaunaryoperator:
• Itisalwaysappliedtoasinglerelation
𝝈 :R→R
𝝈 Balance<3000 (Account)
Selectoperator Relationorrelationalalgebraexpression
thepredicate(condition)AttributeComparator(≥,>,=,≠,<,≤)Attribute|Constant
13DatabaseManagementSystem
Exercises• 𝝈 Balance<3,000,000 (Account)
• 𝝈 Number<7003005 (Account)
• 𝝈 Balance=Number (Account)
• 𝝈 Type=“checking” (𝝈 Balance<3,000,000 (Account))
Number Owner Balance Type7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
Account
14DatabaseManagementSystem
RelationalAlgebra:ProjectionOperator(𝜋)
• Therelationalalgebraquery:
• IssimilartotheSQLquery
Number Owner Balance Type7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
Account
𝜋 Number,Owner (Account)
SELECTNumber,OwnerFROMAccount;
15DatabaseManagementSystem
RelationalAlgebra:Projectionoperator(𝜋)• Projection(𝜋)isaunaryoperator:
• Itisalwaysappliedtoasinglerelation
𝜋 :R→R
𝜋 Number,Owner (Account)
Projectionoperator Relationorrelationalalgebraexpression
Listofattributestokeep
16DatabaseManagementSystem
Example
Number Owner Balance Type7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
7003009 AlfredHitchcock 3,400,200 Checking
Account
𝜋 Owner (Account)SELECTNumberFROMAccount;
Vs.
OwnerJaneSmith
AlfredHitchcock
TakumiFujiwara
Brian Mills
Number7003001
7003003
7003005
7003007
7003009
• Relationsarealwayssets• Queryanswerisasetofnames• andJ.Smithappearsjustonce
intheanswer
17DatabaseManagementSystem
CombiningSelectandProject• Areanyoftheseequivalent?
𝜋 Owner(𝝈 Balance<3,000,000 (Account))𝝈 Balance<3,000,000(𝜋 Owner,Balance (Account))𝜋 Owner(𝝈 Balance<3,000,000(𝜋 Owner,Balance(Account)))𝝈 Type=“checking” (𝝈 Balance<3,000,000(𝜋 Owner,Balance(Account)))
Number Owner Balance Type7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
7003009 AlfredHitchcock 3,400,200 Checking
Account
18DatabaseManagementSystem
RelationalAlgebra:CrossProductoperator(×)• Usedinthebasicdefinitionofarelation• “Aninstanceofarelationisasubsetofthecrossproductofitsdomains”
• Isalsoanoperatorintherelationalalgebra
19DatabaseManagementSystem
Example• Supposewehavefollowingtworelations
• Thecrossproductproduceseverypossiblecombinationsofteacherandcourses
Teacher(TID,Tname)Course(CID,Cname)
TID Tname
101 Emma Thompson
105 BillyElliot
110 JohnWaine
TeacherCID Cname
346 HowtoAct
491 HowtoThink
Course
TID Tname CID Cname
101 Emma Thompson 346 HowtoAct
101 Emma Thompson 491 HowtoThink
105 BillyElliot 346 HowtoAct
105 BillyElliot 491 HowtoThink
110 JohnWaine 346 HowtoAct
110 JohnWaine 491 HowtoThink
TeacherXCourse
20DatabaseManagementSystem
SELECT*FROMTeacher,Course;
RelationalAlgebra:Joinoperator(⋈)• Join()isabinaryoperator
• Itisalwaysappliedtoatworelationsandreturnsone⋈:R× R→R
Account⋈Number=Account Deposit
relationorrelationalalgebraexpression
Relationorrelationalalgebraexpression
thejoinpredicate(condition)Attributecomparator(≥,>,=,≠,<,≤) Attribute
21DatabaseManagementSystem
RelationalAlgebra:Joinoperator(⋈)
• Therelationalalgebraquery
• isequivalentto
Account
Number Owner Balance Type
Accnt TxID Date Amount
Deposit
Account⋈Number=Accnt(Deposit)
𝝈 Number=Accnt (Account× Deposit)
22DatabaseManagementSystem
RelationalAlgebra:Joinoperator(⋈)• Thejoinoperatorisdefinedforconvenience
• Anyquerywithajoincanalwaysberewrittenintocrossproductfollowedbyselection
R1⋈a1=a2R2≡𝝈 a1=a2(R1× R2)
23DatabaseManagementSystem
NotesonJoin• EachsimpleBooleanpredicateinthejoinconditionmustcompareanattributefromonerelationtoanattributeintheotherrelation
• type=“checking”isnotajoincondition
• ifyouhaveajoinwithNOcondition,thenitisjustacrossproduct
Account⋈Number=Account^type=“checking”Deposit
24DatabaseManagementSystem
Examples
• S⋈advisor=fid(F)
• S⋈S.age <F.age (F)
• Themostcommonjoiniscalledaequi-join(forequalitycondition)
sid name advisor age
101 Bill 301 20
102 John 302 20
103 Edward 301 19
104 Albert 301 19
105 Thompson 302 19
SinstanceofStudentfid name age
301 Morrison 45
302 Groot 37
FinstanceofFaculty
R1⋈A1=A2R2
25DatabaseManagementSystem
select*fromStudentass,Facultyasfwheres.advisor =f.fid;
select*fromStudentass,Facultyasfwheres.age <f.age;
SQLstatementtoanrelationalAlgebraexpression
• SELECT-FROM-WHEREqueriesaresometimesdescribedasequivalenttotheSelect-Project-Join(SPJ)subsetofrelationalalgebra
SELECTDISTINCTattributesFROMT1,T2,…WHEREconditions
?𝜋 attributes(𝝈 conditions(T1× T2× … ))
26DatabaseManagementSystem
ComplexSQL
27DatabaseManagementSystem
MoreSQLqueryconstructs
SELECTFROMWHERE
………
(SELECT… FROM…WHERE…)
UNION(SELECT… FROM…WHERE…)
ORDERBYGROUPBYHAVING
………
1.2.3.
4.
5.
1.Extensions:SUM,COUNT,MIN,AVG,etc2.ExtensionsincludevariouskindsofJOINs3.Additionalcomparators,e.g.EXISTS,IN,ANY
4.OperatorsthattakestwoormorecompleteSQLqueriesasarguments,e.g.,UNIONandINTERSECT
5.Severaladditionalclauses,e.g.,ORDERBY,GROUPBY,andHAVING
28DatabaseManagementSystem
MoreSQLqueryconstructs
SELECTFROMWHERE
………
(SELECT… FROM…WHERE…)
UNION(SELECT… FROM…WHERE…)
ORDERBYGROUPBYHAVING
………
1.2.3.
4.
5.
1.Extensions:SUM,COUNT,MIN,AVG,etc2.ExtensionsincludevariouskindsofJOINs3.Additionalcomparators,e.g.EXISTS,IN,ANY
4.OperatorsthattakestwoormorecompleteSQLqueriesasarguments,e.g.,UNIONandINTERSECT
5.Severaladditionalclauses,e.g.,ORDERBY,GROUPBY,andHAVING
29DatabaseManagementSystem
SampleDatabase• Let’sconsiderthefollowingDBfortheexamples
• WearegoingtootherDBstimetotime
Customer(Number,Name,Address,Crating,Camount,Cbalance,Salesperson)
Salesperson(Number,Name,Address,Office)
foreignkeycustomer.Salesperson ->Salesperson.Number
30DatabaseManagementSystem
SELECT(1/4)• AggregateOperators:COUNT,SUM,MIN,MAX,andAVG
• IfoneaggregateoperatorappearsintheSELECTclause• ALLOFTHEENTRIESintheselectclauseMUSTBEANAGGREGATEOPERATOR
• UnlessthequeryincludesaGROUPBYclause(moreonlater)
SELECTMIN(Cbalnace),MAX(Cbalance),AVG(Cbalance)FROMCustomer;
SELECTMIN(Cbalnace),MAX(Cbalance),AVG(Cbalance)FROMCustomerWHEREage>35;
31DatabaseManagementSystem
Stoptothink• Whatwould/shouldthequeryresultbe?
• Isitallowed?
SELECTName,Crating,AVG(Cbalance)FROMCustomer;
32DatabaseManagementSystem
SELECT (2/4)• Whatisthedifferencebetweenthesetwoqueries?
• Whenwillthesetwoqueriesreturnthesameanswer?• orwhataretheconditionsforittohappen
SELECTCOUNT(Name)FROMCustomer;
SELECTDISTINCTNameFROMCustomer;
Vs.
33DatabaseManagementSystem
SELECT (3/4)• WhatistheimplicationofusingDISTINCT• WhencomputingtheSUMorAVGofanattribute?
• WhencomputingtheMINorMAXofanattribute?
SUM(DISTINCT(AGE))Vs.SUM(age)
MIN(DISTINCT(AGE))Vs.MIN(age)
TheSUMorAVGwillbecomputedonlydistinctvalues
NoDifference:theresultdoesnotdependonwhetherornotduplicatesareremoved
34DatabaseManagementSystem
SELECT (4/4)• SELECTclauselistcanalsoincludesimplearithmeticexpressionsusing+,-,*,/
SELECT(Camount – Cbalance)ASAvailableCredit,NameFROMCustomerWHERECamount >0
35DatabaseManagementSystem
MoreSQLqueryconstructs
SELECTFROMWHERE
………
(SELECT… FROM…WHERE…)
UNION(SELECT… FROM…WHERE…)
ORDERBYGROUPBYHAVING
………
1.2.3.
4.
5.
1.Extensions:SUM,COUNT,MIN,AVG,etc2.ExtensionsincludevariouskindsofJOINs3.Additionalcomparators,e.g.EXISTS,IN,ANY
4.OperatorsthattakestwoormorecompleteSQLqueriesasarguments,e.g.,UNIONandINTERSECT
5.Severaladditionalclauses,e.g.,ORDERBY,GROUPBY,andHAVING
36DatabaseManagementSystem
FROM:SyntacticSugarsandnewoperators• ThereareanumberofjointypesthatcanbeexpressedinFROMclause• Innerjoin(theregularjoin)
• Crossjoin
• naturaljoin
• leftouterjoin
• rightouterjoin
• fullouterjoin
syntacticsugarsthatcanbeexpressedusingSELECT-FROM-WHEREqueries
Newoperators
37DatabaseManagementSystem
FROM• Thesetwoqueriesareequivalent
SELECTC.Name,S.NameFROMCustomerCJOIN SalespersonSON C.Salesperson =S.NumberWHEREC.Crating <6;
1.
2. SELECTC.Name,S.NameFROMCustomerC,SalespersonSWHEREC.Salesperson =S.Number ANDC.Crating <6;
𝜋 C.Name,S.Name(𝝈C.CreditRating <6(Customer⋈C.Salesperson =S.Number Salesperson))
𝜋 C.Name,S.Name(𝝈C.CreditRating <6^C.Salesperson=S.Number(Customer× Salesperson))
38DatabaseManagementSystem
FROM:JOINwithUSINGclause• JOINwithUSINGclausewhenattributesinthe2tableshavethesamename
• TheseTwoqueriesareequivalent
• USINGclausedoesn’tneed(andcan’thave)acorrelationname
Course(CNumber,CName,Description)Teacher(TNumber,TName,Phone)Offering(CNumber,TNumber,Time,Days,Room)
SELECTC.CNumber,C.CName,RoomFROMCourseCJOIN OfferingUSING(CNumber);
SELECTC.CNumber,C.Name,RoomFROMCourseCJOIN OfferingOON C.CNumber=O.CNumber;
39DatabaseManagementSystem
FROM:BasicJoin≡(INNER)JOIN• FortheINNERJOIN
• Thequeryresultincludesall“matches”butexcludes• customerrowsthatdonothaveaSalesperson
• Salespersonrowsthatarenotassignedtoanycustomers
• Thekeyword“INNER”isoptional• abovequeryisequivalentto
SELECTC.Name,S.NameFROMCustomerCINNERJOINSalespersonSON C.Salesperson =S.Number;
SELECTC.Name,S.NameFROMCustomerCJOINSalespersonSON C.Salesperson =S.Number;
40DatabaseManagementSystem
FROM:crossproduct≡CROSSJOIN• Thefollowingqueriesareequivalent
SELECT*FROMCustomer,Salesperson;
SELECT*FROMCustomerCROSSJOINSalesperson;
41DatabaseManagementSystem
FROM:Equi-Jioin vs.Natual Join(1/3)• Whenthejoinisbasedonequalityofattributes,wealwayshavetwoidenticalattributesintheresult
Name DeptID
Smith 1
James 2
Brown 3
Johnson 1
Robert
FacultyDeptID DeptName
1 Engineering
2 Communications
3 Marketing
Department
SELECT*FROMFacultyFINNERJOINDepartmentDONF.DeptID =D.DeptID;F.Name F.DeptID D.DeptID D.DeptName
Smith 1 1 Engineering
Johnson 1 1 Engineering
James 2 2 Communication
Brown 3 3 Markeing
Equi-Join
42DatabaseManagementSystem
FROM:Equi-Jioin vs.Natual Join(1/3)• Equi-JoinwiththeUSINGconstruct:applicablewithcolumnshavingsamename
Name DeptID
Smith 1
James 2
Brown 3
Johnson 1
FacultyDeptID DeptName
1 Engineering
2 Communications
3 Marketing
Department
SELECT*FROMFacultyFINNERJOINDepartmentDUSING(DeptID);Name DeptID DeptName
Smith 1 Engineering
Johnson 1 Engineering
James 2 Communication
Brown 3 Markeing
Equi-JoinwithUSINGconstruct
43DatabaseManagementSystem
FROM:Equi-Jioin vs.Natual Join(3/3)• NATURALJOIN:Equi-Joinwithonlyonecolumnforeachequallynamedcolumns
Name DeptID
Smith 1
James 2
Brown 3
Johnson 1
FacultyDeptID DeptName
1 Engineering
2 Communications
3 Marketing
Department
SELECT*FROMFacultyNATURALJOINDepartment;
Name DeptID DeptName
Smith 1 Engineering
Johnson 1 Engineering
James 2 Communication
Brown 3 Markeing
NATURALJOINIf you don’t specify which attributes to join on, natural join will join on all attributes with the same name
44DatabaseManagementSystem
FROM:moreonNATURALJOIN(1/2)• NATURALJOINislikea“macro”thatjoinstableswithanequalityconditionforallattributeswiththesamename
• NATURALJOINdropsoneofduplicatecolumnsautomatically
Course(CNumber,CName,Description)
Teacher(TNumber,TName,Phone)
Offering(CNumber,TNumber,Time,Days,Room)
45DatabaseManagementSystem
FROM:moreonNATURALJOIN (2/2)• Listthecourseandteachernameforallcourseofferings
• ThisquerycanbeexpressedwiththeNATURALJOINorwithanINNERJOIN• Thesetwoqueriesareequivalent
• Theyareequivalentbecausethejoinattributeshavethesameattributenames
• Butisitalwaysuseful?
SELECTCName,TNameFROMCourseC,OfferingO,TeachingTWHEREC.CNumber =O.CNumber ANDO.TNumber =T.Tnumber
SELECTCName,TNameFROMCourseNATURALJOINOfferingNATURALJOINTeacher;
46DatabaseManagementSystem
FROM:INNERJOINVs.OUTERJOIN(1/2)• FortheINNERJOIN
• thequeryresultdoesnotinclude(p.40)• acustomerthatdoesnothaveasalesperson
• asalespersonthatisnotassignedtoanycustomers
SELECTC.Name,S.NameFROMCustomerCINNERJOINSalespersonSONC.Salesperson =S.Number
47
Number Name Address Crating Camount Cbalance Salesperson
1 Smith 1st Str. 700 10,000 9,000 55
2 Jones 2nd Str. 700 8,000 4,000 77
3 Mills 3rd Str. 700 11,000 8,000 NULL
Customer
Number Name Address Office
55 Miller 5th Str. 101
77 Khan 7th Str. 102
83 Dunham 8th Str. 103
Salesperson
DatabaseManagementSystem
FROM: INNERJOINVs.OUTERJOIN(2/2)• AnINNER(regular)JOINincludesonlythosecustomersthathavesalespersons(onlythematches)
• ALEFTOUTERJOINwillincludeallmatchesplusall– customersthatdonothaveaSalesperson
• ARIGHTOUTERJOINwillincludeallmatchesplusall– salespersonsthatarenotassignedtoanycustomers
• AFULLOUTERJOINwillincludeallofthese
48
SELECTC.Name,S.NameFROMCustomerasCINNERJOINSalespersonasS
ONC.Salesperson =S.Number;
DatabaseManagementSystem
FROM:LEFTOUTERJOIN
49
Number Name Address Crating Camount Cbalance Salesperson
1 Smith 1st Str. 700 10,000 9,000 55
2 Jones 2nd Str. 700 8,000 4,000 77
3 Mills 3rd Str. 700 11,000 8,000 NULL
Customer
Number Name Address Office
55 Miller 5th Str. 101
77 Khan 7th Str. 102
83 Dunham 8th Str. 103
Salesperson
INNERJOIN onC.Salesperson =S.Number gives:
LEFTOUTERJOINonC.Salesperson =S.Number gives:
1 Smith 1st Str. 700 10,000 9,000 55 55 Miller 5th Str. 101
2 Jones 2nd Str. 700 8,000 4,000 77 77 Khan 7th Str. 102
1 Smith 1st Str. 700 10,000 9,000 55 55 Miller 5th Str. 101
2 Jones 2nd Str. 700 8,000 4,000 77 77 Khan 7th Str. 102
3 Mills 3rd Str. 700 11,000 8,000 NULL NULL NULL NULL NULL
DatabaseManagementSystem
FROM:RIGHTOUTERJOIN
50
Number Name Address Crating Camount Cbalance Salesperson
1 Smith 1st Str. 700 10,000 9,000 55
2 Jones 2nd Str. 700 8,000 4,000 77
3 Mills 3rd Str. 700 11,000 8,000 NULL
Customer
Number Name Address Office
55 Miller 5th Str. 101
77 Khan 7th Str. 102
83 Dunham 8th Str. 103
Salesperson
INNERJOIN onC.Salesperson =S.Number gives:
RIGHTOUTERJOINonC.Salesperson =S.Number gives:
1 Smith 1st Str. 700 10,000 9,000 55 55 Miller 5th Str. 101
2 Jones 2nd Str. 700 8,000 4,000 77 77 Khan 7th Str. 102
1 Smith 1st Str. 700 10,000 9,000 55 55 Miller 5th Str. 101
2 Jones 2nd Str. 700 8,000 4,000 77 77 Khan 7th Str. 102
NULL NULL NULL NULL NULL NULL NULL 83 Dunham 8th Str. 103
DatabaseManagementSystem
FROM:FULLOUTERJOIN
51
Number Name Address Crating Camount Cbalance Salesperson
1 Smith 1st Str. 700 10,000 9,000 55
2 Jones 2nd Str. 700 8,000 4,000 77
3 Mills 3rd Str. 700 11,000 8,000 NULL
Customer
Number Name Address Office
55 Miller 5th Str. 101
77 Khan 7th Str. 102
83 Dunham 8th Str. 103
Salesperson
INNERJOIN onC.Salesperson =S.Number gives:
RIGHTOUTERJOINonC.Salesperson =S.Number gives:
1 Smith 1st Str. 700 10,000 9,000 55 55 Miller 5th Str. 101
2 Jones 2nd Str. 700 8,000 4,000 77 77 Khan 7th Str. 102
1 Smith 1st Str. 700 10,000 9,000 55 55 Miller 5th Str. 101
2 Jones 2nd Str. 700 8,000 4,000 77 77 Khan 7th Str. 102
3 Mills 3rd Str. 700 11,000 8,000 NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL NULL 83 Dunham 8th Str. 103
DatabaseManagementSystem
*notsupportedinmysql
FROM:aformofsubquery• YoucanputacompletequeryexpressionintheFROMclause• alsoknownasnestedqueriesorsubqueries
• Parenthesesareimportant
52
SELECT...FROMEmployeeE,(SELECT...FROM...WHERE...)WHERE...
DatabaseManagementSystem
RelationalAlgebraOperators
53DatabaseManagementSystem
Eightstandardrelationalalgebraoperators• 𝜋 project Wehaveseenalready
• 𝝈 select Wehaveseenalready
• ∪ union Fromsettheory
• ∩ intersect Fromsettheory
• – difference Fromsettheory
• × crossproduct Wehaveseenalready
• ⋈ join Wehaveseenalready
• ÷ divide
• 𝜌 renaming
54
canonlyusedwithunion-compatiblerelations
DatabaseManagementSystem
Union-compatiblerelations• Tworelationsareunion-compatibleif• havesamenumberofattributes
• havesamedomains
• Example
55
Checking(CNum: int, COwner: string, CBalance: int)
Savings(SNum: int, SOwner: string, SBalance: int)
DatabaseManagementSystem
Example: ∪ union
56
Cnum Cowner Cbalance101 Smith 1000
102 Mills 2000
104 Jones 1000
105 Schwab 3000
Checking
Snum Sowner Sbalance103 Smith 5000
Savings
Checking∪SavingsCnum Cowner Cbalance101 Smith 1000
102 Mils 2000
104 Jones 1000
105 Schwab 3000
103 Smith 5000
notethatattributesarefromthefirstrelationinthequery
DatabaseManagementSystem
SELECTCNum,COwner,CBalanceFROMCheckingUNIONSELECTSNum,SOwner,SBalanceFROMSavings;
Example: ∩ intersection
57
Cnum Cowner Cbalance101 Smith 1000
102 Mils 2000
104 Jones 1000
105 Schwab 3000
Checking
Snum Sowner Sbalance103 Smith 5000
Savings
Checking∩ Savings
𝜋Cowner(Checking)∩𝜋 Sowner(Savings)
?Itisempty– notuplesappearinbothrelations
Smith– theonlyownerinSavingsAcount
DatabaseManagementSystem
Example:– difference• FindalltuplesthatareintheCheckingrelationbutarenotintheSavingsrelation
• EveryoneinCheckingexceptSmith
58
CheckingAccount −SavingsAccount
𝜋 COwner(CheckingAccount)−𝜋 SOwner(SavingsAccount)
DatabaseManagementSystem
*notsupportedinmysql
WorkaroundfordifferenceoperationexamplequerySELECT*FROMpLEFTOUTERJOINqONp.id =q.id WHEREq.id ISNULL
MoreSQLqueryconstructs
SELECTFROMWHERE
………
(SELECT… FROM…WHERE…)
UNION(SELECT… FROM…WHERE…)
ORDERBYGROUPBYHAVING
………
1.2.3.
4.
5.
1.Extensions:SUM,COUNT,MIN,AVG,etc2.ExtensionsincludevariouskindsofJOINs3.Additionalcomparators,e.g.EXISTS,IN,ANY
4.OperatorsthattakestwoormorecompleteSQLqueriesasarguments,e.g.,UNIONandINTERSECT
5.Severaladditionalclauses,e.g.,ORDERBY,GROUPBY,andHAVING
59DatabaseManagementSystem
UNIONandINTERSECTION• TwocompletequerieswithUNIONinbetween
60
• TwocompletequerieswithINTERSECTinbetween
(SELECTC.NameFROMCustomerCWHEREC.Name LIKE“B%”)UNION(SELECTS.NameFROMSalespersonSWHERES.Name LIKE“B%”);
(SELECTC.NameFROMCustomerC)INTERSECT(SELECTS.NameFROMSalespersonS);
• TwocompletequerieswithEXCEPT(i.e.,DIFFERENCE)inbetween• MySQLdoesn’tsupportEXCEPT
(SELECT C.NameFROM Customer C) EXCEPT(SELECT S.NameFROM Salesperson S);
DatabaseManagementSystem
ALLinUNION,INTERSECT,andEXCEPT• Ifyoudon’tspecifyALL,theresultiscomputedonsets• Eliminateduplicatesfromfirstoperand
• Eliminateduplicatesfromsecondoperand
• Computeoperation
• Eliminateduplicatesfromresult
• Notethedifferenceandchosewisely• UNIONVs. UNIONALL
• INTERSECTVs. INTERSECTALL
• EXCEPTVs.EXCEPTALL
61DatabaseManagementSystem
MoreSQLqueryconstructs
SELECTFROMWHERE
………
(SELECT… FROM…WHERE…)
UNION(SELECT… FROM…WHERE…)
ORDERBYGROUPBYHAVING
………
1.2.3.
4.
5.
1.Extensions:SUM,COUNT,MIN,AVG,etc2.ExtensionsincludevariouskindsofJOINs3.Additionalcomparators,e.g.EXISTS,IN,ANY
4.OperatorsthattakestwoormorecompleteSQLqueriesasarguments,e.g.,UNIONandINTERSECT
5.Severaladditionalclauses,e.g.,ORDERBY,GROUPBY,andHAVING
62DatabaseManagementSystem
GROUPBY• AnySQLquerycanhavetheanswer“grouped”• oneoutputrowforeachgroup
63
SELECTSalesperson,COUNT(*)FROMCustomer;
SELECTSalesperson,COUNT(*)FROMCustomerGROUPBYSalesperson;
Number Name Address Crating Camount Cbalance Salesperson
1 Smith 1st Str. 700 10,000 9,000 55
2 Jones 2nd Str. 700 8,000 4,000 77
3 Mills 3rd Str. 700 11,000 8,000 NULL
CustomerSalesperson COUNT(*)
55 1
77 1
NULL 1
DatabaseManagementSystem
GROUPBY
64
SELECTSalesperson,COUNT(*)FROMCustomerGROUPBYSalesperson;
Number Name Address Crating Camount Cbalance Salesperson
1 Smith 1st Str. 700 10,000 9,000 55
2 Jones 2nd Str. 700 8,000 4,000 77
3 Mills 3rd Str. 700 11,000 8,000 NULL
4 Bill 4th Str. 700 13,000 5,000 55
5 Jane 5th Str. 800 3,000 3,000 55
6 Harley 8th Str. 700 2,000 8,000 20
7 Khale 9th Str. 900 6,000 1,000 77
Customer
DatabaseManagementSystem
Example:GROUPBY
65
SELECTSalesperson,COUNT(*)FROMCustomerGROUPBYSalesperson;
1.Makegroupsresultingin4Groups2.Evaluate“SELECTSalesperson,Count(*)”foreachgroup
Number Name Address Crating Camount Cbalance Salesperson
1 Smith 1st Str. 700 10,000 9,000 55
2 Jones 2nd Str. 700 8,000 4,000 77
3 Mills 3rd Str. 700 11,000 8,000 NULL
4 Bill 4th Str. 700 13,000 5,000 55
5 Jane 5th Str. 800 3,000 3,000 55
6 Harley 8th Str. 700 2,000 8,000 20
7 Khale 9th Str. 900 6,000 1,000 77
CustomerSalesperson COUNT(*)
55 3
NULL 1
77 2
20 1
DatabaseManagementSystem
SQLHAVING• HAVINGclausespecifiesapredicateevaluatedagainsteachgroup
• AgroupisintheresultifitsatisfiestheHAVINGcondition
66
SELECTSalesperson,COUNT(*)FROMCustomerGROUPBYSalespersonHAVINGCOUNT(*)>1;
Number Name Address Crating Camount Cbalance Salesperson
1 Smith 1st Str. 700 10,000 9,000 55
2 Jones 2nd Str. 700 8,000 4,000 55
3 Mills 3rd Str. 700 11,000 8,000 NULL
CustomerSalesperson COUNT(*)
55 2
DatabaseManagementSystem
Example:GROUPBY
67
SELECTSalesperson,COUNT(*)FROMCustomerGROUPBYSalespersonHAVINGCOUNT(*)>1;
1.Makegroupsresultingin4Groups2.CheckifCOUNT(*)>1holds3.Evaluate“SELECTSalesperson,Count(*)”foreachgroup
Number Name Address Crating Camount Cbalance Salesperson
1 Smith 1st Str. 700 10,000 9,000 55
2 Jones 2nd Str. 700 8,000 4,000 77
3 Mills 3rd Str. 700 11,000 8,000 NULL
4 Bill 4th Str. 700 13,000 5,000 55
5 Jane 5th Str. 800 3,000 3,000 55
6 Harley 8th Str. 700 2,000 8,000 20
7 Khale 9th Str. 900 6,000 1,000 77
CustomerSalesperson COUNT(*)
55 3
NULL 1
77 2
20 1
Salesperson COUNT(*)
55 3
77 2
DatabaseManagementSystem
NoteonGROUPBY,HAVING• Theonlyattributethatcanappearina“grouped”queryare• thegroupingattributes
• aggregateoperatorsthatareappliedtothegroup
• Thus,thefollowingisnotlegal
• Becausether canbemorethanonenameforeachgroup
68
SELECTNameFROMCustomerGROUPBYSalesperson;
DatabaseManagementSystem
Exercise
• WriteSQLqueriesforthefollowing• Averagenumberofwinsandlossesacrossteams
• Averagenumberofwinsandlossesperconference
• Battingaverageforeachplayer,wherebattingaverageisthenumberofhitsdividedbyatbats
69
Team(Name,Games,Wins,Losses,Conference)
Player(Name,Hits,AtBats,HomeRuns,Team)Player.Team ->Team.Name
DatabaseManagementSystem
ORDERBY• Sorttheresultofaquery
70
SELECTNumber,Name,SalespersonFROMCustomerORDERBYName;
Number Name … Salesperson
1 Smith … 55
2 Jones … 77
3 Mills … NULL
4 Bill … 55
5 Jane … 55
6 Harley … 20
7 Khale … 77
Customer
Number Name … Salesperson
4 Bill … 55
6 Harley … 20
5 Jane … 55
2 Jones … 77
7 Khale … 77
3 Mills … NULL
1 Smith … 55
Customer
DatabaseManagementSystem
ORDERBY• Sorttheresultofaquery
71
SELECTNumber,Name,SalespersonFROMCustomerORDERBYNameDESC;
Number Name … Salesperson
1 Smith … 55
2 Jones … 77
3 Mills … NULL
4 Bill … 55
5 Jane … 55
6 Harley … 20
7 Khale … 77
Customer
Number Name … Salesperson
1 Smith … 55
3 Mills … NULL
7 Khale … 77
2 Jones … 77
5 Jane … 55
6 Harley … 20
4 Bill … 55
Customer
DatabaseManagementSystem
ORDERBY• Sorttheresultofaquery
72
SELECTNumber,Name,SalespersonFROMCustomerORDERBYName,Salesperson;
Number Name … Salesperson
1 Smith … 55
2 Jones … 77
3 Mills … NULL
4 Bill … 55
5 Jane … 55
6 Harley … 20
7 Khale … 77
8 Bill … 20
Customer
Number Name … Salesperson
8 Bill … 20
4 Bill … 55
6 Harley … 20
5 Jane … 55
2 Jones … 77
7 Khale … 77
3 Mills … NULL
1 Smith … 55
Customer
DatabaseManagementSystem
Subqueries• Itcanbeusedinthewhereclause(inadditiontotheFROMclause)
• Innerqueryreturns• Asinglevaluethatrepresentsmaxcreditrating
• Outerqueryreturns• Thenameandnumberofthecustomerwiththehighestcreditratings
73
SELECTC1.Number,C1.NameFROMCustomerC1WHEREC1.CRating=(SELECTMAX(C2.Crating)
FROMCustomerC2); Innerquery
Outerquery
DatabaseManagementSystem
Example
1. FROMclauseinouterquery2. TakearowfromtheCustomertable3. CheckiftherowsatisfiestheWHEREclause4. Evaluatetheinnerquery(result:800)5. EvaluateifCratingisequaltotheresult
74
SELECTC1.Number,C1.NameFROMCustomerC1WHEREC1.CRating=(SELECTMAX(C2.Crating)
FROMCustomerC2);
Number Name Address Crating Camount Cbalance Salesperson
1 Smith 1st Str. 200 10,000 9,000 55
2 Jones 2nd Str. 800 8,000 4,000 55
3 Mills 3rd Str. 700 11,000 8,000 NULL
Customer
DatabaseManagementSystem
Subqueries• Subqueriescanbeusedinthewhereclause(inadditiontothefromclause)
• SixComparators:=, >, < >=, <=, <> (not equal) • innerquerymustreturnasinglevalue
• Iftheinnerquerydoesnotmentionanyattributesfromtheouterquery(C1notmentionedintheinnerquery)• Thenyouonlyneedtoevaluatetheinnerqueryonce
• Theinner(sub)queryisNOTcorrelated
75
SELECTC1.Number,C1.NameFROMCustomerC1WHEREC1.CRating=(SELECTMAX(C2.Crating)
FROMCustomerC2);
DatabaseManagementSystem
Subqueries:SOME/ALLcomparison
• ForSOME,theexpressionmustbetrueforatleastonerow inthesubqueryanswer• “ANY”isequivalenttoSOME
• Whatdoesthisqueryreturn?
76
SELECTS.NameFROMSalespersonSWHERES.Name =SOME(SELECTC.Salesperson
FROMCustomerCWHEREC.CRating =700);
The name of each salespeople that has a customer with a credit rating of 700
DatabaseManagementSystem
Subqueries:SOME/ALLcomparison
• ForALL,theexpressionmustbetrueforallrowsinthesubqueryanswer
• Whatdoesthisqueryreturn?
77
SELECTS.NameFROMSalespersonSWHERES.Name =ALL(SELECTC.Salesperson
FROMCustomerCWHEREC.CRating =700);
Thenameofthesalespersonthathasallthecustomerswitharatingof700(ifsuchasalespersonexists)
DatabaseManagementSystem
Subqueries:IN/NOTINcomparison(1/4)
• WithIN,theattributematchesatleastonevaluereturnedfromthesubquery• Sameas“=SOME”
78
SELECTC1.Number,C1.NameFROMCustomerC1WHEREC1.NameIN(SELECTName
FROMSalesperson);
DatabaseManagementSystem
Subqueries:IN/NOTINcomparison(2/4)
• WithNOTIN,theattributematchesnone ofthevaluesreturnedfromthesubquery• Sameas“<>ALL”
79
SELECTC1.Number,C1.NameFROMCustomerC1WHEREC1.Name NOTIN(SELECTName
FROMSalesperson);
DatabaseManagementSystem
Subqueries:IN/NOTINcomparison(3/4)• Aretheseequivalent?
• DoweneedtouseDISTINCTforthesetobeequivalent?
• Isthesubquerycorrelated?
80
SELECTS.Number,S.NameFROMSalespersonSWHERES.Number IN (SELECTC.Salesperson
FROMCustomerC);
SELECTDISTINCT S.Number,S.NameFROMSalespersonS,CustomerCWHERES.Number =S.Salesperson;
DatabaseManagementSystem
Subqueries:IN/NOTINcomparison(4/4)
• Becausethesubquerymentionsanattributefromatableintheouterquery• Thesubquerymustbe(re-)evaluatedforeachrowintheouterquery(eachtimetheWHEREclauseisevaluated)
• Correlatedsubqueriescanbeveryexpensive!
81
SELECTS.Number,S.NameFROMSalespersonSWHERES.Number IN(SELECTC.Salesperson
FROMCustomerCWHEREC.Name =S.Name);
DatabaseManagementSystem
Subqueries:EXISTS/NOTEXISTS(1/2)
• Iftheanswertothesubqueryisnotempty...thentheEXISTSpredicatereturnsTRUE• Isthissubquerycorrelated?
• Whatdoesthisqueryreturn?
82
SELECTC.NameFROMCustomerCWHEREEXISTS (SELECT*
FROMSalespersonSWHERES.Number =C.Salesperson AND
S.Name =C.Name);
DatabaseManagementSystem
Subqueries:EXISTS/NOTEXISTS(2/2)
• Fourpredicatescanbeappliedtoasubquery• EXISTS :isthesubqueryanswernon-empty?
• NOTEXISTS:isthesubqueryanswerempty?
• UNIQUE :doesthesubqueryreturnjustonerow?
• NOTUNIQUE:doesthesubqueryreturnmultiplerows?
83
SELECTC.NameFROMCustomerCWHEREEXISTS (SELECT*
FROMSalespersonSWHERES.Number =C.Salesperson AND
S.Name =C.Name);
DatabaseManagementSystem
MissingRelationalAlgebraOperatorDivide
84DatabaseManagementSystem
DivideOperator(p.54)• Supposewehaveaextratableinourdatabase
• Howdowefindcustomersthathaveatleastoneaccountofeachaccounttype?
85
Number Owner Balance Type7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
Account
TypeChecking
Savings
AccountTypes
𝜋Owner,Type(Account) ÷ AccountTypesFindaccountownerswhohaveALLtypesofaccounts
DatabaseManagementSystem
ForNextWeek• Review– Quizonthematerial• Ch.4to4.2• Ch.5.5
• Readingassignments• Ch.2-2.5• Ch.3.5
• Besureyouunderstand• Aggregateoperations• howjoinoperates• setoperators• GROUPBY,HAVING,ORDERBY,Subqueries
86DatabaseManagementSystem
top related