Relational Algebra 2 ExtendedRelational Algebra

1
Relational Algebra 2Extended-Relational Algebra

• Adam Nafke
• CS157A

2
Generalized Projection -Review

• Extends projection operation by allowing
  arithmetic functions.
• Standard projection ?studentName,
  grade(classList)
• Generalized projection - ?studentName,
  quizAverage testAvg(classList)
• Will return a list of names with the sum of the
  two values.

3
Generalized Projection - continued

• ?studentName, quizAverage testAvg(classList)
  will return a attribute without a
  name.
• To name the attribute we use AS to cast it to a
  new attribute for the relation ?st
  udentName, quizAverage testAvg as
  testScore(classList)

4
Aggreate Functions - Review

• Aggreate functions are functions on relations
  which return a single value. However, many
  values can be retrieved from specific groups
  within relations.
• e.g. G sum(salary)(professors) would return the
  total salary of all professors on the relation
  professors.

5
Aggreate functions -continued

• However, we may want to find the total salaries
  by department. The query department-nam
  e G sum(salary) (professors)
• would give us just that.

6
Aggreate functions -continued

• One way to look at the left-hand subscript in any
  aggreate function is as a for loop. For example
• department-name G sum(salary)(professors)
• Is just
• for each (department-name)
• sum all salaries

7
Aggreate functions -continued

• Combining aggreate functions with generalized
  projection we have
• department-name G sum(salary) as Total Salary,
  max(salary) as HighestPaidProfessor(professors)
• Would perform a for-each on the department list
  and list the sum of the salaries and the amount
  of the highest paid professor.

8
Aggreate functions - continued

• It is important to note that if you are trying to
  find a specific entry in a relation via a
  aggregate function, do not list a unique name on
  the left-hand subscript of G.
• professor-name G max(salary)(professors)
• Will return the same relation as you started with
  (provided no two professors are name
• the same). Find the specific name via a normal
  query.

9
Modifications to the Database

• Now I will discuss how to add, remove, or change
  information in the Database.
  
• We use the assignment operation ( lt-) to make
  modifications to the database.

10
Deletion

• Expressed by r lt- r - X (where r is a
  relation, and X is a query)
• Examples To remove all of professor
  Davis's records professor lt-
  professor Oprofessor_name Davis(professor)
• Any query which returns a tuple or set of tuples
  can be used.

11
Insertion

• To insert data into a relation, either a tuple,
  or a set of tuples must be defined.
• The format of expressing insertion is
• r lt- r U E (r is a relation and E
  is a expression).

12
Insertion - Example

• Let's assume there are two relations Vehicle and
  Owner.
• Vehicle has attributes make, license plate ,
  color and Owner maps license plates to owners
  license plate , name. We add a value to the
  relations as follows
• Vehicle lt- Vehicle U (Corvette, 12345, blue)
• Owner lt- Owner U (12345, John Smith)

13
Updating

• Updating is used to change a value in a tuple
  without changing all values in the tuple. The
  form is r lt- p F1, F2, ...., Fn (r)
  
• Where each Fi is an expression, involving only
  constants and the attributes of r, that gives the
  new value for the attribute.

14
Updating - Example

• Suppose we wanted to halve the tuition for all
  students in relation (student). We would update
  this relation as follows
• student lt- ? name, id, age, tuition .5
  (student)
• What if we wanted to do different updates for
  different tuples?

15
Updating -continued

• An update must cover all tuples in a given
  relation. So if updating only some tuples is
  desired, the following format must be used
• r lt- ?F1, F2, ... (OP(r)) U (r- OP(r))
• What this says, is that in a update you must
  union whatever you select with whatever is left
  in that relation.

16
Updating - example

• Lets say you wanted to double the tuition of all
  students above the age of 30.
• ?name, age, tution 2 (O age gt 30(students))
  selects all students over 30 and doubles the
  value of tution.
• ?name, age, tution (O age lt 30(students)) will
  select all students under 30.
• Students lt- ?name, age, tution 2 (O age gt
  30(students)) U ?name, age, tution (O age lt
  30(students)) Will update all values.