Modèles informatiques

RESULTAT DE LA TRANSFORMATION

Ne pas oublier d'y ajouter les CI explicites

Graphe de dépendance avec agrégation

similaire avec graphe intermédiaire, sauf qu'on rajoute des étapes intermédiaire pour passer d'une variable dimensionnée ou agrégée à une simple variable et vice-versa CANs : SOMMEp (PRIX DE VENTEp * NB VENTESp,s) Le chiffre d'affaire par semaine est la somme en fonction des produits de (SOMMEp) : du prix de vente par produit et par semaine multiplié par le prix de chaque produit

Schéma relationnel (composition)

-La liste des relations (nom de la table) et leurs attributs -Le prédicat de la relation -La clé de chaque relation -Les clés étrangères entre relations (clé étrangère -> clé)

Spécialisation/généralisation UML

Il s'agit de créer une classe (C) et une sous-classe (SC) dans notre schéma UML car plusieurs classes ont des rôles et attributs en communs. Ici la classe clients va devenir la classe, car les autres tables sont également des clients

Algèbre relationnel

Intersection : S ∩ R : Donne lignes communes à S et R Union : S ∪ R : Donne Ligne de S & R Différence : S-R (≠R-S) : Donne ligne de S non comprises dans R Produit Cartésien : S*R : Combine les lignes de S&R Projection: π(col1, col2) : Montre seulement les attributs choisis dans les lignes Filtrage : σ(colonne 1) = « ... » : Fait ressortir les lignes où un attribut particulier prend une certaine valeur Jointure naturelle : S JOIN R : joint deux relations qui ont un attribut en commun Thêta jointure : jointure entre S & R et n'ayant pas d'attributs(colonnes) en communs, mais ayant des valeurs identiques dans chacune d'une de leur colonnes.

RECHERCHE

La fonction =RCHERCHEV(valeur que l'on veut trouver dans la 1ère colonne; plage ; noColonne de la valeur qui va être inscrit dans la cellule ; [valeur_proche], optionnel, soit VRAI ou FAUX, dit si l'on veut une valeur proche ou non, Vrai par défaut). Avec ceci, on peut faire une recherche de façon verticale sur la 1ère colonne d'une plage afin de trouver une ligne concernant une certaine valeur que l'on donne, puis de trouver une autre valeur sur cette ligne que l'on cherche (slides 20 à 22). On peut utiliser =RECHERCHEH pour une recherche sur la 1ère ligne horizontale.

Table centrale

Ne contient que des clés étrangères(souvent un numéro) redirigeant sur les clés primaires des dimensions ainsi que d'autres attributs numériques propres (faits) appelés mesures, c'est ces données qu'on va chercher à aggréger. Dans les dimensions, il n'y a que des données qualitatives

TRANSFORMATION 5/5 Rôle MAX *-*

On créer une nouvelle relation intermédiaire entre les deux autre relations. Les rôles seront de multiplicité 1..1 pour la nouvelle relation et de 0...* pour les anciennes. Selon les règles Rôle max *- 1 , la nouvelle relation va prendre les clés des anciennes relation comme clés étrangères et clés primaires soulignées

Comment vérifier qu'une contrainte d'intégrité est valide ?

On va effectuer une commande qui pourrait prouver que la contrainte n'est pas respectée. Certaines de ces contraintes sont explicites(heure d'arrivée < heure de départ)

Fonction HAVING (GROUP BY)

Permet de filtrer les paquets d'agrégation(GROUP BY) -> Se place après GROUP BY -> Doit reprendre la fonction agrégative

Clé étrangère

RELATION1[attributX] ⊆ RELATION2[attributX] L'attribut X est la clé de la relation 2 et la clé étrangère de la relation 1

Normalisation

Sert à corriger redondances dans nos tables qui peuvent poser problèmes le jour où la base de donnée est modifiée. Cela consiste à extraire la dépendance de la table pour corriger ce genres d'anormalités: - Modifier: On viole la dépendance C->D, en effet, il y aura plusieurs valeurs D pour un seul attribut C - Ajouter: La dépendance C->D (pilote-> noPermis); Si je veux ajouter cette dépendance, je ne connais pas A, B - Supprimer: On perd l'information de la dépendance si elle n'est contenue que dans une seule ligne

Variable dimensionnée et indices

Un indice permet de distinguer les différentes valeurs d'une variable (dimensionnée) e= employé m= mois salaire e,m = salaire brut par employé et par mois

Jointures IN

Une jointure naturelle(INNER JOIN) peut également être faite en utilisant la commande IN -> à utiliser comme pour les contraintes d'intégrité. NOT IN est équivalant à <>, à ne pas utiliser dans une commande de jointure, seulement dans le WHERE

UML

Unified modelling language Permet de passer d'une situation réelle à un schéma de SQL

Modèle de calcul

outil qui appréhende la réalité en transformant des variables dites input en output par règles de dérivation.

Fonction agrégative

prend un ensemble de valeur et redonne une valeur unique (min, max, somme)

Schéma relationnel pour un cube

réunis toutes les dimensions connues d'un fait dans une table

Clé d'une relation (caractéristiques)

-Unique et irréductible; il y en pas 2, on ne peut lui enlever des attributs

Commande de jointure interne

-WHERE: FROM X A,Y B WHERE B. .... = a. .... -INER JOIN: FROM X A INNER JOIN Y B ON A. ... = B. ...

Data mining

Découvrir patterns en analysant des données, les visualiser, les corriger pour aider à la décision

Rôle d'une association UML

Définit la nature de l'association entre objets

Couverture minimale

Dépendances fonctionnelles minimales à connaître pour en déduire toutes les autres, autrement dit, on ne peut pas les déduires.

INDEX

La fonction index sert à donner retrouver des données dans une matrice en utilisant les indices de la plage et pas toute la forme Excel : =INDEX(nom de la plage ; nb ligne, nb colonne) (slide 18 et 19).

Schéma relationnels idéal(normalisation)

La partie gauche d'une dépendance est la clé d'UNE table

TRANSFORMATION 2/5 Rôle MAX 1-*

La relation la plus contraignante(valeur de (rôle max - rôle min) la plus petite) parmi les deux relation va prendre la clé de l'autre relation comme clé étrangère Attention si mult. min. de la relation la plus contraignante est 0, mentionner la clé étrangère est facultatif

TRANSFORMATION 1/5 Création des relations

Les classes deviennent des relations et l'identifiant simple souligné devient une clé primaire (également soulignée)

Règles de dérivations

Lie des variables par expressions mathématique: Coût carburant = prix carburant/l * consommation (l/100km) * kilomètres parcourus On peut aouter des conditions: SI carburant = essence, ALORS prix carburant = prix essence, SINON prix = carburant = prix diesel

Types de multiplicité UML

On indique comme cela la multiplicité d'un rôle sur un schéma: Multiplicité min...-Multiplicité max Exemples: 1...1 = 1 : Exactement un 0...* = * : Zéro ou plus 1...* : Un ou plus 0...1 : zéro ou un a...b : intervalle spécifique

variable

associe libellé à une valeur Numérique/temporel/logique/texte

Indices dans exel

correspond aux lignes et colonnes

Concepts UML

- Objets: Ligne d'une relation - Classe d'objets: Relation - Attributs (des objets) - Associations

Relation

SQL: Tableau ayant pour colonnes des ensembles. Issu d'un produit cartésien d'ensembles

SQL

Structures query language - bas de données

Fonction COUNT() (GROUP BY)

Figure dans le SELECT

Méthode générale (finale) pour composer les écritures SQL

Concrètement, dans le SELECT, je définis ce que je veux afficher, c'est-à-dire des atributs et une fonction aggregative. Il n'est pas nécessaire d'afficher les attributs, mais ça n'a aucn sens de ne pas le faire ; on aura juste des données agrégées sans rien d'autre pour préciser Plus loin, je dois spécifier dans le GROUP BY que je veux avoir mon agrégation par attribut (tous les attributs autre que la fonction aggregative doit figurer) ; si je veux faire une moyenne de notes par élèves et par sujet, je dois mettre dans GROUP BY que je veux faire des blocs par élèves et par sujet. Dans le HAVING, je peux mettre des conditions à mon aggregation, et dans ORDER BY, je peux trier la manière dont s'affiche mon aggregation.

Dépendance fonctionnelle

Dans une relation donnée, si pour une certaine valeur de l'attribut A il n'existe au plus qu'une seule seule valeur de l'attribut B, on dit qu'il a une dépendance fonctionnelle A -> B Par exemple, un no client ne correspond qu'à un seul nom de client.

Fermeture transitive

En dérivant les dépendances fonctionnelles(obtenues grâce à des contraintes décrites dans un énoncé) par transitivité, projection, etc... , on trouve toutes les dépendances, tous les faits sont trouvés.

CI 4 Contraintes cycliques : Un sinistre impliquant un véhicule doit être couvert par un contrat qui assure ce véhicule

Faire la jointure pour les deux chemins afin d'avoir un cycle et essayer de joindre le moins de table possible; si R(a,x) R2(b,x) et R3(c,x) -> R INNER JOIN R3 ON x.R = x.R3 -> à selectionner: WERE ... NOT IN (SELECT ... ...= clés pour les 2 chemins de la relation en question

Identification des clés à partir de la couverture minimale

Faire un schéma en réseau de plomberie qui reprend la couverture minimale. Pour que « l'eau s'écoule » à travers une valve, la valve doit recevoir de l'eau des deux attributs en amont. La clé sera la combinaison d'attribut qui irrigue tout le réseau. Il peut y avoir plusieurs combinaisons

CI 5 Contraintes sur des valeurs (de plusieurs relations : Tous les contrats sont établis avant les sinistres qu'ils couvrent.

Faire une jointure où une valeur d'un attribut de la table 1 est </> à un attribut de la table 2

CI 3 Contrainte d'exclusion : Un contrat ne peut couvrir à la fois un véhicule et une habitation

Faire une jointure sur les attributs qui ne sont pas censés être identiques pour 2 relation (véhicule et habitation)

Commande SQL OUTER JOIN (LEFT/RIGHT)

Fait une jointure de deux tables sur deux attributs, mais retourne également les lignes qui n'on pas pu se joindre: SELECT* FROM X (LEFT/RIGHT) OUTER JOIN Y on X.A = Y.B Ici, LEFT donne les valeurs de la table X et RIGHT celles de Y

Table de données(simulation)

Fonction(profit) dépendante des variables x & y(charges) 1) Faire un tableau à 2 colonnes(libellés + valeurs), représentant toutes les étapes pour arriver au résultat (c.à.d. P*Q - Coût 1 - Coût 2 -> profit) 2) Reprendre le résultat dans une autre cellule(=Résultat) et directement y ajouter des valeurs(choisies arbitrairement) en lignes(x) et colonnes(y) *l'espacement entre chaque valeur de x et y peut être défini par une fonction 3) Sélectionner une plage comprenant toutes les cellules mentionnée en 2) et aller : Données > Scénario > Table de données et y choisir les cellules des variables x et y(dans le tableau en 1) 4) Donner de la couleur aux valeurs: Accueil > Mise en forme conditionnelle > Nuances de couleurs, puis choisir ses préférences *Si la fonction retourne un arguement logique, il faut définir une couleur pour caque réponse

Identifiant hybride UML

ID: attribut(≠clé) + rôles 1...1 La classe n'as pas de clé et on doit combiner un attribut pertinent avec un rôle 1...1 dans une note externe Par exemple, si un contrat ne peut pas être déterminé par son NoDossier ( un contrat a plusieurs dossiers ), on doit combiner le NoDossier et le rôle est conclu par 1..1 comme clé hybride

Export SQL -> EXEL

Il s'agit de transférer un entrepôt de donnée sur SQL vers un classeur EXEL A) Rassembler les infromations de l'entrepôt SQL 1. Faire un SELECT comprenant tous les attributs désirés qu'on renomme avec la fonction AS .... 2. Faire la jointure entre la table centrale et les dimensions comprenant les informations en 1 B) Exporter vers EXEL 1. Exporter les résultat de la requête au format .csv 2. Importer ce fichier dans EXEL ( format UTF-8, délimiteur Semicolon )

TRANSFORMATION SQL association cyclique

Il s'agit se suivre les règles *-* : On créer une relation avec la clé de la relation comme clé primaire et étrangère, on la met deux fois; une fois par rôle, on nomme ces clés de manière à comprendre le lien *-1 : On crée un nouvel attribut dans la relation qui est une clé étrangère qui renvoie sur la clé de la même relation. Le nom de cet attribut reprend le nature et la multiplicité de la multiplication

Commande SQL pour association cyclique

Joindre la table avec elle même par le biais d'un alias (reprendre la même table mais en la nommant différemment)

TRANSFORMATION 3/5 Identifiants hybrides(Rôles 1...1

L'attribut de l'identifiant hybride est souligné dans sa relation, tandis que pour le rôle 1...1, on créer une clé primaire soulignée dans notre relation qui sera également la clé étrangère provenant de la relation avec laquelle notre relation était associée Attention si mult. min. de la relation la plus contraignante est 0, mentionner la clé étrangère est facultatif

TRANSFOMRAMTION SQL spécialisation métode 1

Méthode 1: Appliquer méthode de transformations classiques (1-*) -> clé étrangère = clé primaire

TRANSFORMATION SQL spécialisation méthode 2

Méthode 2: Tout faire figurer dans une seule relation, on sépare les niveaux de classes et les classes d'un même niveau par un: - est un SC1 (oui/non) - attributs de SC1(0..1) - est un SC2 (oui/non) - attributs de SC2(0...1)

Variables logique

NON, ET, OU

Opération de la spécialisation UML

On aura une classe C et des sous classes SC La classe C va hériter des attributs communs des SC, tandis que les SC ne vont garder que leur attributs propres. Dans le schéma, des flèches partent des SC et s'unissent pour pointer vers C

Fonction ORDER (GROUP BY)

Permet d'ordre les paquets dans un certain ordre, on peut soit ordonner en fonction de l'attribut de GROUP BY ou soit en fonction de l'agrégation Parfois on nous demande de trier dans l'ordre, mais on peut trier un nom de mois dans l'ordre Dans ce cas il fait avoir un attribut reprenant le numéro du mois, et il faut mettre cet attribut à la fois dans GROUP BY (à la suite du nom du mois qu'il faut quand même mettre !) et à la fois dans le ORDEFR BY

Contraint d'intégrité (définition)

Propriété logique (VRAI/FAUX) du schéma relationnel qui est toujours correcte dans les relations de la base de données. Certaines de ces contraintes sont explicites(heure d'arrivée < heure de départ). Elles sont exprimées en français ou sous forme d'algèbre relationnel On connaît déjà deux types de contraintes: -Dépendances fonctionnelles -Contraintes référencielles(ref. des clés étrangères)

Prédicat d'une relation

Relation(attribut1, attribut2, ...) Un .... est identifié par un certain ..., ..., ... et comporte/a/etc ..., ..., ...

COMMANDES SQL(1er cours)

SELECT Col1, Col2 : Choisit colonnes d'une relation à afficher SELECT* : Affiche toutes les colonnes d'une relation SELECT DISTINCT : Permet de n'afficher qu'une fois les lignes identiques (il peut y en avoir car on ne choisit pas d'afficher toutes les colonnes FROM Rel1 :Choisit de quelle relation(table) provient les colonnes à afficher WHERE Permet de filtrer les colonnes à afficher en fonction d'un critère WHERE Col =/</> : Lignes où un attribut est égale/inférieur/supérieur à une valeur WHERE Col = '...' : Attribut = texte WHERE Col LIKE '%..%' : Filtrer selon que l'attribut contient ... à quelque part d'un texte AND ... Permet d'ajouter encore un critère ORDER BY Col1 ASC, Col2 DSC : Permet d'ordonner les lignes à afficher en fonction de l'ordre croissant/décroissant des valeurs d'un attribut, puis d'un autre éventuellement

Validation d'une contrainte d'intégrité par commande

SELECT* FROM X WHERE X.A NOT IN (SELECT A FROM X WHERE critères de la contrainte) On va chercher à afficher les données où un attribut particulier ne figure pas dans les données de cet attribut respectant la contrainte

FONCTIONS D'AGRÉGATION(1er cours SQL)

SUM/AVG/MIN/MAX/COUNT SELECT Col SUM(col1) AS .... Permet l'agrégation des attributs affichés

Diagramme de classes UML

Schéma représentant toutes les classes, leurs association(rôles et multiplicités), ainsi que les contraintes d'intégrités liées à une association

Identifiant d'une classe UML

Similaire à clé primaire, mais est constituée au maximum d'un seul attribut

TRANSFORMATION 4/5 Rôle MAX 1-1

Un des rôles est forcément 0-1 car sinon(1...1-1...1), l'un ne peut exister sans l'autre. Ici, la relation qui a le rôle 1..1 prend la clé de l'autre relation comme clé étrangère Attention si mult. min. de la relation la plus contraignante est 0, mentionner la clé étrangère est facultatif

Clé d'une relation

Un ou plusieurs attributs, qui combinés permettent d'identifier un objet(ligne) en particulier; il n'existe pas ligne ayant une même clé Clé primaire: représentatif de la classe; souligné Clé secondaire: les autres attributs Attribut facultatif: PeuT être vide : <NULL>

Commandes exel de base

Une cellule peut être déplacée et les cellules de références pour les fonction s'adaptent également. Si on renomme un groupe de cellules(matrices) ou si on bloque des lignes ou des colonnes avec des $ dans les formules, les cellules en question de s'adaptent pas.

Dépendance stricte

dépendance fonctionelle sauf qu'il y a une combinaison de plusieurs attributs qui définissent un autre attribut. Pilote, Date, heure -> Vol Un pilote, une date et une heure ne correspond qu'à un seul vol. Si on enlève l'heure, il peut y avoir plusieurs vols en une journée

Multiplicité d'une association UML

précise le nombre maximum auquel un objet peut être associé à un autre par rôle: -Lier 1 objet avec plusieurs autres -Lier 1 objet avec 1 seul autre -Lier plusieurs objets à plusieurs objets

CI 2 Contrainte d'inclusion : Seul un véhicule assuré par un contrat de type RC ou RC casco peut faire l'objet d'une déclaration de sinistre

·

Critères des règles de dérivation

-Complète: définie end tous cas -Non-ambiguë: pas définie plusieurs fois -Sans branches mortes: où les conditions sont toujours fausses

Graphe de dépendance

Input (donnée) -> variable interne -> output (résultat) Idée: A influence B et B influence C

Algèbre relationnel (exprime relation e

-Intersection: R∩S : Donne ligne commune à R & S -Union: R∪S : Donne lignes de R & S -Différence : R-S (≠ S-R): Donne les lignes de R sans les lignes communes entre R & S -Produit cartésien: R*S : Donne une combinaison des lignes de R & S -Projection: π(attribut 1, "2, 3") : Donne des lignes montrant uniquement l'/les attribut(s) souhaité -Filtrage

Interrogation du schéma (Entrepôt)

-Joindre les dimensions qui nous intéressent à la table centrale -Dans le SELECT, choisir les attributs ainsi qu'appliquer l'agrégation souhaitée -Faire des paquets avec le GROUP BY; permet d'exprimer l'agrégation, par exemple une moyenne de notes, en fonction d'un ou plusieurs attribut; par exemple en fonction de l'année. Sans GROUP BY: Donne moyenne de notes (1 seule valeur retournée) avec GROUP BY: Donne moyenne de notes en fonctionne chaque valeur que peut prendre l'attribut(ici ce sera pour toutes les années) -> l'attribut dans GROUP BY doit impérativement figurer dans le SELECT

Simulation(EXEL)

-Table de données -Valeur cibles

Caractéristiques d'une relation(table)

-Toute ligne est différente -Ordre des lignes et colonnes indifférent - Valeur de l'attribut est ATOMIQUE

Valeur cible(simulation)

1. Choisir cellule avec valeur à atteindre(souvent résultat) 2. Choisir valeur cible 3. Déterminer la cellule de la variable à modifier

Faire schéma UML pour un entrepôt

1. Identifier activité et ses processus: ici, la classe principale, donc offre; c'est les infos concernas les offres qui nous intéressent 2. Choisir niveau d'unité le plus petit pour les dimensions: une offre en particulier 3. Choix des dimensions 4. Choix des faits/granularité = les attributs de la classe principale; montant, montant actuel

Visualisation(EXEL)

1. Importer des données Données > Fichier texte > fichier.csv (choisir UTF-8, point-virgules, décimales en point) 2. Créer un graphique en sélectionnant les lignes ou colonnes comprenant les données

Création du tableau croisé dynamique

1. Insertion > Tableau croisé dynamique et sélectionner où faire apparaître le tableau, sélectionner la plage des données avec Maj + Ctrl + Flêches 2. On choisit le(s) attributs qui nous intéressent et on les mets dans Valeurs, où on choisi la fonction d'agrégation sur le "i". 3. On choisit ensuite le groupement des infos (GROUP BY dans SQL) en mettant l'attribut groupant dans lignes ou colonnes (ne pas mettre dans le même "axe" que les données agrégées) 4. En cliquant sur la flèche en-haut de la première colonne, on peut choisir l'ordre des lignes en fonction des valeurs (ORDER BY dans SQL), et cela pour chaque colonne. 5. On peut également y choisir de filtrer les lignes(en fonction des étiquettes et des valeurs) dans le tableau et cela en fonction de plusieurs agrégations (HAVING dans SQL) 6. Il y a également une fonction Filtre, ou on peut filtrer en fonction d'attribut non présent dans le tableau croisé dynamique (WERE dans SQL), cela fait apparaître une ligne où on choit notre attribut 7. On peut faire un graphique croisé dynamique ainsi que ça propre colonne/ligne dans la rubrique Champs calculé

Créer une matrice (pas obligatoire pour recherche et index)

1. sélectionner une plage 2. Définir une fonction 3. SHIFT + COMMAND + ENTER

Formes normales

1ère forme: tout dans la table 2ème forme: R(a, b, c) et a->c devient R(a,b) et R(a, c) -> 1ère forme + Si un attribut dépend d'un clé, on le sort de la table 3ème forme: R(a, b, c, d) et c->d devient R(a,b,c) et R(c, d) -> 2ème forme + Si un attribut dépend d'un autre attribut on le sort Boyce-Codd: R(a, b, c) avec ab et bc comme clé et a-> c devient R(a, b) et R(a, c) -> ON sert tout sauf les clés

Association cyclique UML

Association liant les objets d'une même classe (multiplicité minimale obligatoire en début et bout de chaîne= 0)

Récursivité

C'est lorsque une variable dimensionnée est calculée à partir d'elle même par exemple: BUDGGETt = 3/4 * BUDGETt-1 + 300 - Ce calcul peut être indirect - Dans le graphe, il apparaît un cycle

Entrepôt de données

Collection de donnée servant à aider les gestionnaires dans leurs décisions. Ce données sont: -Orientées vers un sujet de gestion et non-dédiée à une application particulière -Propres, validées, en accord avec la boîte -Accumulée à travers le temps donc volumineuse