Hoja de repaso: Introduction à la Conception et Normalisation des Bases

📋 Plan du Cours

1. Objectifs du cours et travaux pratiques
2. Programme : conception, contraintes et implantation
3. Des bases de données aux entrepôts de données
4. Historique des SGBD et évolution des besoins
5. Commandes SQL : LDD, LMD et LCD
6. Étapes de conception de bases de données
7. Conception du schéma et exigences de qualité
8. Normalisation relationnelle : 1FN à 3FN
9. Forme normale de Boyce-Codd et critères
10. Élaboration du schéma physique et SQL2/SQL3

📖 1. Objectifs du cours et travaux pratiques

🔑 Notions clés & Définitions

• Conception de bases de données : Démarche qui transforme un besoin en schéma structuré, en choisissant des modèles et des structures adaptés.
• PL/SQL : Langage procédural associé aux bases relationnelles, utilisé pour écrire des traitements et des requêtes côté SGBD.
• UML : Langage de modélisation orienté objet servant à représenter la structure et les relations d’un système avant implantation.
• SQL2/3 : Évolutions de SQL visant à exprimer davantage de concepts, notamment pour des structures plus riches et des contraintes.
• Intégrité des données : Ensemble des mécanismes qui garantissent que les données restent cohérentes et valides au fil des opérations.

📝 Points essentiels

• Le cours vise des notions avancées en bases de données sur 4 séances de 3h : conception, PL/SQL, UML, SQL2/3 et structures complexes.
• Les travaux pratiques couvrent 6 séances de 3h pour mettre en œuvre les concepts du cours (PL/SQL, UML→SQL2/3, intégrité des données, etc.).
• Le programme inclut des rappels de SQL avant d’aborder la conception et la modélisation de bases de données.
• La conception et la modélisation traitent la méta-modélisation et des formalismes comme ER, EER et UML.
• L’expression et la cohérence des contraintes mobilisent SQL2/3, PL/SQL et OCL pour spécifier et vérifier des règles.
• L’implantation couvre le relationnel-étendu et l’orienté objet, du modèle UML vers SQL2/3, avec des éléments d’accès comme JDBC/Java et PL/SQL.

💡 Astuce mémo

Cours = 4×3h (concepts BD avancées) ; TP = 6×3h (mise en œuvre) : PL/SQL + UML→SQL2/3 + intégrité.

📖 2. Programme : conception, contraintes et implantation

🔑 Notions clés & Définitions

• Big Data : Ensemble de très grands volumes de données, souvent variés, nécessitant des traitements et architectures adaptés pour la performance.
• Entrepôt de données : Base dédiée à l’analyse, qui consolide des données issues de systèmes opérationnels pour permettre des requêtes analytiques.
• OLTP : Mode de traitement transactionnel orienté opérations courantes, avec des temps de réponse de l’ordre de quelques secondes.
• OLAP : Mode d’analyse décisionnelle orienté requêtes analytiques, avec des temps de réponse typiquement inférieurs à 1 heure.
• LDD : Langage de définition de données qui crée, modifie ou supprime la structure et les objets d’une base de données.

📝 Points essentiels

• Les performances dépendent du volume, du type de données et de la nature du traitement (transactionnel, analytique, batch).
• OLTP vise des réponses en quelques secondes, tandis que le batch se fait plutôt en moins d’une heure.
• Un entrepôt de données vise des requêtes OLTP en moins de 10 secondes et des requêtes OLAP en moins d’une heure.
• Les traitements à grosse volumétrie exigent optimisation et suivi des activités du DWh, car certains traitements peuvent ne pas se terminer.
• Les contraintes techniques et fonctionnelles peuvent nécessiter des modifications après quelques jours d’exploitation.
• LDD regroupe CREATE, ALTER et DROP pour créer, modifier et supprimer des objets de base de données.

💡 Astuce mémo

OLTP = Opérations rapides (secondes) ; OLAP = Analyse (≤ 1 h) ; Batch = long (≤ 1 h).

📖 3. Des bases de données aux entrepôts de données

🔑 Notions clés & Définitions

• Langage de manipulation de données : Ensemble de commandes SQL qui permet d’insérer, modifier et supprimer des données dans une base.
• INSERT : Commande de manipulation qui ajoute de nouvelles lignes dans une table.
• UPDATE : Commande de manipulation qui modifie des valeurs existantes dans une ou plusieurs lignes.
• DELETE : Commande de manipulation qui supprime des lignes d’une table, éventuellement sous condition.
• Langage de contrôle de données : Ensemble de commandes qui gère les droits d’accès et les autorisations sur les opérations et les enregistrements.

📝 Points essentiels

• SELECT suit la forme SELECT liste_champs FROM liste_tables [WHERE condition] [options] ;
• INSERT INTO nom_table [(liste_champs)] VALUES (liste_valeurs) ajoute des données en respectant les champs fournis.
• UPDATE nom_table SET champ = expression [WHERE conditions] met à jour les enregistrements ciblés par la clause WHERE.
• DELETE FROM nom_table [WHERE conditions] supprime les enregistrements, avec ou sans filtre.
• Le LCD regroupe des commandes d’autorisation (GRANT) et d’interdiction (DENY) d’opérations.
• REVOKE annule une commande de contrôle précédente, et READ/UPDATE/DELETE servent à limiter les actions sur les enregistrements.

💡 Astuce mémo

CRUD = Create (INSERT), Update (UPDATE), Delete (DELETE) ; droits = GRANT/DENY puis REVOKE.

📖 4. Historique des SGBD et évolution des besoins

🔑 Notions clés & Définitions

• SGBD relationnel : Système de gestion de bases de données qui représente les données sous forme de tables et de relations.
• SGBD relationnel-objet : SGBD combinant le modèle relationnel et des éléments orientés objet, notamment via des types réutilisables.
• SGBD objet : SGBD où les données sont organisées en classes et associations, proches d’un modèle orienté objet.
• Schéma conceptuel : Modèle de problème issu de l’intégration des vues externes, indépendant des structures techniques du SGBD.
• Schéma logique : Représentation des structures de données supportées par le SGBD, obtenue à partir du schéma conceptuel.

📝 Points essentiels

• Les besoins qui ont motivé l’évolution des SGBD incluent la réponse aux requêtes, la prise en charge du développement d’applications et l’évolutivité face à de nouvelles demandes.
• La démarche traditionnelle de conception descend des besoins utilisateurs vers le SGBD par abstractions successives.
• La conception comporte cinq étapes : capture des besoins, schéma conceptuel, schéma logique, affinement du schéma logique, schéma physique.
• L’étape 1 relève plutôt du génie logiciel, tandis que les étapes 2 à 5 relèvent du domaine des bases de données.
• L’étape 1 produit des vues ou schémas externes à intégrer ensuite, souvent via des entretiens et l’analyse des flux d’information et des processus métier.
• L’étape 2 intègre les schémas externes pour former un schéma conceptuel global, avec des allers-retours possibles avec l’étape précédente pour garantir cohérence et non-redondance.

💡 Astuce mémo

1→5 : Besoins (1) → Concept (2) → Logique (3) → Qualité (4) → Performances (5).

📖 5. Commandes SQL : LDD, LMD et LCD

🔑 Notions clés & Définitions

• LDD : LDD regroupe les commandes SQL qui définissent ou modifient la structure des objets de la base de données.
• LMD : LMD regroupe les commandes SQL qui agissent sur les données stockées dans les tables.
• LCD : LCD regroupe les commandes SQL liées au contrôle des droits et à la sécurité d’accès aux objets.
• Schéma physique : Schéma physique désigne la traduction concrète du schéma conceptuel en structures de stockage et d’accès pour de bonnes performances.
• Schéma conceptuel : Schéma conceptuel décrit le modèle du problème (entités, associations, attributs, identifiants) indépendamment des choix techniques.

📝 Points essentiels

• LDD concerne la création, la modification ou la suppression des structures (tables, contraintes, index) plutôt que le contenu des tables.
• LMD concerne l’insertion, la mise à jour et la suppression des lignes, donc l’évolution du contenu des données.
• LCD vise la gestion des autorisations et du contrôle d’accès, plutôt que la structure ou les données elles-mêmes.
• L’élaboration du schéma physique sert à améliorer les performances en tenant compte des transactions des applications et des accès fréquents.
• Le schéma physique choisit des structures physiques comme le groupement ou le partitionnement de tables et l’usage d’index.
• Le schéma conceptuel est produit à partir des besoins, via un formalisme ER/EA ou via UML, puis reste indépendant du schéma physique.

💡 Astuce mémo

LDD = Définir la Structure, LMD = Manipuler les Données, LCD = Contrôler les Droits.

📖 6. Étapes de conception de bases de données

🔑 Notions clés & Définitions

• Cardinalité d’un type-association : La cardinalité d’un type-association indique le nombre minimal et maximal de fois qu’une entité peut participer à une association de ce type.
• Cardinalité minimale : La cardinalité minimale fixe le nombre minimal de participations possibles d’une entité à une association.
• Cardinalité maximale : La cardinalité maximale fixe le nombre maximal de participations possibles d’une entité à une association.
• Attribut : Un attribut est une caractéristique stockée pour une entité, comme un nom ou un âge.
• Domaine d’un attribut : Le domaine d’un attribut est l’ensemble des valeurs autorisées pour cet attribut.

📝 Points essentiels

• La cardinalité minimale doit être inférieure ou égale à la cardinalité maximale.
• La cardinalité se précise à chaque extrémité de la relation entre entités et association.
• Cardinalité minimale 0 : l’entité peut exister sans participer à l’association.
• Cardinalité minimale 1 : l’entité ne peut exister que si elle participe à au moins une association.
• Cardinalité minimale n : l’entité ne peut exister que si elle participe à plusieurs associations (cas rare à éviter).
• Cardinalité maximale 0 : l’entité ne peut pas participer à l’association (normalement signe d’un problème de conception).

💡 Astuce mémo

Min/Max = contraintes de présence : min = “peut ne pas venir ?”, max = “combien de fois au plus ?”

📖 7. Conception du schéma et exigences de qualité

🔑 Notions clés & Définitions

• Cardinalités ER : Les cardinalités ER décrivent le nombre minimal et maximal de participations d’une entité à une association.
• Généralisation ER : La généralisation ER relie une entité A à une entité B en précisant que A dépend de B via une clé.
• Transformation du schéma logique : La transformation du modèle conceptuel vers un modèle logique produit une structure de données indépendante de la machine.
• Passage au relationnel : Le passage au relationnel traduit entités et associations en tables, avec colonnes pour les attributs et clés pour les identifiants.
• Formes normales : Les formes normales sont des niveaux de normalisation qui imposent des contraintes pour réduire redondances, pertes et incohérences.

📝 Points essentiels

• Une cardinalité peut être volontairement absente dans l’interprétation ER, ce qui change la lecture du lien entre entités.
• Si un étudiant participe à une association au moins une fois, alors chaque instance d’étudiant est associée à au moins une formation.
• Une instance d’étudiant peut être associée à plusieurs formations si la cardinalité côté association l’autorise.
• En généralisation ER, A est reliée à B 0 à n fois, et la connaissance de B permet de définir A via la clé de B.
• En UML (conception objet), on met l’accent sur les structures de données et la programmation, alors qu’en ER (bases de données) on met l’accent sur le système d’information.
• La transformation du modèle conceptuel en modèle logique s’appuie sur des règles formelles et peut être automatisée (ex. Objecteering, Rational Rose).

💡 Astuce mémo

ER = Cardinalités + Dépendance par clé ; UML = Héritage/programmation.

📖 8. Normalisation relationnelle : 1FN à 3FN

🔑 Notions clés & Définitions

• Forme normale de niveau N : Une relation en forme normale de niveau N respecte des contraintes qui garantissent qu’elle est aussi conforme à la forme normale de niveau N−1.
• Première forme normale 1FN : Une relation est en 1FN si tous ses attributs ont des valeurs simples, non décomposables, et si les valeurs répétitives ne sont pas stockées sous forme de listes.
• Deuxième forme normale 2FN : Une relation est en 2FN si elle est en 1FN et si chaque attribut non clé dépend totalement de toute la clé.
• Troisième forme normale 3FN : Une relation est en 3FN si elle est en 2FN et si aucun attribut non clé ne dépend d’un autre attribut non clé.

📝 Points essentiels

• Objectif des 1FN à 3FN : permettre la décomposition de relations sans perte d’informations.
• Toute relation en forme normale de niveau N est automatiquement en forme normale de niveau N−1.
• 1FN impose des valeurs simples et non décomposables, et évite les attributs contenant des listes ou des répétitions.
• 1FN évite les attributs redondants dans le temps en préférant une date stable (ex. date de naissance) à une valeur dérivée (ex. âge).
• 2FN exige que tout attribut non clé soit totalement dépendant de toute la clé, sans dépendance partielle.
• Cas de 2FN : si la clé primaire est composée d’un seul attribut, la condition de dépendance partielle ne s’applique pas de la même façon que pour une clé composite.

💡 Astuce mémo

1FN = valeurs simples ; 2FN = tout dépend de toute la clé ; 3FN = plus de dépendance entre non-clés (pas de “chaînage” entre attributs ordinaires).

📖 9. Forme normale de Boyce-Codd et critères

🔑 Notions clés & Définitions

• Forme normale de Boyce-Codd : La forme normale de Boyce-Codd (FNBC) est une forme normale relationnelle qui élimine les anomalies restantes de la 3FN en imposant une contrainte sur les dépendances fonctionnelles.
• Critère FNBC : Un schéma est en FNBC si et seulement si, pour toute dépendance, aucun attribut de la clé ne dépend d’attributs qui ne sont pas dans la clé primaire.
• Dépendance fonctionnelle : Une dépendance fonctionnelle relie deux ensembles d’attributs, indiquant qu’une valeur d’un ensemble détermine la valeur de l’autre ensemble.
• Schéma physique optimisé : Un schéma physique optimisé est la version concrète de la base (tables, index, partitions, etc.) construite pour de meilleures performances et une meilleure prise en compte des transactions.

📝 Points essentiels

• Une relation est en forme normale de Boyce-Codd si et seulement si aucun attribut appartenant à une clé ne dépend d’attributs n’appartenant pas à la clé primaire.
• La FNBC a été définie par R.F. Boyce et E.F. Codd en partant du constat que la 3FN peut encore laisser des anomalies.
• Un modèle relationnel en FNBC est considéré comme suffisant pour une implantation.
• Les cas de relations en 3FN qui ne sont pas déjà en FNBC sont décrits comme très rares.
• Exemple 1 : pour R(A, B*, C*, D) avec B,C → A et B,C → D, la présence de nombreuses redondances motive une décomposition en R(A, B*, C*) et R’(D*, B#).
• Exemple 2 : pour ReservationCourtTennis(NomCourt*, HeureDebut*, HeureFin, ClasseTauxHoraire), la classe du taux horaire détermine les courts disponibles, d’où une décomposition en ReservationCourtTennis(NomCourt*, Heure,

💡 Astuce mémo

FNBC = « clé → clé » : aucun attribut de la clé ne doit être “piloté” par des attributs hors clé.

📖 10. Élaboration du schéma physique et SQL2/SQL3

🔑 Notions clés & Définitions

• Schéma physique SQL2 : Schéma physique : description concrète des tables et contraintes dans SQL2, orientée vers la structure stockée et l’intégrité des données.
• Contrainte PK : Contrainte PK : contrainte d’unicité qui impose que la clé primaire identifie chaque ligne de manière unique.
• Contrainte NN : Contrainte NN : contrainte de non-nullité qui interdit qu’un attribut prenne la valeur NULL.
• Type objet SQL3 : Type objet SQL3 : définition d’un type de données structuré (attributs) utilisable pour modéliser des objets en base.

📝 Points essentiels

• SQL2 : création de tables avec des types (ex. NUMBER, VARCHAR2, DATE) et ajout de contraintes via CREATE TABLE puis CONSTRAINT.
• COURS : clé primaire sur NUM_COURS et contrainte de non-nullité sur NOMC (check de type NOMC IS NOT NULL).
• PROFESSEURS : clé primaire sur NUM_PROF et contrainte de non-nullité sur NOMP (check de type NOMP IS NOT NULL).
• CHARGE : clé primaire composée (NUM_COURS, NUM_PROF) et clés étrangères vers COURS(NUM_COURS) et PROFESSEURS(NUM_PROF) via ALTER TABLE ADD CONSTRAINT FK.
• SQL3 : création de types (CREATE TYPE ... AS OBJECT) puis création de tables de types (CREATE TABLE ... OF type) et tables imbriquées (ex. cours stockés dans un attribut de type).

💡 Astuce mémo

PK = Identifiant unique ; NN = Pas de NULL ; SQL2 = tables + contraintes ; SQL3 = types + tables OF + attributs imbriqués.

📅 Repères chronologiques

Date	Événement
1960 − 1970 − 1980	Période d’évolution des SGBD (générations 1)
1980 − 1990 − 2000	Période d’évolution des SGBD (générations 3)
2004/5 − 2010	Période d’évolution des SGBD (SGBD 4/5)

📊 Tableaux de synthèse

OLTP vs OLAP et batch

Mode	Objectif	Temps de réponse
OLTP	Opérations transactionnelles	quelques secondes
OLAP	Requêtes analytiques	inférieur à 1 heure
Batch	Traitement long	moins d’une heure

LDD vs LMD vs LCD

Type de langage	But	Exemples
LDD	Définir/modifier la structure	CREATE, ALTER, DROP
LMD	Agir sur les données	SELECT, INSERT, UPDATE, DELETE
LCD	Contrôler l’accès et les droits	GRANT, DENY, REVOKE, READ, UPDATE, DELETE

⚠️ Pièges & confusions fréquents

1. Confondre LDD et LMD : LDD crée/modifie/supprime la structure (CREATE/ALTER/DROP) alors que LMD insère/met à jour/supprime des lignes (INSERT/UPDATE/DELETE).
2. Croire que la cardinalité minimale 0 signifie “maximale 0” : min=0 autorise l’absence de participation, tandis que max=0 interdit toute participation.
3. Penser que 1FN autorise des attributs calculés : le cours recommande d’éviter absolument les attributs calculés et d’utiliser des données primaires.
4. Interpréter une généralisation ER comme une simple relation : en ER, la connaissance de B permet de définir A via la clé de B (dépendance par clé).
5. Mélanger schéma conceptuel et schéma physique : le conceptuel reste indépendant des choix techniques, alors que le physique choisit structures d’accès (index, groupement/partitionnement).
6. Croire que la 3FN suffit toujours : le cours précise que la FNBC élimine les anomalies restantes de la 3FN via une contrainte sur les dépendances fonctionnelles.
7. Confondre “cardinalité absente” et “cardinalité non précisée” : le cours indique qu’une cardinalité volontairement absente change l’interprétation du lien (lecture ER).

✅ Checklist Examen

1. Savoir définir conception de bases de données, PL/SQL, UML, SQL2/3 et l’intégrité des données, et relier ces notions aux objectifs du cours.
2. Connaître le découpage du cours et des TP (4×3h concepts, 6×3h mise en œuvre) et citer les thèmes TP : PL/SQL, UML→SQL2/3, intégrité.
3. Distinguer Big Data, entrepôt de données, OLTP, OLAP et batch, avec les ordres de grandeur de temps donnés (secondes, <1h, <10s, etc.).
4. Savoir classer les commandes SQL en LDD, LMD et LCD, et donner les exemples attendus (CREATE/ALTER/DROP ; SELECT/INSERT/UPDATE/DELETE ; GRANT/DENY/REVOKE/READ).
5. Écrire et interpréter la forme générale de SELECT, INSERT INTO, UPDATE ... SET ... [WHERE], et DELETE FROM ... [WHERE] telle que présentée.
6. Maîtriser les étapes de conception traditionnelles : capture des besoins, schéma conceptuel, schéma logique, affinement, schéma physique, et le rôle de l’étape 1 vs 2-5.
7. Construire un schéma conceptuel : identifier entités/classes, associations, attributs, identifiants, et rappeler la règle d’unicité de l’identifiant.
8. Déterminer les cardinalités min/max à chaque extrémité et savoir interpréter min=0, min=1, min=n (cas rare), max=0, max=1, max=n.
9. Transformer conceptuel→logique→relationnel : passer des entités/associations aux tables (colonnes pour attributs, clés pour identifiants) et appliquer la règle de représentation des associations par une table.
10. Appliquer les règles de passage au relationnel pour les associations (table nommée par l’association, clés des entités participantes, attributs d’association) et l’amélioration par regroupement 1..n avec la classe cible.
11. Vérifier la qualité relationnelle : expliquer pourquoi normaliser, puis tester 1FN, 2FN, 3FN (dépendances partielles/totales et dépendances entre attributs non-clés).
12. Savoir caractériser la FNBC : “aucun attribut de la clé ne dépend d’attributs hors clé primaire”, et reconnaître l’idée d’élimination des anomalies restantes de la 3FN.