SQL : Un Langage Relationnel

1. SQL : Un Langage Relationnel Witold LITWIN

2. Langage de base de données(Database Language) • Un sous-langage de programmation • Consiste traditionnellement de deux parties: • langage de définition de données • langage de manipulation de données • langage interactif (de requêtes) • langage imbriqué (embedded) • En pratique, les deux parties sont imbriquées • définition de vues et des attributs hérités en général

3. SQL • Inventé à IBM San Jose, 1974 (Astrahan & 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

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

5. SQL-2 et dialectes SGBD-Serveurs :DB2, SQL-Server, Sybase.. • CREATE (ALTER, DROP) DOMAIN • CREATE (ALTER, DROP) SCHEMA • une partie nommée d’une base • donne une autonomie de nommage • une table T dans le schéma S1 et une table T dans le schéma S2 sont deux tables différentes • nommées S1.T et S2.T dans la base • peut être considérée une base logique • CREATE (DROP) DATABASE • clause hors standard SQL-2 • CREATE (DROP) ALIAS, TRIGGER, FUNCTION, PROCEDURE...

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

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

8. CREATE TABLEClause CONSTRAINT • Clauses CONSTRAINT sont nouvelles dans SQL • Permettent de définir • les clés étrangères FOREIGN KEY • les contraintes d'intégrité CHECK • sur un attribut • inter-attribut d’une table • Autres • La puissance expressive varie entre les dialectes • le standard est le plus puissant • notamment permet tout SELECT dans CHECK • les dialectes ne permettent que • aucun CHECK (MsAccess) • contrainte sur les valeurs d’un même tuple (DB-2) • une par attribut (SQL-Server, DB2) • pas de sous-requêtes (SQL-Server)

9. CREATE TABLE(multibase) • On peut créer une table dans une autre base que celle courante (ouverte) • SQL Server, SQL (seulement) de MsAccess, SQL-2 CREATE TABLE AUTRE-BASE.S(S# CHAR (5) NOT NULL,SNAME CHAR (20),STATUS INT,CITY CHAR (15), PRIMARY KEY (S#) ) ; Autre-Base Base courante

10. SQL-2 Domaines • Une version limitée du concept du domaine relationnel. • surtout au niveau de types de données • Une source commune pour la définition d'attributs • Supporte les contraintes d'intégrité • Pas une découverte: • le SGBD MRDS (Multics) supportait les domaines il y a déjà 15 ans

11. Les domaines SQL-2 S# • CREATE DOMAIN nom [AS] type CONSTRAINT def DEFAULT VALUE value • Exemple : CREATE DOMAIN S#_DOM AS CHAR (4) ; • On peut alors déclarer: CREATE TABLE S (S# S#_DOM...) ; CREATE TABLE SP (S# S#_DOM...) ; • Les deux attributs S# ont la définition: S# CHAR (4) ; S.S# SP.S#

12. Domaines avec contraintes CREATE DOMAIN CITY AS CHAR (15) CONSTRAINT VALID_CITIES CHECK (VALUE IN ('???', 'Athens', 'Paris', London')) ; • Les valeurs légales sont les quatre et NULL ! • à moins d'ajouter la clause: AND VALUE IS NOT NULL • Travail bâclé au niveau du standard !

13. Domaines avec contraintes • Clause VALUE peut être aussi complexe qu'une expression SELECT de SQL • Notamment, on peut référencer les valeurs qui changent dans le temps • cette possibilité fait hurler certains (C.J. DATE) • On peut ajouter la clause définissant une valeur par défaut dans le domaine DEFAULT VALUE ('???') ; DEFAULT VALUE CURRENT-TIME ; DEFAULT VALUE CURRENT-DATE ; • Cette valeur devient celle par défaut de tout attribut basé sur ce domaine.

14. Domaines avec contraintes CREATE DOMAIN INT2 AS INTEGERCONSTRAINT TWO_BYTES CHECK (VALUE >= -32768 AND VALUE <= 32767) ; CREATE TABLE T ... ;CREATE DOMAIN D CHECK (VALUE IN (SELECT C FROM T)) ; CREATE DOMAIN INT1 AS INT2CONSTRAINT ONE_BYTE CHECK (VALUE >= -128AND VALUE <= 127) ; Légal dans SQL2Peut être mise à jour Illegal dans SQL2 (pas de sous-types) • Pour + sur les domaines • voir un livre sur SQL-2

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

16. 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 • indexes uniques obligatoires pour les clés dans le DB2

17. Option CLUSTER Page 2 de C 2 Aberdeen 3, Athens 3 London 3, Milan 4, Moscou 4, Paris 5, Rome 8.... CREATE INDEX C ON S (CITY) CLUSTER; • Même ordre physique: • des entrées de l'indexe CITY • des tuples de S dans leurs pages Page 3 de S 3 1 Smith Aberdeen 100 ... 1 McLeod Aberdeen 100 ... 2 Selis Athens 150 3 Thacher London 3...

18. Indexes • Définition des indexes ne devait pas être à ce niveau de SQL (c'est le schéma interne) • En principe, une table peut avoir un nombre quelconque d'indexes • Les indexes accélèrent les recherches • Mais, les indexes pénalisent les mises à jour ! • Pourquoi ?

19. 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 • ‘;’ sous Access97

20. Example: Table P de S-P • Attention: Type Counter -> Autonumber in MsAccess-97

21. 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 • Memo • taille < 64K caractères • supporte seulement la sélection SQL & QBE • Date/Time • supporte l ’arithmétique de dates/temps • 21/3 - 21/2 = 28 • 21/4 - 21/3 = 31 ? • prévu pour 21-ème siècle (Access97) • 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 • Autonumber • compteur automatique ou OID (option random dans NewValues)

22. MsAccess: Types de Données • Hyperlink • 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 • supporte seulement la sélection SQL & QBE • OLE objet • tout objet Windows • multimédia ou programme • peut être copié ou référencé • il faut double-cliquer sur sa description textuelle dans le tuple pour le voir

23. MsAccess: Champ Number & Currencyclause Field size • Byte 0 à 255 • Integer -32,768 à 32,767, 2 octets. • Long Integer -2,147,483,648 à 2,147,483,647. 4 octets. • Single Six digits de precision -3.402823E38 à 3.402823E38. 4 octets. • Double (Default) 10 digits de precision 1.79769313486232E308 à 1.79769313486232E308. 8 octets. • Replication ID • Pour les bases dupliquées - 16 octets • un OID • peut être aussi dans le type Autonumber

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

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

26. 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 « Limit to List » • On peut la faire aussi sans cet assistant (et mieux) •  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;

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

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

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

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

31. Contraintes référentielles mutuelles en SQL-2 • On utilise CREATE SCHEMA ou combinaison de CREATE TABLE et ALTER TABLE CREATE SCHEMA AUTHORIZATION Witold CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT REFERENCES t2(c1)) CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT REFERENCES t1(c1))

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

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

34. Exemple canon SP S P

35. 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] Predicate: ALL, DISTINCT, DISTINCTROW, TOP.

36. Interrogations simples • Projections d'une table sur certains attributs: SELECT S# FROM S ;

37. Interrogations simples • Projections d'une table sur certains attributs: SELECT S# FROM S ; S# s1 s2 s3 s4 s5

38. Interrogations simples • Projections d'une table sur certains attributs: SELECT S# FROM S ; S# s1 s2 s3 s4 s5 • L'ordre de présentation est fixé par le SGBD et peut changer dans le temps

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

40. Selection avec duplicata SELECT CITY FROM S;

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

42. Elimination de duplicata SELECT DISTINCT CITY FROM S;

43. Elimination de duplicata SELECT DISTINCT CITY FROM S; city Athens London Paris

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

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

46. 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 ?

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

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

49. 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 ?

50. 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ù?