DesignDa数据库与设计

1、Institut Tianjin2011Design & Data BasesChapitre: fondements du modle relationnelMichel KaysDesign & Data BasesObjectifs:connaissances des modles et outils concernant les bases de donnes relationnelles:-les fondements du modle et le langage SQL-la ralisation de programmes et des outils-la con

2、ception des basesDroulement:1- Bases de donnes relationnelles ( Michel Kays ): 8 cours, 4 TD2- Notions avances et programmation ( Ladjel Bellatreche ): 16 cours, 16TD3- Conception ( Yamine Ait-Ameur ): 8 cours, 8TDPartie 1: fondementsplan du cours1 - GENERALITESDfinitions des Bases de Donnes (BD)Sys

3、tmes de Gestion de BD (SGBD)Modle architectural2 LE MODELE RELATIONNELFondementsAlgbre des relations3 - LE MODELE RELATIONNEL : le langage SQLLe langage dinterrogationCration de schmas relationnelsMoteur SQL4 ETUDE DE CASRevue des notions abordes sur un exemplebd: gnralitsUne DfinitionEnsemble organ

4、is de DONNES accessibles informatiquementSELECTIVEMENT et SIMULTANMENT par plusieurs UTILISATEURS dans le but de stocker et rechercher des informationsApplicationsContenu Prog1 Donnes Prog2Flot de contr?leFlot de donnesbd: gnralitsExemple: compagnie ariennePrises de rservationAgences de rservationin

5、formation des clients Prog1ventes billets DonnesContenu Prog2gestion volsService du marketingFrquentation .Avion ( AIRBUS, 340 places ),Avion ( BOEING, 340 places ), .Vol( Vol(, 13h ), 20h )Ligne ( Paris, Poitiers ), Ligne ( Poitiers, Lyon ), .Rservation (Rservation (Passager ( Martin,), Passager (

6、Dupond,bd: gnralitsCaractristiques Prog1 Donnes Prog2Concurrence,performance,rsistance aux pannes,confidentialitRecherche/extraction d infos,Cration/modification,volutifsCentralises,Structures,massives,trs volutives,permanentes,bd: systme de gestion de bd (SGBD)Dfinition:Logiciel ralisant l interfac

7、e entre les applications et le contenu de la base et permettant de grer cette baseInfos de gestionDonnesApplicationSGBDGrer = Construire, Utiliser, Maintenir, RorganiserActeurs:Structurer le contenuDcrire les autorisations d accsAssurer les performances d accsPrvenir et rcuprer les pannesAdministrat

8、eurData Definition LanguageData Manipulation Language? peupler ? le contenuinterrogerUtilisateurbd: modle architecturalL ARCHITECTURE ANSI -SPARCObjectif : indpendance entre la structure de stockage desdonnes et les programmes dapplicationRsultat : architecture 3 niveaux (ANSI 75)? Niveau Interne :?

9、 Niveau logiqueaspect physique et concret de la base, constitu des fichiers et de leurorganisation interne;:dsigne la structure logique du contenu duites du niveau logique? Niveau Externe:Ensemble des vues extraites ou dEnseignantEnseignantCoursTotal heuresHeureServiceNiveau InterneNiveau ExterneNiv

10、eau logiqueTotal heures = .bd: modle architecturalNOTION DE SCHEMAOn appelle schma dune base de donneslensemble des informations descriptives (mta-donnes) du contenu de la base. Monde relSchmaExterne 1Mod lisationSchmaExterne 2SchmaconceptuelSchmaphysiqueSchmaExterne nNiveau externeNiveau logiqueNiv

11、eau internebd: modle architecturalOUTILS & MODELESOUTILS : LANGAGES ASSOCI S? Le langage de D finition des Donn es (DDL):permet de dfinir:dfinit le ? dictionnaire ?- le schma conceptuelde la basede donnes de la base- de prciser certains lments dimplantation physique- dans certains cas, des rgles

12、 dintgrit- les schmas externesutilisables? Le langage de manipulation des donn es (DML):offre,en conformitavec le DDL, les primitives:- concernant linsertion, la mise jour, la suppression des donnes,- la recherche de donnesle des accs aux donnes :?Le langage de contr?(DCL)Ils sontentirementdpendants

13、du type de SGBD, i.e. dumodle conceptuel de donnes sur lequel est fond le SGBD.bd: modle architecturalMODELESmodles : typologieModle Rseau( & Hirarchique(le plus ancien)Modle Relationnel(la gnration oprationnelle actuelle)Modle Relationnel-ObjetModle Objet(pas de standard actuel)Modle Dductif(sy

14、stmes experts)bd: quelques systmes relationnelsSystmes orients ?fichiers ?Access(Microsoft)Systmes ? client/serveur ?Oracle (Oracle Corporation) (40% du march)SQL Server (Microsoft)Informix (IBM)DB2 (IBM)Interbase (Borland)SybasePostgres (universit de Berkeley)MySQLmodle relationnelINTRODUCTIONHisto

15、rique: Codd (1970)Caractristiques:-s,simplicitdes concepts manipul?bien pour les utilisateurs-ind pendance du stockage physique?bien pour les programmeurs-fondement mathmatique clair.oriciens?bien pour les th la base de la plupart des SGBD actuellement commercialissmodle relationnelCONCEPTS DE BASED

16、omainesigne un ensemble de valeurs, caractris par un nom.UndomainedExemple:NOMS_DE_PERSONNE = Paul, Jean, Jacques, Michel. AGE = Nombre_entier_positifRelationUne relation R est un sous-ensemble du produit cartsienD=D1X.XDn, o ( Di) sont des domaines (non ncessairement distincts).lment e=(e? Un 1, .

17、,en)de R est un n-uplet, outupleExemple:La relation PASSAGER dfinie sur NOMS_DE_PERSONNEX AGE ,de contenu:PASSAGER = (Paul, 36), (Jacques, 45), (Michel, 12) modle relationnelCONCEPTS DE BASEAttribut dune relation?La projection proj Di: R ?Di est dnote Ai .Le couple (Ai: Di) est dit attribut de R On

18、notera alors la relation RA1, . , An .Exemple:o,PASSAGER Nom , Agedomaine(Nom)domaine( (Age)NOMS_DE_PERSONNEAGEREPRSENTATIONS DUNE RELATIONsentation en extension: tabledune Relation? ReprPASSAGERtuple_1tuple_2tuple_3NomPaulJacquesMichelAge364512sentation en intention: schmadune Relation: attributs &

19、amp; contraintes?Reprmodle relationnelCONTRAINTES RELATIONNELLESDEFINITIONS:cl dune relation?Sous-groupe minimal des attributs de la relation permettant didentifier de fa?on unique un tuple de cette relation- Ce sous-groupe X peut ne pas tre unique - pour tout e de R, X(e) identifie e de fa? on uniq

20、ue Valeurs nulles?On appelle NULL la valeur conventionnelle permettant de reprsenter une information inconnue ou inapplicable Exemple: cl Dans la relation PERSONNE Num_ident, Nom, Naissance, Dcs , o: Num_ident ? CHAINE, Nom ? NOMS_DE_PERSONNE Naissance et Dcs ? DATE on peut avoir le tuple: ( 1.50.03

21、.17.125.001, Michel, 7/03/50, NULL ) modle relationnelCONTRAINTES RELATIONNELLESSCHEMA RELATIONNEL:Le schma relationnel dune base de donnes =schm a de chacune de ses relations +contraintes dintgrit propres au m odle relationnel : contrainte densemble:? ? Toute relation doit tre munie dune cl, appele

22、 cl primaire. contrainte dentit:? Toute valeur dun attribut participant la cl dune relation doit tre non nullecontrainte de ? cl trangre ? :?cl trangre dune relation = attribut de cette relation qui rfrence la cl primairedune autre relation.? La rgle dintgrit qui en dcoule consiste imposer que cette

23、 valeur apparaisse effectivem ent dans la relation cible (intgrit rfrentielle)modle relationnelEXEMPLEPASSAGER Nom, Age RESERVATION Nom, Num_vol , Num_place VOL Num_vol, Ident_ligne , Date LIGNE Ident_ligne , Dpart, Arrive AEROPORT Nom SchmacontenuRESERVATIONPASSAGERNomJeanMichelVOLNum_vol110303122L

24、IGNEIdent_ligne1012DpartParisParisArriv eTokyoBerlinIdent_ligne101210Date23/02/9826/02/9803/03/98Age4334NomMichelMichelNum_vol303110Num_place546AEROPORTNomParisTokyoBerlinNum _vol ? cl primaire? cl trangrealgbre relationnelleINTRODUCTION? fondement thorique du langage de manipulation de donnes (DML)

25、Le principe gnral consiste dfinir des oprateurs :?oprandes = relations rsultat = relation. oprations de base = oprations unaires (projection, restriction) et?binaires ( union, diffrence, intersection) ? oprations de base ? op rations plus volus par composition algbre relationnelleOPERATIONS DE BASER

26、 est une relation ayant pour schma RA, A dsignant la liste ses attributs. Projection ? dsigne un sous-ensemble de BA: T B = ?B( R) est d fini par ?r ? R , projB(r) ? T Exemple: RA1,A2,A3 A1 a1 a2 a3 Extraction de colonnes! ! ! La projection limine les n-uplets identiquesA2 b1 b1 b3 A3 c1 c2 c3 ?B(R)

27、 = A2 b1 b1 b3 Si B = A2,A3 A3 c1 c2 c3 Si B = A2 A2 b1 b3 algbre relationnelleOPERATIONS DE BASESlection ? (ou restriction) TA = ?exp_select(R) est dfini par: T = t ? R | exp_select (t) = VRAI Exemple: RA= Ident_ligne Extraction de lignesDpart Paris Paris Londres Arrive 10 12 34 Tokyo Berlin Berlin

28、 exp_log = (Dpart = Paris ET Arrive = Berlin) ?exp_log(R)= Ident_ligne Arrive Dpart 12 Paris Berlin algbre relationnelleOPERATIONS DE BASE Union ?T A = R A SAExemple :A1112A2231A11123A131A212 ?A22311Diffrence -T A = R A - S AIntersection ?TA = RA S A algbre relationnelleOPERATIONS DE BASEXProduit Ca

29、rtsien TA+B = RA X SB) est dfini par: T = t=(r,s) , r ? R et s ? S Exemple :A1112R.A1111122A2231 XB1acB2bdR.A2223311S.B1acacacS.B2bdbdbdalgbre relationnelleJOINTURES ?- Jointure : ? - RA et SB dsignent 2 relations, - ?i sont des op rateurs de comparaison( , , = , , =, ), - C est une expression logiq

30、ue ba tie sur les op rateurs ?i et des attributs de R et S TA+B = RA ? C SB = ?C (R X S) Exemple: PASSAGER RESERVATION Num_vol Num_place Nom Nom Age Jean 43 Michel 303 5 Michel 34 Michel 110 46 PASSAGER ? PASSAGER.Nom=RESERVATION.Nom RESERVATION donne : Num_place RESERVATION.Num_vol PASSAGER. Age No

31、m Nom Michel 34 Michel 303 5 Michel 34 Michel 110 46 algbre relationnelleJOINTURESJointure naturelle : *Cas particulier important : jointure naturelle, o ? est lgalit et le rsultat a pour schma A? B , i.e. la fusion des champs qui portent le mme nom. Notation : T = R*S Exemple: PASSAGER RESERVATION

32、Num_vol Nom Age Nom Jean 43 Michel 303 Michel 34 Michel 110 ( PASSAGER * RESERVATION ) donne : Num_vol Num_place Age Nom Michel 34 303 5 Michel 34 110 46 5 46 Num_place algbre relationnelleEXPRESSIONSUne expression relationnelle est dfinie comme une composition des oprateurs dfinis ci-dessus.INT R T

33、a) Expression = requ te dinterrogation sur la Base de Donnes. Quelles sont les lignes reliant Paris et Berlin? ? ( (Dpart = Paris ET Arrive = Berlin ) OU (Dpart = Berlin ET Arrive = Paris ) ) (LIGNE) Quels sont les passagers ayant effectu une r servation? ( PASSAGER * RESERVATION ) Dans ce modle, to

34、ute requ te est ainsi formule partir de la construction dune expression , qui prend en oprandes les relations dont on veut extraire les informations et retourne une relation rsultat. algbre relationnelleEXPRESSIONSb) Exprimer des contraintes sous la forme : rsultat dexpression = ? (relation vide) Ex

35、emple: une ligne doit relier 2 aroports distincts:? Depart=Arrivee(LIGNE) = ? le langage SQLINTRODUCTIONCe qu on va voir:- le DML SQL: interrogations, insertion, suppression, maj de tuples,- le DDL SQL: dfinition du sch maCe qu on ne verra pas:- le DCL (Data Control Language)SQL: gestion des transac

36、tions, droits d accs, Historique:premier langage : SEQUEL (IBM)? a donn le langage SQL (Structured Query Language) :Caractristiques:- standard de lANSI en 1986.- volution SQL2 (SQL 92)- dernire version : SQL3 (SQL 99).- fondement thorique = lAlgbre Relationnelle- Objectif du langage : avoir une expr

37、ession syntaxique aussi naturelle quepossible.- Langage assertionnelle langage SQLQUELQUES TYPES DE V ALEUR - le type chaine de caractres: de longueur fixe CHAR(n) ou variable VARCHAR(n) - les types num rique exact : NUMERIC et ses sous-types: DECIMAL(n,d), INTEGER , SHORTINT - Le type num rique app

38、roch: FLOAT et ses sous-types: REAL et DOUBLE PRECISION - le type chaine de bits: de longueur fixe BIT(n) ou variable BIT VARYING(n) - les types date DATE et heure TIME: exemple : 1999-03- 09, 08 :00 :00.0 : VALEUR CON VENTI ONNELLEN ULLle langage SQLINTERROGATIONS!algbre relationnelle = manipule de

39、s relations (sens ensembliste)SQL manipule des tablespouvant contenir des duplicataLA COMMANDE SELECTSELECT FROM WHERE ? ? ( X X .) le langage SQLINTERROGATIONSClause SELECTLes clauses FROM et WHERE fournissent une table rsultat: SELECT permet de restreindre cette table des colonnes choisies ou de f

40、aire des calculs partir des colonnes cette tableSELECT (1) DISTINCT (2) nom_de_table . * | (3) expression as nom_de_colonne_resultat , 1) DISTINCT permet dliminer les duplicata dans la table r sultat, 2) La table r sultat comporte toutes les colonnes de la table sp cifie 3) dcrit une colonne obtenue

41、, avec son nom ventuel, par le r sultat du calcul spcifi par lexpression donne le langage SQLINTERROGATIONSClause FROMRenommage localde la table : : = ( 1) ,a l i asTabl e_ opr andenom _ de _ t abl eas| ( 2) J OI NON c ondi t i onnom _ de_ t abl enom _ de _ t abl eSELECT PI LOTE . *FROM PI LOTELa ta

42、ble rsultat est une copie de la table PILOTECondition de jointureSELECT PI LOTE . Nom _ P, PI LOTE . Adres s eFROM PI LOTELa table rsultat com porte les 2 colonnes indiques de la table PILOTESELECT DI STI NCT AVI ON. Bas eFROM AVI ONtabl e A VI O N:I dent _AType100A340101B707102FA LCO NCapaci t 3005

43、0015Bas ePari sLyonLyonBas eParisLyonle langage SQLINTERROGATIONSSELECT COUNT( PI LOTE) as Tot al , M AX( PI LOTE. Sal ai re) as M ax_ s al ai reFROM PI LOTEtabl e PI LOTE:Ident _PNom _P1Paul2Jean3Roger4Jean5M i chelAdres sePari sN i ceCaenLyonLyonSal ai re12 00013 5008 0008 50014 000T o tal5M a x _

44、sa la ire1 4 00 0 SELECT PILOTE. Nom_P AS Pilote_en_service FROM PILOTE JOIN VOL ON PILOTE.Ident_P=VOL.Ident_P Evaluation : - Calcul de PILOTE X VOL, - Slection sur la colonne Ident_P, - Projection sur la colonne Nom_P avec renommage : Pilote_en_servicePaulJeanRogerMichelle langage SQLINTERROGATIONS

45、Clause WHEREprdi cat_ de_ s l ecti on : : = ()| A N D O R | N O T| prdi catprdicat:= | | | | | SELECT PI LOTE . *FROM PI LOTEW HERE Sal ai re 10 000I dent_ P125Nom _PPaulJeanM i chelAdres sePari sN i ceLyonSal ai re12 00013 50014 000Pi l ot es dont l e sal ai r e e s t 10 000 ?le langage SQLINTERROG

46、ATIONSSELECT Num_vol as numro, Dpart as Ville FROM AVION, VOL WHERE AVION.Base = VOL.Depart Evaluation :1) Calcul de AVION X VOL,2) Slection indique,3) Projection sur les colonnes indiques avec renommage:tabl e A VI ON:I dent_ AType100A340101B707102FA LCONCapaci t30050015BasePari sLyonLyonnumro V308

47、 V144 Ville Paris Lyon table VOL : Num_vol Ident_A Ident_P Dpart V308 100 1 Paris V112 101 2 Paris V144 101 3 Lyon VSP0 102 1 Paris V134 100 5 Nice Arrive Nice Lyon Marseille Marseille Paris le langage SQLINTERROGATIONSSELECT Nom _ PFROM PI LOTE as P1, PI LOTE as P2W HERE P1. Adres s e = P2. Adres s

48、 eNom de s pi l ot e s ayant l a m m e adr e ss e ?Auto jointuretabl e PI LOTE:I dent_ PNom _ P1Paul2Jean3Roger4Jean5M i chelA dressePari sN i ceCaenLyonLyonSal ai re12 00013 5008 0008 50014 000Nom_PJeanMichelle langage SQLINTERROGATIONSSELECT Bas eFROM AVI ONW HERE Type I N ( A340 , B707 )BaseBase

49、de s avi ons dont l e t ype e s t A340 ou B707?ParisLyonSELECT Nom _PFROM PI LOTEW HERE PI LOTE. I dent_ P I N( SELECT I dent_ PFROM VOLW HERE VOL. Depart = Pari sNom_PJeanNom de s pi l ot es ass ur ant un vol au d par t de Par i s ?Paulle langage SQLINTERROGATIONSSELECT *FROM T1WHERE EXISTS(SELECT

50、*FROM T2WHERE T1.Ident = T2.Ident)sous-question excut e pour chaque ligne de T1table T1 :IdentNom1Paul2Jean3Roger4Jean5Micheltabl e T2:I dentA dresse1Pari s6Poi ti ers3Li l l e7Lyontabl e rsul tat:I dentN om1Paul3Rogerle langage SQLINTERROGATIONSspcification_d_agrgatLorsque le SELECT utilise des fon

51、ctions ?agrgat?: MAX, MIN, SUM, COUNT, . GROUP BY HAVI NG SELECT Depart, COUNT(*) AS Nombre_vols FROM VOL GROUP BY Depart ? Donner, pour chaque ville Dpart, le nombre de vols assurs ?tabl e VOL:N um _volI dent_ AV308100V112101V144101VSP0102V134100I d_ P Dpart1Pari s2Pari s3Lyon1Pari s5Ni ceArri veNi

52、 ceLyonM arsei l l eM arsei l l ePari sH_ dpart12151615Depart Paris Lyon Nice Nombre_vols 3 1 1 le langage SQLCREATION/SUPPRESSION DE TUPLESInsertion de tuplesI NSERT I NTO nom _ de_ tabl e ( l i s te_attri buts _ val uer) VALUES ( l i s te_ des _ val eurs)I NSERT I NTO PI LOTE ( I dent_P, nom _P) V

53、ALU ES ( 7, Charl es )t abl e PI LOTE:I de nt _ PNom _ PAdr e s s eSal ai re7Char l esNULLNULLI N SERT I NTO nom _ de_ tabl e (l i s te_ attri buts_ _ val uer) suppression de tuplesDELETE FROM nom _de_tabl e W HERE DELETE FROM VOL W H ERE N um _vol = V308modification de tuplesUPDATE nom _ de_ tabl e

54、 SET nom _ attri but=val eur ,W HERE UPDATE RESERVATI ON S SET Num _ vol = V112W HERE Num _ vol = V308le langage SQLCREATION DU SCHEMA (DDL) schma := CREATE SCHEMA ( ) Domainesdef i ni ti on_de_ dom ai ne : : =CREA TE D OM AI N nom _ de_dom ai neA S DEFA ULT val eur ; : : = constantec ontrai nte_de_

55、dom ai neCHECK ( VALU E )CREATE DOM AI N num ro AS SHORTI NTCHECK ( VALUE0) ;Dfinition du domaine de valeurs numro (entier0)! Trs rarement implmentle langage SQLCREATION DU SCHEMA (DDL)Tablesdefinition_de_table := CREATE TABLE nom_de_la_table ( , , , ); definition_de_colonne := nom NOT NULL UNIQUE c

56、ontrainte_de_cl : := cl_primaire : := PRIMARY KEY( nom_de_colonne, ) cl_trangre : := FOREIGN KEY( nom_de_colonne ) REFERENCES nom_de_table(nom_de_colonne) : : = contrai nte_ uni ci t UNI QUE(nom _ de_ col onne) : : = contrai ntes _ l ocal esCHECK ( express i on_l ogi que )le langage SQLCREATION DU SCHEMA (DDL)CREATE SCHEMA ( CREATE DOMAIN numro AS SHORTINT CHECK ( VALUE0) ; CREATE TABLE PILOTE ( Ident_P numro , Nom_P VARCHAR (20) NOT NULL, Adresse VARCHAR (80), Salaire DE