Database Systems

Thomas M. Connolly is the head of the Computing and Information Systems division at the University of Paisley. Carolyn E. Begg is a lecturer at the University of Paisley, with research interests in Information Systems, Database Management Systems, and Decision Support Systems within medicine.

• Table of Contents Part 1 Background Chapter 1 Introduction to Databases 1.1 Introduction1.2 Traditional File-Based Systems 1.2.1 File-Based Approach1.2.2 Limitations of the File-Based Approach1.3 Database Approach 1.3.1 The Database1.3.2 The Database Management System (DBMS)1.3.3 (Database) Application Programs1.3.4 Components of the DBMS Environment1.3.5 Database Design: The Paradigm Shift1.4 Roles in the Database Environment 1.4.1 Data and Database Administrators1.4.2 Database Designers1.4.3 Application Developers1.4.4 End-Users1.5 History of Database Management Systems1.6 Advantages and Disadvantages of DBMSs Chapter SummaryReview QuestionsExercisesChapter 2 Database Environment 2.1 The Three-Level ANSI-SPARC Architecture 2.1.1 External Level2.1.2 Conceptual Level2.1.3 Internal Level2.1.4 Schemas, Mappings, and Instances2.1.5 Data Independence2.2 Database Languages 2.2.1 The Data Definition Language (DDL)2.2.2 The Data Manipulation Language (DML)2.2.3 Fourth-Generation Languages (4GLs)2.3 Data Models and Conceptual Modeling 2.3.1 Object-Based Data Models2.3.2 Record-Based Data Models2.3.3 Physical Data Models2.3.4 Conceptual Modeling2.4 Functions of a DBMS Chapter SummaryReview QuestionsExercisesChapter 3 Database Architectures and the Web 3.1 Multi-user DBMS Architectures 3.1.1 Teleprocessing3.1.2 File-Server Architecture3.1.3 Traditional Two-Tier Client—Server Architecture3.1.4 Three-Tier Client—Server Architecture3.1.5 N-Tier Architectures3.1.6 Middleware3.1.7 Transaction Processing Monitors3.2 Web Services and Service-Oriented Architectures 3.2.1 Web Services3.2.2 Service-Oriented Architectures (SOA)3.3 Distributed DBMSs3.4 Data Warehousing3.5 Cloud Computing 3.5.1 Benefits and Risks of Cloud Computing3.5.2 Cloud-based database solutions3.6 Components of a DBMS3.7 Oracle Architecture 3.7.1 Oracle’s Logical Database Structure3.7.2 Oracle’s Physical Database Structure Chapter SummaryReview QuestionsExercisesPart 2 The Relational Model and Languages Chapter 4 The Relational Model 4.1 Brief History of the Relational Model4.2 Terminology 4.2.1 Relational Data Structure4.2.2 Mathematical Relations4.2.3 Database Relations4.2.4 Properties of Relations4.2.5 Relational Keys4.2.6 Representing Relational Database Schemas4.3 Integrity Constraints 4.3.1 Nulls4.3.2 Entity Integrity4.3.3 Referential Integrity4.3.4 General Constraints4.4 Views 4.4.1 Terminology4.4.2 Purpose of Views4.4.3 Updating Views Chapter SummaryReview QuestionsExercises Chapter 5 Relational Algebra and Relational Calculus 5.1 The Relational Algebra 5.1.1 Unary Operations5.1.2 Set Operations5.1.3 Join Operations5.1.4 Division Operation5.1.5 Aggregation and Grouping Operations5.1.6 Summary of the Relational Algebra Operations5.2 The Relational Calculus 5.2.1 Tuple Relational Calculus5.2.2 Domain Relational Calculus5.3 Other LanguagesChapter SummaryReview QuestionsExercises Chapter 6 SQL: Data Manipulation 6.1 Introduction to SQL 6.1.1 Objectives of SQL6.1.2 History of SQL6.1.3 Importance of SQL6.1.4 Terminology6.2 Writing SQL Commands6.3 Data Manipulation 6.3.1 Simple Queries6.3.2 Sorting Results (ORDER BY Clause)6.3.3 Using the SQL Aggregate Functions6.3.4 Grouping Results (GROUP BY Clause)6.3.5 Subqueries6.3.6 ANY and ALL6.3.7 Multi-table Queries6.3.8 EXISTS and NOT EXISTS6.3.9 Combining Result Tables (UNION, INTERSECT, EXCEPT)6.3.10 Database Updates Chapter SummaryReview QuestionsExercisesChapter 7 SQL: Data Definition 7.1 The ISO SQL Data Types 7.1.1 SQL Identifiers7.1.2 SQL Scalar Data Types7.2 Integrity Enhancement Feature 7.2.1 Required Data7.2.2 Domain Constraints7.2.3 Entity Integrity7.2.4 Referential Integrity7.2.5 General Constraints7.3 Data Definition 7.3.1 Creating a Database7.3.2 Creating a Table (CREATE TABLE)7.3.3 Changing a Table Definition (ALTER TABLE)7.3.4 Removing a Table (DROP TABLE)7.3.5 Creating an Index (CREATE INDEX)7.3.6 Removing an Index (DROP INDEX)7.4 Views 7.4.1 Creating a View (CREATE VIEW)7.4.2 Removing a View (DROP VIEW)7.4.3 View Resolution7.4.4 Restrictions on Views7.4.5 View Updatability7.4.6 WITH CHECK OPTION7.4.7 Advantages and Disadvantages of Views7.4.8 View Materialization7.5 Transactions 7.5.1 Immediate and Deferred Integrity Constraints7.6 Discretionary Access Control 7.6.1 Granting Privileges to Other Users (GRANT)7.6.2 Revoking Privileges from Users (REVOKE) Chapter SummaryReview QuestionsExercisesChapter 8 Advanced SQL 8.1 The SQL Programming Language 8.1.1 Declarations8.1.2 Assignments8.1.3 Control Statements8.1.4 Exceptions in PL/SQL8.1.5 Cursors in PL/SQL8.2 Subprograms, Stored Procedures, Functions, and Packages8.3 Triggers8.4 Recursion Chapter SummaryReview QuestionsExercisesChapter 9 Object-Relational DBMSs 9.1 Advanced Database Applications9.2 Weaknesses of RDBMSs9.3 Storing Objects in a Relational Database 9.3.1 Mapping Classes to Relations9.3.2 Accessing Objects in the Relational Database9.4 Introduction to Object-Relational Database Systems9.5 SQL:2011 9.5.1 Row Types9.5.2 User-Defined Types9.5.3 Subtypes and Supertypes9.5.4 User-Defined Routines9.5.5 Polymorphism9.5.6 Reference Types and Object Identity9.5.7 Creating Tables9.5.8 Querying Data9.5.9 Collection Types9.5.10 Typed Views9.5.11 Persistent Stored Modules9.5.12 Triggers9.5.13 Large Objects9.5.14 Recursion9.6 Object-Oriented Extensions in Oracle 9.6.1 User-Defined Data Types9.6.2 Manipulating Object Tables9.6.3 Object Views9.6.4 Privileges Chapter SummaryReview QuestionsExercisesPart 3 Database Analysis and Design Chapter 10 Database System Development Lifecycle 10.1 The Information Systems Lifecycle10.2 The Database System Development Lifecycle10.3 Database Planning10.4 System Definition 10.4.1 User Views10.5 Requirements Collection and Analysis 10.5.1 Centralized Approach10.5.2 View Integration Approach10.6 Database Design 10.6.1 Approaches to Database Design10.6.2 Data Modeling10.6.3 Phases of Database Design10.7 DBMS Selection 10.7.1 Selecting the DBMS10.8 Application Design 10.8.1 Transaction Design10.8.2 User Interface Design Guidelines10.9 Prototyping10.10 Implementation10.11 Data Conversion and Loading10.12 Testing10.13 Operational Maintenance10.14 CASE Tools Chapter SummaryReview QuestionsExercisesChapter 11 Database Analysis and the DreamHome Case Study 11.1 When Are Fact-Finding Techniques Used?11.2 What Facts Are Collected?11.3 Fact-Finding Techniques 11.3.1 Examining Documentation11.3.2 Interviewing11.3.3 Observing the Enterprise in Operation11.3.4 Research11.3.5 Questionnaires11.4 Using Fact-Finding Techniques: A Worked -Example 11.4.1 The DreamHome Case Study–An Overview of the Current System11.4.2 The DreamHome Case Study–Database Planning11.4.3 The DreamHome Case Study–System Definition11.4.4 The DreamHome Case Study–Requirements Collection and Analysis11.4.5 The DreamHome Case Study–Database Design Chapter SummaryReview QuestionsExercisesChapter 12 Entity—Relationship Modeling 12.1 Entity Types12.2 Relationship Types 12.2.1 Degree of Relationship Type12.2.2 Recursive Relationship12.3 Attributes 12.3.1 Simple and Composite Attributes12.3.2 Single-valued and Multi-valued Attributes12.3.3 Derived Attributes12.3.4 Keys12.4 Strong and Weak Entity Types12.5 Attributes on Relationships12.6 Structural Constraints 12.6.1 One-to-One (1:1) Relationships12.6.2 One-to-Many (1:*) Relationships12.6.3 Many-to-Many (*:*) Relationships12.6.4 Multiplicity for Complex Relationships12.6.5 Cardinality and Participation Constraints12.7 Problems with ER Models 12.7.1 Fan Traps12.7.2 Chasm Traps Chapter SummaryReview QuestionsExercisesChapter 13 Enhanced Entity—Relationship Modeling 13.1 Specialization/Generalization 13.1.1 Superclasses and Subclasses13.1.2 Superclass/Subclass Relationships13.1.3 Attribute Inheritance13.1.4 Specialization Process13.1.5 Generalization Process13.1.6 Constraints on Specialization/Generalization13.1.7 Worked Example of using Specialization/ Generalization to Model the Branch View of the DreamHome Case Study13.2 Aggregation13.3 Composition Chapter SummaryReview QuestionsExercisesChapter 14 Normalization 14.1 The Purpose of Normalization14.2 How Normalization Supports Database Design14.3 Data Redundancy and Update Anomalies 14.3.1 Insertion Anomalies14.3.2 Deletion Anomalies14.3.3 Modification Anomalies14.4 Functional Dependencies 14.4.1 Characteristics of Functional Dependencies14.4.2 Identifying Functional Dependencies14.4.3 Identifying the Primary Key for a Relation Using Functional Dependencies14.5 The Process of Normalization14.6 First Normal Form (1NF)14.7 Second Normal Form (2NF)14.8 Third Normal Form (3NF)14.9 General Definitions of 2NF and 3NF Chapter SummaryReview QuestionsExercisesChapter 15 Advanced Normalization 15.1 More on Functional Dependencies 15.1.1 Inference Rules for Functional Dependencies15.1.2 Minimal Sets of Functional Dependencies15.2 Boyce—Codd Normal Form (BCNF) 15.2.1 Definition of BCNF15.3 Review of Normalization Up to BCNF15.4 Fourth Normal Form (4NF) 15.4.1 Multi-Valued Dependency15.4.2 Definition of Fourth Normal Form15.5 Fifth Normal Form (5NF) 15.5.1 Lossless-Join Dependency15.5.2 Definition of Fifth Normal Form Chapter SummaryReview QuestionsExercisesPart 4 Methodology Chapter 16 Methodology–Conceptual Database Design 16.1 Introduction to the Database Design Methodology 16.1.1 What Is a Design Methodology?16.1.2 Conceptual, Logical, and Physical Database Design16.1.3 Critical Success Factors in Database Design16.2 Overview of the Database Design Methodology16.3 Conceptual Database Design MethodologyStep 1: Build Conceptual Data Model Chapter SummaryReview QuestionsExercisesChapter 17 Methodology–Logical Database Design for the Relational Model 17.1 Logical Database Design Methodology for the Relational ModelStep 2: Build Logical Data Model Chapter SummaryReview QuestionsExercisesChapter 18 Methodology–Physical Database Design for Relational Databases 18.1 Comparison of Logical and Physical Database Design18.2 Overview of the Physical Database Design Methodology18.3 The Physical Database Design Methodology for Relational DatabasesStep 3: Translate Logical Data Model for Target DBMSStep 4: Design File Organizations and IndexesStep 5: Design User ViewsStep 6: Design Security Mechanisms Chapter SummaryReview QuestionsExercisesChapter 19 Methodology–Monitoring and Tuning the Operational System 19.1 Denormalizing and Introducing Controlled RedundancyStep 7: Consider the Introduction of Controlled Redundancy19.2 Monitoring the System to Improve PerformanceStep 8: Monitor and Tune the Operational System Chapter SummaryReview QuestionsExercisesPart 5 Selected Database Issues Chapter 20 Security and Administration 20.1 Database Security 20.1.1 Threats20.2 Countermeasures–Computer-Based Controls 20.2.1 Authorization20.2.2 Access Controls20.2.3 Views20.2.4 Backup and Recovery20.2.5 Integrity20.2.6 Encryption20.2.7 RAID (Redundant Array of Independent Disks)20.3 Security in Microsoft Office Access DBMS20.4 Security in Oracle DBMS20.5 DBMSs and Web Security 20.5.1 Proxy Servers20.5.2 Firewalls20.5.3 Message Digest Algorithms and Digital Signatures20.5.4 Digital Certificates20.5.5 Kerberos20.5.6 Secure Sockets Layer and Secure HTTP20.5.7 Secure Electronic Transactions and Secure Transaction Technology20.5.8 Java Security20.5.9 ActiveX Security20.6 Data Administration and Database Administration 20.6.1 Data Administration20.6.2 Database Administration20.6.3 Comparison of Data and Database Administration Chapter SummaryReview QuestionsExercisesChapter 21 Professional, Legal, and Ethical Issues in Data Management 21.1 Defining Legal and Ethical Issues in IT 21.1.1 Defining Ethics in the Context of IT21.1.2 The Difference Between Ethical and Legal Behavior21.1.3 Ethical Behavior in IT21.2 Legislation and Its Impact on the IT Function 21.2.1 Securities and Exchange Commission (SEC) Regulation National Market System (NMS)21.2.2 The Sarbanes-Oxley Act, COBIT, and COSO21.2.3 The Health Insurance Portability and Accountability Act21.2.4 The European Union (EU) Directive on Data Protection of 199521.2.5 The United Kingdom’s Data Protection Act of 199821.2.6 Access to Information Laws21.2.7 International Banking–Basel II Accords21.3 Establishing a Culture of Legal and Ethical Data Stewardship 21.3.1 Developing an Organization-Wide Policy for Legal and Ethical Behavior21.3.2 Professional Organizations and Codes of Ethics21.3.3 Developing an Organization-Wide Policy for Legal and Ethical Behavior for DreamHome21.4 Intellectual Property 21.4.1 Patent21.4.2 Copyright21.4.3 Trademark21.4.4 Intellectual Property Rights Issues for Software21.4.5 Intellectual Property Rights Issues for Data Chapter SummaryReview QuestionsExercisesChapter 22 Transaction Management 22.1 Transaction Support 22.1.1 Properties of Transactions22.1.2 Database Architecture22.2 Concurrency Control 22.2.1 The Need for Concurrency Control22.2.2 Serializability and Recoverability22.2.3 Locking Methods22.2.4 Deadlock22.2.5 Timestamping Methods22.2.6 Multiversion Timestamp Ordering22.2.7 Optimistic Techniques22.2.8 Granularity of Data Items22.3 Database Recovery 22.3.1 The Need for Recovery22.3.2 Transactions and Recovery22.3.3 Recovery Facilities22.3.4 Recovery Techniques22.3.5 Recovery in a Distributed DBMS22.4 Advanced Transaction Models 22.4.1 Nested Transaction Model22.4.2 Sagas22.4.3 Multilevel Transaction Model22.4.4 Dynamic Restructuring22.4.5 Workflow Models22.5 Concurrency Control and Recovery in Oracle 22.5.1 Oracle’s Isolation Levels22.5.2 Multiversion Read Consistency22.5.3 Deadlock Detection22.5.4 Backup and Recovery Chapter SummaryReview QuestionsExercisesChapter 23 Query Processing 23.1 Overview of Query Processing23.2 Query Decomposition23.3 Heuristical Approach to Query Optimization 23.3.1 Transformation Rules for the Relational Algebra Operations23.3.2 Heuristical Processing Strategies23.4 Cost Estimation for the Relational Algebra Operations 23.4.1 Database Statistics23.4.2 Selection Operation (S = sp(R))23.4.3 Join Operation (T = (R 1F S))23.4.4 Projection Operation (S = pA1, A2, . . . , A m(R))23.4.5 The Relational Algebra Set Operations (T = R ¿ S, T = R > S, T = R — S)23.5 Enumeration of Alternative Execution Strategies 23.5.1 Pipelining23.5.2 Linear Trees23.5.3 Physical Operators and Execution Strategies23.5.4 Reducing the Search Space23.5.5 Enumerating Left-Deep Trees23.5.6 Semantic Query Optimization23.5.7 Alternative Approaches to Query Optimization23.5.8 Distributed Query Optimization23.6 Query Processing and Optimization 23.6.1 New Index Types23.7 Query Optimization in Oracle 23.7.1 Rule-Based and Cost-Based Optimization23.7.2 Histograms23.7.3 Viewing the Execution Plan Chapter SummaryReview QuestionsExercisesPart 6 Distributed DBMSs and Replication Chapter 24 Distributed DBMSs–Concepts and Design 24.1 Introduction 24.1.1 Concepts24.1.2 Advantages and Disadvantages of DDBMSs24.1.3 Homogeneous and Heterogeneous DDBMSs24.2 Overview of Networking24.3 Functions and Architectures of a DDBMS 24.3.1 Functions of a DDBMS24.3.2 Reference Architecture for a DDBMS24.3.3 Reference Architecture for a Federated MDBS24.3.4 Component Architecture for a DDBMS24.4 Distributed Relational Database Design 24.4.1 Data Allocation24.4.2 Fragmentation