Slides-BD-2A-EMSI-2023-Nassar.pdf

Bases de données EMSI-Rabat 2ème ANNEES PREPARATOIRES AUX CYCLES D’INGENIEUR « IIR-IAII-GI » 2022-2023 Prof. Mahmoud Nassar Ecole Nationale Supérieure d’Informatique et d’Analyse des Systèmes - Rabat - Plan 1. Introduction aux Bases de données 2. Modèle relationnel et l’Algèbre relationnelle 3. Langage SQL 2 Introduction aux Bases de données SYSTEME D’INFORMATION Environnement basé sur un système de fichiers - plusieurs fichiers - plusieurs programmes créés en différentes dates, avec éventuellement différents langages 3 I N C O N V E N I E N T S Redondance et inconsistance même information répliquée dans plusieurs fichiers PROGRAMMES UTILISATEURS FICHIERS D’APPLICATION Enregistrement Programme d’application 1 Données Programme D E G d’application 2 D F G K Programme d’application 3 E H Programme d’application 4 I J TRI Programme D E G H K d’application 5 H J Pour chaque nouvelle application, un programmeur crée un nouveau fichier. Une grande installation à des centaines ou milliers de fichiers avec une grande redondance des données. - Accroissement du volume global des données - Risque d’inconsistance des données : informations sur plusieurs fichiers, non « rafraîchies » au même moment. 4 Difficultés d’accès aux données Les besoins occasionnels ne peuvent être satisfaits de façon efficace, ce qui nuit donc à l’efficacité du système, Utilise un langage de programmation classique Directeur Analyste Personnel Programmeur de l’informatique système Conception Prog. Test Cadre supérieur ou Directeur Semaines ou mois Maintenance programme d’application 1 D F G K programme d’application 2 E H programme d’application 3 Un fichier finit par être utilisé par des applications multiples. Une programme restructuration de ce fichier d’application 4 provoquera une réaction en chaîne programme de modifications dans d’autres d’application 5 programmes d’applications. 5 Difficultés de partage de données Supposons un environnement multi-utilisateurs, Utilisateur-1 Utilisateur-2 compte=500 Lire compte t1 _ t2 Lire compte compte:=compte-50 t3 compte:=compte-50 Écrire compte t4 _ _ t5 Ecrire compte Problèmes de sécurité Les données stockées sur fichiers doivent être protégées contre les accès non autorisés, inaccessible fichier paie accessible Caissiers Service paie accessible fichier comptes inaccessible Problèmes d’intégrité Les données doivent satisfaire certaines contraintes Exemples : - Solde >= 25 - Un client référencé dans fichier commandes doit figurer d’abord dans fichier clients 6 Nécessité donc de modules spécifiques chargés de respecter les contraintes au sein des diverses applications BASE DE DONNEES Une base de données est une collection cohérente de données reliées entre elles, stockées ensemble, aussi peu redondantes que possible pour être utilisées par une ou plusieurs applications d’une façon optimale, Les données sont stockées de façon à être indépendantes des programmes qui les utilisent, JAMES MARTIN 7 SYSTEME DE GESTION DE BASE DE DONNEES Logiciel qui gère un ensemble de fichiers qui constituent la BD et qui permet à plusieurs utilisateurs de stocker et d’extraire des données de ces fichiers, dans des conditions d’intégrité et de confidentialité, UTILISATEUR DESCRIPTION INTERROGATION ACCES CONFIDENTALITE INTEGRITE DES DONNEES & M.A.J CONCURRENT Exemples de SGBD : - Oracle - MySQL - Microsoft SQL Server SGBD - DB2 (IBM) - Informix (IBM) - Sybase - MongoDB - PostgreSQL - Microsoft Access BASE DE DONNEES 8 - Etc. ARCHITECTURE D’UNE BASE DE DONNEES Le SGBD présente la BD sous trois niveaux (niveaux de description de données : architecture ANSI / SPARC) : - niveau interne (physique) - niveau conceptuel (logique) - niveau externe Niveau Schéma Schéma Schéma externe externe externe externe Niveau schéma conceptuel conceptuel (logique) (logique) SGBD Niveau interne Schéma interne (physique) BD 9 Niveau conceptuel La description des données porte ici essentiellement sur l’aspect sémantique : - le contenu global de la BD (les entités) - les liens entre les données (les relations) - les règles de contrôle, de sécurité et d’intégrité, Niveau interne Il décrit la structure physique des données : mode de stockage de ces données sur support externe et la correspondance avec la structure logique : - description de l’enregistrement interne - spécification des index, pointeurs,… - séquence physique des enregistrements Niveau externe Correspond aux différentes vues que vont avoir les utilisateurs sur la BD. Les utilisateurs n’étant pas concernés par toutes les données de la BD, un SGBD présente les données qu’il gère sous plusieurs vues différentes, simplifiant ainsi l’interaction utilisateur- BD. 10 O B J E C T I F D E S S G B D L’objectif essentiel des SGBD est de simplifier et faciliter l’exploitation des données tout en assurant un degré de performances satisfaisant : -Indépendance programmes / données La manipulation des données et leur organisation sur support externe sont deux aspects indépendants, Accès aux données Manipulation Programme d’application -Indépendance physique - indépendance par rapport aux mécanismes de stockage de bas niveau (spécification blocs, cylindres, etc…) - indépendance par rapport à la composition et à la séquence des champs d’un enregistrement : permettre de changer le schéma physique sans affecter le schéma logique et les programmes d’exploitation -Indépendance logique - indépendance par rapport à l’architecture logique de la BD, quand elle doit évoluer (ajout de champs ou d’enregistrements) Abstraction des données: masquer les détails de représentation machine pour faciliter le raisonnement au niveau global, Autonomie des données au niveau physique et logique. 11 -Accès par des langages de haut niveau (indépendance programme - méthode d’accès) Un langage de manipulation permettant de : Personnel - sélectionner Langage - insérer Cadre supérieur d’interrogation ou Directeur de B.D - modifier - supprimer Minutes des données définies, sans spécifier le schéma d’accès à ces données. -Intégrité des données Les données doivent satisfaire certaines contraintes de cohérence (intégrité). Ex : solde >= 25 heures _ travail 500 (dépôt) agence=‘’Bd. Mohamed V’’ ^ position>500 (dépôt) Prédicat : condition faisant intervenir attributs et constantes et utilisant des opérateurs de comparaison. Les conditions peuvent être composées à l’aide des opérateurs ^, v (et, ou) 27 Projection a1, a2, · · ·, an (Rel) Rel Elimine certains attributs et supprime les tuples en double Exemple :  agence, client Agence compte client position Bd. Mohamed V 101 ALAMI 500 Murs sultan 205 TRIKI 700 My Youssef 102 RADI 400 Bd. Mohamed V 305 BADI 500 Bd. Mohamed V 405 ALAMI 1.000.000 = Agence client Bd. Mohamed V ALAMI Murs sultan TRIKI My Youssef RADI Bd. Mohamed V BADI L’argument peut être une expression algébrique  ( (Dépôt)) 28 client agence=‘’Bd. Mohamed V’’ Produit cartésien Rel X Rel Rel Une relation composée des attributs des deux relations et dont les tuples s’obtiennent par les combinaisons des tuples des deux relations. Exemple : Clientèle Client ADRESSE VILLE Triki 2, Rue zerhoune Rabat Alami 5, Rue oujda Rabat Badi 70, Rue sebou Casa Radi 2, Av. My Youssef Rabat Affectation Client employé Alami Amine Triki Kacem Badi Amine Radi Kacem ? Les clients de l’employé Amine et les villes où ils résident r = Affectation X clientèle 29 R =Affectation X clientèle Affectation Affectation Clientèle Clientèle Clientèle.client.employé.client.adresse.ville Alami Amine Triki 2, Rue zerhoune Rabat Alami Amine Alami 5, Rue oujda Rabat Alami Amine Badi 70, Rue sebou Casa Alami Amine Radi 2, Av. My Youssef Rabat Truki Kacem Triki 2, Rue zerhoune Rabat Triki Kacem Alami 5, Rue oujda Rabat Triki Kacem Badi 70, Rue sebou Casa Triki Kacem Radi 2, Av. My Youssef Rabat Badi Amine Triki 2, Rue zerhoune Rabat Badi Amine Alami 5, Rue oujda Rabat Badi Amine Badi 70, Rue sebou Casa Badi Amine Radi 2, Av. My Youssef Rabat Radi Kacem Triki 2, Rue zerhoune Rabat Radi Kacem Alami 5, Rue oujda Rabat Radi Kacem Badi 70, Rue sebou Casa Radi Kacem Radi 2, Av. My Youssef Rabat Affectation.employé =‘’Amine’’ (Affectation X Clientèle) Affectation Affectation Clientèle Clientèle Clientèle.client.employé.client.adresse.ville Alami Amine Triki 2, Rue zerhoune Rabat Alami Amine Alami 5, Rue oujda Rabat Alami Amine Badi 70, Rue sebou Casa Alami Amine Radi 2, Av. My Youssef Rabat Badi Amine Triki 2, Rue zerhoune Rabat Badi Amine Alami 5, Rue oujda Rabat Badi Amine Badi 70, Rue sebou Casa Badi Amine Radi 2, Av. My Youssef Rabat 30 Affectation.client = Clientèle.client ( Affectation.employé = ‘’Amine’’ (Affectation X Clientèle)) Affectation Affectation Clientèle Clientèle Clientèle.client.employé.client.adresse.ville Alami Amine Alami 5,Rue oujda Rabat Badi Amine Badi 70, Rue sebou Casa  ( Affectation.client, clientèle.ville Affectation.client= clientèle.client ( Affectation.employé = ‘’Amine’’ (Affectation X Clientèle))) Affection Clientèle.client.ville Alami Rabat Badi Casa 31 Union Rel U Rel Rel Union ensembliste entre relations de même schéma Exemple : ? Les clients de l’agence ‘’Bd, Mohamed V’’ qui y ont un compte ou en ont obtenu un prêt r1 : ensemble des clients qui ont un compte à l’agence ‘’Bd, Mohamed V’’ :  ( (Dépôt)) client agence= ‘’Bd. Mohamed V’’ r2 : ensemble des clients qui ont un crédit à l’agence ‘’Bd, Mohamed V’’ :  ( (Crédit)) client agence= ‘’Bd. Mohamed V’’ r1 U r2 :  ( (Dépôt)) client agence= ‘’Bd. Mohamed V’’ U  ( (Crédit)) client agence= ‘’Bd. Mohamed V’’ Conditions de validité de r U s : - r et s de même n-arité -Vi, le domaine du ième attribut de r doit être identique à celui du ième attribut de s. 32 Différence Rel – Rel Rel Différence ensembliste entre des relations de même schéma (r- s : ensemble de tuples qui satisfont r sans satisfaire s) R S Exemple : ? Les clients qui ont un compte à l’agence « Bd, Mohamed V » sans y amortir un prêt  ( (Dépôt)) client agence= ‘’Bd. Mohamed V’’ -  ( (Crédit)) client agence= ‘’Bd. Mohamed V’’ 33 Autres opérateurs Intersection Rel ∩ Rel Rel Intersection ensembliste entre relations de même schéma Exemple : ? Les clients qui ont un compte et un prêt à l’agence « Bd, Mohamed V »  ( (Dépôt)) client agence= ‘’Bd. Mohamed V’’ ∩ ( (Crédit)) client agence= ‘’Bd. Mohamed V’’ Remarque : N’est pas une opération élémentaire : r ∩ s = r – (r – s) = s – (s – r) 34 Jointure Rel Rel Rel Composition de deux relations sur un domaine commun Exemple : ? Les clients qui amortissent un prêt et leur lieu de résidence Crédit agence prêt client montant Bd. Mohamed V 15 ALAMI 500 Murs sultan 20 TRIKI 700 My Youssef 10 RADI 400 Clientèle client adresse ville Triki 2,Rue zerhoune Rabat Alami 5,Rue oujda Rabat Badi 70,Rue sebou Casa  Crédit.client, Clientèle.adresse, Clientèle.ville ( (Crédit X Clientèle)) Crédit.client = Clientèle.client’ client adresse ville Triki 2, Rue zerhoune Rabat Alami 5, Rue oujda Rabat 35  (Crédit Clientèle) Crédit.client, Clientèle.adresse, Clientèle.ville La jonction (appelée également équijonction) est une opération binaire combinant le produit cartésien et la sélection basée sur l’attribut commun ( (Crédit X Client)) Crédit. Client = Clientèle. client’ Ξ (Crédit Client) Exemple : ? Les clients qui amortissent un prêt auprès de l’agence « Bd, Mohamed V » et leur lieu de résidence,  Crédit.client, Clientèle.adresse, Clientèle.ville ( (Crédit Client)) agence= ‘’Bd. Mohamed V’’’ Définition formelle : - R,S : deux schémas relationnels, avec : R ∩ S = {a1, a2, · · ·, an} - r(R), s(S) : deux relations de R, S r s=  ( (r x s)) RUS r.al= s. al ^ · · · ^ r. an= s. an si R ∩ S = Ø alors r s=rxs Exemple : 36 ? Les noms et avoirs des agences qui ont des clients de casa Travaux dirigés (N°1) Objectifs : Maitriser les concepts du modèle relationnel et les opérations de l’algèbre relationnelle 37 Travaux dirigés (N°1) Exercice 1 : 38 Travaux dirigés (N°1) - Suite Exercice 2 : Soit le schéma relationnel de base de données suivant : SALLE (Nom, Horaire, Titre) FILM (Titre, Réalisateur, Acteur) PRODUIT (Producteur, Titre) VU (Spectateur, Titre) AIME (Spectateur, Titre) Avec les règles suivantes : Une salle peut proposer plusieurs films au même horaire Plusieurs salles peuvent proposer le même film et éventuellement au même horaire Un réalisateur peut réaliser plusieurs films Un réalisateur peut également être acteur dans son film ou un autre film Un producteur peut produire plusieurs films, de même qu’un film peut être produit par plusieurs producteurs Un spectateur peut aimer plusieurs films, de même qu’il peut voir plusieurs films. On suppose que le nom d’une personne est unique. Questions : 1) Donnez le sens de chaque relation dans ce schéma. 2) Donnez la clé primaire de chaque relation et toutes les clés étrangères qui apparaissent dans ce39 schéma. Travaux dirigés (N°1) - Suite 3) Ecrire les requêtes suivantes en algèbre relationnelle : 3.1) Où et à quelle heure peut-on voir le film " Casa Night " ? 3.2) Quels sont les films réalisés par "Farhati" ? 3.3) Quels sont les acteurs de "Casa Night" ? 3.4) Quels sont les acteurs qui jouent dans des films de "Farhati" ? 3.5) Où peut-on voir un film dans lequel joue "Wali" ? 3.6) Quels sont les acteurs qui ont produit un film ? 3.7) Quels sont les acteurs qui produisent un film dans lequel ils jouent ? 3.8) Où peut-on voir un film dans lequel joue " Wali" après 16 h ? 3.9) Quels acteurs jouent dans tous les films ? 3.10) Quels acteurs jouent dans tous les films de "Farhati" ? 3.11) Qui produit tous les films de "Farhati" ? 3.12) Quels spectateurs voient tous les films ? 3.13) Quels films ne passent dans aucune salle ? 3.14) Qui n’aime aucun film ? 3.15) Quels spectateurs aiment un film qu’ils n’ont pas vu ? 3.16) Quels sont les spectateurs qui aiment tous les films qu’ils voient ? 3.17) Quels sont les producteurs qui voient tous les films qu’ils produisent ? 3.18) Qui produit un film qui ne passe dans aucune salle ? 3.19) Qui ne produit aucun film de "Farhati" ? 3.20) Quels producteurs voient tous les films de "Farhati" ? 3.21) Quels sont les acteurs qui produisent un film qu’ils n’ont pas réalisé ? 40 3.22) Quels sont les producteurs qui ne voient que les films qu’ils produisent ? LANGAGE SQL (Structured Query Language) Langage relationnel commercial mettant enjeu, pour la consultation, une combinaison de l’algèbre relationnelle et du calcul relationnel La norme SQL 1970 : article de E. F. CODD ‘’A Relational model for large Data Banks’’, ACM vol 13, No 6, october 1970 présentant la théorie des bases de données relationnelle IBM System R, SEQUEL Barkley INGRESS, QUEL 1980 : SEQUEL enrichi et amélioré, a donné lieu à SQL : Langage utilisé par les SGBD SQL/DS, DB2 1986 : norme SQL86 préparée par le comité (X3H2) de l’ ANSI, adoptée également par ISO et par X/open en 87 41 1989 : ANSI publie une extension de la norme sous le nom SQL89 LANGAGE SQL (Structured Query Language) Le langage de définition des données (LDD) CREATE ALTER DROP Le langage de manipulation des données (LMD) SELECT INSERT UPDATE DELETE 42 Langage de Définition de Données Syntaxe CREATE DOMAIN CREATE DOMAIN [valeur] [CONSTRAINT nom_contrainte CHECK (condition) ] Exemple : CREATE DOMAIN TypeNomDOC IS VARCHAR2(20); CREATE DOMAIN DATE_RDV IS DATE DEFAULT (CURRENT_DATE) CHECK (VALUE >= CURRENT_DATE) NOT NULL; 43 Langage de Définition de Données Syntaxe de la commande CREATE TABLE CREATE TABLE nom Table ( colonne type [contrainte de la colonne] [, colonne type [contrainte de la colonne]] … [, contrainte de la table] …) ; 44 Langage de Définition de Données Contrainte sur une colonne [ CONSTRAINT ] [ NOT NULL | UNIQUE | PRIMARY KEY | CHECK (condition) | REFERENCES (colonne) ] Contrainte sur une table [ CONSTRAINT [ UNIQUE (liste de colonnes) | PRIMARY KEY (liste de colonnes) | CHECK (condition) | FOREIGN KEY (liste de colonnes) REFERENCES (liste colonnes) [] ] 45 ] Langage de Définition de Données [::=[ON DELETE {CASCADE|SET DEFAULT|SET NULL}] | [ON UPDATE {CASCADE| SET DEFAULT| SET NULL}] ] Remarque : Contrainte sur la colonne ou la table : si la contrainte ne fait intervenir qu’un SEUL ATTRIBUT. Contrainte sur la table : si la contrainte fait intervenir PLUSIEURS ATTRIBUTS. 46 Langage de Définition de Données 47 Langage de Définition de Données Create Table DOC( NumDOC integer PRIMARY KEY, NomDOC VARCHAR2(20), VilleDOC VARCHAR2(20) ); Create Table DOC( NumDOC integer, NomDOC VARCHAR2(20), VilleDOC VARCHAR2(20), Constraint PK_DOC Primary Key (NumDOC) ); 48 Langage de Définition de Données Create Table DOC( NumDOC integer Constraint PK_DOC PRIMARY KEY, NomDOC VARCHAR2(20), VilleDOC VARCHAR2(20) ); 49 Langage de Définition de Données Create Table DET( NumORD integer, NumLigne integer, NumMED integer, QTE integer Not Null, Constraint PK_DET Primary Key (NumORD, NumLigne), Constraint NbMaxMed Check (NumLigne < 5), Constraint Ref_ORD Foreign Key (NumORD) References ORD(NumORD) on delete cascade, Constraint Ref_MED Foreign Key( NumMED )References MED(NumMED) on update cascade ); 50 Langage de Définition de Données Définition du concept d’Index Un index est une structure de données qui permet d'accélérer les recherches dans une table en associant à une clé d'index (la liste des attributs indexés) l'emplacement physique de l'enregistrement sur le disque et ainsi améliorer les performances d’une application utilisant une base de données. Syntaxe de création d’un Index en SQL - Syntaxe basique : CREATE INDEX ‘Index_Nom’ ON ‘Table_Nom’; - Index sur une seule colonne : CREATE INDEX ‘Index_Nom’ ON ‘Table_Nom’ (‘Colonne1’); - Index sur plusieurs colonnes CREATE INDEX ‘Index_Nom’ ON ‘Table_Nom’ (‘Colonne1’, ‘Colonne 2’, …); - Index unique (unicité des valeurs de la colonne indexée) CREATE UNIQUE INDEX ‘Index_Nom’ ON ‘Table_Nom’ (‘Colonne1’); 51 Langage de Définition de Données Syntaxe de la commande ALTER TABLE ALTER TABLE { ADD COLUMN | DROP COLUMN [RESTRICT|CASCADE] | ADD CONSTRAINT | DROP CONSTRAINT [RESTRICT|CASCADE] | } RESTRICT : pas de destruction si l’objet est référencé ou utilisé ailleurs CASCADE : propage la destruction 52 Langage de Définition de Données Exemples : ALTER TABLE DOC ADD COLUMN TEL NUMBER NOT NULL; ALTER TABLE DOC DROP COLUMN TEL; ALTER TABLE DOC ADD CONSTRAINT NN_NOM NomDoc NOT NULL; ALTER TABLE DOC ADD CONSTRAINT Ville_valide CHECK(Ville = ‘Rabat’ OR ville=‘Casa’); ALTER TABLE DOC DROP CONSTRAINT NN_NOM; 53 Langage de Définition de Données Syntaxe DROP TABLE DROP TABLE Exemple : Drop table DOC; 54 LANGAGE SQL (Structured Query Language) Le langage de définition des données (LDD) CREATE ALTER DROP Le langage de manipulation des données (LMD) SELECT INSERT UPDATE DELETE 55 Langage de Manipulation de Données Syntaxe INSERT INTO INSERT INTO [( colonne1 [, colonne2] … )] { VALUES ( [, ] … ) | } ; Exemples : Table: DOC(NumDoc, NomDoc, VilleDoc) INSERT INTO DOC (NumDoc, NomDoc, VilleDoc) values (123, ‘Filali’,’Fès’); INSERT INTO DOC values (123, ‘Filali’,’Fès’); INSERT INTO DOC (NumDoc, NomDoc) values (444, ‘Alaoui’); INSERT INTO DOC select * from tabledesmedecins; 56 Langage de Manipulation de Données Syntaxe UPDATE UPDATE SET = valeur [, = valeur ] … [WHERE ]; Exemples : Table: DOC(NumDoc, NomDoc, VilleDoc) UPDATE DOC SET NomDoc=‘Andaloussi’, NomVille=‘Safi’ Where NumDoc=444; UPDATE DOC SET VilleDoc=Null; 57 Langage de Manipulation de Données Syntaxe DELETE DELETE FROM [WHERE ] Exemples : Table: DOC(NumDoc, NomDoc, VilleDoc) DELETE FROM DOC WHERE NumDoc=444; DELETE FROM DOC WHERE NomDoc IN ( select NomDOc from DOC WHERE VilleDoc=NULL ); 58 LANGAGE SQL (Structured Query Language) Le langage de définition des données (LDD) CREATE ALTER DROP Le langage de manipulation des données (LMD) SELECT INSERT Langage d’interrogation UPDATE des données DELETE 59 Langage de Manipulation de Données Syntaxe simplifiée de la requête Select SELECT [ DISTINCT ] * | expr[, expr…] FROM table [ WHERE condition ] [ ORDER BY expr| position [ASC| DESC]] ; 60 Langage de Manipulation de Données Conditions Permettent de comparer une colonne ou une expression à une autre colonne ou expression – Comparaison de valeurs =, >, =, 180 ORDER BY nom ASC, naissance DESC ; 62 Langage de Manipulation de Données Fonctions de groupe Fonction Description Count(*|[ DISTINCT|ALL] expr) Le nombre de ligne de expr Avg( [ DISTINCT | ALL] expr) Valeur moyenne de expr, en ignorant les valeurs NULL Min( [ DISTINCT | ALL] expr) Valeur minimale de expr, en ignorant les valeurs NULL Max( [ DISTINCT | ALL] expr) Valeur maximale de expr, en ignorant les valeurs NULL Sum( [ DISTINCT | ALL] expr) Somme des valeurs de expr, en ignorant les valeurs NULL 63 Langage de Manipulation de Données Exemples SELECT Avg(salaire), Sum(salaire), Min(salaire), Max(salaire) FROM personnes ; SELECT Min(naissance), Max(naissance) FROM personnes ; SELECT Min(nom), Max(nom) FROM personnes ; SELECT Count(*) FROM personnes ; 64 Langage de Manipulation de Données Exemples SELECT Count( telephone) FROM personnes SELECT Count( DISTINCT prenom) FROM personnes; SELECT Count( telephone), COUNT(*) FROM personnes WHERE ville = ‘Rabat’; 65 Travaux pratiques (N°1) Objectifs : 1) Se familiariser avec le SGBD MySQL 2) Créer une Base de données MySQL et Manipuler des ordres LDD et LMD sur une seule table 66 Travaux pratiques (N°1) - Les travaux pratiques se dérouleront sous le SGBD MySQL : https://www.mysql.com - Pour avoir la syntaxe détaillée des ordres SQL, consulter le site : https://sql.sh On s’intéresse à la base de données LocationChambre permettant de représenter les données relatives à la gestion des locations des chambres dans un hôtel. Cette base de données comporte les tables TypeChambre, Chambre, Client et Location. On s’intéresse dans ce TP1 à la table TypeChambre dont la description est la suivante : NTC : Numéro Type de Chambre de type MEDIUMINT(2), Nom : Nom du type de chambre de type VARCHAR(30), FL : Frais de location par nuit pour ce type de chambre de type DECIMAL(7,2) 1) Créer la base de données LocationChambre. 2) Créer la table TypeChambre en spécifiant les contraintes suivantes : NTC est une clé primaire Nom et FL doivent être Not Null 3) Créer un index unique sur le champ NTC. 4) Insérer les types de chambre suivants : (1,’Chambre Double Classique’, 700), (2,’Chambre Lits Jumeaux Standard’, 900), (3,’Chambre Double Deluxe’, 1050), (4,’Suite Lit King-Size’, 1500). 5) Insérer d’autres types de chambre. 67 Travaux pratiques (N°1) - Suite 6) Lister tous les types de chambre. 7) Lister les noms et les frais de locations des types de chambre. 8) Lister tous les types de chambre ordonnés par ordre croissant de leurs frais de location. 9) Lister tous les types de chambre ordonnés par ordre croissant de leurs frais de location et ordre décroissant de leurs noms. 10) Lister le nombre de types de chambre existants (utiliser un « alias »). 11) Lister la valeur maximale et la valeur minimale des frais de location des types de chambre. 12) Lister la somme et la moyenne des frais de location des types de chambre. 13) Lister les types de chambre dont les frais de location sont supérieurs à 1000 DH. 14) Lister les types de chambre dont les frais de location sont entre 800 DH et 1100 DH. 15) Lister les types de chambre dont le nom contient le mot ‘Double’. 16) Lister les types de chambre dont le nom ne contient pas le mot ‘Jumeaux’. 17) Lister le ou les types de chambre ayant les plus petits frais de location. 18) Modifier le numéro du type de chambre N°3, le nouveau numéro est 30. 19) Ajouter 10% aux frais de location de tous les types de chambre. 20) Ajouter 15% aux frais de location des types de chambre dont les FL sont inférieurs à 950. 21) Retrancher 5% des frais de location des types de chambre dont le numéro est entre 2 et 4. 22) Supprimer les types de chambre dont les frais de locations sont < à 750 ou > à 1200. 68 Langage de Manipulation de Données EMP Deptno sal Question : 10 5000 Salaire moyen pour 10 1500 chaque département de 10 1300 la table EMP 20 2975 20 3000 20 1100 30 2850 30 1250 30 1600 30 1500 30 950 30 1250 Table EMP 69 Langage de Manipulation de Données La clause GROUP BY SELECT column, group_fonction FROM table [ WHERE condition ] [ GROUP BY group_by_expression ] [ ORDER BY column]; Remarque : Les attributs du select ne peuvent être que – L’attribut qui crée le groupe – Une fonctions de groupe. 70 Langage de Manipulation de Données Exemple : SELECT deptno, AVG( sal ) FROM emp GROUP BY deptno; EMP Deptno sal 10 2600 20 2175 30 1566.7 71 Langage de Manipulation de Données EMP Deptno Job Sal 10 Dir technique 5000 Question 10 Chef projet 1500 10 programmeur 1300 Somme des salaires pour chaque poste (job), regroupés 20 Chef projet 2975 par département 20 Analyste 3000 20 programmeur 1100 30 Chef projet 2850 30 commercial 1250 30 commercial 1600 30 commercial 1500 30 programmeur 950 30 commercial 1250 72 Langage de Manipulation de Données SELECT deptno, job, SUM( sal ) FROM emp GROUP BY Deptno, job; Deptno Job SUM(sal) 10 programmeur 1300 10 Chef projet 1500 10 Dir technique 5000 20 Analyste 6000 20 programmeur 1900 20 Chef projet 2975 30 programmeur 950 30 Chef projet 2850 30 Commercial 4350 73 Langage de Manipulation de Données GROUP BY avec HAVING SELECT column, group_fonction FROM table [ WHERE condition ] [ GROUP BY group_by_expression [ HAVING group_condition ] ] [ ORDER BY column]; 74 Langage de Manipulation de Données Exemple Exemple SELECT deptno, SELECT deptno, M MAX( sal )) AX( sal FROM emp FROM emp EMP EMP Deptno Deptno job job sal sal GROUP BY deptno GROUP BY deptno 10 10 Dir technique Dir technique 5000 5000 HAVING HAVI NG MMAX( sal )) > AX( sal > 2900; 2900; 10 10 Chef projet Chef projet 1500 1500 Deptno MAX(sal) 10 10 programmeur programmeur 1300 1300 Deptno MAX(sal) 10 5000 20 20 Chef projet Chef projet 2975 2975 10 5000 20 3000 20 20 Analyste Analyste 3000 3000 20 3000 30 2850 20 20 programmeur programmeur 1100 1100 30 2850 30 30 Chef projet Chef projet 2850 2850 30 30 commercial commercial 1250 1250 30 30 commercial commercial 1600 1600 Deptno Deptno MAX(sal) MAX(sal) 30 30 commercial commercial 1500 1500 10 10 5000 5000 30 30 programmeur programmeur 950 950 20 20 3000 3000 30 30 commercial commercial 1250 1250 75 Langage de Manipulation de Données Synthèse SELECT column, group_fonction FROM tables [ WHERE condition ] [ GROUP BY group_by_expression [ HAVING group_condition ] ] [ ORDER BY column]; 76 Langage de Manipulation de Données Requêtes sur plusieurs tables - Les opérateurs de jointures - L’imbrication de requêtes - Les opérateurs ensemblistes 77 Langage de Manipulation de Données Requêtes sur plusieurs tables : la jointure Equijointure ( jointure naturelle) Autojointure (jointure sur la même table) Jointure externe Non-équijointure (jointure par non égalité, théta jointure) 78 Langage de Manipulation de Données Requêtes sur plusieurs tables : la jointure Equijointure ( jointure naturelle) SELECT expr FROM table 1 INNER JOIN table2 ON table1.col1=table2.col2 SELECT expr FROM table 1 OU SELECT expr INNER JOIN table2 FROM table1, table 2 WHERE table1.col1=table2.col2 WHERE table1.col1= table2.col2 tab1 col1 tab2 col2 79 Langage de Manipulation de Données Equijointure ( jointure naturelle) Liste des RDV avec le docteur ‘Alaoui’ SELECT NumRDV FROM RDV R , DOC D WHERE R.NumDoc = D.NumDoc and D.NomDoc =‘Alaoui’; 80 Langage de Manipulation de Données Equijointure ( jointure naturelle) Liste des patients ayant un RDV avec le docteur ‘Alaoui’ SELECT PAT.NomPat FROM PAT , RDV , DOC WHERE PAT.NumPat = RDV.NumPat and RDV.NumDoc = DOC.NumDoc and DOC.NomDoc =‘Alaoui’; 81 Langage de Manipulation de Données EMP Deptno NOM sal 10 Alaoui 5000 Liste des employés ayant un salaire 10 Filali 1500 égal à celui de « Azhari » 10 Rachidi 1250 20 Tahiri 2975 20 Rochdi 3000 20 Ouazzani 1100 30 Zohri 2850 30 Azhari 1250 30 Taouil 1600 30 Rbati 1500 30 Andaloussi 950 30 Soussi 1250 82 Langage de Manipulation de Données Requêtes sur plusieurs tables : la jointure Autojointure SELECT expr FROM table1 Alias1, table1 Alias 2 WHERE Alias1.col1= Alias2.col1 tab1 col1 83 Langage de Manipulation de Données Autojointure EMP Deptno NOM sal Liste des employés ayant un salaire 10 Alaoui 5000 égale à celui de «Azhari» 10 Filali 1500 10 Rachidi 1250 20 Tahiri 2975 20 Rochdi 3000 20 Ouazzani 1100 SELECT E2.Nom 30 Zohri 2850 FROM EMP E1, EMP E2 30 Azhari 1250 WHERE E1.sal=E2.sal 30 Taouil 1600 and E1.Nom =‘Azhari’; 30 Rbati 1500 30 Andaloussi 950 30 Soussi 1250 84 Langage de Manipulation de Données Autojointure EMP Deptno NOM sal Liste des employés ayant un salaire 10 Alaoui 5000 =, >, (SELECT salemp Filali 105 FROM EMP Rochdi 100 WHERE nom=‘Filali’); Fatimi 200 Les employés ayant un salaire supérieur à la moyenne ? SELECT nom FROM EMP WHERE salemp > (SELECT AVG(salemp) FROM EMP); 100 Langage de Manipulation de Données Exemples Les noms des employés qui ne sont pas les moins payés ? SELECT nom FROM EMP WHERE salemp > ANY (SELECT salemp FROM EMP ); Le nom de l’employé le mieux payé ? SELECT nom FROM EMP WHERE salemp >= ALL (SELECT salemp FROM EMP); 101 Langage de Manipulation de Données IN : la condition est vraie si EXP appartient à la liste des valeurs retournées par la sous-requête ANY : la condition est vraie si la comparaison est vraie pour AU MOINS une des valeurs retournées par la sous-requête ALL : la condition est vraie si la comparaison est vraie pour TOUTES les valeurs retournées par la sous-requête FAUX si Resultat(Sous-requête) = ᴓ EXISTS (sous-requête) VRAIE si Resultat(Sous-requête) ≠ ᴓ 102 Exemples : Liste des docteurs n’ayant pas eu de RDV en 2009 Select * from DOC Where NumDOC NOT IN (select numdoc from RDV where dateRDV Between ‘01/01/2009’ and ’31/12/2009’ ); Avec EXISTS : Select * from DOC D Where NOT EXISTS (select * from RDV R where dateRDV Between ‘01/01/2009’ and ’31/12/2009’ AND R.NumDOC=D.numDoc ); 103 Exercice Q1 : Les numéros d’ordonances et leur montant total Q2 : Les noms des patients ayant pris au moins un médicament de prix supérieur à 150 DH. Q3 : Le nombre de RDV par docteur en 2019 Q4 : Patients sans RDV en 2019 Q5 : Les patients ayant eu des RDV avec tous les docteurs Q6 : Les docteurs ayant eu des RDV avec tous les patients Q7 : Les patients ayant eu des RDV avec les mêmes docteurs que le patient N°10. Q8 : Le médicaments le plus prescrit en 2019. 104 Langage de Manipulation de Données Opérateurs ensemblistes 105 Langage de Manipulation de Données Opérateurs ensemblistes INTERSECT UNION UNION ALL MINUS Requête SELECT Requête SELECT Remarque : Il est nécessaire que chacune des deux requêtes retournes le même nombre de colonnes, avec les mêmes types de données et dans le même ordre. 106 Exemples : Les médicaments prescrits en mêmes temps dans les ordonnances 1 et 3 Select NumMed from DET Where NumOrd=1 INTERSECT Select NumMed from DET Where NumOrd=3; Les docteurs n’ayant pas eu de RDV en 2022 Select NumDoc from DOC MINUS Select NumDoc from RDV Where dateRDV Between ‘01/01/2022’ and ’31/12/2022’ ; 107 Travaux pratiques (N°4) Objectifs : Manipuler : 1) Les tables à clés primaires composées 2) Les requêtes imbriquées et les jointures 3) Les opérateurs ensemblistes 108 Travaux pratiques (N°4) On s’intéresse toujours à la base de données LocationChambre plus précisément aux nouvelles tables Client et Location décrites comme suit : Table Client NC : Numéro Client de type SMALLINT, Nom : Nom du Client de type VARCHAR(15), Prenom : Prénom du Client de type VARCHAR(15), Adresse : Adresse du Client de type VARCHAR(50), Table Location NC : Numéro Client ayant fait la location de type SMALLINT, NCH : Numéro de chambre louée par ce client de type SMALLINT, DL : Date de location de cette chambre par ce client de type DATE, PL : Période de location (en nb de nuits) de type SMALLINT UNSIGNED, 1) Créer ces deux tables en spécifiant les contraintes ci-dessous : NC est une clé primaire dans Client, (NC, NCH, DL) est une clé primaire dans Location, NC est une clé étrangère dans Location qui référence NC de Client, NCH est une clé étrangère dans Location qui référence NC de Chambre, 2) Insérer des clients dans la table Client. 3) Insérer pour chaque client des locations. 4) Lister tous les clients. 5) Lister les clients ayant loué la chambre N°2. 6) Lister les clients ayant loué une chambre dont les frais de location dépassent 650. 109 Travaux pratiques (N°4) - Suite 7) Lister pour chaque location ses frais (FL*PL). 8) Lister pour chaque location, le nom et le prénom du client, le nom du type de la chambre louée, la période de location, les frais de location par jour (FL) ainsi que les frais globaux de cette location (FL * PL). 9) Lister les chambres louées avant la date du ‘31-12-2022’. 10) Lister les chambres louées pour une période comprise entre 2 et 5 nuits. 11) Lister pour chaque client le nombre de locations effectuées. 12) Lister pour chaque client le nombre de chambres louées. 13) Lister pour chaque chambre le nombre de clients l’ayant loué. 14) Idem que 11 mais uniquement pour des périodes de locations entre 3 et 7 nuits. 15) Idem que 13 mais uniquement pour les chambres dont les FL ne dépassent pas 900DH. 16) Lister par client la somme des FL*PL pour toutes les locations faites par ce client. 17) Idem que 16 mais uniquement pour les clients dont le numéro est 1, 3 ou 5. 18) Idem que 16 mais uniquement si le nombre de locations dépasse 4. 19) Idem que 16 mais uniquement si la somme des (FL*PL) est entre 1500 et 5000 DH. 20) Lister les types de chambres dont les FL sont supérieurs à la moyenne des FL. 21) Lister les chambres n’ayant jamais été louées en 2022. 22) Lister les clients ayant loués tous les types de chambres. 23) Lister les chambres qui n’ont pas été louées en octobre 2022. 24) Lister les chambres les plus demandées (sans tenir compte du nombre de nuitées) 110 LES VUES 111 Les vues Définition : Une vue est une Table virtuelle calculée à partir d’autres tables ou vues par une requête Pas d’existence physique mais recalculée chaque fois qu’elle est invoquée Vue mono table Vue multi-tables Intérêts : Indépendance application/données Personnalisation des données selon les besoins des utilisateurs Confidentialité Rapidité des requêtes Utilisation : Pour les sélections, comme une table ordinaire Pour les maj. (insert, update, delete), y a des restrictions 112 Les vues Syntaxe de CREATE VIEW CREATE VIEW [(liste des attributs)] AS [WITH CHECK OPTION] WITH CHECK OPTION Permet de vérifier que les mises à jour ou les insertions faites à travers la vue ne produisent que des lignes qui feront partie de la sélection de la vue. 113 Les vues Exemples : CREATE VIEW MedecinsDeRabat AS Select * From DOC Where villeDoc=‘Rabat’; CREATE VIEW ORD_Total (NumORD, Total) AS Select NumORD, SUM(Qte*Prix) FROM DET D, MED M WHERE D.NumMed=M.NumMed GROUP BY NumORD; 114 Les vues Règles d’utilisations des VUES UPDATE SELECT INSERT DELETE Le SELECT principal de la vue contient Plusieurs tables OUI NON NON NON GROUP BY OUI NON NON NON DISTINCT OUI NON NON NON fonction de groupe OUI NON NON NON Attribut calculé OUI NON OUI NON Attribut NOT NULL pas dans le OUI OUI OUI NON SELECT UNION, INTERSETC, MINUS OUI NON NON NON 115 Travaux pratiques (N°5) Objectifs : 1) Comprendre le concept de vue et ses règles d’utilisation 2) Manipuler ce concept sur des exemples concrets 116 Travaux pratiques (N°5) 1) Créer une vue sur les types de chambres dont les frais de locations sont supérieurs à 920. 2) Insérer à travers cette vue des types de chambres respectant la condition (FL>920) et d’autres ne la respectant pas. Qu’est ce que vous remarquez ? 3) Afficher de cette vue les types de chambre dont le nom contient le mot Deluxe. 4) Afficher en utilisant cette vue le nombre de types de chambre dont les FL sont supérieurs à 920. 5) Afficher en utilisant cette vue le nombre de types de chambre dont les FL sont compris entre 920 et 1600. 6) Afficher en utilisant cette vue la somme et la moyenne des frais de locations des types de chambre dont les FL sont supérieurs à 920. 7) Modifier à travers cette vue les frais de locations des types de chambres dont les FL sont supérieurs à 920 en ajoutant 50DH. 8) Modifier à travers cette vue les frais de locations des types de chambres dont les FL sont supérieurs à 920 en retranchant 15DH. Qu’est ce que vous remarquez ? 9) Modifier cette vue de telle façon que la condition FL>920 soit toujours respectée. 10) Tenter d’exécuter les requêtes des questions 2) et 8). Qu’est ce que vous remarquez ? 11) Créer une vue sur les chambres louées pendant l’année 2022 (DL entre ‘1-1-2022’ et ’31-12-2022’ de telle façon à ce que cette condition soit toujours respectée lors des mises à jour (Utiliser WITH CHECK OPTION) 12) Afficher à travers cette vue les informations de cette chambre. 117 Travaux pratiques (N°5) - Suite 13) Afficher, en utilisant cette vue, les chambres louées par le client N°2 pendant l’année 2022. 14) Afficher, en utilisant cette vue, les chambres louées avant Mai 2022. 15) Afficher par client, et ce en utilisant cette vue, le nombre de chambres louées pendant l’année 2022. 16) Afficher, en utilisant cette vue, les chambres louées pendant une période de plus de 5 nuits pendant l’année 2022. 17) Afficher, en utilisant cette vue, les chambres louées pendant l’année 2022 par les clients dont les noms commencent par ‘BEN’. 18) Afficher, en utilisant cette vue, la somme des frais de locations des chambres louées pendant l’année 2022. 118 ANNEXE Résumé de quelques commandes MySQL importantes http://www.w3schools.com/sql 119 106 120 121 FIN 122

Slides-BD-2A-EMSI-2023-Nassar.pdf

Document Details

Related

Full Transcript

Upgrade to continue