Database Systems Database Systems A Pragmatic Approach Third Edition Elvis C. Foster with Shripad V. Godbole Third Edition published 2023 by CRC Press 6000 Broken Sound Parkway NW, Suite 300, Boca Raton, FL 33487-2742 and by CRC Press 2 Park Square, Milton Park, Abingdon, Oxon, OX14 4RN © 2023 Taylor & Francis Group, LLC First Edition published 2014 by Apress Second Edition published 2016 by Apress CRC Press is an imprint of Taylor & Francis Group, LLC The right of Elvis C. Foster to be identified as author of this book has been asserted by him in accordance with sections 77 and 78 of the Copyright, Designs and Patents Act 1988.
Reasonable efforts have been made to publish reliable data and information, but the author and publisher cannot assume responsibility for the validity of all materials or the consequences of their use. The authors and publishers have attempted to trace the copyright hold- ers of all material reproduced in this publication and apologize to copyright holders if permission to publish in this form has not been obtained. If any copyright material has not been acknowledged please write and let us know so we may rectify in any future reprint. Except as permitted under U.
Copyright Law, no part of this book may be reprinted, reproduced, transmitted, or utilized in any form by any electronic, mechanical, or other means, now known or hereafter invented, including photocopying, microfilming, and recording, or in any information storage or retrieval system, without written permission from the publishers. For permission to photocopy or use material electronically from this work, access www.com or contact the Copyright Clearance Center, Inc. (CCC), 222 Rosewood Drive, Danvers, MA 01923, 978-750-8400. For works that are not available on CCC please contact mpkbookspermissions@tandf.uk Trademark notice: Product or corporate names may be trademarks or registered trademarks and are used only for identification and explanation without intent to infringe.1201/9781003275725 Typeset in Garamond by Deanta Global Publishing Services, Chennai, India Contents Preface.xxv Section A PRELIMINARY TOPICS 1 Introduction to Database Systems.1 Definitions and Rationale.2 Objectives of a Database System.1 Primary and Secondary Objectives.2 Clarification on Data Independence.3 Advantages of a Database System.4 Approaches to Database Design.5 Desirable Features of a DBS.6 Database Development Life Cycle.7 Summary and Concluding Remarks.
12 References and/or Recommended Readings. 12 2 The Database System Environment.1 Levels of Architecture.2 Inter-Level Mappings.1 Database Administrator and Data Architect.4 User Liaison Specialist.5 Network and Infrastructure Specialist.4 The Database Management System.5 Components of DBMS Suite.1 The DBMS Engine.2 Data Definition Subsystem.3 The User Interface Subsystem.4 Application Development Subsystem.5 Data Administration Subsystem.6 Data Dictionary Subsystem.7 Data Communications Manager.6 Front-End and Back-End Perspectives.7 Database System Architecture.8 Database Management System Classifications.1 Classification Based on Data Models.2 Classification Based on Number of Users.3 Classification Based on Site Configuration.4 Classification Based on Database Purpose.9 Summary and Concluding Remarks.31 References and/or Recommended Readings.31 Section B THE RELATIONAL DATABASE MODEL 3 Introducing the Relational Model.1 Properties of a Relation.2 Kinds of Relations.4 Relational Database System.1 The Entity–Relationship Model.2 The Object–Relationship Model.3 Summary of Traditional Models.4 Multiplicity of Relationships.8 Innovation: The Relation–Attributes List and Relationship List.9 Database Naming Convention.10 Summary and Concluding Remarks.67 References and Recommended Readings.67 4 Integrity Rules and Normalization.1 Fundamental Integrity Rules.2 Foreign Key Concept.3 Rationale for Normalization.4 Functional Dependence and Non-Loss Decomposition.2 Non-Loss Decomposition.1 Corollary from Heath’s Theorem.2 Conclusion from Heath’s Theorem and Its Corollary.5 First Normal Form.6 Second Normal Form.7 Third Normal Form.8 Boyce–Codd Normal Form.9 Fourth Normal Form.1 Multi-Valued Dependency.3 The Zoo Revisited.10 Fifth Normal Form.1 Definition of Join Dependency.12 Other Normal Forms.1 The Domain-Key Normal Form.2 The Sixth Normal Form.13 Summary and Concluding Remarks. 97 References and Recommended Readings. 98 5 Database Modeling and Design.1 Database Model and Database Design.2 The E–R Model Revisited.3 Database Design via the E–R Model.4 The Extended Relational Model.3 E-Relations and P-Relations.5 Database Design via the XR Model.1 Determining the Kernel Entities.2 Determining the Characteristic Entities.3 Determining the Designative Entities.4 Determining the Associations.5 Determining Entity Subtypes and Super-Types.6 Determining Component Entities.7 Determining Additional Properties.8 Additional Application of the XR Model.6 The UML Model.7 Database Design via the UML Model.8 Innovation: The Object/Entity Specification Grid.9 Database Design via Normalization Theory.1 Example: Mountaineering Problem.2 Determining Candidate Keys and Then Normalizing.10 Database Model and Design Tools.11 Summary and Concluding Remarks.132 References and/or Recommended Readings.133 6 Database User Interface Design.2 Deciding on User Interface.3 Steps in User Interface Design.1 Menu or Graphical User Interface.2 Command-Based User Interface.4 User Interface Development and Implementation.5 Summary and Concluding Remarks.148 References and/or Recommend Readings.2 Basic Operations of Relational Algebra.1 Primary and Secondary Operations.2 Codd’s Original Classification of Operations.3 Syntax of Relational Algebra.5 Natural Join Statement.8 Inner and Outer Joins.9 UNION, INTERESECT, and MINUS Statements.4 Aliases, Renaming, and the Relational Assignment.1 The Alias Operation.2 The Assignment Operation.3 The Rename Operation.6 Summary and Concluding Remarks.173 References and Recommended Readings.2 Calculus Notations and Illustrations.3 Quantifiers: Free and Bound Variables.1 Well-Formed Formula.2 Free and Bound Variables.4 Substitution Rule and Standardization Rules.5 Introductory Query Optimization.6 Domain-Oriented Relational Calculus.7 Summary and Concluding Remarks.188 References and/or Recommended Readings.188 9 Reflective Look at the Relational Database Model.1 The Relational Model Summarized.2 Ramifications of the Relational Model.1 Codd’s Early Benchmark.2 Revised Definition of a Relational System.1 Codd’s Zero-Rule.3 Far-Reaching Consequences.3 Summary and Concluding Remarks.196 Section C THE STRUCTURED QUERY LANGUAGE 10 Overview of SQL.1 Commonly Used DDL Statements.2 Commonly Used DML and DCL Statements.2 Advantages of SQL.3 Summary and Concluding Remarks.
203 11 SQL Data Definition Statements.1 Overview of Oracle’s SQL Environment.2 Basic Concepts in a Typical Oracle Database Environment.7 Oracle Cloud Features.1 Creating and Using a Database Instance via OAC.2 Accessing the OAC Database via SQL Developer.3 Accessing the OAC Database via Application Express.9 Dropping or Modifying a Table.10 Working with Indexes.11 Working with Sequences.1 Creating and Using Sequences.2 Altering and Dropping Sequences.12 Working with Synonyms.13 Summary and Concluding Remarks. 238 References and Recommended Readings. 239 12 SQL Data Manipulation Statements.1 Insertion of Data.3 Deletion of Data.4 Commit and Rollback Operations.5 Basic Syntax for Queries.7 Queries Involving Multiple Tables.1 The Traditional Method.2 The ANSI Method.8 Queries Involving the Use of Functions.3 Data Conversion Functions.4 Programmer-Defined Functions.9 Queries Using the LIKE and BETWEEN Operators.1 Nested Queries Involving Use of the IN Operator.2 Nested Queries Involving Use of ANY or ALL Operator.3 Nested Queries Using the Existential Quantifier.11 Queries Involving Set Operation.12 Queries with Runtime Variables.13 Queries Involving SQL*Plus Format Commands.16 Summary and Concluding Remarks. 292 References and/or Recommended Readings.
292 13 Logical Views and System Security.1 Traditional Logical Views.2 View Modification and Removal.3 Usefulness and Manipulation of Logical Views.1 Access to the System.2 Access to System Resources.3 Managing Access to System Resources via Development Privileges and Roles.4 Access to System Data.1 Security via Object Privileges.2 Security via Views.3 Security via Database Design.1 Creating a Materialized View.2 Altering or Dropping a Materialized View.4 Summary and Concluding Remarks.311 References and Recommended Readings.312 14 The System Catalog.2 Three Important Catalog Tables.1 The User_Tables View.2 The User_Tab_Columns View.3 The User_Indexes View.3 Other Important Catalog Tables.4 Querying the System Catalog.5 Updating the System Catalog.6 Summary and Concluding Remarks. 326 References and/or Recommended Readings. 326 15 Some Limitations of SQL.2 Limitations on Views.1 Restriction on Use of the ORDER-BY-Clause for Earlier Versions of SQL.2 Restriction on Data Manipulation for Views Involving UNION, INTERSECT, or JOIN.3 Restriction on Use of Aggregation Functions for Earlier Versions of SQL.3 Stringent Enforcement of Referential Integrity.4 Limitations on Calculated Columns.5 If-Then Limitation.6 Summary and Concluding Remarks. 334 Section D ADVANCED TOPICS 16 Database Administration.1 Database Installation, Creation, and Configuration.4 Database Backup and Recovery.1 Oracle Backups: Basic Concept.2 Oracle Recovery: Basic Concept.3 Types of Failures.5 Basic Recovery Steps.6 Oracle’s Backup and Recovery Solutions.7 Summary and Concluding Remarks.
347 References and/or Recommended Readings. 347 17 Distributed Database Systems.2 Advantages of Distributed Database Systems.3 Twelve Rules for Distributed Database Systems.4 Challenges to Distributed Database Systems.6 The Future of Distributed Database Systems.2 Electronic Communication Systems.7 Summary and Concluding Remarks.358 References and/or Recommended Readings.2 Overview of Object-Oriented Database Management Systems.3 Challenges for Object-Oriented Database Management Systems.5 Summary and Concluding Remarks. 366 References and/or Recommended Readings.2 Acquiring a Data Warehouse.2 Rationale for Data Warehousing.3 Characteristics of a Data Warehouse.2 Nature of Data Stored.4 Twelve Rules that Govern a Data Warehouse.4 Data Warehouse Architecture.1 Basic Data Warehouse Architecture.2 Data Warehouse Architecture with a Staging Area.3 Data Warehouse Architecture with a Staging Area and Data Marts.5 Extraction, Transformation, and Loading.1 What Happens During the ETL Process.2 IBM Db2 Tools.3 Daily Operations and Expansion of the Data Warehouse.6 Summary and Concluding Remarks.379 References and/or Recommended Readings.379 20 Web-Accessible Databases.2 Web-Accessible Database Architecture.4 Implementation with Selected DBMS Suites.1 Implementation in Oracle.2 Implementation with DB2.3 Implementation in MySQL.5 Generic Implementation via Front-End and Back-End Tools.6 Challenges and Opportunities.1 Cloud Computing and Big Data.7 Summary and Concluding Remarks. 390 References and/or Recommended Readings.
390 21 Using Database Systems to Anchor Management Support Systems.1 Overview of Management Support Systems.2 Building System Security through Database Design.3 Case Study: Dynamic Menu Interface Designer.1 Database Requirements of the DMID.2 Overview of the DMID’s User Interface Requirements.3 Management of System Constraints via the DMID.4 Access to System Resources.5 Usefulness and Applicability of the DMID.4 Selected MSS Project Ideas.1 Electoral Management System.2 Health Information Management System.3 Strategic Education Management System.4 Flight Information Management System.5 Financial Information Management System.6 Disease Recognition System.7 Cognitive Leadership Analysis System.8 Financial Status Assessment System.9 College/University Administrative Information System.5 Summary and Concluding Remarks. 420 References and/or Recommended Readings. 420 Section E OVERVIEW OF SELECTED DBMS SUITES AND TECHNOLOGIES 22 Overview of Oracle.2 Main Components of the Oracle Suite.2 Oracle PL/SQL and SQL*Plus.3 Oracle Developer Suite.4 Oracle Enterprise Manager Database Control and SQL Developer.5 Oracle Enterprise Manager Grid Control.6 Oracle Database Configuration Assistant.7 Oracle Data Integrator.3 Shortcomings of Oracle.4 Summary and Concluding Remarks. 433 References and Recommended Readings.
433 23 Overview of Db2.2 Main Components of the Db2 Suite.1 IBM Data Management Console.2 IBM Advanced Recovery Feature.3 IBM Db2 Connect.4 IBM Data Studio.5 IBM InfoSphere Data Architect.6 IBM Data Warehousing Products.3 Shortcomings of Db2.4 Summary and Concluding Remarks. 442 References and/or Recommended Readings. 442 24 Overview of MySQL.1 Introduction to MySQL.2 Main Features of MySQL.3 Main Components of MySQL.4 Alternate Storage Engines.5 MySQL Database Service.6 Shortcomings of MySQL.1 Limitations on Logical Views.2 Limitations on Subqueries.3 Limitation on Server-Side Cursors.7 Summary and Concluding Remarks.452 References and Recommended Readings.452 25 Overview of Microsoft SQL Server.3 Microsoft Server Editions.2 Main Features of Microsoft SQL Server.3 Main Components of Microsoft SQL Server Suite.4 Shortcomings of Microsoft SQL Server.5 Summary and Concluding Remarks. 460 References and/or Recommended Readings.461 26 Other Emerging Database Methodologies.2 Entity–Attributes–Value Model.1 Rationale for the EAV Model.2 Challenges of the EAV Model.3 Illustrating Application of the EAV Model.3 Database-Supporting Frameworks.1 NoSQL Databases versus SQL Databases.2 Emerging NoSQL Data Models.3 Examples of NoSQL Databases.5 Other Burgeoning Trends.6 Summary and Concluding Remarks.470 References and/or Recommended Readings.470 Section F APPENDICES Appendix 1: Review of Trees.473 Appendix 2: Review of Hashing.501 xvi ◾ Contents Appendix 3: Review of Information-Gathering Techniques.
519 Appendix 4: BNF Syntax for Selected SQL Statements.537 Appendix 5: Sample Exercises and Examination Questions.589 Preface This book has been compiled with three target groups in mind: The book is best suited for under- graduate students of computer science (CS) or a related discipline who are pursuing a course in database systems. Graduate students who are pursuing an introductory course in the subject may also find it useful. Finally, practicing software engineers and/or information technology (IT) pro- fessionals who need a quick reference on database design may find it useful. The motivation that drove this work was a desire to provide a concise but comprehensive guide to the discipline of database design, construction, implementation, and management.
Having worked in the software engineering and IT industries for several years before making a career switch to academia, it has been my observation that many IT professionals and software engineers tend to pay little attention to their database design skills; this is often reflected in the prolifera- tion of software applications with inadequately designed underlying databases.