Banco de Dados - Alessandro Carneiro

INSTITUTO FEDERAL NORTE DE MINAS GERAIS
Curso Técnico em Informática - Integrado
Disciplina: Banco de Dados
Professor: Alessandro Carneiro Ribeiro
E-mail: [email protected]
Projeto Físico de Banco de Dados
Linguagem SQL – DDL
Create Table
Alter Table
Drop Table
Modelo Físico de Banco de Dados
 Segunda etapa do projeto de Banco de Dados para um SI, cujo objetivo é obter
uma implementação do modelo conceitual do BD. Fase que depende do SGBD.
 Supondo que o SGBD a ser utilizado para implementar o BD é um SGBD
Relacional o modelo físico é composto pelas seguintes atividades:
1. Construção do DER Físico Relacional a partir do DER Conceitual (DER
Lógico).
2. Normalização das tabelas: 1 FN, 2 FN e 3 FN.
3. Construção do Dicionário de Dados.
4. Geração do código SQL para criação do BD.
5. Otimização do BD: Índices, Views, Triggers e Storeds Procedures.
6. Implementação dos esquemas de segurança: Grants.
A LINGUAGEM SQL – STRUCTURED QUERY LANGUAGE:




SQL é uma linguagem padrão de alto nível (não procedural) para SGBDs.
É padronizada pelo ANSI (American National Standards Institute), uma instituição nãogovernamental sem fins lucrativos, patrocinada por organizações de comércio, sociedades
profissionais e pela indústria e membro da Organização de Padrões Internacionais (ISO).
SQL pode ser utilizada para todas as atividades relativas a um BD (DDL e DML) podendo ser
utilizada pelo Analista, pelo DBA, por programadores, sistemas(L4G) e usuários finais.
Para efeitos didáticos e práticos de SQL vamos trabalhar com o seguinte estudo de caso:
Sistema de controle de pessoal de uma empresa
Este sistema tem como objetivo automatizar a área de recursos humanos de um determinada empresa. Os empregados
da empresa são admitidos e ficam lotados em departamentos da empresa. Estes departamentos controlam determinados
projetos. É importante saber quais foram todos os departamentos que um empregado já trabalhou. Existem dois tipos
especiais de empregados: os empregados que são administradores e os empregados que são engenheiros. Os
empregados que são administradores podem gerenciar um departamento e quando isso ocorre eles recebem um
gratificação. Não é importante saber quais foram os gerentes de um dado departamento mas somente quem é o gerente
atual. Os empregados que são engenheiros podem trabalhar em projetos previamente criados e é importante saber o dia,
a hora e a quantidade de horas, que um empregado trabalhou em um projeto. Por questões de controle interno o sistema
deve controlar também qual a máquina um dado empregado que tenha trabalhado em um dado projeto possa ter
utilizado naquele dia. Neste caso a quantidade de horas gastas pelo empregado utilizando a máquina deve ser guardado
no banco de dados. Todo projeto possui um único empregado coordenador e este coordenador só pode ser um
empregado que é do tipo administrador. Se o empregado coordena algum projeto ele também recebe um gratificação
extra. Um empregado pode ser supervisor de vários outros empregados e quem é o supervisor é um fato importante a ser
gerenciado no sistema. Um empregado sempre possui um único cargo na empresa, o que define o seu salário base. De
acordo com sua formação escolar o empregado pode receber uma gratificação extra. A empresa possui amplo plano de
benefícios distribuídos aos funcionários e isto deve ser armazenado no banco de dados. Informações dos dependentes
dos funcionários e o tipo de relação entre o funcionário e seu dependente (exemplo: esposa, filho, etc) deve ser
controlado no sistema. O contra cheque dos empregados da empresa é gerada no final de cada mês com base no salário
base e nas participações do empregado nos projetos, gerência ou coordenação de projetos. Mensalmente devem ser
emitidos relatórios estratégicos para o gerente da empresa como o total da folha de pagamento por departamento, os
empregos demitidos e admitidos no mês, os projetos em andamento com os respectivos coordenadores, a quantidade de
horas trabalhadas por cada empregado em cada projeto, a relação dos benefícios por empregado, a relação de
dependentes por empregado, a relação dos gerentes por departamento, a relação dos supervisores por empregado,
relação das máquinas utilizadas por um dado empregado classificados pelo projeto e pela data, etc.
DER Conceitual (Feito no System Architect 2001)
Entidades e Atributos
Empresa(@CodEmpresa, NomeFantasia, CGC, Endereco, Telefone)
FormacaoEscolar (@CodFormacao, Descricao, GratificacaoFormacao)
Cargo(@CodCargo, Descricao, SalarioBase, GratificacaoCargo)
Beneficio(@CodBeneficio, Descricao, Valor)
Departamento(@CodDepto, Descricao, Localizacao)
Funcionario(@CodFunc, Nome, Endereco, Telefone, DataNascimento,
DataAdmissao, DataDemissao)
Engenheiro(@CodFunc, CREA)
Administrador(@CodFunc, CRA)
Relacao(@TipoRelacao, DescricaoRelacao)
Dependentes(@CodFunc, @NrDependente, Nome, DataNascimento)
Projeto(@CodProjeto, Descricao, DataInicioProjeto, DataFimProjeto)
Maquina(@CodMaquina, Descricao, Procedimentos)
ContraCheque(@CodFunc, @DataEmissao, SalarioBase, Gratificacoes,
SalarioBruto, SalarioLiquido)
Atributos dos Relacionamentos:
PossuiCargo(DataInicioCargo)
Lotacao(@DataInicio, DataFim)
CoordenaProjeto(DataInicioCoordenacao, GratificacaoCoordenador)
GerenciaDepartamento(DataInicioGerencia, GratificacaoGerente)
BeneficioFuncionario(DataInicioBeneficio, SituacaoBeneficio)
EngenheiroMaquina(@DataUsoMaquina, HorasUsoMaquina)
Supervisiona(DataInicioSupervisao, GratificacaoSupervisao)
EngenheiroProjeto(@DataTrabalho, HorasProjeto)
DER Físico Relacional (Gerado pelo System Architect)
Cargo
CodCargo
integer [PK1]
Descricao
char(30)
SalarioBase
money
GratificacaoCargo
money
PossuiCargo
BeneficioFuncionario
CodFunc
integer [PK1] [FK]
CodBeneficio
integer [PK2] [FK]
DataInicioBeneficio
smalldatetime
SituacaoBeneficiocharacter(1)
Relacao
RelacaoDependenteTipoRelacao
char(1) [PK1]
Descricao character(20)
Dependentes
CodFunc
integer [PK1] [FK]
NrDependenteinteger [PK2]
Nome
character(40)
smalldatetime
PossuiDependentes DataNascimento
TipoRelacao char(1) [FK]
VinculoEmpresa
PossuiFormacao
FormacaoEscolar
CodFormacao
integer [PK1]
Descricao
character(30)
GratificacaoFormacao
money
Empresa
CodEmpresainteger [PK1]
NomeFantasia
character(30)
CGC
character(20)
Endereco
character(40)
Telefone
character(15)
Funcionario
CodFunc
integer [PK1]
Nome
character(40)
Endereco
character(40)
Telefone
character(15)
DataNascimento
smalldatetime
DataAdmissao
smalldatetime
DataDemissao
smalldatetime
SalarioBruto
money
SalarioLiquido
money
CodCargo
integer [FK]
DataInicioCargo
smalldatetime
CodFormacao
integer [FK]
CodEmpresa
integer [FK]
CodFuncSupervisorinteger
DataInicioSupervisao
smalldatetime
GratificacaoSupervisao
money
Lotacao
CodFunc integer [PK1] [FK]
CodDeptointeger [PK2] [FK]
DataIniciosmalldatetime [PK3]
DataFim smalldatetime
Departamento
CodDepto
integer [PK1]
Descricao
character(30)
Localizacao
character(30)
CodFuncGerenteinteger [FK]
DataInicioGerentesmalldatetime
GratificacaoGerente
money
Supervisiona
GerenciaDepartamento
ControlaProjeto
Beneficio
CodBeneficio
integer [PK1]
Descricao character(30)
Valor
money
Administrador
Engenheiro
CodFuncinteger [PK1] [FK] CodFuncinteger [PK1] [FK]
CRA
char(10)
CREA char(15)
Maquina
CodMaquina integer [PK1]
Descricao
character(30)
Procedimentos
character(50)
UsaMaquina
EngenheiroProjeto
CodFunc
integer [PK2] [FK]
CodProjeto
integer [PK1] [FK]
DataTrabalho datetime [PK3]
HorasProjeto
decimal
CodMaquina
integer [FK]
HorasUsoMaquina
decimal
CoordenaProjeto
Projeto
CodProjeto
integer [PK1]
Descricao
character(40)
DataInicioProjeto
smalldatetime
DataFimProjeto
smalldatetime
CodFuncCoordenadorinteger [FK]
DataInicioCoordenacao
smalldatetime
GratificacaoCoordenacao
money
CodDeptoControla integer [FK]
Observação:
 O modelo físico do CASE System Architect suporta relacionamentos 1:1.
3.1 DDL - SQL
3.1.1 Criando Banco de Dados
 Somente o DBA pode executar estes comandos:
CREATE DATABASE pessoal
DROP DATABASE pessoal
3.1.2 Criando Tabelas
 Exemplo 1:
CREATE TABLE Cargo(
CodCargo
Descricao
SalarioBase
GratificacaoCargo
integer NOT NULL,
char(30) NOT NULL,
float NOT NULL,
float)
 Exemplo 2:
CREATE TABLE FormacaoEscolar(
CodFormacao
integer Not Null PRIMARY KEY,
Descricao
character(30) NOT NULL,
GratificacaoFormacao float)
 Exemplo 3:
CREATE TABLE ContraCheque(
CodFunc
integer NOT NULL,
DataEmissao
date NOT NULL,
SalarioBase
float,
Gratificacoes
float,
SalarioBruto
float,
SalarioLiquido
float,
PRIMARY KEY (CodFunc, DataEmissao) )
 Exemplo 4:
CREATE TABLE EngenheiroProjeto(
CodFunc
integer NOT NULL,
CodProjeto
integer NOT NULL,
DataTrabalho
date NOT NULL,
HorasProjeto
float)
ALTER TABLE EngenheiroProjeto ADD
PRIMARY KEY (CodFunc,CodProjeto,DataTrabalho)
 Exemplo 5:
CREATE TABLE Projeto(
CodProjeto
Descricao
DataInicioProjeto
DataFimProjeto
DataInicioCoordenacao
GratificacaoCoordenacao
CodDeptoControla
CodFuncCoordenador
integer PRIMARY KEY,
char(40) NOT NULL,
date,
date,
date,
float,
integer,
integer)
 Exemplo 6:
CREATE TABLE Dependentes(
CodFunc
NrDependente
Nome
DataNascimento
TipoRelacao
integer NOT NULL,
integer NOT NULL,
character(30) NOT NULL,
smalldatetime NOT NULL,
char(1) NOT NULL)
ALTER TABLE Dependentes ADD
PRIMARY KEY (CodFunc, NrDependente)
ALTER TABLE Dependentes ADD
FOREIGN KEY (CodFunc) REFERENCES Funcionario (CodFunc)
ALTER TABLE Dependentes ADD
FOREIGN KEY (TipoRelacao) REFERENCES Relacao (TipoRelacao)
 Exemplo 7:
CREATE TABLE Departamento(
CodDepto
integer NOT NULL,
Descricao
character(30) NOT NULL,
Localizacao
character(30) NULL,
DataInicioGerente
smalldatetime NULL,
GratificacaoGerente
float NULL,
CodFuncGerente
integer NULL)
ALTER TABLE Departamento ADD CONSTRAINT Departamento_PK
PRIMARY KEY (CodDepto)
ALTER TABLE Departamento ADD CONSTRAINT
GerenciaDepartamento
FOREIGN KEY (CodFuncGerente) REFERENCES Administrador (CodFunc)
 Exemplo 8:
CREATE TABLE Funcionario(
CodFunc
integer NOT NULL,
Nome
character(30) NOT NULL,
Endereco
character(30) NULL,
Telefone
character(15) NULL,
DataNascimento
smalldatetime NULL
CHECK (DataNascimento > "01/01/1900") AND
(DataNascimento <= getdate() ),
DataAdmissao
smalldatetime NOT NULL
DEFAULT (getdate()) ,
DataDemissao
smalldatetime NULL,
CodCargo
integer NULL,
DataInicioCargo
smalldatetime NULL,
CodFormacao
integer NULL,
CodEmpresa
integer NOT NULL
DEFAULT (1),
CodFuncSupervisor
integer NULL,
DataInicioSupervisao smalldatetime NULL,
GratificacaoSupervisao float NULL
CHECK (GratificacaoSupervisao >= 0),
PRIMARY KEY (CodFunc),
FOREIGN KEY (CodCargo) REFERENCES Cargo(CodCargo),
FOREIGN KEY (CodFormacao) REFERENCES
FormacaoEscolar(CodFormacao),
FOREIGN KEY (CodEmpresa) REFERENCES Empresa(CodEmpresa),
FOREIGN KEY (CodFuncSupervisor) REFERENCES
Funcionario(CodFunc) )
 Exemplo 9: Regras no MS SQL Server:
CREATE TABLE Aluno(
RA
CHAR(15) PRIMARY KEY,
Nome
CHAR(30) NOT NULL,
Endereco
VARCHAR(100) NOT NULL,
Estado
CHAR(2) NOT NULL DEFAULT("MG"),
DataNascimento SMALLDATETIME NULL)
CREATE RULE regra_estado
AS @est IN ("MG", "BA", "SP", "PR", "RJ", "RS", "AM", "AC", "RN")
SP_BINDRULE "regra_estado", "Aluno.Estado"
CREATE RULE regra_data AS @data > "01/01/1900" AND @data < getdate()
SP_BINDRULE "regra_data", "Aluno.DataNascimento"
SP_BINDRULE "regra_data", "Funcionario.DataInicioCargo"
SP_UNBINDRULE "regra_estado", "Aluno.Estado"
 Exemplo 10:
ALTER TABLE Dependentes ADD telefone char(15) NULL.
ALTER TABLE Dependentes ALTER COLUMN telefone char(20).
ALTER TABLE Dependentes DROP telefone.
ALTER TABLE Departamento DROP CONSTRAINT GerenciaDepartamento
 Exemplo 11: Implementado relacionamentos 1:1
CREATE TABLE Curso(
Cod_Curso integer PRIMARY KEY,
Descricao char(30),
DataCriacao smalldatetime,
Cod_Coordenador integer UNIQUE
FOREIGN KEY REFERENCES Professor(CodCurso))
 Exemplo 12:
DROP TABLE Funcionario
DROP TABLE Cargo
Observação Importante: Só é possível remover uma tabela se ela não for
referenciada por nenhuma outra tabela (ou seja um de seus atributos não for FK em
outra tabela).
 Exemplo 13: Especificando as opções de implementação dos relacionamentos
(Microsoft SQL Server só aceita na forma de trigger):
CREATE TABLE Departamento(
CodDepto
integer PRIMARY KEY,
Descricao
character(30) NOT NULL,
Localizacao
character(30) NULL,
CodFuncGerente
integer NULL,
FOREIGN KEY (CodFuncGerente) REFERENCES Administrador (CodFunc)
ON DELETE RESTRICT ON UPDATE CASCADE)
CREATE TABLE Projeto(
CodProjeto
integer PRIMARY KEY,
Descricao
character(40) NOT NULL,
DataInicioProjeto
smalldatetime NULL,
DataFimProjeto
smalldatetime NULL,
CodDeptoControla
integer NULL
FOREIGN KEY REFERENCES Departamento(CodDepto)
ON DELETE SET NULL ON UPDATE CASCADE,
CodFuncCoordenador
integer NULL
FOREIGN KEY REFERENCES Administrador(CodFunc)
ON DELETE RESTRICT ON UPDATE CASCADE)
Exercícios: Gere manualmente o script de criação dos seguintes BD:
a) Sistema de Controle de Histórico Escolar:
b) Sistema de Controle uma Loja para Eletrodomésticos:
c) Sistema de Controle de Supermercado: