# Conception et modélisation des données

MERISE : méthode franco-française des années 80 qui vise à aider, simplifier, formaliser l'analyse des besoins d'un système d'information. C'est trois étapes son proposées : modèle conceptuel, modèle logique puis un modèle physique.

Trois étapes :
- **Conceptuel** : analyse le besoin de l'organisation, ses activités (comptabilité, commercial, juridique...), identifier les entités / objets (entités métiers, objets métiers, objets de gestion). Ces entités ont des propriétés et généralement des associations entre elles. Décrire les entités avec leurs propriétés / attributs et comment ces entités sont en association. On ne va retenir comme propriétés que celles qui nous intéressent dans notre contexte. A ce stade on ne se préoccupe pas du type de base de données et encore moins de l'éditeur de la base de données, on se concentre sur les besoins.

- **Logique** : on a fait le choix du type de base de données, mais pas de l'éditeur. Dans notre contexte une base de données de type relationnel. On va appliquer un ensemble de règles de transformation du MCD (Modèle Conceptuel des Données) au MLD-R (Modèle Logique des Données Relationnel) qui sont communes à ce type de base de données. Dans les MCD et MLD-R on suit un ensemble de règles de normalisation (toute entité doit avoir un identifiant).

- **Physique** : on a fait le choix de l'éditeur de la base de données relationnelle, on connit ses particularités, on peut donc écrire dans le langage SQL de la base cible le script (instructions SQL) pour créer physiquement le modèle dans le système de stockage (fichiers). On connait les types précis de l'éditeur (boolean PostgreSQL vs int 0-1 MySQL par exemple). Au stade du MPD on peut dénormaliser son modèle, notamment motivé par des problématiques de performance (ajout d'une colonne calculée pour éviter/limiter certaines jointures coûteuses)

Cette démarche vise à réduire au maximum, voire éviter les redondances d'information et les incohérences de données (doublons...)

## Le MCD

### Bonnes pratiques
- Utiliser le langage naturel, reprend le vocabulaire métier
- Utiliser la langue (français vs anglais) qui parle à tous les acteurs du projet
- L'entité est désignée par un nom au pluriel ou au singulier (exemples Commande vs Commandes), conventionnellement au pluriel
- Une entité représente une abstraction d'un objet de gestion qui est d'intérêt pour l'organisation/l'activité. Au lieu d'avoir une entité pour chaque objet "réel" on procède à un regroupement des caractéristiques communes pour en faire une entité qui permet de représenter toutes les occurences de ce "type" d'objets (par exemple une entité Factures permet de représenter n'importe quelle facture), on dit qu'une occurence est un individu (une facture) d'une population (l'entité Factures)

### Règles à respecter sur les entités
- Les noms sont uniques dans un MCD
- Chaque entité doit avoir au moins une propriété, qui sera son identifiant
- Chaque entité doit avoir un identifiant unique et discriminant. Il sert à discriminer chaque individu (le pays France) de la population (l'entité Pays). Le nom de l'identifiant doit être unique dans un même MCD
- On ne se préoccupe pas des cnontraintes, règles de gestion ou encore les types (sauf à être générique : texte, alphanumérique, oui/non, date...)

### Associations
- Les noms sont des verbes (soit à l'infinitif soit conjugués ce qui est préféré pour aider à lire le sens de l'association)
- Les verbes sont uniques dans un MCD
- Les associations sont le lien sémantique entre entités, bien choisir ses verbes et éviter les verbes "génériques" (être, avoir, posséder, contenir...)
- Les associations n'ont pas d'identifiant mais peuvent avoir des propriétés (exemple de la quanité de produits commandés dans l'association entre Commandes et Produits)
- Les associations ont toutes des cardinalités, des 2 côtés de l'association. Une cardinalité a un minimum et un maximum, indique le nombre de fois au mininum et au maximum qu'une association peut exsiter entre les entités

## Le MLD-R
Le MLD est la traduction du MCD en MLD en respectant des règles de traduction. On se préoccupe de la manière dont les données vont être stockées, plus précisément où. On tient compte du fait que le type de base de données sera le relationnel, des données stockées dans des tables en relation.

### Règles de traduction
- On introduit des conventions de nommage de base de données en spécifiant des noms logiques et non plus des noms conceptuels. On peut choisir les minuscules ou majuscules, le caractère souligné comme séparateur de mots
- Les entités deviennent des tables
- Les propriétés deviennent des colonnes
- Les identifiants (discriminants) deviennent des clefs primaires
- Les associations deviennent des clefs étrangères :
- On retient les valeurs maximums des cardinalités
- Les associations où les maximums sont 1/n, la clef étrangère glisse dans la table du côté du 1
- Les associations où les maximums sont n/n donnent naissance à une table d'association (ou de jointure) pour stocker toutes les combinaisons possibles
- Pour les tables d'association, le nom peut être basé sur le verbe de l'association ou une concaténation des tables en relations en séparant par un caractère souligné

## Le MPD
Le MPD est la traduction du MLD-R en script SQL permettant d'être exécuté et créer physiquement le schéma de la base de données dans le SGBD-R cicle (Système de Gestion de Base de Données Relationnelles).
On peut, à ce stade, dénormaliser le modèle en veillant à respecter les besoins fonctionnels, cette dénormalisation est généralement motivée par des optimisation de performance (ajout de colonnes calculées, introduction de clef primaires numériques, ajout d'index...)
Le MPD est livré dans un script SQL qui, par convention, peut indiquer le type d'instructions contenu dans le fichier en spécifiant "ddl", par exemple `schema.ddl.sql`. DDL = Data Definition Language, une sous catégorie de SQL, pas un langage à part, indique que les instructions SQL sont de type DDL, en d'autres termes de modification de la structure de la base de données par opposition aux instructions de manipulation de données.

### Exemple de MPD avant dénormalisation

DROP TABLE IF EXISTS 
	t_skills, t_developers, t_accounts, t_languages, t_roles;

CREATE TABLE t_languages (
	code char(5),
	"name" varchar(50) NOT NULL,
	CONSTRAINT t_languages_pkey PRIMARY KEY (code)
);

CREATE TABLE t_roles (
	code varchar(20),
	"name" varchar(50) NOT NULL,
	CONSTRAINT t_roles_pkey PRIMARY KEY (code)
);

CREATE TABLE t_accounts (
	username varchar(255),
	"password" char(60) NOT NULL,
	role_code varchar(20) NOT NULL, -- même type que dans t_roles
	CONSTRAINT t_accounts_pkey PRIMARY KEY (username),
	CONSTRAINT t_accounts_t_roles_fkey
		FOREIGN KEY (role_code)
		REFERENCES t_roles (code);

CREATE TABLE t_developers (
	registration_number char(7),
	firstname varchar(200) NOT NULL,
	lastname varchar(200) NOT NULL,
	account_username varchar(255) NOT NULL,
	CONSTRAINT t_developers_pkey PRIMARY KEY (registration_number),
	CONSTRAINT t_developers_account_username_ukey UNIQUE (account_username), -- relation "one to one", doit être unique
	CONSTRAINT t_developers_t_accounts_fkey
		FOREIGN KEY (account_username)
		REFERENCES t_accounts (username)
);

CREATE TABLE t_skills (
	developer_registration_number char(7),
	language_code char(5),
	"level" varchar(12) NOT NULL,
	CONSTRAINT t_skills_pkey PRIMARY KEY (developer_registration_number, language_code),
	CONSTRAINT t_skills_t_developers_fkey
		FOREIGN KEY (developer_registration_number)
		REFERENCES t_developers (registration_number),
	CONSTRAINT t_skills_t_languages_fkey
		FOREIGN KEY (language_code)
		REFERENCES t_languages (code)
);

### Exemple de MPD après dénormalisation

DROP TABLE IF EXISTS 
	t_skills, t_developers, t_accounts, t_languages, t_roles;

CREATE TABLE t_languages (
	id serial, -- introduction d'un id "technique" pour la performance des jointures
	code char(5) NOT NULL, -- le discriminant reste un identifiant mais non primaire, il devient une clef secondaire
	"name" varchar(50) NOT NULL,
	CONSTRAINT t_languages_pkey PRIMARY KEY (id), -- l'id "technique" devient la clef primaire
	CONSTRAINT t_languages_code_ukey UNIQUE (code) -- garantir l'unicité du discriminant
);

CREATE TABLE t_roles (
	id serial,
	code varchar(20) NOT NULL,
	"name" varchar(50) NOT NULL,
	CONSTRAINT t_roles_pkey PRIMARY KEY (id),
	CONSTRAINT t_roles_code_ukey UNIQUE (code)
);

CREATE TABLE t_accounts (
	id serial,
	username varchar(255) NOT NULL,
	"password" char(60) NOT NULL,
	role_id integer NOT NULL,
	CONSTRAINT t_accounts_pkey PRIMARY KEY (id),
	CONSTRAINT t_accounts_username_ukey UNIQUE (username),
	CONSTRAINT t_accounts_t_roles_fkey
		FOREIGN KEY (role_id)
		REFERENCES t_roles (id) -- les clefs étrangères référencent désormais les id "technique" qui sont les clefs primaires des tables référencées
;

CREATE TABLE t_developers (
	id serial,
	registration_number char(7) NOT NULL,
	firstname varchar(200) NOT NULL,
	lastname varchar(200) NOT NULL,
	account_id integer NOT NULL,
    skills smallint NOT NULL, -- colonne calculée (nombre de compétences)
	CONSTRAINT t_developers_pkey PRIMARY KEY (id),
	CONSTRAINT t_developers_registration_number_ukey UNIQUE (registration_number),
	CONSTRAINT t_developers_account_id_ukey UNIQUE (account_id),
	CONSTRAINT t_developers_t_accounts_fkey
		FOREIGN KEY (account_id)
		REFERENCES t_accounts (id)
);

CREATE TABLE t_skills (
	id serial,
	developer_id integer NOT NULL,
	language_id integer NOT NULL,
	"level" varchar(12) NOT NULL,
	CONSTRAINT t_skills_pkey PRIMARY KEY (id),
	CONSTRAINT t_skills_developer_language_ukey UNIQUE (developer_id, language_id),
	CONSTRAINT t_skills_t_developers_fkey
		FOREIGN KEY (developer_id)
		REFERENCES t_developers (id),
	CONSTRAINT t_skills_t_languages_fkey
		FOREIGN KEY (language_id)
		REFERENCES t_languages (id)
);

## Insertion de données référentielles et de test
- Données référentielles : des données qui sont généralement stables, ne changent que très peu et/ou ne nécessitent pas de fonctionnalités d'administration pour les gérer (créer, modifier, supprimer). Exemples typiques : pays, devises, langues, couleurs, tailles... Ces données doivent généralement être insérées après la mise en place de la base de données lors du premier déploiement
- Données de test : des données pour l'environnement de développement et celui de test

Il faut séparer les deux dans deux fichiers SQL de type "DML" (Data Manipulation Language).

### Exemple de script de données référentielles

DELETE FROM t_languages;
DELETE FROM t_roles;

INSERT INTO t_languages (code, "name") VALUES 
	('CD001', 'Java'), ('CD002', 'JavaScript'), ('CD003', 'HTML'), ('CD004', 'CSS'), ('CD005', 'Python');

INSERT INTO t_roles (code, "name") VALUES 
	('ROLE_DEV', 'Developer'), ('ROLE_MODERATOR', 'Moderator');

### Exemple de script de données de test

DELETE FROM t_skills;
DELETE FROM t_developers;
DELETE FROM t_accounts;
	
INSERT INTO t_accounts (username, "password", role_id) VALUES 
	('[e-mail]', '$2a$12$Ya5Ah0.3BSj809H14XuUtu1gsQup2u3bLCWzk1fSfMf765WuzWWla', 
		(SELECT id FROM t_roles r WHERE r.code = 'ROLE_DEV')),
	('[e-mail]', '$2a$12$p0mND/dx4jLtE0aRIT6bnuPb.HtmhTfVOJQKdHuN/jbAnJZAoTzkO', 
		(SELECT id FROM t_roles r WHERE r.code = 'ROLE_MODERATOR'));

INSERT INTO t_developers (registration_number, firstname, lastname, account_id) VALUES 
	('a411665', 'Toto', 'DUPONT', (SELECT id FROM t_accounts a WHERE a.username = '[e-mail]')),
	('a215698', 'Foo', 'DURAND', (SELECT id FROM t_accounts a WHERE a.username = '[e-mail]'));

INSERT INTO t_skills (developer_id, language_id, "level") VALUES 
	((SELECT id FROM t_developers d WHERE d.registration_number = 'a411665'), 
		(SELECT id FROM t_languages l WHERE l.code = 'CD001'), 'NOOB'),
	((SELECT id FROM t_developers d WHERE d.registration_number = 'a411665'), 
		(SELECT id FROM t_languages l WHERE l.code = 'CD002'), 'APPRENTICE'),
	((SELECT id FROM t_developers d WHERE d.registration_number = 'a411665'), 
		(SELECT id FROM t_languages l WHERE l.code = 'CD003'), 'NORMAL'),
	((SELECT id FROM t_developers d WHERE d.registration_number = 'a215698'), 
	(SELECT id FROM t_languages l WHERE l.code = 'CD001'), 'MOUNT_STUPID');