Entity Relationship Modeling

What You'll Learn This Week

• Entity Relationship Modeling
  • Set of E-R modeling constructs
  • Entities
  • Attributes
  • Identifiers
  • Relationships
• E-R Diagrams
  • Variation One - What the Kroenke book uses
  • Variation Two - Elmasri/Navathe Book
  • Variation Three - Oracle Designer/2000 CASE
  • Variation Four - Visible Analyst
  • Variation Five - Sybase PowerDesigner
  • This is NOT an E-R diagram!
  • Displaying Attributes
  • Weak Entities
  • Subtype Entities
• For Next Week

Elmasri/Navathe (3rd) ed.	Kroenke (7th ed.)	McFadden (5th ed.)
Chapter 3 and 4	Chapters 3	Chapters 3 and 4

Entity Relationship Modeling

• Entity Relationship Modeling: A Set of constructs used to interpret, specify and document logical data requirements for database processing systems.
• E-R Models are Conceptual Models of the database. They can not be directly implemented in a database.
• Many variations of E-R Modeling used in practice.
• Mainly differences in notation, symbols used to represent the 4 main constructs.

E-R Modeling Constructs

• E-R Modeling Constructs are: Entity, Relationship, Attributes, Identifiers

• It is important to get used to this terminology and to be able to use it at the appropriate time. For example, in the ER Model, we do not refer to tables. Here we call them entities.

• Entity: Some identifiable object relevant to the system being built. Examples of Entities are:
  EMPLOYEE
  CUSTOMER
  ORGANIZATION
  PART
  INGREDIENT
  PURCHASE ORDER
  CUSTOMER ORDER
  PRODUCT
  

  An instance of an entity is like a specific example:
  Bill Gates is an Employee of Microsoft
  SPAM is a Product
  Greenpeace is an Organization
  Flour is an ingredient

• Attribute: A characteristic of an Entity. Properties used to distinguish one entity instance from another. Attributes of entity EMPLOYEE might include:
  EmployeeID
  Social Security Number
  First Name
  Last Name
  Street Address
  City
  State
  ZipCode
  Date Hired
  Health Benefits Plan

  Attributes of entity PRODUCT might include:
  ProductID
  Product_Description
  Weight
  Size
  Cost
  

  Exercise: Come up with a list of attributes for each of the entities above.

• Identifier: A special attribute used to identify a specific instance of an entity.
  
  • Typically we look for unique identifiers:
  • Social Security Number uniquely identifies an EMPLOYEE
  • CustomerID uniquely identifies a CUSTOMER
  • We can also use two attributes to indicate an identifier: ORDER_NUMBER and LINE_ITEM uniquely identify an item on an order.

  Exercise: Choose one of your attributes as the identifier for each of the entities above.

• Relationship: An association between two entities.
  
  • A CUSTOMER places a CUSTOMER ORDER
    An EMPLOYEE takes a CUSTOMER ORDER
    A STUDENT enrolls in a COURSE
    A COURSE is taught by a FACULTY MEMBER
    

  • Relationships are typically given names.

  • A relationship can include one or more entities
  • The degree of a relationship is the number of Entities that participate in the relationship.
  • Relationships of degree 2 are called binary relationships. Most relationships in databases are binary.

  • Relationship Cardinality refers to the number of entity instances involved in the relationship. For example:
    one CUSTOMER may place many CUSTOMER ORDERS
    many STUDENTS may sign up for many CLASSES
    one EMPLOYEE receives one PAYCHECK
    one SALESPERSON is assigned one COMPANY_CAR

    1:N	"One to Many"
    N:M	"Many to Many"
    1:1	"One to One"

    Beware of 1:1 relationships. The two entities involved might be coalesced into one. Also called HAS-A relationship.

    Beware of N:M relationships. Typically split these into two 1:N relationships with an intersection entity.

  • Participation of instances in a relationship may be mandatory or optional.
  • For example,
    one CUSTOMER may place many CUSTOMER ORDERS
    one EMPLOYEE must fill out one or more PAY SHEETS
  • This is also called "minimal cardinality" or the "optionality" of a relationship.

E-R Diagrams

• The most common way to represent the E-R constructs is by using a diagram

• There are a wide variety of notations for E-R Diagrams. Most of the differences concern how relationships are specified and how attributes are shown.
• In almost all variations, entities are depicted as rectangles with either pointed or rounded corners. The entity name appears inside.
• Relationships can be displayed as diamonds (see below) or can be simply line segments between two entities.

• For Relationships, need to convey: Relationship name, degree, cardinality, optionality (minimal cardinality)

• Here we will give examples from 4 variations: The Kroenke textbook, Elmasri/Navathe textbook, Oracle Designer/2000 and Visible Analyst.

Variation One - What the Kroenke book uses

• Relationship Name: Displayed just outside of the relationship diamond.
• Degree: Shown by line segments between the relationship diamond and 2 or more entities.
• Cardinality: Displayed inside the relationship diamond.
• Optionality: Mandatory participation indicated by an intersecting hash mark made perpendicular to the relationship line segment.
  Optional participation indicated by a 0 intersecting the relationship line segment.

For this diagram:

• An ORDER must be placed by one and only one CUSTOMER.
• A CUSTOMER may place zero or more ORDERS.
• An ORDER may have zero or more ITEMS.
• An ITEM must have one and only one ORDER.

Variation Two - Elmasri/Navathe Book

• Relationship Name: Displayed just inside the relationship diamond.
• Degree: Shown by line segments between the relationship diamond and 2 or more entities.
• Cardinality: Displayed between the participating entity and the relationship diamond next to the relationship line. Split up the cardinality.
• Optionality: Mandatory participation indicated by double relationship line
  Optional participation indicated by a single relationship line.

Variation Three - Oracle Designer/2000 CASE

• In Oracle Corporation's Designer/2000, relationships are expressed in a rigid sentence format. For example: An ORDER must be placed by one and only one CUSTOMER.
  The "be" is mandatory making the verb difficult to get right.
• Relationship diamonds are not used.
• Relationship Names: Are expressed as a verb phrase starting with "be".
  There are two phrases, one for each direction of the relationship.
  This phrase is then written along the line segments for the relationship.
• Degree: Shown by line segments between any two entities. As such, 3 way relationships as described in the Kronke book can not exist.

• Cardinality: Single participation ("1" in the previous example) is indicated by a single line segment.
  Multiple participation ("N") is indicated by crow's feet

• Optionality: Mandatory participation is indicated by a solid relationship line segment.
  Optional participation is indicated by a dotted line segment.

• One ORDER must be placed by one and only one CUSTOMER.
• One CUSTOMER may be placing zero or more ORDERS.
• One ORDER may be made up of zero or more ITEMS.
• One ITEM must be an item on one and only one ORDER.

There are a set of tools that can print these "relationship sentences".

Variation Four - Visible Analyst

• Visible Analyst Workbench (VAW) uses the rounded box to show an Attributive Entity - one that depends on the existence of a fundamental entity (noted by just the rectangle).
• The relationships use the following symbols:
  • For cardinality, the crow's feet are used to show a "Many" side of a relationship.
  • A single line show a "One" side of the relationship.
  • Optional participation is shown with an open circle. Thus in the above diagram, a Customer May place one or more Orders.
  • Mandatory participation is shown with two hash marks. Thus in the above diagram, an Order Must be placed by one and only one Customer.

Variation Five - Sybase PowerDesigner

This is not an Entity Relationship Diagram!

It is true: The "Relationships" screen in MS Access is NOT an Entity Relationship diagramming tool. This is a "physical" level diagram of how the tables are actually created.

Displaying Attributes

• Technically, an Entity-Relationship diagram should show only entities and their relationships.
• Consider: Entity-Relationship-Attribute (ERA) model.
• Two main ways to display attributes associated with an entity.
  1. Attributes appear in ovals attached to the entity. Gets messy.
  2. List attributes inside of the entity box.

Weak Entities

• Broad definition. Weak Entity: An entity that depends on another for its existence.

• Elmasri/Navathe definition: Weak entity: Entity types that do not have key attributes of their own.

• ID Dependent Entity: A weak entity that includes the identifier of the related strong entity.

• Examples of strong entities:
  People, Employees, Customers, Clients, Vendors, Students
  Products, Services, Parts, Resources, Materials
  Banks

• Examples of ID Dependent entities: Dependents (of employees), Bank Branches (of Banks).

• ID Dependent entities are sometimes shown with curved boxes as in the Visible Analyst ER example. Note that an ITEM can not exist by itself. It must be identified with a specific Order.

  
  

• The Elmasri/Navathe notation shows the ID Dependent entity with a double box. The "identifying relationship" (from the strong entity to the weak entity) is shown with a double diamond.

• Final note: ID Dependent entities will always result in relations (and later on tables) with composite keys.

Subtype Entities

• Attributes of two or more Entities may overlap significantly but not completely.

• Consider:
  Phone Call (Source#, Destination#, Time of day, Duration)
  LongDistance Call (Source#, Destination#, Time of day, Duration, Long distance Carrier)
  Cell Phone Call (Source#, Destination#, Time of day, LandTime, AirTime)
  

• One approach would be to put all of the attributes into a single entity.
  

  
  

• Second approach, put common attributes into a parent or supertype entity and then have 3 subtype entities.

• Relationship is called an IS-A relationship.
  

  
  

  The above diagram uses the Oracle Designer/2000 symbols for Supertype/Subtype. Below is the same diagram drawn using E-R symbols from the Elmasri/Navathe book.

  
  

  The d in the circle indicates the subtype entity is distinct. Only one subtype entity can participate in an instance.

  As before, the double line between the Call entity and the d in the circle indicates the relationship is mandatory.

For Next Week

• Check for formal assignments
• Chapters to Read

  Elmasri/Navathe	Kroenke	McFadden
  Chapter 9 - Section on Converting ER and EER to relational models	Chapter 6	Chapter 6 and Appendix B

• Form your groups and converge on one idea for the group project. Submit a project proposal.