Database Design
Entity-Relationship Diagrams (ERDs) provide a way to document the entities in a database along with the attributes that describe them.
Entity-Relationship Diagrams (ERDs) provide a way to document the entities in a database along with the attributes that describe them.
In practice, there are several styles of ER diagrams:
Entity-Relationship Diagrams (ERDs) provide a way to document the entities in a database along with the attributes that describe them.
In practice, there are several styles of ER diagrams:
Entity-Relationship Diagrams (ERDs) provide a way to document the entities in a database along with the attributes that describe them.
In practice, there are several styles of ER diagrams:
Chen notation (named after the originator of ER modeling, Dr. Peter P.S. Chen)
Information Engineering (IE, or "crow's feet")
Entity-Relationship Diagrams (ERDs) provide a way to document the entities in a database along with the attributes that describe them.
In practice, there are several styles of ER diagrams:
Chen notation (named after the originator of ER modeling, Dr. Peter P.S. Chen)
Information Engineering (IE, or "crow's feet")
Entity-Relationship Diagrams (ERDs) provide a way to document the entities in a database along with the attributes that describe them.
In practice, there are several styles of ER diagrams:
Chen notation (named after the originator of ER modeling, Dr. Peter P.S. Chen)
Information Engineering (IE, or "crow's feet")
Idiosyncratic styles that mix and match features of each of these
Entity-Relationship Diagrams (ERDs) provide a way to document the entities in a database along with the attributes that describe them.
In practice, there are several styles of ER diagrams:
Chen notation (named after the originator of ER modeling, Dr. Peter P.S. Chen)
Information Engineering (IE, or "crow's feet")
Idiosyncratic styles that mix and match features of each of these
We will [more-or-less] follow Chen notation.
An entity is something about which we store data.
An entity is something about which we store data.
An entity is something about which we store data.
For example, a customer is an entity, as is a merchandise item stocked by shop.
Entities are not necessarily tangible. For example, a concert or a doctor's appointment is an entity.
An entity is something about which we store data.
For example, a customer is an entity, as is a merchandise item stocked by shop.
Entities are not necessarily tangible. For example, a concert or a doctor's appointment is an entity.
In relational databases, we typically create a separate table for each entity.
An entity is something about which we store data.
For example, a customer is an entity, as is a merchandise item stocked by shop.
Entities are not necessarily tangible. For example, a concert or a doctor's appointment is an entity.
In relational databases, we typically create a separate table for each entity.
In entity relationship diagrams, entities are represented as rectangles with the singular entity name inside.
An example of two entities: a dog and a person.
Entities have data that describe them... their attributes.
Entities have data that describe them... their attributes.
Entities have data that describe them... their attributes.
For example, a customer entity is usually described by a customer number, first name, last name, street, city, state, zip code, and phone number.
A concert entity might be described by a title, date, location, and name of the performer.
Entities have data that describe them... their attributes.
For example, a customer entity is usually described by a customer number, first name, last name, street, city, state, zip code, and phone number.
A concert entity might be described by a title, date, location, and name of the performer.
In relational databases, we typically create a fields within the table representing the entity for each attribute that describes it.
Entities have data that describe them... their attributes.
For example, a customer entity is usually described by a customer number, first name, last name, street, city, state, zip code, and phone number.
A concert entity might be described by a title, date, location, and name of the performer.
In relational databases, we typically create a fields within the table representing the entity for each attribute that describes it.
In entity relationship diagrams, attributes are represented as ellipses with the attribute name inside.
An example of attributes of a dog:
An example of attributes of a person:
In a logical model, some attributes are indicated as primary keys - attributes that can be used to uniquely identify each entity.
In a logical model, some attributes are indicated as primary keys - attributes that can be used to uniquely identify each entity.
In a logical model, some attributes are indicated as primary keys - attributes that can be used to uniquely identify each entity.
For example, a customer entity might have a customer id field that holds an arbitrary unique identifier of that customer.
In entity relationship diagrams, primary key attribute names are underlined.
An example of attributes of a dog, with the primary key underlined:
An example of attributes of a person, with the primary key underlined:
Entity Relationship Diagramming purists would say that foreign key fields should not be included in the diagrams.
Entity Relationship Diagramming purists would say that foreign key fields should not be included in the diagrams.
Entity Relationship Diagramming purists would say that foreign key fields should not be included in the diagrams.
See this discussion if you find this curious or interesting.
However, many often find it useful to do so.
Entity Relationship Diagramming purists would say that foreign key fields should not be included in the diagrams.
See this discussion if you find this curious or interesting.
However, many often find it useful to do so.
Some people put double underlines under the foreign key field, although this is non-standard.
Entity Relationship Diagramming purists would say that foreign key fields should not be included in the diagrams.
See this discussion if you find this curious or interesting.
However, many often find it useful to do so.
Some people put double underlines under the foreign key field, although this is non-standard.
Feel free to follow this non-standard if it helps you explain your ideas.
Entities sometimes have relationships with one another.
Entities sometimes have relationships with one another.
Entities sometimes have relationships with one another.
For example, imagine we have two kinds of entities in a store: a Customer and a Product.
A Customer in a Store might purchase a Product.
Entities sometimes have relationships with one another.
For example, imagine we have two kinds of entities in a store: a Customer and a Product.
A Customer in a Store might purchase a Product.
A Student might register for a Course.
Entities sometimes have relationships with one another.
For example, imagine we have two kinds of entities in a store: a Customer and a Product.
A Customer in a Store might purchase a Product.
A Student might register for a Course.
An Employee works for a Company.
Entities sometimes have relationships with one another.
For example, imagine we have two kinds of entities in a store: a Customer and a Product.
A Customer in a Store might purchase a Product.
A Student might register for a Course.
An Employee works for a Company.
Relationships are represented as a rhombus (diamond) symbol.
An example of relationship between a dog and a person:
Another example of relationship between a dog and a person:
Both relationships in one diagram.
Sometimes it may seem unclear whether a given term should be represented as an entity or as an attribute.
Sometimes it may seem unclear whether a given term should be represented as an entity or as an attribute.
The following questions may help determine the answer to this question:
Sometimes it may seem unclear whether a given term should be represented as an entity or as an attribute.
The following questions may help determine the answer to this question:
Sometimes it may seem unclear whether a given term should be represented as an entity or as an attribute.
The following questions may help determine the answer to this question:
Is the term of direct interest on its own?
Does the term have sub-terms of its own?
Sometimes it may seem unclear whether a given term should be represented as an entity or as an attribute.
The following questions may help determine the answer to this question:
Is the term of direct interest on its own?
Does the term have sub-terms of its own?
Could there be multiple instances of the object for a single record?
Sometimes it may seem unclear whether a given term should be represented as an entity or as an attribute.
The following questions may help determine the answer to this question:
Is the term of direct interest on its own?
Does the term have sub-terms of its own?
Could there be multiple instances of the object for a single record?
Is the term often nonexistent or unknown?
Sometimes it may seem unclear whether a given term should be represented as an entity or as an attribute.
The following questions may help determine the answer to this question:
Is the term of direct interest on its own?
Does the term have sub-terms of its own?
Could there be multiple instances of the object for a single record?
Is the term often nonexistent or unknown?
If the answer to any of these questions is yes
, then the matter in question should be an entity.
Cardinality is a jargon term meaning how many entities of each type are involved in a given relationship.
Cardinality is a jargon term meaning how many entities of each type are involved in a given relationship.
The possibilities are:
Cardinality is a jargon term meaning how many entities of each type are involved in a given relationship.
The possibilities are:
Cardinality is a jargon term meaning how many entities of each type are involved in a given relationship.
The possibilities are:
one-to-one
one-to-many
Cardinality is a jargon term meaning how many entities of each type are involved in a given relationship.
The possibilities are:
one-to-one
one-to-many
many-to-many
Cardinality is a jargon term meaning how many entities of each type are involved in a given relationship.
The possibilities are:
one-to-one
one-to-many
many-to-many
Cardinality is indicated by numbers or letters next to each entity in the relationship.
Cardinality is a jargon term meaning how many entities of each type are involved in a given relationship.
The possibilities are:
one-to-one
one-to-many
many-to-many
Cardinality is indicated by numbers or letters next to each entity in the relationship. Numbers represent a distinct number of the given entity involved in the relationship.
Cardinality is a jargon term meaning how many entities of each type are involved in a given relationship.
The possibilities are:
one-to-one
one-to-many
many-to-many
Cardinality is indicated by numbers or letters next to each entity in the relationship.
Numbers represent a distinct number of the given entity involved in the relationship.
The letters, M
and N
, indicate multiples of the given entity are involved in the relationship.
A Cyclops has only one eye.
In the ancient Greek myth of the Graeae, three sisters named Pephredo, Enyo, and Dino shared one eye and one tooth.
In the ancient Greek myth of the Graeae, three sisters named Pephredo, Enyo, and Dino shared one eye and one tooth.
1
next to the Eye.1
next to the Tooth.M
next to the Graeae.M
next to the Graeae.Each painter paints many paintings.
Each painter paints many paintings.
Each painter paints many paintings.
Note that in this model, any given painting must have been painted by only one painter.
If painters can collaborate on paintings, then we would need to indicate that with a many-to-many relationship:
Each painter paints many paintings.
Note that in this model, any given painting must have been painted by only one painter.
If painters can collaborate on paintings, then we would need to indicate that with a many-to-many relationship:
Of course painters and paintings most likely have attributes of interest to us.
A painter might also own one or more dogs.
But beware... a painter's dog may tear up their painting.
While not all Entity-Relationship diagrams show how cardinality will be "physically" implemented in a real relational database, let us explore this for a minute.
A one-to-one relationship is implemented by having a foreign key in one table pointing to the other... it doesn't matter which table has the foreign key.
A one-to-one relationship is implemented by having a foreign key in one table pointing to the other... it doesn't matter which table has the foreign key.
Image a cyclopses
table in a world where each cyclops has only one eye:
id | name | |
---|---|---|
1 | Brontes | brontes@greek.io |
2 | Steropes | steropes@jourrapide.com |
3 | Arges | arges@mythology.org |
4 | Polyphemus | polyphemus@bmoney.com |
... | ... | ... |
A one-to-one relationship is implemented by having a foreign key in one table pointing to the other... it doesn't matter which table has the foreign key.
Image a cyclopses
table in a world where each cyclops has only one eye:
id | name | |
---|---|---|
1 | Brontes | brontes@greek.io |
2 | Steropes | steropes@jourrapide.com |
3 | Arges | arges@mythology.org |
4 | Polyphemus | polyphemus@bmoney.com |
... | ... | ... |
Related to an eyes
table, where each eye belongs to one cyclops:
id | color | cyclops_id |
---|---|---|
1 | blue | 1 |
2 | hazel | 4 |
3 | blue | 3 |
4 | brown | 2 |
... | ... | ... |
In a one-to-many relationship, it is always the "many" table that has the foreign key pointing to the other.
In a one-to-many relationship, it is always the "many" table that has the foreign key pointing to the other.
Image a cyclopses
table in a world where multiple cyclopses share one eye:
id | name | eye_id | |
---|---|---|---|
1 | Deino | deino@greek.io | 1 |
2 | Enyo | enyo@jourrapide.com | 1 |
3 | Pemphredo | pemphredo@mythology.org | 1 |
... | ... | ... | ... |
In a one-to-many relationship, it is always the "many" table that has the foreign key pointing to the other.
Image a cyclopses
table in a world where multiple cyclopses share one eye:
id | name | eye_id | |
---|---|---|---|
1 | Deino | deino@greek.io | 1 |
2 | Enyo | enyo@jourrapide.com | 1 |
3 | Pemphredo | pemphredo@mythology.org | 1 |
... | ... | ... | ... |
Related to an eyes
table:
id | color |
---|---|
1 | blue |
2 | hazel |
3 | blue |
4 | brown |
... | ... |
A many-to-many relationship requires a new "linking" table that contains only foreign keys pointing to the two records involved in each relationship.
A many-to-many relationship requires a new "linking" table that contains only foreign keys pointing to the two records involved in each relationship.
E.g. a cyclopses_eyes
linking table:
cyclops_id | eye_id |
---|---|
1 | 1 |
2 | 1 |
3 | 1 |
1 | 3 |
2 | 2 |
... | ... |
A many-to-many relationship requires a new "linking" table that contains only foreign keys pointing to the two records involved in each relationship.
E.g. a cyclopses_eyes
linking table:
cyclops_id | eye_id |
---|---|
1 | 1 |
2 | 1 |
3 | 1 |
1 | 3 |
2 | 2 |
... | ... |
A many-to-many relationship requires a new "linking" table that contains only foreign keys pointing to the two records involved in each relationship.
E.g. a cyclopses_eyes
linking table:
cyclops_id | eye_id |
---|---|
1 | 1 |
2 | 1 |
3 | 1 |
1 | 3 |
2 | 2 |
... | ... |
These are not "cyclopses" anymore, since each can have multiple eyes!
The cyclopses
and eyes
tables do not require any foreign keys.
A many-to-many relationship requires a new "linking" table that contains only foreign keys pointing to the two records involved in each relationship.
E.g. a cyclopses_eyes
linking table:
cyclops_id | eye_id |
---|---|
1 | 1 |
2 | 1 |
3 | 1 |
1 | 3 |
2 | 2 |
... | ... |
These are not "cyclopses" anymore, since each can have multiple eyes!
The cyclopses
and eyes
tables do not require any foreign keys.
These tables typically use both fields as a composite primary key.
How you diagram a model depends on whether you're designing the conceptual model, the logical model, or the physical model.
How you diagram a model depends on whether you're designing the conceptual model, the logical model, or the physical model.
How you diagram a model depends on whether you're designing the conceptual model, the logical model, or the physical model.
For small projects, it is sometimes more convenient to jump directly to physical modeling.
For larger, more complex projects, creating an abstract conceptual or logical model is usually beneficial prior to physical modeling and implementation.
Conceptual modeling involves creating a model of a real world problem that is independent of any physical considerations.
Conceptual modeling involves creating a model of a real world problem that is independent of any physical considerations.
Conceptual modeling involves creating a model of a real world problem that is independent of any physical considerations.
This is very high-level and only shows the most important entities and their relationships.
Typically this is a first-cut model, with insufficient detail to build an actual database.
Building a conceptual model involves:
Building a conceptual model involves:
Building a conceptual model involves:
ER diagramming in a simple rough sketch manner, just the entities with optionally some relationships and attributes
Removing redundancy (i.e. normalization).
Building a conceptual model involves:
ER diagramming in a simple rough sketch manner, just the entities with optionally some relationships and attributes
Removing redundancy (i.e. normalization).
Making sure all intended uses of the data are supported by the design.
Compared to conceptual modeling, a logical model shows more detail of all entities and relationships
Compared to conceptual modeling, a logical model shows more detail of all entities and relationships
Compared to conceptual modeling, a logical model shows more detail of all entities and relationships
It is still general and abstract from how the database is actually implemented.
Logical models show the primary key fields, the attributes, and cardinality of relationships (more on these later).
Compared to conceptual modeling, a logical model shows more detail of all entities and relationships
It is still general and abstract from how the database is actually implemented.
Logical models show the primary key fields, the attributes, and cardinality of relationships (more on these later).
Foreign keys are optional.
Building a logical model involves:
Building a logical model involves:
Building a logical model involves:
Further refine the conceptual model to include all entities and attributes and relationships.
Determine which attributes will serve as primary keys, and [optionally] also foreign keys.
Building a logical model involves:
Further refine the conceptual model to include all entities and attributes and relationships.
Determine which attributes will serve as primary keys, and [optionally] also foreign keys.
Establishing the cardinality among all entities.
Building a logical model involves:
Further refine the conceptual model to include all entities and attributes and relationships.
Determine which attributes will serve as primary keys, and [optionally] also foreign keys.
Establishing the cardinality among all entities.
Normalization of those tables, following the standard relational database normal forms.
Building a logical model involves:
Further refine the conceptual model to include all entities and attributes and relationships.
Determine which attributes will serve as primary keys, and [optionally] also foreign keys.
Establishing the cardinality among all entities.
Normalization of those tables, following the standard relational database normal forms.
We will aim to achieve the logical level of diagramming.
The physical model includes all the same properties as the conceptual and logical models, in addition to more details of how the database will actually be implemented in a particular type of relational database management system.
The physical model includes all the same properties as the conceptual and logical models, in addition to more details of how the database will actually be implemented in a particular type of relational database management system.
Building a physical modeling involves:
Building a physical modeling involves:
Building a physical modeling involves:
Developing the baseline logical model first.
Establishing database indices for increased efficiency.
Building a physical modeling involves:
Developing the baseline logical model first.
Establishing database indices for increased efficiency. An index is an order or organization of the data in some fields of a table to make retrieval faster and more efficient.
Building a physical modeling involves:
Developing the baseline logical model first.
Establishing database indices for increased efficiency. An index is an order or organization of the data in some fields of a table to make retrieval faster and more efficient. Creating indices becomes increasingly important for larger data sets.
Building a physical modeling involves:
Developing the baseline logical model first.
Establishing database indices for increased efficiency. An index is an order or organization of the data in some fields of a table to make retrieval faster and more efficient. Creating indices becomes increasingly important for larger data sets.
Determining data types for each attribute.
Building a physical modeling involves:
Developing the baseline logical model first.
Establishing database indices for increased efficiency. An index is an order or organization of the data in some fields of a table to make retrieval faster and more efficient. Creating indices becomes increasingly important for larger data sets.
Determining data types for each attribute.
Imposing constraints on the data for increased integrity and security.
Thank you. Bye.
Keyboard shortcuts
↑, ←, Pg Up, k | Go to previous slide |
↓, →, Pg Dn, Space, j | Go to next slide |
Home | Go to first slide |
End | Go to last slide |
Number + Return | Go to specific slide |
b / m / f | Toggle blackout / mirrored / fullscreen mode |
c | Clone slideshow |
p | Toggle presenter mode |
t | Restart the presentation timer |
?, h | Toggle this help |
Esc | Back to slideshow |