BDSI Bases de données Algèbre relationnel Le modèle relationnel Un modèle relationel a 3 parties Partie structurelle La strucutre de données du modèle, i.e., la relation, le schéma de la relation Partie manipulation il y a 3 langages de requête relationels l'algèbre relationnel : langage procédural le language relationnel: langage déclaratif Non­recursive Datalog : language de programmation logique Partie intégrité Force les relations d'une bases de données à respecter cetraines conditions Contraintes d'intégrité Partie sturcutrelle du modèle relationnel Le modèle relationnel n'a qu'une strucutre de données La relation Une relation est un ensemble de tuples Une base de données relationnelle est un ensemble de relations Un schéma de relation est un ensemble d'attributs décrivant le contenu des tuples Un schéma de base de données est un ensemble de schémas de relation Exemple et notation La relation R1 Student Name Age Address Toto 21 Clermont Dupont 35 paris Durand 45 Lyon Schema(Student)={Name, Age, Address} Attributs et domaines Soit U ensemble infini dénombrable d'attributs U est appelé Univers des attribut Soit D un ensemble infini dénombrable de valeurs D est le domaine de base de données sous­jacent Soit un attribut A dans U, le domaine de A, noté DOM(A), est un sous ensemble de D Unique Name Assumption (UNA) de valeurs c1 ∈ DOM(A1), c2 ∈ DOM(A2) sont égales si et seulement si elles sont syntaxiquement identiques, i.e., elles ont le même nom Exemple "Toto" = "Toto" mais "Toto" ≠ "Dupond" et "Toto" ≠ "21" Schéma de relation Un Schéma de relation R a les composants suivants le nom du schéma : R un opérateur type(R), représentant le nombre d'attribut de R un ensemble d'attributs {att(1), . . . , att(type(R))} noté schema(R) Exemple type(Student) = 3 schema(Student) = {att(1), att(2), att(3)} where att(1) = name, att(2) = age and att(3) = address Créer une relation en SQL CREATE TABLE Students ( Name : CHAR(20), Age : INTEGER, Address : CHAR(40) ) NB le type (ou le domaine) de chaque champs est spécifié, et vérifié par le SGBD à chaque insertion ou mise à jour Types atomiques Numérique : entier ou réel (SQL : Int, Float, etc.) Chaîne de caractères (SQL : Char(20), VarChar(32), etc.) Date (SQL : DATE, TIME, YEAR, etc.) Type énuméré Schéma de base de données Un schéma de base de données R est une collection {R1, ... , Rn} de relations schema(R) est l'union de tous les schema(Ri), ∀Ri ∈ R Un schéma de relation R est en 1ère forme normale(1NF) si tous les domaines de ses attributs Ai ∈ schema(R) sont atomiques Un schéma de base de données R est en 1ère forme normale(1NF) si tous ses schémas de relation Ri ∈ R sont en 1ère forme normale(1NF) Relations et bases de données Un tuple de R, avec schema(R) = {A1, ... , Am} est un membre du produit cartésien DOM(A1) × DOM(A2) × ... × DOM(Am) Un tuple t de R peut également être vue comme un "total mapping" de schema(R) dans l'union des domaine DOM(Ai) tel que ∀Ai ∈ schema(R), t(Ai) ∈ DOM(Ai) une relation R est un ensemble fini de tuples de R une base de données d sur R est une collection {r1 , . . . , rn } de relations ri sur Ri Les relations d'un schéma de relations 1NF sont appelées relations 1NF Projection et domaine actif Projection La projection d'un tuple t sur une relation r d'un schéma de relation R sur l'attribut Ai = att(i) est la ièmecomposante (ou encore la coordonnée i) de t, i.e., t(i) Soit Y = {att(i1), ... , att(in)} ⊆ schema(R) La projection de t sur Y, notée t[Y], est définie par t[Y] =<t(i1), ... , t(in)> Domaine actif Le Domaine actif d'une relation r de R, noté ADOM(r), est l'ensemble des valeurs qui apparraissent dans les tuples de r Le Domaine actif d'une base de données d sur R, noté ADOM(d), est l'union des domaines actifs de ses relations Exemple Schéma {Cle: INT, Nom: VarChar(20), Email: VarChar(20)} instance de ralation (aussi appelée Extension) { {Cle: 1, Nom: "Edgar", Email: "[email protected]"} {Cle: 2, Nom: "Frank", Email: "[email protected]"} ... } Clé et SuperClé SuperClé Un sous ensemble SK d'un schema schema(R) est une superclé pour R si pour toute relation r de R, la projection t[SK] d'un tuple t sur R identifie un tuple unique de r Clé Une clé d'un schéma de relation R est une super clé minimale de R Clé Primaire Une clé primaire de R est une clé choisie par l'administrateur de base de données Clé étrangère Soit R un schéma de base de donnée, R1, R2 ∈ R et K est la clé primaire de R2 FK ⊆ R1 est une clé étrangère pour R1 référençant la clé primaire K de R2 si elle vérifie la condition suivante ∀d = {r1 , r2, ... , rn} sur R et ∀t1 ∈ r1, si t1[FK] ne contient pas de valeur nulle, alors ∃t2 ∈ r2 tel que t1[FK] = t2[K] Déclarer un clé en SQL CREATE TABLE Students ( sid : CHAR(20), ssn : CHAR(20) NOT NULL, Name : CHAR(20), Age : INTEGER, dep : CHAR(5) PRIMARY KEY (sid), UNIQUE (ssn), FOREIGN KEY (dep) REFERENCES Department ) Langages de requêtes Le modèle relationnel défini de manière rigoureuse des langages de requêtes simples et puissants Approche algébrique Algèbre relationnelle Langage procédurale, mais très utile pour représenter les plans d’exécution des requêtes Approche logique Calcul relationnel Orienté utilisateur car il est plus proche du langage naturel permet à un utilisateur de décrire le résultat recherché sans spécifier comment l’obtenir (langage déclaratif) Datalog Langage déclaratif à base de règles Augmente le calcul relationnel avec des capacités d’inférence Notions préliminaires Une requête est appliquée à des instances de relations Le résultat d’une requête est une instance de relation Le schéma de la relation résultat est déterminé par la définition des constructeurs du langage de requêtes utilisés Exemple La relation r1 sur Student Name Age Address Dept Toto 21 Clermont Computing Dupont 35 paris Maths Durand 45 Lyon Linguistics Dan Lyon Computing 34 La relation r2 sur Department loc mgr Bât A Ullman dept Computing Bât B Mendelson Maths Bât C Dupond linguistics L’algèbre relationnelle Proposée par Codd en 1970 C’est une collection d’opérateurs algébriques Entrée : une ou deux relations Sortie : une relation Requête relationnelle Composition d’un nombre fini d’opérateurs algébriques chaque opération renvoie une relation propriété de fermeture de l’algèbre relationnelle Opérateurs relationnels Projection (π) Sélection (σ) Jointure (⋈) Renommage (ρ A → B) Division (÷) Opérateurs ensemblistes Pour des instances de relations de même schémas Union (⋃) Intersection (⋂) Différence (–) Pour des instances de relations de schémas quelconques Produit cartésien (×) Projection (π) et sélection (σ) Soit r une relation sur R La projection de r sur Y ⊆ schema(R), notée πY(r), est définie comme suit πY(r) = {t[Y ] | t ∈ r} sélection Une formule de sélection simple sur R est une expression de la forme : A = a ou A = B, où A, B ∈ Schema(R) et a ∈ Dom(A) Une formule de sélection est une expression composée de formules de sélection simples connectées à l’aide des connecteurs logiques ∧, ∨, ¬ et les parenthèses () Une Opération de sélection est une sélection des tuples de la relation r par rapport à F , σF(r), est définie comme suit : σF(r) = {t ∈ r | t ⊨ F } Implication logique (⊨) t ⊨ A = a si t[A] = a t ⊨ A = B si t[A] = t[B] t ⊨ F1 ∧ F2 si t ⊨ F1 et t ⊨ F2 t ⊨ F1 ∨ F2 si t ⊨ F1 ou t ⊨ F2 t ⊨ ¬F si t ⊭ F t ⊨ (F) si t ⊨ F Opérations ensemblistes Soient r1 et r2 deux instances de relations sur R Union : r1 ⋃ r2 = {t | t ∈ r1 ou t ∈ r2 } Différence : r1 – r2 = {t | t ∈ r1 ou t ∉ r2 } Intersection : r1 ⋂ r2 = {t | t ∈ r1 et t ∈ r2 } r1 ⋂ r2 = r1 – (r1 – r2) σ¬F (r) = r – σF (r) σF1 ∨ σF2 = σF1(r) ⋃ σF2(r) σF1 ∧ σF2 = σF1(r) ⋂ σF2(r) Produit cartésien Soient r1 et r2 deux instances de relations sur R1 et R2 respectivement avec schema(R1) ⋂ schema(R2) = ∅ Le produit cartésien r1 × r2 est une relation sur un schéma de relation R, avec schema(R) = schema(R1) ⋃ schema(R2), définie par r1 × r2 = {t |∃t1 ∈ r1 et ∃t2 ∈ r2 tel que t[schema(R1)] = t1 et t[schema(R2)] = t2} Si schema(R1) ⋂ schema(R2) ≠ ∅, alors il y a un conflit entre les attributs de mêmes noms dans R1 et R2 résolution de ce type de conflit à l’aide de l’opérateur de renommage Jointure Soient r1 et r2 deux instances de relations sur R1 et R2 respectivement. La jointure naturelle de r1 et r2 (notée r1 ⋈ r2 ) est une relation sur un schéma de relation R, avec schema(R) = schema(R1) ⋃ schema(R2), définie comme suit r1 ⋈ r2 = {t |∃t1 ∈ r1 and ∃t1 ∈ r1 tel que t[schema(R1)] = t1 et t[schema(R2)] = t2 } Si schema(R1) = schema(R2), alors r1 ⋈ r2 = r1 ⋂ r2 Si schema(R1) ⋂ schema(R2) = ∅, alors r1 ⋈ r2 = r1 × r2 Algorithme Join Soient r1 et r2 deux instance de relations sur R1 et R2 respectivement et X = schema(R1) ⋂ schema(R2) Join(r1, r2) Result := ∅ for all t1 ∈ r1 do for all t2 ∈ r2 do if t1[X] = t2[X] then Joined_tuple := a tuple over R such that Joined_tuple[schema(R1)] := t1 and Joined_tuple[schema(R2)] := t2 Result := Result ⋃ Joined_tuple Ensure: Result Renommage Soit r une instance de relation de R, A ∈ schema(R) et B ∉ schema(R) Le renommage de A en B pour r, noté ρA→B(r), est une relation sur S tel que schema(S) = schema(R) – {A} ⋃ {B}, et ρA→B(r) = {t|∃u ∈ r, t[schema(S) – {B}] = u[schema(R) – {A}] et t[B] = u[A]} Division La division permet d’exprimer la quantification universelle ∀ Exemple de requête pour la quelle la division est utile "Donner la liste des étudiants qui sont inscrits dans tous les départements" Définition formelle de la division Soit r une instance de relation sur R, avec schema(R) = XY , et s une instance de relation sur S, avec schema(S) = Y La division de r par s, notée r ÷ s, est une instance de relation sur un schéma de relation R1 , avec schema(R1) = X, définie par r ÷ s = {t[X]|t ∈ r et s ⊆ πY (σF(r)), où X = {A1, ..., Aq } et F est la formule A1 = t[A1] ⋀ ... ⋀ Aq = t[Aq]} Exemple de division Expression de r ÷ s La division n’est pas une opération essentielle car elle peut être exprimée à l’aide des opérations de projection, différence et produit cartésien Pour calculer r ÷ s, il faut calculer toutes les valeurs de X dans r qui ne sont pas disqualifiées par des valeurs de Y dans s Une valeur de X est disqualifiée si en lui rattachant une valeur de Y de s, le tuple XY obtenu n’appartient pas à r Valeurs disqualifiées de X : πX((πX(r) × s) – r) r ÷ s = πX(r) – valeurs disqualifiées de X ⇒ r ÷ s = πX(r) – πX((πX(r) × s) – r) Exemple Requête : "Donner la liste des parents et leurs petits­fils" πGparent,Gchild(ρChild→Jatt(ρParent→Gparent(Fami ⋈ρParent⇒Jatt(ρChild⇒Gchild(Family)))) Fonctions d’agrégation Les fonctions d’agrégation permettent de répondre aux requêtes du type "Quel est le nombre d’étudiants inscrits dans chaque département" "Quel est l’âge moyen des étudiants" Définition (fonction d’agrégation) Soit R un schéma de relation et A ∈ schema(R). Une fonction d’agrégation fA sur R est une fonction calculable qui pour un ensemble fini de tuples sur R renvoie une valeur entière. Exemples de fonctions : MIN, MAX, AVG, COUNT