Database Lesson #4 of 8 - Data Modeling and the ER Model

Introduction to Data Modeling and ER Model

In this section, Dr. Soper introduces the topic of data modeling and the entity relationship (ER) model. The objectives of the lecture are to understand the value of data models, gain expertise in reading and creating ER models, explore different elements of entity relationship models, and discuss various types of relationships between entities.

Data Modeling and Entity Relationship Model

• Data modeling is valuable for designing database solutions.

• Entity Relationship (ER) model is a graphical representation used for data modeling.

• Objectives:

• Understand the value of data models.

• Gain expertise in reading and creating ER models.

• Explore elements of entity relationship models.

• Discuss different types of relationships between entities.

Types of Relationships

• Different types of relationships can exist between entities:

• Unary or recursive relationships

• Binary relationships

• Ternary relationships

Cardinality

• Cardinality describes the number of instances one entity can participate in a relationship with another entity.

Strong and Weak Entities

• Differences between strong and weak entities will be discussed.

• Identifying and non-identifying relationships involving weak entities will be explored.

Supertype and Subtype Relationships

• Supertype and subtype relationships within the entity relationship model will be discussed.

• Recursive relationships within the model will also be examined.

Stages of Database Development

This section discusses the three stages involved in database development: requirements analysis, design phase, and implementation.

Requirements Analysis Stage

• Requirements analysis involves gathering information to understand the data problem that needs to be solved.

• Possible sources of information include interviews with users, existing forms/reports/queries, use cases from unified modeling language (UML), business rules, and observation.

• Joint Application Development (JAD) sessions can be useful for gathering information by bringing stakeholders together for discussions.

Design Phase

• The design phase involves creating data models, which are graphical representations of the database solution.

• Data models are based on the information gathered during requirements analysis.

Implementation Stage

• The implementation stage is where the database designs are transformed into a functioning physical database that can be used to solve data problems.

Entity Relationship (ER) Model

This section provides an overview of the entity relationship (ER) model, its components, and their definitions.

Components of ER Models

• Entities: Represented as tables or relations in the ER model.

• Attributes: Include identifiers/keys and non-key attributes.

• Relationships: Connections between entities.

Entity Class vs. Entity Instance

• An entity class is a structural description of individual occurrences of an entity.

• It can be compared to concepts like recipes or blueprints that provide guidelines for creating instances.

Conclusion

The lecture introduces data modeling and the entity relationship (ER) model. It covers various types of relationships, cardinality, strong and weak entities, supertype and subtype relationships, as well as the stages of database development. The components of ER models are explained, including entities, attributes, and relationships.

Entity Classes and Instances

In this section, the concept of entity classes and instances is explained using an analogy of building houses. The transcript also introduces the idea that entity instances are occurrences of entity classes.

Entity Classes and Instances

• An entity instance is an occurrence of an entity class.

• Entity classes can be compared to architectural blueprints for a house, while the resulting individual houses represent instances of the entity class.

• Attributes define specific information to track for each instance of an entity.

• Examples include employee attributes like ID number, name, and department.

• Adding a new row of data with specific attribute values describes a real-world entity instance.

Attributes in Entity Classes

This section discusses attributes in entity classes and their role in defining specific instances. It also mentions different types of attributes and their properties.

Attributes in Entity Classes

• Attributes are specific pieces of information tracked for each instance of an entity.

• Examples include employee name, age, date hired, project name, start date, etc.

• Filling in attribute values defines a specific instance of the entity class.

• Each attribute has a data type and additional properties like null value allowance or textual description.

• Two types of attributes: identifiers (keys) used to identify instances and non-key attributes used for tracking information.

Identifiers (Keys) in Entity Classes

This section focuses on identifiers (keys) as attributes used to identify instances. It explains unique keys versus non-unique keys and provides examples.

Identifiers (Keys) in Entity Classes

• Identifiers are attributes whose values identify instances of the entity class.

• Common examples include social security numbers, student IDs, employee IDs, email addresses, or department IDs.

• Unique keys have distinct values for each instance/row in the table, like employee numbers.

• Non-unique keys group instances into categories, such as department IDs.

Composite Keys in Entity Classes

This section introduces composite keys and explains how they gain uniqueness by combining values of multiple attributes. An example of a composite key is provided.

Composite Keys in Entity Classes

• Composite keys combine values from two or more attributes to achieve uniqueness.

• Example: Flight number and date can be combined to identify a specific flight with real-world details.

• The combination of these values creates a unique identifier for the instance.

Representing Entities in Entity Relationship Diagrams

This section briefly mentions different ways entities can be represented in entity relationship diagrams (ERDs).

Representing Entities in ERDs

• There are various ways to represent entities in ERDs, but no specific details are provided in this section.

Conceptual Data Modeling

This section introduces the concept of conceptual data modeling and different ways to represent entities.

Representing Entities in Conceptual Data Modeling

• In conceptual data modeling, entities are represented without considering their attributes.

• One way to depict entities is by showing the entity itself and its key attributes.

• Another way is to represent the entity in its entirety, including all non-key attributes.

Relationships Between Entities

• Relationships naturally emerge among business concepts in data modeling.

• Different types of relationships can be defined based on the degree of participation:

• Unary relationship: an entity related to itself (e.g., marriage).

• Binary relationship: one entity related to another entity (most common type).

• Ternary relationship: three entities involved in the relationship.

Examples of Relationships

• Unary Relationship:

• Example: Person entity related to itself through marriage.

• A person can be married to another person.

• Binary Relationship:

• Example: Employee and parking space entities related to each other.

• An employee may park in a parking space or be assigned a parking space.

• Ternary Relationship:

• Example: Doctor, patient, and drug entities involved in a prescription.

• A doctor writes a prescription involving a patient and a specific drug.

Describing Relationships Between Entities

This section introduces symbols used for describing relationships between entities and provides examples of different types of relationships.

Symbols for Conceptual Relationships

• Simple line symbol represents a one relationship between entities.

• Crow's foot symbol represents the many relationship between entities.

One-to-One Relationship

• In a one-to-one relationship, an instance of one entity is related to one instance of another entity.

• Example: Employee and locker entities have a one-to-one relationship.

One-to-Many Relationship

• In a one-to-many relationship, an instance of one entity is related to multiple instances of another entity.

• Example: Department and employee entities have a one-to-many relationship.

Many-to-Many Relationship

• In a many-to-many relationship, multiple instances of one entity are related to multiple instances of another entity.

• Example: Student and course entities have a many-to-many relationship.

Conclusion

In this transcript, we learned about conceptual data modeling and different ways to represent entities. We also explored various types of relationships between entities and how to describe them using symbols. Understanding these concepts is essential for effective data modeling.

New Section

This section discusses different types of relationships between entities in entity relationship modeling.

One-to-Many Relationship

• A one-to-many relationship exists between the department and employee entities. An employee works in one department, but a department can have many employees. This is a one-to-many relationship between entities.

Many-to-Many Relationship

• A conceptual many-to-many relationship can exist between an item entity and a supplier entity. In this type of relationship, many instances of one entity can be related to many instances of another entity. For example, a supplier can supply many items, while a given item might be supplied by many different suppliers.

• In a real-world implementation of a database, we cannot directly implement a many-to-many relationship between two entities. We must always have a third entity or table that acts as a lookup table to link the instances of both entities together. For example, to implement the many-to-many relationship between item and supplier entities, we would need to create a third entity called "item supplier" to link items and suppliers together.

Cardinalities

• Cardinalities allow us to describe the relationships between entities more precisely.

• There are two types of cardinalities: maximum cardinality and minimum cardinality.

• Maximum cardinality specifies the maximum number of instances of one entity allowed to participate in a relationship, while minimum cardinality specifies the minimum number of instances that must participate in the relationship.

• Maximum cardinalities are typically defined as one, many, or some other specific positive fixed number.

• Minimum cardinalities are typically specified as zero or one.

Maximum Cardinality

• The hash mark symbol across the relationship line represents maximum cardinality.

• It indicates how many instances of one entity can participate in the relationship.

• The symbol closest to the entity represents the maximum cardinality.

Minimum Cardinality

• The circle symbol represents a minimum cardinality of zero.

• Minimum cardinalities are specified as zero or one, indicating an optional or mandatory relationship respectively.

• The maximum cardinality can never be zero, as it would mean no instances of one entity are allowed to participate in the relationship.

Types of Relationships

• Based on the different combinations of minimum and maximum cardinalities, there are four types of specific relationships that can exist among entities in an entity relationship model:

• One-and-only-one (exactly one) relationship: Both minimum and maximum cardinalities are one.

• One-to-many relationship: Many instances of one entity may participate in a relationship with a single instance of another entity. At least one entity instance must be involved in the relationship.

• Zero-to-one relationship: Minimum cardinality is zero and maximum cardinality is one. It indicates an optional relationship where an instance of one entity may or may not be related to an instance of another entity.

• Zero-to-many relationship: Minimum cardinality is zero and maximum cardinality is many. It indicates that an instance of one entity may be related to multiple instances of another entity, but it is not mandatory for any instance to participate in the relationship.

New Section

This section discusses entity relationship diagrams and the relationships between entities in a database.

Entity Relationship Diagrams

• Entity relationship diagrams (ERDs) are graphical representations of the relationships among entities in a database. Entities are represented by rectangles, and relationships are depicted using lines.

• Cardinality symbols, such as crow's foot symbols, are used to indicate the specific nature of the relationships between entities.

• The examples shown in this section represent "HAS-A" relationships, where an entity has a relationship with another entity.

Strong and Weak Entities

• A strong entity is an entity that can exist independently in the database without relying on other entities.

• A weak entity is an entity that cannot exist in the database unless it is related to an instance of another entity.

• There are two types of weak entities: ID-dependent weak entities and non-ID dependent weak entities.

• An ID-dependent weak entity has its uniqueness partially linked to its parent entity. Its primary key includes the primary key of its parent entity.

• A non-ID dependent weak entity does not have its identifier composed of the identifier of a strong entity.

Identifying Relationships

• If a relationship between a strong entity and a weak entity is ID-dependent, it is called an identifying relationship. The primary key of the strong entity appears as part of the weak entity's primary key.

• Identifying relationships are represented by solid lines on ERDs.

Non-Identifying Relationships

• If the identifier of a weak entity is not partially composed of the identifier of a strong entity, it is called a non-identifying relationship.

• Non-ID dependent relationships are represented by dashed lines on ERDs.

New Section

This section continues discussing strong and weak entities, focusing on examples and clarifying differences between ID-dependent and non-ID-dependent relationships.

Examples of ID-Dependent Weak Entities

• In the example of a building and apartment relationship, an apartment cannot exist without belonging to a building. The building name is part of the primary key for the apartment entity.

• ID-dependent weak entities are represented by solid lines on ERDs.

Non-ID-Dependent Relationships

• If the identifier of a weak entity is not partially composed of the identifier of a strong entity, it is a non-ID-dependent relationship.

• Non-ID dependent relationships are represented by dashed lines on ERDs.

• The primary key of the strong entity appears as a foreign key within the weak entity but is not part of its primary key.

New Section

This section concludes the discussion on identifying and non-identifying relationships between strong and weak entities.

Summary

• ID-dependent weak entities have their uniqueness linked to their parent entity's primary key, while non-ID dependent weak entities do not.

• Identifying relationships are represented by solid lines on ERDs, while non-identifying relationships are represented by dashed lines.

• In identifying relationships, the primary key of the strong entity appears as part of the weak entity's primary key. In non-identifying relationships, it appears as a foreign key within the weak entity.

Timestamps may vary slightly depending on video playback.

Entity Relationship Model: Weak Entities and Supertype-Subtype Relationships

In this section, we will discuss weak entities and supertype-subtype relationships in the context of the Entity Relationship Model.

Weak Entities

• A weak entity is an entity that does not have a primary key on its own.

• The primary key of a weak entity is derived from its relationship with a strong entity.

• In the Entity Relationship Model, the primary key of the strong entity appears as a foreign key in the weak entity.

• Weak entities are represented by double rectangles in ER diagrams.

Supertype-Subtype Relationships

• In a supertype-subtype relationship, the subtype is a specific version or case of the supertype.

• The supertype represents a generic concept, while subtypes represent specific variations or types.

• There are two types of supertype-subtype relationships: exclusive and inclusive.

• Exclusive relationships allow each instance of the supertype to be related to at most one subtype.

• Inclusive relationships allow each instance of the supertype to be related to one or more subtypes.

• Subtypes inherit all attributes or properties from their supertypes.

• Supertypes and subtypes are connected using common attributes.

IS-A Relationships

• IS-A relationships indicate that a subtype is a specific type of its supertype.

• For example, a car, truck, and motorcycle are all types of vehicles. Each subtype has unique characteristics but can be broadly classified as vehicles.

• Subtypes inherit all attributes from their supertypes.

Examples

1. Exclusive Supertype-Subtype Relationship:

• Supertype: Student

• Subtypes: Undergraduate Student, Graduate Student

• A student can be either an undergraduate or graduate student but not both simultaneously.

2. Inclusive Supertype-Subtype Relationship:

• Supertype: Student

• Subtypes: Hiking Club, Sailing Club

• A student can be a member of multiple clubs simultaneously.

The timestamps provided are approximate and may not align perfectly with the video.

New Section

In this section, the lecturer discusses exclusive and inclusive supertype-subtype relationships. They also introduce the concept of recursive relationships.

Exclusive and Inclusive Supertype-Subtype Relationships

• If an X symbol appears inside the circle, it indicates an exclusive supertype-subtype relationship.

• If no X appears inside the circle, it indicates an inclusive supertype-subtype relationship.

Recursive Relationships

• Unary relationships allow entities to be related to themselves.

• The example given is an employee entity with a one-to-one relationship between employee ID and manager ID.

• Recursive relationships can be useful for tracking customer referrals or implementing hierarchies.

New Section

This section focuses on how recursive relationships can be used to implement hierarchies using the example of an employee entity.

Implementing Hierarchies with Recursive Relationships

• Each rectangle represents a record in the employee table.

• At the top of the hierarchy is employee ID number one, which has no manager (CEO).

• Employees two, three, and four report to employee number one.

• Employees five and six work for employee three, who in turn works for employee one.

• This recursive relationship allows easy tracking of hierarchical relationships among employees and managers in organizations of any size.

New Section

This section emphasizes that a simple data modeling approach using recursive relationships can implement hierarchies of any complexity.

Tracking Employee-Manager Relationships

• Recursive relationships can track hierarchical relationships among employees and managers in organizations of any size.

• The same approach can be used for small companies with just a few employees or massive companies with thousands of employees and multiple levels of management.

• The key takeaway is that a hierarchy of any arbitrary level of complexity can be implemented using this simple data modeling approach.