Relational Database Design

Click for printer-friendly version
Summary
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.

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

Course Objectives
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
Length
2 Days

Format
Instructor-led course, with practical written exercises.

Course Outline
  • 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

Hardware and Software Requirements

Go to course catalogue

Oracle and Java are registered trademarks of Oracle and/or its affiliates. PL/SQL and SQL*Plus are trademarks or registered trademarks of Oracle Corporation. Microsoft is either a registered trademark or trademark of Microsoft Corporation in the United States and/or other countries. MVS is a registered trademark of the International Business Machines Corp. in the U.S. or other countries or both. Motif, OSF/1, UNIX, and the "X Device" are registered trademarks and IT DialTone and The Open Group are trademarks of The Open Group in the U.S. and other countries. All other trademarks are the property of their respective owners.