1 / 16

Understanding Relational Algebra: Definitions, Operations, and SQL Mapping

This document provides a comprehensive overview of key concepts in relational algebra as presented by E.F. Codd. It defines fundamental terms such as sets, relations, tuples, attributes, and domains. The core operations of relational algebra, including projection (π), selection (σ), Cartesian product (×), joins, and renaming (ρ) are explained in detail, accompanied by SQL mapping examples. The role of these operations in processing relational databases is highlighted, emphasizing their significance in SQL queries and data manipulation.

mardi
Télécharger la présentation

Understanding Relational Algebra: Definitions, Operations, and SQL Mapping

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. HIT334 Week 2

  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 • Domain: A Data type describing the types of values that appear in each column

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

  4. 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 =

  5. Projection  inColour Movie = • Note: No duplicates because: • In the relational algebra of sets, duplicate tuples are always eliminated

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

  7. SELECTION -  • length > 100 AND studioName = ‘Fox’ (MOVIE) • Combining them… • title, year, studio(length > 100 AND studioName = ‘Fox’(MOVIE))

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

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

  10. Cartesian Product • Cross Product or Product • Denoted as X e.g R X S • Pairing of each tuple with each tuple from S.

  11. Cartesian Product Relation R Relation S R X S =

  12. To find the tuples that agree on the value in column B : TEMP <- R X S RESULT <- σR.B = R.S TEMP

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

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

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

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

More Related