Data Modeling: Names, Definitions, Rules & Models

• Good Data Names are related to business, meaningful, unique, readable, repeatable and in standard syntax
• Data Definitions explain a term or fact, including a term and any facts relating it to others
• Good Data Definitions have concise descriptions, gathered with system requirements, diagrams, and refined by consensus

E-R Model Constructs:

• Entities: Person, place, object, event or concept that corresponds to a row in a table
• Entity Type: Collection of entities, which often corresponds to a table
• Relationships: Link between entities, corresponding to primary key-foreign key equivalencies in related tables
• Relationship Type: Category of relationship, linking between entity types
• Attributes: Properties or characteristics of an entity or relationship type, corresponds to a field in a table

Business Rules:

• Business Rules define or constrain aspects of the business.
• Derived from policies, procedures, events, functions.
• Business Rules assert business structure and control business behavior
• They are expressed in terms familiar to end users, and are automated by DMBS software

Good Business Rules:

• A good business rule should be declarative, precise, atomic, consistent, expressible, distinct and business-oriented

Entities:

• Entity: person, place, object, event, or idea about which an organization saves data.
• Entity Type: collection of entities that share common properties.
• Entity Instance: A single occurrence of an entity type.
• Entities should be an object with multiple instances, attributes that can be modeled.
• Entities should not be a user or an output

Strong vs. Weak Entities:

• Strong Entity: exists independently of other entities, and has its own identifier
• Weak Entity: depends on a strong entity, cannot exist on its own, and does not have a unique identifier. Has a partial identifier.
• Identifying Relationship: links a strong entity to a weak entity.

Attributes:

• Attributes are properties or characteristics of an entity or relationship type.
• Attributes can be Required, Optional, Simple, Composite, Single-Valued, Multivalued, Stored, Derived and an Identifier

Attribute Classifications:

• Required Attribute: must have a value for every entity instance.
• Optional Attribute: may not have a value for every entity instance.
• Composite Attribute: has meaningful component parts.
• Multivalued Attribute: may take on more than one value for a given entity instance.
• Derived Attribute: values can be calculated from related attribute values, and are not physically stored.

Keys:

• Identifiers (Keys) are an attribute (or combo) which uniquely identifies individual instances of an entity type
• Candidate Key: attribute that could be a key and satisfies identifier requirements.
• Identifiers (Keys) should not change and cannot be null
• Intelligent identifiers (e.g., locations or people that might change) should be avoided.
• Substitute new, simple keys for long, composite keys.

Naming Attributes:

• Attribute names should be a singular noun or noun phrase
• Names should be unique and follow a standard formula: [Entity type name { [ Qualifier ] } ] Class
• Similar attributes of different entity types should use the same qualifiers and classes

Defining Attributes:

• State what an attribute is and why.
• Make clear what is included in the attribute’s value
• Include aliases in documentation
• State source of values
• Specify required vs. optional
• State min and max occurrences allowed
• Indicate relationships with other attributes

Relationships:

• Relationship Types are modeled as lines between entity types. Instances exist between specific entity instances
• Relationships can have attributes
• Two entities can have more than one type of relationship between them

Degree of Relationships:

• Degree of relationships is the number of entity types in it
• Unary Relationship: one entity type
• Binary Relationship: two entity types
• Ternary Relationship: three entity types

Cardinality of Relationships:

• Cardinality defines the number of instances of one entity that can or must be associated with each instance of another
• One-to-One: Each entity has exactly one related entity
• One-to-Many: One side can have many related entities, the other must have a maximum of one related entity
• Many-to-Many: Entities on both sides can have many related entities on the other side
• Cardinality Constraints include minimum cardinality and maximum cardinality
• Minimum cardinality of zero is optional
• Minimum cardinality of one or more is mandatory
• Multiple relationships connect entities in ore than one way

Associative Entities:

• Associative Entities are like a relationship with attributes, but it is also considered to be an entity in its own right.
• Associative Entities has attributes
• Relationships link entities together
• Relationships with attributes should be entities if all the relationships for the associative entity are many
• Associative Entity could have meaning independent of the other entities
• Associative entity should have unique identifier and other attributes
• Associative entity may participate in relationships other than associated relationships
• Ternary relationships should be converted to associative entities.

Time Stamps:

• A time stamp is a time value linked to a data value
• Indicates events affecting the data value
• Time stamps are used for time-dependent data, e.g, HIPAA and Sarbanes-Oxley.