{
"cells": [
{
"cell_type": "markdown",
"id": "d4a49d54-fe44-461a-aadc-e4e43d6743c8",
"metadata": {},
"source": [
"## Announcements\n",
"\n",
"- Lecture exercises 2 and 3 to be graded soon!\n",
"- Lecture exercise 4 to be released later today (or tomorrow morning)\n",
"- Hw#1 to be released soon, due next week "
]
},
{
"cell_type": "markdown",
"id": "0e56820a-033b-4467-a487-20623c2e4bb0",
"metadata": {},
"source": [
"# Relational algebra operators (recap) \n",
"\n",
"## Basic operators\n",
"\n",
"- Selection: $\\sigma_{C}(R)$ (or SELECT_C ( R ) )\n",
"- Projection: $\\Pi_{A_1,\\ldots,A_n} (R)$ (or PROJECT_C ( R ) )\n",
"- Rename: X(A1,A2,..,An) = Y\n",
"- Set union: $R \\cup S$ or (or R UNION S)\n",
"- Set difference: $R - S$\n",
"- Cartesian Product: $R \\times S$\n",
"\n",
"## Derived operators\n",
"- Set intersection: $R \\cap S$ or (or R INTERSECT S)\n",
" $$\n",
" R \\,\\cap\\, S = (R\\,\\cup\\,S)- ((R-S)\\,\\cup\\,(S-R))\n",
" $$\n",
"- Theta_join: $R \\bowtie_C S$ (or R JOIN_C S)\n",
"\n",
"$$R \\bowtie_C S = \\sigma_{C} (R \\times S)$$\n",
"\n",
"i.e. R join_C S = select_C (R x S)\n",
"\n",
"- Natural Join $R \\bowtie S$, , R JOIN S, or R * S\n",
"\n",
" Join R and S on the equality of the attributes in common\n",
"\n",
" If R(A,B) and S(B,C) then T=R*S has schema T(A,B,C)\n",
" and it is computed as follows:\n",
" S1(B1,C) = S\n",
" T = Project_(A,B,C) ( R join_(B=B1) S1 )\n",
"\n",
" If R(A,B) and S(A,B,C) then T=R*S has schema T(A,B,C)\n",
" and it is computed as follows:\n",
" S1(A1,B1,C) = S\n",
" T = Project_(A,B,C) ( R join_(A=A1 and B=B1) S1 )\n",
"\n",
"**Note:** \n",
"\n",
"1. Set operations are only defined if the input relations have the same schema\n",
"2. Cartesian product requires the input relations to have completely different schema (i.e. no attributes in common)\n",
"3. A join condition C should only contain comparisons of attributes from the two relations, otherwise it is a selection condition"
]
},
{
"cell_type": "code",
"execution_count": 4,
"id": "c3f4e4c5-43b5-4d7a-be5d-68dcfe755b86",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"[[1, 2, 'b', 'c', 'd'], [1, 2, 'e', 'f', 'g'], [1, 2, 'h', 'e', 'f'], [3, 4, 'b', 'c', 'd'], [3, 4, 'e', 'f', 'g'], [3, 4, 'h', 'e', 'f'], [5, 6, 'b', 'c', 'd'], [5, 6, 'e', 'f', 'g'], [5, 6, 'h', 'e', 'f'], [7, 8, 'b', 'c', 'd'], [7, 8, 'e', 'f', 'g'], [7, 8, 'h', 'e', 'f']]\n"
]
}
],
"source": [
"r = [ [1,2], [3,4], [5,6], [7,8] ]\n",
"s = [ ['b','c','d'], ['e','f','g'], ['h','e','f' ]]\n",
"\n",
"cart = []\n",
"for val1 in r:\n",
" for val2 in s:\n",
" cart += [val1+val2]\n",
"\n",
"print(cart) "
]
},
{
"cell_type": "code",
"execution_count": 10,
"id": "b240ce5b-8d4a-4e10-ba53-464967a3cc81",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"R\n",
"['asd423', 'ny', 1, 'Red']\n",
"['rfw424', 'ny', 4, 'gray']\n",
"S\n",
"['r1', 'asd423', 'ny', 1, 'Red']\n",
"['r2', 'asd423', 'ny', 1, 'Red']\n",
"['r3', 'rfw424', 'ny', 4, 'gray']\n",
"RxS\n",
"['asd423', 'ny', 1, 'Red', 'r1', 'asd423', 'ny', 1, 'Red']\n",
"['asd423', 'ny', 1, 'Red', 'r2', 'asd423', 'ny', 1, 'Red']\n",
"['asd423', 'ny', 1, 'Red', 'r3', 'rfw424', 'ny', 4, 'gray']\n",
"['rfw424', 'ny', 4, 'gray', 'r1', 'asd423', 'ny', 1, 'Red']\n",
"['rfw424', 'ny', 4, 'gray', 'r2', 'asd423', 'ny', 1, 'Red']\n",
"['rfw424', 'ny', 4, 'gray', 'r3', 'rfw424', 'ny', 4, 'gray']\n"
]
}
],
"source": [
"##cars\n",
"r = [ ['asd423','ny',1,'Red'],['rfw424','ny',4,'gray']]\n",
"##studentcars\n",
"s = [ ['r1','asd423','ny',1,'Red'], ['r2','asd423','ny',1,'Red'],['r3','rfw424','ny',4,'gray']]\n",
"\n",
"cart = []\n",
"for val1 in r:\n",
" for val2 in s:\n",
" cart += [val1+val2]\n",
"\n",
"print('R')\n",
"for val in r:\n",
" print(val)\n",
"print('S')\n",
"for val in s:\n",
" print(val)\n",
"print('RxS') \n",
"for val in cart:\n",
" print(val)"
]
},
{
"cell_type": "markdown",
"id": "16b9e3d4-2399-46b3-ab1a-bfdb8b3caa1c",
"metadata": {},
"source": [
"## Example Database\n",
"\n",
"CarTypes(CarId, Make, Model, Year, PkgId, HP, Doors, is4WD, MPG,\n",
" IsSelfD, isAWD) \n",
"Cars(License, State, CarID, Color, Mileage, VIN) \n",
"StudentCars(RIN, License, State) \n",
"FacultyCars(RIN, License, State) \n",
"\n",
"Key of each relation is underlined\n"
]
},
{
"cell_type": "markdown",
"id": "ddf630a9-4348-4fd6-abb8-2dcbc0922842",
"metadata": {},
"source": [
"### Example Queries\n",
"\n",
"1. Return the RIN of all students who own a red car that a faculty also owns \n",
"2. Return the make of all cars driven by students\n",
"3. Return the make of all cars on campus\n",
"4. Return the make of all cars that no on campus owns\n",
"5. Find the RIN of all students who own a Kia with at least 20,000 miles and is not red\n",
"6. Find the RIN of all faculty or students who own a Kia\n",
"7. Find the state(s) in which a Tesla owned by someone on campus is registered\n",
"8. Find cars owned by two different students"
]
},
{
"cell_type": "markdown",
"id": "31936d11-084e-4c69-b7f0-9d9de56ba955",
"metadata": {},
"source": [
"2. Return the make of all cars driven by students\n",
"\n",
"R1(RIN, L1, S1) = StudentCars \n",
"R2(L2,S2, CarId2) = Project_(License, State, CarId) Cars \n",
"R3 = R1 x R2 \n",
"R4 = SELECT_(L1=L2 and S1=S2) (R3) \n",
"R5 = R4 x CarTypes \n",
"R6 = SELECT_(CarId2=CarId) (R5) \n",
"Result = Project_(Make) (R6) \n",
"\n",
"Alternate solution:\n",
"\n",
"R1(RIN, L1, S1) = StudentCars \n",
"R2(L2,S2, CarId2) = Project_(License, State, CarId) Cars \n",
"R4 = R1 JOIN_(L1=L2 and S1=S2) R2 \n",
"R6 = R4 JOIN_(CarId2=CarId) CarTypes \n",
"\n",
"Result = Project_(Make) (R6)\n",
"\n",
"Alternate solution\n",
"\n",
"Result = Project_(Make) (CarTypes * Cars * StudentCars)"
]
},
{
"cell_type": "markdown",
"id": "0fc067e8-72b1-4a32-bb3a-8cfd614cabc4",
"metadata": {},
"source": [
"**Join** is an operation between intersection and Cartesian products)!\n",
"\n",
"Given: R(A,B) and S(A,B)\n",
"\n",
"S1(A1,B1) = S\n",
"R1 = R join_(A=A1 and B=B1) S1\n",
"Result = Project_(A,B) (R1) #identical to R intersect S\n",
"\n",
"\n",
"Given R(A,B) and T(C,D)\n",
"\n",
"Result = R join_(true) T #identical to Cartesian product, R x T"
]
},
{
"cell_type": "markdown",
"id": "ddce8d4c-856d-4e5e-be9f-978c3625cc4d",
"metadata": {},
"source": [
"3. Return the make of all cars on campus\n",
"\n",
"R1(L1,S1) = (Project_(License, State) StudentCars) union (Project_(License, State) FacultyCars) \n",
"R1(L1,S1) = Project_(License, State) (StudentCars union FacultyCars) \n",
"R2(CarId1) = Project_(CarId) ( R1 join_(L1= License and S1=State) Cars ) \n",
"Result = Project_(Make) R2 join_(CarId1=CarId) CarTypes\n",
"\n",
"Alternate solution\n",
"\n",
"R1 = (Project_(License, State) StudentCars) union (Project_(License, State) FacultyCars) \n",
"Result = Project_(Make) (R1 * Cars * CarTypes)"
]
},
{
"cell_type": "markdown",
"id": "7d6cf2c0-ed62-4c7b-a05c-9aceee6adb37",
"metadata": {},
"source": [
"1. Return the RIN of all students who own a red car that a faculty also owns \n",
"\n",
"#cars owned by faculty and student \n",
"R1 = Project_(License, State) (StudentCars) intersect Project_(License, State) (FacultyCars) \n",
"R2 = SELECT_(Color='red')(R1 * Cars) \n",
"R3 = Project_(RIN) (R2 * StudentCars) \n",
"\n",
"4. Return the make of all cars that no one on campus owns\n",
"\n",
"##All cars someone owns \n",
"R1 = Project_(License, State) (StudentCars) union Project_(License, State) (FacultyCars) \n",
"R2 = (Project_(License, State) Cars) - R1 ##cars no one owns \n",
"R2 = Cars - (R1*Cars) ##cars no one owns \n",
"Result = Project_(Make) = R2 * CarTypes \n",
"\n",
"5. Find the RIN of all students who own a Kia with at least 20,000 miles and is not red\n",
"\n",
"R1 = Select_(Make='Kia' and color <>'red' and mileage>=20000) (CarTypes * Cars * StudentCars) \n",
"Result = Project_(RIN) (R1) \n",
"\n",
"8. Find cars owned by at least two different students, return license, state\n",
"\n",
"R1(RIN1, L1, S1) = StudentCars \n",
"R2 = StudentCars join_(L1=License and S1=State and RIN1 <> RIN) R1 \n",
"Result = Project_(License, State) (R2) \n",
"\n",
"R1 c1 \n",
"R2 c1 \n",
"R3 c1 \n",
"R4 c2 \n",
"\n",
"R1 c1 R1 c1 \n",
"R1 c1 R2 c1 * \n",
"R1 c1 R3 c1 * \n",
"R1 c1 R4 c2 \n",
"R2 c1 R1 c1 * \n",
"R2 c1 R2 c1 \n",
"R2 c1 R3 c1 * \n",
"R2 c1 R4 c2 \n",
"R3 c1 \n",
"R4 c2 "
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "a50e1d38-8602-4ab7-8942-affb89679493",
"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
}