TY - BOOK AU - Ricardo,Catherine M. TI - Databases illuminated / SN - 0763733148 (hardcover) U1 - 005.74 PY - 2004/// CY - Sudbury, Mass. PB - Jones and Bartlett Publishers KW - Database management N1 - Includes bibliographical references (p. [847]-854) and index; Table of Contents 1. Introductory Database Concepts 1.0 Chapter Objectives 1.1 Databases in Everyday Life 1.2 A Sample Database 1.3 The Integrated Database Environment 1.4 Roles in the Integrated Database Environment 1.5 Advantages of the Integrated Database Approach 1.6 Disadvantages of the Integrated Database Approach 1.7 Historical Developments in Information Systems 1.8 Chapter Summary Exercises Lab Exercises: Exploring the Access Database for the University Example Creating and using a Simple Access Database SAMPLE PROJECT: Introduction to the Problem: The Art Gallery Sample Project Steps 1.1-1.4 STUDENT PROJECTS: Introduction to Student Projects Student Project Steps 1.1-1.4 Project One: Beta University Annual Fund Project Two: Friendly Cars Dealership Project Three: Pleasantville Community Theater Group Project Four: Images Photography Studio Project Five: Wellness Clinic - Medical Group 2. Database Planning and Database Architecture 2.0 Chapter Objectives 2.1 Data as a Resource 2.2 Characteristics of Data 2.2.1 Data and Information 2.2.2 Levels of Data 2.3 Stages in Database Design 2.3.1 Systems Analysis Approach 2.3.2 Staged Database Design Approach 2.4 Design Tools 2.4.1 Data Dictionary 2.4.2 Project Management Software 2.5 Database Administration 2.5.1 Planning and Design 2.5.2 Developing the Database 2.5.3 Database Management 2.6 The Three Level Architecture 2.6.1 External Views 2.6.2 Conceptual Model 2.6.3 Internal Model 2.6.4 Data Independence 2.7 Overview of Conceptual Data Models 2.7.1 Entity-Relationship Model 2.7.2 Relational Model 2.7.3 Object-Oriented Model 2.7.4 Object-Relational Model 2.7.5 Semistrucured Data Model 2.8 Chapter Summary Exercises Lab Exercises: Using a Diagramming Tool Using a Project Management Tool SAMPLE PROJECT: Applying Planning Techniques to The Art Gallery Project Step 2.1: Designing the Data Dictionary for The Art Gallery Step 2.2: Modifying the List of Assumptions Step 2.3: Writing a Cross-Reference Table Step 2.4: Project Management Planning for The Art Gallery STUDENT PROJECTS: Applying Planning Techniques to the Student Project Step 2.1: Designing the Data Dictionary for the Student Project Step 2.2: Modifying the List of Assumptions Step 2.3: Writing a Cross-Reference Table Step 2.4: Project Management Planning for the Student Project 3. The Entity-Relationship Model 3.0 Chapter Objectives 3.1 Purpose of the E-R Model 3.2 Entities 3.3 Attributes 3.3.1 Domains 3.3.2 Null Values 3.3.3 Multivalued Attributes 3.3.4 Composite Attributes 3.3.5 Derived Attributes 3.4 Keys 3.4.1 Superkeys 3.4.2 Candidate Keys 3.4.3 Primary Keys 3.5 Relationships 3.5.1 Types of Relationships 3.5.2 Attributes of Relationship Sets 3.5.3 Cardinality of a Relationship 3.5.4 Showing Cardinality on an E-R Diagram 3.5.5 Participation Constraints 3.6 Roles 3.7 Existence Dependency and Weak Entities 3.8 A Sample E-R Diagram 3.9 Chapter Summary Exercises Lab Exercises: Drawing E-R Diagrams SAMPLE PROJECT: Step 3.1-3.3: Creating the E-R Diagram for The Art Gallery Step 3.4-3.5: Updating the Data Dictionary and Assumptions STUDENT PROJECTS: Step 3.1-3.3: Creating the E-R Diagram for the Student Project Step 3.4-3.5: Updating the Data Dictionary and Assumptions 4. The Relational Model 4.0 Chapter Objectives 4.1 Brief History of the Relational Model and SQL 4.2 Advantages of the Relational Model 4.3 Relational Data Structures 4.3.1 Tables 4.3.2 Mathematical Relations 4.3.3 Database Relations and Tables 4.3.4 Properties of Relations 4.3.5 Degree and Cardinality 4.3.6 Relation Keys 4.4 Integrity Constraints: domain, key, foreign key, general constraints 4.5 Representing Relational Database Schemas 4.6 Relational Data Manipulation Languages 4.6.1 Categories of DMLs 4.6.2 Relational Algebra: SELECT, PROJECT, Products and Joins, Division, Set Operations 4.6.3 Relational Calculus: Tuple-oriented, Domain-oriented 4.7 Views 4.8 Mapping an E-R Model to a Relational Model 4.9 Codd's Rules for a Relational Database Management System 4.10 Chapter Summary Exercises SAMPLE PROJECT: Step 4.1: Initial Mapping of the E-R Model to Tables for The Art Gallery STUDENT PROJECTS: Step 4.1: Initial Mapping to Tables for the Student Project 5. Normalization 5.0 Chapter Objectives 5.1 Objectives of Normalization 5.2 Insertion, Update, and Deletion Anomalies 5.3 Functional Dependency 5.4 Superkeys, Candidate Keys and Primary Keys 5.5 The Normalization Process Using Primary Keys 5.5.1 First Normal Form 5.5.2 Full Functional Dependency and Second Normal Form 5.5.3 Transitive Dependency and Third Normal Form 5.5.4 Boyce-Codd Normal From 5.5.5 Comprehensive Example of Functional Dependencies 5.6 Properties of Relational Decompositions 5.6.1 Attribute Preservation 5.6.2 Dependency Preservation 5.6.3 Lossless Decomposition 5.7 Formal Relational Design 5.7.1 Inference Rules: Armstrong's Axioms 5.7.2 Closure of a Set of Functional Dependencies 5.7.3 Closure of an Attibute 5.7.4 Identifying Redundant Functional Dependencies 5.7.5 Covers and Equivalent Sets of FDs 5.7.6 Minimal Set of Functional Dependencies 5.7.7 Finding a Minimal Cover for a Set of FDs 5.7.8 Decomposition Algorithm for Boyce-Codd Normal Form with Lossless Join 5.7.9 Synthesis Algorithm for Third Normal Form Decomposition 5.8 Multivalued Dependencies and Fourth Normal Form 5.9 Lossless Decomposition and Fifth Normal Form 5.10 Domain-Key Normal Form 5.11 The Normalization Process 5.11.1 Analysis 5.11.2 Synthesis 5.11.3 Normalizating from an Entity-Relationship Diagram 5.12 When to Stop Normalizing 5.13 Chapter Summary Exercises SAMPLE PROJECT: Step 5.1: Normalizing the Relational Model for The Art Gallery Step 5.2 Updating the Data Dictionary and the List of Assumptions STUDENT PROJECTS: Step 5.1: Normalizing the Relational Model for the Student Project Database Step 5.2 Updating the Data Dictionary and the List of Assumptions 6 Relational Database Management Systems and SQL 6.0 Chapter Objectives 6.1 Brief History of Relational Database Systems 6.2 Architecture of a Relational Database Management System 6.3 Defining the Database: SQL DDL 6.3.1 CREATE TABLE 6.3.1.1 Data Types 6.3.1.2 Column and Table Constraints 6.3.2 CREATE INDEX 6.3.3 ALTER TABLE, RENAME TABLE 6.3.4 DROP Statements 6.4 Manipulating the Database: SQL DML 6.4.1 Introduction to the SELECT Statement 6.4.2 SELECT Using Multiple Tables 6.4.3 SELECT with Other Operators 6.4.4 Operators for Updating: UPDATE, INSERT, DELETE 6.5 Active Databases 6.5.1 Enabling and Disabling Constraints 6.5.2 SQL Triggers 6.6 Using COMMIT and ROLLBACK Statements 6.7 SQL Programming 6.7.1 Embedded SQL 6.7.2 ODBC and JDBC 6.7.3 SQL PSMs 6.8 Creating and Using Views 6.9 The System Catalog 6.10 Chapter Summary Exercises Lab Exercises 1. Exploring the Oracle Database for University Example (provided on CD) 2. Creating and Using a Simple Database in Oracle SAMPLE PROJECT: Step 6.1: Creating a Relational Database for The Art Gallery Using Oracle Step 6.2: Querying and Updating the Oracle Database for The Art Gallery Step 6.3: Programming Using SQL for The Art Gallery STUDENT PROJECTS: Step 6.1: Creating a Relational Database for the Student Projects Using Oracle Step 6.2: Querying and Updating the Oracle Database for the Student Projects Step 6.3: Programming Using SQL for the Student Projects 7. The Enhanced Entity Relationship Model and Object-Relational Model 7.0 Chapter Objectives 7.1 Rationale for Extending the ER Model 7.2 Generalization and Specialization 7.2.1 Specialization 7.2.2 Generalization 7.2.3 Generalization Constraints - Disjointness, Completeness, Definition Method 7.2.4 Multiple Hierarchies and Inheritance 7.3 Union 7.4 Using (min..max) Notation for Cardinality and Participation 7.5 A Sample EER Diagram 7.6 Mapping the EER Model to the Relational Model 7.6.1 Summary of ER to Relational Mapping Concepts 7.6.2 Mapping EER Class Hierarchies to Relational Tables 7.6.3 Mapping Unions 7.7 Extending the Relational Model 7.7.1 New Fundamental Data Types 7.7.2 Collection Types 7.7.3 User-defined Data Types 7.7.4 Type Hierarchies 7.7.5 Reference Types 7.8 Converting an EER Diagram to an Object-Relational Database Model 7.9 Representing Objects in Oracle 7.10 Chapter Summary Exercises Lab Exercises: Creating an EER Diagram SAMPLE PROJECT: Sample Project Steps 7.1-7.5 Creating an EER Diagram for The Art Gallery Converting the EER Diagram to an Object-Relational Model Creating an Oracle Database Using Objects Querying the Object-Relational Oracle Database Updating the Data Dictionary and the List of Assumptions STUDENT PROJECTS: Student Project Steps 7.1-7.5 Creating an EER Diagram for the Student Project Converting the EER Diagram to an Object-Relational Model for the Student Project Creating an Oracle Database Using Objects for the Student Project Querying the Object-Relational Oracle Database Updating the Data Dictionary and the List of Assumptions 8. The Object-Oriented Model 8.0 Chapter Objectives 8.1 Rationale for the Object-Oriented Model 8.2 Object-Oriented Data Concepts 8.2.1 Objects 8.2.2 Classes 8.2.3 Class Hierarchies and Inheritance 8.2.4 Object Identity 8.3 Object-Oriented Data Modeling Using UML 8.4 The ODMG Model and ODL 8.4.1 Class Declarations 8.4.2 Extent 8.4.3 Attributes 8.4.4 Relationships 8.4.5 Methods 8.4.6 Classes and Inheritance 8.4.7 n-ary Relationships and M:M relationships with attributes 8.4.8 Keys 8.5 OQL 8.6 Developing an OO Database 8.7 Chapter Summary Exercises Lab Exercises Creating UML Diagrams using a diagramming tool SAMPLE PROJECT: Sample Project Steps 8.1-8.3 Creating a UML Diagram for The Art Gallery Converting the UML Diagram to an Object-Oriented Database Model STUDENT PROJECTS Steps 8.1-8.3 Creating a UML Diagram for the Student Project Converting the UML Diagram to an Object-Oriented Database Model 9. Introduction to Database Security 9.0 Chapter Objectives 9.1 Issues in Database Security 9.1.1 Accidental Security Threats 9.1.2 Deliberate Security Threats 9.2 Physical Security and User Authentication 9.3 Authorization 9.4 Access Control 9.5 Using Views for Access Control 9.6 Security Logs and Audit Trails 9.7 Encryption 9.8 SQL Authorization Language 9.9 Security in Oracle 9.9.1 Object Privileges 9.9.2 System Privileges 9.9.3 Roles 9.9.4 Using the Oracle Security Manager 9.9.5 Access Control for a Single Database 9.10 Statistical Database Security 9.10 Database Security and the Internet 9.11 Chapter Summary Exercises Lab Exercises: Exploring Oracle's Authorization System SAMPLE PROJECT: Implementing Security Features for The Art Gallery database STUDENT PROJECTS: Implementing Security Features for Student projects 10. Transaction Management 10.0 Chapter Objectives 10.1 Properties of Transactions 10.2 Need for Concurrency Control 10.3 Serializability 10.4 Locking 10.4.1 Deadlock 10.4.2 Two-Phase Locking 10.4.3 Levels of Locking 10.5 Timestamping 10.5.1 Basic Timestamping protocol 10.5.2 Thomas' Write Rule 10.5.3 Multiversion Timestamping 10.6 Validation Techniques 10.7 Need for Recovery 10.8 Recovery Techniques 10.8.1 Deferred Update Protocol 10.8.2 Checkpoints 10.8.3 Immediate Update Protocol 10.8.4 Shadow Paging 10.8.5 Overview of ARIES Recovery Algorithm 10.9 Transaction Management in Oracle 10.10 Chapter Summary Exercises 11. Query Optimization 11.0 Chapter Objectives 11.1 Interpretation and Optimization of Queries 11.2 Algebraic Techniques for Query Transformation 11.2.1 The Query Tree 11.2.2 An SQL Query and Its Relational Algebra Translation 11.2.3 Performing SELECT Operations Early 11.2.4 Evaluating Conjunctive Conditions 11.2.5 Performing PROJECT Early 11.2.6 Properties of the Natural Join 11.2.7 Equivalence of Algebraic Operations 11.2.8 Heuristics for Query Optimization 11.3 Processing Techniques and Cost Estimation 11.3.1 Cost Factors 11.3.2 Cost of Processing Selects 11.3.3 Processing Joins 11.3.3.1 Estimating the Size of the Result 11.3.3.2 Methods of Performing Joins 11.3.4 Processing Other Operations 11.4 Pipelining 11.5 Query Optimization in Oracle 11.6 Chapter Summary Exercises 12 Distributed Databases and Client-Server Architecture 12.0 Chapter Objectives 12.1 Rationale for Distribution 12.2 Architectures for a Distributed Processing System 12.2.1 Distributed Processing Using a Centralized Database 12.2.2. Client Server Systems 12.2.3 Parallel Databases 12.2.3 Distributed Databases 12.3 Components of a Distributed Database System 12.4 Data Placement 12.5 Transparency 12.6 Transaction Control for Distributed Databases 12.6.1 Concurrency Control 12.6.1.1 Locking Protocols 12.6.1.2 Global Deadlock Detection 12.6.1.3 Timestamping Protocols 12.6.2 Recovery 12.6.2.1 Failures and Recovery 12.6.2.2 Commit Protocols 12.7 Distributed Query Processing 12.7.1 Steps in Distributed Query Processing 12.7.2 Estimating Data Communications Costs 12.7.3 The Semijoin Operation 12.8 Chapter Summary Exercises SAMPLE PROJECT: Planning the Distribution of the Relational Database for The Art Gallery STUDENT PROJECTS: Planning for Distribution 13. Databases and the Internet 13.0 Chapter Objectives 13.1 Introduction 13.2 Fundamental Concepts of the Internet and the World Wide Web 13.2.1 Origins of the Web 13.2.2 Browsers, Links and URIs 13.2.3 HTTP 13.2.4 HTML 13.2.5 XML 13.2.5.1 Standalone XML Documents 13.2.5.2 DTDs 13.2.5.3 XML Schemas 13.3 Tiered Architecture 13.3.1 Single-tier Architecture 13.3.2 Two-tier Architecture 13.3.3 Three-tier Architecture 13.3.3.1 The Presentation Layer 13.3.3.2 The Middle Tier 13.4 The Semistructured Data Model 13.4.1 Graphical Representation 13.4.2 XML Data Manipulation 13.4.2.1 XPath Expressions 13.4.2.2 XQuery Expressions 13.4.2.3 FLOWR Expressions 13.5 XML and Relational Databases 13.5.1 Representing and Manipulating XML Data in a Relational Database 13.5.2 Publishing Relational Database Data in XML Format 13.6 Chapter Summary Lab: Browsing E-Commerce Websites that are "Powered by Oracle" SAMPLE PROJECT: Step13.1: Designing the E-Commerce Website that uses an Oracle Database for the Sample Project STUDENT PROJECTS: Step 13.1: Designing the E-Commerce Website for an Oracle Database for the Student Project 14. Social and Ethical Issues 14.0 Chapter Objectives 14.1 Computerization and Ethical Issues 14.2 Intellectual Property 14.2.1 Definition of Intellectual Property 14.2.2 Legal Protections for Intellectual Property 14.2.2.1 Meaning of Copyright 14.2.2.2 International Copyright Laws and US Copyright Laws 14.2.2.3 Patents 14.2.2.4 Trade Secrets 14.2.2.5 Trademarks 14.2.3 Intellectual Property Protection for Software 14.3 Privacy Issues 14.3.1 Privacy and Security 14.3.2 Privacy as a Human Right 14.3.2.1 Privacy Legislation in the United States 14.3.2.2 Privacy Legislation in Europe 14.4 Human Factors 14.4.1 Human factors in Software Development 14.4.2 The Human-Database Interface 14.4.3 Usability Testing for Database Applications 14.4.4 Ethical Standards for Computer and Software Professionals 15 Data Warehouses and Data Mining 15.0 Chapter Objectives 15.1 Purpose of Data Warehouses 15.2 Operational Databases and Data Warehouses 15.3 Architecture of a Data Warehouse 15.4 Data Models for Data Warehouses 15.5 Data Warehouse Queries and SQL:1999 OLAP Extension 15.6 Index Techniques 15.7 Views and View Materialization 15.8 Data Mining 15.9 Purpose of Data Mining 15.10 Types of Knowledge Discovered 15.11 Data Mining Methods 15.12 Applications of Data Mining 15.13 Chapter Summary Exercises Bibliography APPENDIX A: PHYSICAL DATA ORGANIZATION A.0 Objectives A.1 File Organization A.1.1 Storage Media A.1.2 Blocking of Records A.1.3 Record Formats A.1.4 File Organizations A.2 Data Structures A.2.1 Inverted Files A.2.2 Linked Lists A.2.3 Trees A.2.4 B+ Trees A.2.5 B Trees APPENDIX B The Network Model B.0 Objectives B.1 History of the Network Model B.2 DBTG Model and Terminology B.3 Records and Sets B.4 DBTG DDL B.4.1 The Schema B.4.2 The Subschema B.5 DBTG Data Manipulation B.6 Mapping a Network Model Database to an E-R Model B.7 Network Model Database Migration APPENDIX C. The Hierarchical Model C.0 Objectives C.1 History of the Hierarchical Model C.2 Hierarchical Data Structure C.3 IMS Architecture C.4 Control Blocks C.4.1 DBDs C.4.2 PCBs C.5 DL/1 Commands-GU,GN,GNP,GHU,GHN,GHNP,ISRT,DLET,REPL C.6 Mapping a Hierarchical Model to an E-R Model C.7 Hierarchical Model Database Migration ER -