Tables as Entities

1. Entity-Relationship Diagrams (ERDs)

Earlier in the course you learned about entity-relationship diagrams (ERDs) and the different notational styles available for creating them. You also learned how database business rules are used to define the entities and relationships needed for a database project. Next, you’ll see how those topics fit together to create ERDs.

Recall that Chen notation, crow’s foot notation, and UML notation are different notation systems for creating ERDs. In this lesson and the upcoming ones, we will use Chen notation to create an ERD for a movie ratings database.

try it

Here’s the scenario. Imagine that an organization would like to create a movie ratings database in which users can rate movies on a numeric scale and provide text comments. Each review should include the date it was written, the user who wrote it, a numeric rating, and optionally, a text comment. The information about each movie should include its release date, title, description, genre(s), and actors. Users will sign into the system using a username/password combination, and the database will also store their first name, last name, and date of birth.

This example database we will design is fairly simple and straightforward, but that may not always be the case on the job. More complex business rules may involve multiple entities and attributes, making them more difficult to fully express using an ERD. You may need to document them in a separate data dictionary or as annotations in the ERD.

term to know

Chen Notation

A visual representation technique for entity-relationship modeling that uses rectangles to represent entities, diamonds for relationships, and lines to represent cardinality and participation constraints.

2. Defining Entities

The first step in creating our example ERD is to examine the above scenario and make a list of the entities that the database will need. It is important that you stick with the details in the scenario rather than adding in extra details that the organization may not need. For example, a movie has many others who contribute to its creation, such as producers and directors. However, the organization is not asking for those details to be included. Let’s take a look at what some of the entities would be by identifying the business rules from the scenario:

• A movie can belong to multiple genres, and a genre can have multiple movies.
• A user can submit multiple ratings, and a rating can only belong to a single user.
• A rating is only for a single movie, and a movie can be rated multiple times.
• A movie can have many actors, and an actor can act in many movies.

hint

Most database designers choose singular nouns for their table names, like Movie or User (rather than Movies and Users). This is just a convention in database design, not a hard-and-fast rule.

In the diagram below, all we have are the table names. We have no relationships between them yet.

3. Diagramming Entities and Their Relationships

Chen notation specifies certain formatting conventions for an ERD. Here are the conventions that apply to entities and relationships.

3a. Entities

Entities (which are tables in the database) represent real-world objects and concepts, such as people, places, ideas, and things. In Chen notation, entities are represented by rectangles, with the entity name inside.

Recall from a previous lesson that entities can be either strong or weak. A strong entity does not rely on any other entity for its existence. For example, in our movie ratings database, Movie is a strong entity because the movies exist even when there are no users or ratings. A strong entity’s rectangle in Chen notation has a solid single-line border.

In contrast, a weak entity depends on another entity, sometimes called the identifying entity, that is considered its owner. A weak entity cannot exist without the entity on which it depends. In Chen notation, weak entities have a double-line border. In our example database, Rating is a weak entity because it cannot exist without its connection to the User and Movie entities.

Recall that an associative entity is a certain type of weak entity that represents many-to-many relationships expressed by another entity. In Chen notation, an associative entity is drawn as a diamond inside a rectangle. Our example database might need an associative entity to manage the many-to-many relationships between actors and movies, and perhaps for some other relationships too.

terms to know

Strong Entity

An entity that can exist independently of the other entities with which it has relationships.

Weak Entity

An entity that cannot exist independently of one or more other entities with which it has relationships.

Associative Entity

Associative entities represent many-to-many relationships expressed by another entity (or table in a database).

3b. Relationships

Relationships define the interconnection between two entities. Diamonds are used to represent them. Through lines called participations, the entities are linked to the relationship. In the diagram shown below, the relationship between Movie and Rating is expressed as a diamond with the text “What Rating” in it. The relationship between User and Rating is expressed as a diamond with the text “What Movie” in it.

As with entities, strong and weak relationships are represented differently in Chen notation. Strong relationships are shown with a single-line border diamond, and weak relationships are shown with a double-line border diamond, as introduced earlier.

For example, the relationship between Movie and Actor is a strong relationship because actors and movies can exist independently of one another. In contrast, the relationship between Rating and User or Movie is a weak (identifying) relationship, because the Rating entity depends on the existence of both the User and the Movie.

The examples below focus on strong relationships for simplicity, but weak relationships are equally important and will continue to appear in later examples.

The next step after defining the entities, which you did in the previous section, is to define the direct relationships between them. Direct relationships are relationships that do not require an associative table. In other words, direct relationships are one-to-one or one-to-many relationships.

The following figure provides two examples of one-to-many relationships expressed in Chen notation. Using this same format, see if you can define the other direct relationships in our example database.

You have now done the first step in our ERD, by identifying the entities for your database.

terms to know

Strong Relationship

A relationship between two entities that are existentially independent of one another.

Weak Relationship

A relationship involving two entities whose existence is dependent on that of the related entity.

Direct Relationship

A relationship that does not require an associative table—in other words, a one-to-one or one-to-many relationship.