Systmes dInformation et Application Anne Universitaire 2018 2019

Systèmes d’Information et Application Année Universitaire 2018 -2019 SIA TD 2 SQL – Le langage de définition des données (LDD) Emmanuel Fruchart

Contact Web: http: //happymaths. pagesperso-orange. fr Email: happy. maths@orange. fr

Descriptif du TD Objectif : Savoir créer la structure d’une base de données à partir d’un schéma relationnel et des types de données fournis. Supports : SQL : le Langage de Définition des Données.

Contenu du TD Rappel de cours SQL – le LDD SQL 0 h 30 Etude de cas Création d’une base de données en SQL à partir d’un schéma relationnel 1 h Application Exercice puis corrigé 0 h 30 TNE 2 Exercice 2 2 h

Présentation Structured Query Language (SQL) Langage de Définition des Données (LDD)

Structured Query Language (SQL) Ø SQL est un langage structuré de requêtes. Ø Evolution de SEQUEL, IBM, années 70, pour accompagner son premier SGBDR (SYSTEM-R). Ø SQL devenu la norme en matière de langage relationnel : § SQL/86 : standard officiel ratifié par comités ISO et ANSI. § SQL/89 : + définition des contraintes de référence. § SQL/92, SQL-2 ou SQL Standard : + connexions et sessions C/S + normalisation types données date et heure + opérateurs intersection, différence, jointure + contraintes d'intégrité + traitement du SQL dynamique + catalogue BDD. Ø SQL/92, SQL Standard = support de ce cours

Structured Query Language (SQL) SQL admet les caractéristiques suivantes : Ø Langage relationnel ensembliste (pas de variable !) Ø Langage à intégration verticale : adapté administrateur BDD, architecte d'applications, développeur. Même gamme de mots réservés Ø Langage à intégration horizontale : commandes qui permettent de : § Définir les données (LDD). § Manipuler les données (LMD). § Contrôler les données (LCD).

Structured Query Language (SQL) SQL peut être : Ø interprété directement (dans le SGBD) Ø incorporé (embedded) dans un langage procédural (type C ou Pascal). Tous les principaux SGBD relationnels (SGBDR) du marché, dont DB 2, Oracle, Informix, SQL Server, Sybase, Ingres ou Access ont adopté SQL. Chaque SGBDR utilise sa propre variante du langage SQL. Mais chaque variante est très proche : SQL est un langage relativement portable.

LDD : Langage de Définition des Données LDD (DDL en anglais) : Le LDD est l'ensemble des commandes permettant de : Ø définir un objet d'une BDD relationnelle. Ø modifier un objet d'une BDD relationnelle. Ø supprimer un objet d'une BDD relationnelle.

Type données SQL : chaîne de caractères Ø SQL Standard introduit 2 types chaînes de caractères : § CHAR(n) : longueur fixe. § VARCHAR(n) : longueur variable. Ø Privilégier CHAR si contenu colonne imprévisible, mais taille maximale connue. VARCHAR si utilisation de taille maximale de la colonne fait exception. Ø Constantes chaînes entourées par des apostrophes (parfois guillemets). Si déjà une apostrophe, on la double : 'L''eau vive’. Ø Consulter la doc. du SGBD pour n maximum et portabilité. Exemple : 16383 caractères max.

Type données SQL : numérique Ø SQL Standard introduit les types numériques : § SMALLINT : entier signé entre – 32768 et 32767. § INTEGER : entier signé entre – 231 et 231 - 1. § NUMERIC(p, d) ou DECIMAL(p, d) : nombre décimal à p chiffres significatifs dont d après virgule. § REAL : réel à simple précision, >= 7 chiffres significatifs. § FLOAT ou DOUBLE PRECISION : réel à double précision, >= 15 chiffres significatifs. Ø Constantes numériques : – 5 ou 2. 5 ou 5. 4 E-5. Pas de ‘ ’ ! Ø Consulter la doc. du SGBD pour vérifier la portabilité.

Type données SQL : temporel Ø SQL Standard introduit les types temporels : § DATE : pour une date simple, 2 chiffres pour le jour, 2 pour le mois et 4 pour l'année. § TIME : pour une heure, au format "heure minute seconde". Les secondes pouvant contenir des décimales. § TIMESTAMP : pour un moment précis, une date avec heures, minutes et secondes avec 6 chiffres après la virgule. § INTERVAL : s'emploie pour un intervalle de temps. Ø Constantes temporelles : dépendent des options que l'administrateur a choisies à la création de la BDD ('01/01/2000‘ mode français ou '2000/01/01‘ mode anglais). ØConsulter la doc. du SGBD pour vérifier la portabilité.

Type données SQL : binaire Ø SQL Standard introduit les types binaires : § BIT : type binaire de longueur constante. § BIT VARYING : type binaire de longueur variable. Ø Ces types peuvent servir pour l'enregistrement de données type images et sons, mais nous ne les utiliserons pas dans le cadre de ce cours. ØConsulter la doc. du SGBD pour vérifier la portabilité.

LDD : Création et suppression d’une base Ø Création d'une base de données Instruction standard SQL CREATE DATABASE nom_base; Ø Suppression d'une base de données Instruction standard SQL DROP DATABASE nom_base; Attention, sous certains S. G. B. D. , les bases de données sont en fait créés par l'administrateur au moment de la configuration du S. G. B. D. par des outils dédiés, de sorte que l'instruction ci-dessus est obsolète. L'utilisateur, lors de la connexion à une base de données, spécifie son nom d'utilisateur, son mot de passe, et l'alias de base utilisée. Il peut alors accéder alors aux objets de cette base de données.

LDD : Création d’une table Ø Création d'une table de base de données Instruction standard SQL CREATE TABLE nom_table ( nom_col type_col [DEFAULT valeur] [contrainte_col], ………… […………………. ] [………………. . ], nom_col type_col [DEFAULT valeur] [contrainte_col], CONSTRAINT ………………. . CONSTRAINT ); nom_contrainte ………………… nom_contrainte_table, …………………, contrainte_table

LDD : Création d’une table et conventions Ø Paramètres entre crochets facultatifs donc optionnels. Ø Attention syntaxe précise (ne pas oublier de virgules, ni en mettre en trop). Ø Noms des colonnes : noms communs, sans caractères spéciaux, ni accents. Ø Valeurs par défaut : constantes du type de la colonne (2000 pour integer, 500. 00 pour float, ‘Titi' pour char). Ø Noms des contraintes de table : noms communs, sans caractères spéciaux, ni accents (exemple : PKperiode pour clé primaire de la table periode).

LDD : Contraintes de colonne Ø Les contraintes de colonne possibles sont : § PRIMARY KEY : colonne clé primaire de la table. § NOT NULL : valeurs colonne doivent être renseignées. § UNIQUE : interdit présence de doublons dans colonne. § CHECK (expr_cond_col) : les données de la colonne doivent respecter une certaine condition portant sur la colonne uniquement. § REFERENCES nom_table_ref (nom_col_ref) : colonne reliée à clé étrangère qui est le champ nom_col_ref de la table nom_table_ref.

LDD : Contraintes de tables Ø Les contraintes de table possibles sont : § PRIMARY KEY(nom_col 1, nom_col 2, … ) : clé primaire de la table formée des champs nom_col 1, nom_col 2, … § UNIQUE(nom_col 1, nom_col 2, … ) : interdit les doublons dans les valeurs des t - uplets (valeur_col 1, valeur_col 2, …). § CHECK (expr_cond_table) : les données de la table doivent respecter une certaine condition portant sur tout ou partie des colonnes de la table. § FOREIGN KEY(nom_col) REFERENCES nom_table_ref (nom_col_ref) : colonne reliée à une clé étrangère qui est le champ nom_col_ref de la table nom_table_ref.

LDD : Mise à jour d’une table Ø Mise à jour d'une table de base de données Instruction standard SQL ALTER TABLE nom_table ordre_de_mise_a_jour; Ordres de mise à jour possibles § ADD COLUMN nom_col type_col [DEFAULT valeur] [contrainte_col] § ADD CONSTRAINT nom_contrainte_tbl § ALTER nom_col type_col [DEFAULT valeur] [contrainte_col] § DROP COLUMN nom_col § DROP CONSTRAINT nom_contrainte_tbl

LDD : Suppression d’une table Ø Suppression d'une table de base de données Instruction standard SQL DROP TABLE nom_table;

Etude de cas : schéma relationnel BDCours

Etude de cas : types de données BDCours annee id_annee desc_annee nb_filles nb_garcons INTEGER CHAR(50) INTEGER periode id_periode desc_periode CHAR(2) CHAR(100) classe id_classe desc_classe CHAR(4) CHAR(100) matiere id_matiere desc_matiere CHAR(4) CHAR(100)

Etude de cas : types de données BDCours professeur et professeur_rem id_professeur CHAR(8) nom CHAR(50) prenom CHAR(50) sexe CHAR(1) date_naissance DATE id_matiere CHAR(4) enseignement id_annee id_periode id_classe id_matiere id_professeur moyenne_classe INTEGER CHAR(2) CHAR(4) CHAR(8) FLOAT

Etude de cas : création base BDCours create database bdcours;

Etude de cas : création table annee create table annee ( id_annee desc_annee nb_filles nb_garconstraint ); integer char(50), integer, PKannee CKannee NOT NULL, PRIMARY KEY(id_annee), CHECK(id_annee >= 2014 AND id_annee < 2500)

Etude de cas : tables periode, table, matiere create table periode (id_periode char(2) desc_periode char(100), constraint PKperiode create table classe (id_classe char(4) desc_classe char(100), constraint PKclasse create table matiere (id_matiere char(4) desc_matiere char(100), constraint PKmatiere NOT NULL, PRIMARY KEY(id_periode)); NOT NULL, PRIMARY KEY(id_classe)); NOT NULL, PRIMARY KEY(id_matiere));

Etude de cas : création table professeur create table professeur ( id_professeur char(8) nom char(50) prenom char(50) sexe char(1) date_naissance date, id_matiere char(4) constraint PKprof constraint FKprofmat constraint ); NOT NULL, NOT NULL, PRIMARY KEY(id_professeur), FOREIGN KEY(id_matiere) REFERENCES MATIERE (id_matiere), CKprofsexe CHECK(sexe IN ('H', 'F'))

Etude de cas : création tbl professeur_rem create table professeur_rem ( id_professeur char(8) nom char(50) prenom char(50) sexe char(1) date_naissance date, id_matiere char(4) constraint PKprem constraint FKpremmat constraint ); NOT NULL, NOT NULL, PRIMARY KEY(id_professeur), FOREIGN KEY(id_matiere) REFERENCES MATIERE (id_matiere), CKpremsexe CHECK(sexe IN ('H', 'F'))

Etude de cas : création table enseignement create table enseignement (id_annee integer NOT NULL, id_periode char(2) NOT NULL, id_classe char(4) NOT NULL, id_matiere char(4) NOT NULL, id_professeur char(8) NOT NULL, moyenne_classe float, constraint PKens PRIMARY KEY(id_annee, id_periode, id_classe, id_matiere), constraint FKensann FOREIGN KEY(id_annee) REFERENCES ANNEE(id_annee), constraint FKensper FOREIGN KEY(id_periode) REFERENCES PERIODE(id_periode), constraint FKenscla FOREIGN KEY(id_classe) REFERENCES CLASSE(id_classe), constraint FKensmat FOREIGN KEY(id_matiere) REFERENCES MATIERE(id_matiere), constraint FKensprof FOREIGN KEY(id_professeur) REFERENCES PROFESSEUR(id_professeur), constraint CKensmoy CHECK(moyenne_classe BETWEEN 0 AND 20));