Relational Database Front Matter - 2 Day, No SQL

Relational

Database

Design

and

Usage

Compiled and Presented

Thomas P. Sturm, Ph.D.

Graduate Programs in Software

Technical Seminar

The University of St. Thomas

St. Paul, Minnesota

No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, or translated into any language, without prior written permission of the author.

Microsoft, Microsoft File, and MS-DOS are registered trademarks of Microsoft Corporation

IBM, IBM-DOS, AS400, System R, SQL/DS, VM/CMS, DOS/VSE, DB2, MVS, MVS/370, MVS/XA, and QMF are registered trademarks of International Business Machines Corporation

pfs, pfs:File, and pfs:First Choice are registered trademarks of Software Publishing Corporation

IDMS is a registered trademark of Cullinet Corporation

Ingres, Vifred, Vigraph, OSL, and ABF are registered trademarks of ASK, Inc.

LISA is a registered trademark of Control Data Corporation

Oracle and SQL+ are registered trademarks of Oracle Corporation

MIDAS is a registered trademark of Pr1me Computer Corporation

Turbo C, Turbo C++, Borland C++, and Sidekick are registered trademarks of Borland International, Inc.

UNIX is a registered trademark of American Telephone and Telegraph Company

VAX, DEC, RdB, DBMS, VMS, and VAX C are registered trademarks of Digital Equipment Corporation

WordStar is a registered trademark of WordStar Corporation

DB Master, PC-File, System 2000, Focus, IMS, MDBS III, dbVista III are registered trademarks

Relational Database

Design and Usage

The goal of this seminar is to develop sound principles for determining the value of information, what data should he stored, how it should he organized, retrieved and managed to provide a manageably sized, responsive, user friendly, accurate, information producing relational database.

Objectives: By the end of the course, qualified and diligent participants should know:

The concepts of data and information and how data produces information

The identification procedure for entities and the procedure for determining their interrelationships

The relational database model and how it differs from other database models

How to construct logical data structures for modeling data

How to construct a relational database starting from a logical data structure

How to construct a relational database starting from an existing collection of data or existing “tables.”

The advantages and proper use of relational models

How to present users with an appropriate “view” of the data

During the seminar, participants will be given opportunity to:

Identify attributes, entities, values, and relationships

Use relational operators on a set of tables to produce information

Normalize an existing set of data into a set of well-formed relations

Construct a logical data structure

Map a logical data structure into a set of well-formed relations

Relational Database

Design and Usage

Course Schedule

Monday - Day 1 - ANALYSIS

Data Concepts

Introduction and justification

Concepts of data, information and database

The need for information-producing systems

Data base design goals

Definition of entity, attribute, value, and relationship

Lab l - Entity identification

Database Models

Case Study 1

Database advantages and need for a database approach

Data independence

Major non-relational database models

(flat file, indexed sequential, hierarchical, network)

Lab 2 - Structure identification

Relational Database Model:

Conceptual structure

Definition of a relation

Relational operators

Understanding relational terminology

Elimination of redundancy.

Lab 3 - Relational operators

Wednesday - Day 2: DESIGN

Modeling using Normalization:

Principles of logical database design

Various normal forms

(zeroth, first through fifth, projection-join)

Identification of keys and relationships

Normalizing existing forms and databases

Case study 2

Lab 4 - Normalizing an order form

Modeling using Logical Data Structures

LDS components

Relating entities, attributes, and relationships

Handling 1-1, 1-many, and many-many relationships

Modeling choices

Constraint modeling and enforcement

Mapping logical data structures to well-formed relations

Lab 5 LDS construction

Lab 6 mapping LDS to a relational database

Implementation:

Query optimization

Creating effective user views

Index creation

Designing read-only databases

Case study 3

Course Goals

Understand how data produces information

Identify entities and their interrelationships

Understand the relational model and how it differs from other models

Construct a logical data structure for modeling data

Construct a relational database from a logical data structure

Re-form an existing collection of data into relational form

Understand the advantages and proper use of relational models

Present users with an appropriate “view” of the data

Non-Goals

Will not learn the details of any particular database management system

Will not receive extensive product evaluations

Participant Introductions

1. Name

2. Company and Department

3. Data base systems used

(or planned to be used)

4. Data base projects you are working with

(or plan to be working with)

Table of Contents

1. Data Concepts

2. Data Models

3. Relational Model

4. Normalization

5. Logical Data Structures

6. Implementation

7. Exercises

8. References