Database Systems. A Pragmatic Approach

Cover
Half Title
Title Page
Copyright Page
Table of Contents
Preface
Section A: Preliminary Topics
	Chapter 1 Introduction to Database Systems
		1.1 Definitions and Rationale
		1.2 Objectives of a Database System
			1.2.1 Primary and Secondary Objectives
			1.2.2 Clarification on Data Independence
		1.3 Advantages of a Database System
		1.4 Approaches to Database Design
			1.4.1 Conventional Files
			1.4.2 Database Approach
		1.5 Desirable Features of a DBS
		1.6 Database Development Life Cycle
		1.7 Summary and Concluding Remarks
		1.8 Review Questions
		References and/or Recommended Readings
	Chapter 2 The Database System Environment
		2.1 Levels of Architecture
			2.1.1 External Level
			2.1.2 Conceptual Level
			2.1.3 Internal Level
		2.2 Inter-Level Mappings
		2.3 Database Personnel
			2.3.1 Database Administrator and Data Architect
			2.3.2 Tools Expert
			2.3.3 Application Programmer
			2.3.4 User Liaison Specialist
			2.3.5 Network and Infrastructure Specialist
		2.4 The Database Management System
		2.5 Components of DBMS Suite
			2.5.1 The DBMS Engine
			2.5.2 Data Definition Subsystem
			2.5.3 The User Interface Subsystem
			2.5.4 Application Development Subsystem
			2.5.5 Data Administration Subsystem
			2.5.6 Data Dictionary Subsystem
			2.5.7 Data Communications Manager
			2.5.8 Utilities Subsystem
		2.6 Front-End and Back-End Perspectives
		2.7 Database System Architecture
		2.8 Database Management System Classifications
			2.8.1 Classification Based on Data Models
			2.8.2 Classification Based on Number of Users
			2.8.3 Classification Based on Site Configuration
			2.8.4 Classification Based on Database Purpose
		2.9 Summary and Concluding Remarks
		2.10 Review Questions
		References and/or Recommended Readings
Section B: The Relational Database Model
	Chapter 3 Introducing the Relational Model
		3.1 Basic Concepts
		3.2 Domains
		3.3 Relations
			3.3.1 Properties of a Relation
			3.3.2 Kinds of Relations
		3.4 Relational Database System
		3.5 Identifying Relationships
		3.6 Representing Relationships
			3.6.1 The Entity–Relationship Model
			3.6.2 The Object–Relationship Model
			3.6.3 Summary of Traditional Models
				3.6.3.1 Database Tree
				3.6.3.2 Database Networks
			3.6.4 Multiplicity of Relationships
		3.7 Implementing Relationships
		3.8 Innovation: The Relation–Attributes List and Relationship List
		3.9 Database Naming Convention
		3.10 Summary and Concluding Remarks
		3.11 Review Questions
		References and Recommended Readings
	Chapter 4 Integrity Rules and Normalization
		4.1 Fundamental Integrity Rules
		4.2 Foreign Key Concept
		4.3 Rationale for Normalization
		4.4 Functional Dependence and Non-Loss Decomposition
			4.4.1 Functional Dependence
			4.4.2 Non-Loss Decomposition
				4.4.2.1 Corollary from Heath’s Theorem
				4.4.2.2 Conclusion from Heath’s Theorem and Its Corollary
		4.5 First Normal Form
		4.6 Second Normal Form
		4.7 Third Normal Form
		4.8 Boyce–Codd Normal Form
		4.9 Fourth Normal Form
			4.9.1 Multi-Valued Dependency
			4.9.2 Fagin’s Theorem
			4.9.3 The Zoo Revisited
		4.10 Fifth Normal Form
			4.10.1 Definition of Join Dependency
			4.10.2 Fagin’s Theorem
		4.11 An Example
		4.12 Other Normal Forms
			4.12.1 The Domain-Key Normal Form
			4.12.2 The Sixth Normal Form
		4.13 Summary and Concluding Remarks
		4.14 Review Questions
		References and Recommended Readings
	Chapter 5 Database Modeling and Design
		5.1 Database Model and Database Design
			5.1.1 Database Model
			5.1.2 Database Design
		5.2 The E–R Model Revisited
		5.3 Database Design via the E–R Model
		5.4 The Extended Relational Model
			5.4.1 Entity Classifications
			5.4.2 Surrogates
			5.4.3 E-Relations and P-Relations
			5.4.4 Integrity Rules
		5.5 Database Design via the XR Model
			5.5.1 Determining the Kernel Entities
			5.5.2 Determining the Characteristic Entities
			5.5.3 Determining the Designative Entities
			5.5.4 Determining the Associations
			5.5.5 Determining Entity Subtypes and Super-Types
			5.5.6 Determining Component Entities
			5.5.7 Determining Additional Properties
			5.5.8 Additional Application of the XR Model
		5.6 The UML Model
		5.7 Database Design via the UML Model
		5.8 Innovation: The Object/Entity Specification Grid
		5.9 Database Design via Normalization Theory
			5.9.1 Example: Mountaineering Problem
			5.9.2 Determining Candidate Keys and Then Normalizing
		5.10 Database Model and Design Tools
		5.11 Summary and Concluding Remarks
		5.12 Review Questions
		References and/or Recommended Readings
	Chapter 6 Database User Interface Design
		6.1 Overview
		6.2 Deciding on User Interface
		6.3 Steps in User Interface Design
			6.3.1 Menu or Graphical User Interface
			6.3.2 Command-Based User Interface
		6.4 User Interface Development and Implementation
		6.5 Summary and Concluding Remarks
		6.6 Review Questions
		References and/or Recommend Readings
	Chapter 7 Relational Algebra
		7.1 Overview
		7.2 Basic Operations of Relational Algebra
			7.2.1 Primary and Secondary Operations
			7.2.2 Codd’s Original Classification of Operations
			7.2.3 Nested Operations
		7.3 Syntax of Relational Algebra
			7.3.1 Database Scenario
			7.3.2 Syntax Convention
			7.3.3 Select Statement
			7.3.4 Projection Statement
			7.3.5 Natural Join Statement
			7.3.6 Cartesian Product
			7.3.7 Theta-Join
			7.3.8 Inner and Outer Joins
			7.3.9 UNION, INTERESECT, and MINUS Statements
			7.3.10 Division Statement
		7.4 Aliases, Renaming, and the Relational Assignment
			7.4.1 The Alias Operation
			7.4.2 The Assignment Operation
			7.4.3 The Rename Operation
		7.5 Other Operators
		7.6 Summary and Concluding Remarks
		7.7 Review Questions
		References and Recommended Readings
	Chapter 8 Relational Calculus
		8.1 Overview
		8.2 Calculus Notations and Illustrations
			8.2.1 Essential Notations
			8.2.2 Some Examples
		8.3 Quantifiers: Free and Bound Variables
			8.3.1 Well-Formed Formula
			8.3.2 Free and Bound Variables
		8.4 Substitution Rule and Standardization Rules
		8.5 Introductory Query Optimization
		8.6 Domain-Oriented Relational Calculus
		8.7 Summary and Concluding Remarks
		8.8 Review Questions
		References and/or Recommended Readings
	Chapter 9 Reflective Look at the Relational Database Model
		9.1 The Relational Model Summarized
		9.2 Ramifications of the Relational Model
			9.2.1 Codd’s Early Benchmark
			9.2.2 Revised Definition of a Relational System
				9.2.2.1 Codd’s Zero-Rule
				9.2.2.2 Codd’s 12 Rules
			9.2.3 Far-Reaching Consequences
		9.3 Summary and Concluding Remarks
		9.4 Review Questions
		References
Section C: The Structured Query Language
	Chapter 10 Overview of SQL
		10.1 Important Facts
			10.1.1 Commonly Used DDL Statements
			10.1.2 Commonly Used DML and DCL Statements
			10.1.3 Syntax Convention
		10.2 Advantages of SQL
		10.3 Summary and Concluding Remarks
		10.4 Review Questions
		Recommended Readings
	Chapter 11 SQL Data Definition Statements
		11.1 Overview of Oracle’s SQL Environment
		11.2 Basic Concepts in a Typical Oracle Database Environment
		11.3 Database Creation
		11.4 Database Management
		11.5 Tablespace Creation
		11.6 Tablespace Management
		11.7 Oracle Cloud Features
			11.7.1 Creating and Using a Database Instance via OAC
			11.7.2 Accessing the OAC Database via SQL Developer
			11.7.3 Accessing the OAC Database via Application Express
		11.8 Table Creation
		11.9 Dropping or Modifying a Table
		11.10 Working with Indexes
		11.11 Working with Sequences
			11.11.1 Creating and Using Sequences
			11.11.2 Altering and Dropping Sequences
		11.12 Working with Synonyms
		11.13 Summary and Concluding Remarks
		11.14 Review Questions
		References and Recommended Readings
	Chapter 12 SQL Data Manipulation Statements
		12.1 Insertion of Data
		12.2 Update Operations
		12.3 Deletion of Data
		12.4 Commit and Rollback Operations
		12.5 Basic Syntax for Queries
		12.6 Simple Queries
		12.7 Queries Involving Multiple Tables
			12.7.1 The Traditional Method
			12.7.2 The ANSI Method
		12.8 Queries Involving the Use of Functions
			12.8.1 Row Functions
			12.8.2 Date Functions
			12.8.3 Data Conversion Functions
			12.8.4 Programmer-Defined Functions
			12.8.5 Aggregation Functions
			12.8.6 Analytic Functions
		12.9 Queries Using the LIKE and BETWEEN Operators
		12.10 Nested Queries
			12.10.1 Nested Queries Involving Use of the IN Operator
			12.10.2 Nested Queries Involving Use of ANY or ALL Operator
			12.10.3 Nested Queries Using the Existential Quantifier
		12.11 Queries Involving Set Operation
		12.12 Queries with Runtime Variables
		12.13 Queries Involving SQL*Plus Format Commands
		12.14 Embedded SQL
		12.15 Dynamic Queries
		12.16 Summary and Concluding Remarks
		12.17 Review Questions
		References and/or Recommended Readings
	Chapter 13 Logical Views and System Security
		13.1 Traditional Logical Views
			13.1.1 View Creation
			13.1.2 View Modification and Removal
			13.1.3 Usefulness and Manipulation of Logical Views
		13.2 System Security
			13.2.1 Access to the System
			13.2.2 Access to System Resources
			13.2.3 Managing Access to System Resources via Development Privileges and Roles
			13.2.4 Access to System Data
				13.2.4.1 Security via Object Privileges
				13.2.4.2 Security via Views
				13.2.4.3 Security via Database Design
		13.3 Materialized Views
			13.3.1 Creating a Materialized View
			13.3.2 Altering or Dropping a Materialized View
		13.4 Summary and Concluding Remarks
		13.5 Review Questions
		References and Recommended Readings
	Chapter 14 The System Catalog
		14.1 Introduction
		14.2 Three Important Catalog Tables
			14.2.1 The User_Tables View
			14.2.2 The User_Tab_Columns View
			14.2.3 The User_Indexes View
		14.3 Other Important Catalog Tables
		14.4 Querying the System Catalog
		14.5 Updating the System Catalog
		14.6 Summary and Concluding Remarks
		14.7 Review Questions
		References and/or Recommended Readings
	Chapter 15 Some Limitations of SQL
		15.1 Programming Limitations
		15.2 Limitations on Views
			15.2.1 Restriction on Use of the ORDER-BY-Clause for Earlier Versions of SQL
			15.2.2 Restriction on Data Manipulation for Views Involving UNION, INTERSECT, or JOIN
			15.2.3 Restriction on Use of Aggregation Functions for Earlier Versions of SQL
		15.3 Stringent Enforcement of Referential Integrity
		15.4 Limitations on Calculated Columns
		15.5 If-Then Limitation
		15.6 Summary and Concluding Remarks
		15.7 Review Questions
		Recommended Readings
Section D: Advanced Topics
	Chapter 16 Database Administration
		16.1 Database Installation, Creation, and Configuration
		16.2 Database Security
		16.3 Database Management
		16.4 Database Backup and Recovery
			16.4.1 Oracle Backups: Basic Concept
			16.4.2 Oracle Recovery: Basic Concept
			16.4.3 Types of Failures
			16.4.4 Database Backups
			16.4.5 Basic Recovery Steps
			16.4.6 Oracle’s Backup and Recovery Solutions
		16.5 Database Tuning
			16.5.1 Tuning Goals
			16.5.2 Tuning Methodology
		16.6 Database Removal
		16.7 Summary and Concluding Remarks
		16.8 Review Questions
		References and/or Recommended Readings
	Chapter 17 Distributed Database Systems
		17.1 Preliminaries
		17.2 Advantages of Distributed Database Systems
		17.3 Twelve Rules for Distributed Database Systems
		17.4 Challenges to Distributed Database Systems
			17.4.1 Query Optimization
			17.4.2 Catalog Management
			17.4.3 Update Propagation
			17.4.4 Concurrency
			17.4.5 Transaction Management
		17.5 Database Gateways
		17.6 The Future of Distributed Database Systems
			17.6.1 Object Technology
			17.6.2 Electronic Communication Systems
			17.6.3 Cloud Technology
			17.6.4 Big Data
		17.7 Summary and Concluding Remarks
		17.8 Review Questions
		References and/or Recommended Readings
	Chapter 18 Object Databases
		18.1 Overview
		18.2 Overview of Object-Oriented Database Management Systems
		18.3 Challenges for Object-Oriented Database Management Systems
		18.4 Hybrid Approach
		18.5 Summary and Concluding Remarks
		18.6 Review Questions
		References and/or Recommended Readings
	Chapter 19 Data Warehousing
		19.1 Overview
			19.1.1 Definitions
			19.1.2 Acquiring a Data Warehouse
		19.2 Rationale for Data Warehousing
		19.3 Characteristics of a Data Warehouse
			19.3.1 Definitive Features
			19.3.2 Nature of Data Stored
			19.3.3 Processing Requirements
			19.3.4 Twelve Rules that Govern a Data Warehouse
		19.4 Data Warehouse Architecture
			19.4.1 Basic Data Warehouse Architecture
			19.4.2 Data Warehouse Architecture with a Staging Area
			19.4.3 Data Warehouse Architecture with a Staging Area and Data Marts
		19.5 Extraction, Transformation, and Loading
			19.5.1 What Happens During the ETL Process
			19.5.2 ETL Tools
				19.5.2.1 Oracle Tools
				19.5.2.2 IBM Db2 Tools
			19.5.3 Daily Operations and Expansion of the Data Warehouse
		19.6 Summary and Concluding Remarks
		19.7 Review Questions
		References and/or Recommended Readings
	Chapter 20 Web-Accessible Databases
		20.1 Introduction
		20.2 Web-Accessible Database Architecture
		20.3 Supporting Technologies
		20.4 Implementation with Selected DBMS Suites
			20.4.1 Implementation in Oracle
			20.4.2 Implementation with DB2
			20.4.3 Implementation in MySQL
		20.5 Generic Implementation via Front-End and Back-End Tools
		20.6 Challenges and Opportunities
			20.6.1 Cloud Computing and Big Data
			20.6.2 Cybersecurity
		20.7 Summary and Concluding Remarks
		20.8 Review Questions
		References and/or Recommended Readings
	Chapter 21 Using Database Systems to Anchor Management Support Systems
		21.1 Overview of Management Support Systems
		21.2 Building System Security through Database Design
		21.3 Case Study: Dynamic Menu Interface Designer
			21.3.1 Database Requirements of the DMID
			21.3.2 Overview of the DMID’s User Interface Requirements
			21.3.3 Management of System Constraints via the DMID
			21.3.4 Access to System Resources
			21.3.5 Usefulness and Applicability of the DMID
		21.4 Selected MSS Project Ideas
			21.4.1 Electoral Management System
			21.4.2 Health Information Management System
			21.4.3 Strategic Education Management System
			21.4.4 Flight Information Management System
			21.4.5 Financial Information Management System
			21.4.6 Disease Recognition System
			21.4.7 Cognitive Leadership Analysis System
			21.4.8 Financial Status Assessment System
			21.4.9 College/University Administrative Information System
		21.5 Summary and Concluding Remarks
		21.6 Review Questions
		References and/or Recommended Readings
Section E: Overview of Selected DBMS Suites and Technologies
	Chapter 22 Overview of Oracle
		22.1 Introduction
		22.2 Main Components of the Oracle Suite
			22.2.1 Oracle Server
			22.2.2 Oracle PL/SQL and SQL*Plus
			22.2.3 Oracle Developer Suite
			22.2.4 Oracle Enterprise Manager Database Control and SQL Developer
			22.2.5 Oracle Enterprise Manager Grid Control
			22.2.6 Oracle Database Configuration Assistant
			22.2.7 Oracle Data Integrator
		22.3 Shortcomings of Oracle
		22.4 Summary and Concluding Remarks
		22.5 Review Questions
		References and Recommended Readings
	Chapter 23 Overview of Db2
		23.1 Introduction
		23.2 Main Components of the Db2 Suite
			23.2.1 IBM Data Management Console
			23.2.2 IBM Advanced Recovery Feature
			23.2.3 IBM Db2 Connect
			23.2.4 IBM Data Studio
			23.2.5 IBM InfoSphere Data Architect
			23.2.6 IBM Data Warehousing Products
		23.3 Shortcomings of Db2
		23.4 Summary and Concluding Remarks
		23.5 Review Questions
		References and/or Recommended Readings
	Chapter 24 Overview of MySQL
		24.1 Introduction to MySQL
		24.2 Main Features of MySQL
		24.3 Main Components of MySQL
		24.4 Alternate Storage Engines
		24.5 MySQL Database Service
		24.6 Shortcomings of MySQL
			24.6.1 Limitations on Logical Views
			24.6.2 Limitations on Subqueries
			24.6.3 Limitation on Server-Side Cursors
			24.6.4 Other Limitations
		24.7 Summary and Concluding Remarks
		24.8 Review Questions
		References and Recommended Readings
	Chapter 25 Overview of Microsoft SQL Server
		25.1 Introduction
			25.1.1 Brief History
			25.1.2 Operating Environment
			25.1.3 Microsoft Server Editions
		25.2 Main Features of Microsoft SQL Server
		25.3 Main Components of Microsoft SQL Server Suite
			25.3.1 Server Components
			25.3.2 Management Tools
			25.3.3 Development Tools
			25.3.4 Client Connectivity
			25.3.5 Code Samples
		25.4 Shortcomings of Microsoft SQL Server
		25.5 Summary and Concluding Remarks
		25.6 Review Questions
		References and/or Recommended Readings
	Chapter 26 Other Emerging Database Methodologies
		26.1 Introduction
		26.2 Entity–Attributes–Value Model
			26.2.1 Rationale for the EAV Model
			26.2.2 Challenges of the EAV Model
			26.2.3 Illustrating Application of the EAV Model
		26.3 Database-Supporting Frameworks
		26.4 NoSQL
			26.4.1 NoSQL Databases versus SQL Databases
			26.4.2 Emerging NoSQL Data Models
			26.4.3 Examples of NoSQL Databases
		26.5 Other Burgeoning Trends
		26.6 Summary and Concluding Remarks
		26.7 Review Questions
		References and/or Recommended Readings
Section F: Appendices
	Appendix 1: Review of Ttrees
	Appendix 2: Review of Hashing
	Appendix 3: Review of Iinformation-Gathering Ttechniques
	Appendix 4: BNnF Syntax for Selected SQL Statements
	Appendix 5: Sample Eexercises and Eexamination Questions
Index