3 Introduction au modle relationnel 3 1 Concepts

3 Introduction au modèle relationnel 3. 1 Concepts de base n n n Domaine : ensemble de valeurs Relation : ensemble de tuples SQL : multi-ensemble 15/06/2021 © Robert Godin. Tous droits réservés. 1

suite n Deux facettes du concept de table – Schéma d'une table (table schema) n n – ex: Client(no. Client, nom. Client, no. Téléphone) Instance ou extension d'une table n n définition de son type (intention) état de la table ~ variable qui contient un ensemble de lignes modifications d ’état Schéma relationnel – 15/06/2021 ensemble de schémas de tables © Robert Godin. Tous droits réservés. 2

Instance de BD Ventes. Plein. De. Foin 15/06/2021 © Robert Godin. Tous droits réservés. 3

3. 2 Contraintes d'intégrité du modèle relationnel 3. 2. 1 Contrainte de domaine et de valeur non nulle n T(A 1 : – D 1, A 2 : D 2, …, An : Dn) Di : domaine de Ai n Valeur – – 15/06/2021 nulle Comportement particulier Valeur inconnue Valeur non applicable. . . © Robert Godin. Tous droits réservés. 4

3. 2. 2 Clé primaire et contrainte d'entité n Clé unique (unique key) – – n Clé candidate (candidate key) – n clé unique minimale Clé primaire (primary key) – n ou superclé (superkey ) identifiant sert de mécanisme de référence aux lignes de la table Contrainte d'entité (entity constraint) – – 15/06/2021 clé primaire non nulle © Robert Godin. Tous droits réservés. 5

3. 2. 3 Contrainte d'intégrité référentielle n Clé étrangère non nulle clé primaire 15/06/2021 © Robert Godin. Tous droits réservés. 6

Représentation UML 15/06/2021 © Robert Godin. Tous droits réservés. 7

Nom clé étrangère nom clé primaire n Étiquette de la relation de dépendance 15/06/2021 © Robert Godin. Tous droits réservés. 8

3. 3 Conception logique : traduction du modèle conceptuel de données en schéma conceptuel relationnel n Output : schéma relationnel (niveau conceptuel) – – tables colonnes n types, – contraintes d ’intégrité n clés n domaines, . . . primaires, étrangères, . . . Input à la conception – 15/06/2021 modèle conceptuel des données © Robert Godin. Tous droits réservés. 9

3. 3. 1 Première ébauche du schéma : une table par classe 15/06/2021 © Robert Godin. Tous droits réservés. 10

3. 3. 2 Traduction des attributs n Attribut de la classe -> colonne de la table 15/06/2021 © Robert Godin. Tous droits réservés. 11

3. 3. 2. 1 TRADUCTION DES CONTRAINTES D'IDENTIFICATION (UNIQUE) n Contrainte UNIQUE -> clé candidate relationnelle – 15/06/2021 clé primaire ? © Robert Godin. Tous droits réservés. 12

3. 3. 2. 2 TRADUCTION DES TYPES DE DONNÉES 15/06/2021 © Robert Godin. Tous droits réservés. 13

Types de données déclarés n Domaine pas toujours supporté par le dialecte SQL 15/06/2021 © Robert Godin. Tous droits réservés. 14

3. 3. 2. 3 TYPES ÉNUMÉRÉS n Petit domaine invariant – 15/06/2021 création d ’un domaine VARCHAR + CHECK © Robert Godin. Tous droits réservés. 15

Création d ’une table n Gros domaine ou extensible – – – 15/06/2021 création d ’une table à part utilisé comme liste de valeurs (LOV Designer) introduction d ’une clé primaire artificielle ? © Robert Godin. Tous droits réservés. 16

3. 3. 2. 4 TYPES COMPLEXES n 1. Représentation explicite des attributs du type complexe 15/06/2021 © Robert Godin. Tous droits réservés. 17

2. Création d'une nouvelle table n N. B. Pas de partage de la même adresse ! 15/06/2021 © Robert Godin. Tous droits réservés. 18

3. 3. 2. 5 QUALIFICATEUR n Colonne dans la table du rôle opposé – 15/06/2021 voir 3. 3. 3. 2 © Robert Godin. Tous droits réservés. 19

3. 3. 2. 6 DISCRIMINANT n Redondant ? 15/06/2021 © Robert Godin. Tous droits réservés. 20

3. 3. 2. 7 ATTRIBUT MULTIVALUÉ n Table à part – n Petit tableau de taille fixe – n n colonnes (valeurs nulles) Encodage – n clé étrangère + colonne pour l ’attribut invisible au SGBD Oracle 8 – 15/06/2021 VARRAY, NESTED TABLE © Robert Godin. Tous droits réservés. 21

3. 3. 2. 8 COMPRESSION DES VALEURS DES ATTRIBUTS DE GRANDE TAILLE Supporté par le SGBD ? n Multimédia n 15/06/2021 © Robert Godin. Tous droits réservés. 22

3. 3. 2. 9 CRÉATION DE TABLES SUPPLÉMENTAIRES POUR LES ATTRIBUTS DE CLASSE 15/06/2021 © Robert Godin. Tous droits réservés. 23

3. 3. 3 Réalisation de l'identité par les clés primaires 15/06/2021 © Robert Godin. Tous droits réservés. 24

3. 3. 3. 1 CRÉATION D'UNE CLÉ PRIMAIRE ARTIFICIELLE De manière systématique ? n Mécanisme de SEQUENCE Oracle n 15/06/2021 © Robert Godin. Tous droits réservés. 25

3. 3. 3. 2 UTILISATION D'UN IDENTIFIANT NATUREL COMME CLÉ PRIMAIRE n 1. Utilisation d'un identifiant naturel (UNIQUE) n Si identifiant naturel trop lourd – 15/06/2021 introduire clé primaire artificielle © Robert Godin. Tous droits réservés. 26

2. Utilisation du qualificateur 15/06/2021 © Robert Godin. Tous droits réservés. 27

3. 3 IDENTIFIANT NATUREL POUR UNE SPÉCIALISATION Éviter de changer de clé primaire dans le contexte d ’une hiérarchie de généralisation n Si la table du parent est omise n – 15/06/2021 considérer identifiant naturel de la spécialisation © Robert Godin. Tous droits réservés. 28

3. 3. 4 Traduction des associations 15/06/2021 © Robert Godin. Tous droits réservés. 29

3. 3. 4. 1 CAS GÉNÉRAL : TRADUCTION D'UNE ASSOCIATION PAR UNE TABLE 15/06/2021 © Robert Godin. Tous droits réservés. 30

3. 3. 4. 2 TRADUCTION D'UN RÔLE ORDONNÉ POUR UNE ASSOCIATION 15/06/2021 © Robert Godin. Tous droits réservés. 31

3. 3. 4. 3 CLASSE ASSOCIATIVE 15/06/2021 © Robert Godin. Tous droits réservés. 32

3. 3. 4. 4 CAS UN À PLUSIEURS n 1. Traduction par une table ? 15/06/2021 © Robert Godin. Tous droits réservés. 33

2. Traduction par l'ajout d'une clé étrangère à privilégier n Navigation plus performante 15/06/2021 © Robert Godin. Tous droits réservés. 34

Renommer la clé étrangère au besoin 15/06/2021 © Robert Godin. Tous droits réservés. 35

3. 3. 4. 5 CAS UN À UN n 1. Une clé étrangère (du côté obligatoire) 15/06/2021 © Robert Godin. Tous droits réservés. 36

2. Une nouvelle table 15/06/2021 © Robert Godin. Tous droits réservés. 37

3. Fusion n Cas 1 -1 obligatoire 15/06/2021 © Robert Godin. Tous droits réservés. 38

3. 3. 4. 6 CAS DE L'AGRÉGATION n Comme une association normale 15/06/2021 © Robert Godin. Tous droits réservés. 39

Composition n Cas 1 -1 – ~ attribut complexe Mode SQL CASCADE n Oracle 8 n – 15/06/2021 VARRAY, NESTED TABLE © Robert Godin. Tous droits réservés. 40

3. 3. 5 Traduction des relations de généralisation/spécialisation Délégation n Fusion n Concaténation n 15/06/2021 © Robert Godin. Tous droits réservés. 41

3. 3. 5. 1 DÉLÉGATION 15/06/2021 © Robert Godin. Tous droits réservés. 42

Changement de clé primaire ? n Extraire le téléphone résidence d'un Membre possédant l'id. Utilisateur 12345 15/06/2021 © Robert Godin. Tous droits réservés. 43

Traduction de la contrainte {disjointe, complète} 15/06/2021 © Robert Godin. Tous droits réservés. 44

3. 3. 5. 1. 1 Cas de la multi-classification et de l'héritage multiple 1. Créer une table de jointure 15/06/2021 © Robert Godin. Tous droits réservés. 45

Alternatives 2. Identité commune sans table de jointure n 3. Identité séparée et redondance de données n 15/06/2021 © Robert Godin. Tous droits réservés. 46

3. 3. 5. 2 ANALOGIE AVEC UNE ASSOCIATION UN À UN n Clé étrangère – – dans l ’enfant dans le parent ? ? ? n Approche n Fusion – – n Arc de Designer vers le parent (approche par fusion) vers l ’enfant (approche par concaténation) Nouvelle table ? ? ? 15/06/2021 © Robert Godin. Tous droits réservés. 47

3. 3. 5. 3 FUSION 15/06/2021 © Robert Godin. Tous droits réservés. 48

3. 3. 5. 4 CONCATÉNATION Conserver une table pour le parent ? 15/06/2021 © Robert Godin. Tous droits réservés. 49

Cas d ’une référence au parent 15/06/2021 © Robert Godin. Tous droits réservés. 50

3. 3. 5. 5 TRADUCTION DES GÉNÉRALISATIONS POUR SYLERAT 15/06/2021 © Robert Godin. Tous droits réservés. 51

3. 3. 6 Traduction des autres contraintes 15/06/2021 © Robert Godin. Tous droits réservés. 52

3. 4 Algèbre relationnelle n Opérations de manipulation de données n Entrée n n – une table : opération unaire – deux tables : opération binaire Sortie – une table – permet la composition Origine de SQL ( « SQUARE de IBM, San Jose » ) 15/06/2021 © Robert Godin. Tous droits réservés. 53

3. 4. 1 Sélection ( ) prix. Unitaire < 20. 00 ET no. Article > 30 (Article) 15/06/2021 © Robert Godin. Tous droits réservés. 54

3. 4. 2 Projection ( ) no. Client, date. Commande (Commande) 15/06/2021 © Robert Godin. Tous droits réservés. 55

3. 4. 3 Expressions complexes date. Commande > 05/07/2000 Commande no. Client, date. Commande ( date. Commande > 05/07/2000 Commande) 15/06/2021 © Robert Godin. Tous droits réservés. 56

3. 4. 4 Produit cartésien ( ). . . Client Commande 15/06/2021 © Robert Godin. Tous droits réservés. 57

Exemple. Produire les informations au sujet des Clients et de leurs Commandes Client Commande clé primaire = clé étrangère Client. no. Client = Commande. no. Client (Client Commande) 15/06/2021 © Robert Godin. Tous droits réservés. 58

Exemple. Produire les informations au sujet des Clients et de leurs Commandes Client. no. Client, nom. Client, no. Téléphone, no. Commande, date. Commande (…)) 15/06/2021 © Robert Godin. Tous droits réservés. Colonne redondante 59

3. 4. 5 Jointure naturelle ( ) Colonnes communes : colonnes de jointure ou clé de jointure Client Commande 15/06/2021 © Robert Godin. Tous droits réservés. Schéma : union des colonnes 60

Sélectionner les nom. Client qui ont commandé au moins un plant d'herbe à puce 15/06/2021 © Robert Godin. Tous droits réservés. 61

Sélectionner les nom. Client qui ont commandé au moins un plant d'herbe à puce nom. Client ( description = “Herbe à puce ” (Client Commande Ligne. Commande Article)) 15/06/2021 © Robert Godin. Tous droits réservés. 62

Formulations équivalentes nom. Client ( description = “Herbe à puce ” (Client Commande Ligne. Commande Article)) n nom. Client ( description = “Herbe à puce ” (((Client Commande) Ligne. Commande) Article)) n nom. Client ( description = “Herbe à puce ” (((Client Ligne. Commande) Article)) n… n 15/06/2021 © Robert Godin. Tous droits réservés. 63

Quelques règles d ’équivalence · Eclatement d'une sélection conjonctive (SE) – · Commutativité de la sélection (SC) – · T 1 T 2 = T 1 T 2 Associativité de la jointure (JA) – n liste 1 ( liste 2 (T)) = liste 1 (T) Commutativité de la jointure (JC) – · e 1 ( e 2 (T)) = e 2 ( e 1 (T)) Elimination des projections en cascades (PE) – · e 1 ET e 2 (T) = e 1 ( e 2 (T)) T 1 (T 2 T 3) = (T 1 T 2) T 3 Voir évaluation des requêtes 15/06/2021 © Robert Godin. Tous droits réservés. 64

Représentation sous forme d ’arbre de l ’expression algébrique n nom. Client ( description Article)) 15/06/2021 = “Herbe à puce ” (((Client Commande) Ligne. Commande) © Robert Godin. Tous droits réservés. 65

Cas d ’une clé de jointure composée Ligne. Commande Détail. Livraison 15/06/2021 © Robert Godin. Tous droits réservés. 66

3. 4. 6 n Opération renommer ( ) Quels sont les Clients qui ont le même numéro de téléphone? Client 2 (Client) Client. no. Client, Client 2. no. Client, ( Client. no. Téléphone = Client 2. no. Téléphone (Client 2 (Client))) 15/06/2021 © Robert Godin. Tous droits réservés. 67

Renommer les colonnes n Quels sont les Clients qui ont le même numéro de téléphone? Client 2(no. Client 2, nom. Client 2, no. Téléphone) (Client) no. Client, no. Client 2 (Client 2(no. Client 2, nom. Client 2, no. Téléphone) (Client)) 15/06/2021 © Robert Godin. Tous droits réservés. 68

3. 4. 7 Jointure- Client no. Client = numéro. Client Commande altérée = no. Client = numéro. Client (Client Commande altérée) 15/06/2021 © Robert Godin. Tous droits réservés. 69

Cherchez les Ligne. Commandes qui ont été livrées en une seule livraison Ligne. Commande. no. Commande, Ligne. Commande. no. Artilce, quantité, no. Livraison, quantitéLivrée (Ligne. Commande Ligne. Commande. no. Commande = Détail. Livraison. no. Commande ET Ligne. Commande. no. Article = Détail. Livraison. no. Article ET quantité = quantitéLivrée Détail. Livraison) 15/06/2021 © Robert Godin. Tous droits réservés. 70

3. 4. 8 Jointure externe n · Inclure les « célibataires » La jointure externe à gauche · · La jointure externe à droite · · T 1 = T 2 (célibataires de T 1 ) T 1 = T 2 La jointure externe pleine · 15/06/2021 T 1 = = T 2 © Robert Godin. Tous droits réservés. 71

Produire les informations au sujet des Clients et de leurs Commandes incluant les informations sur les Clients qui n’ont pas placé de Commande Client = Commande 15/06/2021 © Robert Godin. Tous droits réservés. 72

3. 4. 9 Semi-jointure ( ) Client Commande 15/06/2021 © Robert Godin. Tous droits réservés. 73

3. 4. 10 Opérations ensemblistes ( , , -) n Schémas compatibles 15/06/2021 © Robert Godin. Tous droits réservés. 74

Produire les noms et numéros de téléphone des Employés qui sont aussi des Clients de la pépinière ( nom. Personne, no. Téléphone ( Client (no. Client, nom. Personne, no. Téléphone) (Client))) ( nom. Personne, no. Téléphone ( Employé (code. Employé, nom. Personne, no. Téléphone) (Employé))) équivalent à si compatibles 15/06/2021 © Robert Godin. Tous droits réservés. 75

3. 4. 11 n A B – – n Division ( ) plus grande table C qui est telle que B C A ~ inverse du produit cartésien T 1 (X) T 2 (Y) = – 15/06/2021 X-Y (T 1) - (( X-Y (T 1) T 2 ) - T 1) © Robert Godin. Tous droits réservés. 76

Quelles sont les no. Commande des Commandes qui incluent tous les Articles dont le prix. Unitaire est $10. 99 no. Commande, no. Article (Ligne. Commande) no. Article ( prix. Unitaire = 10. 99 (Article)) no. Commande, no. Article (Ligne. Commande) no. Article ( prix. Unitaire = 10. 99 (Article)) 15/06/2021 © Robert Godin. Tous droits réservés. 77

3. 4. 12 Ensemble minimal d'opérations de l'algèbre relationnelle n Sélection ( ) n Projection ( ) n Produit cartésien ( ) n Union ( ) n Différence (-) n Renommage ( ) 15/06/2021 © Robert Godin. Tous droits réservés. 78

3. 4. 13 Extensions de l'algèbre relationnelle pour les bases de données n Fonctions sur colonnes – – – n Fonctions de groupe (ou d'agrégat) – n opérations arithmétiques (+, - , *, /, …) manipulation de chaînes (concaténation, …). . . e. g. total, moyenne, écart-type, min, max, … Récursivité – 15/06/2021 e. g. fermeture transitive © Robert Godin. Tous droits réservés. 79