Description
Introduction
Apache Derby SQL, Data Modeling & Database Development provides comprehensive knowledge of SQL programming, relational data modeling, schema design, database objects, transactions, stored procedures, functions, advanced SQL development, and database application design using Apache Derby.
This training helps participants develop practical skills in designing database schemas, writing SQL queries, managing database objects, implementing database logic, and building reliable database solutions using Apache Derby.
Prerequisites
-
Basic understanding of relational database concepts.
-
Basic SQL knowledge.
-
Familiarity with tables, keys, constraints, and relationships.
-
Basic understanding of database normalization.
-
Basic Java programming knowledge.
-
Familiarity with database development concepts.
Estimated Duration
-
Beginner Level: 3 Days – 24 Hours
-
Intermediate Level: 5 Days – 40 Hours
-
Advanced Level: 7 Days – 56 Hours
-
Enterprise Level: 10 Days – 80 Hours
Training Lab Specification
-
Apache Derby latest stable release.
-
Java Development Kit (JDK).
-
Apache Derby Embedded environment.
-
Apache Derby Network Server environment.
-
Derby command-line tools.
-
JDBC drivers.
-
SQL development environment.
-
Windows or Linux virtual machine.
-
Minimum 4 GB RAM per virtual machine.
-
Minimum 20 GB available disk space.
-
Sample databases and datasets.
-
Database development scripts.
-
Java IDE such as IntelliJ IDEA, Eclipse, or equivalent.
Table Of Content
MODULE 1: APACHE DERBY SQL FUNDAMENTALS
1.1 Apache Derby SQL Architecture
1.1.1 Apache Derby SQL Engine Overview
1.1.2 Derby SQL Architecture
1.1.3 SQL Processing and Execution
1.1.4 Embedded and Network SQL Execution
1.1.5 SQL Development Environment
1.2 SQL Data Definition Language
1.2.1 CREATE DATABASE Objects
1.2.2 CREATE TABLE Statements
1.2.3 ALTER TABLE Operations
1.2.4 DROP and TRUNCATE Operations
1.2.5 Managing Database Objects
1.3 SQL Data Manipulation
1.3.1 INSERT Statements
1.3.2 UPDATE Statements
1.3.3 DELETE Statements
1.3.4 MERGE Operations
1.3.5 Managing Data Modification Operations
MODULE 2: APACHE DERBY ADVANCED SQL PROGRAMMING
2.1 Querying Derby Databases
2.1.1 SELECT Statement Fundamentals
2.1.2 Filtering and Sorting Data
2.1.3 DISTINCT and Result Processing
2.1.4 Expressions and Operators
2.1.5 Advanced Query Construction
2.2 Joins and Set Operations
2.2.1 INNER JOIN Operations
2.2.2 OUTER JOIN Operations
2.2.3 CROSS JOIN Operations
2.2.4 UNION and UNION ALL
2.2.5 INTERSECT and EXCEPT Operations
2.3 Aggregation and Subqueries
2.3.1 Aggregate Functions
2.3.2 GROUP BY and HAVING
2.3.3 Scalar Subqueries
2.3.4 Correlated Subqueries
2.3.5 EXISTS and NOT EXISTS
2.4 Advanced SQL Expressions
2.4.1 CASE Expressions
2.4.2 NULL Handling
2.4.3 String Functions
2.4.4 Date and Time Functions
2.4.5 Numeric Functions and Expressions
MODULE 3: APACHE DERBY DATA MODELING AND SCHEMA DESIGN
3.1 Relational Data Modeling
3.1.1 Relational Database Concepts
3.1.2 Entities and Attributes
3.1.3 Relationships Between Entities
3.1.4 Logical Data Models
3.1.5 Physical Data Models
3.2 Database Normalization
3.2.1 First Normal Form
3.2.2 Second Normal Form
3.2.3 Third Normal Form
3.2.4 Denormalization Considerations
3.2.5 Normalization Best Practices
3.3 Keys and Constraints
3.3.1 Primary Keys
3.3.2 Foreign Keys
3.3.3 Unique Constraints
3.3.4 NOT NULL Constraints
3.3.5 CHECK Constraints
3.4 Schema Implementation
3.4.1 Creating Schemas
3.4.2 Designing Tables
3.4.3 Defining Relationships
3.4.4 Managing Referential Integrity
3.4.5 Schema Design Best Practices
MODULE 4: APACHE DERBY DATABASE OBJECTS
4.1 Views
4.1.1 Understanding Database Views
4.1.2 Creating Views
4.1.3 Querying Views
4.1.4 Managing Views
4.1.5 View Design Considerations
4.2 Indexes
4.2.2 Creating Indexes
4.2.3 Managing Indexes
4.2.4 Index Design Considerations
4.2.5 Index Usage in Query Development
4.3 Sequences and Generated Values
4.3.1 Sequence Concepts
4.3.2 Creating and Managing Sequences
4.3.3 Generated Identity Values
4.3.4 Using Generated Values in Applications
4.3.5 Sequence Design Considerations
MODULE 5: APACHE DERBY TRANSACTIONS AND CONCURRENCY
5.1 Transaction Management
5.1.1 Understanding Derby Transactions
5.1.2 Transaction Boundaries
5.1.3 COMMIT Operations
5.1.4 ROLLBACK Operations
5.1.5 Savepoints and Transaction Control
5.2 Concurrency Control
5.2.1 Understanding Concurrency Concepts
5.2.2 Locking Mechanisms
5.2.3 Transaction Isolation Levels
5.2.4 Lock Contention
5.2.5 Deadlock Management
5.3 Transaction-Oriented Application Design
5.3.1 Designing Reliable Transactions
5.3.2 Managing Long-Running Transactions
5.3.3 Transaction Error Handling
5.3.4 Transaction Performance Considerations
5.3.5 Transaction Management Best Practices
MODULE 6: APACHE DERBY STORED PROCEDURES AND FUNCTIONS
6.1 Stored Procedure Development
6.1.1 Stored Procedure Concepts
6.1.2 Creating Stored Procedures
6.1.3 Procedure Parameters
6.1.4 Executing Stored Procedures
6.1.5 Managing Stored Procedures
6.2 User-Defined Functions
6.2.1 Function Concepts
6.2.2 Creating Database Functions
6.2.3 Function Parameters and Return Values
6.2.4 Using Functions in SQL Statements
6.2.5 Managing User-Defined Functions
6.3 Database Logic Development
6.3.1 Implementing Reusable Database Logic
6.3.2 Java-Based Database Procedures
6.3.3 Error Handling in Database Logic
6.3.4 Testing Database Procedures and Functions
6.3.5 Database Logic Development Best Practices
MODULE 7: APACHE DERBY ADVANCED DATABASE DEVELOPMENT
7.1 Advanced SQL Development
7.1.1 Complex Query Design
7.1.2 Nested Query Development
7.1.3 Common Query Patterns
7.1.4 Reusable SQL Components
7.1.5 Advanced SQL Development Practices
7.2 Database Application Design
7.2.1 Database-Driven Application Architecture
7.2.2 Database Access Layer Design
7.2.3 Data Validation Strategies
7.2.4 Error and Exception Handling
7.2.5 Application Database Design Practices
7.3 Database Development Lifecycle
7.3.1 Development Environment Setup
7.3.2 Schema Development
7.3.3 SQL Development and Testing
7.3.4 Database Change Management
7.3.5 Deployment Preparation
MODULE 8: APACHE DERBY DATA INTEGRITY AND VALIDATION
8.1 Data Integrity
8.1.1 Entity Integrity
8.1.2 Referential Integrity
8.1.3 Domain Integrity
8.1.4 Constraint-Based Validation
8.1.5 Data Integrity Best Practices
8.2 Data Validation
8.2.1 Input Validation
8.2.2 Database-Level Validation
8.2.3 Application-Level Validation
8.2.4 Handling Invalid Data
8.2.5 Data Quality Management
MODULE 9: APACHE DERBY SQL DEVELOPMENT TESTING
9.1 SQL Testing Strategies
9.1.1 Unit Testing SQL Statements
9.1.2 Testing Database Objects
9.1.3 Testing Stored Procedures
9.1.4 Testing Transaction Behavior
9.1.5 Regression Testing
9.2 Database Development Debugging
9.2.1 Identifying SQL Errors
9.2.2 Interpreting Derby Error Messages
9.2.3 Debugging Database Logic
9.2.4 Diagnosing Data Integrity Problems
9.2.5 SQL Troubleshooting Methodology
MODULE 10: APACHE DERBY DATABASE DEVELOPMENT BEST PRACTICES
10.1 SQL Development Standards
10.1.1 SQL Coding Standards
10.1.2 Naming Conventions
10.1.3 Query Readability
10.1.4 Reusable SQL Design
10.1.5 SQL Documentation
10.2 Schema Development Standards
10.2.1 Schema Naming Standards
10.2.2 Table and Column Design
10.2.3 Constraint Standards
10.2.4 Relationship Design Standards
10.2.5 Schema Documentation
MODULE 11: APACHE DERBY DATABASE DEVELOPMENT CAPSTONE LAB
11.1 Database Design Lab
11.1.1 Designing a Relational Data Model
11.1.2 Creating Database Schemas
11.1.3 Implementing Tables and Constraints
11.1.4 Creating Views and Indexes
11.1.5 Validating Database Design
11.2 SQL Development Lab
11.2.1 Developing Complex SQL Queries
11.2.2 Implementing Joins and Subqueries
11.2.3 Implementing Aggregation
11.2.4 Developing Database Functions
11.2.5 Testing SQL Operations
11.3 End-to-End Database Development Project
11.3.1 Designing the Database
11.3.2 Implementing the Database Schema
11.3.3 Developing SQL and Database Logic
11.3.4 Testing Database Functionality
11.3.5 Completing the Database Development Assessment
CONCLUSION
This training develops practical skills in Apache Derby SQL programming, relational data modeling, schema design, database object management, transaction handling, stored procedures, and database application development. Participants gain hands-on experience designing, implementing, testing, and maintaining database solutions.
Reviews
There are no reviews yet.