Unit-6: Relational Languages

Tuple Relational Calculus (TRC)

Tuple Relational Calculus (TRC) is a non-procedural query language for relational databases. Unlike Relational Algebra, which specifies how to retrieve data, TRC specifies what data to retrieve without detailing the steps. It uses tuple variables that range over tuples (rows) of relations.

Key Concepts of TRC

• Tuple Variables: Variables that represent a tuple (row) in a relation. For example, T could represent a tuple from the Student relation.
• Domain Variables: Variables that represent components (attributes) of a tuple. For example, T.Name refers to the 'Name' attribute of tuple T.
• Atomic Formulas: Basic conditions that can be true or false. These include:
  • R(T): Tuple T belongs to relation R.
  • T.A op U.B: Attribute A of tuple T is compared with attribute B of tuple U using an operator (=, <, >, <=, >=, !=).
  • T.A op constant: Attribute A of tuple T is compared with a constant.
• Logical Connectives: Used to combine atomic formulas: AND (∧), OR (∨), NOT (¬).
• Quantifiers:
  • Existential Quantifier (∃): "There exists" or "for some". (∃T) (P(T)) means there exists at least one tuple T for which condition P(T) is true.
  • Universal Quantifier (∀): "For all". (∀T) (P(T)) means for all tuples T, condition P(T) is true.

General Form of a TRC Query

{ T | P(T) }
This reads as: "The set of all tuples T such that predicate P(T) is true."

TRC Examples

1. Find all students' names:
   Let Student(ID, Name, Major) be a relation.
   

   { T.Name | Student(T) }

2. Find the names of students majoring in 'Computer Science':
   

   { T.Name | Student(T) AND T.Major = 'Computer Science' }

3. Find the names of students who have enrolled in at least one course:
   Let Enrolls(StudentID, CourseID) be a relation.
   

   { S.Name | Student(S) AND (∃E) (Enrolls(E) AND S.ID = E.StudentID) }

Exam Notes

• TRC is considered tuple-oriented because variables represent entire tuples.
• It is theoretically equivalent in expressive power to Relational Algebra and Domain Relational Calculus.

Domain Relational Calculus (DRC)

Domain Relational Calculus (DRC) is another non-procedural query language, similar to TRC, but its variables range over the domains of attributes rather than entire tuples. It specifies a predicate that the desired attribute values must satisfy.

Key Concepts of DRC

• Domain Variables: Variables that represent individual values from the domain of an attribute. For example, ID, Name, Major could be domain variables.
• Atomic Formulas: Similar to TRC, but involving domain variables directly:
  • R(v1, v2, ..., vn): The values v1, v2, ..., vn form a tuple in relation R.
  • v1 op v2 or v op constant: Comparisons between domain variables or a variable and a constant.
• Logical Connectives and Quantifiers: Same as in TRC (AND, OR, NOT, ∃, ∀).

General Form of a DRC Query

{ v1, v2, ..., vn | P(v1, v2, ..., vn) }
This reads as: "The set of all tuples of values (v1, v2, ..., vn) such that predicate P(v1, v2, ..., vn) is true."

DRC Examples

1. Find all students' names:
   Let Student(ID, Name, Major) be a relation.
   

   { N | (∃I, M) (Student(I, N, M)) }

2. Find the names of students majoring in 'Computer Science':
   

   { N | (∃I) (Student(I, N, 'Computer Science')) }

3. Find the names of students who have enrolled in at least one course:
   Let Enrolls(StudentID, CourseID) be a relation.
   

   { SN | (∃SI, SM, ECI, ESID) (Student(SI, SN, SM) AND Enrolls(ESID, ECI) AND SI = ESID) }

Comparison: TRC vs. DRC

Query by Example (QBE)

Query by Example (QBE) is a visual, two-dimensional query language developed by IBM. It allows users to formulate queries by providing an example of the desired result, primarily by filling in skeleton tables displayed on a screen. It's user-friendly and often used in graphical database interfaces.

Key Concepts of QBE

• Skeleton Tables: Users interact with blank table structures (schemas) for relations.
• Example Elements: Variables represented by an underscore followed by a name (e.g., _NAME, _ID). These are used to link conditions across tables or to specify which columns to print.
• Constant Elements: Actual values used for selection criteria (e.g., 'Computer Science').
• Condition Box: A separate area where complex conditions involving multiple attributes or aggregate functions can be specified.
• Operators: Keywords used to specify actions. Common operators include:
  • P. (Print): Indicates the attributes to be displayed in the result.
  • I. (Insert): For inserting new tuples.
  • D. (Delete): For deleting existing tuples.
  • U. (Update): For modifying existing tuples.
  • AND, OR, comparison operators (<, >, =, etc.).

QBE Examples

Assume relations: STUDENT(ID, Name, Major, Age) and COURSE(C_ID, C_Name, Credits) and ENROLLS(S_ID, C_ID, Grade).

1. Find the names and majors of all students:
   

   STUDENT	ID	Name	Major	Age
   		P.	P.

2. Find the names of students majoring in 'Computer Science':
   

   STUDENT	ID	Name	Major	Age
   		P.	'Computer Science'

3. Find the IDs and names of students who have enrolled in the 'Database' course:
   

   STUDENT	ID	Name	Major	Age
   	P. _SID	P. _SNAME

   
   

   COURSE	C_ID	C_Name	Credits
   	_CID	'Database'

   
   

   ENROLLS	S_ID	C_ID	Grade
   	_SID	_CID

   Here, _SID and _CID are example elements linking the three tables.

Exam Notes

• QBE is well-suited for interactive query interfaces due to its visual nature.
• It simplifies complex queries by allowing users to fill out tables rather than writing textual code.

Datalog

Datalog is a declarative logic programming language often used as a query language for deductive databases. It's a subset of Prolog and is particularly useful for expressing recursive queries.

Key Concepts of Datalog

• Facts: Atomic statements that are considered true, representing the extensional database (EDB). They are similar to tuples in a relational database.
  • Example: student('101', 'Alice', 'CS').
• Rules: Horn clauses that define new relations (intensional database - IDB) based on existing facts and other rules. Rules have a head (conclusion) and a body (conditions).
  • Syntax: Head :- Body. (Read as: Head is true if Body is true).
  • Example: enrolled_in_cs(Name) :- student(ID, Name, 'CS'). (A student is enrolled in CS if their major is CS).
• Queries: Statements that ask whether a certain fact is true or to retrieve all facts that satisfy a certain pattern.
  • Syntax: ?- Query.
  • Example: ?- enrolled_in_cs('Alice'). (Is Alice enrolled in CS?)
  • Example: ?- enrolled_in_cs(X). (Who is enrolled in CS? - retrieves all matching X).
• Recursion: Datalog's powerful feature allows rules to refer to themselves, directly or indirectly, enabling the definition of recursive relationships (e.g., finding all descendants).
  • Example (simple transitive closure):
    path(X, Y) :- direct_path(X, Y).
path(X, Y) :- direct_path(X, Z), path(Z, Y).
    This defines a path between X and Y if there's a direct path, or if there's a direct path from X to Z and a path from Z to Y.

Components of a Datalog Program

• Extensional Database (EDB): The set of facts explicitly stored in the database.
• Intensional Database (IDB): The set of relations defined by rules. These are derived facts.

Datalog Example

Consider two relations: Parent(Child, ParentName) and Female(PersonName).

Facts (EDB):
Parent('Bob', 'Alice').
Parent('Carol', 'Bob').
Parent('David', 'Carol').
Female('Alice').
Female('Carol').

Rules (IDB):
1. Define Mother(Child, MotherName):
Mother(C, M) :- Parent(C, M), Female(M).
2. Define Grandparent(Grandchild, GParent):
Grandparent(GC, GP) :- Parent(GC, P), Parent(P, GP).

Queries:
?- Mother('Bob', M). — Result: M = 'Alice'
?- Grandparent('David', GP). — Result: GP = 'Alice'

Exam Notes

• Datalog is declarative; it focuses on what to retrieve, not how.
• It's particularly strong in handling recursive queries, which are difficult or impossible in standard Relational Algebra or SQL without extensions.

Set Operations

Relational databases treat relations as sets of tuples. Therefore, standard mathematical set operations can be applied to compatible relations (tables) in SQL. For set operations to be valid, the relations must be union-compatible, meaning they must have the same number of columns, and corresponding columns must have compatible data types.

1. UNION

The UNION operator combines the result sets of two or more SELECT statements into a single result set. It automatically removes duplicate rows.

• Syntax:
  SELECT column1, column2 FROM TableA
UNION
SELECT column1, column2 FROM TableB;
• Characteristics:
  1. Combines results from two or more queries.
  2. Requires union-compatible relations.
  3. Removes duplicate rows by default.
  4. Use UNION ALL to include duplicates (often more efficient if duplicates are desired).
• Example: Find all unique names of employees who are either in the 'Sales' department or have a salary greater than 50000.
  SELECT Name FROM Employees WHERE Department = 'Sales'
UNION
SELECT Name FROM Employees WHERE Salary > 50000;

2. INTERSECT

The INTERSECT operator returns only the rows that are present in the result sets of both SELECT statements. It only returns distinct rows.

• Syntax:
  SELECT column1 FROM TableA
INTERSECT
SELECT column1 FROM TableB;
• Characteristics:
  1. Returns common rows from two or more queries.
  2. Requires union-compatible relations.
  3. Removes duplicate rows by default.
• Example: Find names of employees who are in the 'Sales' department AND have a salary greater than 50000.
  SELECT Name FROM Employees WHERE Department = 'Sales'
INTERSECT
SELECT Name FROM Employees WHERE Salary > 50000;

3. EXCEPT (or MINUS)

The EXCEPT (or MINUS in some SQL dialects like Oracle) operator returns all rows from the first SELECT statement that are not found in the second SELECT statement. It returns only distinct rows.

• Syntax:
  SELECT column1 FROM TableA
EXCEPT
SELECT column1 FROM TableB;
• Characteristics:
  1. Returns rows unique to the first query.
  2. Requires union-compatible relations.
  3. Removes duplicate rows by default.
• Example: Find names of employees who are in the 'Sales' department but DO NOT have a salary greater than 50000.
  SELECT Name FROM Employees WHERE Department = 'Sales'
EXCEPT
SELECT Name FROM Employees WHERE Salary > 50000;

Important Observation

• The column names in the final result set are typically taken from the first SELECT statement.
• Order of columns and data types must match for all queries involved in a set operation.

Aggregate Functions

Aggregate functions perform a calculation on a set of rows and return a single summary value. They are commonly used with the GROUP BY clause to perform calculations on groups of rows.

Common Aggregate Functions

1. COUNT()

   Counts the number of rows or non-NULL values in a specified column.

   • COUNT(*): Counts all rows, including those with NULL values.
   • COUNT(column_name): Counts rows where column_name is not NULL.
   • COUNT(DISTINCT column_name): Counts the number of unique, non-NULL values in a column.
   • Example: SELECT COUNT(*) FROM Students; (Total number of students)
     SELECT COUNT(DISTINCT Major) FROM Students; (Number of unique majors)
2. SUM()

   Calculates the sum of all values in a numeric column. Ignores NULL values.

   • Example: SELECT SUM(Salary) FROM Employees; (Total salary of all employees)
     SELECT SUM(Quantity) FROM OrderDetails WHERE ProductID = 101;
3. AVG()

   Calculates the average of all values in a numeric column. Ignores NULL values.

   • Example: SELECT AVG(Age) FROM Students; (Average age of students)
     SELECT AVG(Price) FROM Products WHERE Category = 'Electronics';
4. MIN()

   Finds the minimum value in a column. Works with numeric, string, and date/time data types. Ignores NULL values.

   • Example: SELECT MIN(Salary) FROM Employees; (Lowest salary)
     SELECT MIN(EnrollmentDate) FROM Students;
5. MAX()

   Finds the maximum value in a column. Works with numeric, string, and date/time data types. Ignores NULL values.

   • Example: SELECT MAX(Salary) FROM Employees; (Highest salary)
     SELECT MAX(DOB) FROM Students;

GROUP BY Clause

The GROUP BY clause is used with aggregate functions to group rows that have the same values in specified columns into summary rows. The aggregate function then operates on each group separately.

• Syntax:
  SELECT column_name(s), aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
• Example: Find the average salary for each department.
  SELECT Department, AVG(Salary) FROM Employees GROUP BY Department;
• Observation: Any column in the SELECT list that is not part of an aggregate function must appear in the GROUP BY clause.

HAVING Clause

The HAVING clause is used to filter groups based on a specified condition. It's similar to the WHERE clause but applies to groups after they have been formed and aggregated, rather than individual rows.

• Syntax:
  SELECT column_name(s), aggregate_function(column_name)
FROM table_name
GROUP BY column_name(s)
HAVING condition;
• Example: Find departments where the average salary is greater than 60000.
  SELECT Department, AVG(Salary) FROM Employees GROUP BY Department HAVING AVG(Salary) > 60000;
• Common Mistake: Using WHERE for aggregate conditions. WHERE filters individual rows before grouping; HAVING filters groups after grouping and aggregation.

NULL Values

In relational databases, a NULL value represents an unknown, inapplicable, or missing piece of information. It is not the same as zero or an empty string; it signifies the absence of any value.

Characteristics of NULL

• Unknown: The value exists but is not known.
• Not Applicable: The attribute does not apply to a particular tuple.
• Missing: The value should be present but is currently absent.

Implications of NULL Values

1. Arithmetic Operations: Any arithmetic operation involving a NULL value will result in NULL.
   

   10 + NULL = NULL
5 * NULL = NULL

2. Comparison Operations: Comparing any value with NULL using standard comparison operators (=, <, >, etc.) results in UNKNOWN, not true or false. In SQL, UNKNOWN behaves like false in WHERE clauses.
   

   'Apple' = NULL is UNKNOWN
   10 < NULL is UNKNOWN
   NULL = NULL is UNKNOWN (NULL is never equal to anything, not even itself)

3. Logical Operations (AND, OR, NOT): The presence of NULL introduces three-valued logic (TRUE, FALSE, UNKNOWN).
   • TRUE AND UNKNOWN = UNKNOWN
   • FALSE AND UNKNOWN = FALSE
   • TRUE OR UNKNOWN = TRUE
   • FALSE OR UNKNOWN = UNKNOWN
   • NOT UNKNOWN = UNKNOWN
4. Handling NULL in WHERE Clause: Because standard comparisons with NULL yield UNKNOWN, special predicates are used to check for NULL values.
   • IS NULL: Checks if a value is NULL.
   • IS NOT NULL: Checks if a value is not NULL.
   • Example: SELECT Name FROM Employees WHERE Department IS NULL;
SELECT Name FROM Employees WHERE Salary IS NOT NULL;
5. Impact on Aggregate Functions: By default, most aggregate functions (SUM, AVG, MIN, MAX) ignore NULL values in their calculations. COUNT(column_name) also ignores NULLs, but COUNT(*) counts all rows, including those with NULLs in other columns.
   • Example: If a column has values {10, 20, NULL, 30}, then AVG() would calculate (10+20+30)/3 = 20, not (10+20+NULL+30)/4.

Exam Notes

• Understanding NULL behavior is crucial for accurate query results.
• Always use IS NULL or IS NOT NULL for checking NULLs, never = NULL or != NULL.
• NULL values can significantly affect aggregate function results; be aware of their exclusion.