Social Algebra

1988 days ago, 694 views
PowerPoint PPT Presentation
Social Query Languages. Inquiry =

Presentation Transcript

Slide 1

Social Algebra

Slide 2

Relational Query Languages Query = "recovery program" Language cases: Theoretical : Relational Algebra Relational Calculus tuple social analytics (TRC) space social math (DRC) Practical SQL (SEQUEL from System R) QUEL (Ingres) Datalog (Prolog-as ql) Theoretical: offer semantics to functional QL's vital to comprehend inquiry enhancement in social DBMSs

Slide 3

Relational Algebra Basic administrators select (  ) extend ( p ) union (  ) set distinction ( - ) cartesian item ( x ) rename (  ) The administrators take maybe a couple relations as information sources and give another connection accordingly. connection social administrator connection

Slide 4

Example Instances R1 S1 Boats S2 Schema: Boats( offer , bname, shading) Sailors( sid , sname, rating, age) Reserves( sid, offer, day )

Slide 5

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

Slide 6

Projection S2

Slide 7

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

Slide 8

Selection Notation:  p ( r ) p is known as the determination predicate , r can be the name of a table, or another inquiry Predicate: Simple attr1 = attr2 Attr = steady esteem (additionally, <, > , and so forth) Complex predicate1 AND predicate2 predicate1 OR predicate2 NOT (predicate)

Slide 9

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

Slide 10

Union S1 S2

Slide 11

Set Difference S1 S2 – S1 S2

Slide 12

Cartesian-Product S1  R1: Each line of S1 matched with each line of R1. Like the c.p for scientific relations: each tuple of S1 "annexed" to each tuple of R1 Q: what number lines in the outcome? Result construction has one field for each 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 13

Cartesian Product Example R1 S1 R1 X S1 =

Slide 14

Rename (  ) Allows us to allude to a connection by more than one name and to rename clashing names Example:  x ( E ) gives back the expression E under the name X If a social variable based math expression E has arity n , then  x ( A1, A2, … , A ) ( E ) gives back the aftereffect of expression E under the name X , and with the ascribes renamed to A1, A2, … ., A . Ex.  temp1 ( sid1,sname,rating, age, sid2, offer, day ) (R1 x S1)

Slide 15

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

Slide 16

Intersection S1 S2

Slide 17

Compound Operator: Join Joins are compound administrators including cross item, choice, and (now and then) projection. Most regular kind of join is a " common join " (frequently just called "join"). R S reasonably is: Compute R  S Select lines where properties that show up in both relations have measure up to qualities Project all extraordinary atttributes and one duplicate of each of the regular ones. Note: Usually done a great deal more productively than this. Valuable for putting "standardized" relations back together.

Slide 18

Natural Join Example R1 S1 R1 S1 =

Slide 19

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

Slide 20

Compound Operator: Division Useful for communicating "for all" inquiries like: Find sids of mariners who have saved all water crafts . For A/B properties 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 tuples (x) with the end goal that for each y tuple in B, there is a xy tuple in A.

Slide 21

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

Slide 22

A/B = Disqualified x values Expressing A/B Using Basic Operators Division is not basic operation; only a helpful shorthand. (Likewise valid for joins, however joins are common to the point that frameworks actualize joins uncommonly.) Idea : For A/B , figure 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 . Precluded x values =

Slide 23

Banking Example (branch-name, branch-city, resources) (client name, client road, client just) record (account-number, branch-name, adjust) advance (credit number, branch-name, sum) contributor (client name, account-number) borrower (client name, advance number)

Slide 24

Example Queries Find all advances of over $1200  sum >1200 ( advance ) Find the advance number for each advance of a sum more noteworthy than $1200 p advance number ( sum > 1200 ( advance )) (advance number, branch-name, sum)

Slide 25

Example Queries Find the names of all clients who have an advance, an investor account, or both, from the bank p client name ( borrower )  p client name ( contributor ) Find the names of all clients who have an advance and a contributor account at bank. p client name ( borrower )  p client name ( investor ) contributor (client name, account-number) borrower (client name, credit number)

Slide 26

Example Queries Find the names of all clients who have an advance at the Perryridge branch yet don't have a contributor account at any branch of the bank. p client name ( branch-name = "Perryridge" (borrower credit)) (advance number, branch-name, sum) contributor (client name, account-number) borrower (client name, advance number) – p client name ( investor )

Slide 27

Example Queries (account-number, branch-name, adjust) Find the biggest record adjust Rename account connection as d The question may be: p adjust (account) - p account.balance (  account.balance < d.balance ( account x r d (account )))

Slide 28

Example Queries (account-number, branch-name, adjust) Find all clients who have a record from in any event the "Downtown" and the Uptown" branches. Inquiry 1 p CN (  BN ="Downtown " ( investor account ))  p CN (  BN ="Uptown " ( contributor account )) where CN indicates client name and BN signifies branch-name . Question 2  client name, branch-name ( investor account )   temp(branch-name ) ({("Downtown"), ("Uptown")}) contributor (client name, account-number)

Slide 29

Example Queries Find all clients who have a record at all branches situated in Boston. p client name, branch-name ( contributor account )  p branch-name ( branch-city = "Boston" ( branch ))

Slide 30

Extended Relational Operations Additional Operators that amplify the force of the dialect Based on SQL… make the dialect less spotless Generalized projections Outer Joins Update

Slide 31

General Projection Notation: p e1, e2, … , en (Relation) ei: can incorporate any math operation – not just characteristics credit = Example: Then: p cname, confine – adjust =

Slide 32

Outer Joins Motivation: advance borrower advance borrower = Join result loses: any record of Perry any record of Smith

Slide 33

Outer Join ( ) Left external Join ( ) safeguards all tuples in left connection advance borrower = Right external Join ( ) saves all tuples in right connection

Slide 34

Outer Join (cont) Full Outer Join ( ) protects all tuples in both relations

Slide 35

Update ( ) Deletion: r  r – s account  account – s bname = Perry (account) Insertion: r  r s branch  branch {( BU, Boston, 9M)} Update: r  p e1, e2, … , en (r) account  p bname, acct_no, bal * 1.05 (record) 