Bancos de Dados

Antes dos bancos de dados, os programas armazenavam dados em arquivos planos — geralmente um arquivo por programa. Isso funciona bem para pequenos volumes de dados, mas falha em escala maior.

Limitações

• redundância de dados — os mesmos dados (o endereço de um cliente) estão mantidos em vários arquivos, um por programa, resultando em armazenamento desperdiçado e exigindo atualização de cada cópia.
• inconsistência de dados — quando uma cópia é atualizada e outra não, os arquivos entram em conflito e ninguém sabe qual está correta.
• dependência de dados — cada programa é escrito para o layout exato de seus arquivos; alterar o comprimento de um campo ou adicionar um campo exige reescrever todo programa que lê o arquivo.
• sem acesso compartilhado — um arquivo é bloqueado enquanto um programa o usa, impedindo que usuários trabalhem nos dados simultaneamente.
• integridade fraca — sem regras centrais que impeçam valores inválidos ou links para clientes inexistentes; segurança fraca — o acesso é por arquivo, não por campo; e consultas entre arquivos exigem um novo programa cada vez.

Um banco de dados relacional corrige esses problemas ao armazenar dados em tabelas gerenciadas por um único software (SGBD) utilizado por todos os programas.

Por que um banco de dados relacional é melhor — a resposta de três pontos. Cada item de dado é armazenado uma única vez, em uma tabela, e as tabelas são ligadas por chaves, não havendo redundância nem inconsistência; os dados são independentes dos programas, que solicitam ao SGBD o que precisam e permanecem inalterados quando a estrutura muda; e o SGBD aplica regras de integridade, controla o acesso por usuário e por campo, permite múltiplos usuários simultaneamente e executa qualquer consulta sem a necessidade de escrever um novo programa.

Exemplo resolvido. Uma oficina armazena seus clientes, dispositivos e serviços de reparo usando uma abordagem baseada em arquivos, com um arquivo por programa. Cite três problemas isso causa e descreva como um banco de dados relacional os eliminaria.

O nome e o número de telefone do cliente estão armazenados no arquivo de reparos e também no arquivo de faturas (redundância); quando um cliente muda de número, um arquivo é atualizado e o outro não (inconsistência); e quando a loja quer um novo relatório — reparos por técnico — um novo programa precisa ser escrito para ler os arquivos (sem consultas ad hoc). Em um banco de dados relacional, o cliente é armazenado uma única vez em uma tabela CUSTOMER e referenciado pelo CustomerID na tabela REPAIR, então a alteração é feita uma única vez e vista em todos os lugares; o relatório é uma única consulta SQL.

• tabela (relação) — uma grade de linhas e colunas; uma tabela por tipo de entidade (ex. CUSTOMER).
• registro (linha, também chamado de tupla) — uma linha; uma instância da entidade.
• campo (coluna, também chamado de atributo) — uma coluna; uma informação sobre cada registro.
• chave primária — um campo (ou campos) que identifica unicamente cada registro; nunca nulo ou duplicado.
• chave estrangeira — um campo cujo valor corresponde à chave primária de outra tabela, vinculando as duas.
• chave composta — uma chave primária formada por dois ou mais campos juntos.
• chave candidata — qualquer campo(s) que poderia ser a chave primária.
• chave secundária — um campo não primário que possui índice para busca rápida.
• indexação — criação de um índice em um campo para tornar buscas e junções mais rápidas.
• integridade referencial — todo valor de chave estrangeira deve corresponder a uma chave primária existente (sem registros órfãos).

Uma tabela é escrita em notação resumida com a chave primária sublinhada e chaves estrangeiras anotadas:

CUSTOMER(CustomerID, Name, Phone)
ORDER(OrderID, CustomerID, OrderDate)   -- CustomerID is FK → CUSTOMER

Exemplo resolvido. Explique o significado de entidade, chave primária e integridade referencial em um banco de dados relacional, e complete a tabela de termo ↔ descrição para tupla e atributo.

Uma entidade é algo sobre o qual dados são armazenados — uma pessoa, objeto ou evento — que se torna uma tabela. Uma chave primária é o atributo (ou combinação de atributos) que identifica unicamente cada registro em uma tabela. Integridade referencial significa que todo valor de chave estrangeira deve corresponder ao valor de uma chave primária na tabela a que se refere, para que um registro não possa apontar para um que não existe. Uma tupla é uma linha de uma tabela (um registro); um atributo é uma coluna (um campo). Aprenda os pares: tabela/relação, registro/tupla, campo/atributo.

Um diagrama de entidade-relacionamento mostra a estrutura: cada entidade é um retângulo, cada relação uma linha, com a cardinalidade marcada em cada extremidade:

• um-para-um (1:1).
• um-para-muitos (1:M) — cada Cliente tem muitos Pedidos; cada Pedido tem um Cliente.
• muitos-para-muitos (M:N) — Estudantes frequentam muitos Cursos, e Cursos têm muitos Estudantes.

Uma relação muitos-para-muitos não pode ser armazenada diretamente. Divida-a em duas relações um-para-muitos através de uma tabela de ligação contendo as duas chaves estrangeiras:

ENROLMENT(StudentID, CourseID, EnrolmentDate)

Desenhando o diagrama E-R para um conjunto dado de tabelas. Cada tabela torna-se uma entidade. Um relacionamento existe sempre que uma tabela contenha uma chave estrangeira para outra; ele vai da tabela que contém a chave estrangeira (a extremidade muitos) para a tabela cuja chave primária ela é (a extremidade um). Uma tabela com duas chaves estrangeiras e nenhuma outra identidade geralmente é uma tabela de ligação resolvendo um relacionamento muitos-para-muitos. Roteie cada linha com o tipo de relacionamento.

Exemplo resolvido. Uma oficina possui as tabelas CUSTOMER(CustomerID, Name, Phone), DEVICE(DeviceID, CustomerID, Type, Model), TECHNICIAN(TechnicianID, Name) e REPAIR(RepairID, DeviceID, TechnicianID, RepairDate, Cost). Identifique os relacionamentos e seus tipos.

DEVICE contém CustomerID, então CUSTOMER–DEVICE é um-para-muitos (um cliente, muitos dispositivos). REPAIR contém DeviceID, então DEVICE–REPAIR é um-para-muitos; também contém TechnicianID, então TECHNICIAN–REPAIR é um-para-muitos. Não há linha direta CUSTOMER–REPAIR: o link passa por DEVICE. Três linhas, três pés-de-galinha, todos nas extremidades REPAIR ou DEVICE.

Normalização organiza tabelas para reduzir redundância e inconsistência, seguindo as formas normais em sequência.

• Primeira forma normal (1FN) — cada campo contém um único valor (atômico), sem grupos repetidos, e com uma chave primária.
• Segunda forma normal (2NF) — em 1NF, e cada campo não-chave depende da totalidade da chave primária (importa apenas para uma chave composta).
• Terceira forma normal (3FN) — na 2FN, e todos os campos não-chave dependem apenas da chave primária, não de outro campo não-chave (sem dependência transitiva).

Um design 3NF armazena cada fato uma única vez, assim anomalias de inserção/atualização/exclusão desaparecem. A contrapartida é mais tabelas e mais junções. Almeje 3NF.

Para produzir um design 3NF: encontre as entidades e seus atributos; escolha uma chave primária para cada; divida campos repetidos/não-atômicos (1NF); divida campos que dependem de parte de uma chave composta (2NF); divida campos que dependem transitivamente da chave (3NF); adicione chaves estrangeiras para os relacionamentos.

Exemplo resolvido. A tabela ORDER(OrderID, CustomerID, CustomerName, ProductID, Quantity) tem a chave primária composta (OrderID, ProductID). Normalize-a para 3NF. Teste cada campo não-chave contra a chave. Quantity depende de ambos OrderID e ProductID, o que está correto. Mas CustomerID depende de OrderID sozinho - apenas parte da chave composta. Isso é uma dependência parcial, então a tabela não está em 2NF. Divida-a em ORDER_LINE(OrderID, ProductID, Quantity) e ORDER(OrderID, CustomerID, CustomerName). Agora teste 3NF: naquela nova tabela ORDER, CustomerName depende de CustomerID, que não é a chave - uma dependência transitiva. Divida novamente: ORDER(OrderID, CustomerID) e CUSTOMER(CustomerID, CustomerName). Nomeie a dependência que viola cada forma (parcial viola 2NF, transitiva viola 3NF); "ela tem dados repetidos" descreve o sintoma e não ganha pontos.

As três perguntas a fazer sobre qualquer tabela. Cada célula é um único valor, sem grupo repetido? Se não, não está na 1FN. Se a chave é composta, todo campo não-chave depende da chave inteira? Se algum campo depende apenas de parte dela, há uma dependência parcial e a tabela não está na 2FN. Todo campo não-chave depende apenas da chave? Se um campo depende de outro campo não-chave, há uma dependência transitiva e a tabela não está na 3FN. Uma resposta "explique por que a tabela não está na 3FN" deve nomear a dependência e os campos envolvidos.

Exemplo resolvido. Uma loja de aluguel de carros registra cada aluguel como RENTAL(RentalID, RentalDate, CustomerID, CustomerName, CustomerPhone, CarReg, CarModel, DailyRate, Days), onde um aluguel pode incluir vários carros. Explique por que a tabela não está normalizada e produza um design 3NF.

Não em 1NF: os campos de carro CarReg, CarModel, DailyRate, Days formam um grupo repetido — um aluguel tem vários carros. Mova-os para RENTAL_CAR(RentalID, CarReg, CarModel, DailyRate, Days) com a chave composta (RentalID, CarReg). Não em 2NF: em RENTAL_CAR, CarModel e DailyRate dependem de CarReg sozinho — uma dependência parcial. Mova-os para CAR(CarReg, CarModel, DailyRate), deixando RENTAL_CAR(RentalID, CarReg, Days). Não em 3NF: em RENTAL, CustomerName e CustomerPhone dependem de CustomerID, um campo não-chave — uma dependência transitiva. Mova-os para CUSTOMER(CustomerID, CustomerName, CustomerPhone), deixando RENTAL(RentalID, RentalDate, CustomerID). O design 3NF é quatro tabelas — CUSTOMER, RENTAL, RENTAL_CAR, CAR — com CustomerID, RentalID e CarReg como chaves estrangeiras; sublinhe todas as chaves primárias.

Um SGBD gerencia o banco de dados centralmente. Recursos que superam as limitações dos arquivos:

• dicionário de dados — descrição de todas as tabelas, campos, tipos e chaves; programas consultam-no em vez de codificar a estrutura manualmente.
• controle de redundância/consistência — cada fato armazenado uma única vez.
• controle de acesso simultâneo — bloqueios e transações permitem que vários usuários trabalhem ao mesmo tempo.
• backup e recuperação; segurança e permissões por usuário.
• regras de integridade — chaves, restrições de unicidade e intervalo, aplicadas centralmente.
• transações — um conjunto de operações que todas têm sucesso ou todas falham.
• vistas — tabelas virtuais que mostram a cada usuário "sua" fatia dos dados.
• gestão de dados e modelagem de dados — controlam como os dados são armazenados e definem sua estrutura como um esquema lógico (o projeto lógico, independente do armazenamento físico).
• integridade de dados e segurança de dados — garantem correção e controlam o acesso centralmente.
• um processador de consultas executa queries; uma interface de desenvolvedor fornece ferramentas e APIs para criar aplicativos.

Suas ferramentas incluem um editor de dicionário de dados, um construtor de consultas, um construtor de formulários, um gerador de relatórios, gerenciamento de usuários e um editor SQL.

O que o dicionário de dados armazena (uma questão "listar três itens"): os nomes das tabelas; os nomes dos campos em cada tabela; o tipo de dado e o comprimento de cada campo; as chaves primárias e estrangeiras e os relacionamentos entre tabelas; regras de validação; índices; e quem pode acessar cada tabela. É metadado — dados sobre os dados — e o DBMS o usa para verificar cada consulta e cada alteração.

Como o SGBD mantém os dados seguros (uma questão "descreva dois métodos"): autenticação — nome de usuário e senha, ou biométrica, antes de qualquer acesso; direitos de acesso — cada usuário ou grupo tem permissão para ler, escrever ou excluir apenas tabelas ou campos específicos, frequentemente através de uma vista; criptografia dos dados armazenados e dos dados transmitidos, para que um arquivo copiado seja ilegível; backups realizados regularmente, permitindo restauração após perda; e um registro de transações que registra quem alterou o quê.

As duas ferramentas de software. A developer interface é o que um programador usa para construir o banco de dados e os aplicativos sobre ele: criar tabelas e definir chaves e validações, escrever consultas e SQL, e projetar formulários e relatórios, sem saber como os dados são armazenados fisicamente. O query processor recebe uma consulta (SQL de um programa ou uma consulta construída na interface), verifica-a contra o dicionário de dados, determina a maneira mais eficiente de executá-la, recupera os dados e retorna os resultados.

Logical schema. O DBMS mantém o projeto lógico (quais tabelas e campos existem e como se relacionam) separado do physical storage (arquivos, índices, blocos de disco). Os programas trabalham com o schema lógico, para que o armazenamento físico possa ser reorganizado sem alterar um único programa — esta é a independência de dados que a abordagem baseada em arquivos não possuía.

SQL (Linguagem de Consulta Estruturada) tem duas partes:

• Linguagem de Definição de Dados (DDL) — cria ou altera a estrutura (tabelas, chaves, restrições).
• Linguagem de Manipulação de Dados (DML) — opera sobre os dados (inserir, atualizar, excluir, consultar).

Noções básicas de DDL

CREATE TABLE CUSTOMER (
  CustomerID INTEGER PRIMARY KEY,
  Name VARCHAR(50) NOT NULL,
  Phone VARCHAR(20)
);

Adicionar uma chave estrangeira:

CREATE TABLE ORDER (
  OrderID INTEGER PRIMARY KEY,
  CustomerID INTEGER,
  OrderDate DATE,
  FOREIGN KEY (CustomerID) REFERENCES CUSTOMER(CustomerID)
);

Modificar e excluir:

ALTER TABLE CUSTOMER ADD Email VARCHAR(100);
DROP TABLE CUSTOMER;

Tipos comuns: INTEGER, REAL, VARCHAR(n), CHAR(n) (também CHARACTER(n)), DATE, TIME, BOOLEAN, DECIMAL(p, s).

Noções básicas de DML

Consulta com SELECT:

SELECT Name, Phone
FROM CUSTOMER
WHERE City = 'London'
ORDER BY Name ASC;

SELECT lista campos, FROM nomeia a tabela, WHERE filtra linhas, ORDER BY ordena.

Um join combina duas tabelas usando uma relação de chave estrangeira:

SELECT C.Name, O.OrderDate
FROM CUSTOMER C INNER JOIN ORDER O
  ON C.CustomerID = O.CustomerID
WHERE O.OrderDate >= '2024-01-01';

Funções agregadas (COUNT, SUM, AVG, MIN, MAX) são frequentemente usadas com GROUP BY:

SELECT CustomerID, COUNT(*) AS NumOrders
FROM ORDER
GROUP BY CustomerID;

Inserir, atualizar, excluir:

INSERT INTO CUSTOMER (CustomerID, Name, Phone)
VALUES (101, 'Ada Lovelace', '020-1234-5678');

UPDATE CUSTOMER SET Phone = '020-9999-0000' WHERE CustomerID = 101;

DELETE FROM CUSTOMER WHERE CustomerID = 101;

Sempre coloque uma cláusula WHERE em UPDATE e DELETE, ou a alteração atingirá todas as linhas.

Dicas para SQL de exame

• use exatamente os nomes de tabela e campo da questão.
• cite strings com aspas simples ('Smith'); não cite números.
• comparações: =, <, >, <=, >=, <>.
• LIKE 'A%' corresponde a tudo que começa com A (% = qualquer string, _ = um caractere); IN (1,2,3); BETWEEN 10 AND 20.
• combine condições com AND / OR / NOT, e termine cada statement com ponto e vírgula.

O padrão DDL que o exame quer. Cada CREATE TABLE nomeia cada campo com seu tipo, marca a chave primária e declara cada chave estrangeira com a tabela que ela referencia; uma chave composta é declarada em sua própria linha:

CREATE TABLE RENTAL_CAR (
  RentalID INTEGER,
  CarReg VARCHAR(8),
  Days INTEGER,
  PRIMARY KEY (RentalID, CarReg),
  FOREIGN KEY (RentalID) REFERENCES RENTAL(RentalID),
  FOREIGN KEY (CarReg) REFERENCES CAR(CarReg)
);

Exemplo resolvido. Usando CUSTOMER(CustomerID, Name, Phone) e DEVICE(DeviceID, CustomerID, Type, Model), escreva scripts SQL para: (a) listar o nome e o número de telefone de todo cliente que possui um dispositivo do tipo 'tablet', em ordem alfabética de nome; (b) contar os dispositivos de cada tipo; (c) registrar que o cliente 17 agora tem o número de telefone '0771 234 5678'; (d) adicionar um novo dispositivo, ID 305, um 'laptop' do modelo 'X1' pertencente ao cliente 17.

(a)

SELECT CUSTOMER.Name, CUSTOMER.Phone
FROM CUSTOMER INNER JOIN DEVICE
  ON CUSTOMER.CustomerID = DEVICE.CustomerID
WHERE DEVICE.Type = 'tablet'
ORDER BY CUSTOMER.Name ASC;

(b)

SELECT Type, COUNT(DeviceID) AS NumberOfDevices
FROM DEVICE
GROUP BY Type;

(c) UPDATE CUSTOMER SET Phone = '0771 234 5678' WHERE CustomerID = 17; (d) INSERT INTO DEVICE (DeviceID, CustomerID, Type, Model) VALUES (305, 17, 'laptop', 'X1');

Pontuação é dada por cláusula — os campos, as tabelas, a condição de join, o WHERE, o ORDER BY — então um script com uma cláusula errada ainda pontua o resto. Escreva Table.Field sempre que duas tabelas estiverem envolvidas.

Exemplo resolvido. Explique o que este script faz: SELECT T.Name, SUM(R.Cost) AS Total FROM TECHNICIAN T INNER JOIN REPAIR R ON T.TechnicianID = R.TechnicianID GROUP BY T.Name;

Ele exibe o nome de cada técnico com o custo total dos reparos realizados por esse técnico, uma linha por técnico: as duas tabelas são unidas por TechnicianID, as linhas são agrupadas por nome e os custos de cada grupo são somados. Quando pedirem o que um script faz, descreva o resultado, não a sintaxe.

Condições e chaves SQL

LIKE 'Li%' corresponde a texto que começa com Li; % representa qualquer quantidade de caracteres e _ exatamente um. AND exige ambas as condições; OR exige pelo menos uma. Use parênteses ao combiná-las.

CREATE DATABASE Club;
CREATE TABLE Booking (
  MemberID INTEGER,
  EventID INTEGER,
  PRIMARY KEY (MemberID, EventID)
);
ALTER TABLE Booking ADD FOREIGN KEY (MemberID)
  REFERENCES Member(MemberID);
SELECT Name FROM Member
WHERE Name LIKE 'Li%' AND (Year = 12 OR Year = 13)
ORDER BY Name DESC;

A chave primária composta torna o par único, não cada coluna separadamente. Uma chave estrangeira impede referência não nula a um registro pai ausente. ON UPDATE CASCADE propaga alteração da chave pai para linhas referenciadoras; ON DELETE CASCADE exclui essas linhas quando o pai é excluído.

Uma questão de definição é avaliada contra wording fixo. Aprenda estas exatamente, e dê apenas uma resposta.

• Defina os termos exatamente: entity, attribute, primary key, foreign key, e os tipos de relacionamento (1:1, 1:muitos, muitos:muitos).
• Dê uma razão em cada forma normal: 1NF (sem grupos repetidos), 2NF (sem dependência parcial), 3NF (sem dependência não-chave) — e nomeie os campos envolvidos.
• Explique o que um DBMS fornece (independência de dados, segurança, integridade, acesso simultâneo, um dicionário de dados, uma interface de desenvolvedor, um processador de consultas).
• Distinga DDL (definir a estrutura) de DML (consultar e alterar os dados), e escreva cláusulas SQL uma por uma: SELECT, FROM, INNER JOIN … ON, WHERE, GROUP BY, ORDER BY.
• Para desenhar um diagrama E-R a partir de tabelas, encontre primeiro cada chave estrangeira: toda chave estrangeira representa um relacionamento de um-para-muitos, com o "muitos" na tabela que a contém.

Erros comuns

• Desenhar um relacionamento de muitos-para-muitos diretamente. Ele deve ser dividido em dois relacionamentos de um-para-muitos através de uma tabela de ligação contendo ambas as chaves estrangeiras.
• Explicando "não em 3NF" como "os dados estão repetidos". Nomeie a dependência (parcial ou transitiva) e os campos envolvidos.
• Aspas duplas em torno de strings no SQL, ou aspas em torno de números. Strings levam 'single quotes'; números não levam nada.
• Deixar de fora a condição ON depois de INNER JOIN. Sem ela, as duas tabelas não estão ligadas.
• Colocar um campo comum ao lado de COUNT ou SUM em um SELECT sem um GROUP BY.
• UPDATE ou DELETE sem um WHERE. Ela altera ou remove todas as linhas da tabela.