上海交通大学：《数据库系统原理 The principle of Database System》课程教学资源（课件讲稿）Chapter5 Algebraic and Logic Query languages

I Relational Database Modeling-how to define Relational Model data structure,operations,constraints Design theory for relational database High-level Models E/R model,UML model,ODL

I Relational Database Modeling – how to define Relational Model • data structure, operations, constraints • Design theory for relational database High-level Models • E/R model, UML model, ODL

II Relational Database Programming how to operate From an abstract point of view to study the question of database queries and modifications.(chapter 5) ■ Relational Algebra ■A Logic for relation From a practical point to learn the operations on Database The Database Language SQL (chapter 6~10)

II Relational Database Programming – how to operate From an abstract point of view to study the question of database queries and modifications. (chapter 5) „ Relational Algebra „ A Logic for relation From a practical point to learn the operations on Database „ The Database Language SQL (chapter 6~10)

Chapter 5 Algebraic and Logic Query languages Relational operations (chapter 2) Extended operators Datalog:a logic for relations Relational algebra and Datalog

Chapter 5 Algebraic and Logic Query languages „ Relational operations (chapter 2) „ Extended operators „ Datalog: a logic for relations „ Relational algebra and Datalog

Review 1:what is Relational Algebra? An algebra whose operands are relations or variables that represent relations. Operators are designed to do the most common things that we need to do with relations in a database. The result is an algebra that can be used as a query language for relations

Review 1: what is Relational Algebra? „ An algebra whose operands are relations or variables that represent relations. „ Operators are designed to do the most common things that we need to do with relations in a database. „ The result is an algebra that can be used as a query language for relations

Review 2: of Relational Algebra Set operations:Union,intersection and difference (the relation schemas must be the same) ■ Selection:Picking certain rows from a a relation. Projection:picking certain columns. Products and joins:composing relations in a useful ways. ■ Renaming of relations and their attributes

Review 2: “Core” of Relational Algebra „ Set operations: Union, intersection and difference (the relation schemas must be the same) „ Selection: Picking certain rows from a relation. „ Projection: picking certain columns. „ Products and joins: composing relations in a useful ways. „ Renaming of relations and their attributes

Review 3:Bags Model SQL,the most important query language for relational databases is actually a bag language. SOL will eliminate duplicates,but usually only if you ask it to do so explicitly. Some operations,like projection, are much more efficient on bags than sets

Review 3: Bags Model „ SQL, the most important query language for relational databases is actually a bag language. „ SQL will eliminate duplicates, but usually only if you ask it to do so explicitly. „ Some operations, like projection, are much more efficient on bags than sets

Extended (Nonclassical") Relational Algebra Add features needed for SQL bags. 1.Duplicate-elimination operator 8 2. Extended projection. 3.Sorting operator t 4.Grouping-and-aggregation operator 5.Outerjoin operator o

Extended ( “Nonclassical ”) Relational Algebra Add features needed for SQL bags. 1. Duplicate-elimination operator δ 2. Extended projection. 3. Sorting operator τ 4. Grouping-and-aggregation operator γ 5. Outerjoin operator ∞°

Duplicate Elimination s(R)=relation with one copy of each tuple that appears one or more times in R. Example R A B 1 2 3 4 1 2 δ(R)= A B 1 2 3 4

Duplicate Elimination δ (R ) = relation with one copy of each tuple that appears one or more times in R. Example R = A B 1 2 3 4 1 2 δ (R ) = A B 1 2 3 4

Sorting (R)=list of tuples of R,ordered according to attributes on list L Note that result type is outside the normal types (set or bag)for relational algebra. Consequence,t cannot be followed by other relational operators. R= A B TB(R)=[(1,2),(5,2),(3,4)] 1 2 3 4 5 2

Sorting τL(R) = list of tuples of R, ordered according to attributes on list L Note that result type is outside the normal types (set or bag) for relational algebra. Consequence, τ cannot be followed by other relational operators. R = A B τ B(R ) = [(1,2), (5,2), (3,4)] 1 2 3 4 5 2

Extended Projection Allow the columns in the projection to be functions of one or more columns in the argument relation. Example: R-A B 元A+B,A,A(R)= :A+B A1 A2 1 2 3 1 1 3 4 7 3 3 .Arithmetic on attributes .Duplicate occurrences of the same attribute

Extended Projection Allow the columns in the projection to be functions of one or more columns in the argument relation. Example: R = A B π A+B,A,A (R)= A+B A1 A2 1 2 3 1 1 3 4 7 3 3 •Arithmetic on attributes •Duplicate occurrences of the same attribute