So, in the last post I went over a little about how things are represented in an ER diagram, as well as the reasons to why using an ER diagram is helpful to design a good database. In this post, we'll elaborate on specific rules and guidelines that should be followed ad considered when creating an ER Diagram along with more information on relationships. Let's get started!
The basic setup of the ER diagram is formed in the first post, but there are Integrity constraints that need to be established.
Entity Integrity:
The entity integrity constraints states that a primary key of an entity cannot be NULL. NULL means that the value is unknown. The reason behind this is that specific rows of an entity is identified by the primary key(previous post), and if there is no primary key, then there is no way to identify it.
Referential Integrity:
The referential integrity constraint states a specific constraint between two tables. The primary table's records must exist in order for another table to reference that record.
For example, John sells a used car to Adam. The license of this car is 00A154.
In the Car table, there would first exist an record of this car:
(License, Year, Model, Manufacturer) --> (00A154, 2014, Civic, Honda)
In the Sells table, there would then exist an entry:
(Salesman, Client, Car) --> (John, Adam, 00A154)
1. If John were to change this record in table Car to (00B154, 2014, Civic, Honda), the Sells table would not be able to locate a record of 00A154.
2. If John were to delete the record in table Car, the Sells table would also not be able to locate a record of 00A154.
3. If John accidentally inserted the Sells record incorrectly say (John, Adam, 00B154), then when the Sells table try to locate Car 00B154 and it's information, the records would not be able to find it.
4. However, John can insert the Sells record as (John, Adam, NULL).
Foreign Integrity:
Foreign integrity constraints help solve the first two problems we have with the Referential Integrity Constraints.
Cascade Update:
Any time the primary table's record is changed, anything that references it must also be changed. That way there would not have mismatching records when we try to locate the reference.
Cascade Delete:
Any time a primary table's record is deleted, anything that references it must also be deleted. That way there would not have unfound records.
Relationship Constraints: There are key constraints, One-to-One, One-to-Many, and Many-to-Many. Then, there are participation constraints.
One-to-One: One to one means that an entity can at most be related one entity. For example, a man can be married to one woman, and vice versa. Sometimes there would also be specific ranges on the line specifying the relationship as [1:1]. This would be denoted by an arrow:
Man --> marries <-- Woman
One-to-Many: Let's say in a case that a man can marry many women, but a woman can only marry one man. Sometimes there would also be specific ranges on the line specifying the relationship as [1:N]. The relationship would be a One-to-Many, expressed as the following:
Man -- marries <-- Woman
Many-to-Many: Many-to-Many are represented like the diagram above, just a thin line.
Mechanic -- Repairs -- Car
Participation Constraints: If we want to create a database where everyone must be married, then we would have a participation constraint. The arrows are still present to represent the One-to-One relation, and the line is thickened to show that records MUST be married couples.
Man --> marries <-- Woman
