RelationalDatabaseDesignandUsageCompiled and PresentedbyThomas P. Sturm, Ph.D.Graduate Programs in SoftwareTechnical SeminarThe University of St. ThomasSt. Paul, Minnesota© Copyright 1971 to 2002 Thomas P. SturmAll rights reserved.No part of this publication may be reproduced, stored in a retrieval system, ortransmitted 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 CorporationIBM, 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 Corporationpfs, pfs:File, and pfs:First Choice are registered trademarks of Software Publishing CorporationIDMS is a registered trademark of Cullinet CorporationIngres, Vifred, Vigraph, OSL, and ABF are registered trademarks of ASK, Inc.LISA is a registered trademark of Control Data CorporationOracle and SQL+ are registered trademarks of Oracle CorporationMIDAS is a registered trademark of Pr1me Computer CorporationTurbo C, Turbo C++, Borland C++, and Sidekick are registered trademarks ofBorland International, Inc.UNIX is a registered trademark of American Telephone and Telegraph CompanyVAX, DEC, RdB, DBMS, VMS, and VAX C are registered trademarks of Digital Equipment CorporationWordStar is a registered trademark of WordStar CorporationDB Master, PC-File, System 2000, Focus, IMS, MDBS III, dbVista III are registered trademarksCopyright © 1971-2002 Thomas P. Sturm Relational Database Design and Usage 2Relational DatabaseDesign and UsageThe 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 informationThe identification procedure for entities and the procedure for determining their interrelationshipsThe relational database model and how it differs from other database modelsHow to construct logical data structures for modeling dataHow to construct a relational database starting from a logical data structureHow to construct a relational database starting from an existing collection of data or existing “tables.”The advantages and proper use of relational modelsHow to present users with an appropriate “view” of the dataDuring the seminar, participants will be given opportunity to:Identify attributes, entities, values, and relationshipsUse relational operators on a set of tables to produce informationNormalize an existing set of data into a set of well-formed relationsConstruct a logical data structureMap a logical data structure into a set of well-formed relationsCopyright © 1971-2002 Thomas P. Sturm Relational Database Design and Usage 3Relational DatabaseDesign and UsageCourse ScheduleDay 1 - ANALYSISData ConceptsIntroduction and justificationConcepts of data, information and databaseThe need for information-producing systemsData base design goalsDefinition of entity, attribute, value, and relationshipLab l - Entity identificationDatabase ConceptsCase Study 1Database advantages and need for a database approachData independenceData ModelsMajor non-relational database models(flat file, indexed sequential, hierarchical, network)Lab 2 - Structure identificationRelational Database Model:Conceptual structureDefinition of a relationRelational operatorsUnderstanding relational terminologyElimination of redundancy.Lab 3 - Relational operatorsCopyright © 1971-2002 Thomas P. Sturm Relational Database Design and Usage 4Day 2: DESIGNRelational Design Concepts:Principles of logical database designModeling using Normalization:Various normal forms(zeroth, first through fifth, projection-join)Identification of keys and relationshipsNormalizing existing forms and databasesCase studies 2 and 3Lab 4 - Normalizing an order formModeling using Logical Data StructuresLDS componentsRelating entities, attributes, and relationshipsHandling 1-1, 1-many, and many-many relationshipsModeling choicesConstraint modeling and enforcementMapping logical data structures to well-formed relationsLab 5 LDS constructionLab 6 mapping LDS to a relational databaseImplementation:Query optimizationCreating effective user viewsIndex creationDesigning read-only databasesCase study 4Copyright © 1971-2002 Thomas P. Sturm Relational Database Design and Usage 5Course 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 dataNon-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 6Participant Introductions1. Name2. Company and Department3. 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 7Table of Contents1. Data Concepts2. Database Concepts3. Data Models4. Relational Model5. Relational Design Concepts6. Normalization7. Logical Data Structures8. Implementation9. Exercises10. ReferencesCopyright © 1971-2002 Thomas P. Sturm Relational Database Design and Usage