BDD 1 : Introduction to databases

What you will learn

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).

Details

72h

3 sessions

Teacher

Pole SIG