DMD - Database Modeling and Design
| Categories |
Database Training, SQL Training, Database Modeling and Design Training |
| Duration |
2 days (14 hours) |
| Pre-requisites |
Basic familiarity with databases. Familiarity with Structured Query Language helpful, but not required. For a solid overview of using SQL, see MSSQL - ANSI SQL Using Microsoft SQL Server. |
|
|
|
| Courseware |
DMD-BS-01 (110 pages) |
| Price |
$790 |
| Notes |
Microsoft SQL Server is used in the classroom to execute the SQL statements required to build the database. However, the concepts covered in this course can be applied to data modeling in any language or product, and the SQL used in the course can be easily modified to work on other database management systems, such as MySQL, Oracle, Sybase or dBASE.
|
This Database Modeling and Design training class covers designing data models using Entity Relationship Diagrams (ERDs) and Data Dictionaries, along with exercises that allow students to build a data model and the database it represents. Students will gain enough proficiency to extract a data model diagram from an existing dataset, and to design new data models as a starting point for building a dataset. They will also gain practical experience in building and testing a database based using Structured Query Language (SQL) and the design tools in Microsoft SQL Server, based on the information in a data model.
"[The instructor] was extremely professional and proficient with the presentation of the course. Provided some real-world examples to consider in the application of the fundamentals of database modeling. The course was very logical to follow. Focused more on the structure of the database and didn't elaborate on the interviewing portion of the development process; which for me was exactly what I was looking for.." -- Andy C. from San Diego, CA
"I think the presentation of this class was very good. All questions were answered and explained very well. This class would benefit anyone working with Data Modeling. They would come away with a much better understand of referential integrity.." -- Betty E. from Bismark, ND
"This was an excellent course and I would highly recommend it! [The instructor] is an excellent instructor. The pace and the presentation of the material is concise and very well organized.." -- Sophia R. from La Jolla, CA
"If you are interested in learning about relational database design regardless of your skill level, this is the class for you! The speaker (was very knowledgeable and responded well to the speed of the class.." -- Steve O. from Solana Beach, CA
"Speaker was well informed and allowed for many questions and difference of opinions. I would recommend attending.." -- Deb S. from Bismark, ND
Chapter 1 - Overview
Benefits of Data Modeling
Terms and Concepts
Prerequisites
Chapter 2 - Entity Relationship Diagrams
Database Terminology
Databases
Metadata
Tables
Records and Cursors
ERD Terminology
Entities
Attributes
Relationships
One-to-Many Relationships
One-to-One Relationships
Other Relationships
Relationships are Bi-directional
Diagrams
Working with Entities and Relationships
Chapter 3 - Diagramming
Diagram Notation
Crowsfoot Notation
Entity Frames
Diagramming Relationships
Diagramming One-to-Many Relationships
Diagramming Key Fields
Diagramming One-to-One Relationships
Diagramming Bi-directional Relationships
Positioning of Entity Frames
Connecting Multiple Related Entities
Diagramming Many-to-Many Relationships
Working with Diagrams
Chapter 4 - Data Modeling
Data Dictionaries
Databases
Tables
Table Constraints
Columns
Defining Tables and Fields
Chapter 5 - Key Fields
Primary Keys
Unique Keys
NOT NULL Keys
Foreign Keys
Referencing a Primary Key
Key Considerations
Uniqueness
Key Types
Key Sizes
Numeric Key Sizes
Character Key Sizes
Keys vs. Codes
Auto-increment Keys
Alphanumeric Codes
Compound Keys
Key Field Naming Conventions
efining Key Fields
Chapter 6 - Normalization
First Normal Form: Eliminate Repeating Groups
Second Normal Form: Eliminate Redundant Data
Anomalies
Third Normal Form: Eliminate Non-dependent Columns
Working with Normalization
Chapter 7 - Modeling Relationships
Modeling Entities
Modeling Relationships
Explicit Relationships
Implicit Relationships
Building the ERD
Identifying Additional Relationships
Identifying Primary Keys
Identifying Foreign Keys
Working with ERDs
The Current Entity Relationship Diagram
Chapter 8 - Creating a Database
Using Microsoft SQL Server
Using the Enterprise Manager
Using the Query Analyzer
Selecting a Database
Executing an SQL Statement
Creating a Database
Creating a Database using SQL
Creating a Database Using the Enterprise Manager Creating Tables
Creating Tables Using SQL
Creating Tables with SQL Script Files
Dropping Tables
Adding Sample Data
SQL Inserts
Adding Data with SQL Scripts
Batches in SQL Server Script Files
Implementing Tables with Sample Data
Chapter 9 - Key Constraints
Setting Key Constraints
Adding Primary Key Constraints
Adding Compound Primary Keys
Adding Primary Keys Using the Enterprise Manager Auto-Increment Column Values
Adding Foreign Key Constraints
Adding Foreign Keys Using the Enterprise Manager Adding Constraints to Existing Tables
ALTER TABLE
Sequencing in SQL Scripts
Implementing Primary and Foreign Keys
Chapter 10 - Domains
Creating Domains
Creating Domains in SQL
Using Domains in Tables
Check Constraints
Working with Domains
Chapter 11 - Business Rules
Implementing Business Rules
Data Integrity
Referential Integrity
Key Constraints
Column Constraints
Domain Constraints
Table Constraints
Deletion Rules
Cascade
Prevent
Duplicate Records
Generators
Triggers
Stored Procedures
Implementing Business Rules
Chapter 12 - Testing Data Models
The Testing Process
Data Entry
Queries
Reporting
Correcting Errors
Testing your Data Model
Chapter 13 - Performance Issues
Performance Problems
Excessive Traversal
Redundant Paths
Implementing Redundant Paths
Where to Go From Here
Practical Experience
Other Courses
Appendix A - Data Models
Appendix B - Codd's 12 Rules of Normalization
Appendix C - Worksheets