Data Modeling

Length:

In-person classes – 2 days / Virtual (Live online) and Anytime Learning – 4 sessions

Overview: 

This course introduces people to the principles and process of logical data modeling, which is to say translating business data requirements into a graphical representation.  It teaches people how to analyze business requirements that should be incorporated into a logical data model, how to create the components of a logical data model, how to diagram/explain them, and how to normalize data and handle complex relationships.  In short, participants will learn proven, practical skills for analyzing and modeling data requirements, and make them ready to be transformed into a relational database.

Pre-requisites: A general understanding of information technology and database concepts is required.

Skill Level: Basic

Audience:

This seminar is designed for business analysts, application developers/analysts, business analysts, project leaders and data/database administrators.

PDUs/CDUs: 14   CEUs: 1.4

Certificate Programs:

• Masters Certificate in Business Analysis

Format: 

To assimilate the tools and techniques learned, there is a mixture of individual and team exercises throughout the course.  A lively role play and case study help reinforce concepts learned.  Students need to be prepared for a high level of participation.

Content:

Introduction to Logical Data Modeling

• Definitions
• Benefits of logical data modeling
• Data modeling vs. physical database design
• Roles involved in data modeling
• Steps in the data modeling process
• Example data model

Entities

• Identifying entities
• Validating entities
  • Documenting "instances" of entities
  • Distinguishing entities from attributes
• Naming entities
• Starting an Entity/Relationship (E/R) diagram
• Workshop

Relationships

• Identifying significant relationships
• Determining the "cardinality" or "degree" of a relationship
  • One-to-One
  • One-to-Many
  • Many-to-Many
• Determining whether a relationship is optional or mandatory
• Giving a relationship a name
• Documenting the relationships in the E/R diagram
• Walking people through an E/R diagram
• Workshop
• Resolving Many-to-Many Relationships
• Real-world examples of many-to-many relationships
• Why many-to-many relationships are broken down into simpler relationships
• Identifying "association" or "intersection" entities
• Documenting the new relationships in the E/R diagram
• Workshop

Attributes and Normalization

• Defining and categorizing attributes
• Domains and integrity rules
• Unique identifiers/primary keys
• Foreign keys
• Occurrence population
• Normalization: validating the placement of each attribute
  • Attribute does not repeat (first normal form)
  • Attribute is dependent on its entire UID (second normal form)
  • Attribute is dependent only on its UID (third normal form)
• Workshop

Subtypes and Supertypes

• Identifying subtypes: real-world examples of subtypes and supertypes
• Determining when entities are similar
  • UIDs
  • Attributes
  • One-to-one relationships
• Creating subtypes and supertypes
• “Type” entities
• Using subtypes to apply fourth normal form
• Establishing the relationships of the sub- and super-entities to other entities
• Mutually exclusive vs. non-mutually exclusive subtypes
• “Role” entities to handle complex subtypes
• Workshop

Recursive Relationships

• Real-world examples of recursive relationships
• Discovering recursive relationships
• Determining whether the relationships are optional or mandatory
• Documenting the new relationships in the E/R diagram
• Hierarchical vs. Network recursive relationships
• “Structure” or ”Bill of Materials” entities: fifth normal form
• Workshop

Appendix: Implementing a Relational Database

• Relational database objects: tables, views, indexes, etc.
• Mapping logical objects to physical objects
• Denormalization
  • Why
  • How
  • Pros/Cons
• Distributing databases
• Referential integrity

 This outline is subject to change.