Relational Database Design

This course explains how to design a relational database using database design models and principles. Participants will learn how to refine an initial database design through various concepts. The course also discusses ways to reduce data redundancy and utilize logical design methods to “tune up” designs.

Familiarity with the concepts and practices of logical data modeling as taught in the Logical Data Modeling course is required. A basic understanding of SQL is desirable but not necessary.

On completion of this course, the student should be able to

• Describe the concepts of good relational database design and its benefits
• Recognize the roles involved in database design
• Refine initial database table design
• Perform advanced relational database design

2 Days

Instructor-led course, with practical written exercises.

• Introduction
  • Database design process
  • Logical data modeling vs. database design
  • Three data model levels
  • Roles and responsibilities
• Relational Databases
  • Entity-relationship LDM concepts
  • E-R models
  • Primary and foreign keys
  • Concurrency control
  • Security
  • Optimizer
  • Physical storage of tables
• Logical Database Design
  • Normalization
  • 1st through 5th normal form
  • Domains or data types
  • Detailed table design
  • When to denormalize?
  • Contrived columns or artificial keys
  • Redundant or derived tables
  • Data partitioning
  • Mapping supertype/subtype entities to tables
• Transactions vs. Decisions
  • Data warehouse design considerations
  • Dimensional data
  • Physical data warehouse design
• Physical Database Design
  • Indexes
  • Clustered vs. unclustered indexes
  • Index storage structures: B-tree and hash
  • Database sizing
  • Database-level options for security design
  • Integrity