Session 3

Entity Relationship Models

CS 3140
Database Systems

Opening Exercise

Here are three instances of the Movies relation from last time. For each, tell me if it is a legal instance. If it is not, say which rule it violates.

    title                year   length   genre
    --------------------------------------------
    The Big Chill        1983   105      drama
    Blade Runner         1982   117      scifi
    What About Bob?      1991   99       comedy
    Blade Runner         1982   117      scifi


                            title                year   length   genre
                            --------------------------------------------
                            Black Panther        2018   134      action
                            The Princess Bride   1987   98       fantasy
                            The Replacements     2000   118      comedy
                            Star Trek            1979   132      scifi


    title                year   length   genre
    --------------------------------------------
    Lethal Weapon        1987   109      action
    Return of the Jedi   1983   131      scifi
    48 Hrs.              1982   96       comedy
    Lethal Weapon        1987   44       comedy

Review of Opening Exercise

Instance 1: A relation is a set of tuples.

Instance 2: Looks good.

Instance 3: A key uniquely identifies a tuple.

These movies point out a weakness in our model. IMDB lists Black Panther as an action film, an adventure film, and as a scifi film. 48 Hrs. is included in the categories "action", "comedy", and "crime". Is genre a single attribute?

While creating an ER model of your own for last week's on-line session, many of you thought about what made a design "right" or "wrong". Even criteria such as parsimony and lack of reduncancy can be hard to define clearly in all cases. The question about genres is a matter of fidelity. Does representing genre as a single attribute faithfully represent the domain?

All models leave something out. In the end, we have to know something about the applications we want to support before we can decide. IMDB wants to support a wide variety of users, with both different operational needs and different views of movies. For it, genre needs to be a richer idea. If a movies can have multiple genres in our application, what can we do?

In my Session 1 example, a flat file for albums included "songs it contains" as as attribute. We can do anything we want with flat files, I suppose, but... We have since learned that, in both the ER model and the relational model, the value of each attribute must be atomic. If we want to associate a set of genres with a movie, then we need a separate table that relates a movie to a genre, with repetitions allowed.

This is similar to the "StarsIn" relation from the closing example last time. Because movies can have more than on star, "star" cannot be an attribute of movie.

In our new ER model, genre becomes an entity, and a new table captures the relationship between movies and genres.

This design is different from most we've seen before: Genre is an entity set with only one attribute!

Conceptual modeling is a skill all its own. We can only scratch the surface in a first course on databases. Our goal is to understand the entire database design process, to become familiar with all parts, to develop solid skills in a couple of areas, and to be prepared to participate in -- and especially to learn more about -- the rest.

Where Are We?

For the last two weeks, we have been exploring conceptual modeling and logical modeling in parallel. Our specific tools are the Entity Relationship model and the relational data model. In this session and next week's sessions, we will firm up our understanding of both and bring them together. We will then focus our attention on the relational model and database programming.

Questions to Answer

As part of doing the online activity last week, you asked a lot of questions. I noticed a few common threads and a few insightful concerns:

• When can, or should, an attribute be an entity?
• Is there something I don't see that could be an entity or attribute?
• Is there a limit to the number of attributes a single entity can have? If so, is that when we factor out a new entity?
• Should my attributes be more specific or more general?
• Do all entities need to relate to each other in some way?
• How do we represent many-to-many relationships in an ER model? What if it has other attributes?
• How do I tell if something is redundant or necessary? Can it depend on the situation?
• What does a database with redundancies would look like?

Let's look over a few student designs, some needing more work than others, and see if we can't answer some or all of these in context. You won't be seeing your design in class, so don't worry. That said, getting feedback on our own work is often the best way to get better. Seek out honest, thoughtful feedback whenever possible!

Reviewing Some ER Diagrams

Here are four diagrams submitted from online session 2.

Finally, take a look at my ER diagram for a university database from the online activity...

Here are a few key ideas to keep in mind:

• An entity is singular. There will almost certainly be multiple instances of this kind of object in the database. A database is many of something.


• Attributes are atomic values. An attribute like "statistics" is a red flag.
• Choose attributes that are fundamental, not computed. Consider: age versus date of birth.


• Multiple entities can partake in a relationship. Consider a "supplies" relationship among a supplier, a part, and a project. However, I think you'll find that most often the relationships you want to model in a domain are binary.
• Relationships exist among entities. A relationship will never connect two relationships.


• Faithful designs always leave something out. Entities in the world are usually more complicated than what we need to know about them in our databases.
• A common redundancy is to store the same attribute in multiple locations.
• Another common redundancy is to represent relationships that are computable from others. Consider: coach [-of-] team [-contains-] player [-coachedBy-] coach.
• An unnecessary attribute violates parsimony, not redundancy. Regardless, you don't want it!


• Use the design principles to make your design better, not to justify why your design is correct.

Oh, and one cultural takeaway: guys like sports.

One new idea is worth highlighting: Relationships can have different cardinality.

• 1:1
• 1:n and n:1
• m:n

I didn't answer your questions individually. I hope this session helps. It might be worth looking back at your own submission and questions. If you still have a question from that activity, or a new question, please ask!

Exercise: Create an ER Diagram

Draw an ER diagram for a database for a bank that includes information about its customers and their accounts. Each customer has a name, an address, a phone number, and a Socal Security number. Each account has a number, a type (checking or savings), and a balance. A customer has one or more accounts.

Reviewing the Exercise

What factors do we have to consider? Are there alternatives? For example, what data type is account type: string or boolean? If string, can we narrow the domain?

Here is one design:

How does this design stand up in the face of the design principles we learned last week?

As described, the Owns relationship is 1:n. Could it be 1:1, n:1, or m:n? How do we represent cardinality in our ER diagrams?

What if a customer can have multiple addresses or multiple phones? An attribute becomes an entity with its own attributes! -- street, city, state, zip code.

Up Next

Watch for this week's online activity.

Homework 1 will be posted this afternoon along with these notes.