Data Modeling: Using Entity Relationship Diagrams ERD’s

Data Modeling: Using Entity Relationship Diagrams ERD’s CIS 332

Today • Data modeling • Overview • Logical and physical data models • ERD • Overview • Sample ERD • Main elements of ERD • Entity • Attribute • Relationship

Data modeling: Overview • Deeper understanding of data that are used or created by the system is critical. • Process models, DFD’s, provide a general understanding of processes, external entities, data stores, and data flows but they do not show how the data is organized and represented • Data model • Formal representation of data that illustrates people, places, or things about which information is captured and how they are related to each other. • Two types of data models: • Logical data models: logical organization of data without indicating how data are stored, created or manipulated (our focus today) • The main objective is to match the diagram to real business requirements of the system • Physical data models: Implementation and technical details needed to store to organize the storage of files (Chapter 11)

ERD: Overview • An ERD is the most common technique for drawing logical data models • Illustrates data created, stored, and used by the system. • Provides deeper understanding of how pieces of data are organized and related to each other. • Similar kinds of information are grouped together in one box called entity. • For example, Individual pieces of data such as Customer name, Customer address, and Customer birth date could be grouped together in one entity called CUSTOMER • Lines are drawn amongentities to show how they are connected. • For example, if another entity is identified as PRODUCT then a typical relationship between the CUSTOMER and the PRODUCT would be that a CUSTOMER buys a PRODUCT. • Symbols are added to these lines in order to reflect business rules • For example, In the CUSTOMER-PRODUCT relationship, could a customer buy more than one product?

ERD: Overview “Cont.” • Example of a typical ERD • The supermarket ERD Source: Dennis, Wixom, and Roth (2009) Systems analysis and design • Could DFD’s help us in creating ERD’s? • Yes, data flows illustrated in DFD’s could be a good source for generating entities and their attributes in ERD’s

ERD: Overview “Cont.” • The supermarket ERD • The supermarket Checkout DFD

ERD’s main elements: Entity • Basic building block for a data model • Could be a person, place, event, or thing about which data is collected. • Depicted by a rectangle. • Entities should have a name, a short description, an identifier, and multiple occurrences or instances. • An identifier is a way for locating information in the entity such as CUSTOMER_ID. • An instance is an occurrence for an entity. For example: John Doe, Susan Jones, and Peter Todd could be instances of CUSTOMER.

ERD’s Main elements: Entity “Cont.”

ERD’s Main elements: Attribute • Information to describe an entity. • For example, the entity CUSTOMER could be described by the following attributes: First Name, Last Name, and Address • Attributes are nouns listed within an entity • Different entities may share similar attributes. Thus, it is recommended to add the entity name at the beginning of the attributes to avoid confusion. • For example, a CUSTOMER is described by LastName, FisrtName, and Address. An EMPLOYEE is described by LastName, FirstName, and Address. • To avoid confusion, the CUSTOMER attributes would become: CUST_LastNameCUST_FirstName, and CUST_Address. The EMPLOYEE attributes would become EMP_LastNameEMP_FirstName, and EMP_Address

ERD’s Main elements: Attribute “Cont.” • One or more attribute may serve as the entity identifier. • Such identifier should be unique. Why? • For example, we can identify instances within the CUSTOMER entity by Last_Name (If that attribute is unique) • If we cannot find a unique attribute, we may follow one of the following approaches: • Use concatenated identifiers: A combination of multiple attributes (If that combination is unique). For example: CUST_LastName and CUST_FirstName. • Create an artificial identifier. For example, Customer_ID. • Postpone assigning identifiers until the design phase. For example, you can leave this task to the database management system to assign randomly generated numbers.

ERD’s Main elements: Attribute “Cont.” • Choices of identifiers “Summary”

ERD’s Main elements: Relationship • Relationships connect entities. • Shown by lines that connect entities together. • Every relationship has a parent entity and a child entity • The parent is the first entity in the relationship • The child is the second entity in the same relationship. • Relationships should be labeled with active verbs that are read in two directions. • For example, a relationship between SALE and PAYMENT entities could be defined as “A PAYMENT pays a SALE” and “A SALE is paid for by a PAYMENT” • On a relationship line, the top words are read from a parent to a child and the bottom words are read from a child to a parent.

ERD’s Main elements: Relationship “Cont.” • Examples of relationships among entities

Next time • Cardinality and modality of relationships • Data dictionary and Meta data • Steps for creating ERD • Normalization

Data Modeling: Using Entity Relationship Diagrams ERD’s