Database Design For Client/Server Applications

Available only for on-site training

Why This Course?

Having built a complete logical data model that captures business rules and requirements, it is critical to systematically convert it to a physical model and to a database schema that accurately enforces those rules in a client/server environment. Several design decisions are critical to this conversion process. Data administrators, database designers and programmers need to fully understand the different concepts related to physical database design to carry out the tasks of this process. This one-day course covers those concepts and uses automated tools to demonstrate the tasks of the process.

What will you learn?

  • The difference between data modeling and physical database design
  • The tasks necessary to convert a logical data model to a physical model and generate a database schema
  • How is database design for client/server applications different
  • Defining different physical properties for the server and the clients
  • The use of stored procedures and triggers to implement business rules

Course Outline

1. Preparing for Physical Data Base Design

  • Normalization
  • Data types and domains
  • Handling many-to-many relationships
  • Handling subtypes/supertypes
  • Referential integrity constraints

2. The Client/Server Environment

  • Evolution of computing environment
  • Client/Server environment definition
  • Physical and logical architectures
  • How C/S different from LAN
  • Server tools and client tools

3. Stored Procedures and Triggers

  • Why stored procedures?
  • Components of stored procedures
  • Stored procedures vs. triggers and performance issues
  • Stored procedures and triggers in ERwin

4. Defining Physical Storage Information

  • Types of physical storage objects
  • Defining physical storage objects and their parameters
  • Associating entities with physical storage objects

5. Defining Client Information

  • Display format
  • Validation rules
  • Default values
  • Other client-specific properties

6. Approach 1: Evolving the Logical Model to a Physical Model (ERwin/ERX)

  • Choosing your target server
  • Changing defaults for the target server
  • Assigning column properties
  • Specifying referential integrity for each relationship
  • Building indexes

7. Approach 2: Creating a physical model from the Logical Model (System Architect)

  • Creating physical ER vs. creating physical model

8. Schema Generation and Database Creation

  • What to generate?
  • Generating physical storage objects
  • Generating indexes
  • Generating triggers and stored procedures

9. Reverse Engineering and Synchronization

  • Reverse engineering and synchronizing physical storage objects
  • Reverse engineering indexes