SQL : Un Langage Relationnel (08-09)

1. SQL : Un Langage Relationnel(08-09) Witold LITWIN

2. SQL • Inventé à IBM San Jose, 1974 (Boyce & Chamberlin) pour System R • Basé sur le calcul de tuple & algèbre relationnelle • relationnellement complet (et plus) • Le langage de SGBD relationnels • En évolution contrôlée par ANSI (SQL1, 2, 3...) • Il existe aussi plusieurs dialectes • Les possibilités basiques sont simples • Celles avancées peuvent être fort complexes • Signalées dans ce qui suit par

3. SQL: Définition de Données • CREATE TABLE CREATE VIEW CREATE INDEX • ALTER TABLE • DROP TABLE DROP VIEW DROP INDEX

4. CREATE TABLE(clauses essentielles) • Définit la table réelle (de base) CREATE TABLE table (column [,column]... [, primary key] ; column := name type [NOT NULL] type :=INTEGER, CHAR (n), GRAPHIC, ICON, DATE, TIME, TIMESTAMP

5. EXAMPLE CREATE TABLE S(S# CHAR (5) NOT NULL,SNAME CHAR (20),STATUS INT,CITY CHAR (15), PRIMARY KEY (S#) ) ;

6. ALTER & DROP TABLE ALTER TABLE S ADD DISCOUNT SMALLINT ; • certains systèmes: ALTER TABLE S DROP DISCOUNT SMALLINT ; ALTER TABLE S RENAME SNAME NAME ; ..... DROP TABLE P ;

7. Indexes CREATE [UNIQUE] INDEX index ON table ( column [order] [, column...) [CLUSTER] ; CREATE UNIQUE INDEX XS ON S (S#) ; CREATE UNIQUE INDEX XSP ON SP (S# ASC, P# DESC) ; • UNIQUE = pas de duplicata de valeurs indexées • L’indexe peut accélérer l’accès 100 – 10.000 fois • Indexes uniques obligatoires pour les clés dans le DB2

8. Indexes • En principe, une table peut avoir un nombre quelconque d'indexes • Les indexes accélèrent les recherches • Mais pénalisent les mises à jour ! • Pourquoi ? • Définition des indexes ne devait pas être à ce niveau de SQL (c'est la propriété du schéma interne) • Les indexes (Linear) Hash de Postgres & de MySql résultent de recherche à l’INRIA du soussigné • Les manuels au CRIO • Wikipedia • Idem pour « Analysis Services » de SQL Server

9. Un dialecte de SQLSQL-MsAccess • Le dialecte le plus répandu aujourd'hui • Définition de données est considérablement plus élaborée que dans le SQL Standard • Certaines options du standard sont toutefois • sous restriction • s'expriment sous mots-clés différents • voir MsAccess Aide • pas toujours nécessaires

10. Table P de la base S-P Table P de la base S-P

11. MsAccess: Contraintes d’Intégrité • Voir le générateur d’expression • On dispose de: • Constantes • Chaîne vide, faux, null, vrai • Opérateurs • Comparaison <, <=, >, >=, <>, Imp, Entre • Arithmétiques +,-, /, *, ^, \, Mod • Logiques Et, Ou, Pas, Ou_X, Eqv, Imp • Fonctions nombreuses (voir) • Parenthèses

12. MsAccess: Types de Données • Text • limité par défaut à 50 caractères • clause FIELD SIZE permet 256 caractères • supporte les prédicats SQL & QBE • Par ex. WHERE Contrat LIKE (*Voiture de tourisme*) • Mémo • taille < 64K caractères • supporte les prédicats SQL & QBE • Sous MsAccess 2007

13. MsAccess: Types de Données • Date/Heure • supporte l’arithmétique de dates/temps • 21/3 - 21/2 = 28 • 21/4 - 21/3 = 31 ? • prévu pour 21-ème siècle • 1/1/00 à 31/12/29 signifie 1/1/2000 à 31/12/2029 • 1/1/30 à 31/12/99 signifie 1/1/1930 à 31/12/1999 • Monétaire : • Dévises

14. MsAccess: Types de Données • NuméroAuto • compteur automatique (+1 à chaque tuple crée) • option incrément dans Nouvelles Valeur • OID pour chaque tuple crée • option aléatoire dans Nouvelles Valeur • Dans Create Table: CREATE TABLE table1(id1 AUTOINCREMENT,…

15. MsAccess: Types de Données • Hyperlien • comme son l ’indique • nom symbolique < 2048 octets • URL ou UNC< 2048 octets • sous-adresse (dans le fichier ou la page) CajunDelights#http://www.cajundelights.com#Price

16. MsAccess: Types de Données • Objet OLE • tout objet Windows • multimédia ou programme • peut être copié dans la table • les MAJ de l’original ne sont pas visibles dans la BD • peut être seulement référencé • gain de place • les MAJ de l’original sont visibles dans la BD • il faut double-cliquer sur sa description textuelle dans le tuple pour voir l’objet

17. MsAccess: Types de Données • Pièce Jointe (2007) • Tout objet Windows sécurisé • multimédia ou programme • Peut être copié ou seulement référencé • On peut attacher dans une même valeur plusieurs PJs.

18. MsAccess: Champ Numériqueclause Field size • Octet 0 à 255 • Entier-32,768 à 32,767, 2 octets. • Entier Long -2,147,483,648 à 2,147,483,647. 4 octets. • Précision Simple : Six digits -3.402823E38 à 3.402823E38. 4 octets. • Double (Default) 10 digits de précision 1.79769313486232E308 à 1.79769313486232E308. 8 octets. • Replication ID • Pour les bases dupliquées - 16 octets • un OID • peut être aussi dans le type Autonumber

19. MsAccess: Champ Yes/No A utiliser comme son nom l ’indique • Vrai/Faux Oui/Non Actif/Inactif • fixé par le champ Format et Liste de Choix • visualisé par défaut comme Faux ou Non ou Inactif • Valeur 0 = Faux, -1 = Vrai • mais, il y a d ’autres possibilités - taille: 1 octet

20. Intégrité référentielle

21. MsAccess : domaines • On peut les simuler (en QBE) par : • une table D source de valeurs • table de la base ou une liste de valeurs • une zone de texte ou zone de texte modifiable sur l’attribut A à valeurs dans D • déclaré dans la définition de A (partie Liste de choix) • une requête déclarée dans la définition de A (dans «  contenu »)

22. MsAccess : surprises • Seules les valeurs apparaissant dans la 1-ère colonne du box et donc dans D peuvent être dans A • Même si l’on indique une autre « colonne liée » • Type de données Assistant Liste de choix réalise cette manipulation • Drôle de type de données • Attention aux bugs de cet assistant • Access 03 • Aussi à l ’option « Limiter à la liste » • On peut la faire aussi sans cet assistant (et mieux)

23. MsAccess : surprises • La table peut hériter l’attribut A si l’on déclare: • L’attribut héritant à le même nom que le 1èr attribut de D déclaré dans SELECT • L’attribut A est le 2-ème dans SELECT • La 1-ère longueur de colonne = 0cm  Dans notre exemple DB, SP peut ainsi hériter SNAME  Question: et si l’on sélectionne une autre colonne liée ? • Par ex. on utilise d’abord pour saisir les valeurs de P.CITY la requête • SELECT S.City, S.SName FROM S; • Puis, on la remplace par: • SELECT S.Sname, S.City FROM S;

24. CREATE INDEX(MSAccess) • Il existe l’alternative QBE • Commande Index dans le Menu Affichage ou bouton droit • Quand la table est ouverte

25. MsAccess : CREATE INDEX • Pas d'indexe CLUSTER sous MsAccess • En SQL • Syntaxehabituelle create index xs on S (snameasc, status desc);

26. MsAccess : CREATE TABLECONSTRAINT = INDEX CREATE TABLE [Friends] ([First Name] TEXT, [Last Name] TEXT); CREATE TABLE Friends1 ([First Name] TEXT, [Last Name] TEXT, [Date of Birth] DATETIME, CONSTRAINTMyTableConstraint UNIQUE ([First Name], [Last Name], [Date of Birth])); CREATE TABLE Friends2 ([First Name] TEXT, [Last Name] TEXT, SSN INTEGER CONSTRAINTMyFieldConstraint PRIMARY KEY); • SSN est la clé primaire. • On peut créer une table sans clé primaire • alors elle accepte des duplicata • contrairement à la théorie du relationnel

27. MsAccess : CREATE TABLECONSTRAINT = Contraintes d'intégrité • Contrainte sur attribut unique: CONSTRAINT nom {PRIMARY KEY | UNIQUE | NOT NULL | REFERENCES foreigntable [(foreignfield1, foreignfield2)]} • Contrainte sur plusieurs attributs: CONSTRAINT name {PRIMARY KEY (primary1[, primary2 [, ...]]) | UNIQUE (unique1[, unique2 [, ...]]) | NOT NULL (notnull1[, notnull2 [, ...]]) | FOREIGN KEY (ref1[, ref2 [, ...]]) REFERENCES foreigntable [(foreignfield1 [, foreignfield2 [, ...]])]} • foreigntable = la table avec la clé primaire référencée • le langage de définition de QBE d ’MsAccess permet de définir davantage de contraintes (comme on a vu en partie) • surtout les contraintes prédicatives d intégrité • mono ou multi-attribut, mais mono-table.

28. La table nommée P_1 est en fait la table P Les clauses CASCADE n ’existent qu’en QBE de MsAccess

29. Exercice : que veulent dire ces contraintes ? Les clauses «is Null> dans les Validation Rules sont-elles utiles ?

30. Sous-Tables en MsAccess • Une table peut avoir une sous-table • dite sous-feuille • La sous-table « auto » contient la clé étrangère de sa table (feuille) • Alternativement, la sous-table est choisie à travers un lien sémantique défini manuellement • Table ou requête

31. Sous-Tables en MsAccess • Les sous-tables sont utiles pour • Réification d’attributs dérivés • Tout particulièrement de valeurs agrégées • Par la création de sous-tables requêtes (vues) • Comparaison détail versus l’agrégation • Navigation hiérarchique • Table -> Sous-table -> Sous-table -> Sous-table…

32. Sous-Tables en MsAccess • Dans la base SP • Table SP est automatiquement la sous-table de S • Table S peut être choisie manuellement comme sous-table de SP • Avec le champs père SP.S# et champs fils S.S# • Suggérés par MsAccess • Les liens S -> SP -> S sont alors transitifs

33. Sous-Tables en MsAccess • Dans la base SP • Soit la requête « Quantité / fournisseur » : • Select Sum (Qty) From SP Group by [S#] ; • Cette requête peut être rendue sous-feuille de SP • Elle matérialise alors le concept de l’attribut dérivé d’UML pour SP • Le formulaire résultant apparaît comme entité structurée

34. Sous-Tables en MsAccess • On crée une sous-table • Sur le menu Propriétés d’une table • Auto / Aucune / Nom de la table / requête • On peut fixer la hauteur de la sous-fenêtre ou la laisser auto (option 0 cm) • La sous-feuille peut apparaître in extenso (ligne « étendue » oui) ou par « + » seulement • à cliquer pour la voir étendue • Sur le menu Insertion de la vue de la table ouverte • La sous-feuille est signalée par « + » seulement

35. Sous-Tables en MsAccess Réalisation limitée d’une table à attributs hérités. Litwin, W. Ketabchi M. Risch, T. « Relations with Inherited Attributes » HPL Tech Rep. HPL-DTD-92-45, April. 1992), 30.

36. Sous-Table Requête

37. Sous-Table Requête Attribut dérivé - Risque relatif pour s1 s’il perde la commande de p1 - Part de la production représenté par p1 - ….

38. Sous-Tables Imbriquées

39. FIN

41. Manipulation deDonnées Relationnelles Witold LITWIN 08-09

42. Manipulation deDonnées Relationnelles Deux langages dominants SQL Interface de commande Calcul de tuple QBE Interface interactive graphique Calcul de domaine

43. SQL • Inventé à IBM San Jose, 1974 (Boyce & Chamberlin) pour System R • Basé sur le calcul de tuple & algèbre relationnelle • relationnellement complet (et plus) • Le langage de SGBD relationnels • En évolution contrôlée par ANSI (SQL1, 2, 3...) • Il existe aussi plusieurs dialectes • Les possibilités basiques sont simples • Celles avancées peuvent être fort complexes • Signalées dans ce qui suit par « Maillot Jaune »

44. SQL: Manipulation de Données • Expression générale de sélection: SELECT [DISTINCT] attribut(s) FROM table(s) [WHERE condition] [GROUP BY field(s) ] [HAVING condition ] [ORDER BY attribute(s)] • basée sur le calcul de tuple • produit une table temporaire (en général avec des duplicata)

45. Examples • En pratique sur MsAccess • Sauf ceux spécifiques à • SQL-Server • DB2

46. Exemple canon SP S P

47. MsAccess SELECT SELECT [predicate] { * | table.* | [table.]field1 [, table.]field2.[, ...]]} [AS alias1 [, alias2 [, ...]]] FROM tableexpression [, ...] [IN externaldatabase] [WHERE... ] [GROUP BY... ] [HAVING... ] [ORDER BY... ] [WITH OWNERACCESS OPTION] Predicat: ALL, DISTINCT, DISTINCTROW, TOP.

48. Interrogations (vraiment) simples • Projections d'une table sur certains attributs: SELECT [S#] FROM S Combien de lignes de programmation faudrait-il pour cette requête en Cobol ? 20 ? 50 ? 100 ? S# s1 s2 s3 s4 s5 MsAccess • L'ordre de présentation est fixé par le SGBD et peut changer dans le temps

49. Projection avec duplicata SELECT CITY FROM S; • Le résultat peut avoir les duplicata • alors, il n'est pas une relation, mais un bag

50. Elimination de duplicata SELECT DISTINCT CITY FROM S; Combien de lignes de programmation faudrait-il pour cette requête en C ? 20 ? 50 ? 100 ? CITY Athens London Paris • DISTINCT est optionnel pour deux raisons: • éliminer les duplicata coûte en temps de réponse • les fonctions agrégats en ont besoin.