Profor: Gilberto Braga de Oliveira
Tecnologias e Linguagens para Banco de Dados II
Projeto de Banco de Dados: Empresa X
Competências: Analisar e aplicar o resultado da modelagem de
dados; Habilidades: Implementar as estruturas modeladas usando banco de
dados; Bases Tecnológicas: Administração de banco de dados;
Ambientes/ferramentas de gerenciamento de bancos de dados. (SQL Server
2005).
Modelo de negócio: Empresa X
O objeto é criar um projeto de banco de dados para um sistema de informação
automatizado de uma empresa para que o departamento de Recursos Humanos gerencie a
alocação de funcionários nos departamentos da empresa.
Os funcionários são cadastrados por um número de matrícula, nome, data de nascimento
e data de matrícula. Cada funcionário “deve” ser alocado em um e somente um departamento.
Os departamentos são cadastrados pelo nome do departamento e por um valor de
orçamento. Um departamento “pode” alocar um ou mais funcionários.
O número de matrícula do funcionário poderá ser um número inteiro seqüencial, atribuído
automaticamente, obrigatório e que não pode ser repetir entre os funcionários da empresa. O
nome, a data de nascimento e a data da matrícula são dados obrigatórios a todos os funcionários
da empresa.
O nome do departamento é um dado obrigatório e não repetido para cada departamento
no sistema. O orçamento de cada departamento pode ser omitido.
O nome e a data da matrícula do funcionário, e o nome do departamento são usados
constantemente para consultas.
N
FUNCIONARIO
1
tem
DEPARTAMENTO
Figura 1 - Modelo Entidade-Relacionamento
ATRIBUTOS
RELACIONAMENTO
*#Matricula
CodDepartamento
com Departamento 1:1
FUNCIONARIO
Nome
Total
DataNascimento
DataMatricula
*CodDepartamento
com Funcionario 1:N
DEPARTAMENTO
#Departamento
Total
Orcamento
Tabela 1 – Modelo Lógico-Relacional
ENTIDADE
Rodovia Pref. Luiz Salomão Chamma, S/N Km 41 – Pouso Alegre – CEP 07895-340
Franco da Rocha – SP – Brasil - Telefones (11) 4443-6110/4449-3115
E-mail: [email protected] Site: http://www.etecfran.com.br/
1
Tecnologias e Linguagens para Banco de Dados II
Profor: Gilberto Braga de Oliveira
Projeto de banco de dados em SQL Server 2005:
1. No menu Iniciar, aponte para Todos os programas, Microsoft SQL Server
2005, SQL Server Management Studio.
2. Na caixa de diálogo Connect to Server, verifique o tipo de servidor, o nome e
as caixa de autenticação, para saber se estão ok, e clique em Connect para
aceitar os valores padrão.
3. No menu File, do SQL Server Management Studio, aponte para New, e
clique em Project. A caixa de diálogo New Project surge.
4. Na caixa de texto name, selecione SQL Server Scripts1, escreva EmpresaX e
clique em OK.
5. Na janela Solution Explorer clique com o botão direito no diretório
Connections do projeto EmpresaX e clique em New Connection. A caixa de
diálogo Connect to Server aparecerá.
6. Na caixa de diálogo Connect to Server, clique em OK.
7. Na região Solution Explorer, clique com o botão direito no diretório Queries e
clique em New Query. A caixa de diálogo Connect to Database Engine
surge.
8. Na caixa de diálogo Connect to Database Engine clique em Connect
9. Na janela Solution Explorer, clique com o botão direito no arquivo
SQLQuery1.sql, clique em Rename, e escreva CriarEmpresaX.sql.
10. No arquivo CriarEmpresaX.sql que está aberto na janela Summary, escreva
as declaração SQL a seguir:
--Criação do Banco de Dados EmpresaX
CREATE DATABASE EmpresaX;
GO
--Usando EmpresaX
USE EmpresaX;
GO
--Criação do esquma RecursosHumanos
CREATE SCHEMA RecursosHumanos
--Criação da tabela Departamento
CREATE TABLE Departamento(
CodDepartamento INT NOT NULL,
Departamento VARCHAR(25) NOT NULL,
Orcamento MONEY
);
GO
--Adição do campo CodDepartamento como Chave Primária da tabela
Departamento
Rodovia Pref. Luiz Salomão Chamma, S/N Km 41 – Pouso Alegre – CEP 07895-340
Franco da Rocha – SP – Brasil - Telefones (11) 4443-6110/4449-3115
E-mail: [email protected] Site: http://www.etecfran.com.br/
2
Tecnologias e Linguagens para Banco de Dados II
Profor: Gilberto Braga de Oliveira
ALTER TABLE RecursosHumanos.Departamento ADD CONSTRAINT PK_Departamento
PRIMARY KEY (CodDepartamento);
GO
--Adição do campo Departamento na lista de índices da tabela
CREATE INDEX IDX_Departamento ON
RecursosHumanos.Departamento(Departamento);
GO
--Criação da tabela Funcionário
CREATE TABLE RecursosHumanos.Funcionario(
Matricula INT NOT NULL IDENTITY,
CodDepartamento INT NOT NULL,
Nome VARCHAR(50),
DataNascimento DATETIME NOT NULL,
DataMatricula DATETIME NOT NULL
);
GO
--Adição do campo Matricula como Chave Primária da tabela Funcionário
ALTER TABLE RecursosHumanos.Funcionario ADD CONSTRAINT PK_Funcionario
PRIMARY KEY (Matricula);
GO
--Adição do campo CodDepartamento como Chave Estrangeira da tabela
Funcionário
ALTER TABLE RecursosHumanos.Funcionario ADD CONSTRAINT
FK_Departamento_Funcionario
FOREIGN KEY(CodDepartamento) REFERENCES RecursosHumanos.Departamento;
GO
--Adição dos campos Nome e DataNascimento na tabela de índices de
Funcionário
CREATE INDEX IDX_Nome_DtMatricula ON RecursosHumanos.Funcionario(Nome,
DataMatricula DESC);
GO
11. Popule as tabelas criadas com dados de teste conforme o código a seguir:
USE EmpresaX;
GO
INSERT INTO RecursosHumanos.Departamento(CodDepartamento, Departamento,
Orcamento)
VALUES(1, 'Recursos Humanos', 7560.25);
GO
INSERT INTO RecursosHumanos.Departamento(CodDepartamento, Departamento,
Orcamento)
VALUES(2, 'Produção', 25320.5);
GO
INSERT INTO RecursosHumanos.Departamento(CodDepartamento, Departamento,
Orcamento)
Rodovia Pref. Luiz Salomão Chamma, S/N Km 41 – Pouso Alegre – CEP 07895-340
Franco da Rocha – SP – Brasil - Telefones (11) 4443-6110/4449-3115
E-mail: [email protected] Site: http://www.etecfran.com.br/
3
Tecnologias e Linguagens para Banco de Dados II
Profor: Gilberto Braga de Oliveira
VALUES(3, 'Comercial', NULL);
GO
INSERT INTO RecursosHumanos.Departamento(CodDepartamento, Departamento,
Orcamento)
VALUES(21, 'Organização e Métodos', 9700.31);
GO
INSERT INTO RecursosHumanos.Departamento(CodDepartamento, Departamento,
Orcamento)
VALUES(5, 'Controladoria', 3950.89);
GO
INSERT INTO RecursosHumanos.Departamento(CodDepartamento, Departamento,
Orcamento)
VALUES(6, 'Financeiro', NULL);
GO
INSERT INTO RecursosHumanos.Funcionario(CodDepartamento, Nome,
DataNascimento, DataMatricula)
VALUES(21, 'João da Silva', '1977-11-21', '1982-03-15');
GO
INSERT INTO RecursosHumanos.Funcionario(CodDepartamento, Nome,
DataNascimento, DataMatricula)
VALUES(3, 'José dos Santos', '1951-10-18', '1989-11-23');
GO
INSERT INTO RecursosHumanos.Funcionario(CodDepartamento, Nome,
DataNascimento, DataMatricula)
VALUES(3, 'Maria das Dores', '1989-01-10', '1994-05-10');
GO
INSERT INTO RecursosHumanos.Funcionario(CodDepartamento, Nome,
DataNascimento, DataMatricula)
VALUES(2, 'Pedrina Farias', '1964-08-25', '1983-09-07');
GO
INSERT INTO RecursosHumanos.Funcionario(CodDepartamento, Nome,
DataNascimento, DataMatricula)
VALUES(1, 'Carlos Margarido', '1942-12-28', '2005-04-04');
GO
INSERT INTO RecursosHumanos.Funcionario(CodDepartamento, Nome,
DataNascimento, DataMatricula)
VALUES(3, 'Gabriela Duarte', '1974-12-28', '2002-10-09');
GO
INSERT INTO RecursosHumanos.Funcionario(CodDepartamento, Nome,
DataNascimento, DataMatricula)
VALUES(2, 'Adriano Borges', '1980-03-21', '2001-06-12');
GO
INSERT INTO RecursosHumanos.Funcionario(CodDepartamento, Nome,
DataNascimento, DataMatricula)
VALUES(5, 'Adriana Santana', '1975-12-21', '2008-07-21');
GO
INSERT INTO RecursosHumanos.Funcionario(CodDepartamento, Nome,
DataNascimento, DataMatricula)
VALUES(6, 'João da Silva', '1988-01-30', '2008-07-21');
GO
INSERT INTO RecursosHumanos.Funcionario(CodDepartamento, Nome,
DataNascimento, DataMatricula)
Rodovia Pref. Luiz Salomão Chamma, S/N Km 41 – Pouso Alegre – CEP 07895-340
Franco da Rocha – SP – Brasil - Telefones (11) 4443-6110/4449-3115
E-mail: [email protected] Site: http://www.etecfran.com.br/
4
Tecnologias e Linguagens para Banco de Dados II
Profor: Gilberto Braga de Oliveira
VALUES(2, 'Barbara Cruz', '1971-08-21', '2008-08-02');
GO
INSERT INTO RecursosHumanos.Funcionario(CodDepartamento, Nome,
DataNascimento, DataMatricula)
VALUES(21, 'Almir Braz', '1948-08-23', '2008-02-25');
GO
INSERT INTO RecursosHumanos.Funcionario(CodDepartamento, Nome,
DataNascimento, DataMatricula)
VALUES(2, 'Márcia Basílio', '1980-06-21', '2008-10-30');
GO
INSERT INTO RecursosHumanos.Funcionario(CodDepartamento, Nome,
DataNascimento, DataMatricula)
VALUES(2, 'Roberto Valério', '1980-12-21', '2009-01-17');
GO
12. Agora consulte as tabelas para saber se elas foram populadas
adequadamente, verifique também as estruturas das tabelas, caso seja
necessário.
--Usar EmpresaX
USE EmpresaX;
GO
--Seleciona todas as colunas de RecursosHumanos.Funcionario
SELECT * FROM RecursosHumanos.Funcionario;
GO
--Seleciona todas as colunas de RecursosHumanos.Departamento
SELECT * FROM RecursosHumanos.Departamento;
GO
--Visualizar todos os "meta-dados" da tabela Departamento
EXEC sp_help 'RecursosHumanos.Departamento';
GO
Teste de banco de dados com DML (Insert, Update e Delete)
13. Teste as tabelas usando comandos DML (Insert, Update e Delete)
conforme os requisitos mostrados anteriormente:
USE EmpresaX;
GO
--Teste de tipo de dado
UPDATE RecursosHumanos.Funcionario
SET DataMatricula = '2008-07-21'
WHERE DataMatricula = '2008-21-07';
GO
--Teste de campos que não permitem valores nulos
UPDATE RecursosHumanos.Funcionario
SET Nome = NULL
WHERE Nome = 'Adriano Borges';
GO
/************************ CORREÇÃO: ************/
Rodovia Pref. Luiz Salomão Chamma, S/N Km 41 – Pouso Alegre – CEP 07895-340
Franco da Rocha – SP – Brasil - Telefones (11) 4443-6110/4449-3115
E-mail: [email protected] Site: http://www.etecfran.com.br/
5
Tecnologias e Linguagens para Banco de Dados II
Profor: Gilberto Braga de Oliveira
-- 1. Corrija a atualização feita
UPDATE RecursosHumanos.Funcionario
SET Nome = 'Adriano Borges'
WHERE Nome IS NULL;
GO
-- 2. Exclua o índice criado sobre o campo a ser alterado
DROP INDEX IDX_Nome_DtMatricula ON RecursosHumanos.Funcionario;
GO
-- 3. Altere o campo
ALTER TABLE RecursosHumanos.Funcionario
ALTER COLUMN Nome VARCHAR(50) NOT NULL;
GO
-- 4. Recrie o índice
CREATE INDEX IDX_Nome_DtMatricula ON RecursosHumanos.Funcionario(Nome,
DataMatricula DESC);
GO
/*************** USE SELECT * SEMPRE QUE NECESSÁRIO ********************/
--Teste de integridade dos campos que não permitem valores repetidos
UPDATE RecursosHumanos.Departamento
SET Departamento = 'Recursos Humanos'
WHERE Departamento LIKE 'Produção';
GO
/****** Campo permite valor repetido, para fazer a correção: **********/
-- 1. Corrija a atualização feita
UPDATE RecursosHumanos.Departamento
SET Departamento = 'Produção'
WHERE ((Departamento LIKE 'Recursos Humanos') AND (CodDepartamento = 2));
GO
-- 2. Exclua o índice criado sobre o campo a ser alterado
DROP INDEX IDX_Departamento ON RecursosHumanos.Departamento;
GO
-- 3. Recrie o índice
CREATE UNIQUE INDEX IDX_Departamento ON
RecursosHumanos.Departamento(Departamento);
GO
/**********************************************************************/
-- Teste de Integridade Referencial
DELETE FROM RecursosHumanos.Departamento
WHERE CodDepartamento = 5;
GO
Rodovia Pref. Luiz Salomão Chamma, S/N Km 41 – Pouso Alegre – CEP 07895-340
Franco da Rocha – SP – Brasil - Telefones (11) 4443-6110/4449-3115
E-mail: [email protected] Site: http://www.etecfran.com.br/
6
Profor: Gilberto Braga de Oliveira
Tecnologias e Linguagens para Banco de Dados II
--Teste de Integridade de Identidade
INSERT INTO RecursosHumanos.Departamento(Departamento, Orcamento)
VALUES('Contas a Receber', 21320.6);
GO
/*script usado como ferramenta para visualizar os dados e os meta-dados*/
-- Visualizar todos os "dados" da tabela Funcionário
SELECT * FROM RecursosHumanos.Funcionario;
GO
-- Visualizar todos os "dados" da tabela Departamento
SELECT * FROM RecursosHumanos.Departamento;
GO
14. Crie um diagrama a partir da opção Database Diagram que está contida no
banco de dados EmpresaX que está na janela Summary para confirmar se o
grau do relacionamento está correto:
Departamento (RecursosHumanos)
CodDepartamento
Funcionario (RecursosHumanos)
Departamento
Matricula
Orcamento
CodDepartamento
Nome
DataNascimento
DataMatricula
Figura 2 - Diagrama do banco de dados Empresa X.
15. Uma vez preenchida e livre de erros você pode implementar um código SQL
responsável por atender aos requisitos funcionais listados (lembre-se que no
seu projeto você só vai implementar esses recursos posteriormente, a medida
que avança no curso de TLBD2):
Requisitos funcionais do banco de dados:
16. Gerar relatórios:
o
Mensal:
com a matrícula, nome, e data de matrícula de todos os
funcionários, dos mais novos para os mais “velhos de casa”
SELECT Matricula, Nome, DataMatricula
FROM RecursosHumanos.Funcionario
ORDER BY DataMatricula DESC;
GO
nome e a data de nascimento dos funcionários aniversariantes do
“próximo” mês
SELECT Nome, DataNascimento
Rodovia Pref. Luiz Salomão Chamma, S/N Km 41 – Pouso Alegre – CEP 07895-340
Franco da Rocha – SP – Brasil - Telefones (11) 4443-6110/4449-3115
E-mail: [email protected] Site: http://www.etecfran.com.br/
7
Tecnologias e Linguagens para Banco de Dados II
Profor: Gilberto Braga de Oliveira
FROM RecursosHumanos.Funcionario
WHERE DATEPART(MM, DataNascimento) = DATEPART(MM, DATEADD(MM, 1,
GETDATE()));
GO
o Trimestral:
com todos os nomes de departamentos e orçamentos maiores
que a média dos orçamentos dos departamentos da empresa
SELECT Departamento, Orcamento
FROM RecursosHumanos.Departamento
WHERE Orcamento > (SELECT AVG(ISNULL(Orcamento,0)) FROM
RecursosHumanos.Departamento);
GO
o Anual
com matricula, nome, departamento, idade e tempo de casa de
todos os funcionários maiores de 60 anos ou que tenham mais de
25 anos de casa
SELECT
F.Matricula, F.Nome, D.Departamento,
Idade = DATEDIFF(year, DataNascimento, GETDATE()),
[Tempo de Casa] = DATEDIFF(year, DataMatricula, GETDATE())
FROM RecursosHumanos.Funcionario AS F JOIN
RecursosHumanos.Departamento AS D
ON D.CodDepartamento = F.CodDepartamento
WHERE ((DATEDIFF(year, DataNascimento, GETDATE()) > 60) OR
(DATEDIFF(year, DataMatricula, GETDATE()) > 25));
GO
Rodovia Pref. Luiz Salomão Chamma, S/N Km 41 – Pouso Alegre – CEP 07895-340
Franco da Rocha – SP – Brasil - Telefones (11) 4443-6110/4449-3115
E-mail: [email protected] Site: http://www.etecfran.com.br/
8