PV168

Databases I

What is a Database

A database is an organized collection of structured information, or data, typically stored electronically in a computer system.

Multiple database types:

• Relational Databases
  • Oracle DB, PostgreSQL, MySQL, MS SQL, H2
• NoSQL Databases (non-relational)
  • MongoDB, ElasticSearch, Redis, DynamoDB
• Other

Why we need Databases

• Storing data (persistence, state, replication)
• Structure (schema, relations)
• Transactions (atomicity)
• Data Integrity (foreign keys, relationships, constraints)
• Access Control (locks, permissions)
• Availability

JDBC – Java Database Connectivity

• API for communication with RDBMS
• Each DB has its own driver
  • Enables Java application to interact with the DB
• Introduced in Java 1.1
  • Stable yet older API
  • With its own quirks

JDBC URI

Examples:

• MySQL: jdbc:mysql://server:port/dbname?characterEncoding=UTF-8
• Postgres: jdbc:postgresql://host:port/database

H2 Database JDBC URI for specific modes:

• H2 (in-memory): jdbc:h2:mem:database_name
• H2 (embedded): jdbc:h2:~/file/path/database.db;PROP=value
• H2 (server): jdbc:h2:tcp://localhost/~/db_name

SQL - Structured Query Language

Types:

• Data Query Language (DQL) - SELECT
• Data Manipulation Language (DML) - INSERT, UPDATE, DELETE
• Data Definition Language (DDL) - CREATE, ALTER, DROP
• Data Control Language (DCL) - GRANT, DENY (access)
• WARNING: Most of the DDL and DCL are implementation-specific
  • Depend on the RDBMS you are using

Create a new Department Table

-- create a new Department table
CREATE TABLE IF NOT EXISTS Department
(
    id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    number    VARCHAR(10) NOT NULL UNIQUE,
    name      VARCHAR(50) NOT NULL,
    createdAt TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Create a new Employee Table

-- create a new Emplotee table
CREATE TABLE IF NOT EXISTS Employee
(
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    firstName    VARCHAR(150) NOT NULL,
    lastName     VARCHAR(150) NOT NULL,
    birthDate    DATE         NOT NULL,
    departmentId BIGINT REFERENCES Department (id),
    createdAt    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Primary Key

• Identification of objects (UNIQUE, NOT NULL, immutable)
• Should be synthetic
  • Identity (since SQL:2003):
    • id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
    • id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • Seqence: CREATE SEQUENCE Department_Id_Seq AS BIGINT START WITH 1;
  • UUID

Relationships

Are used for collections or compositions:

• One-to-One
• One-to-Many
• Many-to-Many

Foreign Key

FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY of another table.

• Examples:
  • departmentId BIGINT REFERENCES Department (id),
  • FOREIGN KEY (Department_Id_FK) REFERENCES Department (id)

Constraints and Default Indexes

Constraints:

• UNIQUE - column values must be unique
• CONSTRAINT Employee_Name_UC UNIQUE (firstName, lastName)

Index is automatically created for:

• Primary Key
• Unique Constraints

Cascades

• Task 6: What happened on DELETE a department with employees?
• What might help: ON DELETE CASCADE:
  • departmentId BIGINT REFERENCES Department (id) ON DELETE CASCADE,
• Cascades have problems
  • They might cause an outage 🤣
  • Use with extra care! (never as default option)

Information Schema

• Bonus task
• Schema: INFORMATION_SCHEMA
  • Provides information about all the tables, views, columns, indexes, relationships etc. in the DB
• Examples:
  • SELECT * FROM INFORMATION_SCHEMA.INDEXES;
  • SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'Department';

Migrations

• Way to manage database changes
• Use existing tools for it
  • Java: Liquibase
  • Python: Alembic
  • GO: Migrate
• We will not be covering the migration on this course

Logging

Why Logging

• To keep track of what happened in the past
• To be able to get back to it in the future
  • In real systems it is a MUST
  • Some defects cannot be solved otherwise

What to log

• Every high-level operation being invoked
  • And most of the intermediate operations
• Code paths diverging from the happy path
  • Stack trace is a must when logging exceptions!
• Various log levels are typically available
  • E.g. TRACE, DEBUG, INFO, WARN, ERROR, FATAL

How to log

• Various APIs and libraries available in Java
  • java.util.logging not being the best option
  • Log4J and SLF4J much more usable/common
  • tinylog is extremely simple yet usable
• Output to console is not the most practical
  • Persisting somewhere is better (file, DB, …)
  • Housekeeping is a must!