Data Modeling
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.
Learning Objectives
-
Describe some of the benefits and advantages of performing logical data modeling.
-
Explain the distinction between logical data modeling and physical database design.
-
Apply a repeatable four-step process for doing logical data modeling
-
Identify the components of a data model, including entities, relationships and attributes.
-
Normalize entities to fifth normal form and select proper unique identifiers (primary keys).
-
Handle complex relationships:
-
Many-to-many
-
Sub-types and super-types
-
Recursive/”Bill of Materials”
-
-
Document the data model with an Entity/Relationship diagram and walk through portions of a data model with people to validate it.
Who should attend
This workshop is designed for business analysts, application developers/analysts, project leaders and data/database administrators.
Prerequisites
A general understanding of information technology and database concepts is required.
Format and Material
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.
Duration: 2 Full days or 4 half days
Course Contents
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
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