Improve Your Data Modeling Skills

Data professionals should know that improving their data modeling skills
increases productivity and efficiency. Certifications can demonstrate these
skills, which also improves your marketability.

Motivation

o Essential data modeling skills:

• basic understanding of how to create and read data models

• fundamental data design techniques

Data Modeling

o process of analyzing data-oriented structures

o includes variety of specific model types

• types range from models for physical data to models for high-level
  concepts
  
  o similar to class modeling for object-oriented (OO) design

• data modelers versus OO developers:
  
  . model entity types versus classes
  
  . assign attributes to entity types versus attributes and
  operations to classes
  
  . associations between entities versus between classes in OO
  design: similar

Entity types

o understanding entity types is fundamental skill for data models

o entity types represent:

• collection of similar objects (such as people, places, and things)

• non-physical concepts (such as events)

• example: in order entry database: Customer, Order, and Item are
  common entity types
  
  o entity types only represent data whereas classes also describe
  object’s behavior

Attributes

o entity types have at least one attribute

o example: attributes for entity type Customer typically include attributes
First Name and Last Name

o developers typically implement attributes as columns in database tables

• achieving optimum level of detail is often challenging
  
  o expressing single attributes with multiple columns:

• can provide greater control over data

• incurs development and maintenance costs
  
  o example: phone number in North America

• has three components: Area Code, Prefix, and Line Number

• rarely need to assign each component to separate columns

Naming Conventions

o naming conventions for data modeling:

• typically maintained by enterprise administrators

• essential for making code easy to understand and modify

• physical and logical data models typically have different naming
  conventions since they have different purposes
  
  o example:

• for logical data models: give greater priority to human readability

• for physical models: focus more on technical considerations

Relationships

o relationships between entities:

• key requirement for developing data modeling skills

• conceptually identical to associations between objects in OO
  programming

• example: order entry system:
  
  . Customers place Orders, so placement is typical relationship
  between customers and orders
  
  . Customers live at Address, and Zip Code is part of Address
  
  o naming relationships often becomes unnecessary when specifying
  entities’ role in relationships with sufficient clarity

Key Assignment

o data modeling uses two basic strategies to assign keys to tables:

• assigning natural key:
  
  . usually best option when table has at least one attribute that is
  unique to table’s business concept

• create surrogate key:
  
  . data modelers need to add new column for tables without such
  attribute
  
  ~ no business meaning
  
  ~ merely serves to identify entity type
  
  . example:
  
  ~ addresses do not have obvious natural key because needs entire
  address to identify it
  
  ~ data modelers often identify addresses with surrogate key called
  something like Address Identifier

Normalization

o process of organizing data within data models

o make entity types of data models more cohesive

o generally involves reducing data redundancy

• highly beneficial for application development

• storing objects in relational databases becomes much easier when
  information about those objects is maintained in only one place
  
  o first three levels of normalization are most common
  
  o higher levels are possible
  
  o progressive hierarchy: next level meets all requirements of
  previous level
  
  o example:

• entity type in first normal form (1NF):
  
  . does not contain repeating data groups

• entity type in second normal form (2NF)
  
  . in 1NF
  
  . its non-key attributes fully dependent on its primary key

• entity type is in third normal form (3NF):
  
  . in 2NF
  
  . its attributes directly dependent on primary key
  
  o incurs performance cost

• denormalization also important skill for data modelers

• data models often bear little resemblance to their normalized
  schema

In addition to proper training, the key to improving data modeling skills
is practice.