INF2603
Databases I

Database Systems Design, Implementation, & Management

(14th edition)

by Carlos Coronel, Steven Morris

1. Database Systems
1.1. Why Databases?
1.2. Data versus Information
1.3. Introducing the Database
1.3.3a. Role and Advantages of the DBMS
1.3.3b. Types of Databases
1.4. Why Database Design Is Important
1.5. Evolution of File System Data Processing
1.5.5a. Manual File Systems
1.5.5b. Computerized File Systems
1.5.5c. File System Redux: Modern End-User Productivity Tools
1.6. Problems with File System Data Processing
1.6.6a. Structural and Data Dependence
1.6.6b. Data Redundancy
1.6.6c. Data Anomalies
1.7. Database Systems
1.7.7a. The Database System Environment
1.7.7b. DBMS Functions
1.7.7c. Managing the Database System: A Shift in Focus
1.8. Preparing for Your Database Professional Career
2. Data Models
2.1. Data Modeling and Data Models
2.2. The Importance of Data Models
2.3. Data Model Basic Building Blocks
2.4. Business Rules
2.4.4a. Discovering Business Rules
2.4.4b. Translating Business Rules into Data Model Components
2.4.4c. Naming Conventions
2.5. The Evolution of Data Models
2.5.5a. Hierarchical and Network Models
2.5.5b. The Relational Model
2.5.5c. The Entity Relationship Model
2.5.5d. The Object-Oriented Model
2.5.5e. Object/Relational and XML
2.5.5f. Emerging Data Models: Big Data and NoSQL
2.5.5g. Data Models: A Summary
2.6. Degrees of Data Abstraction
2.6.6a. The External Model
2.6.6b. The Conceptual Model
2.6.6c. The Internal Model
2.6.6d. The Physical Model
3. The Relational Database Model
3.1. A Logical View of Data
3.1.1a. Tables and Their Characteristics
3.2. Keys
3.2.2a. Dependencies
3.2.2b. Types of Keys
3.3. Integrity Rules
3.4. Relational Algebra
3.4.4a. Formal Definitions and Terminology
3.4.4b. Relational Set Operators
3.5. The Data Dictionary and the System Catalog
3.6. Relationships within the Relational Database
3.6.6a. The 1:M Relationship
3.6.6b. The 1:1 Relationship
3.6.6c. The M:N Relationship
3.7. Data Redundancy Revisited
3.8. Indexes
3.9. Codd's Relational Database Rules
4. Entity Relationship (ER) Modeling
4.1. The Entity Relationship Model
4.1.1a. Entities
4.1.1b. Attributes
4.1.1c. Relationships
4.1.1d. Connectivity and Cardinality
4.1.1e. Existence Dependence
4.1.1f. Relationship Strength
4.1.1g. Weak Entities
4.1.1h. Relationship Participation
4.1.1i. Relationship Degree
4.1.1j. Recursive Relationships
4.1.1k. Associative (Composite) Entities
4.2. Developing an ER Diagram
4.3. Database Design Challenges: Conflicting Goals
5. Advanced Data Modeling
5.1. The Extended Entity Relationship Model
5.1.1a. Entity Supertypes and Subtypes
5.1.1b. Specialization Hierarchy
5.1.1c. Inheritance
5.1.1d. Subtype Discriminator
5.1.1e. Disjoint and Overlapping Constraints
5.1.1f. Completeness Constraint
5.1.1g. Specialization and Generalization
5.2. Entity Clustering
5.3. Entity Integrity: Selecting Primary Keys
5.3.3a. Natural Keys and Primary Keys
5.3.3b. Primary Key Guidelines
5.3.3c. When to Use Composite Primary Keys
5.3.3d. When to Use Surrogate Primary Keys
5.4. Design Cases: Learning Flexible Database Design
5.4.4a. Design Case 1: Implementing 1:1 Relationships
5.4.4b. Design Case 2: Maintaining History of Time-Variant Data
5.4.4c. Design Case 3: Fan Traps
5.4.4d. Design Case 4: Redundant Relationships
6. Normalization of Database Tables
6.1. Database Tables and Normalization
6.2. The Need for Normalization
6.3. The Normalization Process
6.3.3a. Conversion to First Normal Form (1NF)
6.3.3b. Conversion to Second Normal Form (2NF)
6.3.3c. Conversion to Third Normal Form (3NF)
6.4. Improving the Design
6.5. Surrogate Key Considerations
6.6. Higher-Level Normal Forms
6.6.6a. The Boyce-Codd Normal Form
6.6.6b. Fourth Normal Form (4NF)
6.6.6c. Fifth Normal Form (5NF)
6.7. Normalization and Database Design
6.8. Denormalization
6.9. Data-Modeling Checklist
7. Introduction to Structured Query Language (SQL)
7.1. SQL Basics
7.1.1a. Data Types
7.1.1b. SQL Queries
7.1.1c. The Database Model
7.2. Basic SELECT Queries
7.3. SELECT Statement Options
7.3.3a. Using Column Aliases
7.3.3b. Using Computed Columns
7.3.3c. Arithmetic Operators: The Rule of Precedence
7.3.3d. Date Arithmetic
7.3.3e. Listing Unique Values
7.4. FROM Clause Options
7.5. ORDER BY Clause Options
7.6. WHERE Clause Options
7.6.6a. Selecting Rows with Conditional Restrictions
7.6.6b. Using Comparison Operators on Character Attributes
7.6.6c. Using Comparison Operators on Dates
7.6.6d. Logical Operators: AND, OR, and NOT
7.6.6e. Special Operators
7.7. JOIN Operations
7.7.7a. Natural Join
7.7.7b. JOIN USING Syntax
7.7.7c. JOIN ON Syntax
7.7.7d. Common Attribute Names
7.7.7e. Old-Style Joins
7.7.7f. Outer Joins
7.7.7g. Cross Join
7.7.7h. Joining Tables with an Alias
7.7.7i. Recursive Joins
7.8. Aggregate Processing
7.8.8a. Aggregate Functions
7.8.8b. Grouping Data
7.8.8c. HAVING Clause
7.9. Subqueries
7.9.9a. WHERE Subqueries
7.9.9b. IN Subqueries
7.9.9c. HAVING Subqueries
7.9.9d. Multirow Subquery Operators: ALL and ANY
7.9.9e. FROM Subqueries
7.9.9f. Attribute List Subqueries
7.9.9g. Correlated Subqueries
7.10. SQL Functions
7.10.10a. Date and Time Functions
7.10.10b. Numeric Functions
7.10.10c. String Functions
7.10.10d. Conversion Functions
7.11. Relational Set Operators
7.11.11a. UNION
7.11.11b. UNION ALL
7.11.11c. INTERSECT
7.11.11d. EXCEPT (MINUS)
7.11.11e. Syntax Alternatives
7.12. Crafting SELECT Queries
7.12.12a. Know Your Data
7.12.12b. Know the Problem
7.12.12c. Build One Clause at a Time
8. Advanced SQL
8.1. Data Definition Commands
8.1.1a. Starting Database Model
8.1.1b. Creating the Database
8.1.1c. The Database Schema
8.1.1d. Data Types
8.2. Creating Table Structures
8.2.2a. CREATE TABLE command
8.2.2b. SQL Constraints
8.2.2c. Creating a Table with a SELECT Statement
8.2.2d. SQL Indexes
8.3. Altering Table Structures
8.3.3a. Changing a Column's Data Type
8.3.3b. Changing a Column's Data Characteristics
8.3.3c. Adding a Column
8.3.3d. Adding Primary Key, Foreign Key, and Check Constraints
8.3.3e. Dropping a Column
8.3.3f. Deleting a Table from the Database
8.4. Data Manipulation Commands
8.4.4a. Adding Table Rows
8.4.4b. Inserting Table Rows with a SELECT Subquery
8.4.4c. Saving Table Changes
8.4.4d. Updating Table Rows
8.4.4e. Deleting Table Rows
8.4.4f. Restoring Table Contents
8.5. Virtual Tables: Creating a View
8.5.5a. Updatable Views
8.6. Auto Increment, Identity, and Sequences
8.7. Procedural SQL
8.7.7a. Stored Procedures
8.7.7b. Working with Variables
8.7.7c. Conditional Execution
8.7.7d. Iteration or Looping
8.7.7e. SELECT Processing with Cursors
8.7.7f. Stored Procedures with Parameters
8.7.7g. Triggers
8.7.7h. User Defined Functions
8.8. Embedded SQL
9. Database Design
9.1. The Information System
9.2. The Systems Development Life Cycle
9.2.2a. Planning
9.2.2b. Analysis
9.2.2c. Detailed Systems Design
9.2.2d. Implementation
9.2.2e. Maintenance
9.3. The Database Life Cycle
9.3.3a. The Database Initial Study
9.3.3b. Database Design
9.3.3c. Implementation and Loading
9.3.3d. Testing and Evaluation
9.3.3e. Operation
9.3.3f. Maintenance and Evolution
9.4. Conceptual Design
9.4.4a. Data Analysis and Requirements
9.4.4b. Entity Relationship Modeling and Normalization
9.4.4c. Data Model Verification
9.4.4d. Distributed Database Design
9.5. DBMS Software Selection
9.6. Logical Design
9.6.6a. Map the Conceptual Model to the Logical Model Components
9.6.6b. Validate the Logical Model Using Normalization
9.6.6c. Validate the Logical Model Integrity Constraints
9.6.6d. Validate the Logical Model against User Requirements
9.7. Physical Design
9.7.7a. Define Data Storage Organization
9.7.7b. Define Integrity and Security Measures
9.7.7c. Determine Performance Measurements
9.8. Database Design Strategies
9.9. Centralized versus Decentralized Design
10. Transaction Management and Concurrency Control
10.1. What Is a Transaction?
10.1.1a. Evaluating Transaction Results
10.1.1b. Transaction Properties
10.1.1c. Transaction Management with SQL
10.1.1d. The Transaction Log
10.2. Concurrency Control
10.2.2a. Lost Updates
10.2.2b. Uncommitted Data
10.2.2c. Inconsistent Retrievals
10.2.2d. The Scheduler
10.3. Concurrency Control with Locking Methods
10.3.3a. Lock Granularity
10.3.3b. Lock Types
10.3.3c. Two-Phase Locking to Ensure Serializability
10.3.3d. Deadlocks
10.4. Concurrency Control with Time Stamping Methods
10.4.4a. Wait/Die and Wound/Wait Schemes
10.5. Concurrency Control with Optimistic Methods
10.6. ANSI Levels of Transaction Isolation
10.7. Database Recovery Management
10.7.7a. Transaction Recovery
11. Database Performance Tuning and Query Optimization
11.1. Database Performance-Tuning Concepts
11.1.1a. Performance Tuning: Client and Server
11.1.1b. DBMS Architecture
11.1.1c. Database Query Optimization Modes
11.1.1d. Database Statistics
11.2. Query Processing
11.2.2a. SQL Parsing Phase
11.2.2b. SQL Execution Phase
11.2.2c. SQL Fetching Phase
11.2.2d. Query Processing Bottlenecks
11.3. Indexes and Query Optimization
11.4. Optimizer Choices
11.4.4a. Using Hints to Affect Optimizer Choices
11.5. SQL Performance Tuning
11.5.5a. Index Selectivity
11.5.5b. Conditional Expressions
11.6. Query Formulation
11.7. DBMS Performance Tuning
11.8. Query Optimization Example
12. Distributed Database Management Systems
12.1. The Evolution of Distributed Database Management Systems
12.2. DDBMS Advantages and Disadvantages
12.3. Distributed Processing and Distributed Databases
12.4. Characteristics of Distributed Database Management Systems
12.5. DDBMS Components
12.6. Levels of Data and Process Distribution
12.6.6a. Single-Site Processing, Single-Site Data
12.6.6b. Multiple-Site Processing, Single-Site Data
12.6.6c. Multiple-Site Processing, Multiple-Site Data
12.7. Distributed Database Transparency Features
12.8. Distribution Transparency
12.9. Transaction Transparency
12.9.9a. Distributed Requests and Distributed Transactions
12.9.9b. Distributed Concurrency Control
12.9.9c. Two-Phase Commit Protocol
12.10. Performance and Failure Transparency
12.11. Distributed Database Design
12.11.11a. Data Fragmentation
12.11.11b. Data Replication
12.11.11c. Data Allocation
12.12. The CAP Theorem
12.13. C. J. Date's 12 Commandments for Distributed Databases
13. Business Intelligence and Data Warehouses
13.1. The Need for Data Analysis
13.2. Business Intelligence
13.2.2a. Business Intelligence Architecture
13.2.2b. Business Intelligence Benefits
13.2.2c. Business Intelligence Evolution
13.2.2d. Business Intelligence Technology Trends
13.3. Decision Support Data
13.3.3a. Operational Data versus Decision Support Data
13.3.3b. Decision Support Database Requirements
13.4. The Data Warehouse
13.4.4a. Data Marts
13.4.4b. Twelve Rules That Define a Data Warehouse
13.5. Star Schemas
13.5.5a. Facts
13.5.5b. Dimensions
13.5.5c. Attributes
13.5.5d. Attribute Hierarchies
13.5.5e. Star Schema Representation
13.5.5f. Performance-Improving Techniques for the Star Schema
13.6. Online Analytical Processing
13.6.6a. Multidimensional Data Analysis Techniques
13.6.6b. Advanced Database Support
13.6.6c. Easy-to-Use End-User Interfaces
13.6.6d. OLAP Architecture
13.6.6e. Relational OLAP
13.6.6f. Multidimensional OLAP
13.6.6g. Relational versus Multidimensional OLAP
13.7. Data Analytics
13.7.7a. Data Mining
13.7.7b. Predictive Analytics
13.8. SQL Analytic Functions
13.8.8a. The ROLLUP Extension
13.8.8b. The CUBE Extension
13.8.8c. Materialized Views
13.9. Data Visualization
13.9.9a. The Need for Data Visualization
13.9.9b. The Science of Data Visualization
13.9.9c. Understanding the Data
13.10. Data Lake
14. Big Data and NoSQL
14.1. Big Data
14.1.1a. Volume
14.1.1b. Velocity
14.1.1c. Variety
14.1.1d. Other Characteristics
14.2. Hadoop
14.2.2a. HDFS
14.2.2b. MapReduce
14.2.2c. Hadoop Ecosystem
14.2.2d. Hadoop Pushback
14.3. NoSQL
14.3.3a. Key-Value Databases
14.3.3b. Document Databases
14.3.3c. Column-Oriented Databases
14.3.3d. Graph Databases
14.3.3e. Aggregate Awareness
14.4. NewSQL Databases
14.5. Working with Document Databases Using MongoDB
14.5.5a. Importing Documents in MongoDB
14.5.5b. Example of a MongoDB Query Using find()
14.6. Working with Graph Databases Using Neo4j
14.6.6a. Creating Nodes in Neo4j
14.6.6b. Retrieving Node Data with MATCH and WHERE
14.6.6c. Retrieving Relationship Data with MATCH and WHERE
15. Database Connectivity and Web Technologies
15.1. Database Connectivity
15.1.1a. Native SQL Connectivity
15.1.1b. ODBC, DAO, and RDO
15.1.1c. OLE-DB
15.1.1d. ADO.NET
15.1.1e. Java Database Connectivity (JDBC)
15.2. Database Internet Connectivity
15.2.2a. Web-to-Database Middleware: Server-Side Extensions
15.2.2b. Web Server Interfaces
15.2.2c. The Web Browser
15.2.2d. Client-Side Extensions
15.2.2e. Web Application Servers
15.2.2f. Web Database Development
15.3. Extensible Markup Language (XML)
15.3.3a. Document Type Definitions (DTD) and XML Schemas
15.3.3b. XML Presentation
15.3.3c. XML Applications
15.4. Cloud Computing Services
15.4.4a. Cloud Implementation Types
15.4.4b. Characteristics of Cloud Services
15.4.4c. Types of Cloud Services
15.4.4d. Cloud Services: Advantages and Disadvantages
15.4.4e. SQL Data Services
16. Database Administration and Security
16.1. Data as a Corporate Asset
16.2. The Need for a Database and Its Role in an Organization
16.3. Introduction of a Database: Special Considerations
16.4. The Evolution of Database Administration
16.5. The Database Environment's Human Component
16.5.5a. The DBA's Managerial Role
16.5.5b. The DBA's Technical Role
16.6. Security
16.6.6a. Security Policies
16.6.6b. Security Vulnerabilities
16.6.6c. Database Security
16.7. Database Administration Tools
16.7.7a. The Data Dictionary
16.7.7b. Case Tools
16.8. Developing a Data Administration Strategy
16.9. The DBA's Role in the Cloud
16.10. The DBA at Work: Using Oracle for Database Administration
16.10.10a. Oracle Database Administration Tools
16.10.10b. Ensuring That the RDBMS Starts Automatically
16.10.10c. Creating Tablespaces and Datafiles
16.10.10d. Managing Users and Establishing Security
16.10.10e. Customizing the Database Initialization Parameters