Relational Algebra and SQL

Relational Algebra

• Selection($\sigma$): selects a subset of rows from relation

• Projection($\pi$): Retains only wanted colums from relation

• Cross-product($\times$): Allows us to combine two relations

• Set-difference($-$): Tuples in one relation, but not in the other

• Union($\cup$): Tuples in one relation and/or in the other

Projection

Retains only attributes that are in the projection list

Also elimates the duplicate

Selection

$\sigma_{rating>8}(S)$

Operations can be combined $\pi_{sname,rating}(\sigma_{rating>8}(S_2))$

Union and Set-difference

These operations take two input relations, which must be union-compatible.

• Same number of fields

• Corresponding fields have the same type

Compound Operator: Intersection

Intersection retains rows that appear in both sets

Cross Product & Join

Cross product combines two relations:

• Each row of one input is merged with each row from another input

• Output is a new relation with all attributes of both inputs

The renaming operator: $ho(C(1\rightarrow sid1, 5\rightarrow sid2), S1\times S2)$

And Here is cross join:

SELECT * FROM Rel1, Rel2; 

Joins are compound operators involving cross product, selection and sometimes projection.

Most common type: natural join $R\Join S$, a cross product that matches rows where attributes that appear in both relations have equal values( and we omit duplicate attributes). And there’s a projection in the final output which means duplicate attributes will be eliminated.

Condition Join(or theta-join) is a cross product with a condition.

$R\Join_C S= \sigma_C(R\times S)$

Equi-Join is a special case of condition join where condition $C$ only contains equalities.

Not same as natural join.

Screenshot 2023-03-27 at 4.27.53 PM.png

Notes:

• Write Department $\Join$ Sale $\Join$ Item instead of Department $\Join$ Item $\Join$ Sale. The Department and Item do not share the same attributes.

• Find the employees whose salary is less than half that of their managers.

  $ho(Employee\rightarrow TeamMember)$

  $ho(Employee\rightarrow Boss)$

  $TeamMember \Join_{TeamMember. BossID = Boss.EmployeeID}Boss$

  $\sigma_{TeamMember.Salary<0.5\times Boss.Salary}(TeamMember \Join_{TeamMember. BossID = Boss.EmployeeID}Boss)$

SQL

Creating Table

CREATE TABLE Customer (
	CustomerID smallint auto_increment,
	CustFirstName varchar(100),
	CustMiddleName varchar(100),
	CustLastName varchar(100) NOT NULL,
	BussinessName varchar(200),
	CustType enum('Personal', 'Company') NOT NULL,
	PRIMARY KEY (CustomerID)
);

Inset Data

INSERT INTO Customer(CustFirstName, CustLastName, CustType) VALUES ("Peter", "Smith", "Personal");

INSERT INTO Customer(DEFAULT, "James", NULL, "Jones", "JJ Enterprises", "Company");

MySQL doesn’t discard duplicates. To remove them use DISTINCT in front of the projection list

LIKE clause:

LIKE "REG_EXP"

% represents zero, one or multiple charactres.

_ represents a single character.

All of the aggregate functions except for COUNT(*) ignore null values and return null if all values are null. COUNT(*) counts the number of records.

Limit and Offset

Limit number limits the output size

Offset number skips ‘number’ records

Join tables together

SELECT * FROM Rel1, Rel2; 

SELECT * FROM Department NATRUAL JOIN Sale

The natural join automatically eliminates duplicate columns, while the inner join does not.

Outer join: include records that don’t match the join from the other table

Set Operations

UNION shows all rows returned from teh queries(or tables)

INTESECT shows rows that are common in the queries(or the tables)

UNION/INTESECT ALL if you want duplicate rows in the results you need to use ALL keyword.

Union compatible are essentially two tables or expressions that have the same number of columns and corresponding columns are of same type.

Sub-Query Comparison Operators

IN / NOT IN Used to test whether the attribute is IN/ NOT IN the subquery list

ANY True if any value returned meets the condition

ALL True if all values returned meet the condition

EXISTS True if the subquery returns one or more records

SELECT * FROM Buyer
WHERE BuyerID IN (SELECT BuyerID FROM offer WHERE ArtefactID = 1)

JOIN is a bit faster than nesting queries.

SELECT * FROM Buyer WHERE EXISTS
(SELECT * FROM Offer WHERE Buyer.BuyerID = Offer.BuyerID AND ArtefactID = 1)

Check the records in the Buyer table one by one.

ALL must satisfy all inner conditions

SELECT empno sal
FROM emp
WHERE sal > ALL(200,300,400)

More on INSERT

Inserting records from a table

INSERT INTO NewEmployee
	SELECT * FROM Employee

Multiple record inserts:

INSERT INTO Employee VALUES
	(DEFAULT, "A", "A's Addr", "2012-02-02", NULL, "S"),
	(DEFAULT, "B", "B's Addr", "2012-02-02", NULL, "S"),
	(DEFAULT, "C", "C's Addr", "2012-02-02", NULL, "S");

INSERT INTO Employee
	(Name, Address, DateHired, EmployeeType)
	VALUES
		("D", "D's Addr", "2012-02-02", "C"),
		("E", "E's Addr", "2012-02-02", "C"),
		("F", "F's Addr", "2012-02-02", "C");

The UPDATE Statement

Changes existing data in tables

Order of statement is important

Specifying a WHERE clause is important

UPDATE Hourly
	SET HourlyRate = HourlyRate * 1.10;

CASE

UPDATE Salaried
SET AnnualSalary = 
	CASE
		WHEN AnnualSalary <= 100000
		THEN AnnualSalary * 1.05
		ELSE AnnualSalary * 1.10
	END;

Views

CREATE VIEW nameofview AS validsqlstateemnt

More DDL Commands

ALTER Allows us to add or remove attributes (columns) from a relation (table)

ALTER TABLE TableName ADD AttributeName AttributeType
ALTER TABLE TableName DROP AttributeName

RENAME

RENAME TABLE CurrentTableName TO NewTableName

TRUNCATE

Same as DELETE * FROM table

Faster but cannot ROLL BACK a TRUNCATE command

DROP

Porentially dangerous

Kills a relation - removes the data, removes the relation

DROP TABLE TableName