RelationalDatabaseDesignCompiled 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, AS/400, 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 2Relational DatabaseDesignThe 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 interrelationshipsHow 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.”During 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 3Relational Database DesignCourse ScheduleData ConceptsIntroduction and justificationConcepts of data, information and databaseThe need for information-producing systemsData base design goalsDefinition of entity, attribute, value, and relationshipLab l - Entity 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 4Relational 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 study 2Lab 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 databaseCopyright © 1971-2002 Thomas P. Sturm Relational Database Design 5Course Goals· Understand how data produces information· Identify entities and their interrelationships· Construct a logical data structure for modeling data· Construct a relational database starting from a logical data structure· Re-form an existing collection of data into relational formNon-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 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 7Table of Contents1. Data Concepts2. Relational Model3. Relational Design Concepts4. Normalization5. Logical Data Structures6. Exercises7. ReferencesCopyright © 1971-2002 Thomas P. Sturm Relational Database Design