WU-CSC345.ABD2

CSC345: Database Foundations

Intermediate Self-paced · 1 year access

100 Hands-On LiveLabs

Practice real IT tasks in guided environments.

Real environments
Auto-graded
No installation

01 / Lessons & labs

See exactly what you will learn and practice

Download outline (PDF)

Lessons

46 Interactive Lessons · 380 topics

01 Executing a Simple Query 5 topics · 2 LiveLab

TOPIC A: Connect to the SQL Database
TOPIC B: Query a Database
TOPIC C: Save a Query
TOPIC D: Modify and Execute a Saved Query
Summary

2 LiveLab in this lesson — see the labs panel →

02 Performing a Conditional Search 4 topics · 4 LiveLab

TOPIC A: Search Using One or More Conditions
TOPIC B: Search for a Range of Values and NULL Values
TOPIC C: Search Data Based on Patterns
Summary

4 LiveLab in this lesson — see the labs panel →

03 Working with Functions 4 topics · 3 LiveLab

TOPIC A: Perform Date Calculations
TOPIC B: Calculate Data Using Aggregate Functions
TOPIC C: Manipulate String Values
Summary

3 LiveLab in this lesson — see the labs panel →

04 Organizing Data 7 topics · 6 LiveLab

TOPIC A: Sort Data
TOPIC B: Rank Data
TOPIC C: Group Data
TOPIC D: Filter Grouped Data
TOPIC E: Summarize Grouped Data
TOPIC F: Use PIVOT and UNPIVOT Operators
Summary

6 LiveLab in this lesson — see the labs panel →

05 Retrieving Data from Multiple Tables 4 topics · 3 LiveLab

TOPIC A: Combine the Results of Two Queries
TOPIC B: Compare the Results of Two Queries
TOPIC C: Retrieve Data by Joining Tables
Summary

3 LiveLab in this lesson — see the labs panel →

06 Exporting Query Results 3 topics · 2 LiveLab

TOPIC A: Generate a Text File
TOPIC B: Generate an XML File
Summary

2 LiveLab in this lesson — see the labs panel →

07 Using Subqueries to Perform Advanced Querying 7 topics · 6 LiveLab

TOPIC A: Search Based on Unknown Values
TOPIC B: Compare a Value with Unknown Values
TOPIC C: Search Based on the Existence of Records
TOPIC D: Generate Output Using Correlated Subqueries
TOPIC E: Filter Grouped Data Within Subqueries
TOPIC F: Perform Multiple-Level Subqueries
Summary

6 LiveLab in this lesson — see the labs panel →

08 Manipulating Table Data 3 topics · 1 LiveLab

TOPIC A: Insert Data
TOPIC B: Modify and Delete Data
Summary

1 LiveLab in this lesson — see the labs panel →

09 Manipulating the Table Structure 6 topics · 7 LiveLab

TOPIC A: Create a Table
TOPIC B: Create a Table with Constraints
TOPIC C: Modify a Table's Structure
TOPIC D: Back Up Tables
TOPIC E: Delete Tables
Summary

7 LiveLab in this lesson — see the labs panel →

10 Working with Views 5 topics · 5 LiveLab

TOPIC A: Create a View
TOPIC B: Manipulate Data in Views
TOPIC C: Create Aliases
TOPIC D: Modify and Delete Views
Summary

5 LiveLab in this lesson — see the labs panel →

11 Indexing Data 3 topics · 2 LiveLab

TOPIC A: Create Indexes
TOPIC B: Drop Indexes
Summary

2 LiveLab in this lesson — see the labs panel →

12 Managing Transactions 3 topics · 1 LiveLab

TOPIC A: Create Transactions
TOPIC B: Commit Transactions
Summary

1 LiveLab in this lesson — see the labs panel →

13 Appendix A 2 topics

The FullerAckerman Database
Schema

14 Databases and Database Users 12 topics · 1 LiveLab

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

1 LiveLab in this lesson — see the labs panel →

15 Database System Concepts and Architecture 10 topics · 1 LiveLab

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

1 LiveLab in this lesson — see the labs panel →

16 Data Modeling Using the Entity–Relationship (ER) Model 15 topics · 1 LiveLab

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

1 LiveLab in this lesson — see the labs panel →

17 The Enhanced Entity–Relationship (EER) Model 12 topics

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

18 The Relational Data Model and Relational Database Constraints 7 topics

Relational Model Concepts
Relational Model Constraints and Relational Database Schemas
Update Operations, Transactions, and Dealing with Constraint Violations
Summary
Review Questions
Exercises
Selected Bibliography

19 SQL Data Definition and Data Types 9 topics · 13 LiveLab

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

13 LiveLab in this lesson — see the labs panel →

20 More SQL: Complex Queries, Triggers, Views, and Schema Modification 8 topics · 9 LiveLab

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 LiveLab in this lesson — see the labs panel →

21 The Relational Algebra and Relational Calculus 12 topics · 4 LiveLab

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

4 LiveLab in this lesson — see the labs panel →

22 Relational Database Design by ER- and EER-to-Relational Mapping 7 topics

Relational Database Design Using ER-to-Relational Mapping
Mapping EER Model Constructs to Relations
Summary
Review Questions
Exercises
Laboratory Exercises
Selected Bibliography

23 Introduction to SQL Programming Techniques 9 topics · 3 LiveLab

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

3 LiveLab in this lesson — see the labs panel →

24 Web Database Programming Using PHP 8 topics

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

25 Object and Object-Relational Databases 10 topics · 1 LiveLab

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

1 LiveLab in this lesson — see the labs panel →

26 XML: Extensible Markup Language 11 topics · 1 LiveLab

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

1 LiveLab in this lesson — see the labs panel →

27 Basics of Functional Dependencies and Normalization for Relational Databases 12 topics · 5 LiveLab

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

5 LiveLab in this lesson — see the labs panel →

28 Relational Database Design Algorithms and Further Dependencies 11 topics · 2 LiveLab

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

2 LiveLab in this lesson — see the labs panel →

29 Disk Storage, Basic File Structures, Hashing, and Modern Storage Architectures 15 topics

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

30 Indexing Structures for Files and Physical Database Design 11 topics · 1 LiveLab

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

1 LiveLab in this lesson — see the labs panel →

31 Strategies for Query Processing 12 topics · 1 LiveLab

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

1 LiveLab in this lesson — see the labs panel →

32 Query Optimization 14 topics · 2 LiveLab

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

2 LiveLab in this lesson — see the labs panel →

33 Introduction to Transaction Processing Concepts and Theory 10 topics · 1 LiveLab

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

1 LiveLab in this lesson — see the labs panel →

34 Concurrency Control Techniques 11 topics

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

35 Database Recovery Techniques 11 topics · 1 LiveLab

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

1 LiveLab in this lesson — see the labs panel →

36 Distributed Database Concepts 12 topics

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

37 NOSQL Databases and Big Data Storage Systems 9 topics · 3 LiveLab

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

3 LiveLab in this lesson — see the labs panel →

38 Big Data Technologies Based on MapReduce and Hadoop 9 topics · 1 LiveLab

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

1 LiveLab in this lesson — see the labs panel →

39 Enhanced Data Models: Introduction to Active, Temporal, Spatial, Multimedia, and Deductive Databases 9 topics · 2 LiveLab

Active Database Concepts and Triggers
Temporal Database Concepts
Spatial Database Concepts
Multimedia Database Concepts
Introduction to Deductive Databases
Summary
Review Questions
Exercises
Selected Bibliography

2 LiveLab in this lesson — see the labs panel →

40 Introduction to Information Retrieval and Web Search 11 topics

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

41 Data Mining Concepts 11 topics · 3 LiveLab

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

3 LiveLab in this lesson — see the labs panel →

42 Overview of Data Warehousing and OLAP 10 topics · 1 LiveLab

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

1 LiveLab in this lesson — see the labs panel →

43 Database Security 14 topics · 1 LiveLab

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

1 LiveLab in this lesson — see the labs panel →

44 Appendix A: Alternative Diagrammatic Notations for ER Models

45 Appendix B: Parameters of Disks

46 Appendix C: Overview of the QBE Language 2 topics

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

Hands-On Labs Our edge

100 LiveLabs

Connecting to a Database
Saving a Query and Modifying and Executing the Saved Query
Comparing Column Values in a Table
Searching Using Simple and Multiple Conditions
Searching for a Range of Values and NULL Values
Retrieving Data Based on Patterns

Performing Date Calculations
Using Aggregate Functions
Manipulating String Values in a Table
Sorting Data in a Table
Ranking Data in a Table
Grouping Data in a Table
Filtering Grouped Data in a Table
Summarizing Grouped Data
Using the PIVOT and UNPIVOT Operators
Combining the Results of Two Queries
Comparing the Results of Two Queries
Retrieving Data by Joining Tables
Saving the Query Results in the CSV Format
Generating an XML File
Searching for Records Based on Unknown Values
Searching for Records by Comparing Them with Unknown Values
Searching for Records Based on Their Existence in Another Table
Generating Output Using Correlated Subqueries
Filtering Grouped Data Within a Subquery
Generating Output Using Nested Subqueries
Inserting, Modifying, and Deleting Data
Creating a Table
Creating a Table with Constraints
Adding and Dropping Columns in a Table
Adding and Dropping Constraints in a Table
Modifying the Column Definition in a Table
Backing Up a Table
Deleting a Table
Creating a View
Creating Views with Schema Binding
Manipulating Data in Views
Creating Aliases
Modifying and Deleting Views
Creating Indexes
Dropping Indexes
Creating and Committing Transactions
Understanding the Basic Database System Environment
Understanding the Schema Architecture
Understanding Physical Schema - ER Model
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
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 Triggers in SQL
Creating and Updating a View
Using the UNION, INTERSECTION, and EXCEPT Operations
Modifying a Table
Using Unary Relational Operations
Using the CROSS JOIN Operation
Using the DIVISION Operation
Using the JOIN Operation
Connecting to a Database Using Visual Studio Code
Embedding SQL Commands in Java
Using Embedded SQL in C
Creating UDTs
Generating XML Data Using SQL Functions
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
Solving the Problems with NULL Values
Solving the Problems with Dangling Tuples
Creating Function-Based Indexes
Retrieving Data Using Semi-join and Anti-join
Unnesting a Query
Using the View-Merging Operation
Understanding Transaction States and Additional Operations
Creating a Backup and Restoring Data
Building the Sample Data in MongoDB
Performing CRUD Operations in MongoDB
Using Basic Cypher Queries
Understanding the Hive System Architecture
Performing Basic Operations in Oracle
Using Triggers in Oracle Notation
Creating a Decision-Tree Model
Using the Apriori Algorithm
Performing k-Means Clustering
Understanding the General Architecture of a Data Warehouse
Granting and Revoking Privileges

Labs run in your browser — nothing to install.

02 / FAQs

Questions before you start

Prepare for CSC345: Database Foundations

One-time payment. Full access for 1 year. Start with a free trial if you want to look around first.

1 year of full access
100 LiveLab included
Certificate of completion

Try Free

No credit card required

CSC345: Database Foundations

100 Hands-On LiveLabs

See exactly what you will learn and practice

Lessons

Hands-On Labs Our edge

Questions before you start

You might also like

Prepare for CSC345: Database Foundations