Contents – Introduction to Database Management Systems

Contents

Foreword

Preface

Chapter 1   File Systems

1.1    Need for a File

1.2    Files

1.2.1    Sample File

1.2.2    Records and Fields

1.2.3    Master and Transaction Data

1.3    Computer Files

1.4    Library Management – A Case Study

1.4.1    Record Keys

1.4.2    Searching Records

1.5    Sequential Organisation

1.5.1    What is Sequential Organisation?

1.5.2    Advantages of Sequential Organisation

1.5.3    Problems with Sequential Organisation

1.6    Pointers and Chains

1.6.1    Problems with One-way Chains

1.6.2    Two-way Chains

1.6.3    Queries Based on Other Fields

1.7    Indexed Organisation

1.7.1    Using Indexes

1.7.2    Improvements to Index-chain Method

1.7.3    Maintaining a List of All Items in the Index

1.7.4    Keeping a Count of Records

1.7.5    Complex Queries and Query Optimisation

1.7.6    Indexed Organisation in Computer Files

1.8    Direct Organisation

1.8.1    Basic Concepts

1.8.2    Non-hashed Files

1.8.3    Hashed Files

Key Terms and Concepts

Chapter Summary

Practice Set

Chapter 2   Introduction to Database Systems

2.1    What is DBMS?

2.2    File Management Systems (FMS)

2.3    Database Management Systems (DBMS)

2.4    FMS versus DBMS

2.5    An Overview of Database Management

2.5.1    DBMS Basics

2.5.2    Internal Process

2.5.3    Tables, Rows and Columns

2.5.4    SQL and its Power

2.6    Brief Introduction to SQL

2.6.1    Data Definition Language (DDL)

2.6.2    Data Manipulation Language (DML)

2.6.3    Select, Insert, Update and Delete

2.6.4    Multiple Tables and Joins

2.6.5    Nested Queries

2.6.6    Data Control Language

2.7    Embedded SQL

2.7.1    Embedding SQL Statements inside 3GL

2.7.2    Embedded SQL Program Lifecycle

2.7.3    Cursors

2.8    Dynamic SQL

2.9    DBMS models

2.9.1    The Hierarchical Model

2.9.1.1    Retrieval

2.9.1.2    Insert

2.9.1.3    Delete

2.9.1.4    Update

2.9.2    Network Model

2.9.2.1    Retrieval

2.9.2.2    Insert

2.9.2.3    Delete

2.9.2.4    Update

2.9.3    Relational Model

2.9.3.1    Retrieval

2.10     Database System Architecture

Key Terms and Concepts

Chapter Summary

Practice Set

Chapter 3   The Relational Model

3.1    Relational Databases Primer

3.1.1    Tabular Representation of Data

3.1.2    Some Terminology

3.1.3    Domains

3.2    Relational Database Characteristics

3.3    Relational Algebra

3.3.1    Relational Algebra Operators

3.3.1.1 Restrict

3.3.1.2 Project

3.3.1.3 Product

3.3.1.4 Union

3.3.1.5 Intersection

3.3.1.6 Difference

3.3.1.7 Join

3.3.1.8 Divide

3.3.2    Grouping

3.4    Relational Calculus

3.5    Database Integrity

3.5.1    Constraints

3.5.2    Declarative and Procedural Constraints

3.5.2.1 Type constraints

3.5.2.2 Attribute constraints

3.5.2.3 Instance constraints

3.5.2.4 Database constraints

3.5.3    More on Constraints

3.6    Keys

3.6.1    Superkey and Key

3.6.2    Composite Key

3.6.3    Candidate Key

3.6.4    Primary Key

3.6.5    Alternate Key or Secondary Key

3.6.6    Foreign Key

3.6.7    Keys and SQL

3.6.7.1 Defining primary keys in SQL

3.6.7.2 Defining foreign keys in SQL

3.7    Entity and Referential Integrity

3.7.1    Entity Integrity

3.7.2    Referential Integrity

3.8    Views

3.8.1    What is a View?

3.8.2    Updating Data through Views

Key Terms and Concepts

Chapter Summary

Practice Set

Chapter 4   Database Design

4.1    Design Considerations

4.2    Functional Dependency

4.3    Normalisation and Normal Forms

4.3.1    Decomposition

4.3.2    What is Normalisation?

4.3.3    First Normal Form (1NF)

4.3.4    Second Normal Form (2NF)

4.3.5    Third Normal Form (3NF)

4.3.6    Boyce-Codd Normal Form (BCNF)

4.3.7    Fourth Normal Form (4NF)

4.3.8    Fifth Normal Form (5NF)

4.3.9    Normalisation Summary

4.3.10  Denormalisation

4.4    Entity/Relationship (E/R) Modelling

4.4.1    Aspects of E/R Modelling

4.4.2    Types of Relationships

Key Terms and Concepts

Chapter Summary

Practice Set

Chapter 5   Transaction Processing and Management

5.1    Transaction

5.1.1    Transactions – Need and Mechanisms

5.1.2    Transaction Processing (TP) Monitor

5.1.3    Transaction Properties

5.2    Recovery

5.2.1    Classification of Recovery

5.2.2    System Recovery

5.2.2.1    Failure recovery

5.2.2.2    Media recovery

5.3    Transaction Models

5.3.1    Flat Transactions

5.3.2    Chained Transactions

5.3.3    Nested Transactions

5.4    Two-phase Commit

5.5    Concurrency Problems

5.5.1    Lost Update Problem

5.5.2    Dirty (Uncommitted) Read Problem

5.5.3    Non-Repeatable Read Problem

5.5.4    Phantom Read Problem

5.6    Locking

5.7    Concurrency Problems Revisited

5.7.1    Lost Update Problem Revisited

5.7.2    Dirty (Uncommitted) Read Problem Revisited

5.7.3    Non-repeatable Read Problem Revisited

5.7.4    Phantom Read Problem Revisited

5.8    Deadlocks

5.9    Transaction Serialisability

5.10  Two-phase Locking

5.11  Isolation Levels

Key Terms and Concepts

Chapter Summary

Practice Set

Chapter 6   Database Security

6.1    Data Classification

6.1.1    Importance of Data

6.1.2    Private Organisations versus Military Classifications

6.2    Threats and Risks

6.2.1    Confidentiality

6.2.2    Authentication

6.2.3    Integrity

6.2.4    Non-repudiation

6.3    Cryptography

6.3.1    Types of Cryptography

6.3.1.1    Symmetric key cryptography

6.3.1.2    Asymmetric key cryptography

6.4    Digital Signature

6.5    Database Control

6.5.1    Discretionary Control

6.5.2    Mandatory Control

6.6    Users and Database Privileges

6.7    Types of Privileges

6.8    Object Privileges

6.8.1    Operations and Privileges

6.8.2    Granting Object Privileges

6.8.3    Restricting Object Privileges to Certain Columns

6.8.4    Granting All Privileges at the Same Time

6.8.5    Allowing Others to Grant Privileges

6.9    Taking Away Privileges

6.10  Filtering Table Privileges

6.11  Statistical Databases

Key Terms and Concepts

Chapter Summary

Practice Set

Chapter 7   Query Execution and Optimisation

7.1    Query Processing

7.2    Using Indexes

7.3    Optimiser Functionality

7.3.1    Driver Index

7.3.2    List Merge

7.4    Implementing SELECT

7.4.1    Simple SELECT

7.4.2    Complex SELECT Implementation

7.4.3    JOIN Implementation

7.4.4    PROJECT Implementation

7.4.5    SET Operator Implementation

7.4.6    Aggregate Functions Implementation

7.5    OptImisation Recommendations

7.6    Database Statistics

Key Terms and Concepts

Chapter Summary

Practice Set

Chapter 8   Distributed Databases

8.1    Distributed Database Concepts

8.1.1    Distributed Computing

8.1.2    Distributed Databases

8.2    Distributed Database Architectures

8.3    Advantages of Distributed Databases

8.4    Distributed Database Requirements

8.5    Distributed Database Techniques

8.5.1    Data Fragmentation

8.5.2    Data Replication

8.6    Distributed Query Processing

8.6.1    Costs

8.6.2    Semi-join

8.6.3    Distributed Query Decomposition

8.7    Distributed Concurrency Control and Recovery

8.7.1    Concurrency and Recovery Problems

8.7.2    Distinguished Copy

8.7.2.1    Primary site technique

8.7.2.2    Primary site with backup site technique

8.7.2.3    Primary copy technique

8.7.3    Dealing with Coordinator Failures

8.7.4    Voting Method

8.7.5    Distributed Recovery

8.8    Distributed Deadlocks

8.8.1    Prevent a Deadlock

8.8.2    Avoid a Deadlock

8.8.3    Detect a Deadlock

8.9    Client/Server Computing and DDBMS

8.9.1    Client/server Computing

8.9.2    Client/server Computing and DDBMS

8.10  Date's 12    Rules

Key Terms and Concepts

Chapter Summary

Practice Set

Chapter 9   Decision Support Systems, Data Warehousing and Data Mining

9.1    Information and Decision Making

9.1.1    Data and Information

9.1.2    Need for Information

9.1.3    Quality of Information

9.1.4    Value of Timely Information

9.1.5    Historical Data

9.2    What is a Data Warehouse?

9.3    Data Warehousing Concepts

9.4    Data Warehousing Approaches

9.4.1    Enterprise Data Warehouse

9.4.2    Data Marts

9.4.2.1    Dependent data mart

9.4.2.2    Independent data mart

9.4.3    Operational Data Stores

9.5    Online Analytical Processing (OLAP)

9.5.1    Desktop OLAP

9.5.2    Relational OLAP (ROLAP)

9.5.3    Multidimensional OLAP (MOLAP)

9.5.4    Hybrid OLAP

Key Terms and Concepts

Chapter Summary

Practice Set

Chapter 10 Object Technology and DBMS

10.1    An Introduction to Object Technology

10.1.1    Attributes and Methods

10.1.2    Messages

10.1.3    What is Modelling?

10.1.4    Practical Example of an Object

10.1.5    Classes

10.1.6    The Essence of Classes and Objects

10.2    Abstraction

10.3    Encapsulation

10.4    Inheritance

10.5    Object Technology and RDBMS

10.5.1    Identifying a Record Uniquely

10.5.2    Mapping Classes to Tables

10.5.3    Mapping Binary Associations to Tables

10.5.4    Modelling Generalisations to Tables

10.6    Object Oriented Database Management Systems (OODBMS)

10.6.1    Basic Concepts

10.6.2    When Should OODBMS be Used?

10.6.3    Advantages of OODBMS

10.6.4    Examples of ODL, OQL and OML

Key Terms and Concepts

Chapter Summary

Practice Set

Chapter 11 Advanced Topics in DBMS

11.1    Deductive Databases

11.1.1    Features of Deductive Databases

11.1.2    An Overview of Logic

11.1.3    Knowledge Representation

11.2    Internet and DBMS

11.2.1    What is WWW?

11.2.2    Web server and Web browser

11.2.3    Hyper Text Markup Language (HTML)

11.2.4    Dynamic Web Pages

11.2.5    Issues in Web Databases

11.3    Multimedia Databases

11.3.1    What is Multimedia?

11.3.2    Sampling and Quantising

11.3.3    Issues in Multimedia Databases

11.4    Digital Libraries

11.5    Mobile Databases

11.5.1    What is Mobile Computing?

11.5.2    Case Study - WAP

11.5.3    Data in Mobile Applications

11.5.4    Mobile Databases: Problem Areas

Key Terms and Concepts

Chapter Summary

Practice Set

Appendix A  Data Structures

Appendix B  Sorting Techniques

Appendix C  Database Management with Access

Appendix D  Case Studies

Index