BA370 - Data Modeling and the ERD Model

• Database development:
1. Requirement or needs analysis --> data model; e.g., Entity Relationship Diagram.
2. Database design --> tables, columns, constraints, disk layout, etc.
3. Database implementation --> coding and population.

• Note that Kroenke provides no information on the process of collecting requirements (we'll discuss and formally practice this in BA 371).
• Entity Relationship Diagrams (ERDs):
• Entities: Discrete objects of information that must be persistently stored (compare with records in table).
• An entity type or class generically describes an entity.
• Weak entity: an entity whose existence depends on another entity (compare with foreign key constraint).
  

• Attributes: Characteristics that define an entity (compare with columns in a table).
• Identifiers: Attributes that uniquely identify an entity (compare with primary key).
• Relationships: Associations between entities.
• Binary relationships: relationships between two entity classes or entity types (examples, Figure 4-4, p. 90):
• 1 : 1 (one-to-one): One entity of class X is associated with one entity of class Y.
• 1 : N (one-to-many) or N : 1 (many-to-one): One entity of class X is associated with one or more entities of class Y.
• M : N (many-to-many): More than one entity of class X is associated with one or more entities of class Y.
• Recursive relationship: An entity maintains a relationship with itself; e.g., a parent can be a child and a child can be a parent; a customer can him/herself have customers.

• Cardinality: a relationship involves one or more entities.
• Optionality: a relationship contains zero or more entities.

• ERD vs. UML (p. 93-94):
• Unified Modeling Language (BA 371): 
• Graphical language for representing entities, entity types and their exchange of information. 
• A methodology for developing object-oriented programs.

• UML has objects (compare with entities), classes (compare with entity types), entity attributes.
• UML models dynamics; i.e., changes in an entity's state as a consequence of input from another entity.

• OO ≠ relational:

  Object orientation	Relational
  Classes	Relations/Tables
  Objects	Records/Tuples
  Attributes	Attributes/Fields/Columns
  Methods (dynamics, behavior); e.g. Figure 4-8, p. 95	-
  Inheritance	-
  Assertions (recent in e.g., Java)	Integrity constraints (primary/foreign key, nullability)
  C++, Java, .NET, etc.	SQL
  Objects	Relational

• Note: language support for the two models is very different ==> many OO designs clash with the relational model or require special 'wrapper' classes.
• 'Our' ERD:
• For each entity class:
• Show entity class name.
• Show attributes, where for each attribute:
• Show name.
• Show datatype.
• Show primary key (if applicable).
  
• Show nullability.
  

• Show binary relationship including cardinality/optionality:
• Note that a variety of notations is acceptable (Kroenke, Microsoft Access (Tools...Relationships), UML, etc).
  

• Note: Microsoft Access's 'relationships' provides a graphical display of entity types, their attributes and the relationships between attributes. However, it does not show all of the required components.