Bancos de Dados

Antes dos bancos de dados, os programas armazenavam dados em arquivos planos 平面文件 — geralmente um arquivo por programa. Isso é fine para pequenos dados, mas falha em escala.

Limitações

• redundância de dados 数据冗余 — os mesmos dados (o endereço de um cliente) estão em vários arquivos, um por programa, desperdiçando armazenamento e exigindo atualização de todas as cópias.
• inconsistência de dados 数据不一致 — quando uma cópia é atualizada e outra não, os arquivos divergem 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 完整性 — nenhuma regra central impede 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 isso armazenando dados em tabelas gerenciadas por um único software (DBMS) que todos os programas usam.

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, ligando 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 é indexado para busca rápida.
• indexação 索引 — criar um índice em um campo para que buscas e junções sejam mais rápidas.
• integridade referencial 参照完整性 — todo valor de chave estrangeira deve corresponder a uma chave primária existente (nenhum registro órfão).

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 relacionamento é 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.

Um relacionamento muitos-para-muitos não pode ser armazenado diretamente. Divida-o em dois relacionamentos 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, passando pelas formas normais 范式 em ordem.

• Primeira forma normal (1NF) — cada campo contém um único valor (atômico 原子), sem grupos repetidos, e possui 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 (3NF) — em 2NF, e cada campo não-chave depende apenas da chave primária, não de outro campo não-chave (não há 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 para fazer a qualquer tabela. Cada célula tem um único valor, sem grupo repetido? Se não, não está em 1NF. Se a chave for composta, todo campo não-chave depende da chave inteira? Se algum campo depender de parte dela, há uma dependência parcial 部分依赖 e a tabela não está em 2NF. Todo campo não-chave depende apenas da chave? Se um campo depender de outro campo não-chave, há uma dependência transitiva e a tabela não está em 3NF. Uma resposta "explique por que a tabela não está em 3NF" nomeia 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. Funcionalidades que corrigem os limites baseados em arquivos:

• dicionário de dados 数据字典 — uma descrição de cada tabela, campo, tipo e chave; programas consultam-no em vez de codificar a estrutura fixamente.
• controle de redundância/consistência — cada fato armazenado uma única vez.
• controle de acesso concorrente 并发访问 — bloqueios e transações permitem que muitos usuários trabalhem simultaneamente.
• backup 备份 e recuperação; segurança e permissões por usuário.
• regras de integridade — chaves, restrições de unicidade e intervalo, aplicadas centralmente.
• transactions 事务 — um grupo de operações em que todas têm sucesso ou todas falham.
• visualizações 视图 — tabelas virtuais que mostram a cada usuário "sua" fatia dos dados.
• data management 数据管理 e data modelling 数据建模 — controlam como os dados são armazenados e definem sua estrutura como um logical schema 逻辑模式 (o projeto lógico, independente do armazenamento físico).
• data integrity 数据完整性 e data security 数据安全 — impõem correção e controlam o acesso centralmente.
• um query processor 查询处理器 executa consultas; uma developer interface 开发者接口 fornece ferramentas e APIs para construir 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 DBMS mantém os dados seguros (uma questão "descrever dois métodos"): authentication 身份验证 — um nome de usuário e senha, ou biométrico, antes de qualquer acesso; access rights — cada usuário ou grupo tem permissão para ler, escrever ou excluir apenas certas tabelas ou campos, muitas vezes através de uma view; encryption dos dados armazenados e dos dados enviados a eles, para que um arquivo copiado seja ilegível; backups feitos regularmente, para que os dados possam ser restaurados após perda; e um log 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 结构化查询语言 (Structured Query Language) possui duas partes:

• Data Definition Language 数据定义语言 (DDL) — cria ou altera a estrutura (tabelas, chaves, restrições).
• Data Manipulation Language 数据操纵语言 (DML) — trabalha com os dados (inserir, atualizar, excluir, query 查询).

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 um relacionamento 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输出每个技术人员姓名及其执行的维修总成本，每行一个技术人员：两个表通过TechnicianID连接，行按姓名分组，每组中的成本相加。当被问及脚本做什么时，描述结果，而不是语法。

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.