SQL : Un Langage Relationnel

1. SQL : Un Langage Relationnel 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: 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

12. 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

13. 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 • Hyperlien • comme son l ’indique • nom symbolique < 2048 octets • URL ou UNC< 2048 octets • sous-adresse (dans le fichier ou la page) Cajun Delights#http://www.cajundelights.com#Price

14. 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

15. 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.

16. 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

17. MsAccess: Champ Yes/No A utiliser comme son nom l ’indique • Yes/No ou On/Off ou True/False • fixé par le champ Format • visualisé par défaut par Check-box • mais, il y a d ’autres possibilités taille: 1 octet

18. 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 (combo-box) sur l’attribut A à valeurs dans D • déclaré dans la définition de A (partie Liste de choix /Lookup) • une requête déclarée dans la définition de A (dans «  contenu /rowsource »)

19. 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 / Lookup Wizard réalise cette manipulation • Drôle de type de données • Attention aux bugs de cet assistant • Aussi à l ’option « Limiter à la liste / Limit to List » • On peut la faire aussi sans cet assistant (et mieux)

20. 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  Qu’arrive t’il aux tuples existants 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;

21. 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, CONSTRAINT MyTableConstraint UNIQUE ([First Name], [Last Name], [Date of Birth])); CREATE TABLE Friends2 ([First Name] TEXT, [Last Name] TEXT, SSN INTEGER CONSTRAINT MyFieldConstraint 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 • Pas d'indexes CLUSTER sous MsAccess

22. 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.

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

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

25. 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

26. 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

27. 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

28. 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

29. 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.

30. Sous-Table Requête

31. Sous-Table Requête Attribut dérivé

32. Sous-Tables Imbriquées

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

34. 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)

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

36. Exemple canon SP S P

37. 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.

38. 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

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

40. 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.

41. Selections multiples • Les attributs apparaissent dans l’ordre de leur énumération dans la clause SELECT SELECT [S#], CITY, SNAME FROM S; S# City SName s1 Paris Smith s2 Paris Jones s3 Paris Blake s4 London Clark s5 Athens Adam

42. SELECT * • Tout sur toutes les fournitures : SELECT S#, P#, QTY FROM SP; • Formulation plus courante : SELECT * FROM SP; • Ordre d'attributs est celui de CREATE TABLE • Cette formulation est plus simple, mais deconseillée pour les programmes d'application pourquoi ?

43. ORDER BY SELECT * FROM SP ORDER BY QTY DESC, [S#];

44. ORDER BY SELECT * FROM SP ORDER BY QTY DESC, [S#]; S# p# Qty s1 p3 400 s2 p2 400 s4 p5 400 s1 p1 300 s2 p1 300 s4 p4 300 s1 p4 200 s1 p2 200 s3 p2 200 s4 p2 200 s1 p6 100 s1 p5 100 Combien de lignes de programmation faudrait-il pour cette requête en PL1 ? 20 ? 50 ? 100 ?

45. ORDER BY SELECT * FROM SP ORDER BY QTY DESC, [S#]; S# p# Qty s1 p3 400 s2 p2 400 s4 p5 400 s1 p1 300 s2 p1 300 s4 p4 300 s1 p4 200 s1 p2 200 s3 p2 200 s4 p2 200 s1 p6 100 s1 p5 100 Et la quantité nulle serait où?

46. TOP SELECT top 3 [S#] AS [Les petits], [P#], QTY FROM SP ORDER BY QTY ASC, [S#] ;

47. TOP SELECT top 3 [S#] AS [Les petits], [P#], QTY FROM SP ORDER BY QTY ASC, [S#] ; Les petits Product ID QTY s1 p6 100 s1 p5 100 s1 p4 200 s1 p2 200

48. TOP SELECT top 3 [S#] AS [Les petits], [P#], QTY FROM SP ORDER BY QTY ASC, [S#] ; Les petits Product ID QTY s1 p6 100 s1 p5 100 s1 p4 200 s1 p2 200 • Mot-clé utile, mais pas dans SQL standard (MsAccess) • - essaye de formuler cette requête en SQL standard • Pas de distinction entre les duplicata par rapport au critère d'ordre QTY, S# (les 3 tops sont devenus 4 tuples) Product ID ?

49. Restrictions simples SELECT [P#], PNAME FROM P WHERE COLOR = 'RED';

50. Restrictions simples SELECT [P#], PNAME FROM P WHERE COLOR = 'RED'; Product ID Product Name p1 nuts p4 screw p6 cog • Les noms d'attributs sont les légendes créées à la création de P • L'ordre de tuples délivrés est définit par le SGBD et peut changer d'une exécution à l'autre • Est-il possible de faire: SELECT [Product ID], [Product Name]…