# Review of Relational Algebra

Shared by:
Categories
-
Stats
views:
9
posted:
6/6/2010
language:
English
pages:
12
Document Sample

```							                CS 440:                                  Review of Relational Algebra
Database Management Systems
How to design a language to query a
relational database?
–   Relational algebra
Procedural, explain how results are obtained
–   Relational calculus
Declarative, no need to specify the steps

Review of Relational Algebra                             Review of Relational Algebra

Queries consist of a collection of operators             Basic operators
Every operator accepts one or two relation                –   SELECT (σ), PROJECT (π), RENAME (ρ)
instances as input and return a relation                  –   UNION ( U ), INTERSECTION ( I ) , MINUS (-)
instance as output                                        –   CROSS PRODUCT (×)
Combining these operators can form more                   –   JOIN (>< ), DIVISION (÷)
EQUIJOIN, NATURAL JOIN (*)
complex queries

Review of Relational Algebra                             Review of Relational Algebra

Extended                                                 Selection
Generalized π to include projection of functions
–
–   Aggregate functions and grouping F
σ GPA>3.5 ( Students)
–   Recursive closure operation
–   OUTER JOIN                                          Projection
−
LEFT OUTER JOIN ( − >< )
−
RIGHT OUTER JOIN ( >< − )                              π SID ,SName ,GPA ( Students )
FULL OUTER JOIN ( − ><− )
−   −
–   OUTER UNION
Review of Relational Algebra                    Review of Relational Algebra

Display the ID, Name, and GPA of students       Display the ID, Name, and GPA of students
with a GPA > 3.5                                with a GPA > 3.5
π SID ,SName ,GPA (σ GPA>3.5 Students)

Review of Relational Algebra                    Review of Relational Algebra

Union of two sets of relation instances         Union of two sets of relation instances
R∪S                                             R∪S

R                  S                            R                   S                R∪S
John     12345     Mike   12405                 John     12345     Mike    12405       Mike   12405
Jean     54321    Kathy   43109                 Jean     54321     Kathy   43109      Kathy   43109
John    12345
Jean    54321

Review of Relational Algebra                    Review of Relational Algebra

Union of two sets of relation instances         Union of two sets of relation instances
R∪S                                             R∪S

R                  S                            R                   S                R∪S
John     12345     Mike   12405                 John     12345     Mike    12405       Mike   12405
Jean     54321    Kathy   43109                 Jean     54321     Kathy   43109      Kathy   43109
Mike     12405                                  Mike     12405                        John    12345
Jean    54321
Review of Relational Algebra                          Review of Relational Algebra

Union of two sets of relation instances               Union of two sets of relation instances
R∪S                                                   R∪S

R                   S                                 R                     S                 R∪S
John     12345     Mike     TX                        John      12345      Mike     TX         Mike   12405
Jean     54321     Kathy    OR                        Jean      54321     Kathy     OR        Kathy   43109
John       TX
Jean       OR

Review of Relational Algebra                          Review of Relational Algebra

Union of two sets of relation instances               Union-compatibility
–   Same number of fields
–   Same domain for every corresponding field
R                   S               R∪S               R                     S                 R∪S
John     12345     Mike     TX       Mike     12405   John      12345      Mike     TX         Mike   12405
Jean     54321     Kathy    OR      Kathy     43109   Jean      54321     Kathy     OR        Kathy   43109
John      TX                                              John       TX
Jean      OR                                              Jean       OR

Review of Relational Algebra                          Relational Algebra

Intersection                                          Intersection
R∩S                                                   R∩S

R                   S                                 R                     S                 R∩S
John     12345     Mike    12405                      John      12345      Mike    12405       Mike   12405
Jean     54321     Kathy   43109                      Jean      54321     Kathy    43109
Mike     12405                                        Mike      12405
Relational Algebra                                                                                                    Relational Algebra

Intersection needs to respect union-                                                                                  Difference
compatibility, too.                                                                                                    R−S

R                                       S                                       R∩S                                   R                     S                 R−S
John          12345                   Mike             12405                      Mike             12405              John    12345        Mike    12405      John   12345
Jean          54321                  Kathy             43109                                                          Jean    54321        Kathy   43109      Jean   54321
Mike          12405                                                                                                   Mike    12405

Relational Algebra                                                                                                    Relational Algebra

Difference respects union-compatibility.                                                                              Cross Product R × S
R                S              R×S
R                                       S                                       R−S                                   John    12345         TX         John    12345     TX
Jean    54321         OR         Jean    54321     TX
John          12345                   Mike             12405                      John             12345
Jean          54321                  Kathy             43109                      Jean             54321              Mike    12405                    Mike    12405     TX
John    12345     OR
Mike          12405
Jean    54321     OR
Mike    12405     OR

Relational Algebra                                                                                                    Joins

Renaming:                                                                                                             Can be derived from cross-product and
–   Change relation name only:                                                                                        projection/selection
ρ New _ relation _ name R                                                                                 More frequent than cross-product in practice

–   Change attribute names only:
ρ ( New _ attribute _ 1 _ name, New _ attribute _ 2 _ name,..., New _ attribute _ N _ name ) R

–   Change both relation name and attribute names:
ρ New _ Re lation _ Name ( New _ attribute _1_ name, New _ attribute _ 2 _ name,..., New _ attribute _ N _ name ) R
Joins                                                            Joins

Conditional joins                                                 Equijoins
R >< cS = σ C ( R × S )                                           R >< cS = σ C ( R × S )
Example                                                           Example
S1 Sailors(sid:integer, sname:string,rating:integer,age:real)     S1 Sailors(sid:integer, sname:string,rating:integer,age:real)
S2 Boats(bid:integer, bname: string, color: string)               S2 Boats(bid:integer, bname: string, color: string)
R1 Reserves(sid:integer, bid: integer, day: date)                 R1 Reserves(sid:integer, bid: integer, day: date)

S1 >< S 1.sid = R1.sid R1                                         S1 >< S 1.sid = R1.sid R1

Joins                                                            Joins

Natural joins                                                     More examples
R*S                                                   S1 Sailors(sid:integer, sname:string,rating:integer,age:real)
S2 Boats(bid:integer, bname: string, color: string)
Example                                                           R1 Reserves(sid:integer, bid: integer, day: date)

S1 Sailors(sid:integer, sname:string,rating:integer,age:real)
S2 Boats(bid:integer, bname: string, color: string)               Find the names of sailors who have reserved
R1 Reserves(sid:integer, bid: integer, day: date)
boat 103
S1 * R1

Joins                                                            Joins

More examples                                                     More examples
S1 Sailors(sid:integer, sname:string,rating:integer,age:real)     S1 Sailors(sid:integer, sname:string,rating:integer,age:real)
S2 Boats(bid:integer, bname: string, color: string)               S2 Boats(bid:integer, bname: string, color: string)
R1 Reserves(sid:integer, bid: integer, day: date)                 R1 Reserves(sid:integer, bid: integer, day: date)

Find the names of sailors who have reserved                       Find the names of sailors who have reserved
boat 103                                                          boat 103
π sname ((σ bid =103 R1 ) * S1 )                                 π sname ((σ bid =103 ( R1 × S1 ))
Joins                                                                       Joins

More examples                                                               More examples
S1 Sailors(sid:integer, sname:string,rating:integer,age:real)               S1 Sailors(sid:integer, sname:string,rating:integer,age:real)
S2 Boats(bid:integer, bname: string, color: string)                         S2 Boats(bid:integer, bname: string, color: string)
R1 Reserves(sid:integer, bid: integer, day: date)                           R1 Reserves(sid:integer, bid: integer, day: date)

Find the names of sailors who have reserved                                 Find the names of sailors who have reserved
a red or green boat                                                         a red or green boat
ρTempboats ((σ color = 'red ' S 2 ) ∪ (σ color = ' green ' S 2 ))
π sname ((Tempboats * R1 ) * S1 )

Joins                                                                       Joins

More examples                                                               More examples
S1 Sailors(sid:integer, sname:string,rating:integer,age:real)               S1 Sailors(sid:integer, sname:string,rating:integer,age:real)
S2 Boats(bid:integer, bname: string, color: string)                         S2 Boats(bid:integer, bname: string, color: string)
R1 Reserves(sid:integer, bid: integer, day: date)                           R1 Reserves(sid:integer, bid: integer, day: date)

Find the names of sailors who have reserved
Find the names of sailors who have reserved                                 at least two boats
a red or green boat
ρTempboats (σ color = 'red '∨ color = ' green ' S 2 )
π sname ((Tempboats * R1 ) * S1 )

Joins                                                                       Division

More examples                                                               Find the sailors who have reserved all the
S1 Sailors(sid:integer, sname:string,rating:integer,age:real)               boats
S2 Boats(bid:integer, bname: string, color: string)
R1 Reserves(sid:integer, bid: integer, day: date)

=
sid           bid                   bid                sid
Find the names of sailors who have reserved
at least two boats
15
15
1
2
÷           1
2
15

ρ Re servations (π sid , sname,bid ( S1 × R1 ))                                        15             3                     3
ρ Re servationpairs ( sid 1,sname1,bid 1,sid 2,sname 2,bid 2) R1 × R1                  13             1
13             2
π sname1σ ( sid 1= sid 2)∧(bid 1≠bid 2) Re servationpairs
16             3
Division                                                                                                                   Query Tree

Find the sailors who have reserved all the                                                                            A graph consists of vertices and edges
boats                                                                                                                 A graph is connected is there is a path
between any pair of vertices that consists of
=
sid               bid                         bid                            sid
edges in the graph
15
15
1
2
÷              1
2
15
A tree is a connected graph for which
#V=#E+1
15                 3                           3
13                 1
ρTempsids (π sid ,bid R1 ) ÷ (π bid S 2 )
13                 2
16                 3                  π sname (Tempsids * S1 )

Query Tree                                                                                                                 Extended Relational Algebra Operators

A query tree is a tree structure such that                                                                            Generalized projection
–   Leaf nodes represent the input relations                                                                          –   Allow functions
–   Internal nodes represent the relational algebra                                                                         T1 Teams(TID, Name, Conference, GamesWon, GamesLost)
operations.
π TID ,GamesWon+GamesLostTeams

Extended Relational Algebra Operators                                                                                      Extended Relational Algebra Operators

Aggregate functions and grouping                                                                                      Transitive closures
–   Allow functions                                                                                                   –   Example:
T1 Teams(TID, Name, Conference, GamesWon, GamesLost)                                                              Find all the teams that were beat by OSU
Find all the teams that were beat by a team that was
beat by OSU
Find all the teams that were beat by a team that was
ρ C ( No _ Teams , Average _ Wins , Minimum _ Losses ) (Conference FCOUNT TID , AVGERAGE GamesWon, MINIMUM GamesLostTeams)           beat by a team that beat by OSU
….
Can’t do this in traditional relational algebra
Extended Relational Algebra Operators                                    Extended Relational Algebra Operators

OUTER JOINS                                                              OUTER JOINS
–   List all the employees and the information about                   –   List all the employees and the information about
the department that they manage.                                       the department that they manage.
–   If someone isn’t managing a department, still                      –   If someone isn’t managing a department, still
show this.                                                             show this.
π EName, DID ( EMPLOYEE >< SSN = MgrSSN DEPT )
EMPLOYEE                   DEPT                                          EMPLOYEE                 DEPT
SSN            EName            DID         MsgSSN                       SSN          EName            DID       MsgSSN
123456789         John             HR     987654321                      123456789       John            HR       987654321
987654321         Tracy          DEV      000000000                      987654321       Tracy          DEV       000000000

Extended Relational Algebra Operators                                    Extended Relational Algebra Operators

OUTER JOINS                                                              OUTER JOINS
–   List all the employees and the information about                   –   List all the employees and the information about
the department that they manage.                                       the department that they manage.
–   If someone isn’t managing a department, still                      –   If someone isn’t managing a department, still
show this.                                                             show this.
π EName, DID ( EMPLOYEE >< SSN = MgrSSN DEPT )                        π EName, DID ( EMPLOYEE − >< SSN = MgrSSN DEPT )
−

RESULT                                                                 RESULT

EName         DID                                                     EName          DID
Tracy         DEV                                                      John
Tracy        DEV

Extended Relational Algebra Operators                                    Mapping ER to Database Schema

OUTER UNION
R                        S                       R∪S
John       12345       Mike         TX       John     12345
Jean       54321       Kathy        OR       Jean     54321
Mike              TX
Kathy              OR
Mapping ER to Database schema                Mapping ER to Database Schema

Fundamental building blocks in ER-diagrams:
–   Entities
Strong
weak
–   Attributes
Simple
Composite
Multi-valued
–   Relationships
Cardinality
participation

Mapping ER to Database Schema                Mapping ER to Database Schema

Fundamental building blocks in relational    Map entities and relationships from ER to
databases:                                   tables in database schema
–   Tables                                   Map attributes from ER to attributes in
–   Attributes                               database schema
Primary keys
Secondary keys
Foreign keys
Non-key attributes
–   Data types
…

Mapping ER to Database Schema                Mapping of Regular Entities

Mapping of entities                          Create a table for each regular entity
–   Regular                                  Use only simple attribute or the simple
–   Weak
component of a composite attribute
Mapping of relationships
Determine a primary key
–   1:1
–   1:N                                      If multiple keys, determine secondary keys
–   M:N                                       –   Can be useful for indexing
Mapping of multivalued attributes
Mapping of N-ary Relationship Types
Example                                              Example

Mapping of Weak Entities                             Example

Create a table for each weak entity
Use only simple attribute or the simple
component of a composite attribute
Include a foreign key attribute to owner entity
Determine a primary key
–   Partial key plus foreign key to owner entity
If the owner is also a weak entity, then
append its owner’s primary key

Example                                              Mapping of M:N Relationship

Create a table
Include foreign key to the participating
entities
–   These foreign keys form the primary key of the
relationship
Example                                                       Example

Mapping of 1:N Relationship                                   Example

Method 1:
–   Treat as M:N (is this a good way?)
Method 2:
–   Make a foreign key for the entity on the N-side to
point to the primary key of the entity on the 1-side.

Example                                                       Mapping of 1:1 Relationship

Method 1:
–   Treat as M:N (is this a good way?)
Method 2:
–   Treat as 1:N    (is this a good way?)
Method 3:
–   Make a foreign key in one entity to the primary
key of another entity (is this a good way?)
–   Put all attributes of the relationship to the table
describing the entity with the foreign key
Example                                                     Example

Mapping of Multivalued Attribute                            Example

Create a table for each such attribute
–   Make a foreign key to the entity which this
attribute belongs
–   Combine the foreign key and the attribute itself as
the primary key of the table

Example                                                     Mapping of N-ary Attribute

Like binary relationship
–   Create a table
–   Make foreign key attributes to each of the
participating entity
–   Combine these foreign keys to form the primary
key

```
Related docs
Other docs by dux15396
A Complete Set of Relational Algebra