Relational Query Languages - Database Design - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Relational Query Languages - Database Design - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Relational Algebra

Introduction

• Relational query languages are languages for describing queries on a relational database
• Three variants Three variants
  • Relational Algebra
  • Relational Calculus
  • SQL
• Query languages V.S. programming languages
  • Query languages support easy, efficient access to large data sets.

What is Algebra?

• A language based on operators and a domain of values
• Operators map values taken from the domain into other domain values
• Hence, an expression involving operators and arguments produces a value in the domain
• Consider arithmetic operations +, - and * over integers.
  • Algebra expressions: 2+3, (46–3)+3, (7x)+(3x)
• Relational algebra:
  • Domain: the set of all relations
  • Expression: referred to as a query

Relational Algebra

• Domain : set of relations
• Basic operators : select, project, union, set

difference, Cartesian product

• Derived operators : set intersection, division,

join

• Procedural : Relational expression specifies

query by describing an algorithm for

determining the result of an expression

Examples Queries

• Find all the courses student named ‘John Doe’ has completed.
• Find all students who are taking a course by Prof. Lee in ‘Fall 2005’.
• Find all courses taught by a faculty from the ‘CS’ department in ‘Fall 2005’.
• Find all faculty who taught courses both in ‘Fall’ and ‘Spring’ 2005.
• Find all faculty who did not teach courses both in ‘Fall’ and ‘Spring’ 2005.
• Find all students with 4.0 GPA.
• Find all students who completed a course without taking one of its prerequisites.

Relational Algebra

• A relation schema is given by R(A1,…,Ak), the name of the relation and the list of the attributes in the relation
• A relation is a set of tuples that are valid instances of its schema
• Relational algebra expressions take as input relations and produce as output new relations.
• After each operation, the attributes of the participating relations are carried to the new relation. The attributes may be renamed, but their domain remains the same.

Set Operators

• Set operators connect two relations by set operations.
• However, for a set operation R1 op R2 to be valid, R and R2 should be union-compatible, that is - R1, R2 have the same number of columns - The names of the attributes are the same in both relations - Attributes with the same name in both relations have the same domain

Selection

SELECTION: σ selection-condition ( R )

 Select from R all tuples that satisfy the selection- condition

 The condition only refers to the attributes in R

 An atomic-selection-condition is of the form

 relation-attribute operation constant, or  relation-attribute operation relation-attribute

 A selection-condition is obtained by boolean combination of atomic selection conditions by means of connectives AND, OR, and NOT.

Projection

PROJECT: Π attribute-list (R)

• Return from R all tuples, but remove from all tuples any attribute that is not in the attribute list
• The attribute list can only refer to attributes in R

Examples:

 Π (^) ID (FACULY)

 Π (^) ID ( σ Name like ‘%Lee’ FACULTY )

 Π CrsCode, Textbook (COURSE)

 Π (^) PrereqCrsCode ( σ CrsCode=‘CSCI4380’ REQUIRES )

Set Operators

• Set operators connect two relations by set operations.
• However, for a set operation R1 op R2 to be valid, R and R2 should be union-compatible, that is - R1, R2 have the same number of columns - The names of the attributes are the same in both relations - Attributes with the same name in both relations have the same domain

Set Operators

 Π (^) Name (FACULY) ∪ Π (^) Name (STUDENT)

 Π (^) Address (FACULY) ∩ Π (^) Address (STUDENT)

 Π (^) CrsCode (CLASS) - Π (^) CrsCode (TRANSCRIPT)

 Π (^) Id (STUDENT) - Π (^) StudId (TRANSCRIPT)

 Problem: The two relations in this case are not union compatible (even though the attribute numbers and domains match, the names do not).  Solution For this?

Rename

• Rename all the attributes in the relation
  • Given a relation with schema R(A1,…,An)
  • The expression R[B1,…,Bn] is used to rename attribute A1 as B1, …, An as Bn.
  • The rename operator does not change the domain of the attributes.
  • The rename operator does not change the number of attributes in a relation (that can be done by the projection operation).

Set Difference

Find all students with 4.0 GPA

or find students with all As (and at least one A grade) or find students who never got anything other than an A

Temp1= Π (^) StudId (σ Grade=’A’ (TRANSCRIPT))

Students who got at least one non-A grade

Temp2 = Π (^) StudId ( TRANSCRIPT)

Students who got at least one grade

Result = Temp2 - Temp

Cartesian Product

• Given two sets, A={a1,a2,a3}, B={b1,b2}, the Cartesian

Product (A × B) returns the set of tuples

A × B={(a1,b1), (a1,b2), (a2,b1), (a2,b2), (a3,b1), (a3,b2)}

• The Cartesian product for relations is a generalization of this concept.