RelationalDatabaseDesign,Usage,andImplementationusing OracleCompiled and PresentedbyThomas P. Sturm, Ph.D.Quantitative Methods and Computer ScienceQMCS 450 - Database DesignThe 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, MS-DOS, MS-Windows, Windows, Windows NT are registered trademarks of Microsoft CorporationIBM, IBM-DOS, AS/400, AIX, OS/2, 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 Computer AssociatesLISA is a registered trademark of Control Data CorporationOracle, SQL+, Designer 2000, Developer 2000, and PL/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 UNIX System Laboratories, Inc.VAX, Alpha, Alpha AXP, ACMS, ALL-IN-1, DEC, DECmessageQ, DECnet, DECserver, Digital, OpenVMS, RMS, Ultrix, 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 trademarks of their respective owners.Copyright © 1971-2002 Thomas P. Sturm Relational Database Design, Usage, and Implementation 2Relational Database Design, Usage,and Implementation using OracleThe goal of this course 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 student 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 write simple queries in SQL that create, update, and retrieve data in a relational databaseHow to present users with an appropriate “view” of the dataThe criteria for determining to what extent commercial database management systems are truly “relational.”How to tune a relational system for efficiencyDuring the course, students 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 relationsWrite SQL statements to query and update a databaseBuild an application using a fourth-generation development toolCopyright © 1971-2002 Thomas P. Sturm Relational Database Design, Usage, and Implementation 3Relational Database Design, Usage,and Implementation using OracleCourse ScheduleANALYSISData ConceptsIntroduction and justificationConcepts of data, information and databaseThe need for information-producing systemsData base design goalsDefinition of entity, attribute, value, and relationshipDatabase ConceptsCase Study 1Database advantages and need for a database approachData independenceData ModelsMajor non-relational database models(flat file, indexed sequential, hierarchical, network)Relational Database Model:Conceptual structureDefinition of a relationRelational operatorsUnderstanding relational terminologyElimination of redundancy.Copyright © 1971-2002 Thomas P. Sturm Relational Database Design, Usage, and Implementation 4DESIGNModeling using Normalization:Principles of logical database designVarious normal forms(zeroth, first through fifth, projection-join)Identification of keys and relationshipsNormalizing existing forms and databasesCase study 2Modeling using Logical Data StructuresLDS componentsRelating entities, attributes, and relationshipsHandling 1-1, 1-many, and many-many relationshipsModeling choicesConstraint modeling and enforcementMapping an LDS to well-formed relationsModeling using Entity-Relationship DiagramsE-R componentsRelating entities, attributes, and relationshipsHandling 1-1, 1-many, and many-many relationshipsModeling choicesConstraint modeling and enforcementMapping E-R diagrams to well-formed relationsCopyright © 1971-2002 Thomas P. Sturm Relational Database Design, Usage, and Implementation 5CONSTRUCTIONSQL:ANSI standard query languageTable creation, update, and query operationsTable query for listing and summariesPerforming table joinsCreating end-user views.Oracle:Data definitionBack-endCommunicationsSQL*PlusForm systemPL/SQL and Embedded queriesAdvanced SQL:Field-level integrityTable constraints and referential integritySQL for Oracle System Tables:Listing of all tables, views, and fieldsAccess to all database attributesOracle SQL Plus:Screen report formattingSQL buffer/editorCopyright © 1971-2002 Thomas P. Sturm Relational Database Design, Usage, and Implementation 6IMPLEMENTATIONOracle PL/SQL:Programming with SQLVariables, blocks, loopsFourth Generation Languages:Application generationReport generationData entry and screen paintingOracle Developer 2000Implementation:Query optimizationCreating effective user viewsIndex creationDesigning read-only databasesCase study 3Relational Database Criteria:Measuring the ability to perform relational operationsConformance to established relational database criteriaLevels of relational conformanceCodd's 12 rulesAdvanced Topics:Data warehousingData miningObject-oriented databasesSQL 3 and object-relational