RelationalModel.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "87a85ab9",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Relational Model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ab9eb19",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9fcc45dd",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "0d11420c",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Data Modeling\n",
    "https://aws.amazon.com/what-is/data-modeling/"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73658cc6",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## What is data modeling ?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8644f86f",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Data modeling is the **process** of creating a **data model** for an **information system** "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4617f7d",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Use visual representation or blueprint the define the information flow and management on any organization"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60e1e240",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Who needs a data model ?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e339c9fc",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- The data model is useful for many people, including not only data analyst, data scientist or developers"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "881c07b8",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- but also business and departments that are the owner and the main consumers"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7a46993d",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- and data protection officer, lawyers as well if you manage personal data!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92069144",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Data models information\n",
    "The data model outlines:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "088d8508",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- the data collected (and possibly how)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd35a94c",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- the relationship between different datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9876c805",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- methods used to store "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef1adbd5",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- how to analyse "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "725d0b2c",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Why is data modeling important ?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "117a184f",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### To understand the needs"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95ae34a8",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Organizations today collect a large amount of data from many different sources. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a9c538a6",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Raw data is not enough. The need is to analyze data for actionable insights that can guide you to make profitable business decisions. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37090e8a",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Accurate data analysis needs efficient data collection, storage, and processing. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a3fffede",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- There are several database technologies and data processing tools, and different datasets require different tools for efficient analysis."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "435383dc",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### To choice the right technology\n",
    "In order to achieve a way to create information from data (i.e. understanding data), it's important to design properly the database (like an architect designs a house before building)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae49ab6f",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Data Modeling Benefits "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4445bed3",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "1. Reduce errors in the development phases"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "012e36ec",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "2. Improve speed and efficency "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f07c5fc5",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "3. Creates a data dictionary accross the organization"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb104bff",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "4. Faciliates communication between different teams"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c104e44",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# History"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed32b85b",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# The Relational Model (1970)\n",
    "https://dl.acm.org/doi/10.1145/362384.362685\n",
    "![](images/coddpaper1970.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "728b4f3c",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "# Data Representation in Relational Model"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dbf3941c",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "**tuple**\n",
    "\n",
    "- Math: Order List of elements defining a row\n",
    "-  set of attribute values in which no two distinct elements have the same name."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "efa0b6af",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "**relations**\n",
    "\n",
    "- A set of tuple (d1, d2, ..., dn), where each element dj is a member of Dj, a data domain. \n",
    "- data domain descrive data elements boundaries and types "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f116206",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "**attribute**\n",
    "\n",
    "- Name paired with a domain (data type)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "efd8c35d",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "**attribute value**\n",
    "\n",
    "- is an attribute name paired with an element of that attribute's domain"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb1adfcc",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## A picture is worth a thousen words\n",
    "![](https://upload.wikimedia.org/wikipedia/commons/thumb/7/7c/Relational_database_terms.svg/640px-Relational_database_terms.svg.png)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d7e0d89",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "# Relations"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bfa93351",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "**Heading**\n",
    "\n",
    "Set of *attributes*. each with a *name* and a *data type* (domain)\n",
    "\n",
    "Example: attribute age, type integer\n",
    "\n",
    "Degree (arity): number of attributes of the relation\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10f15fe4",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "**Body**\n",
    "\n",
    "Set of *tuples* \n",
    "\n",
    "A *tuple* is a set of attribute values in which no two distinct elements have the same name.\n",
    "\n",
    "Cardinality: The number of tuples in the set."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e1f96e34",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# The ANSI/X3/SPARK DBMS Framework (1975)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d7daafb",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "![](https://upload.wikimedia.org/wikipedia/commons/5/5c/ANSI-SPARC_DB_model.jpg)\n",
    "\n",
    "[The ANSI-SPARC three-level architecture](https://en.wikipedia.org/wiki/ANSI-SPARC_Architecture)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb28ea99",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "**Glossary**\n",
    "\n",
    "- ANSI = American National Standard Institute\n",
    "- ANSI/X3 = Committee on Computers and Information Processing\n",
    "- SPARC = Standards Planning and Requirements Committee\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0b77346",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "# A standard not standard"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5bb070f",
   "metadata": {
    "cell_style": "center",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- The idea of the framework was to create interoperable computer systems"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "94d21973",
   "metadata": {
    "cell_style": "center",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- All vendors adopted the schemas, but not compatible (i.e. non portable)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5ef6ff10",
   "metadata": {
    "cell_style": "center",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- So it's a standard not standard"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c304d894",
   "metadata": {
    "cell_style": "center",
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## The Challenge Of Knowledge Soup\n",
    "\n",
    "![](http://www.jfsowa.com/figs/cs1.gif)\n",
    "\n",
    "ISO Standards Project, R.I.P. 1999.\n",
    "\n",
    "Born again as the Semantic Web.\n",
    "\n",
    "http://www.jfsowa.com/talks/challenge.htm"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47c65466",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# The Entity Relationship Model (1976)\n",
    "https://dl.acm.org/doi/10.1145/320434.320440\n",
    "![](images/chenpaper1976.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1d880c7",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Some Highlights"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "110e190b",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- The relational model is based on relational theory and can achieve a high degree of data independence, but it may lose some important semantic information about the real world"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c3f3b6f8",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- The entity-relationship model adopts the more natural view that the real world consists of entities and relationships. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e34cb3a",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- The entity-relationship model can be used as a basis for a unified view of data. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea2f337d",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Data Modeling process"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8985cfb8",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- In this section we will introduce a process to create a data model using a hierachical approach (not a hiearchical data model)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6cd376f6",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- We will use ER diagram to visualize the level, adding details to each level"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d8d4b0e",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Finally, we will use a diagraming tool, draw.io to practice with data modelling"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e48607fd",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#  conceptual-logical-physical hierarchy"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "397826d2",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "![](https://www.gooddata.com/img/blog/_2000xauto/types-of-data-models.png)\n",
    "\n",
    "https://www.gooddata.com/blog/what-a-data-model/"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2892d881",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Conceptual Data Model (WHAT)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71f7f718",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- High Level description of informational need (sketch, essential information)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc166a0b",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Define business concepts and rules using *entities*, their *attributes* and the *relationship*"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "300aee6b",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Usually represented using a ER diagram"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de9deb6f",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Entity (noun)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "feeebb9a",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "> An entity may be defined as a thing that is capable of an independent existence that can be uniquely identified, and is capable of storing data.\n",
    "\n",
    "Richard Walsh (2022). Database Design Using Entity-Relationship Diagrams."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3868c519",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "**Examples**\n",
    "- A Person\n",
    "- A Notebook\n",
    "- A Song\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff1e0e7a",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Entity Attributes (Adjective)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "157be41a",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "The characteristics or properties of entities.\n",
    "\n",
    "Some attributes, that uniquely identifiy each entity, are called **primary keys** \n",
    "(normally added in the logical modeling) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23fe2b3f",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "**Examples**\n",
    "- Gender\n",
    "- Age\n",
    "- SSN\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20f46168",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Relationship (Verb)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "978c8efe",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "> A relationship captures how entities are related to one another. Relationships can be thought of as verbs, linking two or more nouns.\n",
    "\n",
    "Richard Walsh (2022). Database Design Using Entity-Relationship Diagrams."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4dd74051",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "**Examples**: \n",
    "- a buys relationship between a person and a product\n",
    "- an owns relationship between a company and a computer\n",
    "- a supervises relationship between an employee and a department\n",
    "- a performs relationship between an artist and a song"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dbb8ef58",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Relationship Attributes (Adverb)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b0fbc16c",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "Properties of a relationship."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "79e52404",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "**Examples**\n",
    "- Date of purchace (when)\n",
    "- Place of purchase (where)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b4a5dfa",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## ER Basic Symbols\n",
    "![](images/ersymbols.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57539783",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "# Draw.io\n",
    "\n",
    "We will use draw.io to create ER Diagrams \n",
    "\n",
    "https://www.drawio.com/\n",
    "\n",
    "https://drawio-app.com/blog/entity-relationship-diagrams-with-draw-io/"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "070e9eac",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Example\n",
    "\n",
    "Create a ER Diagram of the Master"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "abfc6c9d",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Logical Data Model (HOW)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48cdb988",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Derived from Conceptual Model adding details"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da150472",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- The most used is the Relational Model "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a1eb832",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Use ER with a different notation (tables)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27f671b2",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- DBMS indipendent"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3bd36810",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Relations (Crow's foot notation)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be539672",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "In the logical data model using Relational Model and Crow's foot notation:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8eb97d6c",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Enties are mapped into Relations (i.e. tables)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53ebe67e",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Relationship are mapped into lines that connect Tables"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d58ef358",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Cardinality of a relationship defines how tuples interact"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "868ba8f3",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Cardinality in Relationship"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21167b2a",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "Three symbols are used to represent cardinality:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aef53e9c",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- the ring represents \"zero\"\n",
    "- the dash represents \"one\"\n",
    "- the crow's foot represents \"many\" or \"infinite\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a799a084",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- ring and dash → minimum zero, maximum one (optional)\n",
    "- dash and dash → minimum one, maximum one (mandatory)\n",
    "- ring and crow's foot → minimum zero, maximum many (optional)\n",
    "- dash and crow's foot → minimum one, maximum many (mandatory)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a2620e7e",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Using numbers\n",
    "| Relationship                            | Example                     | Left               | Right              | Narrative                                                                                       |\n",
    "| --------------------------------------- | --------------------------- | ------------------ | ------------------ | ----------------------------------------------------------------------------------------------- |\n",
    "| One-to-one                              | person ←→ birth certificate | **1**              | **1**              | A person must have their own birth certificate, it is specific to that person by its Id number. |\n",
    "| One-to-one _(optional on one side)_     | person ←→ driving license   | **1**              | **0..1**or **?**   | A person may have a driving license, it is specific to that person by its Id number.            |\n",
    "| One-to-many                             | order ←→ line item          | **1**              | **1..\\***or **+**  | An order contains at least one item                                                             |\n",
    "| Many-to-one                             | person ←→ birthplace        | **1..\\***or **+**  | **1**              | Many people can be born in the same place, but 1 person can only be born in 1 birthplace        |\n",
    "| Many-to-many                            | course ←→ student           | **1..\\***or **+**  | **1..\\***or **+**  | Students follow various courses                                                                 |\n",
    "| Many-to-many _(optional on both sides)_ | person ←→ book              | **0..\\***or **\\*** | **0..\\***or **\\*** | A person may own many books(copies), and a book may be owned by many people(readers).           |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e9c1172",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## NULL"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb4c3ae2",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- \"missing information and inapplicable information\" (Codd)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0da5658e",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Can be related to a data value missing, unknown"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4aa73cc",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Adopted as a standard also in SQL"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e0ba4d8",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Some criticism (https://medium.com/geekculture/nulls-the-original-sin-of-database-e5c1313293ca)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d328eb89",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Constraints\n",
    "SQL and Relational Theory: How to Write Accurate SQL Code by C. J. Date (2015)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c74e94b1",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- An integrity constraint (constraint for short) is basically just a boolean expression that must\n",
    "evaluate to TRUE. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31efb063",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- If all constraints evaluate as true, the database is consistent; otherwise, it is inconsistent. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54a8de89",
   "metadata": {
    "cell_style": "center",
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Keys"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eeb13b81",
   "metadata": {
    "cell_style": "center",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- The relational model also supports various kinds of keys. To begin with—and this point is\n",
    "crucial: every relation has at least one candidate key."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c89349fe",
   "metadata": {
    "cell_style": "center",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- A candidate key is just a unique identifier; in other words, it’s a combination of attributes—often but not always a “combination” consisting of just a single attribute—such that every tuple in the relation has a unique value for\n",
    "the combination in question."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "008c6f4f",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- **The entity integrity rule**: Primary key attributes don’t permit nulls."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8dcf95d7",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Primary Keys"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b9f23821",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- a primary key is a candidate key that’s been singled out for special treatment in some\n",
    "way"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "044c2312",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- In ER models primary keys are represented underling the name of the attribute"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d859914b",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Foreign Keys"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8567ba2c",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- a set of attribute FK in a relation R, such as each FK value is required to be equal to some value of same key K in a relation S"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d8b3766",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "![](https://preview.redd.it/8400et4oghl81.jpg?width=640&crop=smart&auto=webp&s=b9a9ba17611cc3a200fac9795a7887f7aadfe633)\n",
    "[Reddit](https://www.reddit.com/r/ProgrammerHumor/comments/t708sn/key_to_relationships/)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68687ed2",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Physical Data Model (HOW)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2aa44de9",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Last step of data modeling process"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4826fefb",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Can be derived from the logical schema or obtained by database (like in Access)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a9c4e25",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- It's dependent of DBMS"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c4d88eb",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Besides Diagram, contains also the database schema"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95ef1826",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Comparison Summary"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c070a22a",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "| Conceptual data model (CDM)                                                                                                   | Logical data model (LDM)                                                         | Physical data model (PDM)                                                                                                                                                                      |   |   |   |   |   |   |   |\n",
    "|-------------------------------------------------------------------------------------------------------------------------------|----------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|---|---|---|---|---|---|---|\n",
    "| Includes high-level data constructs                                                                                           | Includes entities (tables), attributes (columns/fields) and relationships (keys) | Includes tables, columns, keys, data types, validation rules, database triggers, stored procedures, domains, and access constraints                                                            |   |   |   |   |   |   |   |\n",
    "| Non-technical names, so that executives and managers at all levels can understand the data basis of Architectural Description | Uses business names for entities & attributes                                    | Uses more defined and less generic specific names for tables and columns, such as abbreviated column names, limited by the database management system (DBMS) and any company defined standards |   |   |   |   |   |   |   |\n",
    "| Uses general high-level data constructs from which Architectural Descriptions are created in non-technical terms              | Is independent of technology (platform, DBMS)                                    | Includes primary keys and indices for fast data access.                                                                                                                                        |   |   |   |   |   |   |   |\n",
    "|                                                                                                                  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac9d8089",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Data Modeling Process (RACI)\n",
    "https://docs.aws.amazon.com/prescriptive-guidance/latest/dynamodb-data-modeling/process-flow.html"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c8c2761",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Alternatives to ER\n",
    "\n",
    "- [IDEF1X](https://www.idef.com/): Integration DEFinition for information modeling\n",
    "- [UML](https://it.wikipedia.org/wiki/Unified_Modeling_Language): Unified Modeling Language"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "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.9.15"
  },
  "rise": {
   "autolaunch": true,
   "enable_chalkboard": "true",
   "footer": "<div class=\"sds-footer\"> *** Social Data Science (SDS) - Notions of Database Management Systems ***</div>",
   "header": "<div class=\"sds-header\"><div class=\"sds-header-image\"></div></div>",
   "scroll": true,
   "theme": "white"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}