knowledge-kitchen / course-notes

class: center, middle # Entity-Relationship Diagrams Database Design --- # Agenda 1. [Overview](#overview) 1. [Entities](#entities) 1. [Attributes](#attributes) 1. [Relationships](#relationships) 1. [Cardinality](#cardinality) 1. [Physical Implementation of Relationships](#cardinality-physical) 1. [Perspectives](#perspectives) 1. [Conclusions](#conclusions) --- name: overview # Overview -- ## Introduction 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](https://en.wikipedia.org/wiki/Peter_Chen)) -- - Information Engineering (IE, or "[crow's feet](https://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model#Crow's_foot_notation)") -- - [Unified Modeling Language](/content/courses/software-engineering/slides/uml-diagrams) (UML) -- - Idiosyncratic styles that mix and match features of each of these -- _We will [more-or-less] follow Chen notation._ --- name: entities # Entities -- ## Concept 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. --- template: entities ## Example An example of two entities: a dog and a person.   --- name: attributes # Attributes -- ## Concept 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. --- template: entities ## Example An example of attributes of a dog:  --- template: entities ## Example An example of attributes of a person:  --- template: attributes ## Primary Keys 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_. --- template: attributes ## Example An example of attributes of a dog, with the primary key underlined:  --- template: attributes ## Example An example of attributes of a person, with the primary key underlined:  --- template: attributes ## Foreign Keys Entity Relationship Diagramming purists would say that foreign key fields should not be included in the diagrams.  -- - See [this discussion](https://dba.stackexchange.com/questions/271264/how-to-represent-foreign-key-in-an-extended-er-model-in-chen-notation) 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. --- name: relationships # Relationships -- ## Concept 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. --- template: attributes ## Example An example of relationship between a dog and a person:  --- template: attributes ## Example Another example of relationship between a dog and a person:  --- template: attributes ## Example Both relationships in one diagram.  --- template: attributes ## Example An ERD play on the phrase, "[It's a dog eat dog world.](https://thevillageidiom.org/dog-eat-dog/)"  --- template: attributes ## Entity or 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: -- - 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._ --- name: cardinality # Cardinality -- ## Concept **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. --- template: cardinality ## Example A [Cyclops](https://en.wikipedia.org/wiki/Cyclopes) has only one eye.  -- - This diagram also shows that each eye belongs to only one cyclops. --- template: cardinality ## Example In the ancient Greek myth of [the Graeae](https://en.wikipedia.org/wiki/Graeae), three sisters named Pephredo, Enyo, and Dino shared one eye and one tooth.  -- - Each of the Graeae has one eye, indicated by the `1` next to the Eye. - Each of the Graeae has one tooth, indicated by the `1` next to the Tooth. - Each eye belongs to multiple Graeae, indicated by the `M` next to the Graeae. - Each tooth belongs to multiple Graeae, indicated by the `M` next to the Graeae. --- template: cardinality ## Example 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: --  --- template: cardinality ## Example Of course painters and paintings most likely have attributes of interest to us.  --- template: cardinality ## Example A painter might also own one or more dogs.  --- template: cardinality ## Example But beware... a painter's dog may tear up their painting.  --- name: cardinality-physical # Physical Implementation of Relationships -- ## Overview 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. --- template: cardinality-physical ## One-to-one 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 | email | | :----- | :--------- | :---------------------- | | 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 | | ... | ... | ... | --- template: cardinality-physical ## One-to-many 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 | email | 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 | | ... | ... | --- template: cardinality-physical ## Many-to-many 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_. --- name: perspectives # Perspectives -- ## Concept 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. --- template: perspectives ## Conceptual model **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. --- template: perspectives ## Conceptual modeling process 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. --- template: perspectives ## Logical model 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. --- template: perspectives ## Logical modeling process 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_. --- template: perspectives ## Physical model 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. -- - This is not abstract, but very concrete and specific to an actual database system. --- template: perspectives ## Physical modeling process 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. --- name: conclusions # Conclusions -- Thank you. Bye.