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. [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 “crows feet”) -- - [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. ![Entity symbol](../assets/erd/entity.svg) -- - 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. ![Dog entity](../assets/erd/entity_dog.svg) ![Person entity](../assets/erd/entity_person.svg) --- name: attributes # Attributes -- ## Concept Entities have data that describe them... their **attributes**. ![Attribute symbol](../assets/erd/attribute.svg) -- - 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: ![Dog entity](../assets/erd/attributes_dog.svg) --- template: entities ## Example An example of attributes of a person: ![Person entity](../assets/erd/attributes_person.svg) --- 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. ![Key attribute symbol](../assets/erd/key_attribute.svg) -- - 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: ![Key attribute symbol](../assets/erd/key_attribute_dog.svg) --- template: attributes ## Example An example of attributes of a person, with the primary key underlined: ![Key attribute symbol](../assets/erd/key_attribute_person.svg) --- template: attributes ## Foreign Keys Entity Relationship Diagramming purists would say that foreign key fields should not be included in the diagrams. ![Key attribute symbol](../assets/erd/foreign_key_attribute.svg) -- - 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. ![Relationship symbol](../assets/erd/relationship.svg) -- - 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: ![Relationship symbol](../assets/erd/relationship_person_owns_dog.svg) --- template: attributes ## Example Another example of relationship between a dog and a person: ![Relationship symbol](../assets/erd/relationship_dog_bites_person.svg) --- template: attributes ## Example Both relationships in one diagram. ![Relationship symbol](../assets/erd/relationship_owns_bites.svg) --- template: attributes ## Example An ERD play on the phrase, "[It's a dog eat dog world.](https://thevillageidiom.org/dog-eat-dog/)" ![Relationship symbol](../assets/erd/relationship_dog_eats_dog.svg) --- 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. ![Cardinality symbol](../assets/erd/cardinality.svg) -- 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. ![Cardinality example](../assets/erd/cardinality_cyclops.svg) -- - 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. ![Cardinality example](../assets/erd/cardinality_graeae.svg) -- - 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. ![Cardinality example](../assets/erd/cardinality_paintings.svg) -- - 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: -- ![Cardinality example](../assets/erd/cardinality_paintings_many.svg) --- template: cardinality ## Example Of course painters and paintings most likely have attributes of interest to us. ![Cardinality example](../assets/erd/cardinality_paintings_attributes_many.svg) --- template: cardinality ## Example A painter might also own one or more dogs. ![Cardinality example](../assets/erd/cardinality_painters_dogs.svg) --- template: cardinality ## Example But beware... a painter's dog may tear up their painting. ![Cardinality example](../assets/erd/cardinality_paintings_dogs.svg) --- 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.