{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "fa65e521-713f-4c6b-8e27-17d2b489b0cf",
   "metadata": {},
   "source": [
    "## Lecture 8 - Entity-Relationship Models\n",
    "\n",
    "### Announcements\n",
    "\n",
    "1. Exam #1 is October 1st (next thursday), at 2pm.\n",
    "   - Open book and notes, but nothing electronic.\n",
    "   - Covers everything we have done.\n",
    "   - If you need accommodation, this is your last chance to contact me.       \n",
    "2. Lecture Exercise 8 is out later today, due on monday at 2pm.\n",
    "3. Homework #2 is due this friday at midnight.\n",
    "\n",
    "### Today's topics\n",
    "\n",
    "- Entity-Relationship Modeling    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "495a8079-31b5-4c96-9402-7b1e458fa233",
   "metadata": {},
   "source": [
    "### Entity-Relationship Models\n",
    "\n",
    "- Entities are the main class of objects that we will store information about\n",
    "  - Each entity has a name\n",
    "  - A set of attributes, the information about that entity where\n",
    "    - Each attribute should be atomic (i.e. simple valued)\n",
    "    - Each attribute should be about that entity directly\n",
    "  - Each entity should have a key, a set of attributes that imply all the other attributes (so entities should be in BCNF)\n",
    "\n",
    "- Relationships connect two or more entities.\n",
    "\n",
    "\n",
    "\n",
    "![School Example](lecture8_graphs/SchoolER.png)\n",
    "\n",
    "![World Cup Soccer Example](lecture8_graphs/WorldCupER.png)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "366c8eb4-bdc1-4278-bc67-183987297467",
   "metadata": {},
   "source": [
    "### Converting ER Diagrams to Relational Data Model\n",
    "\n",
    "- Map entities to relations\n",
    "  - Key of the entity is a key to the relation\n",
    "  - All attributes of the entity is mapped to the relation \n",
    "\n",
    "- Map relationships\n",
    "  - One-to-one relationships\n",
    "    - Map the key of one of the entities as an attribute in the other\n",
    "  - One-to-many relationships\n",
    "    - Map the key of the entity on the many side as an attribute in the other (see course notes)\n",
    "  - Many-to-many relationships\n",
    "    - Create a new relations which has all the keys of the connecting entities and the combination of these keys is the key\n",
    "\n",
    "- Ternary relationship: depends on the cardinalities. See course notes\n",
    "\n",
    "---\n",
    "\n",
    "Tournaments(<u>Year</u>, StartDate, EndDate) \n",
    "\n",
    "Matches(<u>Id</u>, Type, Date, Location, TournamentYear, Team1CountryName, NumGoalsTeam1, Team2CountryName, NumGoalsTeam2)\n",
    "\n",
    "Teams(<u>CountryName</u>, Uniform1, Uniform2)\n",
    "\n",
    "Players(<u>Name</u>, Position, IsCaption, DoB)\n",
    "\n",
    "Goals(<u>Id</u>, TimeScored, IsPenalty, MatchId, PlayerName, ForTeamCountry)\n",
    "\n",
    "ParticipateIn(<u>TournamentYear, CountryName</u>)\n",
    "\n",
    "PlayedIn(<u>PlayerName, MatchId</u>, nummins)\n",
    "\n",
    "MemberOf(<u>TournamentYear,  PlayerName</u>, TeamCountry)\n",
    "\n",
    "MemberOf:   Tournament, Team, Player\n",
    "\n",
    "Tournament Player -> Team\n",
    "\n",
    "ScoredBy:\n",
    "\n",
    "Goal -> Player Team\n",
    "\n",
    "\n",
    "---\n",
    "Example of 1-1 relationship\n",
    "\n",
    "\n",
    "ChairOf: \n",
    "\n",
    "Faculty -> Deptforwhichtheyarechair\n",
    "\n",
    "Dept -> Facultywhosischair\n",
    "\n",
    "Can be mapped to either faculty or department, but it is better to map to department because for a given department, there is always a chair (so the corresponding attribute will always have a value)\n",
    "\n",
    "Faculty(...., deptiamchairof...\n",
    "dept(...., chairrin..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "62d41ad2-bc4a-4f97-abd6-9b47c1cb3a7b",
   "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
}