CMSC424: Database DesignReview: Relational AlgebraRelational Algebra Redundant OperatorsNatural JoinDivisionOuter JoinsSlide 7Slide 8Slide 9UpdateExtended Relational AlgebraGeneralized ProjectionSlide 13Aggregate Functions and OperationsAggregate Operation – ExampleSlide 16Slide 17Other Theoretical LanguagesReview: Query LanguagesSQL - IntroductionSQL: Basic StructureA Simple SELECT-FROM-WHERE QueryAnother SELECT-FROM-WHERE QueryThe SELECT ClauseThe FROM ClauseThe WHERE ClauseData Definition LanguageDDLCMSC424, Spring 2005CMSC424: Database DesignLecture 5CMSC424, Spring 2005Review: Relational AlgebraRelational Algebra Operators1. Select ()2. Project ()3. Set Union (U)4. Set Difference (-)5. Cartesian Product ()6. Rename ()These are called fundamental operationsCMSC424, Spring 2005Relational AlgebraRedundant Operators4. Update ( )2. Division ( )1. Natural Join ( )3. Outer Joins ( )CMSC424, Spring 2005Natural JoinIdea: match tuples on common attributesA B C D1223αααβ+--+10102010E B D‘a’‘a’‘b’‘c’ααββ10201010rsA B C D E12233αααββ+--++1010201010‘a’‘a’‘a’‘b’‘c’=Relation1Relation2Notation: CMSC424, Spring 2005DivisionQuery: Find customers who have accounts in all branches in Brooklynr1 all branches in Brooklynr2 associate customers with branches they have accounts inNow what ? Use the division operatorRelation1Relation2Notation: Idea: expresses “for all” queriesCMSC424, Spring 2005bname lno amtDowntownRedwoodPerryL-170L-230L-260300040001700loan =cname lnoJonesSmithHayesL-170L-230L-155borrower ==bname lno amt cnameDowntownRedwoodL-170L-23030004000JonesSmithJoin result loses… any record of Perry any record of HayesOuter JoinsMotivation: loan borrower =CMSC424, Spring 2005bname lno amtDowntownRedwoodPerryL-170L-230L-260300040001700loan =cname lnoJonesSmithHayesL-170L-230L-155borrower =bname lno amt cnameDowntownRedwoodPerryL-170L-230L-260300040001700JonesSmith┴• preserves all tuples in left relation1. Left Outer Join ( )┴ = NULLOuter Joinsloan borrower =CMSC424, Spring 2005bname lno amt cnameDowntownRedwood┴L-170L-230L-15530004000┴JonesSmithHayesbname lno amtDowntownRedwoodPerryL-170L-230L-260300040001700loan =cname lnoJonesSmithHayesL-170L-230L-155borrower =• preserves all tuples in right relation2. Right Outer Join ( )┴ = NULLOuter Joinsloan borrower =CMSC424, Spring 2005bname lno amtDowntownRedwoodPerryL-170L-230L-260300040001700loan =cname lnoJonesSmithHayesL-170L-230L-155borrower =• preserves all tuples in both relations3. Full Outer Join ( )┴ = NULLOuter Joinsbname lno amt cnameDowntownRedwoodPerry┴L-170L-230L-260L-155300040001700┴JonesSmith┴Hayesloan borrower =CMSC424, Spring 20051. Deletion: r r – s e.g., account account – σbname=Perry (account)(deletes all Perry accounts)2. Insertion: r r se.g., branch branch {(Waltham, Boston, 7M)}(inserts new branch with bname = Waltham, bcity = Boston, assets = 7M)3. Update: r πe1,…,en (r)e.g., account πbname,acct_no,bal*1.05 (account)(adds 5% interest to account balances)Update Identifier QueryNotation: Common Uses:CMSC424, Spring 2005Extended Relational Algebra1. Generalized projection2. AggregatesCMSC424, Spring 2005e1,…,en (Relation)e1,…,en can include arithmetic expressions – not just attributescname limit balanceJonesTurner5000300020002500credit =π cname, limit - balance (credit) = cname limit-balanceJonesTurner3000500Generalized ProjectionNotation: ExampleThen…CMSC424, Spring 2005e1,…,en (Relation)e1,…,en can include arithmetic expressions – not just attributescname limit balanceJonesTurner5000300020002500credit =π cname, limit - balance as limitbalance (credit) = cname limitbalanceJonesTurner3000500Generalized ProjectionNotation: ExampleThen…CMSC424, Spring 2005Aggregate Functions and OperationsAggregation function takes a collection of values and returns a single value as a result.avg: average valuemin: minimum valuemax: maximum valuesum: sum of valuescount: number of valuesCMSC424, Spring 2005Aggregate Operation – ExampleRelation r:A BC77310g sum(c) as sumC (r)sum-C27CMSC424, Spring 2005Aggregate Functions and OperationsGeneral form: G1, G2, …, Gn g F1( A1), F2( A2),…, Fn( An) (E)E is any relational-algebra expressionG1, G2 …, Gn is a list of attributes on which to group (can be empty)Each Fi is an aggregate functionEach Ai is an attribute nameCMSC424, Spring 2005Aggregate Operation – ExampleRelation account grouped by branch-name:branch-name g sum(balance) (account)branch-name account-number balancePerryridgePerryridgeBrightonBrightonRedwoodA-102A-201A-217A-215A-222400900750750700branch-name balancePerryridgeBrightonRedwood13001500700CMSC424, Spring 2005Other Theoretical LanguagesRelational CalculusNon-proceduralTuple relational calculusExamplesSafetyDomain relational calculusCMSC424, Spring 2005Review: Query LanguagesTheoretical Use Practical UseRelational AlgebraFormal semantics of practical QL’sLanguage ExpressivityInternal query representation for query optimizersTRCFoundation for SQLSQL-92- Standard for Relational DB Query LanguagesSQL-99- Standard for Object-Relational DB Query LanguagesOQL- Standard for Object-Oriented DB Query LanguagesXQuery- Standard for XML-based DB Query LanguagesCMSC424, Spring 2005SQL - IntroductionStandard DML/DDL for relational DB’s•DML = Data Manipulation Language (queries, updates)•DDL = Data Definition Language (create tables, indexes, …)•View definition•Security (Authorization)•Integrity constraints•Transactions•Early 70’s, IBM system R project (SEQUEL)•Later, become standard (Structured Query Language)Also includesHistoryCMSC424, Spring 2005SQL: Basic StructureSELECT A1, ….., AnFROM r1, ….., rmWHERE PEquivalent to: A1,A2,…,An (σP (r1 … rn ))CMSC424, Spring 2005A Simple SELECT-FROM-WHERE QuerySimilar toSELECT bnameFROM loanWHERE amt > 1000Can instead write :SELECT DISTINCT bnameFROM loanWHERE amt > 1000(removes duplicates from result)We will discuss bag algebra a bit laterbname ( amt > 1000 (loan) )bnameRedwoodPerryDowntownPerryWhy preserve duplicates?Duplicates are retained(i.e., result not a set)But not quiteCMSC424, Spring 2005Another
View Full Document
Unlocking...