Hit334
This presentation is the property of its rightful owner.
Sponsored Links
1 / 16

HIT334 PowerPoint PPT Presentation


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

HIT334. Week 2. Some Definitions. Set : A collection of objects that contain NO duplicates Relation : Table, a set of tuples Tuple : Row in a table, a collection of attributes Attribute : Column Heading, an actual role played by Domain

Download Presentation

HIT334

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


Hit334

HIT334

Week 2


Some definitions

Some Definitions

  • Set: A collection of objects that contain NO duplicates

  • Relation: Table, a set of tuples

  • Tuple: Row in a table, a collection of attributes

  • Attribute: Column Heading, an actual role played by Domain

  • Domain: A Data type describing the types of values that appear in each column


Relation algebra

Relation Algebra

  • Proposed by Codd as an algebra on sets of tuples.

  • Originally consisted of:

    • Union, Set, Difference, Projection, Selection

  • Later expanded to include joins

  • Original Relational DBs query language, it underpins SQL


Projection

PROJECTION - 

Projection:Used to produce, from relation R, a new relation that has only some of R’s attributes.

Denoted as  A1,A2..AnR , which gives a relation that only has the columns for attributes A1, A2…An

For Example:

 title, year, studio MOVIE =


Projection1

Projection

 inColour Movie =

  • Note: No duplicates because:

    • In the relational algebra of sets, duplicate tuples are always eliminated


Selection

SELECTION - 

  •  : The selection operator, when applied to a relation, produces a new relation with a subset of R’s tuples, based on some condition

  • Denoted as: <select condition> R

  • length > 100 MOVIE


Selection1

SELECTION - 

  • length > 100 AND studioName = ‘Fox’ (MOVIE)

  • Combining them…

  • title, year, studio(length > 100 AND studioName = ‘Fox’(MOVIE))


Hit334

title, year, studio(length > 100 AND studioName = ‘Fox’ (MOVIE))

Instead of nesting, you can explicitly show a sequence of operations:

TEMP <-  length > 100 AND studioName = ‘Fox’(MOVIE))

RESULT<-  title, year, studio(TEMP)


Hit334

  • Mapping back to SQL would give:

    SELECT title, year, studio

    FROM movie

    WHERE length > 100

    AND studioname = ‘FOX’

  • Note that is not the same as sql “SELECT”


Cartesian product

Cartesian Product

  • Cross Product or Product

  • Denoted as X e.g R X S

  • Pairing of each tuple with each tuple from S.


Cartesian product1

Cartesian Product

Relation R

Relation S

R X S =


Hit334

To find the tuples that agree on the value in column B :

TEMP <- R X S

RESULT <- σR.B = R.S TEMP


Natural join

Natural Join

  • R <join condition> S

  • R R.B = S.B S

  • The relation that results from a JOIN operation has

    • One tuple for each combination of tuples from R & S that satisfy the join condition

    • A Number of attributes equal to the number of attributes in R plus the number of attributes in S


Rename

RENAME

  • s(X,C,D) (S)

    • Produces a relation S, that has attributes called X, C and D, instead of B, C and D

    • When we then take the product R x S, there is no naming conflict. Alternatively:

      • R X S(X,C,D) (S) => RS(A,B,X,C,D)

      •  RS(A,B,X,C,D) (R X S) => RS(A,B,X,C,D)


Recap

RECAP

  • PROJECT:  A1,A2..AnR

  • SELECT: <select condition> R

  • CROSS PRODUCT: R X S

  • JOIN: R <join condition> S

  • RENAME: if R(A1,A2..An)

    •  S(B1,B2..Bn) R

    •  S R

    •  (B1,B2..Bn) R


Hit334

To Retrieve the manager of each Department:

  • Combine each department with each employee whose SSN matches the MGRSSN value in the Department tuple:

    DEPT_MGR  DEPARTMENT MGRSSN=SSN (EMPLOYEE)

  • Project the result over the required attributes:

    RESULT  DNAME,LNAME,FNAME(DEPT_MGR)

    What if you only wanted the Manger for the Administration Department?


  • Login