OVERVIEW
This course introduces the principles of relational databases and the use of data modeling for design. It explains how to incorporate business requirements into a logical data model. The components of a logical data model are defined and how to represent this model as an entity relationship diagram. The normalization of data and how to handle complex relationships are also included. This course also includes the concepts of a Data Warehouse and an overview of the design process for a Data Warehouse.
DURATION: 3 days
OBJECTIVES
- Define relational database terminology
- Describe some of the benefits and advantages of performing logical data modeling.
- Explain the database design life cycle and the role of data modeling
- Define methods for gathering business requirements.
- Document business requirements and use them as input to the data modeling process.
- Explain the distinction between logical data modeling and physical database design.
- Identify the components of a data model, including entities, relationships and attributes.
- Normalize entities to third normal form and select proper unique identifiers (primary keys).
- Handle complex relationships such as many-to-many, and recursive relationships.
- Document the data model with an Entity/Relationship diagram
- Use DDL to define the physical design
- Use DML to query that design
- Understand the overall design and structure of a Data Warehouse
- Be able to identify the attributes of a Star schema Data Warehouse
- Understand the concepts of building a Data Warehouse.
INTENDED AUDIENCE
This seminar is designed for systems analysts, application developers/analysts, business analysts, project leaders and data/database administrators. A general understanding of data processing and database concepts is required.
PREREQUISITE
Participants should have a basic understanding of computer concepts and information technology.
FORMAT/MATERIALS
Lecture and discussion followed by team-based workshops for each major topic to reinforce the material. Through these workshops, participants learn the important process of a team approach to gathering requirements and forming them into a data model. Each participant will receive an extensive lecture guide with several examples and workshop solutions. There is an optional textbook available.
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
Business Requirements
- Business statements
- Business definitions
- Policies, goals and strategies
- Business rules
Gathering business requirements
- Entities
- Identifying entities
- Validating entities
- Documenting "instances" of entities
- Distinguishing entities from attributes
- Naming entities
- Starting an Entity/Relationship (E/R) diagram
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
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)
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
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
- “Structure” entities: fifth normal form
Intro to Data Warehousing
- What is a Data Warehouse?
- The Star Schema
- Star Schema Design
- The Snowflake Schema
- Snowflake Schema Design
- Star vs. Snowflake Schema
- Architecture Decisions
- Required Database Features
- Software Configurations
Building a Dimensional Data Warehouse
- Fact Tables
- Event tracking tables
- Dimension tables
- Database sizing
- Indexing issues
- Aggregation strategies
- Historical duration consideration