Analyse et Conception de Systmes Informatiques ACSI Rappel

Analyse et Conception de Systèmes Informatiques (ACSI)

Rappel Entité Identifiant Occurences Champs Nom Identifiant Champ_1 Champ_2 Champ_N id_val_a ch_1_a ch_2_a ch_N_a id_val_b ch_1_b ch_2_b ch_N_b id_val_c ch_1_c ch_2_c ch_N_c id_val_d ch_1_d ch_2_d ch_N_d id_val_e ch_1_e ch_2_e ch_N_e 2

L'association un à un Est caractérisée par des cardinalités maximales à 1. Présentation Entite_1 Entite_2 Lie Min, 1 Attributs Ces associations sont à proscrire dans un modèle E-A car les 2 entités peuvent être fusionnées. Exemple Personne Profession 0, 1 Exerce 0, 1 date_origine 3

L'association un à plusieurs Est caractérisée par une cardinalité maximale à 1 et une cardinalité maximale à n. Présentation Entite_1 Entite_2 Lie Min, n Min, 1 Attributs Ici, une personne exerce au plus 1 profession et une profession peut être exercée par aucune ou plusieurs personnes. Exemple Personne Profession 0, 1 Exerce 0, n date_origine 4

L'association plusieurs à plusieurs Est caractérisée par des cardinalités maximales à n. Ici, une personne exerce aucune ou plusieurs professions et une profession peut être exercée par aucune ou plusieurs personnes. Présentation Entite_1 Entite_2 Lie Min, n Attributs Exemple Personne Profession 0, n Exerce 0, n date_origine 5

L'association plurielle Deux entités peuvent être liées plusieurs fois, c'est l'association plurielle. Présentation Entite_1 Lie Entite_2 Lie Exemple Personne Exerce Profession Enseigne 6

L'association n-aire Il est possible d'associer autant d'entités que nécessaire. Il est toutefois rare de voir des associations à des degrés supérieurs des ternaires. Présentation Entite_1 Min, Max Lie Min, Max Entite_2 Attributs Min, Max Entite_2 Exemple Personne 0, n Enseigne Jour Heure 0, n Matiere 0, n Universite 7

L'association réflexive Présentation permet d'associer une Entite_1 entité à elle-même. Lie Dans cet exemple, une Exemple personne est parent d'une autre personne. Attributs Personne est_parent 8

Cours 1 : Présentation L'algèbre relationnelle Le modèle entité-relation (E-A) Cours 2 : Du modèle E-A au MPD Le langage SQL Cours 3 : SQL avancé Cours 4 : Méthodes de modélisation Cours 5 : Administration et sécurité 9

Du modèle E-A au MPD Présentation MPD Tables Les types de données usuels Méthodes de transition 10

Présentation Le Modèle Physique de Données est la représentation exacte de la base de données. Le MPD est liée au système utilisé. Un MPD peut être obtenu automatiquement par des outils mais il correspond rarement au besoin. Le MPD peut permettre de créer automatiquement la base de données par des outils en générant le code SQL correspondant. 11

Les tables sont les éléments du MPD. Une table contient des attributs typés. Une table doit contenir une clé primaire permettant d'identifier de façon unique l'occurrence de la table. Une table peut contenir une ou des clé étrangère permettant de la lier à une autre table. Les types de données peuvent varier selon les système de base de données. 12

Les types de données usuels Les champs Numériques (1/2) Type Val min Val max BIT 0 1 TINYINT -128 127 BOOL TRUE FALSE SMALLINT -32768 32767 MEDIUMINT -8388608 8388607 INT -2147483648 2147483647 BIGINT -9, 22337 E+18 SERIAL BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 13

Les types de données usuels Les champs Numériques (2/2) Type Val min Val max FLOAT -3. 402823466 E+38 -1. 175494351 E-38 0 0 1. 175494351 E-38 3. 402823466 E+38 -1. 7976931348623157 E+308 -2. 2250738585072014 E-308 0 0 2. 2250738585072014 E-308 1. 7976931348623157 E+308 DECIMAL(S, D) S<=65 (précision) D<=30 (décimale) FIXED synonyme DECIMAL NUMERIC synonyme DECIMAL DEC synonyme DECIMAL DOUBLE 14

Les types de données usuels Les champs alpha-numériques et binaires Type Longueur max TINYBLOB 256 Type Longueur max BLOB 65 536 (64 Ko) CHAR(S) 255 (selon version) MEDIUMBLOB 16 777 216 (16 Mo) VARCHAR(S) 255 (selon version) LONGBLOB 4 294 967 296 (4 Go) BINARY(S) 255 (selon version) TINYTEXT 256 VARBINARY(S) 255 (selon version) TEXT 65 536 (64 Ko) MEDIUMTEXT 16 777 216 (16 Mo) LONGTEXT 4 294 967 296 (4 Go) 15

Les types de données usuels Les champs date et heure Type Val min Val max DATETIME '1000 -01 -01 00: 00' '9999 -12 -31 23: 59' DATE '1000 -01 -01' '9999 -12 -31' TIMESTAMP '1970 -01 -01 00: 01' '2038 -01 -19 03: 14: 07' TIME '-838: 59' '838: 59' YEAR 1901 2155 16

Les clés On appelle clé primaire le ou les champs permettant d'identifier de façon unique une occurrence d'une table. On appelle clé étrangère le ou les champs permettant de lier une table à une autre. La clé étrangère est liée à la clé primaire de l'autre table. 17

Méthodes de transition L'entité devient une table. L'identifiant devient la clé primaire. Une relation 1 à 1 ou 1 à plusieurs se traduit par une clé étrangère dans la table ayant la cardinalité maximale à 1. Une relation plusieurs à plusieurs devient une table dont la clé primaire peut être l'association des clés primaires des tables liées. 18

Méthodes de transition Contrairement à la règle dans un modèle E-A, un même champs peut exister dans plusieurs tables. Ceci est nécessaire pour les clés étrangères permettant de lier les tables entre elles. Cela peut également être utilisé pour des raisons d'optimisation ou pour séparer des données pour lesquelles il est plus logique d'avoir des tables distinctes d'un point de vue fonctionnel. 19

Exemple (1/3) Le modèle E-A suivant : Personne ID Nom Prenom Adresse 0, 1 Reside 0, N ID Voie CP Ville Pourra s'interpréter : Personne ID int Nom varchar(30) Prenom varchar(30) Adress# int Adresse ID Voie CP Ville int varchar(200) int varchar(50) 20

Exemple (2/3) Le modèle E-A suivant : Personne ID Nom Prenom Adresse 0, N Réside Date_emmen 0, N ID Voie CP Ville Pourra s'interpréter : Personne ID int Nom varchar(30) Prenom varchar(30) Reside ID_personne#int ID_adresse# date_emmen Adresse int date ID Voie CP Ville int varchar(200) int varchar(50) 21

Exemple (3/3) Le modèle E-A suivant : Personne ID Nom Prenom Adresse 0, N Réside 0, N date_debut date_fin ID Voie CP Ville Pourra s'interpréter : Personne ID int Nom varchar(30) Prenom varchar(30) Reside ID ID_personne#int ID_adresse# date_debut date_fin Adresse int date ID Voie CP Ville int varchar(200) int varchar(50) 22

Le langage SQL Présentation Le LDD Création de bases et de tables Suppression et modification de tables Les vues, les triggers et les indexes Le LMD Le Select L'Update, le Delete Les fonctions 23

Présentation Le Structured Query Language est un langage standardisé qui permet d'effectuer des opérations sur des bases de données. Il se compose du langage de définition de données permettant de gérer les structures de la base et du langage de manipulation de données pour interagir avec les données. Attention, certaines syntaxes ou fonctions sont propres au système de base de données utilisé. 24

Le langage de définition de données Les commandes principales sont : - Pour les bases : create, alter, drop - Pour les tables : create, alter, rename, drop - Pour les indexes : create, drop - La gestion des vues, des triggers, des events Ces fonctions sont assez peu utilisées car des outils tels que php. My. Admin permettent d'effectuer directement ces opérations. 25

Pour les bases (1/3) Il est possible de créer autant de bases de données que nécessaires. Généralement, une base regroupe toutes les données nécessaires pour un besoin fonctionnel précis. En clair, on peut résumer : une application, une base de données. L'interaction entre les bases de données est possible mais alourdit la syntaxe SQL. 26

Pour les bases (2/3) Création d'une base de données : CREATE DATABASE [IF NOT EXISTS] db_name [create_specification] Les spécifications permettent notamment de définir le char-set de la base. 27

Pour les bases (3/3) Suppression d'une base de données : DROP DATABASE [IF EXISTS] db_name Modification d'une base de données : ALTER DATABASE db_name alter_specification [, alter_specification]. . . 28

Pour les tables Création d'une table : CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition, . . . )] [table_options] create_definition représente la liste des champs avec leur type et leurs éventuelles options. table_option permet de préciser notamment le char-set ainsi que le moteur de la table (ENGINE). 29

Pour les tables (create) La liste des champs doit être précisée : col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [[PRIMARY] KEY] [reference_definition] Seuls le nom est le type sont obligatoires. Par défaut un champ est défini en NULL. Les champs sont séparés par des virgules. 30

Pour les tables (create) L'option AUTO_INCREMENT permet de confier la gestion du champ par le moteur de base de données. A chaque insertion dans la table, la valeur du champ sera automatiquement incrémentée. Cette option n'est possible que sur des champs de type entier. Le type SERIAL est un raccourci pour définir un champs UNSIGNED BIGINT AUTO_INCREMENT UNIQUE. 31

Pour les tables (create) Les options facultatives de la tables permettent de préciser (en outre) : Le moteur de la table : - My. Isam (par défaut) - Inno. Db (gère les transactions) - Memory (chargée en mémoire) Le char-set utilisé pour le stockage et l'ensemble de règles permettant la comparaisons de caractères dans un jeu (collation), par défaut latin 1_swedish_ci correspondant à ISO-8859. 32

Exemple create table IF NOT EXISTS personne( id int not null primary key auto_increment, nom varchar(100) not null, prenom varchar(100), annee_naiss year default "1950") engine=Inno. DB 33

Exemple create table IF NOT EXISTS personne( nom varchar(100) not null, prenom varchar(100), annee_naiss year default "1950", primary key (nom, prenom), index personne_anne (annee_naiss) ) engine=Inno. DB 34

Pour les tables Modification d'une table : ALTER TABLE tbl_name ADD [COLUMN] column_definition [FIRST | AFTER col_name ] | ADD INDEX [index_name] [index_type] (index_col_name, . . . ) | ADD PRIMARY KEY [index_type] (index_col_name, . . . ) | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} 35

Pour les tables Modification d'une table (suite) : ALTER TABLE tbl_name ADD FOREIGN KEY [index_name] (index_col_name, . . . ) CHANGE [COLUMN] old_col_name column_definition DROP [COLUMN] col_name DROP PRIMARY KEY DROP INDEX index_name DROP FOREIGN KEY fk_symbol 36

Pour les tables Renommage d'une table : RENAME TABLE nom_de_table TO nouveau_nom_de_table Suppression d'une table : DROP TABLE tbl_name Attention, cette action est irréversible, toutes les données contenues dans la table sont évidemment supprimées. 37

Pour les indexes Un indexe permet au moteur d'accéder rapidement à la donnée recherchée. Si vous recherche un champs ayant une valeur donnée et qu'il n'y a pas d'indexe sur ce champ, le moteur devra parcourir toute la table. Les indexes doivent toutefois être utilisés avec parcimonie car ils pénalisent les temps d'insertion et de suppression des données dans la table. Une clé primaire est par définition un indexe unique sur un champ non null. Un indexe unique a ceci de différent qu'il peut éventuellement être null. 38

Pour les indexes Création d'un indexe : CREATE [UNIQUE] INDEX index_name ON tbl_name (col_name [(length)] [ASC | DESC], . . . ) La taille peut être précisée pour des champs varchar par exemple. Elle doit obligatoirement pour les types blob. Suppression d'un indexe : DROP INDEX index_name ON tbl_name 39

Pour les triggers Les triggers sont du code SQL appelé avant une action sur les données de la tables. Cela permet de mettre en place des contrôles sur les données manipulées avant de modifier celles de la table. Par exemple, on peut vérifier que les données que l'on désire insérer répondent à des contrôles d'intégrité (par exemple on désire pouvoir stocker uniquement des dates du 20ème siècle). Les triggers sont à utiliser avec parcimonie car ils ralentissent les actions sur les tables. Ils peuvent également gérer des situations de blocage. 40

Pour les vues Une vue permet de simuler une table par le biais d'une requête de manipulation de données (SELECT). Cela peut s'avérer utile si des données auxquelles ont accède régulièrement se trouve dans plusieurs tables. Le résultat de la requête donne la structure de la vue comme s'il s'agissait d'une table, mais il s'agit en fait de références vers les données. En cas de suppression de données dans la vue, les données physiques dans les tables concernées sont supprimées. 41

Pour les events Il est possible de déclencher une opération SQL à un instant donné. Par exemple, on peut mettre en place l'insertion d'une occurrence dans une table tous les jours à midi. 42

Le langage de manipulation de données Les commandes principales sont : - Insert pour ajouter les données - Update pour modifier les données - Delete pour supprimer les données - Select pour consulter les données 43

La commande INSERT Cette commande permet d'insérer des données dans une table. INSERT [INTO] tbl_name [(col_name, . . . )] VALUES ({expr | DEFAULT}, . . . ) Le nombre de col_name doit correspondre au nombre d'expressions. Le fait de préciser les champs est optionnel mais impose en cas de non indication de donner les expressions de chaque colonne dans l'ordre. 44

La commande INSERT (2/2) Pour champs ayant l'option AUTO_INCREMENT, il est possible soit de ne pas préciser le champ dans la liste, soit de passer la valeur NULL. C'est le système qui se chargera d'attribuer automatiquement une valeur. 45

La commande UPDATE (1/2) Cette commande permet de modifier des données dans une table. UPDATE tbl_name SET col_name 1=expr 1 [, col_name 2=expr 2. . . ] [WHERE where_definition] [LIMIT row_count] Le SET permet d'attribuer une nouvelle valeur au champ. Il est possible de mettre à jour plusieurs champs en même temps. 46

La commande UPDATE (2/2) Le WHERE permet de préciser quelles données on désire mettre à jour. Son fonctionnement sera détaillé avec la commande SELECT. Sans clause WHERE, toutes les données de la table sont mises à jour. La LIMIT permet de limiter le nombre de lignes à modifier. 47

La commande DELETE Cette commande permet de supprimer des données dans une table. DELETE FROM table_name [WHERE where_definition] [LIMIT row_count] Le WHERE permet de préciser quelles données on désire supprimer. Sans clause WHERE, toutes les données de la table sont supprimées. On préfère alors utiliser la commande spéciale TRUNCATE TABLE. 48

La commande SELECT (1/8) Cette commande permet de lire des données dans une table ou plusieurs tables. SELECT [DISTINCT] select_expression, . . . [FROM table_references [WHERE where_definition] [ORDER BY {unsigned_integer | nom_de_colonne} [ASC | DESC] , . . . ] [LIMIT [offset, ] lignes] 49

La commande SELECT (2/8) select_expression Indique la colonne à lire, une constante, ou une valeur calculée. Le DISTINCT permet de ne lire que des valeurs distinctes. Le FROM permet de lister les tables à utiliser dans la recherche des données. Le ORDER BY permet de trier le résultat de la requête (ASC : croissant, DESC décroissant). 50

La commande SELECT (3/8) Exemple : On désire les noms rangés par ordre alphabétique de toutes les personnes Personne ID int Nom varchar(30) Prenom varchar(30) Adress# int qui se prénomme Lisa. SELECT Nom FROM Personne WHERE Prenom = 'Lisa' ODER BY 1 51

La commande SELECT (4/8) Exemple : On désire lire tous les noms et prénoms associés dans un champs séparés Personne ID int Nom varchar(30) Prenom varchar(30) Adress# int par un espace. SELECT concat(Nom, ' ', Prenom) as Gens FROM Personne ODER BY 1 52

La commande SELECT (5/8) Exemple : On désire les ID de toutes les personnes ayant une adresse renseignée. Personne ID int Nom varchar(30) Prenom varchar(30) Adress# int SELECT ID FROM Personne WHERE Adresse# IS NOT NULL 53

La commande SELECT (6/8) Le WHERE permet de préciser les critères de recherche et d'associer les tables entre elles. Tous les opérateurs =, <=>, <, >, !=, >=, <>, BETWEEN, IN, NOT IN, IS NULL, IS NOT NULL, . . . sont supportés. Si l'on désire recherches données contenues dans une table ainsi que dans une autre table liée par le biais d'une clé étrangère, il est indispensable de préciser l'égalité entre les 2 champs. 54

La commande SELECT (7/8) Attention, si toutes les tables listées dans la clause FROM ne sont pas associées dans la clause WHERE, le moteur effectuera un produit cartésien des tables non liées. Ainsi si 3 tables de 500, 1000, et 2500 lignes sont appelées dans le FROM sans association dans la clause WHERE, le résultat sera de : 500 * 1000 * 2500 = 1 250 000 lignes. 55

La commande SELECT (8/8) Exemple : Personne ID int Nom varchar(30) Prenom varchar(30) Adress# int Adresse ID Voie CP Ville int varchar(200) int varchar(50) SELECT Personne. Nom, Adresse. Voie FROM Personne, Adresse WHERE Personne. Adress# = Adresse. ID AND Personne. nom like 'Simps%' 56

La commande INSERT Il est possible d'insérer dans une table des données issues d'une autre requête. INSERT [INTO] tbl_name [(col_name, . . . )] SELECT. . . 57

La commande UPDATE Il est possible de mettre à jour des données en fonction de données d'autres tables : UPDATE tbl_name [, tbl_name. . . ] SET col_name 1=expr 1 [, col_name 2=expr 2. . . ] [WHERE where_definition] C'est toujours la table dont le nom est accolé au mot UPDATE qui est mise à jour. 58

La commande DELETE table_name [, table_name. . . ] FROM table-references [WHERE where_definition] Grâce à cette syntaxe, il est possible de supprimer les données des table_name répondant aux critères données par les clauses WHERE sur les tables données par FROM. 59