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