Data Modeling
Duration
Three Days
Pre-requisites
The students should have at least some SQL or programming experience with any relational database system.
Description
This course introduces the student to the process of logical database design which is also known as logical data modeling. The course presents topics such as the advantages of logical data modeling, how to incorporate business requirements into a logical data model by using use cases, the components of a logical data model and how to create an Entity-Relationship Diagram (ERD) using the open source software called DBDesigner. The course also shows the student how to normalize data through third normal form and how to handle complex data relationships.
Audience
This course is targeted at business analysts, power-users, technical IT staff, team leaders and project managers who need to understand how to design a database using standard Entity-Relationship modeling techniques.
Topics
- Introduction To Logical Data Modeling
- Business Requirements
- Entities
- Attributes
- Entity Relationships (Associations)
- Entity Supertypes And Subtypes
- Recursive Entity Relationships
- Normalization
Course Outline
Introduction To Logical Data Modeling
- Definition of an entity, attribute, logical data model, key, and normalization
- Benefits of Logical Data Modeling
- Data modeling Versus Physical Database Design
- Data Modeling Roles
- Data Modeling Steps
- An Example of a Logical Data Model
Business Requirements
- Business Statements
- Business Requirements
- Business Rules
- Example: Process a Customer Order
- Functional Decomposition
- Actor Types and Categories
- System Use Case Diagram
- Sub-System Use Case Diagram
- One Use Case Per Function
- Use Case Example: Enter Order Header Data
- Workshop: Recording Business Requirements as Use Cases
Entities
- Definition of an Entity
- Finding Entities
- Finding Pre-Existing Entities
- Finding Nouns and Adjective Nouns
- Derive the Entity Name From Its Instances
- Is a Noun an Entity or Attribute
- Entity Synonyms and Homonyms
- Categorize Entities and Attributes
- Create ERD Entity Rectangles With an Entity Name, Entity Definition, and Entity Synonyms
- Workshop: Identify Entities, Entity Definitions, and Entity Synonyms from Use Cases
Attributes
- Attribute Definition
- Attribute Names
- Unique and Non-Unique Keys Plus Non-Key Attributes
- Domain (Range of Values) of an Attribute
- Other Attribute Meta-Data
- Characteristics of a Unique Key (UK) or Primary Key (PK)
- Characteristics of a Foreign Key (FK)
- Entity Integrity (EI)
- Referential Integrity (RI)
- Referential Integrity: Delete Rules
- Referential Integrity: Update Rules
- Workshop: Identifying Primary Keys, Foreign Keys, and Non-Key Attributes for Each Entity
Entity Relationships (Associations)
- 1 to 1 Relationships
- Optional 1 to Optional 1 Relationships
- 1 to Many Relationships
- Non-Identifying Foreign Keys
- Identifying Foreign Keys
- Many to Many Relationships
- Resolving Many to Many Relationships Into Associative Entities
- Workshop: Creating Entity Relationships (Associations) in an ERD
Entity Supertypes And Subtypes
- When Are Entities Similar
- Customer Supertype and Subtypes
- Creating Supertype and Subtype Relationships in an ERD
- Are Subtypes Mutually Exclusive or Not?
- Multiple Supertype and Subtype Dimensions
- Should Subtype Discriminators Be Used?
- Workshop: Identifying Entity Supertypes and Subtypes
Recursive Entity Relationships
- Fixed-Level (Finite) Recursion Hierarchies
- Variable-Level Recursion Hierarchies with No Fixed Intersection Data (No FID)
- Variable-Level Recursion Hierarchies with Fixed Intersection Data (FID)
- Workshop: Identifying Recursive Entity Relationships
Normalization
- Definition of Normalization
- Definition of Denormalization
- First Three Normal Forms
- First Normal Form (1NF)
- Second Normal Form (2NF)
- Third Normal Form (3NF)
- Workshop: Validating Your Logical Data Model Using Normalization Techniques
