Introducing SQL and Relational Databases

A Database is a Model of a Physical or Conceptual System

Your browser needs to be JavaScript capable to view this video

Try reloading this page, or reviewing your browser settings

This video segment explains the E-R model.

Keywords

  • E-R model
  • entities
  • relationships
  • identifiers
  • entity class
  • entity instance
  • one-to-one
  • one-to-many
  • many-to-many

About this video

Author(s)
Allen Taylor
First online
12 January 2019
DOI
https://doi.org/10.1007/978-1-4842-3841-7_12
Online ISBN
978-1-4842-3841-7
Publisher
Apress
Copyright information
© Allen Taylor 2019

Video Transcript

Speaker: We create databases to record and retain information on some physical or conceptual system that we care about. The system could be a sports league with statistics on all the teams, players and games. It could be a business with customers, products, employees, and sales transactions. It could be the movement of the prices of stocks on the stock market. Any facts that you want to remember that can be organized into a logical structure can be stored in databases. Once in a data is stored, you can later selectively retrieve the specific information you want by formulating a query to the database.

Each database includes things that you want to track and the relationships between those things. There are several ways that you could do this, but for relational databases, the most popular is the Entity Relationship or E-R model. The main things that you want to track are called entities and the relationships between the entities appropriately enough are called relationships. And entity is something that you can identify that you want to keep track of. Entities have attributes which are facts that are true about the entities. An entity class consists of a set of entities that all share the same attributes. And entity instance is a single member of an entity class. Entities also have identifiers and identifier can be a single attribute or a collection of attributes that distinguishes a single instance of an entity class from all the other instances of the same class. For example, a business will probably have an entity class called customers. The members of the customer’s class will have attributes such as first name, last name, email, phone number, et cetera. Two customers might coincidentally have the same name, so a unique customer ID number will probably be assigned to each instance of the customer’s entity to guarantee that one customer instance is not confused with another. The unique customer ID number serves as an identifier, guaranteeing that each entity instance is unique. It’s common for two entities that you care about to be directly related to each other. It’s possible, but less common for three or more entities to be directly related to each other.

Let’s concentrate on the first case called binary or degree two relationships. There are three distinct kinds of binary relationship. One-to-one, one-to-many, and many-to-many. In a one-to-one relationship, one instance of the first entity class relates directly to one and only one instance of the second entity class. And one instance of the second entity class relates directly to one and only one instance of the first entity class. In a one-to-many relationship, one instance of the first entity class relates directly to two or more instances of the second entity class. And all instances of the second entity class relate directly to one and only one instance of the first entity class. In a many-to-many relationship, two or more instances of the first entity class relate to two or more instances of the second entity class. Let’s look at some examples of these three types of relationship. The one-to-one relationship is the simplest. In fact, it’s too simple. One example might be the relationship between a driver and that person’s driver’s license. The person has one and only one license and the license applies to one and only one driver. Another example would be the relationship between a us citizen and that citizens social security number. A citizen can have one and only one social security number, and each social security number applies to one and only one citizen. I say that the one-to-one relationship is too simple because in most cases, it’s not necessary. Instead of having both a driver and a license entity, the state could have a license entity class and let each associated driver’s name and other information be attributes of the licensed entity class. The same is true for the social security number and citizen. The facts about a citizen could be attributes of the entity for the citizens social security number. Sometimes it does make sense to have a one-to-one relationship between two entities, but those occasions are rare.

The one-to-many relationship is the one we will be dealing with the most. E-R models with one-to-many relationships are relatively easy to analyze and thus relatively easy to assure that they accurately represent the physical or conceptual system that they are modeling. The E-R model of a business might have a customer entity and a sales transaction entity. There would be a one-to-many relationship between customer and sales transaction because the customer might engage in multiple sales transactions, but each sales transaction involves the one and only one customer. Another example would be that of a high school football team. The team will have multiple players, but each player will be a member of one and only one high school football team. I’m sure you can think of many examples of this kind of relationship. The third type of relationship is the many-to-many relationship. This is the most complex of the three and thus the one most prone to be modeled incorrectly. One example of this kind of relationship would be the relationship between students and courses. A student in a school can be enrolled in multiple courses and each course can enroll multiple students. Another example of this kind of relationship can be found in a hospital where a patient may receive care from multiple providers and a provider may deliver care to multiple patients.

Relationships have two important properties. Maximum cardinality and minimum cardinality. Cardinality refers to the number of elements in a set, thus maximum cardinality refers to the maximum number of elements that a set may have. And minimum cardinality refers to the minimum number of elements that a set may have. In many cases, the specific number of elements does not make any difference in the model. For maximum cardinality, the thing that matters is whether the maximum number of elements is more than one. For a minimum cardinality, the thing that matters is whether the minimum number of elements is less than one, namely zero. If at least one element must exist, the minimum cardinality is said to be mandatory. If the set could be empty, the minimum cardinality is said to be optional. In most cases, minimum cardinality is of theoretical interest, but it’s not important in practical terms. I will say no more about minimum cardinality.

Maximum cardinality on the other hand is very important because it’s what defines relationships as either one-to-one, one-to-many, or many-to-many. It’s convenient to visualize the relationships between entities with entity relationship diagrams. Related entities are connected by a line that shows the relationship. In the middle of the line, a diamond shaped symbol holds letters or numbers that show the maximum cardinality of the relationship. As an example, consider the one-to-one relationship between drivers and drivers licenses. Each entity is shown within a rectangle. The relationship is shown by a line with a maximum cardinality diamond in the middle. The model for a one-to-many relationship is similar. In this case, the one side of the relationship is denoted by a numeral 1 and the many side by the letter N. The N stands for any number that is more than one. The model for a many-to-many relationship is also similar. This time, the maximum cardinality is denoted by N:M to signify that the maximum cardinality of both sides of the relationship is more than one and the maximum cardinality of the first is not necessarily the same as the maximum cardinality of the second. Most practical database models will have more than two entities. It’s important that the relationships between all of them are accurately modeled. In the next segment, we’ll consider an example system and discuss how to model it.