uCertify

Fundamentals of Database Systems

(DATABASE-SYS.AB1) / ISBN : 978-1-64459-578-7
Lessons
Lab
TestPrep
AI Tutor (Add-on)
Instructor Led (Add-on)
Get A Free Trial

About This Course

The Fundamentals of Database Systems course covers the core concepts and skills needed to design, implement, and manage efficient databases. Gain a solid understanding of database architecture, data modeling techniques like entity-relationship diagrams, and SQL for querying and manipulating data. Explore database normalization, indexing, transactions, and security best practices. Through interactive labs and hands-on exercises, you'll practice creating, populating, and optimizing databases using popular database management systems like MySQL, SQL Server, and Oracle. 

Skills You’ll Get

Get the support you need. Enroll in our Instructor-Led Course.

Download Course Outline

1

Introduction

2

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
3

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
4

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
5

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
6

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
7

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
8

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
9

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
10

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
11

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
12

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
13

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
14

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
15

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
  • Exercises
  • Laboratory Exercises
  • Selected Bibliography
16

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
17

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
18

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
19

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
20

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
21

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
22

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
23

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
24

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
  • Exercises
  • Selected Bibliography
25

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
26

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
27

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
  • Exercises
  • Selected Bibliography
28

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
29

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
  • Exercises
  • Selected Bibliography
30

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
31

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
A

Appendix A: Alternative Diagrammatic Notations for ER Models

B

Appendix B: Parameters of Disks

C

Appendix C: Overview of the QBE Language

  • C.1 Basic Retrievals in QBE
  • C.2 Grouping, Aggregation, and Database Modification in QBE

1

Databases and Database Users

  • Understanding the Basic Database System Environment
2

Database System Concepts and Architecture

  • Understanding the Schema Architecture
3

Data Modeling Using the Entity–Relationship (ER) Model

  • Understanding Physical Schema - ER Model
4

SQL Data Definition and Data Types

  • Creating a Table in SQL
  • Enforcing Referential Integrity Constraints
  • Using Attribute Constraints
  • Using the CHECK Constraint
  • Using the Substring Pattern Matching
  • Using the DISTINCT Keyword
  • Using Aliases
  • Using the ORDER BY Clause
  • Using Asterisk in SQL
  • Using Arithmetic Operations
  • Updating Data in a Table
  • Inserting Data into a Table
  • Deleting a Record from a Table
5

More SQL: Complex Queries, Triggers, Views, and Schema Modification

  • Using GROUP BY and HAVING Clauses
  • Using the EXISTS and NOT EXISTS Operators
  • Retrieving Data Using a Nested Query
  • Retrieving Data Using WITH and CASE Clauses
  • Using Aggregate Functions
  • Using Assertions and Triggers
  • Creating and Updating a View
  • Using the UNION, INTERSECTION, and MINUS Operations
  • Modifying a Table
6

The Relational Algebra and Relational Calculus

  • Using Unary Relational Operations
  • Using the CROSS JOIN Operation
  • Using the DIVISION Operation
  • Using the JOIN Operation
7

Introduction to SQL Programming Techniques

  • Connecting to a Database Using Visual Studio Code
  • Embedding SQL Commands in Java
  • Using Embedded SQL in C
8

Object and Object-Relational Databases

  • Creating UDTs
9

XML: Extensible Markup Language

  • Generating XML Data Using SQL Functions
10

Basics of Functional Dependencies and Normalization for Relational Databases

  • Normalizing the 2nd Normal Form to 3rd Normal Form
  • Normalizing the 1st Normal Form to 2nd Normal Form
  • Normalizing the Unnormalized Model to 1st Normal Form II
  • Normalizing the Unnormalized Model to 1st Normal Form I
  • Normalizing the 3rd Normal Form to BCNF
11

Relational Database Design Algorithms and Further Dependencies

  • Solving the Problems with NULL Values
  • Solving the Problems with Dangling Tuples
12

Indexing Structures for Files and Physical Database Design

  • Creating Function-Based Indexes
13

Strategies for Query Processing

  • Retrieving Data Using Semi-join and Anti-join
14

Query Optimization

  • Unnesting a Query
  • Using the View-Merging Operation
15

Introduction to Transaction Processing Concepts and Theory

  • Understanding Transaction States and Additional Operations
16

Database Recovery Techniques

  • Creating a Backup and Restoring Data
17

NOSQL Databases and Big Data Storage Systems

  • Building the Sample Data in MongoDB
  • Performing CRUD Operations in MongoDB
  • Using Basic Cypher Queries
18

Big Data Technologies Based on MapReduce and Hadoop

  • Understanding the Hive System Architecture
19

Enhanced Data Models: Introduction to Active, Temporal, Spatial, Multimedia, and Deductive Databases

  • Performing Basic Operations in Oracle
  • Using Triggers in Oracle Notation
20

Data Mining Concepts

  • Creating a Decision-Tree Model
  • Using the Apriori Algorithm
  • Performing k-Means Clustering
21

Overview of Data Warehousing and OLAP

  • Understanding the General Architecture of a Data Warehouse
22

Database Security

  • Granting and Revoking Privileges

Related Courses

All Course
scroll to top