Relational Database Design and Usage Compiled and Presented by Thomas P. Sturm, Ph.D. Graduate Programs in Software Technical Seminar The University of St. Thomas St. Paul, MinnesotaCopyright © 1971-2002 Thomas P. Sturm Relational Database Design and Usage 2 © Copyright 1971 to 2002 Thomas P. Sturm All rights reserved. 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 trademarksCopyright © 1971-2002 Thomas P. Sturm Relational Database Design and Usage 3 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 relationsCopyright © 1971-2002 Thomas P. Sturm Relational Database Design and Usage 4 Relational Database Design and Usage Course Schedule 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 Concepts Case Study 1 Database advantages and need for a database approach Data independence Data Models 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 operatorsCopyright © 1971-2002 Thomas P. Sturm Relational Database Design and Usage 5 Day 2: DESIGN Relational Design Concepts: Principles of logical database design Modeling using Normalization: Various normal forms (zeroth, first through fifth, projection-join) Identification of keys and relationships Normalizing existing forms and databases Case studies 2 and 3 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 4Copyright © 1971-2002 Thomas P. Sturm Relational Database Design and Usage 6 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 evaluationsCopyright © 1971-2002 Thomas P. Sturm Relational Database Design and Usage 7 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)Copyright © 1971-2002 Thomas P. Sturm Relational Database Design and Usage 8 Table of Contents 1. Data Concepts 2. Database Concepts 3. Data Models 4. Relational Model 5. Relational Design Concepts 6. Normalization 7. Logical Data Structures 8. Implementation 9. Exercises 10.