Every good database starts with clear requirements. Requirements are simply short statements that explain what the database should track and how. For example, clients from The Movie Streaming Company (TMSC) that need a database might say during the conversations with database designers “We need to keep track of our customers and the regions they are located in.” The requirements would then become: (1) customer details, (2) region details, and (3) the relationship between customers and regions they are located in, which becomes the starting point for the database design.
A method called Entity–Relationship (ER) Modeling is used to express the database requirements as a visual schema called an ER Diagram (ERD). Think of an ER Diagram as an initial blueprint that shows the important things a database will be tracking (such as customers and regions), the details about them (e.g. customer’s name and ID), and the way they connect (e.g. each employee is located in a region).
ER Diagrams make the ideas about the planned database concrete. They allow people who aren’t technical to see how the information will be organized, while giving developers a clear guide to follow. Using ERDPlus, you can draw these diagrams (and later convert them into real database tables).
In short, requirements tell us what to build, and ER Modeling provides a method to visually express the requirements in a quick and easy-to-understand manner. Requirements and ER Diagrams are the exact same thing, expressed in a different manner. One is a collection of sentences, and the other is a visual expression of those same sentences. In fact, experienced database designers will, during the requirements related conversations, often first create an ER Diagram based on those conversations and then create a verbal description of the requirements (i.e. sentences).
Entities
The building blocks of an ER Diagram are entities, shown as rectangles in ERDPlus. An entity represents what the database that is being designed will keep track of. If you create two entities: A and B, you are stating that the future database will be keeping track of A’s and B’s.
In our TMSC movie streaming company example, we can start by creating three entities: CUSTOMER, REGION, and MOVIE. With this, we are stating that our future database will keep track of customers, regions, and movies.
In ER Diagrams, the names of entities are typically singular (e.g. CUSTOMER, not CUSTOMERS).
Attributes
Each entity has attributes, which are the details you want to know about the entity. For an entity CUSTOMER, that might be their customer id, name, and gender.
Each entity will have its instances that will be represented as the data in the future database. For example, the future database may have the following instances of the entity CUSTOMER, where each entity instance has value for the entity attributes CustID, CustName, and CustGender:
- 111, Joe, Male
- 222, Sue, Female
- 333, Meg, Female
- …
Note that ER Diagrams depict entity names and attribute names, but they do not depict entity instances (data). Entity instances will appear later in the database created based on the created ER Diagram.
At least one attribute must be a unique attribute, for which every entity instance has a different value. In ER Diagrams, the unique attributes are underlined to make them stand out. In entity CUSTOMER the attribute CustID is unique, so every customer can be distinguished apart by the value of their CustID.
To sum up, the requirement for entity CUSTOMER shown above is:
For entity CUSTOMER, we will keep track of a unique customer id and a customer name and gender.
Here are few more examples of requirements resulting in entities.
For entity REGION, we will keep track of a unique region id and a region name.
For an entity MOVIE, we will keep track of a unique movie id, and a movie name, movie genre and movie length.
Entities will eventually become tables in the future resulting database, and attributes will become columns of those tables. By naming entities and attributes carefully, and choosing good identifiers (unique attributes), you make sure that the database can uniquely recognize each record and keep information organized.
Advertisement
Relationships and Cardinality Constraints
Relationships describe how entities connect to one another. They are drawn as diamonds between rectangles, with symbols next to each rectangle. These symbols, called cardinality constraints, show the minimum and maximum number of times an instance of one entity can be linked to the instances of another.
There are two possible minimums:
- “0” (also known as optional participation) depicted as O
- “1” (also known as mandatory participation) depicted as |
There are two possible maximums:
- “1” depicted as |
- “Many” depicted as crow’s foot
Therefore, the four possible cardinality constraints are:
- Mandatory Many
- Optional Many
- Mandatory One
- Optional One
Cardinality constraints appear on each side of the relationship. For example, consider the requirements for the following relationship:
LocatedIn:
Each customer is located in exactly one (mandatory one) region.
Each region has between one and many (mandatory many) customers.
Note that in this notation (also known as a “look across” notation) the cardinality constraint refers to the entity that is across from it.
Cardinality is always read in both directions. Looking one way, we say, “Each customer is located in exactly one region.” Looking back, we say, “Each region has between one and many customers”. This two-way reading ensures that the model is fully understood and helps avoid misinterpretations.
Let’s now look at several more examples of relationships and their cardinalities.
Buys:
Each customer buys one or more movies.
Each movie is bought by between zero and many customers.
DesignatesAsAmbassador:
Each region designates as an ambassador one customer. Each customer is designated as an ambassador for one region or for no regions.
Produces:
Each movie is produced by no studio or one studio. Each studio produces between one and many movies.
Types of Relationships
Relationships can be classified into three types based on maximum cardinality alone:
- one-to-one (1:1)
- one-to-many (1:M)
- many-to-many (M:N)
When determining whether the relationship is one-to-one, one-to-many, or many-to-many, we only look at the maximum cardinality and we do not take the minimum cardinality into consideration for that determination.
Relationship DesignatesAsAmbassador is a one-to-one relationship, because the maximum on both sides is 1.
Relationship LocatedIn is a one-to-many relationship, because the maximum on one of the sides is 1 and on the other side is many.
Relationship Buys is a many-to-many relationship, because the maximum on both sides is many.
One-to-one relationships are relatively rare. One-to-many and many-to-many relationships are much more common.
In some cases, many-to-many relationships may have their own attributes. For example, recall that the requirements for the Buys relationship state the following:
Each customer buys one or more movies.
Each movie is bought by between zero and many customers.
Now, we will add more to this requirement.
Each time a customer buys a movie we record the date when the customer bought the movie, and the dollar amount that the customer paid for that movie.
This addition to the requirement would be depicted as two attributes of the Buys relationship.
Note that these two attributes, Date and DollarAmount, could only be attached to the relationship Buys and not to either of the entities. For example, if we attached the attribute Date to the entity CUSTOMER, we would not know which movie purchase by the customer the date refers to (as the same customer can buy different movies on different dates). Or, if we attached the attribute Date to the entity MOVIE, we would not know which customer purchase of the movie does the date refers to (as the same movie can be bought by different customers on different dates).
As we illustrated, relationship attributes may occasionally be needed for many-to-many relationships. They are never needed for one-to-one and one-to-many relationships.
Advertisement
ER Diagram Example
Now that we have covered the basics, let us consider an ER Diagram example based on the following requirements.
Movie Streaming Company will keep track of the following:
- For each CUSTOMER a unique customer id and a customer name and gender.
- For each REGION a unique region id and a region name.
- For each MOVIE a unique movie id, and a movie name, genre and length.
- For each STUDIO a unique studio id and a studio name.
- Each customer is located in exactly one region.
Each region has between one and many customers. - Each region designates as an ambassador one customer.
Each customer is designated as an ambassador for one region or for no regions. - Each customer buys one or more movies.
Each movie is bought by between zero and many customers.
Each time a customer buys a movie we record the date when the customer bought the movie and the dollar amount that the customer paid for that movie. - Each movie is produced by no studio or one studio.
Each studio produces between one and many movies.
Note again that the requirement and the ER Diagram are the exact same thing, written using different methods. The requirements are written in English (or any other language) sentences, and the ER Diagram is “written” using ER constructs. However, for people who know English (or any other language) and understand the ER notation, both read exactly the same and are a complete reflection of each other. Therefore, when collecting requirements, you can first create the ERD and then write out the requirements, or you can do it the other way around, whichever you prefer.
This was a brief overview of ER Modeling. To learn more about additional ER Modeling concepts (such as multivalued attributes, derived attributes, unary relationships, weak entities, identifying relationships, etc.) read Chapter 2 of the following database textbook:
or find other sources of information about these topics.