{ "cells": [ { "cell_type": "markdown", "id": "7bbdd383-ab43-4983-a6d1-22832b2dcfa7", "metadata": {}, "source": [ "## Lecture 3 - Relational Algebra\n", "\n", "### Announcements\n", "\n", "1. No class this monday.\n", "2. Lecture Exercises 3 and 4 to be out at 4pm today on Submitty, due at **midnight** on monday. \n", "3. Homework #1 is to be out later today or tomorrow, due on **Monday 9/14**\n", "4. Lectures are being recorded and linked to course website: https://www.cs.rpi.edu/~sibel/csci4380/fall2026/index.html\n", "5. Please continue to send me your accommodations by email. If you are waiting for accommodations, you can email me to let me know as well.\n", "\n", "### Today's topics\n", "\n", "- Relational Algebra\n", "- Functional Dependencies\n" ] }, { "cell_type": "markdown", "id": "a3a7fdc6-63ef-493e-8394-ca96b33b0afc", "metadata": {}, "source": [ "#### Example Database\n", "\n", "- CarTypes(CarId, Make, Model, Year, PkgId, HP, Doors, is4WD, MPG, IsSelfD, isAWD)\n", "- Cars(License, State, CarID, Color, Mileage, VIN)\n", "- StudentCars(RIN, License, State)\n", "- FacultyCars(RIN, License, State)\n", "\n", "\n", "**CarTypes**\n", " \n", "| CarId | Make | Model | Year | Pkgid | HP | Doors | Range | IsSelfD | isAWD |\n", "|-------|------|-------|------|-------|----|-------|-------|---------|-------|\n", "|1 | Kia | EV9 | 2024 | Wind | 215 | 4 | 304 | No | Yes|\n", "|2 | Kia | EV9 | 2025 | GT-Line | 379 | 4 | 270 | No | Yes|\n", "|3 | Rivian | R1T | 2025 | Adventure | 533 | 4 | 420 | No | Yes|\n", "|4 | MG | ZSEV | 2025 | SUV | 140 | 5 | 231 | No | No|\n", "|5 | MG | MG4 | 2025 | SE | 170 | 5 | 323 | No | No|\n", "|6 | Tesla | S | 2024 | Luxe | 1020 | 4 | 410 | Yes | Yes|\n", "|7 | Tesla | 3 | 2024 | Performance | 510 | 4 | 300 | Yes | Yes|\n", "|8 | Tesla | 3 | 2025 | Standard | 346 | 4 | 357 | Yes | Yes|\n", "\n", "**Cars**\n", "\n", "|License | State | CarId | Color | Mileage | VIN|\n", "|--------|-------|-------|-------|---------|----|\n", "|ASD423 | NY | 1 | Red | 10,000 | 123124124|\n", "|RFW424 | NY | 4 | Gray | 20,000 | 124214576|\n", "|FTH356 | IL | 1 | Blue | 5,000 | 654364522|\n", "|EVT352 | CA | 3 | Green | 100,000 | 547756665|\n", "|AGE423 | NY | 6 | Red | 20,000 | 745457457|\n", "|EEV245 | MA | 2 | White | 50,000 | 457434674|\n", "|EGY356 | MA | 2 | Blue | 2,000 | 657453463|\n", "|DYH456 | NY | 7 | White | 5,000 | 364327345|\n", "|JHY452 | NY | 7 | Gray | 1,000 | 346456346|\n", "\n", "\n", "**StudentCars**\n", "\n", "|RIN | License | State|\n", "|----|---------|------|\n", "|R1 | ASD423 | NY|\n", "|R2 | RFW424 | NY|\n", "|R3 | FTH356 | IL|\n", "|R4 | DYH456 | NY|\n", "|R5 | ASD423 | NY|\n", "|R6 | JHY452 | NY|\n", "\n", "\n", "\n", "**FacultyCars**\n", "\n", "|RIN | License | State|\n", "|----|---------|------|\n", "|R4 | DYH456 | NY|\n", "|R7 | EVT352 | CA|\n", "|R8 | AGE423 | NY|\n", "|R9 | JHY452 | NY|\n", "|R10 | FREEHV | GA|\n", " \n" ] }, { "cell_type": "markdown", "id": "8e09c53f-5964-46e1-a989-61ce360de0e8", "metadata": {}, "source": [ "### Relational Algebra\n", "\n", "Given a database containing a set of relations, relational algebra operations define new relations based on the existing ones.\n", "\n", "Each relational algebra operator takes as input one or two relations, and returns a new relation.\n", "\n", "- **SELECTION**: Given a relation R and a Boolean condition C containing attributes in R, selection is written by $$\\sigma_C (R)$$ (OR SELECT_C (R)) returns the set of all tuples in R that satisfy the condition C.\n", "\n", "SELECT_C (R) = {t | t is a tuple in R and t satistifies condition C}\n", "\n", " - Selection returns a relation with the same data model (schema), but a subset of the tuples in that relation.\n", "\n", "- **Projection:** Given a relation R and a subset X of attributes in R, the projection of R into X written as $$\\pi_X(R)$$ (or PROJECT_X(R) and returns a new relation that has all the tuples in R but the tuples have values for only the attributes X.\n", "\n", "PROJECT_X(R) = {t' | t is a tuple in R and t' contains only the attributes X of t}\n", " \n", " - Projection will return all the tuples but a subset of the attributes, and may remove duplicates if any are created.\n", "\n", "- **Rename:** Rename written as R1(...) = R, creates an alias for R and renames all attributes in R. All attributes in R must be listed in R1 with their name (changed or unchanged). \n", "\n", "R1 = Cars ** R1 is an alias for Cars\n", "\n", "R2(L, S, CarId, Color, Mileage, VIN2) = Cars\n", "\n", "- **Set compatibility**: Two relations R and S are set compatible if they have the same schema (same attributes and the same names)\n", "\n", "- **Set Union** Given two relations R and S that are set compatible, the union of R and S is the set of all tuples that are either in R or in S or in both. R UNION S or $$R\\cup S$$\n", "\n", "- **Set Intersection** Given two relations R and S that are set compatible, the intersection of R and S is the set of all tuples that are in R and in S. R INTERSECT S or $$R\\cap S$$\n", "\n", "- **Set Differences** Given two relations R and S that are set compatible, the set difference R-S is the set of all tuples in R that are **NOT** in S.\n", "\n", "\n", "$$R\\cap S = (R\\cup S) - ((R-S) \\cup (S-R))$$\n", "\n", "- **Cartesian Product** Given two relations R and S that have no attributes in common. The cartesian product RxS is a new relation such that\n", "\n", " - RxS has all the attributes in R and in S\n", " - RxS = { r.s | r is a tuple in R, and s is a tuple in S, and r.s is a tuple containing all the attributes values from R and S} \n", "\n", "R \n", "A B \n", "a1 b1 \n", "a1 b2 \n", "a2 b3 \n", "\n", "S\n", "\n", "C D \n", "c1 d1 \n", "c2 d2 \n", "\n", "Result = RxS\n", "\n", "A B C D \n", "a1 b1 c1 d1 \n", "a1 b1 c2 d2 \n", "a1 b2 c1 d1 \n", "a1 b2 c2 d2 \n", "a2 b3 c1 d1 \n", "a2 b3 c2 d2 \n", "\n", "-**JOIN** Given two relations R and S that have no attributes in common, and C is a join condition, the join of R and S (given by R JOIN_(C) S or $$R\\bowtie_C S$$ is given as following:\n", "\n", "R JOIN_(C) S = SELECT_C (R x S)\n", " \n", " - A join condition is a Boolean condition involving attributes from two different relations\n", "\n", "R(A,B)\n", "S(C,D)\n", "\n", "A=5 selection condition \n", "A=C join condition\n", "A=C and B =D join condition" ] }, { "cell_type": "markdown", "id": "ca3fe201-fadb-4a3d-bacf-8eb377c2ed05", "metadata": {}, "source": [ "- CarTypes(CarId, Make, Model, Year, PkgId, HP, Doors, is4WD, MPG, IsSelfD, isAWD)\n", "- Cars(License, State, CarID, Color, Mileage, VIN)\n", "- StudentCars(RIN, License, State)\n", "- FacultyCars(RIN, License, State)\n", "\n", "---\n", "\n", "- Find all cars that are not red, and return license and state.\n", "\n", "R1 = SELECT_(Color <> 'Red') (Cars) \n", "\n", "Result = PROJECT_(License, State) (R1)\n", "\n", "----\n", "\n", "- Find all students who do not own any cars registered in 'NY. Return their RIN.\n", "\n", "StudentCars\n", "\n", "RIN License State\n", "1 ABC NY\n", "2 CDE MD\n", "3 FGH CA\n", "3 HJK NY \n", "\n", "(All Students who own a car) - (Students who own a car from NY)\n", "\n", "R1 = SELECT_(state=NY) (StudentCars)\n", "\n", "Result = PROJECT_(RIN) (StudentCars) - PROJECT_(RIN) (R1)\n", "\n", "---\n", "\n", "1. Find the license and state of all cars owned by a faculty and a student both\n", "\n", "R1 = PROJECT_(License, State) (StudentCars)\n", "\n", "R2 = PROJECT_(License, State) (FacultyCars)\n", "\n", "Result = R1 intersect R2\n", "\n", "---\n", "\n", "2. ** Special challenge: exclude cases where the student and the faculty are the same (i.e. same RIN)**\n", "\n", "R1 = PROJECT_(License, State) (StudentCars)\n", "\n", "R2 = PROJECT_(License, State) (FacultyCars)\n", "\n", "R3 = PROJECT_(License, State) (StudentCars INTERSECT FacultyCars)\n", " \n", "Result = (R1 intersect R2) - R3\n", "\n", "StudentCars\n", "\n", "RIN License State \n", "1 ABC NY \n", "2 CDE MD \n", "3 FGH CA \n", "3 HJK NY \n", "\n", "FacultyCars \n", "2 CDE MD \n", "4 ABC NY \n", "\n", "---\n", "\n", "3. Find license and state of all cars with at least 50,000 mileage owned by a faculty\n", "\n", "(Cars with 50K mileage) intersect (Cars owned by faculty)\n", "\n", "R1 = PROJECT_(License, State) (Select_(Mileage>=50,000) Cars)\n", "\n", "R2 = PROJECT_(License, State) (FacultyCars)\n", "\n", "Result = R1 intersect R2\n", "\n", "---\n", "\n", "4. Find license and state of all cars that are not registered to a student\n", "\n", "R1 = PROJECT_(License, State) (Cars)\n", "\n", "R2 = R1 = PROJECT_(License, State) (StudentCars)\n", "\n", "Result = R1 - R2" ] }, { "cell_type": "markdown", "id": "84c4f766-4e58-425c-88aa-05e9f98122d2", "metadata": {}, "source": [ "---\n", "Cartesian product examples\n", "\n", "FC(RIN, L1, S1) = FacultyCars\n", "\n", "R1 = CarsxFC\n", "\n", "(R1 schema RIN, L1, S1, License, State, CarID, Color, Mileage, VIN)\n", "\n", "R2 = SELECT_(License=L1 and State=S1) (R1)\n", "\n", "- Find the RIN of all faculty who own a red car.\n", "\n", "Result = PROJECT_(RIN) = SELECT_(Color=Red) (R2)\n", "\n", "- Find the RIN of all faculty who do not own a car with less than 10K mileage\n", " \n", "R3 = PROJECT_(RIN) (SELECT_(Mileage<=10K) (R2))\n", "\n", "All faculty who own a car with less than 10K mileage\n", "\n", "Result = (PROJECT_(RIN) (FacultyCars) - R3\n", "\n", "---\n", "- Find RIN of all students who own a KIA.\n", "\n", "R1(C1) = PROJECT_(CarId) (SELECT_(Make='Kia') (CarTypes))\n", "\n", "R1 is all KIA models.\n", " \n", "R2 = CarsxR1\n", "\n", "R3(L1, S1) = PROJECT_(License, State) (SELECT_(CarId = C1) (R2))\n", "\n", "R3 is all cars that are a Kia model.\n", " \n", "R4 = R3xStudentsCars\n", "\n", "R5 = SELECT_(License=L1 and State=S1) (R4)\n", "\n", "R5 is all students who own a Kia.\n", "\n", "Result = PROJECT_(RIN) (R5)" ] }, { "cell_type": "markdown", "id": "339534a9-d315-45d2-9661-4cca2b83a0cf", "metadata": {}, "source": [ "Join examples\n", "\n", "---\n", "- Find RIN of all students who own a KIA.\n", "\n", "R1(C1) = PROJECT_(CarId) (SELECT_(Make='Kia') (CarTypes))\n", "\n", "R2 = Cars JOIN_(CarId = C1) R1\n", "\n", "R3(L1, S1) = PROJECT_(License, State) (R2)\n", "\n", "R4 = R3 JOIN_(L1=License and S1=State) StudentsCars\n", "\n", "Result = PROJECT_(RIN) (R4)\n", "\n", "---\n", "- Return RIN all faculty who own a Red car registered in NY.\n", "\n", "R1(RIN, L1, S1) = FacultyCars \n", "R2 = R1 JOIN_(L1=License and S1=State) Cars \n", "R3 = PROJECT_(RIN) = SELECT_(Color=Red AND State=NY) (R2) \n", " \n", "- Find all students who own a car made in 2026.\n", "\n", "R1(RIN, L1, S1) = StudentCars \n", "R2 = R1 join_(L1=License and S1=State) Cars \n", "R3(C1) = PROJECT_(CarId) ( SELECT_(Year=2026) = CarTypes) \n", "R4 = R3 join_(C1=CarId) R2 \n", "Result = Project_(RIN) (R4) \n", "\n", "- Find all car makes with at least two models in the database.\n", "\n", "R1 = PROJECT_(make, model) (CarTypes) \n", "R2(make2, model2) = R2 \n", "R3 = R1 join_(make=make2 and model<>model2) R2 \n", "Result = PROJECT_(make) (R3)" ] }, { "cell_type": "code", "execution_count": null, "id": "6d798f51-fb45-4ff5-88ba-ba7660a68aa0", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.13.5" } }, "nbformat": 4, "nbformat_minor": 5 }