D426 Chapter 4A — ER Modeling: Entities, Relationships & Cardinality

WGU D426 zyBooks Ch 4, sections 4.1–4.9. Key terms verbatim plus concept, comparison and scenario cards. Not official WGU or zyBooks material.

Entity-relationship model

An entity-relationship model is a high-level representation of data requirements, ignoring implementation details. An entity-relationship model guides implementation in a particular database system, such as MySQL. | In SQL, entities typically become tables, and relationships and attributes typically become foreign keys and columns, respectively. Some relationships and attributes become tables, so requirements are documented as entities, relationships, and attributes rather than tables, keys, and columns. (§4.1)

Entity

An entity is a person, place, product, concept, or activity. | Ex: Passenger and Booking in an airline reservation system. Entities typically become tables. (§4.1)

Relationship

A relationship is a statement about two entities. | Usually the two entities are different, but they may be the same. Ex: Passenger-Holds-Booking. Relationships typically become foreign keys. (§4.1)

Attribute (ER model sense)

An attribute is a descriptive property of an entity. | Ex: PassengerNumber and PassengerName describe Passenger. In an ER diagram, attributes are listed within the entity rectangle; they typically become columns. (§4.1)

Attribute (relational model sense, Terminology box)

Attribute is used in both entity-relationship and relational models. In the relational model, attribute is a formal term for column. Since entity-relationship attributes typically become relational columns, the meaning of attribute is similar in both models. (§4.1)

Reflexive relationship

A relationship is usually a statement about two different entities, but the two entities may be the same. A reflexive relationship relates an entity to itself. | Ex: Employee-Manages-Employee in a human resources system. §4.4 calls relationships among occurrences of the same entity type unary relationships. (§4.1, §4.4)

Entity-relationship diagram (ER diagram)

Entities, relationships, and attributes are depicted in an entity-relationship diagram, commonly called an ER diagram. An ER diagram depicts entities as rectangles with rounded corners, relationships as lines connecting rectangles, and attributes within entity rectangles. ER diagrams always include entities and relationships. Attributes are optional and only appear when additional detail is needed. (§4.1)

Glossary (also called data dictionary or repository)

A glossary, also known as a data dictionary or repository, documents additional detail in text format. A glossary includes names, synonyms, and descriptions of entities, relationships, and attributes. | Simple databases with few users: a text editor; complex databases: a database or software tool designed for glossaries. The ER diagram and glossary are complementary and, together, completely describe an entity-relationship model. (§4.1)

Entity type

An entity type is a set of things. Ex: All employees in a company. | Entity types usually become tables. (§4.1)

Relationship type

A relationship type is a set of related things. Ex: Employee-Manages-Department is a set of (employee, department) pairs, where the employee manages the department. | Relationship types usually become foreign keys. (§4.1)

Attribute type

An attribute type is a set of values. Ex: All employee salaries. | Attribute types usually become columns. (§4.1)

Entity instance

An entity instance is an individual thing. Ex: The employee Sam Snead. | Entity instances usually become rows. (§4.1)

Relationship instance

A relationship instance is a statement about entity instances. Ex: “Maria Rodriguez manages Sales.” | Relationship instances usually become foreign key values. (§4.1)

Attribute instance

An attribute instance is an individual value. Ex: The salary $35,000. | Attribute instances usually become column values. (§4.1)

Conceptual design

Conceptual design develops an entity-relationship model, capturing data requirements while ignoring implementation details. | Particularly important for complex databases with many users; for small databases with just a few tables and users it is less important and often omitted. It covers design steps 1–4. (§4.1)

Logical design

Logical design converts the entity-relationship model into tables, columns, and keys for a particular database system. | Steps 5–8: implement entities, implement relationships, implement attributes, apply normal form. (§4.1)

Physical design

Physical design adds indexes and specifies how tables are organized on storage media. | It depends on specific index and table structures, which vary greatly across relational databases. (§4.1)

Surrogates (discovery)

When users are difficult to reach, a database designer may communicate with surrogates. Ex: A sales representative might communicate on behalf of prospective customers. (§4.2)

Synonyms (entity, relationship, and attribute names)

Often, entity, relationship, and attribute names have synonyms. Ex: Representative may be a synonym for SalesAgent. Synonyms are common in informal communications. To avoid confusion, one official name is selected for each entity, relationship, and attribute. Other names are documented in the glossary as synonyms. (§4.2)

Description (glossary entry)

The glossary also contains complete descriptions of entities, relationships, and attributes. The description states the meaning of each entity, relationship, or attribute in complete sentences. The description begins with the name and includes examples and counterexamples to illustrate usage. (§4.2)

Standard attribute types

Type is chosen from a list of standard attribute types such as Name, Number, and Count. Attribute types are not identical to SQL data types. ... Standard attribute types are documented in the glossary and applied uniformly to all attribute names. | Ex: “Amount” might be an attribute type representing monetary values, and “Count” one representing quantity. (§4.2)

Binary relationship

The simplest kind of relationship is known as a binary relationship. A binary relationship is a relationship between two entity types. | Ex: a salesperson sells products; conversely, products are sold by salespersons. (§4.3)

Cardinalities (§4.3 sense: the maximum)

Figure 4.3.2 shows three binary relationships of different cardinalities, representing the maximum number of entities that can be involved in a particular relationship. | §4.3 calls the minimum the modality; §4.6 uses cardinality for maxima and minima together. (§4.3, §4.6)

One-to-one (1-1) binary relationship

Figure 4.3.2 a shows a one-to-one (1-1) binary relationship, which means that a single occurrence of one entity type can be associated with a single occurrence of the other entity type and vice versa. | Ex: a salesperson works in one office, and an office is occupied by one salesperson; a bar (the “one” symbol) appears at both ends. (§4.3)

One-to-many (1-M) binary relationship

Associations can also be multiple in nature. Figure 4.3.2 b shows a one-to-many (1-M) binary relationship between salespersons and customers. The “crow's foot” device attached to the customer entity box represents the multiple association. | A salesperson sells to many customers; a customer buys from only one salesperson. “Many” means a number that can be 1, 2, 3, ... n, not restricted to exactly one, and it may have a known maximum (Ex: at most ten customers per sales territory). (§4.3)

Many-to-many (M-M) binary relationship

Figure 4.3.2 c shows a many-to-many (M-M) binary relationship among salespersons and products. A salesperson is authorized to sell many products; a product can be sold by many salespersons. | A crow's foot appears at both ends. (§4.3)

Modality

Figure 4.3.3 shows the addition of the modality, the minimum number of entity occurrences that can be involved in a relationship. | The inner symbol, which can be a zero or a one, represents the modality (the minimum); the outer symbol, which can be a one or a crow's foot, represents the cardinality (the maximum). (§4.3)

Intersection data

In fact, the quantity 170 falls at the intersection of salesperson number 137 and product number 24013. It describes the combination of or the association between that particular salesperson and that particular product and it is known as intersection data. ... The intersection data describes the relationship between the two entities. | In general, it describes the combination of a particular occurrence of one entity type and a particular occurrence of the other entity type, so it belongs in neither entity box; it is drawn in a separate box attached to the relationship line. (§4.3)

Associative entity

Figure 4.3.5 shows the many-to-many relationship Sells converted into the associative entity SALES. An occurrence of the SALES associative entity does exactly what the many-to-many relationship did: it indicates a relationship between a salesperson and a product, specifically the fact that a particular salesperson has been involved in selling a particular product, and includes any intersection data that describes this relationship. | Cardinalities and modalities reverse: each SALES occurrence involves exactly one salesperson and exactly one product, while a salesperson or a product ties into many SALES occurrences. Over time, preference has shifted toward this style over drawing the intersection data on the relationship line. (§4.3)

Unique identifier (of a many-to-many relationship or associative entity)

In its most basic form, the unique identifier of the many-to-many relationship or the associative entity is the combination of the unique identifiers of the two entities in the many-to-many relationship. | Sometimes another attribute must be added for uniqueness, often a time element (yearly sales: Salesperson Number, Product Number, and Year). Or the associative entity has its own unique identifier, such as a serial number for each sales record or each traffic ticket. (§4.3)

Unary relationships

Unary relationships associate occurrences of an entity type with other occurrences of the same entity type. | Ex: one person may be married to another person; one person may be the parent of other people. §4.1 calls a relationship of an entity to itself a reflexive relationship. (§4.1, §4.4)

One-to-one unary relationship

Figure 4.4.1a shows the one-to-one unary relationship called Back-Up involving the salesperson entity. The salespersons are organized in pairs as backup to each other when one is away from work. | Salesperson 137 backs up 186, and 186 backs up 137; a modality of one rather than zero in each direction forbids a salesperson not having a backup. (§4.4)

One-to-many unary relationship

Some of the salespersons are also sales managers, managing other salespersons. A sales manager can manage several other salespersons. ... Each salesperson (or sales manager) is managed by exactly one sales manager. This situation describes a one-to-many unary relationship. | A salesperson manages zero to many other salespersons: zero if not a sales manager, many if a sales manager with subordinates. (§4.4)

Many-to-many unary relationship

Unary relationships also come in the many-to-many variety. ... Then, the parts are in a many-to-many unary relationship to each other. Any one particular part can be made up of several other parts while at the same time itself being a component of several other parts. | Ex: a hardware-store product can be part of no other products or of several, and be composed of no other products or of several (tools and tool sets). (§4.4)

Bill of materials problem

One classic example of a many-to-many unary relationship is known as the “bill of materials” problem. | A complex object (an automobile, an airplane, a large factory machine tool) is made of basic parts and sub-assemblies that combine into bigger sub-assemblies until they form the entire object; each basic part and sub-assembly is a “part”. (§4.4)

Ternary relationship

A ternary relationship involves three different entity types. | Ex: a many-to-many-to-many relationship among salespersons, customers, and products records which salesperson sold which product to which customer, with intersection data for the date of the sale and the number of units sold. The figure draws SALE related to exactly one salesperson, one customer, and one product, with all three numbers as its key. (§4.5)

Cardinality (§4.6 sense)

In entity-relationship modeling, cardinality refers to maxima and minima of relationships and attributes. | It depends on business rules. Ex: Employee-WorksIn-Department maxima are usually many-one, but many-many if a company assigns employees to multiple departments. (§4.6)

Relationship maximum

Relationship maximum is the greatest number of instances of one entity that can relate to a single instance of another entity. A relationship has two maxima, one for each of the related entities. Maxima are usually specified as one or many. | Occasionally a plural entity has a fixed numeric maximum (Ex: at most three telephone numbers per employee); fixed numeric maxima are documented in the glossary. (§4.6)

Singular vs plural (related entity)

A related entity is singular when the maximum is one and plural when the maximum is many. | Singular: a short bar across the relationship line, next to the entity. Plural: three short lines converging at a point (a crow's foot), next to the entity. (§4.6)

Crow's foot notation

On ER diagrams, maximum of one is shown as a short bar across the relationship line. Maximum of many is shown as three short lines that converge at a point. The three lines look like a bird's foot, so this convention is called crow's foot notation. (§4.6)

Relationship minimum

Relationship minimum is the least number of instances of one entity that can relate to a single instance of another entity. A relationship has two minima, one for each of the related entities. Minima are usually specified as zero or one. | Occasionally a required entity has a minimum greater than one (Ex: two forms of identification per customer); such minima are documented in the glossary. (§4.6)

Optional vs required (related entity)

A related entity is optional when the minimum is zero and required when the minimum is one. | Optional: a circle on the relationship line, farther from the entity than the maximum symbol. Required: a short bar in that position. (§4.6)

Attribute maximum

Attribute maximum is the greatest number of attribute values that can describe each entity instance. Attribute maximum is specified as one (singular) or many (plural). | Attributes are presumed singular; a “P” following the attribute marks it plural. (§4.6)

Attribute minimum

Attribute minimum is the least number of attribute values that can describe each entity instance. Attribute minimum is specified as zero (optional) or one (required). | Attributes are presumed optional; an “R” following the attribute marks it required. Other fixed numbers (Ex: every employee speaks at least two languages) are documented in the glossary. (§4.6)

Unique attribute

Each value of a unique attribute describes at most one entity instance. Ex: Vehicle identification number (VIN) is a unique attribute of Vehicle. | Attributes are presumed not unique; a “U” following the attribute marks it unique. (§4.6)

Unique composite attribute

Occasionally, a composite of several attributes is unique, although the individual attributes are not. Ex: (AirlineCode, FlightNumber) is a unique composite attribute of Flight. | Flights on different airlines may have the same flight number. In ER diagrams the attributes are grouped with a brace before the “U”. (§4.6)

Singular attribute

A singular attribute has at most one attribute value for each entity instance. | Contrast: a unique attribute has at most one entity instance for each attribute value. Ex: FullName is singular but not unique. (§4.6)

Entity-Has-Attribute relationship

Entities have an implicit relationship with their attributes, called Entity-Has-Attribute. Attribute maximum and minimum are the cardinality of Attribute in this relationship. | An attribute is unique when Entity is singular in this relationship. Ex: each VIN has at most one vehicle, so VIN is a unique attribute of Vehicle. (§4.6)

Identifying attribute

An identifying attribute is unique, singular, and required. Identifying attribute values correspond one-to-one to, or identify, entity instances. | Ex: each project has exactly one ProjectNumber and each number describes at most one project, so ProjectNumber (marked R U) is an identifying attribute of Project. (§4.7)

Identify (ER modeling)

Identifying attribute values correspond one-to-one to, or identify, entity instances. | A supertype identifies its subtypes too: employee numbers, which identify all employees, also identify managers. (§4.7, §4.8)

Strong entity

A strong entity has one or more identifying attributes. When a strong entity is implemented as a table, one of the identifying attributes may become the primary key. | Some ER models call strong entities independent. (§4.7, §4.9)

Weak entity

A weak entity does not have an identifying attribute. Instead, a weak entity usually has a relationship, called an identifying relationship, to another entity, called an identifying entity. | Some ER models call weak entities dependent. (§4.7, §4.9)

Identifying relationship

Instead [of an identifying attribute], a weak entity usually has a relationship, called an identifying relationship, to another entity, called an identifying entity. ... In an ER diagram, an identifying relationship has a diamond next to the identifying entity. | For weak entities, identifying relationships replace identifying attributes. Ex: if each project has many tasks, (ProjectNumber, TaskName) identifies Task, and TaskName must be singular, required, and unique within each project. (§4.7)

Identifying entity

Instead [of an identifying attribute], a weak entity usually has a relationship, called an identifying relationship, to another entity, called an identifying entity. The identifying entity must be singular and required in an identifying relationship. | Since it is always singular and required, a diamond replaces its cardinality symbols. Usually a strong entity, but a weak entity (Task identifies Subtask) or several entities (Passenger and Flight identify Booking) can identify a weak entity. (§4.7)

Subtype entity and supertype entity

A subtype entity is a subset of another entity type, called the supertype entity. Ex: Managers are a subset of employees, so Manager is a subtype entity of the Employee supertype entity. | On ER diagrams, subtype entities are drawn within the supertype. A supertype entity usually has several subtypes. (§4.8)

Similar entities

Similar entities are entities that have many common attributes and relationships. Similar entities become subtypes of a new supertype entity. | Common attributes and relationships move to the new supertype; ones not shared stay with the subtypes (Student, Faculty, and Administrator become subtypes of Person). Likewise, an entity with many optional attributes becomes a supertype, and the optional attributes become required attributes of new subtypes. Neither change is automatic or objective. (§4.8)

Partition (of a supertype entity)

A partition of a supertype entity is a group of mutually exclusive subtype entities. A supertype entity can have several partitions. | Subtypes within each partition are disjoint and do not share instances; subtypes in different partitions overlap and do share instances. In diagrams, subtypes within a partition are vertically aligned and different partitions are horizontally aligned. (§4.8)

Partition attribute

Each partition corresponds to an optional partition attribute of the supertype entity. The partition attribute indicates which subtype entity is associated with each supertype instance. | Ex: CreditType, with values “Credit”, “Debit”, and “Gift”, for the CreditCard, DebitCard, and GiftCard partition of Card. (§4.8)

IsA relationship

A supertype entity identifies its subtype entities. The identifying relationship is called an IsA relationship. Ex: Manager-IsAn-Employee relates each manager instance to the corresponding employee instance. | Since every subtype has an IsA relationship to the supertype, the relationship is assumed and may be omitted from the ER diagram. (§4.8)

Subject area

Some ER models may decompose a complex model into a group of related entities, called a subject area. | Ex: the Cloud Information Model is divided into subject areas such as Shipment, Product, and Payment. (§4.9)

Independent vs dependent (entities)

Some ER models may refer to strong entities as independent and weak entities as dependent. | Independent: has its own identifying attribute. Dependent: identified through an identifying relationship to an identifying entity. (§4.7, §4.9)

Unified Modeling Language (UML)

Unified Modeling Language, or UML, is commonly used for software development. Software data structures are similar to database structures, so UML includes ER conventions. (§4.9)

IDEF1X

IDEF1X stands for Information DEFinition version 1X. IDEF1X became popular, in part, due to early adoption by the United States Department of Defense. (§4.9)

Chen notation

Chen notation appeared in an early ER modeling paper by Peter Chen. Chen notation is not standardized but often appears in literature and tools. (§4.9)

Textual notation (cardinality)

Textual notation depicts optional as 0, singular and required as 1, and plural as M. Maximum cardinality appears first, followed by minimum cardinality in parentheses. | Ex: 1(1) = maximum one, minimum one (exactly one); M(0) = maximum many, minimum zero (zero or many). By the same rule, 1(0) = zero or one and M(1) = one or many. (§4.9)

Cloud Information Model

The Cloud Information Model is an industry standard for sales fulfillment. The model is divided into subject areas. | Its diagrams depict cardinality with crow's foot notation and identifying relationships with diamonds. (§4.9)

Clockwise rule: how is a relationship's full name read on an ER diagram?

The full name of a relationship includes the related entities and is written “Entity-Relationship-Entity”. The full name is read clockwise around the center of the relationship. | Ex: Passenger-Makes-Booking, Flight-Includes-Booking, Flight-ArrivesAt-Airport. (§4.1)

Conceptual design: the four steps and their activities

1) Discover entities, relationships, and attributes: 1A identify them in interviews; 1B draw ER diagram; 1C list standard attribute types in glossary; 1D document names, synonyms, and descriptions in glossary. | 2) Determine cardinality: 2A relationship maxima and minima; 2B attribute maxima and minima; 2C identify unique attributes; 2D document cardinality in glossary and, optionally, on ER diagram. | 3) Distinguish strong and weak entities: 3A identify them; 3B determine the identifying relationship(s) for each weak entity; 3C document them in glossary and ER diagram. | 4) Create supertype and subtype entities: 4A identify them; 4B replace similar entities and optional attributes with them; 4C identify partitions and partition attributes; 4D document them in glossary and ER diagram. | The order is nominal; in practice designers move back and forth between steps. (§4.1, §4.2, §4.6, §4.7, §4.8)

Discovery: how do entities, relationships, and attributes show up in interviews?

Entities: usually nouns, but not all nouns are entities; ignore nouns that denote specific data or are not relevant to the database. | Relationships: usually verbs; ignore statements that are not about entities, not relevant, or redundant to other relationships, and look for relationships that are not explicitly stated. | Attributes: usually nouns that denote specific data, such as names, dates, quantities, and monetary values. | Sources: interviews with users and managers (or surrogates) and written documents, such as the user manual for an older version of the database. (§4.2)

Naming conventions for entities, relationships, and attributes

Entity: a singular noun, commonly used and easily understood (Employee, not Employees). | Relationship: Entity-Verb-Entity with an active verb (Manages, not IsManagedBy); the verb alone is enough when the entities are obvious. | Attribute: EntityQualifierType (EmployeeFirstName); the entity can be omitted when obvious and the qualifier when unnecessary (StudentNumber); the type comes from the glossary's standard attribute types. | Formal vs informal: Vehicle-BelongsTo-Person vs BelongsTo; VehicleLicenseNumber vs LicenseNumber. (§4.2)

Crow's foot legend: the bar, circle, crow's foot, and diamond in words

Bar: a short line across the relationship line meaning one; it is a maximum or a minimum depending on its position. | Crow's foot: three short lines converging at a point, meaning a maximum of many. | Circle: a minimum of zero. | Position: maximum symbols always appear next to the entity; minimum symbols appear farther from the entity. | Diamond: placed next to the identifying entity of an identifying relationship, replacing that entity's cardinality symbols. (§4.6, §4.7)

Reading a relationship line: what do the symbol pairs next to an entity mean?

Start at one entity, read the relationship, and take the symbols next to the other entity: they tell how many of that entity relate to one instance of the first. | Two bars: exactly one (singular, required). | Bar next to the entity with a circle farther out: zero or one (singular, optional). | Crow's foot next to the entity with a bar farther out: one or many (plural, required). | Crow's foot next to the entity with a circle farther out: zero or many (plural, optional). (§4.3, §4.6)

ER diagram attribute markers: P, R, U, and a brace

Attributes are presumed singular, optional, and not unique unless marked. | P after the attribute: plural. R: required. U: unique. A brace grouping several attributes before the U: a unique composite attribute. | Ex: EmployeeNumber R U is singular, required, and unique; SkillCode P is plural and optional. (§4.6)

Benefits of supertype and subtype entities

1) Subtypes highlight subsets of data with different attributes and relationships than the supertype, which clarifies database semantics and behavior. | 2) Optional supertype attributes become required subtype attributes, giving compact tables with fewer NULL values. | 3) An attribute repeated in several subtypes becomes a single supertype attribute, ensuring a single name, description, cardinality, and type. (§4.8)

Diagram and model conventions that vary across ER models

Some ER diagrams: depict relationship names in diamonds; depict weak entities in double rectangles; depict each attribute in a separate ellipse connected to the entity with a line; depict subtype entities with IsA relationships rather than inside supertypes; use color, dashed lines, or double lines to convey additional information. | Some ER models: allow relationships between three or more entities; group related entities into subject areas; call strong entities independent and weak entities dependent. | Software tools usually let users choose and automatically switch between conventions. (§4.7, §4.9)

Entity type vs entity instance (and relationship and attribute types vs instances)

A type is a set; an instance is one element of it. | Entity: type Passenger (all passengers) vs instance Muhammed Ali; table vs row. | Relationship: type Passenger-Holds-Booking vs instance “Muhammed Ali holds 39240”; foreign key vs foreign key value. | Attribute: type BookingCode vs instance 39240; column vs column value. (§4.1)

Conceptual vs logical vs physical design

Conceptual: develops the ER model, capturing data requirements and ignoring implementation details (steps 1–4). | Logical: converts the ER model into tables, columns, and keys for a particular database system (steps 5–8). | Physical: adds indexes and specifies how tables are organized on storage media. | Conceptual design is often omitted for small databases with a few tables and users. (§4.1)

Binary vs unary vs ternary relationship

Binary: between two entity types; the simplest kind (salespersons sell products). | Unary: occurrences of one entity type related to other occurrences of the same type (a salesperson manages other salespersons); §4.1 calls this a reflexive relationship. | Ternary: involves three different entity types (which salesperson sold which product to which customer). (§4.1, §4.3, §4.4, §4.5)

Relationship maximum vs relationship minimum

Maximum: the greatest number of instances of one entity that can relate to a single instance of another; usually one (singular) or many (plural); drawn next to the entity as a bar or crow's foot. §4.3 calls it the cardinality, the outer symbol. | Minimum: the least number; usually zero (optional) or one (required); drawn farther from the entity as a circle or bar. §4.3 calls it the modality, the inner symbol. (§4.3, §4.6)

Unique attribute vs singular attribute

Unique: at most one entity instance for each attribute value (each VIN describes at most one vehicle); marked U. | Singular: at most one attribute value for each entity instance; the default unless marked P (plural). | FullName is singular but not unique. An identifying attribute must be unique, singular, and required. (§4.6, §4.7)

Strong entity vs weak entity

Strong: has one or more identifying attributes (unique, singular, required); one may become the primary key; some models call it independent. | Weak: has no identifying attribute; usually identified through an identifying relationship to an identifying entity, drawn with a diamond next to the identifying entity; some models call it dependent. | Ex: Project is strong (ProjectNumber); Task is weak, identified by Project. (§4.7, §4.9)

Supertype entity vs subtype entity: which attributes apply where?

Supertype: the broader entity type; its attributes apply to all subtypes; it identifies its subtypes through IsA relationships and holds a partition attribute for each partition. | Subtype: a subset of the supertype, drawn inside it; its attributes do not apply to other subtypes or to the supertype. | Optional supertype attributes become required subtype attributes. (§4.8)

Crow's foot vs textual notation vs UML, IDEF1X, and Chen notation

Crow's foot: the symbols used in this chapter; bar = one, circle = zero, three converging lines = many. | Textual notation: 0, 1, and M, with the maximum first and the minimum in parentheses, as in M(0). | UML: commonly used for software development; includes ER conventions. | IDEF1X: Information DEFinition version 1X; popular partly due to early US Department of Defense adoption. | Chen notation: from Peter Chen's early ER modeling paper; not standardized but common in literature and tools. | Differences between conventions are stylistic rather than substantial; the choice does not usually affect the resulting database design. (§4.6, §4.9)

Scenario: A food-truck company says: “Our trucks park at several stops each week, and we record each stop's name. Customers place orders for menu items, and we save each order's total and pickup time. Our cooks wear aprons, but we don't track cooks or uniforms.” Name the entities, relationships, and attributes by convention.

Entities (singular nouns): Truck, Stop, Customer, Order, MenuItem. | Relationships (Entity-Verb-Entity, active verb): Truck-ParksAt-Stop; Customer-Places-Order, not Order-IsPlacedBy-Customer; Order-Contains-MenuItem. | Attributes (nouns for specific data, named EntityQualifierType): StopName, OrderTotalAmount, OrderPickupTime. | Ignore cooks, aprons, and uniforms: not relevant to the database. (§4.2)

Scenario: In a bike-share database, every ride is taken by exactly one rider, and a rider can take many rides, though a newly registered rider has taken none. Give the maxima, minima, and crow's foot symbols for Rider-Takes-Ride.

Rider is singular and required (each ride has exactly one rider): two bars next to Rider. | Ride is plural and optional (a rider takes zero or many rides): a crow's foot next to Ride with a circle farther out. | In relationship-name order, the maxima are one-many and the minima are one-zero. (§4.6)

Scenario: A game studio's Player entity: each player has exactly one tag code that no other player shares; exactly one display name, which other players may also use; zero or more favorite genres; and at most one birth year, which some players skip. Mark each attribute.

PlayerTagCode R U: singular, required, unique, so it is an identifying attribute and Player is a strong entity. | PlayerDisplayName R: singular and required but not unique (unique is not the same as singular). | PlayerGenreName P: plural and optional. | PlayerBirthYear with no marker: singular, optional, and not unique, the defaults. (§4.6, §4.7)

Scenario: A podcast network numbers episodes 1, 2, 3, and so on separately for each show, so many shows have an episode 12. Each show has its own unique show code. Classify Show and Episode, and say how an episode is identified.

Show: strong entity; ShowCode is its identifying attribute (unique, singular, required). | Episode: weak entity, because EpisodeNumber is not unique. Show-Releases-Episode is the identifying relationship, with a diamond next to Show, the identifying entity (singular and required). | Episode is identified by (ShowCode, EpisodeNumber); EpisodeNumber must be singular, required, and unique within each show. (§4.7)

Scenario: A clinic tracks which physicians have treated which patients (many-to-many) and the number of visits for each physician-patient pair. Where does the visit count belong, and what identifies the pairing if it becomes an associative entity?

The visit count is intersection data: it describes one physician combined with one patient, so it fits in neither entity box. | As an associative entity (say Treatment), each occurrence relates exactly one physician and exactly one patient, while a physician or patient ties into many Treatment occurrences (the cardinalities reverse). | Identifier: PhysicianNumber plus PatientNumber; add a time element such as Year if counts are kept per year, or give each record its own unique serial number. (§4.3)

Scenario: Classify each unary relationship: (a) a forum post replies to at most one earlier post, and a post can get many replies or none; (b) a recipe can include other recipes, such as a sauce inside a lasagna, and can itself be used in many recipes; (c) each lifeguard is paired with exactly one relief partner, who is paired back.

(a) One-to-many unary: a post replies to zero or one post and has zero or many replies. | (b) Many-to-many unary, the bill of materials pattern: a recipe can be part of several recipes and composed of several recipes. | (c) One-to-one unary, like the book's Back-Up pairs; a minimum of one in each direction means no lifeguard lacks a partner. (§4.4)

Scenario: A game studio's Asset entity: every asset has a name and file size. Sound assets add a duration and texture assets add a resolution; no asset is both. Separately, every asset is either made in-house or licensed, and licensed assets add a license end date. How should this be modeled?

Supertype Asset keeps AssetName and AssetFileSize, which apply to all subtypes. | Partition 1, with a partition attribute such as AssetTypeCode: SoundAsset and TextureAsset (disjoint: no asset is both). | Partition 2, with a partition attribute such as SourceCode: InHouseAsset and LicensedAsset; the license end date becomes a required LicensedAsset attribute instead of an optional Asset attribute. | Subtypes in different partitions overlap (a sound asset can also be licensed), and each subtype relates to Asset through an assumed IsA relationship. (§4.8)