《數據庫系統(tǒng)》英文教學課件

《數據庫系統(tǒng)》英文教學課件,數據庫系統(tǒng),數據庫,系統(tǒng),英文,教學,課件 Relational AlgebraGuifeng ZhengSchool of SoftwareSUN YAT-SEN UNIVERSITYcourtesy of Joe Hellerstein for some slides2Recap:You are herenFirst part of course is done:conceptual foundationsnYou now know:qE/R ModelqRelational ModelqRelational Algebra(a little)nYou now know how to:qCapture part of world as an E/R modelqConvert E/R models to relational modelsqConvert relational models to good(normal)formsnNext:qCreate,update,query tables with R.A/SQLqWrite SQL/DB-connected applications33-minute Normalization Review1.Q:Whats required for BCNF?2.Q:How do we fix a non-BCNF relation?3.Q:If AsBs violates BCNF,what do we do?4.Q:Can BCNF decomposition ever be lossy?5.Q:How do we combine two relations?6.Q:Can BCNF decomp.lose FDs?7.Q:Why would you ever use 3NF?Relational Query LanguagesnQuery languages:manipulation and retrieval of datanRelational model supports simple,powerful QLs:qStrong formal foundation based on logic.qAllows for much optimization.nQuery Languages!=programming languages!qQLs not expected to be“Turing complete”.qQLs not intended to be used for complex calculations.qQLs support easy,efficient access to large data sets.(Actually,I no longer believe this.But its the standard viewpoint)4Formal Relational Query LanguagesRelational Algebra:More operational,very useful for representing execution plans.Relational Calculus:Describe what you want,rather than how to compute it.(Non-procedural,declarative.)*Understanding Algebra&Calculus is key to understanding SQL,query processing!56What is relational algebra?nAn algebra for relationsn“High-school”algebra:an algebra for numbersnAlgebra=formalism for constructing expressionsqOperationsqOperands:Variables,Constants,expressionsnExpressions:qVars&constantsqOperators applied to expressionsqThey evaluate to valuesAlgebraVars/constsOperatorsEval toHigh-schoolNumbers+*-/etc.NumbersRelationalRelations(=sets of tupes)union,intersection,join,etc.Relations7Why do we care about relational algebra?1.The exprs are the form that questions about the data take（有關數據的問題采用的形式?。﹒The relations these exprs cash out to are the answers to our questions（其表示的關系正是我們的問題的答案）2.RA more succinct rep.(簡潔表示)of many SQL queries3.DBMS parse SQL into something like RA.nFirst proofs of concept for RDBMS/RA:qSystem R at IBMqIngress at Berkeleyn“Modern”implementation of RA:SQLqBoth state of the art,mid-70sPreliminariesnA query is applied to relation instancesnThe result of a query is also a relation instance.qSchemas of input relations for a query are fixedqSchema for the result of a query is also fixed.ndetermined by the query language constructsnPositional vs.named-field notation:qPositional notation easier for formal definitionsqNamed-field notation more readable.qBoth used in SQLnThough positional notation is discouraged8Relational Algebra:5 Basic OperationsnSelection (s s)qSelects a subset of rows(horizontal)nProjection (p p)qRetains only desired columns(vertical)nCross-product ()qAllows us to combine two relations.nSet-difference ()qTuples in r1,but not in r2.nUnion ()qTuples in r1 or in r2.pSince each operation returns a relation,operations can be composed!(Algebra is“closed”.)9R1S1S2BoatsExample Instances10Projection()nExample:nRetains only attributes that are in the“projection list”.nSchema of result:qthe fields in the projection listqwith the same names that they had in the input relation.nProjection operator has to eliminate duplicatesqNote:real systems typically dont do duplicate elimination qUnless the user explicitly asks for it.q(Why not?)11Projection()S2snameratingyuppy9lubber8guppy5rusty1012Selection()snameratingyuppy9rusty10nSelects rows that satisfy selection condition.nResult is a relation.Schema of result is same as that of the input relation.nDo we need to do duplicate elimination?sid sname rating age 28 yuppy 9 35.0 31 lubber 8 55.5 44 guppy 5 35.0 58 rusty 10 35.0 13Union and Set-DifferencenBoth of these operations take two input relations,which must be union-compatible:qSame number of fields.qCorresponding fields have the same type.nFor which,if any,is duplicate elimination required?14Union S1S215Set Difference S1S2S2 S116Guifeng Zheng,DBMS,SS/SYSU17Rename opnChanges the schema,not the instancenNotation:rB1,Bn(R)nr is spelled“rho”,pronounced“row”nExample:qEmployee(ssn,name)qrE2(social,name)(Employee)qOr just:rE(Employee)Cross-ProductnS1 R1:qEach row of S1 paired with each row of R1.nQ:How many rows in the result?nResult schema has one field per field of S1 and R1,qField names inherited if possible.qNaming conflict:S1 and R1 have a field with the same name.qCan use the renaming operator:Output relation nameRenaming list18Cross Product ExampleR1S1S1 x R1=19););Division OperationnNotation:r s nSuited to queries that include the phrase“for all.”nLet r and s be relations over schemas R and S respectively,whereR =(A1,Am,B1,Bn)S =(B1,Bn)The result of r s is a relation over the schema(R S)=(A1,Am)r s =t|(t R-S(r)(u s,tu r)School of Software,SYSUSlide 20School of Software,SYSUSlide 21rsr sThe result consists of attribute A only but not all of the 5 values.How to find out?u=1,2 Check if:u s(tu r)Division Operation-exampler s =t|(t R-S(r)(u s,tu r)B12Is,1 and,2 tuples in r?Is,1 and,2 tuples in r?Is,1 and,2 tuples in r?check and A12311134612BA t R-S(r)A School of Software,SYSUSlide 22Relations r,s:Another Division Exampler sABCAASchool of Software,SYSUSlide 23Relation r,s:rsqProperties of Division OperationLet q =r sThen q is the largest relation satisfying:q s rCompound Operator:IntersectionnOn top of 5 basic operators,several additional“Compound Operators”qThese add no computational power to the languageqUseful shorthandqCan be expressed solely with the basic operators.nIntersection takes two input relations,which must be union-compatible.nQ:How to express it using basic operators?R S=R (R S)24Intersection S1S225Compound Operator:JoinnInvolve cross product,selection,and(sometimes)projection.nMost common type of join:“natural join”qR S conceptually is:nCompute R SnSelect rows where attributes appearing in both relations have equal valuesnProject all unique attributes and one copy of each of the common ones.nNote:Usually done much more efficiently than this.26Natural Join ExampleR1S1S1 R1=27Guifeng Zheng,DBMS,SS/SYSU28Natural JoinnR SnR S=?nUnpaired tuples called danglingABXYXZYZZVBCZUVWZVGuifeng Zheng,DBMS,SS/SYSU29Natural JoinnGiven the schemas R(A,B,C,D),S(A,C,E),what is the schema of R S?nGiven R(A,B,C),S(D,E),what is R S?nGiven R(A,B),S(A,B),what is R S?Other Types of JoinsnCondition Join(or“theta-join”):nResult schema same as that of cross-product.nMay have fewer tuples than cross-product.nEqui-Join:Special case:condition c contains only conjunction of equalities.(sid)snameratingage(sid)bidday22dustin745.05810311/12/9631lubber855.55810311/12/9630ExamplesReservesSailorsBoats31Find names of sailors whove reserved boat#103nSolution 1:nSolution 2:32Find names of sailors whove reserved a red boatnInformation about boat color only available in Boats;so need an extra join:v A more efficient solution:*A query optimizer can find this given the first solution!33Find sailors whove reserved a red or a green boatnCan identify all red or green boats,then find sailors whove reserved one of these boats:34Find sailors whove reserved a red and a green boatnCut-and-paste previous slide?35Find sailors whove reserved a red and a green boatnPrevious approach wont work!Must identify sailors whove reserved red boats,sailors whove reserved green boats,then find the intersection(note that sid is a key for Sailors):36SummarynRelational Algebra:a small set of operators mapping relations to relationsqOperational,in the sense that you specify the explicit order of operationsqA closed set of operators!Can mix and match.nBasic ops include:,nImportant compound ops:,37Relational CalculusGuifeng ZhengSchool of SoftwareSUN YAT-SEN UNIVERSITYcourtesy of Joe Hellerstein for some slidesRelational CalculusnQuery has the form:T|p(T)qT is a tuple variable.qp(T)is a formula containing T.nAnswer =tuples T for which p(T)=true.2FormulaenAtomic formulae:T Relation T.a op T.bT.a op constant op is one of nA formula can be:q an atomic formulaq q q 3Free and Bound Variables自由與約束變量nQuantifiers量詞:and nUse of or binds X.qA variable that is not bound is free.nRecall our definition of a query:qT|p(T)Important restriction:T must be the only free variable in p(T).all other variables must be bound using a quantifier.4Simple QueriesnFind all sailors with rating above 7nFind names and ages of sailors with rating above 7.qNote:S is a variable of 2 fields(i.e.S is a projection of Sailors)S|S Sailors S.rating 7=RA:S|S1 Sailors(S1.rating 7 S.sname=S1.sname S.age=S1.age)=RA:5 Find sailors rated 7 whove reserved boat#103 S|SSailors S.rating 7 R(RReserves R.sid=S.sid R.bid=103)=RA:Joins6Joins(continued)This may look cumbersome,but its not so different from SQL!S|SSailors S.rating 7 R(RReserves R.sid=S.sid B(BBoats B.bid=R.bid B.color=red)Find sailors rated 7 whove reserved a red boat7Universal QuantificationFind sailors whove reserved all boats S|SSailors BBoats(RReserves (S.sid=R.sid B.bid=R.bid)RA:(hint:use)8A trickier example S|SSailors B Boats(B.color=red R(RReserves S.sid=R.sid B.bid=R.bid)Find sailors whove reserved all Red boats S|SSailors B Boats(B.color red R(RReserves S.sid=R.sid B.bid=R.bid)Alternatively9a b is the same as a baTFT FbTTTF10A Remark:Unsafe Queriesn syntactically correct calculus queries that have an infinite number of answers!Unsafe queries.qe.g.,qSolution?Dont do that!11Expressive PowernExpressive Power(Theorem due to Codd):qEvery query that can be expressed in relational algebra can be expressed as a safe query in relational calculus;the converse is also true.nRelational Completeness:Query language(e.g.,SQL)can express every query that is expressible in relational algebra/calculus.(actually,SQL is more powerful,as we will see)12SummarynFormal query languages simple and powerful.qRelational algebra is operationalnused as internal representation for query evaluation plans.qRelational calculus is“declarative”nquery=“what you want”,not“how to compute it”qSame expressive power-relational completeness.nSeveral ways of expressing a given queryqa query optimizer should choose the most efficient version.13Your turn nSchema:Movie(title,year,studioName)ActsIn(movieTitle,starName)Star(name,gender,birthdate,salary)nQueries to write in Relational Calculus:1.Find all movies by Paramount studio2.movies whose stars are all women3.movies starring Kevin Bacon4.Find stars who have been in a film w/Kevin Bacon5.Stars within six degrees of Kevin Bacon*6.Stars connected to K.Bacon via any number of films*Try two degrees for starters *Good luck with this one!14Answers 1.Find all movies by Paramount studioM|MMovie M.studioName=Paramount15Answers 2.Movies whose stars are all womenM|MMovie AActsIn(A.movieTitle=M.title)SStar(S.name=A.starName S.gender=F)16Answers 3.Movies starring Kevin BaconM|MMovie AActsIn(A.movieTitle=M.title A.starName=Bacon)17Answers 4.Stars who have been in a film w/Kevin BaconS|SStar AActsIn(A.starName=S.name A2ActsIn(A2.movieTitle=A.movieTitle A2.starName=Bacon)moviestarA2:S:nameBaconmoviestarA:18Answers 5.Stars within six degrees of Kevin BaconS|SStar AActsIn(A.starName=S.name A2ActsIn(A2.movieTitle=A.movieTitle A3ActsIn(A3.starName=A2.starName A4ActsIn(A4.movieTitle=A3.movieTitle A4.starName=Bacon)two19Two degrees:S:nameBaconmoviestarA2:moviestarA:moviestarA4:moviestarA3:20Answers 6.Stars connected to K.Bacon via any number of filmsSorry that was a trick questionNot expressible in relational calculus!What about in relational algebra?We will be able to answer this question shortly 21SQL:The Query LanguageGuifeng ZhengSchool of SoftwareSUN YAT-SEN UNIVERSITYThe important thing is not tostop questioning.Albert EinsteinReviewRelational Algebra(Operational Semantics操作語義)Given a query,how to mix and match the relational algebra operators to answer itUsed for query optimization用于查詢優(yōu)化Relational Calculus(Declarative Semantics說明性語義)Given a query,what do I want my answer set to include?Algebra and safe calculus are simple and powerful models for query languages for relational modelHave same expressive power有相同的表達力SQL can express every query that is expressible in relational algebra/calculus.(and more)Guifeng Zheng,DBMS,SS/SYSU3RA SQLnSQL RA Projection PnSQL RA Selection snSQL RA Join/crossqComma-separated listnSQL renaming RA rho rnMore ops laternKeep RA in the back of your mindSELECTWHEREFROMRelational Query LanguagesSQL QueryRel.Algebra Query 1Rel.Algebra Query 2Rel.Algebra Query n.Pick the cheapest one查詢分析Relational Query LanguagesnTwo sublanguages:qDDL Data Definition定義 LanguagenDefine and modify schema(at all 3 levels)qDML Data Manipulation操作 LanguagenQueries can be written intuitively.nDBMS is responsible for efficient evaluation.qThe key:precise semantics for relational queries.qOptimizer can re-order operationsnWont affect query answer.qChoices driven by“cost model”成本模型The SQL Query LanguagenThe most widely used relational query language.nStandardized(although most systems add their own“special sauce”-including PostgreSQL)nWe will study SQL92-a basic subsetExample Databasesidsnameratingage1Fred7222Jim2393Nancy827Sailorssidbidday11029/1221029/13Reservesbidbnamecolor101Ninared102Pintablue103Santa MariaredBoatsThe SQL DDLCREATE TABLE Sailors(sid INTEGER,sname CHAR(20),rating INTEGER,age REAL,PRIMARY KEY sid);CREATE TABLE Boats(bid INTEGER,bname CHAR(20),color CHAR(10)PRIMARY KEY bid);CREATE TABLE Reserves(sid INTEGER,bid INTEGER,day DATE,PRIMARY KEY(sid,bid,day),FOREIGN KEY sid REFERENCES Sailors,FOREIGN KEY bid REFERENCES Boats);sidsnameratingage1Fred7222Jim2393Nancy827bidbnamecolor101Ninared102Pintablue103Santa Mariaredsidbidday11029/1221029/13FOREIGN KEY 外鍵 The SQL DMLnFind all 18-year-old sailors:SELECT*FROM Sailors SWHERE S.age=18 To find just names and ratings,replace the first line:SELECT S.sname,S.ratingsidsnameratingage1Fred7222Jim2393Nancy827Sailors Querying Multiple RelationsSELECT S.snameFROM Sailors S,Reserves RWHERE S.sid=R.sid AND R.bid=102sidsnameratingage1Fred7222Jim2393Nancy827Sailorssidbidday11029/1221029/13ReservesCross ProductNatural JoinBasic SQL QuerySELECT DISTINCT target-listFROM relation-listWHERE qualificationrelation-list:List of relation names,possibly with a range-variable after each nametarget-list:List of expressions over attributes of tables in relation-listDISTINCT:optional.Answer should not contain duplicates.SQL default:duplicates are not eliminated!(Result a“multiset”)qualification:Comparisons combined using AND,OR and NOT.Comparisons are Attr op const or Attr1 op Attr2,where op is one of,etc.1.FROM:compute cross product of tables.2.WHERE:Check conditions,discard tuples that fail.3.SELECT:Delete unwanted fields.4.DISTINCT(optional):eliminate duplicate rows.Note:Probably the least efficient way to compute a query!qQuery optimizer will find more efficient ways to get the same answer.Query SemanticsSELECT DISTINCT target-listFROM relation-listWHERE qualificationGuifeng Zheng,DBMS,SS/SYSU13SQL Query SemanticsParallel assignment all tuplesDoesnt impose any orderAnswer=for all assignments x1 in R1,xn in Rn do if Conditions then Answer=Answer (a1,ak)return AnswerSELECT a1,a2,akFROM R1 AS x1,R2 AS x2,Rn AS xnWHERE ConditionsGuifeng Zheng,DBMS,SS/SYSU14SQL Query SemanticsNested loops:Answer=for x1 in R1 do for x2 in R2 do .for xn in Rn do if Conditions then Answer=Answer (a1,ak)return AnswerSELECT a1,a2,akFROM R1 AS x1,R2 AS x2,Rn AS xnWHERE ConditionsFind sailors whove reserved at least one boatnWould DISTINCT make a difference here?nWhat is the effect of replacing S.sid by S.sname in the SELECT clause?qWould DISTINCT make a diff to this variant of the query?S.sidSailors S,Reserves RS.sid=R.sidSELECTFROMWHEREAbout Range VariablesnNeeded when ambiguity could arise.qe.g.,same table used multiple times in FROM (“self-join”)SELECT x.sname,x.age,y.sname,y.ageFROM Sailors x,Sailors yWHERE x.age y.agesidsnameratingage1Fred7222Jim2393Nancy827Sailors xsidsnameratingage1Fred7222Jim2393Nancy827Sailors yArithmetic ExpressionsSELECT S.age,S.age-5 AS age1,2*S.age AS age2FROM Sailors SWHERE S.sname=dustinSELECT S1.sname AS name1,S2.sname AS name2FROM Sailors S1,Sailors S2WHERE 2*S1.rating=S2.rating-1String Comparisons _ stands for any one character and%stands for 0 or more arbitrary characters.SELECT S.snameFROM Sailors SWHERE S.sname LIKE B_%BFind sids of sailors whove reserved a red or a green boatSELECT R.sidFROM Boats B,Reserves RWHERE R.bid=B.bid AND (B.color=red OROR B.color=green)SELECT R.sidFROM Boats B,Reserves RWHERE R.bid=B.bid AND B.color=red UNIONUNION SELECT R.sidFROM Boats B,Reserves RWHERE R.bid=B.bid AND B.color=green.or:SELECT R.sidFROM Boats B,Reserves RWHERE R.bid=B.bid AND (B.color=red ANDAND B.color=green)Find sids of sailors whove reserved a red and a green boatFind sids of sailors whove reserved a red and a green boatSELECT S.sidFROM Sailors S,Boats B,Reserves RWHERE S.sid=R.sid AND R.bid=B.bid AND B.color=redINTERSECTINTERSECTSELECT S.sidFROM Sailors S,Boats B,Reserves RWHERE S.sid=R.sid AND R.bid=B.bid AND B.color=greenCould use a self-join:SELECT R1.sidFROM Boats B1,Reserves R1,Boats B2,Reserves R2WHERE R1.sid=R2.sidR1.sid=R2.sid AND R1.bid=B1.bid AND R2.bid=B2.bid AND(B1.color=red ANDAND B2.color=green)Find sids of sailors whove reserved a red and a green boatFind sids of sailors who have not reserved a boatSELECT S.sidFROM Sailors SEXCEPTEXCEPTSELECT S.sidFROM Sailors S,Reserves RWHERE S.sid=R.sid Nested Queries:INSELECT S.snameFROM Sailors SWHERE S.sid ININ (SELECT R.sid FROM Reserves R WHERE R.bid=103)Names of sailors whove reserved boat#103:SELECT S.snameFROM Sailors SWHERE S.sid NOT INNOT IN (SELECT R.sid FROM Reserves RWHERE R.bid=103)Names of sailors whove not reserved boat#103:Nested Queries:NOT INNested Queries with CorrelationnSubquery must be recomputed for each Sailors tuple.qThink of subquery as a function call that runs a querySELECT S.snameFROM Sailors SWHERE EXISTSEXISTS (SELECT *FROM Reserves R WHERE R.bid=103 AND S.sid=R.sid)Names of sailors whove reserved boat#103:More on Set-Comparison Operatorsnweve seen:IN,EXISTSncan also have:NOT IN,NOT EXISTSnother forms:op ANY,op ALLnFind sailors whose rating is greater than that of some sailor called Horatio:SELECT*FROM Sailors SWHERE S.rating ANY (SELECT S2.rating FROM Sailors S2 WHERE S2.sname=Horatio)Conceptual SQL EvaluationSELECT DISTINCT target-listFROM relation-listWHERE qualificationGROUP BY grouping-listHAVING group-qualificationSELECTRelation cross-product Apply selections(eliminate rows)Project away columns(just keep those used in SELECT,GBY,HAVING)WHEREFROMGROUP BYForm groups&aggregateHAVINGEliminate groupsDISTINCTEliminate duplicatesSorting the Results of a QuerynORDER BY column ASC|DESC,.nCan order by any column in SELECT list,including expressions or aggs:SELECT S.rating,S.sname,S.ageFROM Sailors S,Boats B,Reserves RWHERE S.sid=R.sid AND R.bid=B.bid AND B.color=redORDER BY S.rating,S.sname;SELECT S.sid,COUNT(*)AS redrescntFROM Sailors S,Boats B,Reserves RWHERE S.sid=R.sid AND R.bid=B.bid AND B.color=redGROUP BY S.sidORDER BY redrescnt DESC;Null ValuesnField values are sometimes unknown(e.g.,a rating has not been assigned)or inapplicable(e.g.,no spouses name).qSQL provides a special value null for such situations.nThe presence of null complicates many issues.E.g.:qSpecial operators needed to check if value is/is not null.qIs rating8 true or false when rating is equal to null?What about AND,OR and NOT connectives?qWe need a 3-valued logic (true,false and unknown).Joins Explicit join semantics needed unless it is an INNER join(INNER is default)SELECT(column_list)FROM table_name INNER|LEFT|RIGHT|FULL OUTER JOIN table_name ON qualification_listWHERE Inner JoinOnly rows that match the qualification are returned.SELECT s.sid,s.name,r.bidFROM Sailors s INNER JOIN Reserves rON s.sid=r.sidReturns only those sailors who have reserved boats.SELECT s.sid,s.name,r.bidFROM Sailors s INNER JOIN Reserves rON s.sid=r.sid Left Outer JoinReturns all matched rows,plus all unmatched rows from the table on the left of the join clause(use nulls in fields of non-matching tuples)SELECT s.sid,s.name,r.bidFROM Sailors s LEFT OUTER JOIN Reserves rON s.sid=r.sidSELECT s.sid,s.name,r.bidFROM Sailors s LEFT OUTER JOIN Reserves rON s.sid=r.sid Right Outer JoinRight Outer Join returns all matched rows,plus all unmatched rows from the table on the right of the join clauseSELECT r.sid,b.bid,b.nameFROM Reserves r RIGHT OUTER JOIN Boats bON r.bid=b.bidSELECT r.sid,b.bid,b.nameFROM Reserves r RIGHT OUTER JOIN Boats bON r.bid=b.bid Full Outer JoinFull Outer Join returns all(matched or unmatched)rows from the tables on both sides of the join clause SELECT r.sid,b.bid,b.nameFROM Reserves r FULL OUTER JOIN Boats bON r.bid=b.bidSELECT r.sid,b.bid,b.nameFROM Reserves r FULL OUTER JOIN Boats bON r.bid=b.bid Note:in this case it is the same as the ROJ!bid is a foreign key in reserves,so all reservations musthave a corresponding tuple in boats.Views:Defining External DB SchemasCREATE VIEW view_nameAS select_statementMakes development simplerOften used for securityNot“materialized”CREATE VIEW RedsAS SELECT B.bid,COUNT(*)AS scount FROM Boats B,Reserves R WHERE R.bid=B.bid AND B.color=red GROUP BY B.bidSELECT bname,scount FROM Reds R,Boats B WHERE R.bid=B.bidAND scount 10RedsCREATE VIEW RedsAS SELECT B.bid,COUNT(*)AS scount FROM Boats B,Reserves R WHERE R.bid=B.bid AND B.color=red GROUP BY B.bidViews Instead of Relations in Queries Discretionary Access Control GRANT privileges ON object TO users WITH GRANT OPTIONObject can be a Table or a ViewPrivileges can be:SelectInsertDeleteReferences(cols)allow to create a foreign key that references the specified column(s)AllCan later be REVOKEdUsers can be single users or groupsSee Chapter 17 for more details.Two more important topicsnConstraintsnSQL embedded in other languagesIntegrity Constraints(Review)nAn IC describes conditions that every legal instance of a relation must satisfy.qInserts/deletes/updates that violate ICs are disallowed.qCan ensure application semantics(e.g.,sid is a key),or prevent inconsistencies(e.g.,sname has to be a string,age must be=1 AND rating=10)CREATE TABLE Reserves(sname CHAR(10),bid INTEGER,day DATE,PRIMARY KEY (bid,day),CONSTRAINT noInterlakeResCHECK (Interlake(SELECT B.bnameFROM Boats BWHERE B.bid=bid)Slide 46DBMSuserAd hocqueryresultStored procedurequeryresultEmbedded SQLC/Java programresultSQLDatabase API:ODBC,JDBC,PHPresultWriting Applications with SQLnSQL is not a general purpose programming language.+Tailored for data retrieval and manipulation+Relatively easy to optimize and parallelize-Cant write entire apps in SQL aloneOptions:Make the query language“Turing complete”Avoids the“impedance mismatch”but,loses advantages of relational language simplicityAllow SQL to be embedded in regular programming languages.Q:What needs to be solved to make the latter approach work?Embedded SQLnDBMS vendors traditionally provided“host language bindings”qE.g.for C or COBOLqAllow SQL statements to be called from within a programqTypically you preprocess your programs qPreprocessor generates calls to a proprietary DB connectivity librarynGeneral patternqOne call to connect to the right database(login,etc.)qSQL statements can refer to host variables from the languagenTypically vendor-specificqWe wont look at any in detail,well look at standard stuffnProblemqSQL relations are(multi-)sets,no a priori bound on the number of records.No such data structure in C.qSQL supports a mechanism called a cursor to handle this.Slide 49Why is cursor needed?hostprogramDBMSEmbedded SQLresultcursorCursor bridges the gap between value-orientedhost program and set-oriented DBMSA program variablecan hold one valueat a timeSlide 50Example Embedded SQLFrom within a host language,find the names and account numbers of customers with more than the variable amount dollars in some account.nSpecify the query in SQL and declare a cursor for it EXEC SQLdeclare c cursor forselect customer-name,account-numberfrom depositor,accountwhere depositor.account-number=account.account-number and account.balance :amountEND-EXEC Database APIs:Alternative to embeddingnRather than modify compiler,add a library with database calls(API)qspecial objects/methodsqpasses SQL strings from language,presents result sets in a language-friendly wayqODBC a C/C+standard started on WindowsqJDBC a Java equivalentqMost scripting languages have similar thingsnE.g.For Perl there is DBI,“oraPerl”,other packagesnMostly DBMS-neutral qat least try to hide distinctions across different DBMSsArchitecturenA lookup service maps“data source names”(“DSNs”)to drivers qTypically handled by OSnBased on the DSN used,a“driver”is linked into the app at runtimenThe driver traps calls,translates them into DBMS-specific codenDatabase can be across a networknODBC is standard,so the same program can be used(in principle)to access multiple database systemsnData source may not even be an SQL database!Application ODBC driverData SourceODBC/JDBCnVarious vendors provide drivers qMS bundles a bunch into WindowsqVendors like DataDirect and OpenLink sell drivers for multiple OSesnDrivers for various data sourcesqRelational DBMSs(Oracle,DB2,SQL Server,etc.)q“Desktop”DBMSs(Access,Dbase,Paradox,FoxPro,etc.)qSpreadsheets(MS Excel,Lotus 1-2-3,etc.)qDelimited text files(.CSV,.TXT,etc.)nYou can use JDBC/ODBC clients over many data sourcesqE.g.MS Query comes with many versions of MS Office(msqry32.exe)nCan write your own Java or C+programs against xDBCJDBCnPart of Java,easy to usenJava comes with a JDBC-to-ODBC bridgeqSo JDBC code can talk to any ODBC data sourceqE.g.look in your Windows Control Panel or MacOS Utilities folder for JDBC/ODBC drivers!nJDBC tutorial onlineqhttp:/ 200656hello.phpnhttp:/pages.stern.nyu.edu/mjohnson/dbms/php/hello.phpnQ:What the difference between and n?Hello from PHPHere comes the PHP part:?php print Hello,World!n;?Thats it!M.P.Johnson,DBMS,Stern/NYU,Spring 200657PHP varsnNames always start with$qhttp:/pages.stern.nyu.edu/mjohnson/dbms/php/math.php?$num1=58;$num2=67;print First number .$num1.;print Second number .$num2.;$total=$num1+$num2;print The sum is .$total.;?M.P.Johnson,DBMS,Stern/NYU,Spring 200658Combining PHP and HTMLnhttp:/pages.stern.nyu.edu/mjohnson/dbms/php/combine.php?php for($z=0;$z Iteration number M.P.Johnson,DBMS,Stern/NYU,Spring 200659PHP&MySQL1.Open a connection and open our DB:2.Run query:$db=mysql_connect(“l(fā)ocalhost,user,pass);mysql_select_db(test,$db);$result=mysql_query($query,$db);M.P.Johnson,DBMS,Stern/NYU,Spring 200660PHP&MySQL3.Extract next row of data from the results:qWhat this means:myrow is an array that can then be accessedqOther options,see codenIn general,to scroll through results,do:$myrow=mysql_fetch_row($result)while($myrow=mysql_fetch_row($result)#print rows dataAPI SummaryAPIs are needed to interface DBMSs to programming languagesnEmbedded SQL uses“native drivers”and is usually faster but less standardnODBC(used to be Microsoft-specific)for C/C+nJDBC the standard for JavanScripting languages(PHP,Perl,JSP)are becoming the preferred technique for web-based systemsSummarynRelational model has well-defined query semanticsnSQL provides functionality close to basic relational model(some differences in duplicate handling,null values,set operators,)nTypically,many ways to write a queryqDBMS figures out a fast way to execute a query,regardless of how it is written.Guifeng Zheng,DBMS,SS/SYSU63ReviewnExamples from SELECT LFROM R1,RnWHERE CPL(sC(R1 x Rn)Guifeng Zheng,DBMS,SS/SYSU64Another complex examplenPeople(ssn,name,street,city,state,state)nQ:Who lives on Georges street?nA:First,generate pairs of(renamed)people:qrp1(People)x rp2(People)nThen pick out pairs with George:qsp1.name=George(rp1(People)x rp2(People)nAnd refine to rows with George and someone else:qsp1.name=George AND p1.namep2.name(rp1(People)x rp2(People)nFinally,project out the names:qPp2.name(sp1.name=George AND p1.namep2.name(rp1(People)x rp2(People)Guifeng Zheng,DBMS,SS/SYSU65Live examplesnQ:produce a list of employees and their bossesqWhat if no boss?Or no subordinate?nJoins on emp,emp man:qComma-basedqInnerqNaturalqCrossqOuter left,right,fullGuifeng Zheng,DBMS,SS/SYSU66More live examplesnInner joins require an ON clauseqLike a where clauseqArbitrary boolean expressionqIf always true(1=1),reduces to cross joinnNew compar op:BETWEENqa between 5 and 10 a=5 and a=10nQ:produce a list of employees with their salary gradesqemp,salgrade