BD VII: LDD, Vues et Fonctions SQL

---
name: xxx
layout: true
class: left, middle

.remark-slide-number .progress-bar-container {
  position: absolute;
  bottom: 0;
  height: 4px;
  display: block;
  left: 0;
  right: 0;
}

.remark-slide-number .progress-bar {
  height: 100%;
  background-color: red;
}
</style>

<div>
<style type="text/css">.xaringan-extra-logo {
width: 110px;
height: 128px;
z-index: 0;
background-image: url(./img/UniversiteParisCite_logo_horizontal_couleur_RVB.jpeg);
background-size: contain;
background-repeat: no-repeat;
position: absolute;
top:1em;right:1em;
}
</style>
<script>(function () {
  let tries = 0
  function addLogo () {
    if (typeof slideshow === 'undefined') {
      tries += 1
      if (tries < 10) {
        setTimeout(addLogo, 100)
      }
    } else {
      document.querySelectorAll('.remark-slide-content:not(.hide_logo)')
        .forEach(function (slide) {
          const logo = document.createElement('a')
          logo.classList = 'xaringan-extra-logo'
          logo.href = 'http://master.math.univ-paris-diderot.fr/annee/m1-mi/'
          slide.appendChild(logo)
        })
    }
  }
  document.addEventListener('DOMContentLoaded', addLogo)
})()</script>
</div>

---

# Bases de Données : SQL (V), Fonctions (SQL) et Vues

### 2022-10-21

#### [Licence MIASHS et Mathématiques]()

#### [Bases de Données](http://stephane-v-boucheron.fr/courses/bdd/)

#### [Équipe BD](http://stephane-v-boucheron.fr)

---
template: inter-slide
exclude: true

###  <svg aria-hidden="true" role="img" viewBox="0 0 576 512" style="height:1em;width:1.12em;vertical-align:-0.125em;margin-left:auto;margin-right:auto;font-size:inherit;fill:currentColor;overflow:visible;position:relative;"><path d="M384 476.1L192 421.2V35.93L384 90.79V476.1zM416 88.37L543.1 37.53C558.9 31.23 576 42.84 576 59.82V394.6C576 404.4 570 413.2 560.9 416.9L416 474.8V88.37zM15.09 95.13L160 37.17V423.6L32.91 474.5C17.15 480.8 0 469.2 0 452.2V117.4C0 107.6 5.975 98.78 15.09 95.13V95.13z"/></svg>

---
template: inter-slide
name: xxx

## Revisiter le schéma world

???

---

### Schema de world

---

### Conraintes sur la table `country`

- Un pays est identifié par son `countrycode`

- Deux pays ne devraient pas avoir le même nom

- Une région ne devrait appartenir qu'à un seul continent

- La capitale d'un pays devrait être une ville connue dans `city`

- La capitale d'un pays devrait être une ville de ce pays

- Les capitales de deux pays différents devraient être des villes différentes

- ...

---

### Mise en œuvre des contraintes

- Un pays est identifié par son `countrycode` : `PRIMARY KEY`

- Deux pays ne devraient pas avoir le même nom : `UNIQUE`

- Une région ne devrait appartenir qu'à un seul continent : ???

- La capitale d'un pays devrait être une ville connue dans `city` : `REFERENCES`

- La capitale d'un pays devrait être une ville de ce pays : ???

- Les capitales de deux pays différents devraient être des villes différentes : `UNIQUE`

- Deux pays différents ne peuvent avoir le même `code2`

---

### Examen du schema de `country`

```sql
+--------------------+---------------+-----------+----------+--------------+-------------+
| Column             | Type          | Modifiers | Storage  | Stats target | Description |
|--------------------+---------------+-----------+----------+--------------+-------------|
| countrycode        | character(3)  |  not null | extended | <null>       | <null>      |
| name_country       | text          |  not null | extended | <null>       | <null>      |
| continent          | text          |  not null | extended | <null>       | <null>      |
| region             | text          |  not null | extended | <null>       | <null>      |
| surfacearea        | real          |  not null | plain    | <null>       | <null>      |
| indepyear          | smallint      |           | plain    | <null>       | <null>      |
| population_country | integer       |  not null | plain    | <null>       | <null>      |
| lifeexpectancy     | real          |           | plain    | <null>       | <null>      |
| gnp                | numeric(10,2) |           | main     | <null>       | <null>      |
| gnpold             | numeric(10,2) |           | main     | <null>       | <null>      |
| localname          | text          |  not null | extended | <null>       | <null>      |
| governmentform     | text          |  not null | extended | <null>       | <null>      |
| headofstate        | text          |           | extended | <null>       | <null>      |
| capital            | integer       |           | plain    | <null>       | <null>      |
| code2              | character(2)  |  not null | extended | <null>       | <null>      |
+--------------------+---------------+-----------+----------+--------------+-------------+
```
]

---

###  (continued)

```sql
Indexes:
    "country_pkey" PRIMARY KEY, btree (countrycode)
Check constraints:
    "country_continent_check" CHECK (continent = 'Asia'::text OR 
                                     continent = 'Europe'::text OR 
                                     continent = 'North America'::text OR 
                                     continent = 'Africa'::text OR
                                     continent = 'Oceania'::text OR
                                     continent = 'Antarctica'::text OR
                                     continent = 'South America'::text),
Foreign-key constraints:
    "country_capital_fkey" FOREIGN KEY (capital) REFERENCES city(id)
    "country_fk" FOREIGN KEY (continent) REFERENCES code_continent(continent)
```
]

---

### DDL pour `country`

```sql
CREATE TABLE world.country (
	countrycode bpchar(3) NOT NULL,
	name_country text NOT NULL,
	continent text NOT NULL,
	region text NOT NULL,
	surfacearea float4 NOT NULL,
	indepyear int2 NULL,
	population_country int4 NOT NULL,
	lifeexpectancy float4 NULL,
	gnp numeric(10, 2) NULL,
	gnpold numeric(10, 2) NULL,
	localname text NOT NULL,
	governmentform text NOT NULL,
	headofstate text NULL,
	capital int4 NULL,
	code2 bpchar(2) NOT NULL,
	CONSTRAINT country_continent_check CHECK (((continent = 'Asia'::text) 
    OR (continent = 'Europe'::text) 
    OR (continent = 'North America'::text) 
    OR (continent = 'Africa'::text) 
    OR (continent = 'Oceania'::text)
    OR (continent = 'Antarctica'::text) 
    OR (continent = 'South America'::text))),
	CONSTRAINT country_pkey PRIMARY KEY (countrycode)
);
```
]

---

### Ajout de contraintes référentielles

```sql
-- world.country foreign keys

ALTER TABLE world.country ADD CONSTRAINT 
  country_capital_fkey FOREIGN KEY (capital) REFERENCES world.city(id);

ALTER TABLE world.country ADD CONSTRAINT 
  country_fk FOREIGN KEY (continent) REFERENCES world.code_continent(continent);
```

Les contraintes `country_capital_fkey`  et `country_fk` sont émises par 
la table `country`

---

### Contraintes ajoutées

```sql
-- world.country foreign keys

ALTER TABLE world.country ADD CONSTRAINT 
  country_capital_fkey FOREIGN KEY (capital) REFERENCES world.city(id);

ALTER TABLE world.country ADD CONSTRAINT 
  country_fk FOREIGN KEY (continent) REFERENCES world.code_continent(continent);
```

- Si le domaine est de grande cardinalité, utiliser une table pour représenter le domaine, 
et une contrainte référentielle
- Si le domaine est de petite cardinalité, une contrainte `CHECK` est aussi pratique

---

### Une région n'appartient qu'à un continent

Cette contrainte signifie que le résultat de la requête suivante est vide :

```sql
SELECT *
FROM world.country c1 JOIN world.country c2 ON 
  (c1.continent<> c2.continent AND c1.region=c2.region);
```

On *exclut* l'existence de deux lignes qui coïncideraient sur `region` mais pas sur `continent`

Nous postulons l'existence d'une **dépendance fonctionnelle**

`$$\texttt{region} \longrightarrow \texttt{continent}$$`

On peut considérer qu'il s'agit d'une anomalie de schéma et **normaliser** le schéma

On peut mettre en place une contrainte d'exclusion avec `EXCLUDE`

---
name: exclusion
template: inter-slide
exclude: true

## Une contrainte d'exclusion

---
exclude: true

---
exclude: true
### Index pour la contrainte d'exclusion

---
template: inter-slide

## Normalisation de `$\texttt{country}$`

---

### Éclatement de la table `country`

- Mise en place d'une table `region_continent(region, continent)`

- Émission d'une contrainte référentielle de `country` vers `region_continent`

---

### Préparation

```sql
CREATE SCHEMA monde AUTHORIZATION superdupont ;

GRANT ALL ON SCHEMA monde TO superdupont ;

CREATE TABLE monde.code_continent AS 
       TABLE world.code_continent ;

ALTER TABLE monde.code_continent ADD CONSTRAINT 
  code_continent_pk PRIMARY KEY (codecontinent);
ALTER TABLE monde.code_continent ADD CONSTRAINT 
  code_continent_un UNIQUE (continent);
```

---

### Création de `region_continent`

```sql
CREATE TABLE monde.region_continent AS 
  SELECT DISTINCT region, continent 
  FROM world.country c ;
```

```sql
SELECT * 
FROM monde.region_continent rc1 JOIN monde.region_continent rc2 ON (
	rc1.region=rc2.region AND rc1.continent<> rc2.continent
) ;
```
]

```sql
ALTER TABLE monde.region_continent ADD CONSTRAINT 
	region_continent_pk PRIMARY KEY (region);

ALTER TABLE mode.region_continent ADD CONSTRAINT 
  region_continent_fk FOREIGN KEY (continent) 
  REFERENCES monde.code_continent(continent);
```
---

### Création de la table `monde.country`

```sql
CREATE TABLE monde.country AS  
  SELECT  countrycode, name_country, region, surfacearea, 
          indepyear, population_country, 
          lifeexpectancy, gnp, gnpold, localname,
          governmentform, headofstate, capital,  code2            
  FROM world.country ;
```

---

### Mise en place des contraintes sur `monde.country`

```sql
ALTER TABLE monde.country ADD CONSTRAINT 
  country_pk PRIMARY KEY (countrycode);
ALTER TABLE monde.country ADD CONSTRAINT c
  ountry_un_name UNIQUE (name_country);
ALTER TABLE monde.country ADD CONSTRAINT 
  country_un_code2 UNIQUE (code2);
ALTER TABLE monde.country ADD CONSTRAINT 
  country_un_capital UNIQUE (capital);
```
]

```sql
ALTER TABLE monde.country ADD CONSTRAINT country_fk 
  FOREIGN KEY (region) REFERENCES monde.region_continent(region) 
  ON DELETE SET NULL 
  ON UPDATE CASCADE;
```
]

---

### Copie de `city`

```sql
CREATE TABLE monde.city AS TABLE world.city;
```

---

### Dans un pays plusieurs villes peuvent elles porter le même nom ?

```sql
SELECT *
FROM monde.city c JOIN monde.city c2 
	ON (c.name_city=c2.name_city AND 
      c.countrycode = c2.countrycode  AND 
      c.id<> c2.id) ;
```

renvoie 48 lignes

```sql
SELECT *
FROM monde.city c JOIN monde.city c2 
	ON (c.name_city=c2.name_city AND 
      c.countrycode = c2.countrycode  AND
      c.district = c2.district  AND
      c.id<> c2.id) ;
```

renvoie 2 lignes

---

```sql

```

---

### Existe-t-il un pays dont la capitale est dans un autre pays ?

```sql
SELECT c. countrycode, c.name_country, cc.name_city, cc.countrycode
FROM world.country c JOIN world.city cc ON (c.capital=cc.id AND cc.countrycode <> c.countrycode) ;
```
renvoie 0 lignes <svg aria-hidden="true" role="img" viewBox="0 0 640 512" style="height:1em;width:1.25em;vertical-align:-0.125em;margin-left:auto;margin-right:auto;font-size:inherit;fill:currentColor;overflow:visible;position:relative;"><path d="M639.4 433.6c-8.374-20.37-31.75-30.12-52.12-21.62l-22.12 9.249l-38.75-101.1c47.87-34.1 64.87-100.2 34.5-152.7l-86.62-150.5c-7.999-13.87-24.1-19.75-39.1-13.62l-114.2 47.37L205.8 2.415C190.8-3.71 173.8 2.165 165.8 16.04L79.15 166.5C48.9 219 65.78 284.3 113.6 319.2l-38.75 101.9L52.78 411.9c-20.37-8.499-43.62 1.25-52.12 21.62c-1.75 4.124 .125 8.749 4.25 10.5l162.4 67.37c3.1 1.75 8.624-.125 10.37-4.249c8.374-20.37-1.25-43.87-21.62-52.37l-22.12-9.124l39.37-103.6c4.5 .4999 8.874 1.25 13.12 1.25c51.75 0 99.37-32.1 113.4-85.24l20.25-75.36l20.25 75.36c13.1 52.24 61.62 85.24 113.4 85.24c4.25 0 8.624-.7499 13.12-1.25l39.25 103.6l-22.12 9.124c-20.37 8.499-30.12 31.1-21.62 52.37c1.75 4.124 6.5 5.999 10.5 4.249l162.4-67.37C639.1 442.2 641.1 437.7 639.4 433.6zM275.9 162.1L163.8 115.6l36.5-63.37L294.8 91.4L275.9 162.1zM364.1 162.1l-18.87-70.74l94.49-39.12l36.5 63.37L364.1 162.1z"/></svg>

---

###  <svg aria-hidden="true" role="img" viewBox="0 0 512 512" style="height:1em;width:1em;vertical-align:-0.125em;margin-left:auto;margin-right:auto;font-size:inherit;fill:currentColor;overflow:visible;position:relative;"><path d="M506.3 417l-213.3-364c-16.33-28-57.54-28-73.98 0l-213.2 364C-10.59 444.9 9.849 480 42.74 480h426.6C502.1 480 522.6 445 506.3 417zM232 168c0-13.25 10.75-24 24-24S280 154.8 280 168v128c0 13.25-10.75 24-23.1 24S232 309.3 232 296V168zM256 416c-17.36 0-31.44-14.08-31.44-31.44c0-17.36 14.07-31.44 31.44-31.44s31.44 14.08 31.44 31.44C287.4 401.9 273.4 416 256 416z"/></svg>  Blocages possibles

- `city` emet une contrainte référentielle sur `country` (`countrycode`)
- `country` emet une contrainte référentielle sur `city` (`capital` `$\rightarrow$` `id`)

Risque de blocage : qui précède la capitale ou le pays ?

---

### Émission de la contrainte de `city` vers `country`

```sql
ALTER TABLE monde.city 
  ADD CONSTRAINT city_fk FOREIGN KEY (countrycode) 
      REFERENCES monde.country(countrycode) 
  ON DELETE SET NULL ON UPDATE CASCADE;
```

---

### Table `countrylanguage`

```sql
CREATE TABLE monde.countrylanguage  AS TABLE world.countrylanguage ;
```

```sql
ALTER TABLE monde.countrylanguage ADD CONSTRAINT countrylanguage_fk 
	FOREIGN KEY (countrycode) REFERENCES monde.country(countrycode) 
	ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE monde.countrylanguage ADD CONSTRAINT countrylanguage_pk 
	PRIMARY KEY ("language",countrycode);

ALTER TABLE monde.countrylanguage ADD CONSTRAINT countrylanguage_check 
	CHECK (percentage <= 100.0 AND percentage >=0.0 );
```
]

---

![](./img/bd_2023-monde.png)

---
name: vues-regionales
template: inter-slide

## Vues régionales et continentales

---

### Synthèse régionale

```sql
CREATE OR REPLACE VIEW monde.regionale AS (
SELECT C.region, 
       SUM(c.population_country) AS population_region,
       SUM(COALESCE(c.surfacearea, 0)) AS surfacearea_region,
       SUM(c.gnp) AS gnp_region,
       SUM(c.gnpold) AS gnpold_region
FROM monde.country c 
GROUP BY c.region ) ;
```

---

## Synthèse continentale

```sql
SELECT r.* 
FROM monde.regionale r JOIN monde.region_continent rc 
     ON (r.region=rc.region AND rc.continent='Europe') ;
```

```
+----------------+-----------------+------------------+----------+-------------+
|region          |population_region|surfacearea_region|gnp_region|gnpold_region|
+----------------+-----------------+------------------+----------+-------------+
|Western Europe  |        183247600|         1108456.5|4673272.00|   4578420.00|
|Southern Europe |        144674200|         1316392.4|2012289.00|   1944737.00|
|Baltic Countries|          7561900|          175117.0|  22418.00|     18595.00|
|Nordic Countries|         24166400|         1321901.0| 676655.00|    677698.00|
|British Islands |         63398500|          313173.0|1454251.00|   1369962.00|
|Eastern Europe  |        307026000|          18814094| 659980.00|    792589.00|
```

]

---

### Synthèse continentale

```sql
CREATE OR REPLACE VIEW monde.continentale AS (
SELECT rc.continent, 
       SUM(r.population_region) AS population,
       SUM(r.surfacearea_region) AS surfacearea,
       SUM(r.gnp_region) AS gcp,
       SUM(r.gnpold_region) AS gcpold
FROM monde.regionale r JOIN monde.region_continent rc 
     ON (r.region=rc.region) 
GROUP BY rc.continent;) ;
```

.f6[
```
+-------------+----------+-----------+----------+----------+
|continent    |population|surfacearea|gcp       |gcpold    |
+-------------+----------+-----------+----------+----------+
|South America| 345780000|   17864926|1511874.00|1520641.00|
|North America| 482993000|   24214472|9688627.20|9288175.00|
|Oceania      |  30401150|  8564294.0| 419774.70| 469058.00|
|Antarctica   |         0|   13132101|      0.00|          |
|Africa       | 784475000|   30250376| 580375.00| 553264.00|
|Europe       | 730074600|   23049132|9498865.00|9382001.00|
|Asia         |3705025700|   31881006|7655392.00|8251969.00|
```
]

---

background-image: url('./img/pexels-cottonbro-3171837.jpg')
background-size: cover

# The End