Fundamentals and Modeling

The relational model is the foundation of practically every database you’ll use in your career. Created by Edgar F. Codd at IBM in 1970, it organizes data into tables (called relations) with well-defined rows and columns. Each table represents a real-world entity.

-- Exemplo: tabela cliente
-- Cada linha = um cliente, cada coluna = um atributo

CREATE TABLE cliente (
  id    SERIAL PRIMARY KEY,    -- Coluna: identificador unico
  nome  TEXT NOT NULL,          -- Coluna: nome obrigatorio
  email TEXT UNIQUE             -- Coluna: email unico
);

-- Inserindo uma "tupla" (linha/registro)
INSERT INTO cliente (nome, email)
VALUES ('Ana Silva', 'ana@email.com');

Keys are the mechanism that gives records an identity and connects tables to each other. The primary key (PK) ensures that each record is unique. The foreign key (FK) creates a link between tables by referencing the PK of another table.

-- PK: identifica o cliente
CREATE TABLE cliente (
  id    SERIAL PRIMARY KEY,
  nome  TEXT NOT NULL,
  email TEXT UNIQUE
);

-- FK: conecta pedido ao cliente
CREATE TABLE pedido (
  id         SERIAL PRIMARY KEY,
  cliente_id INT NOT NULL REFERENCES cliente(id)
               ON DELETE CASCADE,
  total      NUMERIC(10,2) NOT NULL,
  criado_em  TIMESTAMP DEFAULT NOW()
);

-- Tentativa de inserir pedido com cliente inexistente:
-- INSERT INTO pedido (cliente_id, total) VALUES (999, 50.00);
-- ERRO: violacao de chave estrangeira!

Constraints are declarative rules in the schema that prevent invalid data from entering the database. They are the last line of defense provides integrity, even if the application has bugs. The database rejects data that violates the constraints.

CREATE TABLE produto (
  id        SERIAL PRIMARY KEY,
  nome      TEXT NOT NULL,                    -- obrigatorio
  sku       TEXT UNIQUE NOT NULL,             -- unico e obrigatorio
  preco     NUMERIC(10,2) NOT NULL
            CHECK (preco > 0),               -- deve ser positivo
  estoque   INT NOT NULL DEFAULT 0
            CHECK (estoque >= 0),            -- nao pode ser negativo
  ativo     BOOLEAN DEFAULT true              -- padrao: ativo
);

-- Tentativas que o banco REJEITA:
-- INSERT INTO produto (nome, sku, preco) VALUES ('X', 'ABC', -10);
-- ERRO: violacao de CHECK (preco > 0)

-- INSERT INTO produto (sku, preco) VALUES ('DEF', 25.00);
-- ERRO: violacao de NOT NULL (nome)

Normalization is the process of reorganizing tables to eliminate redundancy and prevent anomalies. It follows progressive steps called normal forms. In practice, reaching 3NF solves most problems.

-- ANTES (nao normalizado, violando 1FN):
-- | id | nome | telefones          |
-- | 1  | Ana  | 11-9999, 11-8888   |  <- lista em uma celula!

-- DEPOIS (normalizado, 1FN):
CREATE TABLE cliente (
  id   SERIAL PRIMARY KEY,
  nome TEXT NOT NULL
);

CREATE TABLE telefone (
  id         SERIAL PRIMARY KEY,
  cliente_id INT REFERENCES cliente(id),
  numero     TEXT NOT NULL
);

-- Cada telefone e um registro separado
INSERT INTO telefone (cliente_id, numero) VALUES (1, '11-9999');
INSERT INTO telefone (cliente_id, numero) VALUES (1, '11-8888');

Before writing SQL, you model. The Entity-Relationship (ER) Diagram is the database blueprint. It shows which entities exist, what attributes each has, and how they relate to one another.

-- Exemplo de N:N: aluno cursa disciplina
-- Precisa de tabela associativa "matricula"

CREATE TABLE aluno (
  id   SERIAL PRIMARY KEY,
  nome TEXT NOT NULL
);

CREATE TABLE disciplina (
  id   SERIAL PRIMARY KEY,
  nome TEXT NOT NULL
);

-- Tabela ponte (resolve o N:N)
CREATE TABLE matricula (
  aluno_id      INT REFERENCES aluno(id),
  disciplina_id INT REFERENCES disciplina(id),
  semestre      TEXT NOT NULL,
  PRIMARY KEY (aluno_id, disciplina_id, semestre)
);

A relational database isn't the answer to everything. Knowing when to use it and when not to is just as important as knowing SQL. The right choice depends on the nature of the data and the system requirements.