Fundamentals of Database Systems
(database-systems-complete) / ISBN : 978-1-61691-149-2
About This Course
Use the Fundamentals of Database Systems course and lab to learn database concepts and methodologies. Lab simulates real-world, hardware, software, and command-line interface environments and can be mapped to any textbook, course, and training. The database training course covers fundamental concepts necessary for designing, using, and implementing database systems and applications; database modeling and design; languages and models provided by database management systems; and database system implementation techniques.
Skills You’ll Get
Get the support you need. Enroll in our Instructor-Led Course.
Interactive Lessons
34+ Interactive Lessons | 104+ Quizzes | 170+ Flashcards | 170+ Glossary of terms
Gamified TestPrep
55+ Pre Assessment Questions | 50+ Post Assessment Questions |
Preface
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
- Exercise
- 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
- 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
- Exercise
- 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
- 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
Data Modeling Using the Entity–Relationship (ER) Model
- Understanding Physical Schema - ER Model
The Enhanced Entity–Relationship (EER) Model
- Understanding generalization
SQL Data Definition and Data Types
- Creating a table
- Using Referential Integrity constraints in a table
- Using Check constraints in a table
- Retrieving the data from the table
- Using select-project-join query
- Using alias-naming
- Retrieving all the attribute values using asterisk
- Retrieving the data using multiple condition
- Specifying the cross product of the table
- Using SELECT DISTINCT statement and UNION operator
- Using substring pattern matching
- Using arithmetic operators
- Retrieving the sorted data using ORDER BY clause
- Inserting values into a table
- Inserting values into a table from existing table
- Deleting the rows from a table
- Updating the table
More SQL: Complex Queries, Triggers, Views, and Schema Modification
- Retrieving values of a table using nested queries I
- Retrieving values of a table using nested queries II
- Retrieving values of a table using JOIN operator
- Using Aggregate functions
- Counting all the rows of a table
- Using the GROUP BY and HAVING clauses
- Using the CASE clause
- Specifying general constraints as Assertions
- Creating view
- Modifying the table definition
The Relational Algebra and Relational Calculus
- Retrieving the distinct rows from the table
- Using the UNION operator
- Using the SELECT operator
- Understanding relational set operator
Object and Object-Relational Databases
- Drag the object database concept to its description.
Basics of Functional Dependencies and Normalization for Relational Databases
- Normalizing the unnormalized model to 1st normal form I
- Normalizing the unnormalized model to 1st normal form II
- Normalizing the 1st normal form to 2nd normal form
- Normalizing the 2nd normal form to 3rd normal form
- Analyzing the normal forms
- Normalizing the 3rd normal form to BCNF
Relational Database Design Algorithms and Further Dependencies
- Identifying inference rules
Disk Storage, Basic File Structures, Hashing, and Modern Storage Architectures
- Understanding storage device types
- Identifying the components of disk device
- Understanding the buffer replacement strategies
- Understanding program variables
- Describing the hashing techniques
Indexing Structures for Files and Physical Database Design
- Creating a function-based index
- Using a function-based index
Strategies for Query Processing
- Identifying the steps of processing a high-level query
- Counting the number of department from the table
- Using NOT IN operator
- Using INTERSECT operator
Query Optimization
- Unnesting query
Introduction to Transaction Processing Concepts and Theory
- Understanding the page replacement method
- Understanding the transaction states
Concurrency Control Techniques
- Identifying deadlock dealing techniques
Database Recovery Techniques
- Creating a backup
Distributed Database Concepts
- Click to select the advantages of distributed databases.
NOSQL Databases and Big Data Storage Systems
- Categorizing NOSQL systems
Enhanced Data Models: Introduction to Active, Temporal, Spatial, Multimedia, and Deductive Databases
- Classifying spatial operators
Introduction to Information Retrieval and Web Search
- Identifying the features of databases and IR systems
- Identifying types of queries
Data Mining Concepts
- Understanding the applications of data mining
Database Security
- Identifying database attacks