Formal Query Language

In Formal Query Language, there are two fundamental foundations of Query language formation, namely:

1. Relational Algebra
2. Relational Calculus

In this discussion, we will only discuss Relational Algebra because it is more commonly used as the basis for commonly used Query Languages.

Relational Algebra

#

It is a collection of operations on relations, where each operation uses one or more relations to produce a new relation.

A Query Language based on operations in Relational Algebra is a procedural query language.

Operators Used

#

A. Set Operators

#

1. Union or Union \((\cup)\)
   The union of relation A and B is expressed as \(A \cup B\)

   

2. Intersection or Intersection \(( \cap )\)
   The intersection of relation A and B is expressed as \(A \cap B\)

   

3. Difference
   The difference of relation A and B is expressed as A - B

   

4. Cartesian product
   The cartesian product of relation A and B is expressed as A X B

   example:
   A = { 1,2,3}
   B = { 5,7 }
   A X B = { ( 1,5), (1,7), ( 2,5), (2,7), (3,5),(3,7) }

B. Relational Operators

#

1. Restrict \( \sigma \) is Selection of tuple or record
2. Project \( \pi \) is selection of attribute or field
3. Divide \( \div \) is divide
4. Join \( \theta \) is combine

Relational Algebra

#

Operators in relational algebra are divided into 2 groups:

1. Basic operators for fundamental operations
2. Additional operators for additional operations

The table below is an example for performing Relational Algebra commands:

RELATION: COURSE

RELATION: STUDENT

RELATION: REGISTRATION

RELATION: LECTURER

Basic Operators

#

Consists of 2 types:

1. Union Operation -> Operation that uses 1 relation

a. Selection (\(\sigma\)): to select rows from a relation

• \(\sigma\) predicate (R) selection operation works on 1 relation R and defines the relation that contains only tuples of R that satisfy the condition (predicate).
• For more complex predicates, logical operators can be used ^(and), v(or) and ~(not)

Contoh :

• Find tuples from STUDENT that have male gender, Relational algebra expression: \(\sigma\) GENDER=“MALE” (STUDENT)
• Display course data that has code 360 or that has credits 4 \(\sigma\) COURSE_CODE=“360” V CREDITS=4 (COURSE)

\(\sigma\)J_KEL=“LAKI-LAKI” (MAHASISWA)

\(\sigma\)COURSE_CODE=“360” V CREDITS=4 (COURSE)

b. Projection (\(\pi\)): used to detail columns

• \(\pi\) a1…an (R) projection operation works on 1 relation R and defines the relation that contains a vertical subset of R displaying values for certain attributes and eliminating duplicate attribute values.

Contoh : Display lecturer name along with salary
\(\pi\) lecturer_name,salary (LECTURER)

2. Binary Operation -> Operation that uses 2 or a pair of relations
   1. Cartesian product ( X ): An operator with two relations to produce a table that is the result of a cartesian product. In cartesian product there are duplicate values in some tuples/records so it is improved with join condition: namely by providing a specific condition/requirement.

Example: Display lecturer_id, lecturer_name (from Lecturer relation), course_name (from Course relation), academic_year, semester, day, class_time, time, class (from Teaching relation) where the teaching semester is on semester ‘1’.

\(\pi\) lecturer_id, lecturer_name, course_name( \(\sigma\) lecturer.lecturer_id = course.lecturer_id \(\wedge\) course.credits=3 \((\text{Lecturer} \times \text{Course}\)) )

Lecturer x Course

Lecturer x Course (lecturer.lecturer_id = course.lecturer_id \(\wedge\) credits=3)

2. Union \( (\cup) \)
   Operation to produce a union of tables with the condition that both tables have the same attributes, i.e., the domain of the i-th attribute of each table must be the same. Eliminate duplicate attribute values.

   RUS={ X I X E R or X E S}

Example: \(\pi \text{ student \_id(student1)} \cup \pi \text{ student \_id(student2)}\)

STUDENT1

STUDENT2

Result: \(\pi \text{ student \_id(student1)} \cup \pi \text{ student \_id(student2)}\)

3. Set Difference ( - )
   Operation to get tables in one relation but not in another relation.

R – S = { X I X E R and X E S }

Example: Display names of students who live in Depok but are not female

Query I: display names of those living in Depok
\(\pi\) student_name(\(\sigma\) address=“DEPOK” (STUDENT))

Query II: display names of those with female gender
\(\pi\) name(\(\sigma\) gender =“FEMALE”)

Display query I minus query II:
\(\pi\) student_name(\(\sigma\) address=“DEPOK”(STUDENT)) - \(\pi\) name(\(\sigma\) gender=“FEMALE”)

Query I ( R ): \(\pi\) student_name(\(\sigma\) address=“DEPOK” (STUDENT))

Query II (S): \(\pi\) name(\(\sigma\) gender =“FEMALE”)

\(\pi\) student_name(\(\sigma\) address=“DEPOK”(STUDENT)) - \(\pi\) name(\(\sigma\) gender=“FEMALE”)

4. SET INTERSECTION \( (\cap) \)
   Operation to produce an intersection of two tables with the condition that both tables have the same attributes, the domain of the i-th attribute of both tables is the same.

Example: \(\pi\) student_name(\(\sigma\) address=“DEPOK”(STUDENT)) \( \cap \) \(\pi\) name(\(\sigma\) gender=“FEMALE”)

Additional Operators
The condition of duplicate values in cartesian product is improved by join condition, consisting of:

1. THETA JOIN
   Operation that combines cartesian product operation with selection operation with a certain criteria. Theta Join notation R►◄FS. Predicate F can be a comparison operator <,≤,>,≥,≠,=

student.►◄student.student_id=registration.student_id registration

2. NATURAL JOIN
   Operation that combines selection and cartesian product operations with a certain criteria on the same column, where each attribute appears 1 x. Natural Join notation R►◄S.

3. DIVISION
   Operation that divides tuples from 2 relations. Notation R:S

A

B

A/B