Techniques for Manipulating Relational Data

1
Techniques for Manipulating Relational Data

• By Herbert A. Evans

2
Definition Terms

• DDL (data definition language) - of a relational
  system is used to define the databases
  attributes, tables, relationships, and indexes.
• DML (data manipulation language) is used to
  extract, insert, and modify the information
  content of the database.

3
What is SQL?

• The DML that is of most interest to us is the SQL
  (Structured Query Language)
• SQL specifies the manipulation of relations by
  describing the results of queries, but does not
  give specific strategies for executing queries.

4
Requiring a Formal Model

• We need a formal model that is sufficiently
  powerful to allow optimization of queries.
• Relational algebra is that formal model!

5
What does relational algebra operators do?

• Reduce the number of tuples in a set by selecting
  those that satisfy some criteria (selection
  operators).
• Reduce the size of each tuple in a set by
  eliminating specific attributes (projection
  operators).
• Manipulate two similar sets of tuples by
  combining or comparing (set operators).
• Increase the size of each tuple by adding
  attributes (join and product operations).

6
Find all customers whose last name is Doe
7
Using selection operator to solve the previous
task

• Relational algebra expression would be
  ?lastNameDOE(Customer)
• The new relation results from a selection that
  has the same attributes as the input relation,
  but may have fewer rows.

8
Result of finding all customers whose last name
is Doe
9
Find all employees whose ssn is 376-77-0099 and
who were employed after march 1, 1998
10
Using selection operator to solve previous task

• Relational algebra expression would be
  ?ssn376-77-0099 and date gt 01-mar-1999(TimeCa
  rd)
• The new relation results from a selection that
  has the same attributes as the input relation,
  but may have fewer rows.

11
Result of finding all employees whose ssn is
376-77-0099 and who were employed after march 1,
1998
12
List the first and last names of all customers
13
Using Projection Operator to Solve Previous Task

• Relational algebra expression would be ?lastName,
  firstName(Customer)
• The equivalent SQL expression would be select
  lastName, firstName from Customer

14
Result of listing the first and last names of all
customers
15
What are Set Operators?

• When two relations have the same shape, that is,
  when the types of the attributes are the same, we
  can apply the usual set operators to the
  relations.
• This includes union, intersection, and difference.

16
Explanation of Set Operators

• The union of two relations is a relation that
  contains the set of each tuple that is in at
  least one of the input relations.
• The intersection of two relations is the set of
  all tuples that occur in both input relations.
• The difference between two relations is the set
  of all tuples that are in the first relation but
  not in the second.

17
Set Operator Examples
18
Product Operators

• The simplest product operator is the Cartesian
  Product.
• It produces a tuple of the new relation for each
  combination of one tuple from the left operand
  and one tuple from the right operand.

19
Employee x TimeCard
20
Partial Result of ?Employee.ssnTimeCard.ssn(Emplo
yee X TimeCard)
21
Join operators

• It is expressed as those rows in the product
  whose specified fields match.
• It puts together related objects from two
  relations.

22
Employee natural joinssn TimeCard
23
Result of natural joinssn TimeCard
24
Division Operator

• It is used to find objects that match every
  element of another set of objects.

25
Example of Division operator
26
References

• Riccardi, Greg. Principles of DATABASE SYSTEMS
  with Internet and Java Applications Addision
  Wesley, 2001.
• Dr. Lees relational algebra lecture