The Relational Model & Relational Algebra

Relations

A relation is a set of tuples, typically represented as a table. Each row in the table represents a single tuple, and each column represents an attribute. Relations have several important properties:

• No duplicate tuples - Each tuple in a relation must be unique.
• Unordered tuples - The order of tuples in a relation is not significant.
• Unordered attributes - The order of attributes (columns) is not significant, though they're typically given names.
• Atomic values - Each cell in the relation contains a single atomic value (not a set or list).

Attributes and Domains

Attributes are the properties or characteristics of the data stored in a relation. Each attribute has a name and a domain. A domain is the set of allowable values for an attribute. For example, a "salary" attribute might have a domain of positive real numbers.

Keys

Keys play a crucial role in the relational model:

• Primary Key - An attribute (or set of attributes) that uniquely identifies each tuple in a relation.
• Candidate Key - A minimal set of attributes that could serve as a primary key.
• Foreign Key - An attribute (or set of attributes) in one relation that references the primary key of another relation, establishing relationships between data.

Select ()

The select operation filters tuples from a relation based on a specified condition. It extracts those tuples that satisfy a given predicate.

Notation: _predicate(R)

Example: _{salary > 50000}(Employee) returns all tuples from the Employee relation where the salary is greater than 50000.

Project ()

The project operation extracts specified attributes from a relation, producing a new relation with fewer attributes.

Notation: _{attribute-list}(R)

Example: _{name,department}(Employee) returns a relation with only the name and department attributes from the Employee relation.

Union ()

The union operation combines tuples from two relations that are union-compatible (having the same number of attributes and compatible domains).

Notation: R S

Example: FullTimeEmployee PartTimeEmployee returns all tuples that appear in either relation, eliminating duplicates.

Set Difference (-)

The set difference operation returns tuples that exist in the first relation but not in the second. The relations must be union-compatible.

Notation: R - S

Example: Employee - Manager returns all employees who are not managers.

Cartesian Product ()

The Cartesian product combines each tuple from one relation with each tuple from another relation, creating all possible combinations.

Notation: R S

Example: Employee Department pairs each employee with each department, regardless of any actual relationship between them.

Join ()

The join operation combines related tuples from two relations based on a condition. There are several types of joins:

• Theta Join () - Combines tuples from two relations based on a condition ().
• Natural Join () - Combines tuples based on common attributes with the same values, eliminating duplicate columns.
• Equijoin - A special case of theta join where the condition is equality (=) on attributes.
• Left Outer Join (_L) - Includes all tuples from the left relation, matching tuples from the right relation when the condition is met, and padding with NULL values otherwise.
• Right Outer Join (_R) - Similar to left outer join but includes all tuples from the right relation.
• Full Outer Join (_F) - Includes all tuples from both relations, padding with NULL values when there's no match.

Example: Employee _{Employee.dept_id = Department.id} Department combines employee records with their corresponding department information.

Intersection ()

The intersection operation returns tuples that exist in both relations. The relations must be union-compatible.

Notation: R S

Example: FullTimeEmployee FullTimeManager returns those individuals who are both full-time employees and full-time managers.

Division ()

The division operation finds tuples in one relation that are related to all tuples in another relation. This is particularly useful for queries like "find customers who bought all products."

Notation: R S

Example: (Employee Project) Project returns employees who worked on all projects.

Rename ()

The rename operation is used to rename attributes or relations in the result of an expression. This is important for clarity and for handling conflicts when combining relations.

Notation: _new-name(R) or _{new-attr-names}(R)

Example: _Person(Employee) simply renames the Employee relation to Person.

Assignment ()

The assignment operation assigns a relation to a temporary relation variable for convenience in building complex expressions.

Notation: Temp expression

Example: Temp _{department = 'Sales'}(Employee)

Example Schema

Consider the following database schema for an e-commerce system:

• Customer(customer_id, name, email, address)
• Product(product_id, name, category, price)
• Order(order_id, customer_id, order_date)
• OrderItem(order_id, product_id, quantity)

Query 1: Find all customers who ordered products in the 'Electronics' category

customer_id, name(category='Electronics'(Customer  Order  OrderItem  Product))        

Query 2: Find customers who haven't placed any orders

customer_id, name(Customer) - customer_id, name(Customer  Order)        

Query 3: Find the total amount spent by each customer

(This would require aggregation operations, which are an extension to basic relational algebra)