Announcements

- Office hours are now posted in the course website
- Lecture 3 exercises will be out at 2pm today
- Both LE2 and LE3 will be due on monday

----------------------------------

Lecture 3: Relational Algebra


Key reminder:

   A key is a minimal set of attributes such that no two tuples can
   have the same value for the key

   --> the attributes uniquely identify the tuple
   --> they are minimal (no subset of them is unique)

Each relations has at least one key, but there can be multiple keys.

Example:

StudentsInClass(RIN, Email, Name, Address, DateofBirth)

Key: RIN
Key: Email


Relational Algebra
----------------------------

Input: a database -> a set of relations
each relation -> schema and a SET of tuples


A set of primitive operations
Input: a set of relations
Output: a single relation


1. SELECTION  (sigma_C (R)) or (SELECT_C(R))

Input: a relation R
       a Boolean condition C over the attributes of R
Output:  a new relation with the same schema as R
         but only the tuples that satisfy the condition C

2. PROJECTION ( pi_(A1,..,An) (R)) or (PROJECT_(A1,...,An) (R)

Input: a relation R
       a subset A1,...,An of attributes in R
Output:  a new relation with the same tuples in R
         with a schema containing only A1,...,An

3. SET OPERATIONS

   Input: Two relations R and S with the same schema
          (same attributes with the same name)

(3a) UNION  ( R UNION S)
   Output: A new relation with all the tuples in R OR in S

(3b) INTERSECTION  ( R INTERSECT S)

   Output: A new relation with all the tuples in R AND in S

(3c) SET DIFFERENCE  ( R - S)

   Output: A new relation with all the tuples in R that are not in S

Example Database
CarTypes(CarId, Make, Model, Year, PkgId, HP, Doors, is4WD, MPG,
         IsSelfD, isAWD)
Cars(License, State, CarID, Color, Mileage, VIN)
StudentCars(RIN, License, State)
FacultyCars(RIN, License, State)

CarTypes

CarId	Make	Model	Year	Pkgid	HP	Doors	Range	IsSelfD	isAWD
1	Kia	EV9	2024	Wind	215	4	304	No	Yes
2	Kia	EV9	2025	GT-Line	379	4	270	No	Yes
3	Rivian	R1T	2025	Adventure	533	4	420	No	Yes
4	MG	ZSEV	2025	SUV	140	5	231	No	No
5	MG	MG4	2025	SE	170	5	323	No	No
6	Tesla	S	2024	Luxe	1020	4	410	Yes	Yes
7	Tesla	3	2024	Performance	510	4	300	Yes	Yes
8	Tesla	3	2025	Standard	346	4	357	Yes	Yes

Cars
License	State	CarId	Color	Mileage	VIN
ASD423	NY	1	Red	10,000	123124124
RFW424	NY	4	Gray	20,000	124214576
FTH356	IL	1	Blue	5,000	654364522
EVT352	CA	3	Green	100,000	547756665
AGE423	NY	6	Red	20,000	745457457
EEV245	MA	2	White	50,000	457434674
EGY356	MA	2	Blue	2,000	657453463
DYH456	NY	7	White	5,000	364327345
JHY452	NY	7	Gray	1,000	346456346

StudentCars	 
RIN	License	State
R1	ASD423	NY
R2	RFW424	NY
R3	FTH356	IL
R4	DYH456	NY
R5	ASD423	NY
R6	JHY452	NY

FacultyCars
RIN	License	State
R4	DYH456	NY
R7	EVT352	CA
R8	AGE423	NY
R9	JHY452	NY
R10	FREEHV	GA