Database Querying Lecture 4

Lecture 4: Querying Database

• This lecture covers querying databases using Relational Algebra, DML (Data Manipulation Language), and DCL (Data Control Language) commands.

Relational Algebra Overview

• Relational algebra is a set of operations for the relational model.
• These operations allow users to specify retrieval queries.
• Algebra operations create new relations from existing ones.
• A sequence of relational algebra operations forms a relational algebra expression.

Unary Relational Operations: SELECT

• The SELECT operation (denoted by σ) selects a subset of tuples based on a condition.

• The selection condition filters tuples.

• Only tuples satisfying the condition are kept; others are discarded.

• The general form is σ(R) where R is the relation.

• Examples:

  • Selecting employees where department number is 4: σDNO=4(EMPLOYEE)
  • Selecting employees with salary greater than $30,000: σSALARY > 30000(EMPLOYEE)

Unary Relational Operations: PROJECT

• The PROJECT operation (denoted by π) keeps specific columns (attributes) from a relation.

• It discards other columns.

• It creates a vertical partitioning of the relation.

• The general form is π(R) where R is the relation.

• Example:

  • Listing employee's first name, last name, and salary: πLNAME, FNAME, SALARY(EMPLOYEE)

Single Expression vs. Sequence of Relational Operations

• Retrieving employees in department 5 requires a SELECT and PROJECT operation.
• Single expressions are possible.
• Example:
  • Listing employee's first name, last name, and salary for department 5: πFNAME, LNAME, SALARY(σDNO=5(EMPLOYEE))

Relational Algebra Operations from Set Theory: UNION

• UNION combines tuples from two relations (R and S).

• Result includes all tuples in R or S, or both.

• Duplicate tuples are removed.

• The relations must be type compatible (same number of attributes and compatible types).

• Example:

  • Finding employees who work in department 5 or supervise someone in department 5.

Relational Algebra Operations from Set Theory: INTERSECTION

• INTERSECTION returns tuples present in both relations (R and S).
• The two relations must be type compatible.

Binary Relational Operations: JOIN

• JOIN operation combines related tuples from different relations.

• Replaces the sequence of Cartesian product followed by select operations.

• It's essential for relational databases with multiple relations.

• Example:

  • Finding the name of the manager for each department: DEPARTMENT ⋈ MGRSSN = EMPLOYEE.SSN EMPLOYEE

DML Commands in SQL

• SQL includes INSERT, SELECT, UPDATE, and DELETE commands.

INSERT command

• Adds data to a table.

• Syntax: INSERT INTO table1 (column1, column2) VALUES (value1, value2);

SELECT command

• Retrieves data from a table.

• Syntax: SELECT * FROM table_name; / SELECT column1, column2 FROM table_name WHERE condition

UPDATE command

• Modifies data in a table.

• Syntax: UPDATE Table_name SET Column1 = Value1 WHERE condition;

DELETE command

• Removes rows from a table.

• Syntax: DELETE FROM Table_Name WHERE condition;

Data Control Language (DCL)

• DCL controls database access permissions.

• Commands: GRANT and REVOKE

GRANT command

• Grants access privileges.

• Example: GRANT insert, select ON accounts TO Ahmed

REVOKE command

• Revokes access privileges