Description
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.

Audience
Systems analysts/designers and application developers.

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

Format

• Presentation
  
• Written Exercises
  

Objectives
After completing this course, participants 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
• Develop a first-cut data warehouse design

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