{ "cells": [ { "cell_type": "markdown", "id": "7bbdd383-ab43-4983-a6d1-22832b2dcfa7", "metadata": {}, "source": [ "## Lecture 4 - Relational Algebra and Normalization\n", "\n", "### Announcements\n", "\n", "1. Lecture Exercise 4 due at **midnight** today.\n", "2. Lecture Exercise 5 is out later today, due on monday at 2pm.\n", "3. Homework \\#1 due on **Monday 9/14**\n", "\n", "### Today's topics\n", "\n", "- Relational Algebra, recap and natural join\n", "- Functional Dependencies\n" ] }, { "cell_type": "markdown", "id": "8e09c53f-5964-46e1-a989-61ce360de0e8", "metadata": {}, "source": [ "### Relational Algebra\n", "\n", "- SELECT_C (R)) \n", "- PROJECT_X(R) \n", "- R UNION S\n", "- R INTERSECT S\n", "- R - S\n", "- R x S\n", "- R join_(C) S = SELECT_(C) (R x S)\n", "\n", "**Note:**\n", "\n", "- Cartesian product and Theta-join (or join) requires the relations R and S to have no attributes in common. If there are attributes with the same name, they must be renamed. Otherwise, if there are attributes in R and S with the same name, the join conditions are ambiguous and the operation is not defined.\n", "\n", "- The result of R x S and R join S, is a relation with the schema containing all attributes in R and all attributes in S.\n", "\n", "**Natural Join:** In natural join, the input relations R and S may have some attributes in common. \n", " \n", "Suppose attributes A1,...,An are all the attributes common to R and S. Suppose X the remaining set of attributes in R (except for As) and Y is the remaining set of attributes in S.\n", " \n", "We define the natural join R * S as follows.\n", "\n", " 1. Rename all common attributes A1,...,An in S as B1,...,Bn, and leave the remaining attributes the same name.\n", "\n", " Sprime(B1,...,Bn,Y) = S(A1,...,An,Y)\n", "\n", " 2. Compute a join on the equality of all the common attributes:\n", "\n", " T = R join_(A1=B1 and A2=B2 and ... and An=Bn) Sprime\n", "\n", " 3. Remove all the common attributes so that we do not repeat them twice:\n", "\n", " Result = R * S = Project_(X,Y,A1,...,An) (T)\n", "\n", "So, the natural join R*S is an equality join of all the common attributes and the resulting schema has all attributes from R and S, but does not repeat the common attributes. " ] }, { "cell_type": "markdown", "id": "ca3fe201-fadb-4a3d-bacf-8eb377c2ed05", "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, parkingLot)\n", "- FacultyCars(RIN, License, State, parkingLot)\n", "- Faculty(RIN, Name, Email)\n", "- Students(RIN, Name, Email)\n", "---\n", "\n", "Find the RIN, name of all students who own a 2026 Red Kia, registered in NY and parks in 'GLot'.\n", "\n", "R1(carid1) = project_(carid) (select_(make=kia and year=2026) (CarTypes))) \n", "\n", "R2(l1,s1) = project_(license,state) (select_(color=red) (Cars)) join_(carid=carid1) R1) \n", "\n", "R3 = select_(parkinglot=Glot and state='NY') StudentCars \n", "\n", "R4(rin1) = project_(RIN) (R2 join_(license=l1 and state-s1) R3)\n", " \n", "Result = project_(rin, email) (R4 join_(rin=rin1) Students)\n", "\n", "---\n", "\n", "Alternate solution\n", "\n", "R1 = CarTypes * Cars * StudentCars * Students\n", "\n", "R2 = select_(parkinglot=Glot and state='NY' and make=kia and year=2026 and color=red) (R1)\n", "\n", "Result = project_(rin, email) (R2)\n", " \n", "---\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, parkingLot)\n", "- FacultyCars(RIN, License, State, parkingLot)\n", "- Faculty(RIN, Name, Email)\n", "- Students(RIN, Name, Email)\n", "---\n", "\n", "Find all parkinglots that have at least two cars of the same make from 2026 and one of the cars is owned by a faculty and the other one by a student.\n", "\n", "R1 = project_(license,state,make) (Cars * select_(make=2026) (CarTypes))\n", "\n", "R2 = project_(make, parkinglot) (R1 * StudentCars) /*student cars \n", "\n", "R3 = project_(make, parkinglot) (R1 * FacultyCars) /*faculty cars \n", "\n", "Result = project_(parkinglot) (R2 intersect R3)\n", "\n", "Result = project_(parkinglot) (R2 * R3)\n", "\n", "---\n", "\n", "Find the RIN, name of all students who own two different cars of the same make.\n", "\n", "R1 = project_(make, RIN, license, state) (Cars * CarTypes * StudentCars) \n", "\n", "R2(m1, r1, l1, s1) = R1\n", "\n", "R3(m2, r2, l2, s2) = R1\n", "\n", "R4 = R1 join_(make=m1 and rin=r1 and (license<>l1 or state<>s1)) R2 /* students with at least two cars\n", "\n", "R5 = R4 join_(make=m2 and rin=r2 and (license<>l2 or state<>s2) and (l1<>l2 or s1<>s2)) R3\n", "\n", "Studentwithtwoormorecars = Project_(rin, name) (R4 * Students) \n", "\n", "Studentwiththreeormorecars = Project_(rin, name) (R5 * Students) \n", "\n", "Studentwithexactlytwocars = Studentwithtwoormorecars - Studentwiththreeormorecars\n", "\n", "\n", "---\n", "\n", "Find the license and state of all Rivians on campus that park in somewhere other than 'GLot'. \n", "\n", "Find all Rivians - (All Rivians that park in GLot)\n", " \n", "---\n", "\n", "Find the Faculty, who has a car in every parkingLot in the database.\n", "\n", "Find where faculty parks in \n", "\n", "R1 = project_(RIN, parkingLot) (FacultyCars)\n", "\n", "Find all places all faculty can park!\n", "\n", "R2 = project_(RIN) (Faculty) x ((project_(parkinglot) FacultyCars) union (project_(parkinglot) StudentCars))\n", "\n", "All faculty who do not park in every lot!\n", "\n", "R3 = R2 - R1 \n", "\n", "Result is the faculty who park in every lot!\n", "\n", "Result = project_(RIN) (Faculty) - project(RIN) (R3)\n", "\n", "rin parkingLot \n", "1 A \n", "2 B \n", "3 G \n", "4 A \n", "4 B \n", "4 G \n", "5 B \n", "5 G \n", "\n", "\n", "R2: \n", "\n", "rin parkingLot \n", "1 A \n", "1 B \n", "1 G \n", "2 A \n", "2 B \n", "2 G \n", "3 A \n", "3 B \n", "3 G \n", "4 A \n", "4 B \n", "4 G \n", "5 A \n", "5 B \n", "5 G \n", " \n", "\n", "R3:\n", "\n", "rin parkingLot \n", "1 A \n", "1 B \n", "2 G \n", "3 A \n", "3 B \n", "5 A \n", " " ] }, { "cell_type": "markdown", "id": "5d801401-8eb4-4e34-91eb-f7eaa929637b", "metadata": {}, "source": [ "### Normalization\n", "\n", "\n", "\n", "**Functional Dependency (fd):** An fd is an expression of the form X -> Y for a relation R, where X and Y are sets of attributes where\n", "\n", "X-> Y means if two tuples in R have the same values for X, then they must have the same values for Y.\n", "\n", "MusicGroup(Group, Artist, Genre, DateFounded, DateJoined, DateofBirth) \n", "Group -> Genre, DateFounded \n", "\n", "Artist -> DateofBirth \n", "\n", "Group Artist -> DateJoined\n", "\n", "Group Artist -> Genre DateJoined\n", "\n", "Artist -> Artist\n", "\n", "Artist DateofBirth -> DateofBirth\n", "\n", "Artist Group -> DateFounded DateofBirth\n", "\n", "Artist Group -> Artist Group Genre DateFounded DateofBirth DateJoined\n", " \n", "Assumes a single genre for a group \n", "\n", "\n", "- **Key**: Given a relation R, a key is the smallest set of attributes X such that X->Y is true for R where Y is the set of all attributes in R. \n", " \n", "---\n", " \n", "Students(RIN, Name, FirstMajor, Class, RCS)\n", "\n", "F = {RIN-> Name FirstMajor Class RCS, RCS->RIN, RCS-> Name FirstMajor Class RIN}\n", "---\n", " \n", "StudentHobbies(RIN, Hobby)\n", "\n", "F = { } \n", "\n", "--- \n", " \n", "CatalogClasses(DeptCode, CourseCode, CourseName, NumCredits, OfferedWhen)\n", "\n", "F = { DeptCode CourseCode -> CourseName NumCredits }\n", " \n", "--- \n", " \n", "Books(ISBN, Title, EditionNo, Author, Publisher, YearPublished)\n", "\n", "F = {ISBN -> Title EditionNo Publisher YearPublished} \n", " \n", "---\n", " \n", "BirdSighting(birdername, snumber, birdname, latitude, longitude, recorded_date, description) \n", "\n", "F = {birdername snumber -> birdname latitude longitude recorded_date description, recorded_date -> birdername snumber } \n", " \n" ] }, { "cell_type": "markdown", "id": "acd2646e-ddf1-451c-a2ca-d6fa8adbebda", "metadata": {}, "source": [ "### Functional Dependency Inference \n", "\n", "X-> Y means if two tuples in R have the same values for X, then they must have the same values for Y.\n", "\n", "1. Trivial fd: If $$Y \\subseteq X, then X\\rightarrow Y$$\n", "\n", "(always true for all relations)\n", "\n", "2. Transivitity:\n", "\n", "If X->Y and Y->Z, then X->Z.\n", "\n", "3. Decomposition\n", "\n", "If X->YZ then X->Y and X->Z\n", "\n", "Group Artist -> Genre DateJoined\n", " \n", "Group Artist -> Genre \n", "Group Artist -> DateJoined\n", "\n", "4. Combining\n", "\n", "If X->Y and X->Z, then X->YZ\n", "\n", "5. Augmentation \n", "\n", "If X->Y then XZ->YZ\n", "\n", "Given a set F of functional dependences, then F+ is the closure of F, the set of all functional dependencies implied by F. " ] }, { "cell_type": "code", "execution_count": null, "id": "dfee29ec-d5d8-424b-bdcd-5fbce20233b2", "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 }