Relational Database Design, Tools and Techniques: Hands-On

Introduction

An overview of DBMS technology

• Key concepts and terminology
• How data is accessed, organised and stored
• The importance of business rules
• Uses of databases
• The database development process

DBMS and related user tools

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

How a Relational DBMS Works

Relational technology fundamentals

• The structure of a relational database
• 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 optimiser
• An engine that manages the data
• Front-end tools for easy user access

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 modelling

• 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
• Guidelines for a well-formed E-R diagram

Normalising data to design tables

• Why/why not normalise
• Avoiding update anomalies
• Identifying functional dependencies
• Applying rules for normalisation

Working with a CASE tool

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

Physical database design

• Grouping and assigning tables to disc files for performance and maintenance
• Fragmenting large tables
• Planned denormalisation vs. accidental denormalisation
• 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
• Altering structures and constraints

Accessing the database with SQL

• Querying the database to retrieve exactly the desired information
• Joining tables to retrieve related data
• Updating data while maintaining database integrity

Constructing and using views

• Defining views for simplicity and security
• Customising 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 Java or a procedural language

Trends

• Modelling in analysis and design
• Focusing on business rules
• Creating an intelligent server
• Using stored procedures and triggers