dLewpu7m

1drop database if exists trab2vanessa;
2create database trab2vanessa;
3use trab2vanessa;
4
5create table tb_setor (
6id_setor integer primary key not null,
7descricao_setor varchar(50)
8);
9
10create table tb_cargo (
11id_cargo integer primary key not null,
12descricao_cargo varchar(50)
13);
14
15create table tb_funcionario (
16id_funcionario integer primary key not null,
17id_setor integer not null,
18id_cargo integer not null,
19nome varchar(150) not null,
20telefone varchar(11),
21cpf varchar(11) not null,
22email varchar(150),
23dt_nasc date not null,
24sexo char(1) not null,
25salario real not null,
26dt_contratacao date not null,
27situacao bool not null,
28foreign key (id_setor) references tb_setor (id_setor),
29foreign key (id_cargo) references tb_cargo (id_cargo)
30);
31
32create table tb_cliente (
33id_cliente integer primary key not null,
34nome_cliente varchar(150) not null,
35cnpj varchar(15) not null,
36telefone varchar(11)
37);
38
39create table tb_projeto (
40id_projeto integer primary key not null,
41id_cliente integer not null,
42titulo_projeto varchar(150) not null,
43dt_inicio date,
44dt_fim date,
45custo real not null,
46descricao text,
47situacao bool not null,
48foreign key (id_cliente) references tb_cliente (id_cliente)
49); 
50
51create table tb_projeto_funcionario (
52id_projeto integer not null,
53id_funcionario integer not null,
54tipo char(1),
55primary key (id_projeto,id_funcionario),
56foreign key (id_projeto) references tb_projeto (id_projeto),
57foreign key (id_funcionario) references tb_funcionario (id_funcionario)
58); 
59
60create table tb_atividade (
61id_atividade integer primary key not null,
62id_projeto integer,
63descricao_atividade varchar(255),
64dt_inicio date,
65dt_fim date,
66hora_fim time,
67situacao bool,
68foreign key (id_projeto) references tb_projeto (id_projeto)
69);
70
71insert into tb_setor (id_setor,descricao_setor) values
72(1,'Contabilidade'),
73(2,'Recursos humanos'),
74(3,'Informatica'),
75(4,'Marketing'),
76(5,'Producao');
77
78insert into tb_cargo (id_cargo,descricao_cargo) values
79(1,'Analista'),
80(2,'Tecnico'),
81(3,'Auxiliar'),
82(4,'Estagiario'),
83(5,'Assistente');
84
85insert into tb_funcionario (id_funcionario,id_setor,id_cargo,nome,telefone,cpf,email,dt_nasc,sexo,salario,dt_contratacao,situacao) values
86(1,1,1,'Maria de Jesus','6133428645','65476428645','mariaj@gmail.com','1985-11-22 00:00:00','F',1053.03,'2010-07-19 00:00:00',1),
87(2,1,2,'João de Oliveira Mendes','6155259987','65487610983','joao.oliver@hotmail.com','1975-03-24 00:00:00','M',2349.76,'1995-01-25 00:00:00',1),
88(3,2,3,'José Junior Pascal','6155443342','98645317256','jjpascal@yahoo.com.br','1980-08-13 00:00:00','M',3087.09,'2000-07-15 00:00:00',1),
89(4,4,4,'Juliana Pain Lima','6176559876','45378688297','juliana.lima@uol.com.br','1978-07-15 00:00:00','F',4500,'2010-11-14 00:00:00',1),
90(5,2,1,'Adriano Lopes Dias','6155435688','73653429282','lopes@ymail.com','1977-03-23 00:00:00','M',1503.78,'2011-03-07 00:00:00',1),
91(6,2,1,'Adriana Lopes Dias','6187665435','64500987262','alopes@gmail.com','1989-12-28 00:00:00','F',3736,'2010-11-23 00:00:00',1),
92(7,4,2,'Muglia Ferreira Souza','61875437','11265378020','muglia@hotmail.com','1960-04-13 00:00:00','F',5643.98,'2010-11-21 00:00:00',1),
93(8,5,5,'Mel Beatriz Santos','618799874','9874541221','melzinha@msn.com','1984-08-27 00:00:00','F',1247.88,'1995-04-25 00:00:00',0),
94(9,3,1,'Cristina Kelly Beluce','6178665543','98465414711','beluce@globo.com','1990-09-14 00:00:00','F',6439.87,'2002-03-07 00:00:00',0),
95(10,3,4,'Cristiano Cesar Muniz','6188994312','98635262191','muniz@terra.com.br','1975-05-08 00:00:00','M',383.98,'2007-01-23 00:00:00',1);
96
97insert into tb_cliente (id_cliente,nome_cliente,cnpj,telefone) values
98(1,'Nova Produtos Alimenticios','76884888000187','6124537299'),
99(2,'JVM Comercio e Industria','87853663000186','617864366'),
100(3,'CRF Esporte','8994542000176',null),
101(4,'Nova Produtos Alimenticios','76884888000187','6124537299');
102
103insert into tb_projeto (id_projeto,id_cliente,titulo_projeto,dt_inicio,dt_fim,custo,descricao,situacao) values
104(1,1,'Projeto XXY','2011-03-07 00:00:00','2011-04-29 00:00:00',80456.88,'O projeto é para fazer...',1),
105(2,2,'Projeto Beta 1','2011-12-13 00:00:00','2011-06-30 00:00:00',2000000.76,'O projeto é um...',1),
106(3,2,'Projeto Beta 2','2011-04-04 00:00:00','2011-12-24 00:00:00',679651.98,'O projeto visa...',1),
107(4,2,'Projeto Zeta','2011-04-14 00:00:00','2011-12-31 00:00:00',7650.98,'O projeto visa...',1),
108(5,3,'Projeto DEF','2011-10-04 00:00:00','2011-03-15 00:00:00',754444,'O projeto desenvolve...',0);
109
110insert into tb_projeto_funcionario (id_projeto,id_funcionario,tipo) values
111(1,1,'G'),
112(1,3,'P'),
113(1,5,'P'),
114(1,8,'P'),
115(2,2,'G'),
116(2,6,'P'),
117(2,7,'P'),
118(3,9,'G'),
119(3,5,'P'),
120(3,4,'P'),
121(4,6,'G'),
122(5,4,'G'),
123(5,3,'P'),
124(4,1,'P');
125
126insert into tb_atividade (id_atividade,id_projeto,descricao_atividade,dt_inicio,dt_fim,hora_fim,situacao) values
127(1,2,'Ata da Reuniao','2011-03-24 00:00:00','2011-03-28 00:00:00','09:00:00',1),
128(2,2,'Reuniao com cliente','2011-03-13 00:00:00','2011-03-25 00:00:00','11:00:00',1),
129(4,3,'Contato com cliente','2011-03-13 00:00:00','2011-03-13 00:00:00','15:45:00',1),
130(5,2,'Ata da Reuniao','2011-03-24 00:00:00','2011-03-28 00:00:00','09:00;00',1),
131(6,4,'Levantar Informacoes','2011-02-13 00:00:00','2011-04-19 00:00:00','12:00:00',1),
132(7,5,'Reuniao com cliente e fornecedor','2011-03-30 00:00:00','2011-03-30 00:00:00','17:00:00',0);