Relational Data Model

CHAPTER 2: RELATIONAL DATA MODEL

Learning Outcomes Identify the fundamental operators used to retrieve information from a relational database.  Define the traditional set of operators for relational tables.  Draw the Venn diagrams for traditional set operators.  Define union compatibility.  Use the union, intersect and difference set operators to two relational tables given. 

2

Relational Algebra 

Relational Algebra mean a data manipulation language (DML) that provides a set of operators for manipulating one or two files.

3

Relational Algebra Operators Union  Intersection  Difference  Selection/Restriction  Projection  Cross Product  Join 

4

Venn Diagram The Venn Diagram is made up of two or more overlapping circles. It is often used in mathematics to show relationships between sets. It will be used for traditional set operators: Union, Intersection, Difference

Union 

Union: Combines all rows from two tables

Input: two tables R and S  Notation: R ∪ S 

◦ R and S must have identical schema 

Output:

◦ Has the same schema as R and S ◦ Contains all rows in R and all rows in S, with duplicates eliminated R

S R∪S

Example : Union

Intersection 

Intersection : Produces the set of tuples that two relations share in common

Input: two tables R and S  Notation: R ∩ S 

◦ R and S must have identical schema 

Output:

◦ Has the same schema as R and S ◦ Contains all rows that are in both R and S R

S

R∩S

Example: Intersection

Set Difference Acts on two relations and produces the set of tuples from the first relation that do not exist in the second relation.  Input: two tables R and S  Notation: R − S ◦ R and S must have identical schema  Output: ◦ Has the same schema as R and S ◦ Contains all rows in R that are not found in S 

R

S

S-R

Example: Set Difference

Selection 



Operation retrieves tuples from a relation, limiting the results to only those that meet a specific criteria

R1 := σC (R2)

◦ C is a condition (as in “if” statements) that refers to attributes of R2. ◦ R1 is all those tuples of R2 that satisfy C.

12

Example: Selection Relation Sells: stall

juice

price

Joe’s Joe’s Sue’s Sue’s

orange apple orange apple

2.50 2.75 2.50 3.00

JoeMenu :=

σ

stall = “Joe’s”(Sells):

stall

juice

price

Joe’s Joe’s

orange apple

2.50 2.75 13

Projection 



Project : Is essentially a selection operation in which duplicate tuples are removed from the result.

R1 := πL (R2)

◦ L is a list of attributes from the schema of R2. ◦ Eliminate duplicate tuples, if any.

14

Example: Projection Relation Sells:

Prices :=

stall

juice

price

Joe’s Joe’s Sue’s Sue’s

orange apple orange apple

2.50 2.75 2.50 3.00

π

juice . price(Sells):

juice

price

orange apple apple

2.50 2.75 3.00 15

Cross Product 



Cross Product : Produce conbination of tuples from two relations. Every tuple of the first relation being matched with every tuple of the second relation.

R3 := R1 Χ R2

16

Example: R3 := R1 Χ R2 R1(

R2(

A,

B )

1 3

2 4

B,

C )

5 7 9

R3(

A,

R1.B,

R2.B,

C

1 1 1 3 3 3

2 2 2 4 4 4

5 7 9 5 7 9

6 8 10 6 8 10

)

6 8 10

17

JOIN

Slide 3- 18

OUTER JOIN • LEFT OUTER JOIN - keep data from the left-hand table • RIGHT OUTER JOIN - keep data from the right-hand table • FULL OUTER JOIN - keep data from both tables

Slide 3- 19

LEFT / RIGHT OUTER JOIN

Slide 3- 20

FULL OUTER JOIN

Slide 3- 21

Usage Example 1. List all staff with a salary greater than RM10,000. SELECT * FROM STAFF WHERE salary > 10000 Or σ salary > 10000 (STAFF) 2. Produce a list of all salaries for all staff , showing only the Sno, Fname, Lname and Salary details. PROJECTION sno,fname,lname,salary (STAFF) Or Π sno,fname,lname,salary (STAFF)