Click here to view as a presentation
Students will be able to:
-
Identify the data entities for an application
-
Identify attributes for a data entity
-
Identify the relationships between data entities
-
Understand the roles of primary and foreign keys
-
Create an Entity Relationship Diagram (ERD) for an application
-
An important part of the planning process for an application is determining the data persistence needs of that application.
-
This planning results in a data model.
-
The data model is conceptual and is used as a blueprint for implementing the data persistence needs within a given database technology (SQL, NoSQL, etc.).
-
The data model is typically visualized with an Entity Relationship Diagram (ERD).
-
A Data Entity, or just entity, is used to conceptually model (represent) a real-world object within an application.
-
Examples: User, Post, Comment, Order, Product, etc.
-
Each entity type will have one or more attributes...
-
Attributes represent an entity's data. For example, a Book entity would probably have a title attribute.
-
Each attribute has a data type. For example, string, numeric, datetime
- Identify what other attributes a Book entity might have?
-
Remember, the conceptual data model is used as a blueprint for how the actual database will be structured.
-
Each entity in the data model identifies a table in the database. For example, a Book entity will result in a books table in the database.
-
Each attribute in an entity identifies a column in the table. For example, the title attribute will result in a column with the same name.
-
Each row in the table is logically an instance of the entity.
-
Let's design an ERD for a Concert Ticket tracking application.
-
The application should track:
- The tickets for a concert
- The seat and price of the ticket
- The customer that bought a ticket
- The date of the concert
- The performer(s) of the concert
- The venue of the concert
-
Reviewing an application's user stories is a good first step to creating the conceptual data model.
-
There are three components that must be identified:
- The data entities (tables)
- The attributes (columns) for each entity
- The relationships between the entities.
-
Since this application is supposed to track tickets, let's start with a preliminary Ticket entity...
-
Here's our first attempt at modeling the Ticket entity by including all the attributes you might see printed on a ticket:
-
Now let's identify which attributes the Ticket entity currently has that should be their own entity.
-
To do this, we can look for current attributes that:
- Could have attributes of their own, e.g., a Venue.
- Or, be shared between other Tickets, e.g., a Customer.
-
To help with the following exercise, this is how the data might look like in the tickets table:
-
On to the exercise...
-
Database Normalization is a database design technique.
-
A relational database is able to perform searching & updating of data much more efficiently when it is "normalized".
-
Okay, with the entities and their attributes set, let's talk about the third component of the ERD - relationships between the entities...
-
Relationships determine how the entities are related in terms of their cardinality.
-
There are three main types of cardinality:
- one-to-one (1:1)
- one-to-many (1:M)
- many-to-many (M:M)
-
Let's take a look at each of these...
-
The one-to-one relationship exists when one row in a table is "linked" to one row in another table.
-
Although 1:1 relationships are not as common as 1:M and M:M relationships, they have their purpose.
-
Let's see how we specify a one-to-one relationship in an ERD...
-
A business has one mailing address and vice-versa:
-
Let's discuss the connecting line and PK/FK stuff...
-
In an ERD, lines drawn between entities describe the cardinality between those entities as follows:
-
Note that these are the three main types of cardinality. There are more specific versions of these, such as zero or many, as shown here.
-
But what are those (PK) and (FK) attribute annotations you ask?
-
An attribute (column) annotated with PK designates the Primary Key for that entity (table).
-
It is what uniquely identifies a row in a table.
-
Although not as common, a PK can be a composite key, where two or more columns are combined and uniquely identify the a row.
-
An attribute (column) annotated with FK designates a Foreign Key.
-
A FK provides the "link" to a PK in another table.
-
Foreign Keys are what enable a database engine to efficiently join two tables that are related.
-
It's important to note that foreign keys always exist on the many (child/"belongs to") side of a 1:M relationship. Why would this be the case instead of the other way around?
- Okay, back to modeling the relationships between the entities of the ticket tracking application...
-
As a note, to save screen space, the entities are not going to show attributes for the primary and foreign keys.
-
However, be aware that as a default, primary keys are named
idand foreign keys as<parent_entity_name>_id.
-
Usually by focusing on two entities, domain knowledge and common-sense will reveal the relationship (usually a
one-to-many or many-to-many) -
Beginning with Concert and Ticket
What's the relationship? -
Let's see how this is diagramed...
-
Reads as: "A Concert has many Tickets" and "A Ticket belongs to a Concert".
-
Which of the two tables would have to contain the FK?
- Now for Customer and Ticket
What's the relationship?
- Reads as: "A Customer has many Tickets" and "A Ticket belongs to a Customer".
-
Thanks to the way relational databases are designed, you can access other tables that are not directly joined by joining with others that are.
-
For example, you most certainly could access all of the concerts purchased by a customer by joining through tickets.
-
Although not shown on the ERD with a connecting line, you could say that "A Customer has many Concerts through Tickets", as well as, "A Concert has many Customers through Tickets".
-
As a pair exercise, please identify the remaining relationships:
- Concert and Venue
- Concert and Performer
-
We'll review in 2 minutes... (don't peek)
- Note that many-to-many relationships, e.g., Concert and Performer, require a "hidden" join table to implement in the database - Why is this?
-
Modeling data is an important step during the planning of an application. After all, data is the single source of truth!
-
In addition to what we covered in this lesson, there are several other notations/ways to diagram an application's data model. Check out this post from lucidchart.com's website for more info.
-
True or False: Each Data Entity has its own table in a Relational DB?
-
In an eCommerce application with Orders & Products, what would be the relationship (cardinality) between them?
-
In this relationship:
Customer ---< Order, which entity (table) would have the Foreign Key?