Fundamentals of Database Systems

Introduction

Databases and Database Users

• Introduction
• An Example
• Characteristics of the Database Approach
• Actors on the Scene
• Workers behind the Scene
• Advantages of Using the DBMS Approach
• A Brief History of Database Applications
• When Not to Use a DBMS
• Summary
• Review Questions
• Exercises
• Selected Bibliography

Database System Concepts and Architecture

• Data Models, Schemas, and Instances
• Three-Schema Architecture and Data Independence
• Database Languages and Interfaces
• The Database System Environment
• Centralized and Client/Server Architectures for DBMSs
• Classification of Database Management Systems
• Summary
• Review Questions
• Exercises
• Selected Bibliography

Data Modeling Using the Entity–Relationship (ER) Model

• Using High-Level Conceptual Data Models for Database Design
• A Sample Database Application
• Entity Types, Entity Sets, Attributes, and Keys
• Relationship Types, Relationship Sets, Roles, and Structural Constraints
• Weak Entity Types
• Refining the ER Design for the COMPANY Database
• ER Diagrams, Naming Conventions, and Design Issues
• Example of Other Notation: UML Class Diagrams
• Relationship Types of Degree Higher than Two
• Another Example: A UNIVERSITY Database
• Summary
• Review Questions
• Exercises
• Laboratory Exercises
• Selected Bibliography

The Enhanced Entity–Relationship (EER) Model

• Subclasses, Superclasses, and Inheritance
• Specialization and Generalization
• Constraints and Characteristics of Specialization and Generalization Hierarchies
• Modeling of UNION Types Using Categories
• A Sample UNIVERSITY EER Schema, Design Choices, and Formal Definitions
• Example of Other Notation: Representing Specialization and Generalization in UML Class Diagrams
• Data Abstraction, Knowledge Representation, and Ontology Concepts
• Summary
• Review Questions
• Exercises
• Laboratory Exercises
• Selected Bibliography

The Relational Data Model and Relational Database Constraints

• Relational Model Concepts
• Relational Model Constraints and Relational Database Schemas
• Update Operations, Transactions, and Dealing with Constraint Violations
• Summary
• Review Questions
• Exercises
• Selected Bibliography

SQL Data Definition and Data Types

• SQL Data Definition and Data Types
• Specifying Constraints in SQL
• Basic Retrieval Queries in SQL
• INSERT, DELETE, and UPDATE Statements in SQL
• Additional Features of SQL
• Summary
• Review Questions
• Exercises
• Selected Bibliography

More SQL: Complex Queries, Triggers, Views, and Schema Modification

• More Complex SQL Retrieval Queries
• Specifying Constraints as Assertions and Actions as Triggers
• Views (Virtual Tables) in SQL
• Schema Change Statements in SQL
• Summary
• Review Questions
• Exercises
• Selected Bibliography

The Relational Algebra and Relational Calculus

• Unary Relational Operations: SELECT and PROJECT
• Relational Algebra Operations from Set Theory
• Binary Relational Operations: JOIN and DIVISION
• Additional Relational Operations
• Examples of Queries in Relational Algebra
• The Tuple Relational Calculus
• The Domain Relational Calculus
• Summary
• Review Questions
• Exercises
• Laboratory Exercises
• Selected Bibliography

Relational Database Design by ER- and EER-to-Relational Mapping

• Relational Database Design Using ER-to-Relational Mapping
• Mapping EER Model Constructs to Relations
• Summary
• Review Questions
• Exercises
• Laboratory Exercises
• Selected Bibliography

Introduction to SQL Programming Techniques

• Overview of Database Programming Techniques and Issues
• Embedded SQL, Dynamic SQL, and SQL J
• Database Programming with Function Calls and Class Libraries: SQL/CLI and JDBC
• Database Stored Procedures and SQL/PSM
• Comparing the Three Approaches
• Summary
• Review Questions
• Exercises
• Selected Bibliography

Web Database Programming Using PHP

• A Simple PHP Example
• Overview of Basic Features of PHP
• Overview of PHP Database Programming
• Brief Overview of Java Technologies for Database Web Programming
• Summary
• Review Questions
• Exercises
• Selected Bibliography

Object and Object-Relational Databases

• Overview of Object Database Concepts
• Object Database Extensions to SQL
• The ODMG Object Model and the Object Definition Language ODL
• Object Database Conceptual Design
• The Object Query Language OQL
• Overview of the C++ Language Binding in the ODMG Standard
• Summary
• Review Questions
• Exercises
• Selected Bibliography

XML: Extensible Markup Language

• Structured, Semistructured, and Unstructured Data
• XML Hierarchical (Tree) Data Model
• XML Documents, DTD, and XML Schema
• Storing and Extracting XML Documents from Databases
• XML Languages
• Extracting XML Documents from Relational Databases
• XML/SQL: SQL Functions for Creating XML Data
• Summary
• Review Questions
• Exercises
• Selected Bibliography

Basics of Functional Dependencies and Normalization for Relational Databases

• Informal Design Guidelines for Relation Schemas
• Functional Dependencies
• Normal Forms Based on Primary Keys
• General Definitions of Second and Third Normal Forms
• Boyce-Codd Normal Form
• Multivalued Dependency and Fourth Normal Form
• Join Dependencies and Fifth Normal Form
• Summary
• Review Questions
• Exercises
• Laboratory Exercises
• Selected Bibliography

Relational Database Design Algorithms and Further Dependencies

• Further Topics in Functional Dependencies: Inference Rules, Equivalence, and Minimal Cover
• Properties of Relational Decompositions
• Algorithms for Relational Database Schema Design
• About Nulls, Dangling Tuples, and Alternative Relational Designs
• Further Discussion of Multivalued Dependencies and 4NF
• Other Dependencies and Normal Forms
• Summary
• Review Questions
• Exercises
• Laboratory Exercises
• Selected Bibliography

Disk Storage, Basic File Structures, Hashing, and Modern Storage Architectures

• Introduction
• Secondary Storage Devices
• Buffering of Blocks
• Placing File Records on Disk
• Operations on Files
• Files of Unordered Records (Heap Files)
• Files of Ordered Records (Sorted Files)
• Hashing Techniques
• Other Primary File Organizations
• Parallelizing Disk Access Using RAID Technology
• Modern Storage Architectures
• Summary
• Review Questions
• Exercises
• Selected Bibliography

Indexing Structures for Files and Physical Database Design

• Types of Single-Level Ordered Indexes
• Multilevel Indexes
• Dynamic Multilevel Indexes Using B-Trees and B+-Trees
• Indexes on Multiple Keys
• Other Types of Indexes
• Some General Issues Concerning Indexing
• Physical Database Design in Relational Databases
• Summary
• Review Questions
• Exercises
• Selected Bibliography

Strategies for Query Processing

• Translating SQL Queries into Relational Algebra and Other Operators
• Algorithms for External Sorting
• Algorithms for SELECT Operation
• Implementing the JOIN Operation
• Algorithms for PROJECT and Set Operations
• Implementing Aggregate Operations and Different Types of JOINs
• Combining Operations Using Pipelining
• Parallel Algorithms for Query Processing
• Summary
• Review Questions
• Exercises
• Selected Bibliography

Query Optimization

• Query Trees and Heuristics for Query Optimization
• Choice of Query Execution Plans
• Use of Selectivities in Cost-Based Optimization
• Cost Functions for SELECT Operation
• Cost Functions for the JOIN Operation
• Example to Illustrate Cost-Based Query Optimization
• Additional Issues Related to Query Optimization
• An Example of Query Optimization in Data Warehouses
• Overview of Query Optimization in Oracle
• Semantic Query Optimization
• Summary
• Review Questions
• Exercises
• Selected Bibliography

Introduction to Transaction Processing Concepts and Theory

• Introduction to Transaction Processing
• Transaction and System Concepts
• Desirable Properties of Transactions
• Characterizing Schedules Based on Recoverability
• Characterizing Schedules Based on Serializability
• Transaction Support in SQL
• Summary
• Review Questions
• Exercises
• Selected Bibliography

Concurrency Control Techniques

• Two-Phase Locking Techniques for Concurrency Control
• Concurrency Control Based on Timestamp Ordering
• Multiversion Concurrency Control Techniques
• Validation (Optimistic) Techniques and Snapshot Isolation Concurrency Control
• Granularity of Data Items and Multiple Granularity Locking
• Using Locks for Concurrency Control in Indexes
• Other Concurrency Control Issues
• Summary
• Review Questions
• Exercises
• Selected Bibliography

Database Recovery Techniques

• Recovery Concepts
• NO-UNDO/REDO Recovery Based on Deferred Update
• Recovery Techniques Based on Immediate Update
• Shadow Paging
• The ARIES Recovery Algorithm
• Recovery in Multidatabase Systems
• Database Backup and Recovery from Catastrophic Failures
• Summary
• Review Questions
• Exercises
• Selected Bibliography

Distributed Database Concepts

• Distributed Database Concepts
• Data Fragmentation, Replication, and Allocation Techniques for Distributed Database Design
• Overview of Concurrency Control and Recovery in Distributed Databases
• Overview of Transaction Management in Distributed Databases
• Query Processing and Optimization in Distributed Databases
• Types of Distributed Database Systems
• Distributed Database Architectures
• Distributed Catalog Management
• Summary
• Review Questions
• Exercises
• Selected Bibliography

NOSQL Databases and Big Data Storage Systems

• Introduction to NOSQL Systems
• The CAP Theorem
• Document-Based NOSQL Systems and MongoDB
• NOSQL Key-Value Stores
• Column-Based or Wide Column NOSQL Systems
• NOSQL Graph Databases and Neo4j
• Summary
• Review Questions
• Selected Bibliography

Big Data Technologies Based on MapReduce and Hadoop

• What Is Big Data?
• Introduction to MapReduce and Hadoop
• Hadoop Distributed File System (HDFS)
• MapReduce: Additional Details
• Hadoop v2 alias YARN
• General Discussion
• Summary
• Review Questions
• Selected Bibliography

Enhanced Data Models: Introduction to Active, Temporal, Spatial, Multimedia, and Deductive Databases

• Active Database Concepts and Triggers
• Temporal Database Concepts
• Spatial Database Concepts
• Multimedia Database Concepts
• Introduction to Deductive Databases
• Summary
• Review Questions
• Exercises
• Selected Bibliography

Introduction to Information Retrieval and Web Search

• Information Retrieval (IR) Concepts
• Retrieval Models
• Types of Queries in IR Systems
• Text Preprocessing
• Inverted Indexing
• Evaluation Measures of Search Relevance
• Web Search and Analysis
• Trends in Information Retrieval
• Summary
• Review Questions
• Selected Bibliography

Data Mining Concepts

• Overview of Data Mining Technology
• Association Rules
• Classification
• Clustering
• Approaches to Other Data Mining Problems
• Applications of Data Mining
• Commercial Data Mining Tools
• Summary
• Review Questions
• Exercises
• Selected Bibliography

Overview of Data Warehousing and OLAP

• Introduction, Definitions, and Terminology
• Characteristics of Data Warehouses
• Data Modeling for Data Warehouses
• Building a Data Warehouse
• Typical Functionality of a Data Warehouse
• Data Warehouse versus Views
• Difficulties of Implementing Data Warehouses
• Summary
• Review Questions
• Selected Bibliography

Database Security

• Introduction to Database Security Issues
• Discretionary Access Control Based on Granting and Revoking Privileges
• Mandatory Access Control and Role-Based Access Control for Multilevel Security
• SQL Injection
• Introduction to Statistical Database Security
• Introduction to Flow Control
• Encryption and Public Key Infrastructures
• Privacy Issues and Preservation
• Challenges to Maintaining Database Security
• Oracle Label-Based Security
• Summary
• Review Questions
• Exercises
• Selected Bibliography

Appendix A: Alternative Diagrammatic Notations for ER Models

Appendix B: Parameters of Disks

Appendix C: Overview of the QBE Language

• C.1 Basic Retrievals in QBE
• C.2 Grouping, Aggregation, and Database Modification in QBE