Conceptual Data Modeling, Entity Relationship Diagrams

1. Conceptual Data Modeling, Entity Relationship Diagrams

2. Importance of Conceptual Data Modeling • Data rather than processes are more complex in many modern information systems. • Characteristics of data (structure, properties) are more stable, i.e. less likely to change over time, easier to reach consensus on. • It is shared between many processes, therefore is crucial in the design of databases, ensuring integrity of the data in an information system, efficiency of processing.

3. An Entity • An ENTITY or (Entity Type) is something of interest in the environment (e.g., person, place, object, event, concept), characterized with ATTRIBUTES. • Represented in E-R diagram by a rectangle • An ENTITY INSTANCE is a particular occurrence of an entity type – NOT SHOWN ON ERDs. ENTITY ~ Table ATTRIBUTE ~ Column in a table ENTITY INSTANCE ~ Row in a table

4. CUSTOMER Customer_Number Last_Name First_Name Street_Address City State Zip Phone An Attribute • A discrete data element • A characteristic (property) of an entity This Customer entity  has eight attributes

5. Cust_ID Last_Name First_Name Address City ST Zip 0001 Snerd Mortimer General Delivery Tampa FL 33647 0002 Fogg Bob 567 Fogg Lane Omaha NE 32405 0003 Amos Famous 2 Cookie Ct. Miami FL 33133 0004 Targa Maxine 67 Fast Lane Clinton NJ 20082 0005 George Scott 56 Neat St. Boulder CO 35882 0006 Guy Nice 290 Pleasant St. Tampa FL 33641 0007 Smith Bob 76 Quaker Path Wynn NY 21118 0009 Smith James 234 Bayview Tampa FL 33641 Example: identify Entity, Attributes, Instances Customer

6. What Should an Entity Be? • SHOULD BE: • An object that is important to business • An object that will have many instances in the database • An object that will be composed of multiple attributes • SHOULD NOT BE: • Data that is not used by the application, should not be stored in the database.

7. Types of Attributes • Simple vs. Composite • Simple - most basic level • Composite – decomposable into a group of related attributes • ex: address (street, city, state, zip) • Single Valued vs. Multi Valued – • Single - only one value per entity instance (e.g., last name, date of birth) • Mulitvalued- multiple values per entity instance (e.g., degrees, clubs, skills) • Stored vs Derived(e.g. DateOfBirthvs Age)

8. Attributes notation Simple

9. An attribute broken into component parts Multivalued an employee can have more than one skill Derived from date employed and current date Textbook’s notation 9

10. emp-id composite Multivalued f_name m_name skill EMPLOYEE name l_name Alternative notation • Multivalued attributes are shown as double ellipses • Composite attributes may be shown broken down into their simple components Simple/Single Valued; identifier

11. Example: time stamping

12. Identifier Attributes • Every instance of an entity must be uniquely identified (to unambiguously distinguish them) • Simple identifier consists of one attribute • Composite identifier consists of more than one attribute (e.g., first name, middle name, and last name) • Partial identifier (in weak entities) – attribute that together with some attribute from another entity identifies an instance • Underline identifiers in diagrams • Double underline partial identifiers

13. Identifiers • Value of identifier must be unique for each entity instance • Should not change value over time • Guaranteed to have a valid value • No intelligent identifiers (e.g. containing locations or people that might change) • Consider substituting single-attribute artificialidentifiers for natural composite identifiersto simplify design and enhance performance

14. Simple vs composite identifiyer

15. Relationships • A relationshipis an association between one or more entities • The degreeof a relationship indicates the number of entities involved • The cardinality of a relationship describes the number of instances of one entity associated with another entity

16. 0 Optional relationships Mandatory relationships 0 or one one and only one 0 0 0 or many one or more Cardinality Constraints Cardinality Constraints - the number of instances of one entity that can or must be associated with each instance of another entity.

17. A project must be assigned to at least one employee, and may be assigned to many An employee can be assigned to any number of projects, or may not be assigned to any at all Example

18. Degrees of Relationships: unary, binary, ternary The number of different entities involved in a relationship

19. More on Relationships • Relationships (many-to-many or one-to-one) can have attributes • These describe features pertaining to the association between the entities in the relationship • Two entities can have more than one type of relationship between them (multiple relationships) • Associative Entity = combination of relationship and entity

20. Entities can be related to one another in more than one way Figure 3-21a Employees and departments

21. Degrees of Relationships - ternary A vendor supplies parts to warehouses. The unit cost and delivery method may differ for every warehouse. Note: a relationship can have attributes of its own

22. More on Entities: Strong vs. Weak Entities • Strong entities • exist independently of other types of entities • has its own unique identifier • represented with single-line rectangle • Weak entity • dependent on a strong entity…cannot exist on its own • Does not have a unique identifier • represented with double-line rectangle • Identifying relationship • links strong entities to weak entities • represented with double line diamond

23. Associative Entities • Associative entities provide details of a many-to-many association. • It’s an entity– it has attributes • AND it’s a relationship– it links entities together • When should a many-to-manyrelationship with attributes instead be converted into an associative entity? • The associative entity could have meaning independent of the other entities • The associative entity preferably has a unique identifier, and should also have other attributes • The associative entity may participate in other relationships other than the entities of the associated relationship

24. Example:many-to-many relationship with attributes vs. associative entity

25. Associative entity – other notation Associative entity is depicted as a rectangle with a diamond inside.

26. Modeling a ternary relationship as an associative entity

27. SUMMARY of basic E-R notation

28. Entity Attribute Relationship Alternative ER Model Notation