Data Modeling Fundamentals

• Data modeling is important.

Good Data Name

• A good data name relates to the business and its characteristics instead of technical aspects.
• A good data name should be meaningful, self-documenting, unique and repeatable.
• It should also be readable and writted in standard syntax.
• It should be composed of words from an approved list.

Data Definitions

• Data definition is an explanation of a term or fact.
• Terms can be a word or phrase with specific meaning.
• Facts are associations between two or more terms.
• A good data definition includes a concise description of essential data meaning.
• It should be gathered in conjunction with systems requirements and accompanied by diagrams.
• Achieved by consensus, and iteratively refined.

E-R Model Constructs - Entities

• Entity types are a collection of entities, often corresponding to a table.
• Entity Instance are person, place, object, event, concept that often corresponds to a row in a table.

E-R Model Constructs - Relationships

• Relationship instance represents the link between entities.
• It corresponds to a primary key-foreign key equivalencies in related tables.
• Relationship type is a category of relationship that links between entity types.

E-R Model Constructs - Attributes

• Attributes are properties or characteristics of an entity or relationship type.
• Attributes often correspond to a field in a table.

Business Rules

• Business rules are statements that define or constrain some aspect of the business.
• They are derived from policies, procedures, events, and functions.
• These rules assert/state business structure and control/influence business behavior.
• Business rules are expressed in terms familiar to end users and automated through DBMS software.

Good Business Rule

• A good business rule is declarative (what, not how), precise, and has an agreed-upon meaning.
• It should be atomic (one statement) and consistent internally and externally.
• It is expressible in structured, natural language, distinct (non-redundant), and business-oriented.

Entities

• An entity is a person, place, object, event, or concept in the user environment.
• It is what the organization wishes to maintain data about.
• An entity type is a collection of entities that share common properties or characteristics.
• An entity instance is a single occurrence of an entity type.
• Entities should be objects that will have many instances in the database.
• Entities should be composed of multiple attributes and objects being modeled.
• Entities should NOT be a user of the database system or an output of the database system.

Strong vs. Weak Entities

• A strong entity exists independently of other types of entities.
• It has its own unique identifier, underlined with a single line.
• A weak entity is dependent on a strong entity (identifying owner) and cannot exist on its own.
• It does not have a unique identifier but only a partial identifier. The entity box and partial identifier have double lines.
• Identifying relationship links strong entities to weak entities.

Composite Key example

• EmployeeID is a strong entity's unique primary Identifier, and Dependent Name is a weak entities partial identifier.

Attributes

• Attribute signifies the property or characteristic of an entity or relationship type.
• Attributes are classified as required versus optional. simple versus composite, single-valued versus multivalued, stored versus derived and as an identifier.
• Attributes can be multivalued (may take on more than one value for a given entity or relationship)
• Attributes can be derived, their values are calculated from related attribute values

Identifiers

• Identifier (Key) is an attribute (or combination of attributes) that uniquely identifies individual instances of an entity type.
• There can be Simple versus Composite Identifiers.
• A candidate Identifier is an attribute that could be a key that satisfies the requirements for being one.

Criteria for Identifiers

• Identifiers should be chosen such that they will not change in value and will not be null.
• Avoid intelligent identifiers such as those containing locations or people that might change.
• Instead of long, composite keys, substitute new, simple keys.
• Consider [Entity type name { [ Qualifier ] } ] Class when naming attributes.

Naming Attributes

• Use a singular noun or noun phrase for attribute names.
• Ensure the name is unique and follows a standard format.
• Similar attributes of different entity types should use the same qualifiers and classes.

Defining Attributes

• State what the attribute is and possibly why it is important. Also explain what is and is not included in the attributes value
• Include aliases in documentation, state source of values, specify if required or optional
• State min and max number of occurrences allowed and indicate relationships with other attributes

Modeling Relationships

• The relationship type is modeled as lines between entity types, while the instance is between specific entity instances.
• Relationships can have attributes that 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).
• An associative entity is a combination of relationship and entity.
• The degree of a relationship is the number of entity types that participate in it.
• This includes unary, binary, and ternary relationships.

Cardinality of Relationships

• One-to-One: Each entity in the relationship will have exactly one related entity
• One-to-Many: An entity on one side of the relationship can have many related entities
• An entity on the other side will have a maximum of one related entity
• Many-to-Many: Entities on both sides of the relationship can have many related entities on the other side.

Cardinality Constraints

• Cardinality constraints indicate the number of instances of one entity that can or must be associated with each instance of another entity.
• A type of cardinality constraint to consider is minimum cardinality: if zero, then optional, if one or more, then mandatory.
• Also consider maximum cardinality, which is simply the maximum number.
• Consider having entities one another in more than one way i.e. multiple relationships

Associative Entities

• An associative entity has attributes and a relationship links entities together.
• Relationships with attributes instead be an associative entity if all relationships for the associative entity should be many.
• The associative entity could have meaning independent of the other entities or a unique identifier as well as other attributes.
• The associative entity may participate in other relationships other than the entities of the associated relationship.
• Ternary relationships should be converted to associative entities.

Time Stamps

• Time stamp is the time value associated with a data value
• Time stamp is often indicating when that affected the data value
• The Price History attribute is both multivalued and composite.
• The Assignment associative entity shows the date range of a product's assignment to a particular product line.
• Different modeling software tools may have different notation for the same constructs.