database-systems-complete : Fundamentals of Database Systems V7

database-systems_pro
database-systems-complete
Fundamentals of Database Systems V7
ISBN : 978-1-61691-149-2
Fundamentals of Database Systems V7 course and performance-based labs introduce the fundamental concepts necessary for designing, using, and implementing database systems and database applications. Performance-based labs simulate real-world, hardware, software & command line interface environments and can be mapped to any text-book, course & training. Fundamentals of Database Systems V7 course and labs emphasize the fundamentals of database modeling and design, the languages and models provided by the database management systems, and database system implementation techniques.

Here's what you will get

Lessons
Lessons
34+
Exercises
1+
Quizzes
106+
Flashcards
170+
Glossary of terms
170+
TestPrep
Pre-assessment Questions
55+
Post-Assessment Questions
50+
Lab
Performance based lab
64+

Here's what you will learn

  • 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
  • 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
  • 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
  • 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
  • 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
  • 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 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
  • 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 Using ER-to-Relational Mapping
  • Mapping EER Model Constructs to Relations
  • Summary
  • Review Questions
  • Exercises
  • Laboratory Exercises
  • Selected Bibliography
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • Active Database Concepts and Triggers
  • Temporal Database Concepts
  • Spatial Database Concepts
  • Multimedia Database Concepts
  • Introduction to Deductive Databases
  • Summary
  • Review Questions
  • Exercise
  • Selected Bibliography
  • 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
  • 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
  • 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
  • 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
  • C.1 Basic Retrievals in QBE
  • C.2 Grouping, Aggregation, and Database Modification in QBE

Hands on Activities (Labs)

  • Understanding Physical Schema - ER Model
  • 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
  • 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
  • Retrieving the distinct rows from the table
  • Using the UNION operator
  • Using the SELECT operator
  • Understanding relational set operator
  • Understanding the object database concepts
  • 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
  • Understanding storage device types
  • Identifying the components of disk device
  • Understanding the buffer replacement strategies
  • Understanding program variables
  • Describing the hashing techniques
  • Creating a function-based index
  • Using a function-based index
  • Identifying the steps of processing a high-level query
  • Counting the number of department from the table
  • Using NOT IN operator
  • Using INTERSECT operator
  • Unnesting query
  • Understanding the page replacement method
  • Understanding the transaction states
  • Identifying deadlock dealing techniques
  • Identifying the advantages of distributed databases
  • Identifying the features of databases and IR systems
  • Identifying types of queries
  • Understanding the applications of data mining
  • Identifying database attacks