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.
DURATION: 2 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
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