Review of Relational Algebra
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
