Database Systems Introduction to Databases and Data Warehouses 2nd edition by Nenad Jukic, Susan Vrbsky, Svetlozar Nestorov, Abhishek Sharma ISBN 1943153671 9781943153671

Chapter 1. Introduction

Initial Terminology

Steps in the Development of Database Systems

Database Requirements Collection, Definition, and Visualization

Database Modeling

Database Implementation

Developing Front-End Applications

Database Deployment

Database Use

Database Administration and Maintenance

The Next Version of the Database

Database Scope

People Involved With Database Systems

Database Analysts, Designers, and Developers

Front-End Applications Analysts and Developers

Database Administrators

Database End Users

Operational Versus Analytical Databases

Relational DBMS

Book Topics Overview

Key Terms

Review Questions

PART 1. OPERATIONAL DATABASES

Chapter 2. Database Requirements and ER Modeling

Introduction

Basic ER Modeling Constructs

Entities

Attributes (Unique and Non-unique)

Relationships

Cardinality Constraints

Types of Relationships (Maximum Cardinality-wise)

Relationships and Relationship Instances

Relationship Attributes

Example: Set of Database Requirements and ER Diagram

Composite Attributes

Composite Unique Attributes

Multiple Unique Attributes (Candidate Keys)

Multivalued Attributes

Derived Attributes

Optional Attributes

Example: Entity Containing Various Types of Attributes

Exact Minimum and Maximum Cardinality in Relationships

Unary Relationships and Relationship Roles

Multiple Relationships Between Same Entities

Weak Entity

Naming Conventions for Entities, Attributes, and Relationships

Multiple ER Diagrams

Example: Another Set of Database Requirements and an ER Diagram

Database Requirements and ER Model Usage

Various ER Notations

Enhanced ER (EER) Modeling

A Note About M:N Relationships with Multiple Instances Between the Same Entities

A Note About Associative Entities

A Note About Ternary (and Higher Degree) Relationships

A Note About Reducing the Number of Attributes by Treating Some Attributes as Attribute Values

Summary

Key Terms

Review Questions

Exercises

Mini Cases

Chapter 3. Relational Database Modeling

Introduction

Relational Database Model: Basic Concepts

Primary Key

Mapping Entities into Relations

Mapping Entities With Composite Attributes into Relations

Mapping Entities With Unique Composite Attributes into Relations

Mapping Entities With Optional Attributes into Relations

Entity Integrity Constraint

Foreign Key

Mapping Relationships into Relational Database Constructs

Mapping 1:M Relationships

Mapping M:N Relationships

Mapping 1:1 Relationships

Referential Integrity Constraint

Example: Mapping an ER Diagram into a Relational Schema

Granularity of the Table

Mapping Entities With Candidate Keys (Multiple Unique Attributes) into Relations

Mapping Entities With Multivalued Attributes into Relational Database Constructs

Mapping Entities With Derived Attributes into Relations

Example: Mapping an Entity Containing Various Types of Attributes into a Relational Schema

Mapping Unary Relationships

Mapping 1:M Unary Relationships

Mapping M:N Unary Relationships

Mapping 1:1 Unary Relationships

Mapping Multiple Relationships Between the Same Entities

Mapping Weak Entities

Example: Mapping Another ER Diagram Into a Relational Schema

Relational Database Constraints

Implicit Constraints

User-Defined Constraints

A Note About Mapping Associative Entities

A Note About Mapping Ternary Relationships

A Note About Designer-Created Primary Keys and the Autonumber Option

A Note About Performing Both ER and Relational Modeling

Summary

Key Terms

Review Questions

Exercises

Mini Cases

Chapter 4. Update Operations, Update Anomalies, and Normalization

Introduction

Update Operations

Insert Operation Example

Delete Operation Example

Modify Operation Example

Update Operation Terminology Note

Update Anomalies

Example Scenario

Example Relation (Containing Redundant Data)

Insertion Anomaly

Deletion Anomaly

Modification Anomaly

Functional Dependencies

Functional Dependency Notations

Functional Dependencies Example

Streamlining Functional Dependencies

Augmented Functional Dependencies

Equivalent Functional Dependencies

Types of Functional Dependencies

Partial Functional Dependency

Full Key Functional Dependency

Transitive Functional Dependency

Another Functional Dependencies Example

Normalization

First Normal Form (1NF)

Second Normal Form (2NF)

Third Normal Form (3NF)

3NF and Other Normal Forms

Eliminating Redundancy and Resolving Update Anomalies

Another Normalization Example

A Note About Normalization Exceptions

A Note About Denormalization: Normalization versus Performance

A Note About ER Modeling versus Normalization

A Note About Designer-Added Entities (Tables) and Keys for Streamlining Database Content

Key Terms

Review Questions

Exercises

Chapter 5. SQL

Introduction

SQL Commands Overview

Data Definition Language (DDL)

Data Manipulation Language (DML)

Data Control Language (DCL) and Transaction Control Language (TCL)

SQL Data Types

Brief SQL Syntax Notes

CREATE TABLE

DROP TABLE

INSERT INTO

SELECT

WHERE

DISTINCT

ORDER BY

LIKE

Aggregate Functions

GROUP BY

HAVING

Nested Queries

IN

JOIN

Alias

Joining Multiple Relations

ALTER TABLE

UPDATE

DELETE

CREATE VIEW and DROP VIEW

Set Operators: UNION, INTERSECT, EXCEPT (MINUS)

Additional SQL Examples With Additional SQL Commands

CREATE TABLE (Additional Example)

INSERT INTO (Additional Example)

Constraint Management

SELECT (Additional Examples)

Join of a Relation With Itself (self-JOIN)

INNER and OUTER JOIN

Join Without Using a Primary Key/Foreign Key Combination

IS NULL

EXISTS

NOT

Alternative Queries

Inserting From a Query

Other SQL Functionalities

A Note About Inappropriate Use of Observed Values in SQL

A Note About SQL Standard and SQL Syntax Differences

Key Terms

Review Questions

Exercises

Mini Cases

Chapter 6. Database Implementation and Use

Introduction

Referential Integrity Constraint: Delete and Update Implementation

Delete Options

Update Options

Implementing Delete and Update Options

Implementing User-Defined Constraints

CHECK Clause

Other Mechanisms for Implementing User-Defined Constraints

Indexing

Database Front End

Data Quality Issues

Key Terms

Review Questions

Exercises

PART 2. ANALYTICAL DATABASES

Chapter 7. Data Warehousing Concepts

Introduction

Analytical Versus Operational Information

Data Makeup Differences

Technical Differences

Functional Differences

The Data Warehouse Definition

Structured Repository

Integrated

Subject-Oriented

Enterprise-Wide

Historical

Time-Variant

Retrieval of Analytical Information

Detailed and/or Summarized Data

Data Warehouse Components

Source Systems

Data Warehouse

ETL

Data Warehouse Front-End (BI) Applications

Data Marts

Steps in Development of Data Warehouses

Requirements Collection, Definition, and Visualization

Data Warehouse Modeling

Creating the Data Warehouse

Creating ETL Infrastructure

Developing Front-End (BI) Applications

Data Warehouse Deployment

Data Warehouse Use

Data Warehouse Administration and Maintenance

The Next Version of the Data Warehouse

Key Terms

Review Questions

Chapter 8. Data Warehouse Modeling

Introduction

Dimensional Modeling: Basic Concepts

Initial Example: Dimensional Model Based on a Single Source

Characteristics of Dimensions and Facts and the Analysis of the Initial Example

Expanded Example: Dimensional Model Based on Multiple Sources

Additional Possible Fact Attributes

Transaction Identifier in the Fact Table

Transaction Time in the Fact Table

Multiple Fact Tables in a Dimensional Model

Detailed Versus Aggregated Fact Tables

Detailed Fact Table

Aggregated Fact Table

Detailed Versus Aggregated Fact Table

Granularity of the Fact Table

Line-Item Versus Transaction-Level Detailed Fact Table

Slowly Changing Dimensions and Timestamps

Type 1 Approach

Type 2 Approach

Type 3 Approach

Snowflake Model

Data Warehouse (Data Mart) Modeling Approaches

Normalized Data Warehouse

An Example of a Normalized Data Warehouse

Dimensionally Modeled Data Warehouse

Independent Data Marts

A Note About Comparing ER Modeling and Dimensional Modeling as Data Warehouse/Data Mart Design Techniques

Key Terms

Review Questions

Exercises

Mini Cases

Chapter 9. Data Warehouse Implementation and Use

Introduction

Creating a Data Warehouse

ETL: Extraction, Transformation, Load

Online Analytical Processing (OLAP)

OLAP/BI Tools

OLAP/BI Tools Functionalities

Slice and Dice

Pivot (Rotate)

Drill Down and Drill Up

Additional OLAP/BI Tools Functionality Note

OLAP/BI Tools Purpose

Data Warehouse/Data Mart Front-End (BI) Applications

Executive Dashboard

Data Warehouse Deployment

Key Terms

Review Questions

Exercises

Chapter 10. Big Data and Data Lakes

Introduction

Big Data Definition

Example – Three Types of Data Sets in a Corporation

Insurance Company – Operational Database

Insurance Company – Data Warehouse

Insurance Company – Big Data Set

MapReduce and Hadoop

Example – Examining V’s of Big Data

Example – Big Data as a Source for a Data Warehouse

Corporate Use of Big Data

Data Lake

Key Terms

Review Questions

PART 3. OTHER TOPICS

Chapter 11. Overview of DBMS Functionalities and Database Administration

Introduction

DBMS Components

Database Administration Overview

Monitoring and Maintaining the Database System

Data Dictionary

Securing the Database Against Unauthorized Access

Providing Database Backup and Recovery

Ensuring Database Integrity

Optimizing Database Performance

Developing and Implementing Database Policies and Standards

Key Terms

Review Questions

PART 4. APPENDICES

Appendix 1. Enhanced ER

Superclass and Subclass Entities

EER Example 1 – Disjointed Subclasses, Total Specialization

EER Example 2 – Overlapping Subclasses, Total Specialization

EER Example 3–Disjointed Subclasses, Partial Specialization

Appendix 2. Further Notes on Normalization and Higher Normal Forms

Candidate Keys and Functional Dependencies

Boyce-CODD Normal Form (BCNF)

Fourth Normal Form (4NF)

Other Normal Forms

Appendix 3. Enterprise Resource Planning (ERP)

Appendix 4. Data Governance and Master Data Management

Data Governance

Master Data Management

Appendix 5. Object-Oriented Databases

Object-Oriented Concepts

Object-Oriented Queries

Object-Relational Database

Appendix 6. Assertions, Triggers, Stored Procedures and Functions

Assertion

Triggers

Stored Procedures and Functions

Appendix 7. Distributed Databases, Blockchain, Parallel Databases, and Cloud Computing

Distributed Databases

Blockchain

Parallel Databases

Cloud Computing

Appendix 8. Data Mining

Association Rule Mining

Association Rule Mining Example

Appendix 9. XML Markup Languages

XML

XML Queries

Appendix 10. NoSQL Databases

Terminology of NoSQL vs. Relational Databases

NoSQL Database Examples – MongoDB

Applicability of NoSQL

Glossary

Index