E-R diagrams and normalisation · Diagrammes E-R et normalisation
| English | Français |
|---|---|
| entity-relationship diagram/ˈentɪti rɪˈleɪʃənʃɪp ˈdaɪəɡræm/ | diagramme entité-relation |
| normalisation/ˌnɔːməlaɪˈzeɪʃn/ | normalisation |
| entity/ˈentɪti/ | entité |
| cardinality/ˌkɑːdɪˈnælɪti/ | cardinalité |
| one-to-many/wʌn tə ˈmeni/ | un-à-plusieurs |
| many-to-many/ˈmeni tə ˈmeni/ | plusieurs-à-plusieurs |
| link table/lɪŋk ˈteɪbl/ | tableau de liaison |
| normal forms/ˈnɔːml fɔːmz/ | formes normales |
| atomic/əˈtɒmɪk/ | atomique |
| transitive dependency/ˈtrænsɪtɪv dɪˈpendənsi/ | dépendance transitive |
Forty rows to change one phone number
- A school keeps its enrolments in one spreadsheet. Every row is one student on one course, and every row also carries the student's form tutor and the tutor's phone number.
- The tutor changes her number. Forty rows have to be edited. Thirty-nine are. For the rest of the year, one course list rings a stranger.
- Nothing was mistyped. The fault was in the design: a fact that belongs to the tutor was stored once per enrolment, so it could be true in one place and false in another.
- This lesson is the two design tools that prevent that: the entity-relationship diagram 实体关系图, which draws the structure, and normalisation 规范化, which removes the repetition.
Quarante lignes à modifier pour changer un numéro de téléphone
- Une école garde ses inscriptions dans un classeur. Chaque ligne représente un étudiant sur un cours, et chaque ligne porte également le tuteur de l'étudiant et le numéro de téléphone du tuteur.
- Le tuteur change son numéro. Quarante lignes doivent être modifiées. Trente-neuf le sont. Pour le reste de l'année, une liste de cours compose un étranger.
- Rien n'a été mal tapé. L'erreur était dans la conception : un fait qui appartient au tuteur était stocké une fois par inscription, il pouvait donc être vrai à un endroit et faux à un autre.
- Cette leçon présente les deux outils de conception qui empêchent cela : le diagramme entité-relations 实体关系图, qui dessine la structure, et la normalisation 规范化, qui élimine les répétitions.
Entity-relationship diagrams
- An entity-relationship diagram (E-R diagram) documents a database design: each entity 实体 is a rectangle, each relationship is a line between two rectangles, and the cardinality 基数 is marked at each end.
- In crow's-foot notation a single bar means "one" and a three-pronged foot means "many". Read each line in both directions: each customer places many orders; each order is placed by one customer.
- The diagram is drawn before any table is created, and the relationships on it become the foreign keys.
One rectangle per entity, one line per relationship
A bar for one, a foot for many
Diagrammes entité-relations
- Un diagramme entité-relations (diagramme E-R) documente la conception d'une base de données : chaque entité 实体 est un rectangle, chaque relation est une ligne entre deux rectangles, et la cardinalité 基数 est marquée à chaque extrémité.
- En notation patte de corbeau, un trait simple signifie "un" et une patte à trois branches signifie "plusieurs". Lisez chaque ligne dans les deux sens : chaque client passe plusieurs commandes ; chaque commande est passée par un seul client.
- Le diagramme est dessiné avant la création de toute table, et les relations qu'il contient deviennent les clés étrangères.
Un rectangle par entité, une ligne par relation
Un trait pour un, une patte pour plusieurs
In an E-R diagram, the number of one entity that can relate to one of the other, marked at each end of the line, is the ____. · Dans un diagramme E-R, le nombre d'une entité pouvant se relier à une autre, marqué à chaque extrémité de la ligne, est la ____.
Cardinality is one or many at each end; crow's-foot notation draws a bar for one and a foot for many. · La cardinalité est un ou plusieurs à chaque extrémité ; la notation patte-de-poule trace un trait pour un et une patte pour plusieurs.
The three kinds of relationship
- One-to-one (1:1): each member has one library card and each card belongs to one member. Rare; the two entities are often merged into one table.
- One-to-many 一对多 (1:M): one customer places many orders; each order belongs to one customer. Implemented by putting the "one" side's primary key into the "many" side's table as a foreign key.
- Many-to-many 多对多 (M:N): a student takes many courses and a course has many students. It cannot be implemented directly; it needs a link table.
Les trois types de relations
- Un-à-un (1:1) : chaque membre possède une carte de bibliothèque et chaque carte appartient à un seul membre. Rare ; les deux entités sont souvent fusionnées dans une même table.
- Un-à-plusieurs 一对多 (1:M) : un client passe plusieurs commandes ; chaque commande appartient à un seul client. Implémenté en plaçant la clé primaire du côté "un" dans la table du côté "plusieurs" sous forme de clé étrangère.
- Plusieurs-à-plusieurs 多对多 (M:N) : un étudiant suit plusieurs cours et un cours a plusieurs étudiants. Il ne peut pas être implémenté directement ; il nécessite une table de liaison.
Each customer can place many orders, but each order belongs to one customer. This relationship is: · Chaque client peut passer de nombreuses commandes, mais chaque commande appartient à un seul client. Cette relation est :
One customer → many orders, each order → one customer: a one-to-many relationship. · Un client → de nombreuses commandes, chaque commande → un client : une relation un-à-plusieurs.
Match each relationship to its cardinality. · Faites correspondre chaque relation à sa cardinalité.
1:1 each side has one; 1:M one side has many; M:N both sides have many (needs a link table). · 1:1 chaque côté a un ; 1:M un côté a plusieurs ; M:N les deux côtés ont plusieurs (nécessite une table de liaison).
Worked example: draw the E-R diagram
- A school has teachers, classes and students. Each teacher teaches many classes; each class is taught by one teacher. Each class has many students; each student is in one class. Students may join many clubs and each club has many students.
- Four rectangles:
TEACHER,CLASS,STUDENT,CLUB.TEACHER—CLASSis one-to-many, the foot atCLASS.CLASS—STUDENTis one-to-many, the foot atSTUDENT.STUDENT—CLUBis many-to-many, a foot at both ends. - The marks: every entity present, every relationship drawn, and the correct cardinality symbol at each end. A line with no symbols is half an answer.
Exemple résolu : dessiner le diagramme E-R
- Une école a des enseignants, des classes et des étudiants. Chaque enseignant enseigne plusieurs classes ; chaque classe est enseignée par un seul enseignant. Chaque classe a plusieurs étudiants ; chaque étudiant est dans une seule classe. Les étudiants peuvent rejoindre plusieurs clubs et chaque club a plusieurs étudiants.
- Quatre rectangles :
TEACHER,CLASS,STUDENT,CLUB.TEACHER—CLASSest un-à-plusieurs, le pied àCLASS.CLASS—STUDENTest un-à-plusieurs, le pied àSTUDENT.STUDENT—CLUBest plusieurs-à-plusieurs, un pied aux deux extrémités. - Les marques : chaque entité présente, chaque relation dessinée, et le bon symbole de cardinalité à chaque extrémité. Une ligne sans symboles ne vaut que la moitié de la réponse.
Link tables
- A many-to-many relationship is broken into two one-to-many relationships through a link table 连接表 that holds the two foreign keys.
ENROLMENT(StudentID, CourseID, EnrolmentDate): one student has many enrolments, one course has many enrolments, and each row is one student on one course. Its primary key is the composite of the two foreign keys.- Data about the pairing itself, the date, a grade, goes in the link table; data about the student or the course stays in its own table.
One many-to-many becomes two one-to-many
Tables de liaison
- Une relation plusieurs-à-plusieurs est décomposée en deux relations un-à-plusieurs via une table de liaison 连接表 qui contient les deux clés étrangères.
ENROLMENT(StudentID, CourseID, EnrolmentDate): un étudiant a plusieurs inscriptions, un cours a plusieurs inscriptions, et chaque ligne représente un étudiant sur un cours. Sa clé primaire est le composé des deux clés étrangères.- Les données concernant le couple lui-même, la date, une note, vont dans la table de liaison ; les données concernant l'étudiant ou le cours restent dans leur propre table.
Un plusieurs-à-plusieurs devient deux un-à-plusieurs
How is a many-to-many relationship implemented in a relational database? · Comment une relation plusieurs-à-plusieurs est-elle implémentée dans une base de données relationnelle ?
A link (junction) table holds a foreign key to each side, turning M:N into two 1:M relationships. · Une table de liaison (junction) contient une clé étrangère pour chaque côté, transformant M:N en deux relations 1:M.
ENROLMENT(StudentID, CourseID, EnrolmentDate) is a link table. Which statements are true? Select all · tout that apply. · ENROLMENT(StudentID, CourseID, EnrolmentDate) est une table de liaison. Quelles affirmations sont vraies ? Sélectionnez toutes les réponses applicables.
The link table holds the pairing and facts about the pairing. The student's own data stays in STUDENT, or it would repeat on every enrolment. · La table de liaison contient le couple et les faits concernant ce couple. Les propres données de l'étudiant restent dans STUDENT, sinon elles seraient répétées à chaque inscription.
Normalisation
- Normalisation organises the tables so that each fact is stored exactly once, cutting redundancy and inconsistency. It passes through the normal forms 范式 in order: first, second, third.
- The procedure: find the entities and their attributes; choose a primary key for each; remove repeating groups and non-atomic values (1NF); remove attributes that depend on only part of a composite key (2NF); remove attributes that depend on another non-key attribute (3NF); add foreign keys for the relationships.
- The cost is more tables and more joins. The exam asks for 3NF.
Normalisation
- La normalisation organise les tables pour que chaque fait soit stocké exactement une fois, réduisant la redondance et l'incohérence. Elle passe par les formes normales 范式 dans l'ordre : première, deuxième, troisième.
- La procédure : trouver les entités et leurs attributs ; choisir une clé primaire pour chacune ; supprimer les groupes répétés et les valeurs non atomiques (1NF) ; supprimer les attributs qui dépendent seulement d'une partie d'une clé composée (2NF) ; supprimer les attributs qui dépendent d'un autre attribut non-clé (3NF) ; ajouter des clés étrangères pour les relations.
- Le coût est plus de tables et plus de jointures. L'examen demande la 3NF.
Database service lab · Lab de service de base de données
Watch how a DBMS turns a query into safe shared data access. · Regardez comment un SGBD transforme une requête en accès partagé sécurisé aux données.
Put the normal forms in the order you apply them. · Placez les formes normales dans l'ordre dans lequel vous les appliquez.
You reach 3NF by passing through 1NF then 2NF — each builds on the previous. · Vous atteignez la 3NF en passant par la 1NF puis la 2NF — chacune s'appuie sur la précédente.
The main aim of normalisation is to: · Le but principal de la normalisation est de :
Normalising to 3NF stores each fact once, removing update/insert/delete anomalies (at the cost of more joins). · Normaliser jusqu'à la 3NF stocke chaque fait une seule fois, éliminant les anomalies de mise à jour/insertion/suppression (au prix de plus de jointures).
First normal form
- A table is in 1NF when every field holds a single, atomic 原子 value, there are no repeating groups, and there is a primary key.
STUDENT(StudentID, Name, Phone)withPhoneholding0123, 0456is not atomic.STUDENT(StudentID, Name, Course1, Course2, Course3)has a repeating group.- Fix both by moving the repeated data to its own table with a row per value:
STUDENT_PHONE(StudentID, Phone),ENROLMENT(StudentID, CourseID).
Première forme normale
- Une table est en 1NF lorsque chaque champ contient une valeur unique et atomique 原子, qu'il n'y a pas de groupes répétés, et qu'il y a une clé primaire.
STUDENT(StudentID, Name, Phone)avecPhonecontenant0123, 0456n'est pas atomique.STUDENT(StudentID, Name, Course1, Course2, Course3)a un groupe répété.- Corriger les deux en déplaçant les données répétées dans sa propre table avec une ligne par valeur :
STUDENT_PHONE(StudentID, Phone),ENROLMENT(StudentID, CourseID).
A Phone field holds "0123, 0456" for one student. Which normal form does the table fail? · Un champ Phone contient "0123, 0456" pour un étudiant. À quelle forme normale cette table échoue-t-elle ?
Two values in one cell is the 1NF failure. Move the numbers to STUDENT_PHONE(StudentID, Phone), one per row. · Deux valeurs dans une cellule est l'échec de la 1NF. Déplacez les numéros dans STUDENT_PHONE(StudentID, Phone), un par ligne.
Worked example: second normal form
ORDER(OrderID, CustomerID, CustomerName, ProductID, Quantity)has the composite primary key(OrderID, ProductID). Is it in 2NF?- Test each non-key field against the whole key.
Quantitydepends on bothOrderIDandProductID: which order, which product. Fine. CustomerIDandCustomerNamedepend onOrderIDalone, only part of the key: a partial dependency, so the table is not in 2NF. Split it:ORDER(OrderID, CustomerID, CustomerName)andORDER_LINE(OrderID, ProductID, Quantity).
Exemple résolu : deuxième forme normale
ORDER(OrderID, CustomerID, CustomerName, ProductID, Quantity)a la clé primaire composée(OrderID, ProductID). Est-ce en 2NF ?- Tester chaque champ non-clé contre la clé complète.
Quantitydépend à la fois deOrderIDetProductID: quelle commande, quel produit. C'est correct. CustomerIDetCustomerNamedépendent uniquement deOrderID, seulement une partie de la clé : une dépendance partielle, donc la table n'est pas en 2NF. La séparer :ORDER(OrderID, CustomerID, CustomerName)etORDER_LINE(OrderID, ProductID, Quantity).
Worked example: third normal form
- Is
ORDER(OrderID, CustomerID, CustomerName)in 3NF? - A table is in 3NF when it is in 2NF and every non-key field depends only on the primary key, not on another non-key field.
CustomerNamedepends onCustomerID, which is not the key: a transitive dependency 传递依赖, so the table is not in 3NF. - Split again:
ORDER(OrderID, CustomerID)andCUSTOMER(CustomerID, CustomerName), withCustomerIDa foreign key. The name is now stored once, however many orders the customer places.
Each form removes one kind of dependency
Exemple résolu : troisième forme normale
- Est-ce que
ORDER(OrderID, CustomerID, CustomerName)est en 3NF ? - Une table est en 3NF lorsqu'elle est en 2NF et que chaque champ non-clé dépend uniquement de la clé primaire, pas d'un autre champ non-clé.
CustomerNamedépend deCustomerID, qui n'est pas la clé : une dépendance transitive 传递依赖, donc la table n'est pas en 3NF. - Séparer encore :
ORDER(OrderID, CustomerID)etCUSTOMER(CustomerID, CustomerName), avecCustomerIDcomme clé étrangère. Le nom est maintenant stocké une seule fois, peu importe combien de commandes le client passe.
Chaque forme élimine un type de dépendance
Normalising to 3NF stores each fact once and removes update anomalies, at the cost of more tables and joins. · Normaliser jusqu'à la 3NF stocke chaque fait une seule fois et supprime les anomalies de mise à jour, au prix de plus de tables et de jointures.
That trade-off — cleaner data versus more joins — is why 3NF is the usual target. · Ce compromis — données propres contre plus de jointures — explique pourquoi la 3NF est la cible habituelle.
Saying why a table is, or is not, in 3NF
- Not in 3NF: name the dependency. "
TEACHERis not in 3NF because the non-key attributeDepartmentNamedepends on the non-key attributeDepartmentID, not on the primary key." - In 3NF: cover all three conditions. "Every attribute is atomic with no repeating groups; there is no partial dependency on part of the key; every non-key attribute depends only on the primary key, with no transitive dependency."
- Then, if asked, give the normalised tables in the standard notation with the foreign keys marked.
Expliquer pourquoi une table est, ou n'est pas, en 3NF
- Pas en 3NF : nommer la dépendance. "
TEACHERn'est pas en 3NF car l'attribut non-cléDepartmentNamedépend de l'attribut non-cléDepartmentID, pas de la clé primaire." - En 3NF : couvrir les trois conditions. "Chaque attribut est atomique sans groupes répétés ; il n'y a pas de dépendance partielle sur une partie de la clé ; chaque attribut non-clé dépend uniquement de la clé primaire, sans dépendance transitive."
- Ensuite, si demandé, donner les tables normalisées dans la notation standard avec les clés étrangères marquées.
In TEACHER(TeacherID, Name, DepartmentID, DepartmentName), why is the table not in 3NF? · Dans TEACHER(TeacherID, Name, DepartmentID, DepartmentName), pourquoi la table n'est-elle pas en 3NF ?
A transitive dependency. Split out DEPARTMENT(DepartmentID, DepartmentName) and keep DepartmentID in TEACHER as a foreign key. · Une dépendance transitive. Séparez DEPARTMENT(DepartmentID, DepartmentName) et gardez DepartmentID dans TEACHER comme clé étrangère.
Marks that slip away
- 2NF is only a question when the primary key is composite. A single-field key cannot have a partial dependency.
- 3NF requires 2NF. Say both when justifying.
- Atomic means one value per cell. "Two phone numbers in one field" is a 1NF failure, not a 3NF one.
- Naming the normal form is not the answer; naming the dependency is. More tables after normalising is the point, not a fault.
Pièges qui font perdre des points
- La 2NF n'est une question que lorsque la clé primaire est composée. Une clé à un seul champ ne peut pas avoir de dépendance partielle.
- La 3NF exige la 2NF. Citer les deux lors de la justification.
- Atomique signifie une valeur par cellule. "Deux numéros de téléphone dans un champ" est une violation de la 1NF, pas de la 3NF.
- Nommer la forme normale n'est pas la réponse ; nommer la dépendance l'est. Avoir plus de tables après normalisation est le but, pas un défaut.
You've got it
- an E-R diagram shows entities as rectangles and relationships as lines with the cardinality at each end: 1:1, 1:M, M:N
- a many-to-many relationship is stored through a link table of the two foreign keys, its primary key their composite
- 1NF atomic values, no repeating groups, a primary key · 2NF no partial dependency on part of a composite key · 3NF no transitive dependency between non-key attributes
- justify by naming the dependency, then give the split tables with their foreign keys
Vous avez compris
- un diagramme E-R montre les entités comme des rectangles et les relations comme des lignes avec la cardinalité à chaque extrémité : 1:1, 1:M, M:N
- une relation plusieurs-à-plusieurs est stockée via une table de liaison des deux clés étrangères, dont la clé primaire est leur composition
- 1NF valeurs atomiques, pas de groupes répétés, une clé primaire · 2NF pas de dépendance partielle sur une partie d'une clé composite · 3NF pas de dépendance transitive entre attributs non-clés
- justifier en nommant la dépendance, puis donner les tables divisées avec leurs clés étrangères