Modeling Business Requirements to Create a Database Using Microsoft Visual Studio .NET Enterprise Architect

Target Audience
This course provides students with the knowledge and skills to model business requirements in order to create a baseline database design. It will benefit those who need to understand the principles of database design in preparation for modeling, designing, developing, or administering Microsoft SQL Server databases.

Pre-Requisites
Before attending this course, students must have the following pre-requisites:The ability to describe what databases are and their uses.The ability to understand basic concepts of querying databases.The necessary skills to log on to and navigate in a Microsoft Windows environment.

Purpose
After completing this course, students will be able to analyze business requirements; create a conceptual database model using ORM; create a logical database model (ER diagram); validate the model against the external information; transfer the model into a SQL Server database.

Course outline

1. Introduction to Modelling Business Requirements
2. Analyzing External Information and Creating a Conceptual Model-CSDP Step 1
3. Drawing a Conceptual Model and Entering Sample Data-CSDP Step 2
4. Trimming the Conceptual Schema-CSDP Step 3
5. Adding Uniqueness Constraints and Checking Arity of Fact Types-CSDP Step 4
6. Adding Mandatory Role Constraints and Checking For Logical Derivations-CSDP Step 5
7. Adding Value and Set Constraints, and Creating Entity Subtypes-CSDP Step 6
8. Adding Frequency and Ring Constraints-CSDP Step 7
9. Generating a Relational Logical Model
10. Completing the Baseline Model
11. Generating and Reverse Engineering Physical Schema

Module 1:Introduction to Modelling Business Requirements

• Overview of Database Modelling Process
• Data Modelling Concepts
• Lab: Examining External Information

• Describe the process of modelling business requirements.
  
• Summarize the process of modelling a baseline database model.
  
• Summarize data modelling concepts.
  

Module 2:Analyzing External Information and Creating a Conceptual Model-CSDP Step 1

• Verbalizing Data Use Cases
• Formalizing Fact Types
• Lab: Analyzing External Information and Creating a Conceptual Model-CSDP Step 1

• Complete CSDP step 1.
  
• Verbalize data use cases.
  
• Formalize fact types.
  
• Create a conceptual model.
  

Module 3:Drawing a Conceptual Model and Entering Sample Data-CSDP Step 2

• Drawing Fact Types
• Applying a Population Check
• Applying CSDP Step 2
• Lab: Drawing Fact Types and Applying Population Checks

• Apply CSDP step 2.
  
• Draw fact types.
  
• Apply population checks.
  

Module 4:Trimming the Conceptual Schema-CSDP Step 3

• Implementing Primitive Entity Types
• Implementing Derived Fact Types
• Applying CSDP Step 3
• Lab: Implementing Primitive Entity Types and Derived Fact Types-CSDP Step 3

• Check for entity types that should be combined and note any arithmetic derivations.
  
• Implement primitive entity types.
  
• Implement derived fact types.
  
• Apply CSDP step 3.
  

Module 5:Adding Uniqueness Constraints and Checking Arity of Fact Types-CSDP Step 4

• Implementing Uniqueness Constraints
• Implementing Nested Object Types
• Checking Fact Arity
• Applying CSDP Step 4
• Lab: Adding Uniqueness Constraints, and Checking Arity of Fact Types

• Apply CSDP step 4.
  
• Implement uniqueness constraints.
  
• Implement nested object types.
  
• Identify fact arity.
  

Module 6:Adding Mandatory Role Constraints and Checking For Logical Derivations-CSDP Step 5

• Implementing Mandatory Constraints
• Implementing a Primary Reference Scheme
• Checking for Logically Derivable Fact Types
• Applying CSDP Step 5
• Lab: Adding Mandatory Role Constraints, and Checking for Logical Derivations

• Complete CSDP step 5.
  
• Implement mandatory role constraints.
  
• Implement a primary reference scheme.
  
• Avoid modelling logically derivable fact types.
  

Module 7:Adding Value and Set Constraints, and Creating Entity Subtypes-CSDP Step 6

• Implementing Value Constraints
• Implementing Set Constraints
• Implementing Entity Subtypes
• Applying CSDP Step 6
• Lab: Adding Value Constraints, Set Constraints, and Entity Subtypes

• Complete CSDP step 6.
  
• Implement value constraints.
  
• Implement set constraints.
  
• Implement entity subtypes.
  

Module 8:Adding Frequency and Ring Constraints-CSDP Step 7

• Implementing Frequency Constraints
• Implementing Ring Constraints
• Applying CSDP Step 7
• Lab: Implementing Frequency and Ring Constraints

• Complete CSDP step 7.
  
• Implement frequency constraints.
  
• Implement ring constraints.
  

Module 9:Generating a Relational Logical Model

• Understanding Relational Logical Models
• Understanding Normalization
• Generating a Relational Logical Model
• Lab: Generating a Relational Logical Model

• Generate a relational logical model.
  
• Describe a relational logical model.
  
• Describe normalization.
  

Module 10:Completing the Baseline Model

• Refining Conceptual and Logical Models
• Documenting Conceptual and Logical Models
• Lab: Completing the Baseline Model

• Complete a baseline model.
  
• Refine conceptual and logical models.
  
• Document conceptual and logical models.
  

Module 11:Generating and Reverse Engineering Physical Schema

• Forward Engineering
• Reverse Engineering
• Lab: Forward and Reverse Engineering Physical Schema

• Transfer a database model to and from SQL Server 2000.
  
• Generate a database from a relational logical model.
  
• Update a database from a modified relational logical model.
  
• Reverse engineer a database into a relational logical model.