{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f531e38f-9571-4945-871a-ee19c5063012",
   "metadata": {},
   "source": [
    "# Normalization\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98c1431a-90f5-41f1-937d-82f5c5e058bb",
   "metadata": {},
   "source": [
    "\n",
    "|Artist          | DJoin| DLeft| DOB |  Group                 | GFormed | Genre            |\n",
    "|--------------  | -----| -----| --- |  --------------------- | ------- |----------------  |\n",
    "|John Lennon     | 1960 | 1969 | 1940|  Beatles               | 1960    |British Invasion  |\n",
    "|Paul McCartney  | 1960 | 1970 | 1942|  Beatles               | 1960    |British Invasion  |\n",
    "|George Harrison | 1960 | 1970 | 1943|  Beatles               | 1960    |British Invasion  |\n",
    "|Ringo Starr     | 1962 | 1970 | 1940|  Beatles               | 1960    |British Invasion  |\n",
    "|Stuart Sutcliffe| 1960 | 1961 | 1940|  Beatles               | 1960    |British Invasion  |\n",
    "|Pete Best       | 1960 | 1962 | 1941|  Beatles               | 1960    |British Invasion  |\n",
    "|John Lennon     | 1960 | 1969 | 1940|  Beatles               | 1960    |Pop/Rock          |\n",
    "|Paul McCartney  | 1960 | 1970 | 1942|  Beatles               | 1960    |Pop/Rock          |\n",
    "|George Harrison | 1960 | 1970 | 1943|  Beatles               | 1960    |Pop/Rock          |\n",
    "|Ringo Starr     | 1962 | 1970 | 1940|  Beatles               | 1960    |Pop/Rock          |\n",
    "|Stuart Sutcliffe| 1960 | 1961 | 1940|  Beatles               | 1960    |Pop/Rock          |\n",
    "|Pete Best       | 1960 | 1962 | 1941|  Beatles               | 1960    |Pop/Rock          |\n",
    "|George Harrison | 1988 | 1992 | 1943|  The Traveling Wilburys| 1988    |Pop/Rock          |\n",
    "|Roy Orbison     | 1988 | 1992 | 1936|  The Traveling Wilburys| 1988    |Pop/Rock          |\n",
    "|Bob Dylan       | 1988 | 1992 | 1941|  The Traveling Wilburys| 1988    |Pop/Rock          |\n",
    "|Tom Petty       | 1988 | 1992 | 1950|  The Traveling Wilburys| 1988    |Pop/Rock          |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78ff5297-d305-4801-bd66-6cdba1ad199e",
   "metadata": {},
   "source": [
    "Key is a minimal set of attributes such that no two tuples can have the same value for the key.\n",
    "\n",
    "Key: Group, Artist, Genre\n",
    "\n",
    "1. Lots of repeated information, if you change it in one place, you need to change it everywhere\n",
    "2. Coupled information, I cannot enter a group without knowing at least one artist in it.\n",
    "3. If I delete someone from a group, I may loose other information about them like DOB of an artist."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "196a2885-38c4-4383-a558-b8ae261a5cc3",
   "metadata": {},
   "source": [
    "## Functional dependencies\n",
    "\n",
    "Given a relation R, a functional dependency (fd) is an expression of the form\n",
    "\n",
    "X -> Y\n",
    "\n",
    "where \n",
    "- X and Y are a set of attributes in R\n",
    "Means: whenever X->Y is true, then if two tuples have the same values for all attributes in X, then it must have the same value for all attributes in Y.\n",
    "\n",
    "Artist -> DOB  \n",
    "Group -> DFormed  \n",
    "Artist Group -> DJoin DLeft   #assuming artist cannot join back  \n",
    "\n",
    "Artist -> Artist  \n",
    "Artist Group -> Group  ##trivial  \n",
    "Artist Group -> DFormed  \n",
    "Artist Group -> DJoin  \n",
    "Artist Group -> DLeft  \n",
    "\n",
    "Inference rules for functional dependencies\n",
    "\n",
    "1. If $Y\\subseteq X$ then X->Y is true. (Trivial, true for any relation)\n",
    "2. If X->Y and Y->Z then X->Z is also true. (Transitivity)\n",
    "3. If X->YZ then X->Y and X->Z is also true. (Decomposition, only for the right hand side!)\n",
    "4. If X->Y then XZ->YZ is also true. (Accumulation)\n",
    "\n",
    "Y = {Group}  \n",
    "X = {Artist, Group}  \n",
    "Y is a subset of X, X->Y  \n",
    "\n",
    "## Closure of a set of functional dependencies (F+)\n",
    "\n",
    "If I have a set of functional dependencies F for a relation R, the closure (F+) is the set of all functional dependencies in F or are implied by F (i.e. obtained by applying the inference rules to F).\n",
    "\n",
    "MusicGroup(Artist, DJoin, Dleft, DoB, DFormed, Group, Genre)\n",
    "\n",
    "F= {Artist -> DOB, Group -> DFormed, Artist Group -> DJoin DLeft }\n",
    "\n",
    "A **superkey** for a relation R and a set of fds F is a set of attributes  X such that \n",
    "X->Y is in F+ and Y is the set of all attributes in R.\n",
    "\n",
    "A **key** is a superkey that is minimal.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "209d8d9d-b057-4e8f-9bd5-4732da889b84",
   "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
}