{ "cells": [ { "cell_type": "markdown", "id": "7bbdd383-ab43-4983-a6d1-22832b2dcfa7", "metadata": {}, "source": [ "## CSCI 4380 Database Systems \n", "## Lecture 2\n", "\n", "### Announcements\n", "\n", "1. Lecture Exercise 2 to be out at 4pm today on Submitty, due at 2pm on thursday\n", " - Please make sure you are set to receive email notifications on Submitty.\n", "\n", "2. All office hours are announced and posted on the website (https://www.cs.rpi.edu/~sibel/csci4380/fall2026/index.html)\n", "If location is known, it will be held. We are waiting to help you with the course material!\n", "\n", "3. 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", "- Recap of Relational Data Model\n", "- Relational Algebra\n", "\n" ] }, { "cell_type": "markdown", "id": "a4ba7660-e14c-4802-ad1d-cbbc977e97e4", "metadata": {}, "source": [ "## Relational Data Model\n", "\n", "- A **database** is a set of relations (relationals are also called tables).\n", "\n", "- A **relation (or table)** has a name and a set of attributes (also called columns), each is attribute is drawn from a specific domains.\n", "\n", " - Students(RIN, Name, Class) or Students(Name, RIN, Class)\n", "\n", "- **RULE (1st Normal Form):** Attributes in the relational data model can only have simple values (no sets or lists).\n", "\n", "- The relation data model (or schema) is the list of attributes (and their domains) for each relation.\n", "\n", "- A **relation instance** is a set of tuples such that each tuple has a value for all the attributes of that relation.\n", "\n", "**Students**\n", "\n", "|RIN|Name|Major| \n", "|---|----|-----|\n", "|1234|River| CSCI|\n", "|4567|Mountain|EARTH| \n", "\n", "or\n", "\n", "|RIN|Name|Major| \n", "|---|----|-----|\n", "|4567|Mountain|EARTH| \n", "|1234|River| CSCI|\n", "|4567|Mountain|EARTH| \n", "|4567|Mountain|EARTH| \n", " \n", " \n", "- **Key:** Given a relation R, a **key** is the smallest set of attributes\n", "such that no two tuples can have the same values for the key.\n", "\n", " - A relation can have many keys\n", " - First rule of the key is that it uniquely identifies a tuple, i.e. no two different tuples can have the same values for the key\n", " - Second rule of the keys is that they need to be minimal, (i.e. you cannot remove any attributes and satisfy rule 1)\n", " - Note that keys don't all have to be the same number of attributes, but they need to be minimal \n", "\n", "- We define keys based on the expected meaning of the attributes in that relation and the real world object that they represent.\n", "\n", "- All relations have at least one key.\n", "\n", "#### Examples\n", "---\n", "\n", "Students(RIN, Name, FirstMajor, Class, RCS)\n", "\n", "- RIN is a key (we expect no two students can have the same key)\n", "- RCS is also a key (we expect no two student can have the same email)\n", "\n", "Students\n", "\n", "RIN | Name | Class | FirstMajor | RCS\n", "1234 | River | Senior | CSCI | rpi1\n", "1234 | River | Senior | CSCI | rpi1\n", "\n", "RCS, Class, not a key, it is unique but not minimal\n", "\n", "---\n", "\n", "StudentHobbies(RIN, Hobby)\n", "\n", "Students can have multiple hobbies\n", "and a hobby can be shared by multiple students\n", "\n", "- Key: RIN, Hobby\n", "\n", "---\n", "\n", "CatalogClasses(DeptCode, CourseCode, CourseName, NumCredits, OfferedWhen)\n", "\n", "Ex: CSCI | 4380 | Database Systems | 4 | Every semester\n", "\n", "Keys: \n", "\n", "- DeptCode, CourseCode\n", "\n", "Not a key: DeptCode, CourseName (because of 4xxx, 6xxx versions of the same course)\n", "\n", "---\n", "\n", "Books2(ISBN, Title, EditionNo, Author, Publisher, YearPublished)\n", "\n", "Books can have multiple authors, but a single title, publisher for each\n", "edition. Each edition is published in a specific year.\n", "\n", "\n", "1234 Database Systems 2 JU PrenticeHall 2009\n", "1234 Database Systems 2 JW PrenticeHall 2009\n", " \n", "Keys: \n", "\n", "- ISBN, Author\n", "- Title, EditionNo, Author, Publisher (assuming a publisher will not publish two books with the same title, author and edition no, but it is possible that two different publishers may publish a book with the same title)\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", "#### Unary Operations\n", "\n", "- **SELECTION**: Given a relation R and a Boolean condition C with attributes in R, $$\\sigma_C (R)$$ (OR SELECT_C (R)) is the set of all attributes in R that satisfy the 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", "---\n", "- Find all blue cars in the database\n", "\n", "SELECT_(Color = 'Blue') (Cars)\n", "\n", "$$\\sigma_{Color=Blue} (Cars)$$\n", "\n", "---\n", "\n", "- Find all cars in the database that are either registered in NY or have at least 5000 miles.\n", "\n", "$$\\sigma_{State=NY\\mbox{ or } Mileage>=5,000} (Cars)$$\n", "\n", "---\n", "\n", "- Find all faculty cars registered in NY.\n", "\n", "SELECT_(State= NY) (FacultyCars)\n", "\n", "---\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", " - Projection will return all the tuples but a subset of the attributes, and may remove duplicates\n", " if any are created.\n", "\n", "---\n", "\n", "- Find all makes of car types in the database.\n", "\n", "PROJECT_(Make) (CarTypes)\n", "\n", "|Make|\n", "|----|\n", "|Kia|\n", "|MG|\n", "|Rivian| \n", "|Tesla|\n", "\n", "---\n", "\n", "- Find all faculty who have a car.\n", "\n", "PROJECT_(RIN) (FacultyCars)\n", "\n", "- Find all states that a red car is from.\n", "\n", "PROJECT_(State) ( SELECT_(Color=Red)(Cars) )\n", "\n", "Alternate solution:\n", " \n", "- R1 = SELECT_(Color=Red)(Cars) All red cars in the db\n", "- Result = PROJECT_(State) (R1) State for all red cars\n", "--- \n", "\n", "- **Rename:** Rename all attributes in a relation\n", "\n", "R1 = Cars ** R1 is an alias for Cars\n", "R2(L, S, CarId, Color, Mileage, VIN2) = Cars\n" ] }, { "cell_type": "markdown", "id": "ca3fe201-fadb-4a3d-bacf-8eb377c2ed05", "metadata": {}, "source": [ "#### Binary Operations\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", "- In our db, studentcars and facultycars are set compatible.\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. 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", "\n", "- Find the license and state of all cars that are owned by both a faculty and a student.\n", " - Find the license and state of all cars that are owned by faculty.\n", " R1 = Project_(License, state) (FacultyCars)\n", "\n", " - Find the license and state of all cars that are owned by students.\n", " R2 = Project_(License, state) (StudentCars)\n", " Result = R1 intersect R2\n", "\n", "\n", "How about: Ralt = FacultyCars intersect StudentCars\n", "\n", "No this on returns all faculty who are also students and have the same\n", "car registered for both.\n", "\n", "---\n", "\n", "- Find the RIN of all students and faculty who own a car in the database\n", "\n", " PROJECT_(RIN) (StudentCars) union PROJECT_(RIN) (FacultyCars)\n", "\n", "- Find the id of all car types such that there is no car for that type in the database\n", "\n", "(all car types in the db) - (all car types that in the cars relation)\n" ] }, { "cell_type": "code", "execution_count": null, "id": "d79027ba-9ca9-433f-9d8f-fef93969e655", "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 }