Entity Relationship Diagrams — An Explanation
An Entity-Relationship Diagram (ERD) is a visual layout of the structure of a database. It shows the relationships between different entities within a database. These diagrams are blueprints for designing databases that help you conceptualize before you actually build your database. Once you built the diagram, you need to translate it into a database schema in your database management system (MySQL, PostgreSQL, SQL Server, Oracle). Or you can do the reverse. If you already have a database, it is not easy to see what is going on, so you might want a diagram of it.
Before you can jump in and create an ERD, you need to better understand the individual components:
Entities are an object, such as a person, place, or thing, to be tracked in the database. It could be a customer, an order, or a product. Each entity in your diagram represents a table in your database.
Each of these entities are going to take attributes (which are various properties or traits that provide more information about the entities).
If an attribute can have multiple values for a particular entity, it’s called a multivalued attribute. For example, someone can have multiple phone numbers.
If an attribute can be divided up into more meaningful attributes, it’s called a composite attribute. For example, someone’s full name can be broken down into first name and last name, which is useful in some cases.
If an attribute has a value that can be derived from other attributes in the database, it’s called a derived attribute. For example, you can get age from date of birth.
The entities in the database are depicted as the rows, and the attributes are depicted as the columns in a table.
The relationships describe how these entities interact with each other (if they do at all). Lines are drawn between the entities to show that there is some sort of interaction or connection in some way. Solid lines mean there is a direct association, and dashed lines indicate a derived relationship.
This notation is called the cardinality, which defines the relationship between entities in a numerical context. Relationships are shown on both sides of the line. Crows-feet, lines, and circles are used to differentiate the relationships. Memorize the six notatations in the diagram below.
A primary key is an attribute that uniquely identifies every record in a certain table. A single attribute can accomplish this, so you only need one primary key per entity. It is denoted as “PK” in the table.
There are three rules that must be followed for primary keys:
1) It has to be unique, so that it only identifies one record in your table.
2) It can never be changing, it would be difficult to keep an accurate record if you are using an attribute that might not stay consistent.
3) It can never be null, there can never be an occasion where the value could be left blank.
IDs or order numbers both work well as primary keys because they pass all of the rules, they are designed to increment by each addition to the table.
A foreign key is the same as a primary key, just located in a different place. It is when you have a primary key in one entity that references the same attribute in another entity. It is denoted as “FK”.
There are three five rules that must be followed for foreign keys:
1) They don’t have to be unique.
2) There can be multiple foreign keys in one entity.
3) The foreign key must have the same data type as the primary key it refers to.
4) Every value in the foreign key attribute must match a value in the primary key column of the referenced table (or be NULL).
5) If a value in the entity with the primary key is deleted, then the corresponding value in the table with the matching foreign key has to be deleted (or set to NULL).
The table with the foreign key is called the “child” or “dependent” table, and the table being referred to by the foreign key is known as the “parent” or “referenced” table.
Composite primary keys are a type of primary keys that are used when two or more attributes are necessary to uniquely identify every record in a table. For example when two different products are getting sent in two shipments to the customer. In this scenario, you can combine two attributes create a new unique value and create a value that won’t be repeated. This doesn’t mean that there are multiple primary keys, it means that those attributes are needed to create a composite primary key. A composite primary key is denoted with multiple “PK” marks in the table.
There are two rules that should be followed for composite primary keys:
1) Use the fewest number of attributes as possible.
2) Use attributes that are not likely to change, because that could make things messy.
*** It is completely valid to create a completely new attribute to use as the primary key instead of combining two attritbutes.
You can add data types for each attribute, this is helpful if you plan to use some programming in your database. You can specify the attributes with data types like integer, decimal, time, boolean, or various character (varchar). You can set a limit to the characters. These are just a few examples of the data types that can be used.
Sometimes you need to have an intermediary table between two tables. This is particularly useful when you have many-to-many relationships between entities. When two entities are connected together like this, a lot of details can be missed about the interaction between these entitites. A bridge table can be useful in this case. It creates a one-to-many relationship in between two entitites and gets you the information you are lacking.
Entity Relationship Diagrams are worth learning how to create and use to help turn database ideas into reality, making sure that the data is accurate, consistent, and easy to access.
