Management des Systèmes d’Information (MSI)

1. Management des Systèmes d’Information (MSI) Cours : chapitre 1 SQL - Relationnel

2. Cours de Management des Systèmes d’Information -- GI2A4GML0328 Objectifs : • Modéliser, concevoir un SI (UML) : identifier les besoins, réaliser, déployer, récupérer données et +. • Programmer dans MS-ACCESS (relationnel – SQL) • Préparer les enseignements ERP et PLM de 3ème année et option IAE Programmation • 16 sept .. 7 novembre : cours et TD (SQL , UML, études de cas) • Novembre : 6h de formation MS ACCESS 2010 • en décembre : TP et projet (12h encadrées + 20h travail perso) Évaluation : - Projet / TP - Examen (QCM en janvier)

3. Ils vous ont précédé

4. Chap 1 : modèle relationnel - SQL Historique : E.F. CODD dans les années 70 • modèle basé sur la théorie des ensembles • des langages pour manipuler les données (SQL) • une démarche pour représenter le « monde réel » Nombreux outils : MySQL (logiciel libre) version 5.6 ORACLE (Oracle corp.) version 12.1 + Business suite Access (Microsoft)versions 1997 – 2003 – 2007 – 2010 - 2013 SQL Server (Microsoft) version 2014 Ingres (Computer Associates)

5. Un point d’histoire Historique : E.F. CODD dans les années 70 1923 – 2003 Turing award 1981

6. Les “technologies” en 2014

7. Un point d’histoire du business Nombreux outils : MySQL (logiciel libre) ORACLE (Oracle corp.) Access (Microsoft) SQL Server (Microsoft) SAP …

8. Qu’est ce qu’un SI ? Constituant Constituant Constituant Constituant Organisations Task 2 Task 3 propriétés propriétés Task 1 Tâches, processus Quels systèmes ? S . I . Information structurée, bases de données

9. Constituant Constituant Constituant Constituant propriétés propriétés Quels systèmes ? • Consulter • Ajouter • Modifier • Supprimer Information structurée, bases de données

10. Où sont construites les ailes ? Notion de modèle • Qu’est ce qu’un modèle ? (Minsky 1968) http://web.media.mit.edu/~minsky/papers/MatterMindModels.txt A* est un modèle de A pour un observateur O ssi A* aide O à répondre aux questions qu’il se pose sur A. Observateur Modèle Système observé

11. Concepts de systémique (Lemoigne 1977) Système de pilotage (ou de décision) Décisions Informations traitées Informations externes Informations vers l’extérieur Système d ’informations Ordres, consignes Informations collectées Système Système opérant Flux sortants Flux entrants

12. Définitions : « Domaine » Domaines : Di = {di1, di2, ….. din}, Dj = {d Î R}, Dk = Di x Dj Di défini en extension, Dj et Dk en intention Exemples : • D_Num_securite_sociale = { n Î [1013 ; 3.1013]} • D_Nom = { chaine de 30 caractères} • D_Prenom = { chaine de 20 caractères} • D_Code_postal = { chaine de 5 chiffres} • D_Telephone = { chaine de 10 chiffres} • D_couleur = { bleu, vert, rouge, jaune } • D_type_stage = { operateur, Enquete, EDT, inge_adjoint, PFE} • D_titre = { chaine de 200 caractères} • D_date_début ={date} Pour Excel, Access et ttes les applis Microsoft, du 1er janvier 1900 au 31 décembre 9999 Espace discret (date) ou réel (temps), non Fini !

13. Codage des caractères ASCII 7 bits (128 caractères) ASCII étendu IBM (256 caractères) ASCII étendu Apple (256 caractères) Unicode (UTF-8, UTF-16, UTF-32)

14. Codage des nombres (et téléphone) la perte de la sonde martienne Mars Climate Orbiter le 23 septembre 1999 a mis en évidence un manque de formalisation dans les processus de transfert de données entre des partenaires du projet. « Les ingénieurs de Lockheed Martin Astronautics (Denver dans le Colorado), la firme qui a conçu et fabriqué la sonde martienne, avaient apparemment gardé la mauvaise habitude de travailler avec les unités du système Anglo-Saxons. De leur côté, les ingénieurs du Jet Propulsion Laboratory (Pasadena en Californie) travaillaient depuis des années dans le système métrique, reconnu au niveau international comme étant le système de référence. Il semble que lors du transfert des données entre le centre de Lockheed et celui du JPL, personne ne se soit rendu compte qu'il fallait convertir les données ! » (Mars 1999) 1 500,23 € 1500.28 $ 06-88-40-21-15 +33-6-88-40-21-15

15. Codage des dates (et heures) 12-10-2014 10-12-2014 14-12-10

16. Code barrre – RFID – QR code QR code Version 3, 29×29, 35-77 caractères. • RFID« Radio Frequency IDentification », • Lesétiquettes passives. Cesétiquettesàmoindrecoût (qques 0,05€) sont plus petites et possèdentunedurée de vie quasi-illimitée. Exemplesd’applicationsopérationnelles : • Traçabilité des bouteilles de gaz (Air Liquide, AGA), • Suivi des colis (Wal Mart), • Suivi des vêtements de travail loués (Elis), • Identification des animaux : remplace le tatouage (Ordicam), • Gestion des livres d’unebibliothèque, • Identification des bogies et wagons (SNCF).

17. 1 56 05 99 131 088 N° SS : un homme de 58 ans né à l’étranger Donnée, information • Donnée = signe + code • exemple : code ASCII (7 bits) ou étendu 12-10-2012 10-12-2012 588 DGA 38 06-88-40-21-15 +33-6-88-40-21-15 1560599131088 156 05 99 131 088 1 500,23 1500.28 Immatriculation : un véhicule immatriculé dans l’Isère avant avril 2009 588 DGA 38 • Information = donnée + modèle d’interprétation

18. Définitions : « Relations » Une relation (ou table) est un sous ensemble d’un produit cartésien de domaines. Une relation est définie par un nom. Le degré d’une relation est égal par définition au nombre de facteurs de ce produit cartésien. Exemples : relations • PERSONNE Í D_Num_securite_sociale x D_Nom x D_Prenom x D_Code_postal x D_Telephone • STAGE Í D_Num_securite_sociale x D_type_stage x D_Titre x D_date_deb Personne est de degré 5, Stage est de degré 4

19. Définitions : « Constituants » On appelle constituant (ou colonne ou attribut) d’une relation (ou table) le nom donné à une colonne de la table. On note R+ l’ensemble des constituants de R. Exemples : les constituants d’une relation • PERSONNE+ = {Num_securite_sociale, Nom , Prenom , Code_postal, Telephone } • STAGE + = { Num_securite_sociale , type_stage , Titre, date_deb } Ou plus simplement • PERSONNE (Num_securite_sociale, Nom , Prenom , Code_postal, Telephone) • STAGE ( Num_securite_sociale , type_stage , Titre , date_deb )

20. Définitions : « n-uplets » Un n-uplet - ou tuple (en anglais), instance, ligne - d ’une relation est un élément de cette relation Exemples : 2 relations • PERSONNE Í D_Num_securite_sociale x D_Nom x D_Prenom x D_Code_postal x D_Telephone • STAGE Í D_Num_securite_sociale x D_type_stage x D_Titre x D_date-deb

21. Constituant Constituant Constituant Constituant propriétés propriétés Définitions : propriétés, constituants, n-uplets Exemples : 2 relations • PERSONNE Í D_Num_securite_sociale x D_Nom x D_Prenom x D_Code_postal x D_Telephone • STAGE Í D_Num_securite_sociale x D_type_stage x D_Titre x D_date-deb

22. Définitions : clé, contraintes d’intégrité Toute relation ou table doit comporter parmi l’ensemble de ses constituants un sous ensemble qui identifie sans ambiguité chaque n-uplet : ce sous ensemble est appelé « clé »de la relation. Les constituants de ce sous ensemble sont présentés soulignés. Exemples : clé d’une relation • PERSONNE (Num_securite_sociale, Nom , Prénom , Code_postal, Téléphone) • STAGE ( Num_securite_sociale , type_stage , Titre, Date-début )

23. Définitions : Schéma de relations Le schéma d’une relation ou définition en intention de cette relation est constitué des éléments : • le nom de la relation • la liste des constituants et les domaines de chacun • les contraintes d’intégrité à respecter Exemples : PERSONNE (Num_securite_sociale : { n Î [1013 ; 3.1013]} , Nom : { chaine de 30 caractères}, Prenom : { chaine de 20 caractères}, Code_postal : { chaine de 5 chiffres} , Telephone = { chaine de 10 chiffres} ) STAGE (Num_securite_sociale : { n Î [1013 ; 3.1013]} , type_stage : { operateur, Enquete, EDT, inge_adjoint, PFE}, titre : { chaine de 200 caractères} date-début : {date} )

24. Relationnel : opérateurs ensemblistes • Soient R1 et R2 deux relations, on peut définir entre R1 et R2 un certain nombre d’opérations R1 et R2 ont même schéma Opérations entre relations de schéma distinct • union T = (R1 È R2) ou T = Union (R1, R2) • différence T = (R1 - R2) ou T = Minus (R1, R2) • intersection T = (R1 Ç R2) ou T = inter (R1, R2) • produit cartésien T = (R1 * R2) ou T = product (R1, R2) • jointure naturelle T = (R1 >< R2)

25. Définitions : opérateurs exemples • Soient R1 et R2 deux relations de même schéma • union T = (R1 È R2) ou T = Union (R1, R2) • différence T = (R1 - R2) ou T = Minus (R1, R2) • intersection T = (R1 Ç R2) ou T = inter (R1, R2)

26. Définitions : opérateur jointure • Soient R1 et R2 deux relations de schémas distincts, • jointure naturelle T = (R1 >< R2) T = R1 join (c=c) R2 Sera repris plus loin

27. Relationnel : opérations unaires sur une relation • Soit R une relation de schéma (A1, A2, A3, ... An ) • projection de Rsur les attributsAi1, Ai2, Ai3, ... Aip avec p < n est une relation R’ de schéma (Ai1, Ai2, Ai3, ... Aip ) dont les n-uplets sont obtenus par élimination des attributs de R n’appartenant pas à R ’ et par suppression des doublons. On note T = P Ai1, Ai2, Ai3, ... Aip (R) ou T = proj Ai1, Ai2, Ai3, ... Aip (R) • restriction (ou sélection) de la relation R par une qualification Q est une relation R ’ de même schéma que R dont les n-uplets sont ceux de R satisfaisant Q. On note T = sQ (R) ou T = selectQ (R)

28. Déf. : opérateurs projection, restriction • Soient R1 une relation, • projection de R1sur les attributsA et B T = P A, B, (R1) Attention à la suppression des doublons • restriction (ou sélection) de la relation R1 par une qualification Q = (C<30). T = s( C < 30) (R)

29. Déf. : fonctions de calcul Opération CALCULER R = CALCULER (R0, fonction1, fonction2, ...) ou      N = CALCULER (R0, fonction) R = fonction 1, fonction 2 (R0) Exemple R0 = LIGNE_COMMANDE On désire obtenir le chiffre d'affaires total HT, ainsi que le nombre total de produits commandés : R1 = CALCULER (LIGNE_COMMANDE, Somme (Quantité*PuHT), Somme (Quantité)) R1 = SUM Quantité*PuHt , SUM Quantité (LIGNE_COMMANDE)

30. Déf. : fonctions « regrouper et calculer » Exemple R0 = LIGNE_COMMANDE R= REGROUPER_ET_CALCULER (R0, att1, att2, ..., fonction1, fonction2, ...) R = att1, att2, …,fonction 1, fonction 2,… (R0) On désire obtenir le montant total HT de chaque bon de commande : R2= REGROUPER_ET_CALCULER (LIGNE_COMMANDE, N°BonCommande, MontantHt : Somme (Quantité*PuHT)) R = N°BonCommande, MontantHt : SUM Quantité*PuHT(LIGNE_COMMANDE)

31. Définitions : fonctions d’agrégation Les Fonctions d'agrégation       Elles sont utilisées dans les opérateurs : CALCULER et REGROUPER_ET_CALCULER. Les fonctions statistiques de base : Elles portent sur un ou plusieurs groupes de n-uplets et évidemment sur un attribut de type numérique (ou Date). Somme (attribut) : total des valeurs d'un attributMoyenne (attribut) : moyenne des valeurs d'un attributMinimum (attribut) : plus petite valeur d'un attributMaximum (attribut) : plus grande valeur d'un attribut Remarque : les valeurs "non renseignées" de l'attribut sont ignorées. La fonction de comptage : Compte () La fonction de comptage donne le nombre de n-uplets d'un ou de plusieurs groupes de n-uplets. Il n'est donc pas nécessaire de préciser d'attribut.

32. Définitions : contraintes dans une BD L’intérêt de l’usage de Base de données est que la BD permet la définition de « contraintes d’intégrité » qui seront automatiquement vérifiées à tout instant par la base : ces contraintes permettent de garantir la « cohérence » de la base. Elles sont de trois types : 1) contrainte de clé 2) contrainte de type de données (date, intervalle, liste… entier) 3) contrainte d’intégrité référentielle

33. Déf. : contrainte d’intégrité référentielle Permet de vérifier la présence de données référencées dans des tables différentes. Une contrainte d ’intégrité référentielle peut s’utiliser dès qu’une clé primaire d’une table est utilisée comme référence dans une autre table. On la nomme « clé étrangère » de la seconde table. ARTICLE (Code_article : entier long, Désignation : chaine de 50 caractères, prix : monétaire ) LIGNECOMMANDE (code_article : entier long, Num_commande : entier long, quantité : entier) CLIENT (Num_client : entier long , Nom : chaine de 30 caractères, Prenom : chaine de 20 caractères, Code_postal : chaine de 5 chiffres , Telephone : chaine de 10 chiffres ) COMMANDE (Num_commande : entier long, date : date, montant : monétaire client : entier long )

34. Formes Normales Permettentd’éliminertouteforme de redondancedansune BD. Redondance (Larousse) • Abondance excessive et non justifiée de termes dans le discours. • Caractère d'un énoncé qui réitère, sous plusieurs formes différentes, un même trait signifiant. • Terme redondant, redite. • En informatique et dans les télécommunications, duplication d'informations afin de garantir leur sécurité en cas d'incident (commandes redondantes, disques redondants…) • En Systèmes d’infos, information inutilement recopiée (posera pb en cas de modification) A propos des formes normales, http://fsmrel.developpez.com/basesrelationnelles/normalisation/

35. 1ère Forme Normale Une relation est en PREMIERE FORME NORMALE ssi • les valeurs de tous les attributs qui la composent sont atomiques • la relation possède une « clé » Exemple • PERSONNE (Num_securite_sociale, Nom , Prenom , Code_postal, Telephone) On ne peut gérer des listes de prénom ou plusieurs adresses ou plusieurs téléphones, dans la table PERSONNE ; par contre prénom peut être une chaine de nn caractères

36. Dépendance fonctionnelle Cette notion permet de capturer l’idée de dépendance entre informations en visant à limiter la redondance. Soit R une relation et X et Y des groupes d’attributs de R, il existe une dépendance fonctionnelle entre X et Y (on dit que X détermine Y) si dans R chaque valeur de X détermine une et une seule valeur de Y. Attention, cette propriété doit être vérifiée pour tous les n-uplets, y compris ceux à créer

37. 2ème Forme Normale Une relation est en DEUXIEME FORME NORMALE ssi • elle est en première forme normale • tout attribut n ’appartenant pas à une clé ne dépend pas d ’une partie de cette clé • PRET (Num_etudiant, NumLivre, Nom_etud , Prenom_etud , NomLivre, Auteurs, date_emprunt) Exemple N ’est pas en 2ème FN car « Nom_etud » ne dépend que de Num_etudiant, et de même « NomLivre », et « Auteurs » ne dépend que de NumLivre

38. 3ème Forme Normale Une relation est en TROISIEME FORME NORMALE (dite de Boyce Codd) ssi • elle est en deuxième forme normale • tout attribut n ’appartenant pas à une clé ne dépend pas d ’un autre attribut non clé • EMPLOYE (Num_securite_sociale, Nom, Prenom, Service, Adresse_service) Exemple Adresse_service dépend de service, la relation n’est pas en 3eme FN.

39. SQL (Structured Query Language) Le SQL (Structured Query Language) permet d'interroger une base de données, d'en modifier des informations. C'est un langage universel d'interrogation des bases de données, qui permet à différents systèmes d'échanger des données entre eux. ACCESS peut être interrogé en SQL via un macro langage qui “ cache ” le SQL (voir à ce sujet le Menu “ Requêtes ”, “ Spécifique SQL ” “ SQL direct ”). Normalisation ISO : norme SQL1 (1986, 1989) norme SQL2 (1992) norme SQL3 (1999) implémentée dans Oracle Dans la suite, le code SQL est représenté en ARIAL 12, les commentaires en Italique. MySQL 5.6 http://dev.mysql.com/downloads/

40. Instructions SQL SQL comporte 3 parties 1) - Le DML (Data Manipulation Language) Sélection d’information, création et mise à jour d’enregistrements SELECT, INSERT, UPDATE, DELETE, JOIN 2) -Le DDL (Data Definition Language) Création des tables, des attributs et des contraintes d’intégrité CREATE, ALTER, DROP, RENAME 3) -Le DCL (Data Control Language) Pour contrôler l’accès aux données GRANT, REVOKE

41. Select … from …. where …. DML (Data Manipulation Language) • La close WHERE • Elle permet de spécifier la ou les conditions que doivent remplir les lignes choisies. • Liste des élèves habitant Mexico. • SELECT NomElv FROM Eleves WHERE VilleElv = ‘Mexico'; • Liste des matières pour lesquelles l'élève "Simon-Suisse" a eu au moins une note. • SELECT NomMat FROM Notes WHERE NomElv = 'Simon-Suisse'; • Remarque : Dans la close WHERE, on ne peut utiliser que des propriétés qui sont dans la table sélectionnée

42. Select ….. DML (Data Manipulation Language) Le SELECT est la commande de base du SQL destinée à extraire des donnéesd'une base oucalculer de nouvelles données à partir d'existantes... Voici la syntaxe générale d'une commande SELECT : SELECT[DISTINCT ou ALL]* ou liste de colonnes FROM nom de table ou de la vue [WHERE prédicats] [GROUP BY ordre des groupes] [HAVING condition] [ORDER BY ] liste de colonnes NOTA: dans cette syntaxe, les mots clef du SQL sont en gras, les paramètres en minuscule et entre crochets on trouve les parties optionnelles En fait l'ordre SQL SELECT est composé de 6 clauses dont 4 sont optionnelles.Clauses de l'ordre SELECT :

43. L’éditeurd’expressions Dans MS – Access (de 95 à 2013) pour « calcul » (d’un nouvel atribut) ou « sélection » (d’entités)

44. Jointures …. DML (Data Manipulation Language) dans MS Access

45. Jointures …. DML (Data Manipulation Language) SELECT CLIENT.Telephone, COMMANDE.Montant FROM CLIENT INNER JOIN COMMANDE ON CLIENT.Num_client = COMMANDE.Num_client WHERE (((COMMANDE.Montant)>10000));

46. Jointures …. DML (Data Manipulation Language) SELECT personne.Nom, personne.Prenom, stage.Titre, stage.[Date-debut], stage.D_type_stage FROM personne INNER JOIN stage ON personne.Numéro = stage.Numéro;

47. Jointures …. DML (Data Manipulation Language) SELECT personne.Nom, personne.Prenom, stage.Titre, stage.[Date-debut], stage.D_type_stage FROM personne INNER JOIN stage ON personne.Numéro = stage.Numéro; !! La jointure a été faite sur l’attribut « numéro » !! personne.Numéro = stage.Numéro;

48. Jointures …. DML (Data Manipulation Language) SELECT personne.Nom, personne.Prenom, stage.Titre, stage.[Date-debut], stage.D_type_stage FROM personne INNER JOIN stage ON personne.Num_securite_sociale = stage.Num_securite_sociale;

49. Jointures …. DML (Data Manipulation Language)

50. Jointures interne, externe G et D…. DML (Data Manipulation Language)