Adam nafke cs157a
This presentation is the property of its rightful owner.
Sponsored Links
1 / 16

Relational Algebra 2 Extended-Relational Algebra PowerPoint PPT Presentation


  • 121 Views
  • Uploaded on
  • Presentation posted in: General

Adam Nafke CS157A. Relational Algebra 2 Extended-Relational Algebra. Generalized Projection -Review. Extends projection operation by allowing arithmetic functions. Standard projection – ΠstudentName, grade(classList)

Download Presentation

Relational Algebra 2 Extended-Relational Algebra

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Adam nafke cs157a

Adam Nafke

CS157A

Relational Algebra 2Extended-Relational Algebra


Generalized projection review

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.


Generalized projection continued

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:ΠstudentName, quizAverage + testAvg as testScore(classList)


Aggreate functions review

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”.


Aggreate functions continued

Aggreate functions -continued

  • However, we may want to find the total salaries by department. The querydepartment-name G sum(salary) (professors)

  • would give us just that.


Aggreate functions continued1

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}


Aggreate functions continued2

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.

Aggreate functions -continued


Aggreate functions continued3

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.

Aggreate functions - continued


Modifications to the database

Modifications to the Database

  • Now I will discuss how to add, remove, or change information in the Database.

  • We use the assignment operation ( <-) to make modifications to the database.


Deletion

Deletion

  • Expressed by r <- r - X (where r is a relation, and X is a query)

  • Examples:To remove all of professor Davis's records:professor <- professor – Oprofessor_name = “Davis”(professor)

  • Any query which returns a tuple or set of tuples can be used.


Insertion

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 <- r U E (r is a relation and E is a expression).


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 <- Vehicle U {(Corvette, 12345, blue)}

Owner <- Owner U {(12345, “John Smith”)}

Insertion - Example


Updating

Updating

  • Updating is used to change a value in a tuple without changing all values in the tuple. The form is:r <- π 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.


Updating example

Updating - Example

  • Suppose we wanted to halve the tuition for all students in relation (student). We would update this relation as follows:

  • student <- п name, id, age, tuition * .5 (student)

  • What if we wanted to do different updates for different tuples?


Updating continued

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 <- п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.


Updating example1

Updating - example

  • Lets say you wanted to double the tuition of all students above the age of 30.

  • Пname, age, tution * 2 (O age > 30(students)) selects all students over 30 and doubles the value of tution.

  • Пname, age, tution (O age < 30(students)) will select all students under 30.

  • Students <- Пname, age, tution * 2 (O age > 30(students)) U Пname, age, tution (O age < 30(students)) Will update all values.


  • Login