Training Program: Introduction to Databases
Duration:
3 days (21 hours)
Learning Objectives:
- Understand the fundamental concepts of relational databases.
- Learn the basics of data modeling (conceptual and logical).
- Install and configure PostgreSQL/PostGIS and Oracle databases.
- Manipulate databases using SQL (create, query, update).
- Explore spatial features (PostGIS).
Target Audience:
- Developers, analysts, system administrators, or anyone looking to get started with databases.
Prerequisites:
- Basic knowledge of IT (operating systems, programming concepts).
Detailed Program
Day 1: Introduction to Relational Databases
Morning: Fundamental Concepts
- What is a database?
- Differences between relational and non-relational databases.
- Overview of major DBMSs: PostgreSQL, Oracle, MySQL, etc.
- Key concepts: tables, columns, rows, primary keys, foreign keys, and relationships.
Practical Workshop:
- Installing and configuring PostgreSQL and Oracle (basic setup).
- Exploring graphical interfaces (pgAdmin, Oracle SQL Developer).
Afternoon: Data Modeling
- Introduction to conceptual modeling (Merise, UML).
- Creating a Conceptual Data Model (CDM).
- Translating the CDM into a Logical Data Model (LDM).
- Basics of database normalization (1NF, 2NF, 3NF).
Practical Workshop:
- Implementing a simple case study: modeling a database for a management system (e.g., library, inventory management).
Day 2: Introduction to SQL (Structured Query Language)
Morning: SQL Basics
- Structure of SQL commands.
- Data types (integers, strings, dates, etc.).
- DDL (Data Definition Language) commands:
- Creating tables.
- Modifying and deleting tables.
Practical Workshop:
- Creating and modifying a database schema.
Afternoon: Data Manipulation with SQL
- DML (Data Manipulation Language) commands:
- Inserting data (
INSERT).
- Reading data (
SELECT).
- Filtering data with
WHERE, sorting with ORDER BY, and aggregating with GROUP BY.
- Updating and deleting data (
UPDATE, DELETE).
Practical Workshop:
- Creating a dataset.
- Writing simple queries to manipulate and retrieve data.
Day 3: Advanced PostgreSQL, PostGIS, and Introduction to Oracle
Morning: Advanced PostgreSQL and PostGIS
- Transactions and user management.
- Indexing for performance optimization (
CREATE INDEX).
- Introduction to PostGIS: concepts of spatial databases.
- Spatial data types (points, lines, polygons).
- Basic spatial queries (distance, intersection).
Practical Workshop:
- Adding geographic data to a PostgreSQL database.
- Writing spatial queries with PostGIS.
Afternoon: Introduction to Oracle
- Oracle-specific features compared to PostgreSQL.
- Using PL/SQL for automation.
- Comparison of graphical and command-line tools (SQL*Plus, SQL Developer).
Practical Workshop:
- Import/export data between Oracle and PostgreSQL.
- Creating simple stored procedures with PL/SQL.
Teaching Methods:
- Alternating between theoretical lectures, demonstrations, and hands-on workshops.
- Case studies to contextualize learning.
- Training materials provided to participants (slides, exercises, and solutions).
Evaluation and Follow-up:
- Daily quizzes to assess knowledge.
- Final evaluation through a mini-project (database creation, queries).