讲义参考成果数据库

Chapter2

TheRelationalModel2023/1/10DatabasePrinciples&Programming2Ch2TheRelationalModelDataModel(数据模型)isasetofdefinitionsdescribinghowreal-worlddataisconceptuallyrepresentedascomputerizedinformation.Italsodescribesthetypesofoperationsavailabletoaccessandupdatethisinformation.RelationalModel(关系模型)Object-RelationalModel(对象关系模型)2023/1/10DatabasePrinciples&Programming3Ch2TheRelationalModel2.1TheCAPDatabase2.2NamingthePartsofaDatabase2.3RelationalRules2.4Keys,Superkeys,andNullValues2.5RelationalAlgebra2.6Set-TheoreticOperations2.7NativeRelationalOperations2.8TheInterdependenceofOperations2.9IllustrativeExamples2.10OtherRelationalOperations2023/1/10DatabasePrinciples&Programming42.1TheCAPDatabaseExample:Figure2.1&2.2CAPDatabase:lookatpg.28,Figure2.2CUSTOMERS,AGENTS,PRODUCTS,ORDERS.DataModel:lookatpg.27,Figure2.10.00KyotoACMEc0068.00DuluthACMEc0048.00DallasAlliedc00312.00DallasBasicsc00210.00DuluthTipTopc001discntcitycnamecidCUSTOMERS2023/1/10DatabasePrinciples&Programming5Example:Figure2.1&2.2(cont.)aidanamecitypercenta01SmithNewYork6a02JonesNewark6a03BrownTokyo7a04GrayNewYork6a05OtasiDuluth5a06SmithDallas5AGENTS2023/1/10DatabasePrinciples&Programming6Example:Figure2.1&2.2(cont.)pidpnamecityquantitypriceP01combDallas1114000.50p02brushNewark2030000.50p03razorDuluth1506001.00p04penDuluth1253001.00p05pencilDallas2214001.00p06folderDallas1231002.00p07caseNewark1005001.00PRODUCTS2023/1/10DatabasePrinciples&Programming7ordnomonthcidaidpidqtydollars1011janc001a01p011000450.001012janc001a01p011000450.001019febc001a02p02400180.001017febc001a06p03600540.001018febc001a03p04600540.001023marc001a04p05500450.001022marc001a05p06400720.001025aprc001a05p07800720.001013janc002a03p031000880.001026mayc002a05p03800704.001015janc003a03p0512001104.001014janc003a03p0512001104.001021febc004a06p011000460.001016janc006a01p011000500.001020febc006a03p07600600.001024marc006a06p01800400.00ORDERS2023/1/10DatabasePrinciples&Programming82.1TheCAPDatabaseNoteMorecolumnsid,namediscnt,percent,quantity,price,month,qtycitydollarsMorerowsnotworowsinatableissameMoretablesuniqueidentifier2023/1/10DatabasePrinciples&Programming92.2NamingthePartsofaDatabaseTerminology(术语)Table(Relation)Old:fileofrecordsColumnnames(Attributes)Old:fieldnamesofrecordsRows(Tuples)Old:recordsofafileTableheading(Schema)Old:setofattributes2023/1/10DatabasePrinciples&Programming102.2NamingthePartsofaDatabase关系模型关系数据库管理系统(SQL)文件系统Relation（关系）Table（表）FileofRecordsAttribute（属性）Column（列）FieldTuple（元组）Row（行）RecordSchema（模式）TableHeading（表头）TypeofRecord2023/1/10DatabasePrinciples&Programming112.2NamingthePartsofaDatabase0.00KyotoACMEc0068.00DuluthACMEc0048.00DallasAlliedc00312.00DallasBasicsc00210.00DuluthTipTopc001discntcitycnamecidCUSTOMERStabletablenamecolumncolumnnametableheadingrow2023/1/10DatabasePrinciples&Programming122.2NamingthePartsofaDatabaseDefinitiondatabaseasetofnamedtables,orrelations.CAP={CUSTOMERS,AGENTS, PRODUCTS,ORDERS}Headingoftable(schema)asetofnamedcolumnsHead(CUSTOMERS)= {cid,cname,city,discnt}2023/1/10DatabasePrinciples&Programming132.2NamingthePartsofaDatabaseNoteThenumberofrowschangesfrequentlyandrowsarenotrememberedbyusers;thecolumnsusuallyDON'Tchangeinnumber,manynamesareremembered,andUSEDTOPOSEQUERIES.Program-DataIndependence(数据独立性)whenaskedtomakeupaquerytoansweraquestion,querymuststillanswerthequestionevenifallthedatachanges.2023/1/10DatabasePrinciples&Programming142.2NamingthePartsofaDatabaseColumntype

(Domain,Datatype)Domain(city):asetofcitynamesAtableisDECLAREDinSQL.ColumnshavecertainTYPESasinSQL:realintegerchar(13)date…2023/1/10DatabasePrinciples&Programming152.2NamingthePartsofaDatabaseProblemsofColumntype(cont.)Mostcommercialdatabasesystemsdon’tsupporttypesconsistingofenumeratedsets(aswithcity,month).Integrity(完整性)Domain(cityinCUSTOMERS)vsDomain(cityinAGENTS)?particulartype2023/1/10DatabasePrinciples&Programming162.2NamingthePartsofaDatabaseRelationalAlgebra(关系代数)Domainofcolumnislikeanenumeratedtype.Domain(City)allthecitynamesintheU.S.Domain(Discnt)allfloatsbetween0.00and20.00.2023/1/10DatabasePrinciples&Programming172.2NamingthePartsofaDatabaseCartesianProduct(笛卡儿乘积)Set:CID=Domain(cid),CNAME=Domain(cname),CITY=Domain(city),DISCNT=Domain(discnt),thenconsider:

CIDxCNAMExCITYxDISCNTconsistingofalltuples:(w,x,y,z),winCID,xinCNAME,yinCITY,zinDISCNTArelationbetweenthesefourdomainsisasubsetoftheCartesianproduct.CUSTOMERS

CIDxCNAMExCITYxDISCNT2023/1/10DatabasePrinciples&Programming182.2NamingthePartsofaDatabase设有一个由学号sno、姓名name和系别dept等三个属性所构成的学生关系S：snonamedeptS1张山数学2李英数学3王华中文在该关系中加入上述的三个学生元组，在关系代数中，上述的学生关系可以被表示为下面的集合：S={(1,张三,数学)，(2,李英,数学)，(3,王华,中文)}2023/1/10DatabasePrinciples&Programming192.2NamingthePartsofaDatabase假设只考虑上述的三个学生元组，那么：Domain(sno)={1,2,3}Domain(name)={张三,李英,王华}Domain(dept)={数学,中文}snonamedeptS1张山数学2李英数学3王华中文将上述的三个值域进行笛卡尔乘积运算可得到如下的关系表W：S将上述的三个值域进行笛卡尔乘积运算可得到如下的关系表W：2023/1/10DatabasePrinciples&Programming201张山数学1李英数学1王华数学2张山数学2李英数学2王华数学3张山数学3李英数学3王华数学1张山中文1李英中文1王华中文2张山中文2李英中文2王华中文3张山中文3李英中文3王华中文W原关系S显然只是笛卡尔乘积结果关系W的一个真子集。2023/1/10DatabasePrinciples&Programming212.3RelationalRulesRule1.FirstNormalFormRuleCan'thavemulti-valuedfields.Thuscan'thaveemployeestablewithcolumn"dependents"whichcontainsmultipledependent'snames(pg.33,

Figure2.3)Couldcreateonetablewithduplicatesondifferentrowsbutthisisbadforotherreasons.(pg.33,

Figure2.4)e.g.,employees-dependentstablejoinofemployeesanddependents2023/1/10DatabasePrinciples&Programming222.3RelationalRulesRule1.FirstNormalFormRule(cont.)Endsupmeaningwehavetocreatetwotablesandjoin

theminlaterqueries.(pg.34,

Figure2.5)inORmodel,Figure2.3

isOK,butwon'thandlethisforawhilesoasnottoconfuseyou;assumerelational--nomulti-valuedfields.2023/1/10DatabasePrinciples&Programming232.3RelationalRulesRule2.AccessRowsbyContentOnlyRuleCan'tsay:thethirdrowdownfromthetop.NoordertotherowsNoordertothecolumnsDisallows"pointers"torowse.g.RowIDs(RIDs)or"refs".MostrelationalproductsbreakthisrulebyallowinguserstogetatrowsbyRIDs;newobject-relationalproductshaverefsaspartofsyntax.2023/1/10DatabasePrinciples&Programming242.3RelationalRulesRule3.TheUniqueRowRuleTworowscan'tbesameinallattributesatonce.SothatarelationisanunorderedSEToftuples.Butmanyproductsallowthisforefficiencyofload.Thereareevensometableswhereitisagoodthing(temperaturereadingsintable,mightrepeat).ButinthecurrentChapter,Chapter2,wewillassumethatalltheserulesholdperfectly.2023/1/10DatabasePrinciples&Programming252.4Keys,Superkeys,andNullValuesIdeaofKeyssomesetofcolumnsinatabledistinguishesrows.[E.g]cidinCUSTOMERS ordnoinORDERSdefinedbyDBAwhichsetofcolumnshasthispropertyItisUSEFULtohavesuchakeyforatableothertablescanrefertoarowinthistablee.g.cidinCUSTOMERS2023/1/10DatabasePrinciples&Programming262.4Keys,Superkeys,andNullValuesKey&SuperkeySuperkeyisasetofcolumnsthathastheuniquenessproperty[e.g.]AsuperkeyforCUSTOMERS：(cid,cname)keyisaminimalsuperkey:nosubsetofcolumnsalsohasuniquenessproperty.[e.g.]AkeyforCUSTOMERS：cid2023/1/10DatabasePrinciples&Programming272.4Keys,Superkeys,andNullValuesDef.2.4.1TableKeyGivenatableT,withHead(T)={A1,A2,…,An}.AkeyforthetableT,sometimescalledacandidatekey(候选关键字),isasetofattributes,K={Ai1,…,Aik},withtwoproperties:Ifu,varedistincttuplesofT,thenbydesignerintentionu[K]v[K];thatis,therewillalwaysexistatleastonecolumn,Aim,inthesetofcolumnsKsuchthatu[Aim]v[Aim]NopropersubsetHofKhasproperty1Superkey:asetofcolumnsthatfulfillsproperty1butnotnecessarilyproperty2.2023/1/10DatabasePrinciples&Programming282.4Keys,Superkeys,andNullValuesQuestion:pg.28,Figure2.2,PRODUCTSpidpnamecityquantitypriceP01combDallas1114000.50p02brushNewark2030000.50p03razorDuluth1506001.00p04penDuluth1253001.00p05pencilDallas2214001.00p06folderDallas1231002.00p07caseNewark1005001.00Is

pid

akeyforPRODUCTS?

pname?

city

?(pid,city)akeyforPRODUCTS?Issuperkey?2023/1/10DatabasePrinciples&Programming292.4Keys,Superkeys,andNullValuesExample2.4.1.ConsiderthetableT:ABCDa1b1c1d1a1b2c2d1a2b2c1d1a2b1c2d1Assumeintentofdesigneristhatthistablewillremainwiththesamecontents.Thencandeterminekeysfromcontentalone(thisisaVERYUNUSUALsituation).2023/1/10DatabasePrinciples&Programming302.4Keys,Superkeys,andNullValuesExample2.4.1.(cont.)ABCDa1b1c1d1a1b2c2d1a2b2c1d1a2b1c2d1Pairs:

AB,

AC,

BC.Eachofthesepairsdistinguishesallrows.Thereforeallarekeys.Allothersets(musthave3ormoreelements)containoneoftheseorelseDasapropersubset.(Showsubsetlattice).Thereforenomorekeys.Nosinglecolumncanbeakey.Nosetofcolumnscanbeakeyifitcontains

D.2023/1/10DatabasePrinciples&Programming312.4Keys,Superkeys,andNullValuesVariouskeysspecifiedbyintentofDBAarecalledCandidateKeys.APrimaryKey(主关键字)isdefinedtobethecandidatekeychosenbythedesignertoidentifiesrowsofTusedinreferencesbyothertables.Def.2.4.3PrimaryKeyofaTableAprimarykeyofatableTisthecandicatekeychosenbythedatabasedesignertouniquelyidentifyspecificrowsofT.AsORDERSreferencesCUSTOMERSbycid.Probablykeepthatwayofreferencingevenifcname/citywasanothercandidatekey.2023/1/10DatabasePrinciples&Programming322.4Keys,Superkeys,andNullValuesTheorem2.4.2.EverytableThasatleastonekey.Proof.GivenatableTwithHead(T)={A1...An}.SETUPTOLOOP:Letattributeset

S1

bethisentireset.LOOP:Now

S1

isasuperkeyforT;either

S1

isaKeyforTorithasapropersubset

S2,

S2

S1,suchthat

S2

isalsoasuperkeyforT.Now,either

S2

isaKeyorithasapropersubset

S3,S3

S2,suchthat

S3

isalsoasuperkeyforT.2023/1/10DatabasePrinciples&Programming332.4Keys,Superkeys,andNullValuesProof.(cont.)ButeachsuccessiveelementinthesequenceS1,S2,S3,...hasasmallernumberofcolumns(itisaPROPERsubset),andwecannevergoto0(0columnsdon'thaveuniquevalues)orbelow.ThusthismustbeafinitesequencewithasmallestsetattheendSn.Thatmustbethekey.QED.2023/1/10DatabasePrinciples&Programming342.4Keys,Superkeys,andNullValuesTheorem2.4.2.EverytableThasatleastonekey.Proof.GivenatableTwithHead(T)={A1...An}.令K:=Head(T),则K是表T的一个超键(superkey)While(K是表T的超键){IF(存在K的一个真子集S，且S也是T的超键)THEN{K:=S;continue;}ELSEbreak;}Returnkey‘K’forT;2023/1/10DatabasePrinciples&Programming352.4Keys,Superkeys,andNullValuesNullValues(空值)Anullvalueisplacedinafieldofatablewhenaspecificvalueiseitherunknownorinappropriate.[e.g.]Insertanewagent:

(a12,Beowulf,

unknown,unknown)Agenthasn'tbeenassignedapercentcommissionorcityyet(stillintraining,butwanttohavearecordofhim).2023/1/10DatabasePrinciples&Programming362.4Keys,Superkeys,andNullValuesNullValues(cont.)Anullvaluecanbeusedforeitheranumericorcharactertype.BUTITHASADIFFERENTVALUEFROMANYREALFIELD.Inparticular,itisnotzero(0)orthenullstring('').Itishandledspeciallybycommercialdatabases.2023/1/10DatabasePrinciples&Programming372.4Keys,Superkeys,andNullValuesQueryallagentswithpercent<6?Queryallagentswithpercent<6orpercent

6?Queryallagentswithcity‘NewYork’?aidanamecitypercenta01SmithNewYork6a02JonesNewark6a03BrownTokyo7a04GrayNewYork6a05OtasiDuluth5a06SmithDallas5a12BeowulfunknownunknownAGENTS2023/1/10DatabasePrinciples&Programming382.4Keys,Superkeys,andNullValuesRule3.EntityIntegrityRule

(实体完整性)NoColumnbelongingtoaprimarykeyofatableTisallowedtotakeonNULLvaluesforanyrowinT.2023/1/10DatabasePrinciples&Programming392.5RelationalAlgebraRelationalAlgebra（关系代数）abstractlanguageintroducedbyE.F.Coddinformationstoredintheformoftablesresultsofaqueryintableformoperationsofrelationalalgebra2023/1/10DatabasePrinciples&Programming402.5RelationalAlgebraFundamentalOperationsofRelationalAlgebratwotypesofoperationsset-theoreticoperationsdependonfactthattableisasetofrowsnativerelationaloperationsdependonstructureoftable2023/1/10DatabasePrinciples&Programming412.5RelationalAlgebraset-theoreticoperationsNAMESYMBOLFORMEXAMPLEUNION(并)∪UNIONR∪SINTERSECTION(交)∩INTERSECTR∩SDIFFERENCE(差)–MINUSR–SPRODUCT(乘积)TIMESRS2023/1/10DatabasePrinciples&Programming422.5RelationalAlgebranativerelationaloperationsNAMESYMBOLFORMEXAMPLEPROJECT(投影)R[]R[]R[Ai1,…,Aik]Ai1,…,Aik(R)SELECT(选择)RwhereCRwhereCRwhereA1=5C(R)A1=5(R)JOIN(联接)JOINRSDIVISION(除法)DIVIDEBYRS2023/1/10DatabasePrinciples&Programming432.6Set-TheoreticOperationsDef.2.6.1CompatibleTables(相容表)TablesRandSarecompatibleiftheyhavethesameheadingsthatis,ifHead(R)=Head(S),withattributeschosenfromthesamedomainsandwiththesamemeanings.2023/1/10DatabasePrinciples&Programming44CompatibleTable(example1)TablesR1andS1arecompatiblebecause:withthesamenumberofcolumnseachpairofcolumns(oneintableR1,andanotherintableS1)have:thesamedomains(character),andthesamemeanings,suchasthesamenameofcolumnsABCa1b1c1a1b2c3a2b1c2R1ABCa1b1c1a1b1c2a1b2c3a3b2c3S12023/1/10DatabasePrinciples&Programming45CompatibleTable(example2)TablesR2isn’tcompatiblewithtableS2because:ThedomainofcolumnAintableS2isinteger,andthedomainofcolumnAintableR2ischaracter,andthatWecan’tfindapairofcolumns(X,A)(XisacolumnintableR2,andAisacolumnintableS2)withthesamedomain(integer)ABCa1b1c1a1b2c3a2b1c2R2ABC1b1c11b1c21b2c33b2c3S22023/1/10DatabasePrinciples&Programming46CompatibleTable(example3)TablesR3isn’tcompatiblewithtableS3because:Wecan’tfindapairofcolumns(?,D)(?isacolumnintableR3,andDisacolumnintableS3)withthesamemeaningsInrelationalalgebra,twocolumnsaresaidtohavethesamemeaningsiftheyhavethesamenameofcolumn.ABCa1b1c1a1b2c3a2b1c2R3ABDa1b1c1a1b1c2a1b2c3a3b2c3S32023/1/10DatabasePrinciples&Programming47CompatibleTable(example4)TablesR4isn’tcompatiblewithtableS4because:ThenumberofcolumnsintableR4isn’tequaltothenumberofcolumnsintableS4ABCa1b1c1a1b2c3a2b1c2R4ABCDa1b1c1d1a1b1c2d1a1b2c3d1a3b2c3d1S42023/1/10DatabasePrinciples&Programming48CompatibleTable(example5)TablesR5andS5arecompatiblebecause:withthesameheadingsoftableWecanmovethecolumnBtotheleftofthecolumnCintableS5becauseNoordertothecolumns.ABCa1b1c1a1b2c3a2b1c2R5ACBa1c1b1a1c2b1a1c3b2a3c3b2S52023/1/10DatabasePrinciples&Programming49CompatibleTable(example5)c2b1a2c3b2a1c1b1a1CBAR5ACBa1c1b1a1c2b1a1c3b2a3c3b2S5b2b2b1b1Bc3c3c2c1Ca3a1a1a1AS52023/1/10DatabasePrinciples&Programming502.6Set-TheoreticOperationsDef2.6.2Union,Intersection,DifferenceLetRandSbetwocompatibletables,whereHead(R)=Head(S)RSisatablewiththesameheadingasR(orS)foreachrowtinRorinS,tinRSRSisatablewiththesameheadingasR(orS)foreachrowtinR,iftappearinS,thentinRSR–SisatablewiththesameheadingasR(orS)foreachrowtinR,iftdon’tappearinS,thentinR–S2023/1/10DatabasePrinciples&Programming512.6Set-TheoreticOperationsExample2.6.2ABAB

A–B

BAB

2023/1/10DatabasePrinciples&Programming522.6Set-TheoreticOperationsTheUNIONandINTERSECTIONoperationsarecommutative(交换律)andassociative(结合律).RS=SRRS=SRbecausenoordertotherowsintable(RS)

T=R(ST)(RS)

T=R(ST)2023/1/10DatabasePrinciples&Programming532.6Set-TheoreticOperationsTheDIFFERENCEoperationisnotcommutative.R–SS–RR–S=R–(RS)S–R=S–(RS)RS=R–(R–S)=S–(S–R)(R–S)S=(R–S)R=R–S(R–S)(S–R)=2023/1/10DatabasePrinciples&Programming54Example2.6.1ABCa1b1c1a1b2c3a2b1c2RABCa1b1c1a1b1c2a1b2c3a3b2c3SABCa1b1c1a1b2c3a2b1c2a1b1c2a3b2c3RSABCa1b1c1a1b2c3RSABCa2b1c2R–SABCa1b1c2a3b2c3S–RTherowswithbluebackgroundappearonlyintableR,andtherowswithyellowbackgroundappearonlyintableS.2023/1/10DatabasePrinciples&Programming55AnotherexampleFNLNSusanYaoAmyFordJimmyWangRameshShahJohnFordSTUDENT(S)FNLNRameshShahFrancisJohnsonSusanYaoJohnSmithINSTRUCTOR(I)WangJimmyShahRameshFordJohnFordAmyJohnsonFrancisSmithJohnYaoSusanLNFNSIShahRameshYaoSusanLNFNSIWangJimmyFordJohnFordAmyLNFNS–IJohnsonFrancisSmithJohnLNFNI–S2023/1/10DatabasePrinciples&Programming562.6Set-TheoreticOperationsDef2.6.3Assignment,AliasLetRbeatableandletHead(R)={A1,A2,…,An}.AssumethatB1,B2,…,BnarenattributessuchthatDomain(Bi)=Domain(Ai)foralli(1in).WedefineanewtableS,whoseheadingisHead(S)={B1,B2,…,Bn},bywritingtheassignmentS(B1,B2,…,Bn):=R(A1,A2,…,An)ThecontentofthenewtableSisexactlythesameastheoldtableR.foreachrowtintableR,tintableS,andforeachrowtintableS,tintableR2023/1/10DatabasePrinciples&Programming572.6Set-TheoreticOperationsThesymbol:=iscalledtheassignmentoperator.WerefertoSasanaliasofthetableRandsimplywriteS:=RWecan‘save’intermediateresultsofevaluationbydefininganewtablewithassignmentoperator.Example2.6.3T:=(RS)–(RS)WecandefinethetableTbywritingT1:=(RS)T2:=(RS)T:=T1–T22023/1/10DatabasePrinciples&Programming58Example2.6.3ABCa1b1c1a1b2c3a2b1c2RABCa1b1c1a1b1c2a1b2c3a3b2c3SABCa1b1c1a1b2c3a2b1c2a1b1c2a3b2c3T1:=RSABCa1b1c1a1b2c3T2:=RSABCa2b1c2a1b1c2a3b2c3T:=T1

–T2RSThepictureofT2023/1/10DatabasePrinciples&Programming592.6Set-TheoreticOperationsTheProductOperationbasedontheset-theoreticoperationoftheCartesianproduct.Def2.6.4ProductRSTheproductofthetablesRandSisatableTthatifHead(R)={A1,…,An},andHead(S)={B1,…,Bm},thenHead(T)={R.A1,…,R.An,S.B1,…,S.Bm}tisarowinTifandonlyifTherearetworowsuinRandvinSsuchthatt(R.Ai)=u(Ai)for1in,andt(S.Bi)=v(Bi)for1im2023/1/10DatabasePrinciples&Programming60Example2.6.4(pgs46,47)ABCa1b1c1a1b2c3a2b1c2RBCDb1c1d1b1c1d3b2c2d2b1c2d4SR.AR.BR.CS.BS.CS.Da1b1c1b1c1d1a1b1c1b1c1d3a1b1c1b2c2d2a1b1c1b1c2d4a1b2c3b1c1d1a1b2c3b1c1d3a1b2c3b2c2d2a1b2c3b1c2d4a2b1c2b1c1d1a2b1c2b1c1d3a2b1c2b2c2d2a2b1c2b1c2d4RS2023/1/10DatabasePrinciples&Programming612.6Set-TheoreticOperationsIfthenumberofcolumnsintableRisCR,thenumberofcolumnsintableSisCS,thenthenumberofcolumnsinproductoftablesRandSis(CR+CS)IfthenumberofrowsintableRisNR,thenumberofrowsintableSisNS,thenthenumberofrowsinproductoftablesRandSis(NRNS)2023/1/10DatabasePrinciples&Programming622.7NativeRelationalOperationsTheNativeRelationalOperationsofrelationalalgebraareprojection()selectcolumnsintableselection()selectrowsintablejoin()combineatablewithanothertableoritselfdivision()acomplexoperationofrelationalalgebra2023/1/10DatabasePrinciples&Programming632.7NativeRelationalOperationsDef2.7.1ProjectionAssumeHead(R)={A1,A2,…,An},theprojectionofRonattributesAi1,…,Aik,where{Ai1,…,Aik}{A1,A2,…,An},isatableT(isdenotedbyR[Ai1,…,Aik]orAi1,…,Aik(R)

)thatHead(T)={Ai1,…,Aik

}foreveryrowrinthetableRtherewillbeasinglerowtinthetableTsuchthatr[Aij]=t[Aij]for1jkcastoutduplicaterowsintheresultofprojection!2023/1/10DatabasePrinciples&Programming64Example2.7.1listallcustomernamesfromtheCUSTOMERS(isdenotedbyC)tableinfigure2.2cidcnamecitydiscntc001TipTopDuluth10.00c002BasicsDallas12.00c003AlliedDallas8.00c004ACMEDuluth8.00c006ACMEKyoto0.00CACMEACMEAlliedBasicsTipTopcnameCNCN:=C[cname]orCN:=cname(C)2023/1/10DatabasePrinciples&Programming65Example2.7.1(cont.)castoutduplicaterows‘Duluth’and‘Dallas’intheresultoftheprojectionofConcolumn‘city’.cidcnamecitydiscntc001TipTopDuluth10.00c002BasicsDallas12.00c003AlliedDallas8.00c004ACMEDuluth8.00c006ACMEKyoto0.00CcityDuluthDallasKyotocity(C)2023/1/10DatabasePrinciples&Programming66Example2.7.1(cont.)ABCa1b1c1a1b1c3a2b2c2a1b2c4SAa1a2S[A]BAb1a1b2a2b2a1S[B,A]BCb1c1b1c3b2c2b2c4S[B,C]castoutduplicaterows(a1)castoutduplicaterows(b1,a1)2023/1/10DatabasePrinciples&Programming672.7NativeRelationalOperationsDef2.7.2SelectionAssumeHead(S)={A1,A2,…,An},theselectionoperationonScreatesanewtable,denotedbySwhereCorC(S)withthesamesetofattributes,andconsistingofthoserowsofSthatobeytheselectionconditionC.2023/1/10DatabasePrinciples&Programming682.7NativeRelationalOperationsDef.TheselectionconditionCCcanbeanycomparisonoftheformAi

Aj

or Ai

a

whereAiandAjareattributesofShavingthesamedomain,aisaconstantfromDomain(Ai),andisoneofthecomparisonoperators<,>,=,<=,>=,<>IfCandC’areconditions,thennewconditionscanbewritingCANDC’

, CORC’

, NOTC2023/1/10DatabasePrinciples&Programming69Example2.7.2Findallcustomersbasedin‘Kyoto’cidcnamecitydiscntc001TipTopDuluth10.00c002BasicsDallas12.00c003AlliedDallas8.00c004ACMEDuluth8.00c006ACMEKyoto0.00CUSTOMERS0.00KyotoACMEc006discntcitycnamecidTT:=CUSTOMERSwherecity=‘Kyoto’2023/1/10DatabasePrinciples&Programming70Example2.7.2(cont.)Findtheproductsstoredin‘Dallas’thatcostmorethan$0.50pidpnamecityquantitypriceP01combDallas1114000.50p02brushNewark2030000.50p03razorDuluth1506001.00p04penDuluth1253001.00p05pencilDallas2214001.00p06folderDallas1231002.00p07caseNewark1005001.00T:=PRODUCTSwherecity=‘Dallas’ANDprice>0.502023/1/10DatabasePrinciples&Programming71Example2.7.3Example2.7.3:Retrieveallpairsofagents,bothwithapercentagecommissionofatleast6%,andbothstationedinthesamecity.6NewYorkGraya047TokyoBrowna036NewarkJonesa026NewYorkSmitha01percentcityanameaidLStep1:Allagentswhohaveapercentagecommissionofatleast6%L:=AGENTSwherepercent>=6M:=AGENTSwherepercent>=6MisthesametablewithL2023/1/10DatabasePrinciples&Programming72Example2.7.3Step2:Retrieveallpairsofagents,bothwithapercentagecommissionofatleast6%,andbothstationedinthesamecity.6NewYorkGraya047TokyoBrowna036NewarkJonesa026NewYorkSmitha01percentcityanameaidL6NewYorkGraya047TokyoBrowna036NewarkJonesa026NewYorkSmitha01percentcityanameaidMPAIRS:=(LM)whereL.city=M.city2023/1/10DatabasePrinciples&Programming73GraySmithBrownJonesGraySmithM.anameNewYorkNewYorkTokyoNewarkNewYorkNewYorkM.citya04a01a03a02a04a01M.aid667666M.percent6NewYorkGraya046NewYorkSmitha016NewYorkGraya047TokyoBrowna036NewarkJonesa026NewYorkSmitha01L.percentL.cityL.anameL.aidPAIRS:=(LM)whereL.city=M.city6NewYorkGraya047TokyoBrowna036NewarkJonesa026NewYorkSmitha01percentcityanameaidL6NewYorkGraya047TokyoBrowna036NewarkJonesa026NewYorkSmitha01percentcityanameaidM2023/1/10DatabasePrinciples&Programming74L.aidL.anameL.cityL.percentM.aidM.anameM.cityM.percenta01SmithNewYork6a01SmithNewYork6a01SmithNewYork6a04GrayNewYork6a02JonesNewark