Data Modeling with ER Diagrams • What is an Entity-Relationship Model? • How is an E-R model represented as an E-R diagram? • How can a video store be modeled as an E-R diagram? • What is a role in a relationship type? • How can we record the history of video rentals? • How are cardinalities and related to roles? • How can we represent purchase orders? • How can we represent time cards and pay statements?
Entity-Relationship Modeling • An E-R model is a data model that includes • Entity classes • Attributes of each class • Relationship types between classes • Constraints • Types of attributes • Designation of key attributes • Cardinalities of relationship types • An E-R Model is typically represented graphically • E-R diagram, the technique we use • UML diagram, an emerging standard for specifying E-R models and software design
Entity Relationship Diagrams • Sample diagram for entity class Customer
E-R Diagram for Class Video • Sample diagram for entity class Video
Relationship Types • Example of representing relationship type Owns between classes Store and Video
Constraints on Relationship Types • Example of cardinality and participation constraints
Modeling Video Rentals • Examples of current rentals and previous rentals • Differences are in cardinalities and attribute names
Modeling Video Rentals as an Entity Class • Diagram shows entity class Rental and its relationship types with Customer and Video • Note cardinalities and participation constraints • A Rental entity cannot exist without being related to both a customer and a video • Problem occurs because Rental has no key attribute
Weak Entity Classes • A weak entity class is • An entity class with no key of its own • An entity class whose entities cannot exist without being related to other entities • An identifying relationship type is • A relationship type that determines the keys of the weak entities
Roles in Relationship Types • Each entity in a relationship plays a specific role in it • Roles are particularly important in relationship types that relate an entity class to itself • Each person in an IsChildOf relationship is either a parent or a child
Employee Roles and Cardinalities • Is this an accurate model of the relationships between employees and stores? • For example, does this diagram answer these questions correctly? • Does a store have to have a manager? • Can an employee be the manager of more than one store? • What other questions can you ask about roles and cardinalities?
Purchase Orders • What does the diagram below mean? • How do we buy many copies of a single movie? • Does each video have its own videoId before it is received? • What does it mean for an order of a video to have a quantity? • The problem with the diagram is with class Video • The diagram fails to distinguish between a movie and a copy of a movie (a video) • We need to revise the diagram to show Movie as an entity class
Modeling Videos and Movies • This diagram more accurately specifies the true nature of Video and Movie • It also has an accurate specification of PurchaseOrder
Time Cards and Pay Statements • Classes TimeCard and PayStatement are weak • Does this diagram show the correct identifying relationships and discriminators? • For example, can an employee have 2 time cards that begin at the same time but are at different stores?