The Entity-Relationship (ER) Model

2.1 Background

• Used for conceptual database design
• Relies on concepts of entities, attributes, and relationships
• Benefits from pictorial representation, called ER diagrams

2.2 An Entity-Relationship (ER) Diagram

2.3 Entity Types

• Entity types represent sets of objects and are pictured by rectangular nodes
  
  .

  An entity set is the collection of instances (i.e., entities) represented by an entity type.

  

  An entity set is also called an extension of the entity type

2.4 Relationship Types

• Relationship types associate entity types. They are pictured by Diamond nodes, and edges connecting to the related entity types.
  

  A relationship set is the collection of instances (i.e., relationships between objects) represented by a relationship type.

  

  Relationship types may associate an entity type with itself. In such a case, the roles of the entity types in the relationship type are listed on the edges, and the relationship is said to be recursive.

  

2.5 Attributes

• Entity types and relationship types might have attributes.
  

• The value set, or domain, of an attribute is the set of values that may be assigned to the attribute. Mathematically,

  $attribute:entity\to power-set\left(domain\right)$

• Atomic attribute types, pictured by oval nodes
  
• Composite attribute types, achieved by concatenating simpler attribute types, pictured by trees of atomic attributes
  
• Multivalued attribute types
  

  A ‘blue and red’ shirt

• Derived attribute types displayed in dashed ovals
  

  A ‘age’ from ‘birth date’

2.6 Keys and Weak Entity Types

• Key attribute types, identify the instances, may be consisted of more than one attribute, displayed with underlined attribute type names
  
• Weak entity types have no keys. Displayed by double-rectangular nodes
  
• To be identified, instances of weak entity type require an identifying relationship type that relates them to an identifying entity type. Such relations are displayed by double-diamond nodes
  
• Weak entity types typically have partial key for distinguishing their instances.

Regular entity types with keys are sometimes called strong entity types.

2.7 Structural Relationship Constraints

Participation constraint

• specifies whether the existence of an entity depends on its being related to another entity via the relationship type
• Total participation constraints require the participation of every entity the relationship (displayed by double line). Also called existence dependency.
• Partial participation constraints (displayed by a single line).

Cardinality ratio constraint

• Specifies the number of relationship instances an entity can participate in
• Displayed on the diamonds

Cardinality constraint

Specifies lower and upper bounds on the number of relationships each entity can participate in.

Summary of Notation

Entity
Weak Entity
Relationship
Identifying Relationship
Attribute
Key Attribute
Multivalued Attribute
Composite Attribute
Derived Attribute
Total participation of $E_2$ in $R$
Cardinality ratio $1:N$ for $E_1:E_2$ in $R$
Structural constraint (min,max) on participation of $E$ in $R$

2.8 Assignment #1

Due: We, Jan 21

Problem 3.21 in textbook (pages 79–80 4th ed; 93 5th ed):

“Design an er schema for keeping track of information about votes taken in the U.S. House of Representatives during the current two-year congressional session. The database needs to keep track of each U.S. state’s Name (e.g., Texas, New York, California) and includes the Region of the state (whose domain is {Northeast, Midwest, Southeast, Southwest, West}). Each congressperson in the House of Representatives is described by their Name, and includes the District represented, the StartDate when they were first elected, and the political Party they belong to (whose domain is {Republican, Democrat, Independent, Other}). The database keeps track of each bill (i.e., proposed law), and includes the BillName, the DateOfVote on the bill, whether the bill PassedOrFailed (whose domain is {YES,NO}), and the Sponsor (the congressperson(s) who sponsored—i.e., proposed—the bill). The database keeps track how each congressperson voted on each bill (domain of vote attribute is {Yes, No, Abstain, Absent}). Draw an er schema diagram for the above application. State clearly any assumptions you make.”

Notes

• PLEASE: Write your first and last name clearly, and provide a code to be used for posting your grades on the web.

• If you submit your homework electronically, use the departmental submit utility: submit XXX lab1 filename. XXX represents ‘c670aa’ for students of the 3:30 section, and ‘c670ab’ for students of the 5:30 section.
  • Files are restricted to 1,048,576 bytes
  • The submit program issues error messages to the student’s CSE email account

Reference: Ch. 3 in textbook.