1 / 16

Relational Algebra 2 Extended-Relational Algebra

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

sterling
Télécharger la présentation

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Adam Nafke CS157A Relational Algebra 2Extended-Relational Algebra

  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: ΠstudentName, 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-name 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. 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

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

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

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

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

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

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

  14. 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?

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

More Related