Social Algebra

1735 days ago, 570 views
PowerPoint PPT Presentation
. Administrivia. Midterm 1

Presentation Transcript

Slide 1

p Relational Algebra CS 186 Spring 2006, Lecture 8 R & G, Chapter 4 By alleviating the cerebrum of all pointless work, a great documentation sets it allowed to focus on more propelled issues, and, as a result, builds the mental force of the race . - Alfred North Whitehead (1861 - 1947)

Slide 2

Administrivia Midterm 1 – Tues 2/21, in class Covers all material up to and including Th 2/16 Closed book – can bring 1 8.5x11" sheet of notes No adding machines – no PDAs Midterm 2 – Th 3/21, in class Focuses on material after MT 1 Same principles as above. Homework 1 – 2/14 Homework 2 – Out after MT 1, Due 3/14

Slide 3

Relational Query Languages Query dialects : Allow control and recovery of information from a database. Social model backings basic, intense QLs: Strong formal establishment in light of rationale. Takes into consideration much improvement. Question Languages != programming dialects! QLs not anticipated that would be "Turing finished". QLs not planned to be utilized for complex counts. QLs bolster simple, productive access to extensive informational indexes.

Slide 4

Formal Relational Query Languages Two numerical Query Languages frame the reason for "genuine" dialects (e.g. SQL), and for usage: Relational Algebra : More operational , exceptionally helpful for speaking to execution arranges. Social Calculus : Lets clients depict what they need, as opposed to how to register it. ( Non-procedural, definitive .) Understanding Algebra & Calculus is vital to comprehension SQL, inquiry handling!

Slide 5

Preliminaries An inquiry is connected to connection occasions , and the aftereffect of a question is additionally a connection example. Outlines of info relations for an inquiry are settled (yet question will keep running over any legitimate example) The mapping for the consequence of a given inquiry is likewise settled . It is dictated by the meanings of the question dialect builds. Positional versus named-field documentation: Positional documentation simpler for formal definitions, named-field documentation more discernable. Both utilized as a part of SQL

Slide 6

Relational Algebra: 5 Basic Operations Selection ( ) Selects a subset of lines from connection (even). Projection ( ) Retains just needed sections from connection (vertical). Cross-item ( x ) Allows us to join two relations. Set-distinction (–) Tuples in r1, yet not in r2. Union (  ) Tuples in r1 and additionally in r2. Since every operation gives back a connection, operations can be created! (Polynomial math is "shut".)

Slide 7

R1 Example Instances S1 S2 Boats

Slide 8

Projection Examples: ; Retains just traits that are in the " projection list" . Pattern of result: precisely the fields in the projection list, with similar names that they had in the information connection. Projection administrator needs to kill copies (How would they emerge? Why evacuate them?) Note: genuine frameworks normally don't do copy disposal unless the client unequivocally requests it. (Why not?)

Slide 9

Projection S2

Slide 10

Selection (  ) Selects columns that fulfill determination condition . Result is a connection. Outline of result is same as that of the info connection. Do we have to do copy disposal?

Slide 11

Union and Set-Difference All of these operations take two information relations, which must be union-perfect : Same number of fields. `Corresponding' fields have a similar sort. For which, assuming any, is copy end required?

Slide 12

Union S1 S2

Slide 13

Set Difference S1 S2 – S1 S2

Slide 14

Cross-Product S1 x R1: Each column of S1 combined with each line of R1. Q: what number lines in the outcome? Result composition has one field for every field of S1 and R1, with field names `inherited' if conceivable. May have a naming clash : Both S1 and R1 have a field with a similar name. For this situation, can utilize the renaming administrator :

Slide 15

Cross Product Example R1 S1 R1 X S1 =

Slide 16

Compound Operator: Intersection notwithstanding the 5 essential administrators, there are a few extra "Compound Operators" These add no computational energy to the dialect, yet are valuable shorthands. Can be communicated exclusively with the essential operations. Crossing point takes two information relations, which must be union-perfect . Q: How to express it utilizing fundamental administrators? R  S = R  (R  S)

Slide 17

Intersection S1 S2

Slide 18

Compound Operator: Join Joins are compound administrators including cross item, determination, and (some of the time) projection. Most regular sort of join is a " common join " (frequently just called "join"). R S reasonably is: Compute R X S Select lines where properties that show up in both relations have break even with qualities Project all one of a kind atttributes and one duplicate of each of the normal ones. Note: Usually done considerably more proficiently than this. Valuable for putting "standardized" relations back together.

Slide 19

Natural Join Example R1 S1 R1 S1 =

Slide 20

Other Types of Joins Condition Join (or "theta-join") : Result composition same as that of cross-item. May have less tuples than cross-item. Equi-Join : Special case: condition c contains just conjunction of balances .

Slide 21

"Theta" Join Example R1 S1 =

Slide 22

Compound Operator: Division Useful for communicating "for all" questions like: Find sids of mariners who have held all water crafts . For A/B characteristics of B are subset of attrs of A. May need to "venture" to get this going. E.g., let A have 2 fields, x and y ; B have just field y : A/B contains all x tuples with the end goal that for each y tuple in B , there is a xy tuple in A .

Slide 23

Examples of Division A/B B1 B2 B3 A/B2 A/B3 A/B1 A

Slide 24

Disqualified x values: Disqualified x values A/B: Expressing A/B Using Basic Operators Division is not basic operation; only a helpful shorthand. (Additionally valid for joins, yet joins are common to the point that frameworks execute joins exceptionally.) Idea : For A/B , process all x values that are not `disqualified' by some y esteem in B . x esteem is excluded if by connecting y esteem from B , we acquire a xy tuple that is not in A .

Slide 25

Reserves Examples Sailors Boats

Slide 26

Solution 2: Find names of mariners who've held watercraft #103 Solution 1:

Slide 27

A more effective (???) arrangement: Find names of mariners who've saved a red vessel Information about pontoon shading just accessible in Boats; so require an additional join: A question streamlining agent can locate this given the principal arrangement!

Slide 28

Find mariners who've held a red or a green vessel Can distinguish all red or green pontoons, then discover mariners who've saved one of these water crafts:

Slide 29

Find mariners who've saved a red and a green watercraft Previous approach won't work! Must recognize mariners who've saved red vessels, mariners who've held green water crafts, then discover the crossing point (take note of that sid is a key for Sailors) :

Slide 30

Find the names of mariners who've saved all pontoons Uses division; compositions of the info relations to/must be painstakingly picked: To discover mariners who've saved all "Interlake" vessels: .....