notes 13

Information about notes 13

Published on August 21, 2007

Author: Techy_Guy

Source: authorstream.com

Content

Relational Algebra:  Relational Algebra CIS 4301 Lecture Notes Lecture 13 - 2/23/2006 Database Query Languages:  Database Query Languages Use 'Campus' schema Given a database, ask questions, get data as answers Ex: Get all students with GPA andgt; 3.7 who applied to Berkeley and Stanford and nowhere else Ex: Get all humanities departments at campuses in Florida with andlt; 1000 applicants Ex: Get the campus with highest average accept rate over the last five years Some questions are easy to pose, some are not Some questions are easy for DBMS to answer, some are not. 'Query language' also used to update the database Relational Query Languages:  Relational Query Languages Formal: relational algebra, relational calculus, Datalog Actual: SQL, Quel, Query-by-Example (QBE) In ALL languages, a query is executed over a set of relations, get single relation as the result Relational Algebra:  Relational Algebra Notation for describing queries in the relational model Relational model has concrete set of 'standard' operations Operations are not 'Turing Complete' Not a defect, helps with query processing and optimization FYI, a language is Turing Complete if it is powerful enough to implement any Turing machine. It's widely believed that Turing machines can do any calculation that can be performed by a modern computer program Start by introducing operations of relational algebra, SQL next Algebra applies to sets of tuples, i.e., relations Commercial DBMS use different notation of relations which are multisets Relational Algebra:  Relational Algebra Construct new relations from old ones Set of operators Relations are operands Build progressively more complex expressions by applying operators to relations or to rela. algebra expressions (which are relations as well) Query is an expression of relational algebra First concrete example of a query language Four broad classes of operations Set operations, selection operations, operations that combine data from two relations, rename operation Sample Relational Schema:  Sample Relational Schema Movie (Title,Year,length,filmType, studioName,producerC#) StarsIn (MovieTitle,MovieYear,StarName) MovieStar(Name,address,gender,birthdate) MovieExec(name,address,Cert#,netWorth) Studio (Name,address,presC#) Basics:  Basics Operations of traditional relational algebra fall into four broad classes: Set operations Operations that remove parts of a relation Operations that combine tuples of two relations Renaming Set Operations:  Set Operations Union (binary, commutative, associative) R  S Intersection (binary, commutative, associative) R  S Set Difference (binary) R - S Set of elements in R but not in S R-S  S-R !! R(A1,A2,…,An), S(B1,B2,…,Bn) must be union compatible R and S are of the same degree for each i, dom(Ai) = dom(Bi) Columns of R and S must be ordered so that order of attributes is same for both relations Example:  Example name address gender birthdate Carrie Fisher Mark Hamil 123 Maple St., Hollywood 456 Oak Rd., Brentwood F M 9/9/99 8/8/88 R name address gender birthdate Carrie Fisher Harrison Ford 123 Maple St., Hollywood 789 Palm Dr., Beverly Hills F M 9/9/99 7/7/77 S Sample Operations:  Sample Operations name address gender birthdate Carrie Fisher 123 Maple St., Hollywood F 9/9/99 R  S name address gender birthdate Carrie Fisher Harrison Ford 123 Maple St., Hollywood 789 Palm Dr., Beverly Hills F M 9/9/99 7/7/77 R  S Mark Hamil 456 Oak Rd., Brentwood M 8/8/88 name address gender birthdate R - S Mark Hamil 456 Oak Rd., Brentwood M 8/8/88 Relational Operator: Project:  Relational Operator: Project Project (unary) andlt;attr listandgt; (R) andlt;attr listandgt; is a list of attributes (columns) from R only Ex: title, year, length (Movie) 'horizontal restriction' A1 A2 A3 … An ... i A1 A2… Ak ... j, i  j  n k Project:  Project PROJECT can produce many tuples with same value Relational algebra semantics says remove duplicates SQL does not -- one difference between formal and actual query languages Relational Operator: Select:  Relational Operator: Select Select or Restrict (unary, commutative) andlt;predicateandgt; (R) andlt;predicateandgt; is a conditional expression of the type that we are familiar with from conventional programming languages andlt;attributeandgt; andlt;opandgt; andlt;attributeandgt; andlt;attributeandgt; andlt;opandgt; andlt;constantandgt; attribute in R op  {=,,andlt;,andgt;,, …, AND, OR} Ex: length100 (Movie) vertical restriction' Pictorially:  Pictorially A1 A2 A3 … An ... i A1 A2 A3 … An ... j, i  j  title year length filmType Star Wars Mighty Ducks Wayne’s World 1977 1991 1992 124 104 95 color color color Movie result set # of selected tuples is referred to as the selectivity of the condition Cartesian Product:  Cartesian Product Cartesian Product (binary, commutative, associative) R x S Sets of all pairs that can be formed by choosing the first element of the pair to be any element of R, the second any element of S Relation schema is union of schemas for R and S Resulting schema may be ambiguous Use R.A or S.A to disambiguate an attribute that occurs in both schemas Example:  Example A B 1 2 3 4 B C 2 5 4 7 D 6 8 9 10 11 x A R.B S.B C D R S 1 2 2 5 6 1 2 4 7 8 1 2 9 10 11 3 4 3 4 3 4 2 5 6 4 7 8 9 10 11 Join Operations:  Join Operations Natural Join (binary) R join S Match only those tuples from R and S that agree in whatever attributes are common to the schemas of R and S If r and s from r(R) and s(S) are successfully paired, result is called a joined tuple This join operation is the same we used in earlier section to recombine relations that had been projected onto two subsets of their attributes (e.g., as a result of a BCNF decomposition) Example:  Example A B 1 2 3 4 B C 2 5 4 7 D 6 8 9 10 11 join Resulting schema has attributes from R, either R or S (i.e., joining attribute(s)), and S Tuples that fail to pair with any tuple of the other relation are called dangling tuples A B C D R S 1 2 5 6 3 4 7 8 Join Operations:  Join Operations Theta Join (binary) R joinC S, where C is an arbitrary join condition Step 1: take the product of R and S Step 2: Select from the product only those tuples that satisfy condition C As with the product operation, the schema for the result is the union of the schemas of R and S Example:  Example B C 2 3 2 3 D 4 5 7 8 10 joinAandlt;D AND U.BV.B A B 1 2 6 7 C 3 8 9 7 8 U V V.B V.C D A U.B U.C 1 2 3 7 8 10 Final Word on Join:  Final Word on Join DBMS often implements theta-join as basic operation Use of term 'join' in implementation circles usually refers to theta-join or sometimes to cross-product

Related presentations


Other presentations created by Techy_Guy

Character Analysis
04. 01. 2008
0 views

Character Analysis

Roosevelt and Latin America
22. 10. 2007
0 views

Roosevelt and Latin America

S10 Processor Performance
17. 09. 2007
0 views

S10 Processor Performance

Hawaiian Humpback Whale
17. 09. 2007
0 views

Hawaiian Humpback Whale

rainforest
02. 10. 2007
0 views

rainforest

Comvalid BGPsentinel
07. 10. 2007
0 views

Comvalid BGPsentinel

PETERPAN
10. 10. 2007
0 views

PETERPAN

across crocodile lake
11. 10. 2007
0 views

across crocodile lake

MLM basic info
12. 10. 2007
0 views

MLM basic info

VortragRichter
15. 10. 2007
0 views

VortragRichter

azerbaijan
15. 10. 2007
0 views

azerbaijan

ch02jjm
19. 10. 2007
0 views

ch02jjm

PRNAV Eurocontrol presentation
19. 10. 2007
0 views

PRNAV Eurocontrol presentation

Hakkarainen 091104
17. 09. 2007
0 views

Hakkarainen 091104

Extreme Ostrich2
17. 09. 2007
0 views

Extreme Ostrich2

Soy Protein in Baking
04. 10. 2007
0 views

Soy Protein in Baking

McMurrenTidbits
23. 10. 2007
0 views

McMurrenTidbits

Larijani stemcell ABA2007 Final
24. 10. 2007
0 views

Larijani stemcell ABA2007 Final

F Gauze
24. 10. 2007
0 views

F Gauze

TornadoSafetyAMS
07. 10. 2007
0 views

TornadoSafetyAMS

nii report
09. 10. 2007
0 views

nii report

NS102 3a S07 Fighting Sail
21. 10. 2007
0 views

NS102 3a S07 Fighting Sail

am0845 Khanna
16. 11. 2007
0 views

am0845 Khanna

culturechange
10. 12. 2007
0 views

culturechange

Jeopardy
29. 10. 2007
0 views

Jeopardy

masstheory
02. 11. 2007
0 views

masstheory

Finnish Chemicals information
21. 08. 2007
0 views

Finnish Chemicals information

zodiac
21. 08. 2007
0 views

zodiac

ICT Expo Presentation
21. 08. 2007
0 views

ICT Expo Presentation

words alive notes
21. 08. 2007
0 views

words alive notes

200612011440150 ser mama
01. 10. 2007
0 views

200612011440150 ser mama

t5f2
07. 11. 2007
0 views

t5f2

PHYS 124 lt 2
13. 11. 2007
0 views

PHYS 124 lt 2

Localization days1 2
14. 11. 2007
0 views

Localization days1 2

Barlow
15. 11. 2007
0 views

Barlow

CEO breakfast Mar
16. 11. 2007
0 views

CEO breakfast Mar

SEVESO II 28 04 2003 d jansen
23. 11. 2007
0 views

SEVESO II 28 04 2003 d jansen

farawayplaces quiz
31. 10. 2007
0 views

farawayplaces quiz

lino hospitalstay 2005
28. 12. 2007
0 views

lino hospitalstay 2005

eno
05. 10. 2007
0 views

eno

Destinos Tradicionale
22. 10. 2007
0 views

Destinos Tradicionale

El Karib Hagmann 2001 HEKS ACORD
23. 10. 2007
0 views

El Karib Hagmann 2001 HEKS ACORD

Bioceramics
05. 01. 2008
0 views

Bioceramics

dennis
07. 01. 2008
0 views

dennis

DNR wetland benefits
07. 01. 2008
0 views

DNR wetland benefits

Norm Wright Presentation06
17. 09. 2007
0 views

Norm Wright Presentation06

Tudor Sports
21. 08. 2007
0 views

Tudor Sports

watson 2006
21. 08. 2007
0 views

watson 2006

IBM Presentation Roel Spee
24. 10. 2007
0 views

IBM Presentation Roel Spee

david simek
17. 09. 2007
0 views

david simek

75thWinter Silver
02. 08. 2007
0 views

75thWinter Silver

Revay Presentation
17. 09. 2007
0 views

Revay Presentation

week12 f03
17. 09. 2007
0 views

week12 f03

Ch12 ResolutionTheoremPro ving
17. 09. 2007
0 views

Ch12 ResolutionTheoremPro ving

INFOCOM99
05. 10. 2007
0 views

INFOCOM99

RoHS Presentation3 May
12. 10. 2007
0 views

RoHS Presentation3 May

Botany
07. 12. 2007
0 views

Botany

Week6February20 07
20. 02. 2008
0 views

Week6February20 07

Microcosmo Parte II
12. 10. 2007
0 views

Microcosmo Parte II

TSW
29. 02. 2008
0 views

TSW

HazMat Flow Study
26. 02. 2008
0 views

HazMat Flow Study

Vegetarian Nutrition 101
04. 03. 2008
0 views

Vegetarian Nutrition 101

White 10th Inter mountain
21. 08. 2007
0 views

White 10th Inter mountain

hondaimobil
02. 01. 2008
0 views

hondaimobil

Cfi
10. 03. 2008
0 views

Cfi

Timber Bridge Presentation
01. 01. 2008
0 views

Timber Bridge Presentation

carstenschymik
29. 12. 2007
0 views

carstenschymik

Ch 22 WB
07. 04. 2008
0 views

Ch 22 WB

Macroclean
10. 04. 2008
0 views

Macroclean

agingandwork
13. 04. 2008
0 views

agingandwork

nyBrazeau
14. 04. 2008
0 views

nyBrazeau

presentation total
16. 04. 2008
0 views

presentation total

3 Tufano2002
17. 04. 2008
0 views

3 Tufano2002

Chapter 18
18. 04. 2008
0 views

Chapter 18

Baltic states and Russia
12. 10. 2007
0 views

Baltic states and Russia

quotes
03. 10. 2007
0 views

quotes

WDR 2008
29. 11. 2007
0 views

WDR 2008

CHLA PSRS Overview
30. 04. 2008
0 views

CHLA PSRS Overview

15 UKernel
02. 05. 2008
0 views

15 UKernel

Mr Logan OCCAR
06. 03. 2008
0 views

Mr Logan OCCAR

shen
15. 10. 2007
0 views

shen

Industry Brief
22. 10. 2007
0 views

Industry Brief

sess 4 solano
18. 06. 2007
0 views

sess 4 solano

sess 2 vollmer
18. 06. 2007
0 views

sess 2 vollmer

NSDI05 poster
18. 06. 2007
0 views

NSDI05 poster

NLC talk
18. 06. 2007
0 views

NLC talk

My Proxy GW06
18. 06. 2007
0 views

My Proxy GW06

my Master 4
18. 06. 2007
0 views

my Master 4

More Mosaics
18. 06. 2007
0 views

More Mosaics

MEM SPI Jan00
18. 06. 2007
0 views

MEM SPI Jan00

VCA Org Charts
11. 12. 2007
0 views

VCA Org Charts

cjdim com Boudchiche
23. 10. 2007
0 views

cjdim com Boudchiche

GA Conf06China1
25. 03. 2008
0 views

GA Conf06China1

lecture 7 deadlock
17. 09. 2007
0 views

lecture 7 deadlock

Neptune Presentation
15. 06. 2007
0 views

Neptune Presentation

neptune
15. 06. 2007
0 views

neptune

Mehregan
18. 06. 2007
0 views

Mehregan

Plants are very useful
15. 06. 2007
0 views

Plants are very useful

Learning Phonics
15. 06. 2007
0 views

Learning Phonics

Learning Percent III
15. 06. 2007
0 views

Learning Percent III

Learning Percent I
15. 06. 2007
0 views

Learning Percent I

Physical Education Procedures
15. 06. 2007
0 views

Physical Education Procedures

Penguins
15. 06. 2007
0 views

Penguins

Olympic Wax Museum
15. 06. 2007
0 views

Olympic Wax Museum

howe9
17. 09. 2007
0 views

howe9

GSantin Siena 2 SpaceTools
03. 01. 2008
0 views

GSantin Siena 2 SpaceTools

gunderia powerpointlab
26. 11. 2007
0 views

gunderia powerpointlab

MySQL UC solid DB xact
18. 06. 2007
0 views

MySQL UC solid DB xact

Civics Lecture
31. 12. 2007
0 views

Civics Lecture

Physics and psycho2
14. 02. 2008
0 views

Physics and psycho2

TOUREDIT
12. 03. 2008
0 views

TOUREDIT

harvard deas
03. 01. 2008
0 views

harvard deas

Angelology
01. 10. 2007
0 views

Angelology

HenryVIII wwtbam
21. 08. 2007
0 views

HenryVIII wwtbam

AGU 2002
03. 10. 2007
0 views

AGU 2002

RubÃn Blades
22. 10. 2007
0 views

RubÃn Blades

tran present
21. 08. 2007
0 views

tran present

BU01
17. 09. 2007
0 views

BU01

Thode
17. 09. 2007
0 views

Thode

PP R CAJAR
22. 10. 2007
0 views

PP R CAJAR

moore lightning uw05
17. 09. 2007
0 views

moore lightning uw05

Space- The Outside World
15. 06. 2007
0 views

Space- The Outside World

arts and humanities applications
22. 11. 2007
0 views

arts and humanities applications

Session9 CATHALAC UNDP
25. 10. 2007
0 views

Session9 CATHALAC UNDP

use sunscreen
17. 09. 2007
0 views

use sunscreen

aatom
20. 11. 2007
0 views

aatom

9681
02. 08. 2007
0 views

9681

HHDL
15. 10. 2007
0 views

HHDL