Relational Database Design, Tools and Techniques

COURSE OUTLINE:

Description Relational databases often drive company-critical and web-enabled applications; therefore, creating a database design that accurately captures user requirements is vital for success. This course provides a foundation for designing, building and working with relational databases, enabling you to effectively develop and use relational databases in your environment.

Learning Objectives

  • Design, build and use a relational database using proven methods
  • Ensure database designs accurately model business requirements
  • Normalize and denormalize your data to optimize performance
  • Work with integrity constraints
  • Automatically generate database documentation using CASE tools
  • Simplify code and improve performance by avoiding common errors

Introduction

An overview of DBMS technology

  • How data is accessed, organized and stored
  • The database development process

DBMS and related user tools

  • Query and application development tools
  • CASE tools for database analysis and design

How a Relational DBMS Works

Relational technology fundamentals

  • Tables, attributes and relationships
  • Primary and foreign keys
  • Relational integrity constraints
  • Manipulating data: selection, projection, join, union, intersection, difference

Components of a relational DBMS

  • An integrated, active data dictionary
  • The query optimizer

Designing Relational Databases

A step-by-step approach and techniques

  • Developing the logical data model
  • Mapping the data model to the relational model
  • Specifying integrity constraints
  • Defining the data in the data dictionary

Entity-relationship modeling

  • Capturing entities, attributes and identifiers
  • Describing relationships: one-to-one, one-to-many, many-to-many
  • Optional and mandatory relationships
  • Resolving many-to-many relationships for implementation

Normalizing data to design tables

  • Avoiding update anomalies
  • Identifying functional dependencies
  • Applying rules for normalization

Working with a CASE tool

  • Generating the SQL to build the database
  • Reverse engineering to capture the design of an existing database

Physical database design

  • Assigning tables to disk files for performance and maintenance
  • Fragmenting large tables
  • Planned denormalization vs. accidental denormalization
  • Indexing for performance and integrity

Building and Querying a Relational Database

Fundamentals of SQL

  • A dynamic and evolving language
  • ANSI and ISO standards

Creating a relational database

  • Defining the database and its objects: tables, keys, views and indexes
  • Declaring integrity constraints

Accessing the database with SQL

  • Querying the database to retrieve desired information
  • Updating data while maintaining database integrity

Constructing and using views

  • Defining views for simplicity and security
  • Customizing windows into the database
  • Querying and updating through views

The Future of Database Design

Enforcing business rules for data integrity

  • Defining declarative constraints
  • Server-side programming in a procedural language

Trends

  • Modeling in analysis and design
  • Focusing on business rules
  • Creating an intelligent server