CM 3 - BDD

name: inverse
layout: true
class: center, middle, inverse

---
# Base de données - CM 3
## Inès de Courchelle
## 2024-2025

![image](img/CY-tech.png)

---
layout: false
# Rappel
## DF Élémentaires
- réferenceProduit -> libProduit : élémentaire car deux rubriques
- numFacture, réferenceProduit -> quantitéFacturée  est  élémentaire : ni  la  référence  produit  seule,  ni  le numéro de facture seul permettent de déterminer la quantité
- I -> J :  élémentaire car deux rubriques

## DF Non Élémentaires
- numFacture, réferenceProduit -> libProduit : non élémentaire  car  la  référence  du  produit suffit à déterminer le libellé
- AB -> CB : non élémentaire car CB n'est pas un attribut, mais un groupe d'attributs
- N°SS -> Nom, Prénom : non élémentaire car c'est un groupe d'attributs
- AB -> A : non élémentaire car A est incluse dans AB

---
# Rappel
## Exemple
Voiture(id, marque, type, puissance, couleur)
## DF Directes
- id -> type : direct
- id -> couleur : direct

## DF Non directe
id -> marque : non direct car id -> type et type -> marque

---
# Normalisation + LDD
## Plan
1. Normalisation
2. LDD

---
# La Normalisation
## Quand ?
Pendant la phase de conception sur le modèle entités-associations et dans le modèle relationnel

## Objectifs
  - Eviter la redondance de données
  - Perdre des données
  - Ralentir les performances de traitements
  - Enlever de l'incohérences

## Comment ?
règles de normalisation

#### Une base de données est normalisé si celle-ci est au moins en 3ieme forme normale

---
# 1FN 1/2
## Définition
- Une relation est en 1FN si, et seulement si tout attribut contient une valeur atomique
- Aucun attribut ne doit être :
  - multiple
  - composé

## MCD NON 1FN

---
# 1FN 2/2
## MLD non 1FN
Personne(numPersonne, nom, prenom, prenomsEnfants)

Ici prenomsEnfants est une liste, il peut y en avoir plusieurs (ou zéro)

## Solution
Personne(numPersonne, nom, prenom)

EstEnfantDe(#numPersonneParent,#numPersonneEnfant)

---
# 2FN 1/3
## Définition
Une relation est en 2FN si et seulement si
  - Elle est 1FN
  - et si toutes les dépendances fonctionnelles entre la clé et les autres attributs sont élémentaires

---
# 2FN 2/3
## MLD non 2FN
Resultat(#idSkater,#idFigure,note,difficulte)

Figure(idFigure,nom)

Ici, la difficulté ne dépend pas du skater mais de la figure.
## Solution
Resultat(#idSktater,#idFigure,note)

Figure(idFigure,nom,difficulte)

---
# 2FN 3/3
## Dépendances fonctionnelles
 
Travail(#idPersonne, #idProfession, Salaire)

#### Soit les DF suivantes sur cette relation :
1. idPersonne,idProfession -> Salaire
2. idProfession -> Salaire

- La première DF est issu de la clé et elle n'est pas élémentaire.
- Seulement la profession détermine le salaire

## Solution
Travail(#idPersonne, #idProfession)

Profession(#idProfession,salaire)

---
# 3FN 1/4
## Définition
Une relation est en 3FN, si et seulement si :
- Elle est 2FN
- Toutes les dépendances fonctionnelles entre la clé et les autres attributs sont directes

**Il n’existe pas d’attribut non clé dépendant d’un attribut non clé**

---
# 3FN 2/4
## MLD NON 3FN
Adherent(idAdherent,nom,prenom,typeAdh,cotisation)

| idAdherent | nom       | prenom    | typeAdh           | cotisation |
| ---------  | --------- | --------- | ---------         | ---------  |
| 1          | Ronaldo   | Cristiano | interne           | 100        |
| 2          | Messi     | Lionel    | externe           | 50         |
| 3          | Pratt     | Chris     | interne           | 100        |
| 4          | Presley   | Elvis     | demi-pensionnaire | 75         |
| 5          | Nelson    | Ricky     | demi-pensionnaire | 50         |

**Ici, on remarque que le type d'ahésion influence la cotisation de l'adhérent**

---
# 3FN 2/4
## MLD NON 3FN
Adherent(idAdherent,nom,prenom,typeAdh,cotisation)

| idAdherent | nom       | prenom    | typeAdh           | cotisation  |
| ---------  | --------- | --------- | ---------         | ---------   |
| 1          | Ronaldo   | Cristiano | interne           | 100         |
| 2          | Messi     | Lionel    | externe           | 50          |
| 3          | Pratt     | Chris     | interne           | 100         |
| 4          | Presley   | Elvis     | demi-pensionnaire | .dotRed[75] |
| 5          | Nelson    | Ricky     | demi-pensionnaire | .dotRed[50] |

**Ici, on remarque que le type d'ahésion influence la cotisation de l'adhérent**

---
# 3FN 3/4
## SOLUTION
**Création d'une nouvelle relation/entité**
- Adherent(idAdherent,nom,prenom,#idType)
- TypeAdherent(idType,appelation,cotisation)

---
# 3FN 3/4
## Adherent
| idAdherent | nom       | prenom    | idType    |
| ---------  | --------- | --------- | --------- |
| 1          | Ronaldo   | Cristiano | 1         |
| 2          | Messi     | Lionel    | 2         |
| 3          | Pratt     | Chris     | 1         |
| 4          | Presley   | Elvis     | 3         |
| 5          | Nelson    | Ricky     | 3         |

## TypeAdherent
| idType    | appelation        | cotisation |
| --------- | ---------         | ---------  |
| 1         | interne           | 100        |
| 2         | externe           | 50         |
| 3         | demi-pensionnaire | 75         |

.underV[Tous les demi-pensionnaires payeront la même cotisation]

---
# BCNF 1/4
## Définition
Une relation est en BCNF si elle est en 3NF et si tout attribut qui n'appartient pas à une clé n'est pas source d'une DF vers une partie d'une clé. C'est à dire que les seules DFE existantes sont celles dans lesquelles une clé détermine un attribut.

## Autrement dit
Éliminer les redondances créées par des dépendances

---
# 3BCNF 2/4
## Exemple

| idStudent | matière   | prof      |
| --------- | --------- | --------- |
| 1         | BDD       | Gislain   |
| 2         | JAVA      | Albert    |
| 3         | JAVA      | Albert    |
| 4         | BDD       | Gislain   |
| 5         | C++       | Gérard    |

#### FAUX
Ici il y a une redondance de l'information

---
# 3BCNF 3/4
## Solution
#### Table 1
| idStudent | idProf    |
| --------- | --------- |
| 1         | 1         |
| 2         | 2         |
| 3         | 2         |
| 4         | 1         |
| 5         | 3         |

#### Table 2
| idProf    | matière   |
| --------- | --------- |
| 1         | Gislain   |
| 2         | Albert    |
| 3         | Gérard    |

---
# 3BCNF 4/4
## Géneriquement Parlant
- Soit la relation R(A,B,C,D)
- Avec :
  - A .arrow[] BCD
  - BC .arrow[] AD
  - D .arrow[] B
- A nous est une clé
- BC est aussi une clé
- D nous donne B donc B n'est pas une clé

## Solution
- R1(A,D,C)
- R2(D,B)

---
# Normalisation
## Les différentes règles
<img src="img/fn.png" width=200/>

---
# Normalisation
## Pourquoi on fait ça ?
- Eviter les redondances
- Pereniser les données d'un modèle
- Optimiser le stockage de la donnée
- Developper et changer le modèle en fonction du contexte dans lequel il évolue
- Gagner du temps dans le développement

---
# Normalisation + LDD
## Plan
1. Normalisation
2. **LDD**

---
# Le LDD
## Définition
Décrire la structure des données que l'on souhaite manipuler
## Pourquoi ?
Employer un langage compris par la machine
## Quand ?
Utiliser au moment de créer la base de données dans l'ordinateur
## Comment ?
Transformer le MLD en mysql !

---
# La Table 1/2
## Contexte
- Dans le MCD on parle d'entité
- Dans le MLD on parle de relation
- Dans le LDD on parle de table

## Définition d'une Table
- Structure de données composées de colonnes et de lignes !
- Chaque colonne doit être nommée et typée

---
# La Table 2/2
## Exemple
#### Le MCD
<img src="img/mcdtables.png" width=100/>

#### Le MLD
Personne(id,nom,prenom)

#### Description de la Table

La table personne a 3 colonnes :
- id => Entier
- nom => Chaîne de caractères
- prénom => Chaîne de caractères

.under[Comment transformer cela en LDD ?]

---
# Traduction en SQL !
## LDD - étape par étape
1. Création de la table
2. Délimiter les colonnes
3. Identifier les clés primaires
4. Marquer les clés étrangères

---
# Création de la table
## MLD
Client(idClient,nom,prenom)
## Traduction
```
CREATE TABLE Client (

);
```
ATTENTION :
- une table a un début et une fin (délimiter par des parenthèses)
- une creation de table correspond à une seule instruction donc un seul point virgule à la fin
- une table a toujours un nom

---
# Délimiter les colonnes
## MLD
Client(idClient,nom,prenom)
## Traduction
```
CREATE TABLE Client (
 idClient INTEGER(5),
 nom VARCHAR(100),
 prenom VARCHAR(100)
);
```
**Précisions**
- la dernière ligne avant *);* ne prends pas de virgule
- idClient peut prendre une valeur max d'un nombre à 5 chiffres
- il peut donc il y avoir 99 999 clients au maximum !
---
# Définir la clé primaire 1/2
## MLD
Client(idClient,nom,prenom)
## Traduction
```
CREATE TABLE Client (
 idClient INTEGER(5),
 nom VARCHAR(100),
 prenom VARCHAR(100),
 CONSTRAINT pk_id PRIMARY KEY(idClient)
);
```
**Précisions**

CONSTRAINT *< nom de la contrainte >* PRIMARY KEY (*< nom de la colonne >*)

---
# Définir la clé primaire 2/2
## MLD
Client(idClient,nom,prenom)

Commandes(#idProduit, #idClient, quantite)
## Traduction
```
CREATE TABLE Commandes (
 idProduit INTEGER(5),
 idClient INTEGER(5),
 CONSTRAINT pk_id PRIMARY KEY(idProduit,idClient)
);
```
**Précisions**
- La clé primaire est composée de 2 ids
- Cela doit être une contrainte unique

.underR[Attention : Il manque les clés étrangères]

---
# Définir les clés étrangéres 1/2
## MLD
Client(idClient,nom,prenom)

Commandes(#idProduit, #idClient, quantite)

## Traduction
```
CREATE TABLE Commandes (
    idProduit INTEGER(5),
    idClient INTEGER(5),
    CONSTRAINT pk_id PRIMARY KEY(idProduit,idClient),
    FOREIGN KEY fk_Produit(idProduit) references Produit(idProduit),
    FOREIGN KEY fk_Client(idClient) references Client(idClient)
);

```
**Précisions**

FOREIGN KEY *< nom de la contrainte >*(*< nom de la colonne >*) 
REFERENCES *< nom de la table originale>* (*< colonne dans la table originale>*)

---
# Définir les clés étrangéres 2/2
## Traduction
```
CREATE TABLE Client (
    idClient INTEGER(5),
    nom VARCHAR(100),
    prenom VARCHAR(100),
    CONSTRAINT pk_id PRIMARY KEY(idClient)
);

CREATE TABLE Commandes (
    idProduit INTEGER(5),
    idClient INTEGER(5),
    CONSTRAINT pk_id PRIMARY KEY(idProduit,idClient),
    FOREIGN KEY fk_Produit(idProduit) references Produit(idProduit),
    FOREIGN KEY fk_Client(idClient) references Client(idClient)
);
```
**Précisions**

idClient doit avoir la même définition de colonne dans la table Client

---
# Les différents types
## La liste

- les types numériques
- les types alpha-numériques
- les types dates

## syntaxe générale
```sql
*< nom colonne >* TYPE (*< taille du type >*)
```

---
# Types Numériques
## Définition
  - DECIMAL (x,y)
  - INTEGER (x)
  - SMALLINT

## Utilisation
  - age SMALLINT
      - pas besoin de donner une taille
  - quantite INTEGER(3)
      - la quantité max possible à enregistrer est 999
  - salaire DECIMAL(10,2)
      - le salaire max possible est à 10 chiffres
      - le nombre de chiffre après la virgule est max 2
---
# Types Alpha-numériques
## Définition
  - CHAR (n)
  - VARCHAR (n)

## Exemple
  - nom CHAR(20)
  - prénom VARCHAR(100)

---
# Types dates
## Définition
  - DATE
  - TIMESTAMP

## Exemple
  - anniversaire DATE
    - le format par défaut sera le suivant : YYYY-MM-DD
  - horaire TIMESTAMP
    - le format par défaut sera le suivant : YYYY-MM-DD HH:MM:SS

---
# Les autres types

Ils sont tous dans la DOCUMENTATION => [LINK DOC SQL](https://sql.sh)

---
# Modifier une table 1/8
## Que puis je modifier ?
- Ajouter/Modifier/Supprimer des colonnes
- Ajouter/Modifier/Supprimer des contraintes

## Comment ?
```
ALTER TABLE <nom de la table>
ADD/MODIFY/DROP <instruction>;
```

**Précision**
Une modification d'une table est ponctuée par un ; à la fin!

---
# Modifier une table 2/8
Nous considérons la table suivante pour les exemples de modification
```
CREATE TABLE Personne (
    nom VARCHAR(100),
    prenom VARCHAR(100),
    age INTEGER(2)
);
```
| COLONNE | TYPE    |
| ------- | ------- |
| nom     | VARCHAR |
| prenom  | VARCHAR |
| age     | INTEGER |

---
# Modifier une table 3/8
## Ajouter une colonne
```
ALTER TABLE Personne ADD (id INTEGER(100),niveau INTEGER(10), sexe VARCHAR(1));
```
| COLONNE | TYPE    |
| ------- | ------- |
| nom     | VARCHAR |
| prenom  | VARCHAR |
| age     | INTEGER |
| id      | INTEGER |
| niveau  | INTEGER |
| sexe    | VARCHAR |

---
# Modifier une table 4/8
## Ajouter une contrainte
```
ALTER TABLE Personne ADD CONSTRAINT pk_id PRIMARY KEY (id);
```
| COLONNE | TYPE |
| ------- | ------- |
| nom | VARCHAR |
| prenom | VARCHAR |
| age | INTEGER |
| id | INTEGER PRIMARY KEY |
| niveau | INTEGER |
| sexe | VARCHAR |

**Attention** l'attribut id doit exister !

---
# Modifier une table 5/8
## Modifier le type d'une colonne
```
ALTER TABLE Personne MODIFY niveau VARCHAR(100);
```
- Il n'y a pas de parenthèse après modify
- Cette instruction ne permet pas de modifier le nom de la colonne

| COLONNE | TYPE |
| ------- | ------- |
| nom | VARCHAR |
| prenom | VARCHAR |
| age | INTEGER |
| id | INTEGER PRIMARY KEY |
| niveau | VARCHAR |
| sexe | VARCHAR |

---
# Modifier une table 6/8
## Modifier le nom d'une colonne
```
ALTER TABLE Personne CHANGE niveau level INTEGER(100);
```

| COLONNE | TYPE |
| ------- | ------- |
| nom | VARCHAR |
| prenom | VARCHAR |
| age | INTEGER |
| id | INTEGER PRIMARY KEY |
| level | INTEGER |
| sexe | VARCHAR |

---
# Modifier une table 7/8
## Supprimer une colonne
```
ALTER TABLE Personne DROP sexe;
```

## Supprimer une contrainte
```
ALTER TABLE Personne DROP PRIMARY KEY;
```

| COLONNE | TYPE    |
| ------- | ------- |
| nom     | VARCHAR |
| prenom  | VARCHAR |
| age     | INTEGER |
| id      | INTEGER |
| level   | INTEGER |

---
# Modifier une table 8/8
## Modifier une contrainte
En deux étapes : Un drop puis un add !
```
ALTER TABLE Personne
 DROP PRIMARY KEY,
 ADD CONSTRAINT pk_nom PRIMARY KEY(nom);
```
| COLONNE | TYPE |
| ------- | ------- |
| nom | VARCHAR PRIMARY KEY |
| prenom | VARCHAR |
| age | INTEGER |
| id | INTEGER |
| level | INTEGER |

---
# Supprimer une table 1/3
## Attention
```
DROP TABLE < nom de la table >
```
**ATTENTION** Certaines contraintes peuvent interdire la destruction d'une table

---
# Supprimer une table 2/3
## Conducteur
| COLONNE | TYPE |
| ------- | ------- |
| idC | INTEGER PRIMARY KEY |
| nom | VARCHAR |

## Voiture
| COLONNE | TYPE |
| ------- | ------- |
| idV | INTEGER PRIMARY KEY |
| nom | VARCHAR |
| #idC | INTEGER FOREIGN KEY |

**A votre avis quelle table je dois supprimer en premier ?**

---
# Supprimer une table 3/3

## Quel ordre ?
Il faut d'abord supprimer la voiture puis après le conducteur.

## Pourquoi ? :
- La clé étrangère de conducteur est dans voiture
- Si on supprime le conducteur
- Alors la clé étrangère dans voiture n'aura plus de sens

---
# Autres
## Renommer une table
```sql
ALTER TABLE table_name RENAME TO new_table_name;
```
## Verifier la valeur d'un attribut
```sql
CREATE TABLE nomTable(
	latitude DECIMAL(12,10) NOT NULL,
	CONSTRAINT check_latitude CHECK (latitude BETWEEN -90 AND 90),
);
```
## Rendre un attribut unique
```sql
CREATE TABLE nomTable(
    monAttribut DECIMAL(12,10) UNIQUE
);

```
---
# Conclusion
## Analyse
- Étudier la donnée
- Éviter les redondances
- Avoir du bon sens

## SQL
Aboutissement de l'analyse

---
# Conclusion
## Vocabulaire
#### MCD
<img src="img/mcdtables.png" width=100/>

#### MLD
Personne(id,nom,prenom)

#### LDD
```sql
CREATE TABLE Personne(
    id INT(100) primary key,
    nom VARCHAR(15),
    prenom VARCHAR(15)
);
```