BsueycYC

1DROP DATABASE IF EXISTS SIMES;
2CREATE DATABASE SIMES;
3USE SIMES;
4
5CREATE TABLE ETIQUETA(
6	ID INT PRIMARY KEY AUTO_INCREMENT,
7    IDENTIFICADOR VARCHAR(100) NOT NULL UNIQUE
8);
9
10CREATE TABLE PESSOA(
11	ID INT PRIMARY KEY AUTO_INCREMENT,
12    NOME VARCHAR(150) NOT NULL,
13    CPF CHAR(14) NOT NULL UNIQUE,
14    DATA_DE_NASCIMENTO DATE NOT NULL
15);
16
17CREATE TABLE USUARIO(
18	ID INT PRIMARY KEY AUTO_INCREMENT,
19    NIVEL_DE_ACESSO INT NOT NULL,
20    SENHA VARCHAR(100) NOT NULL,
21    PESSOA_ID INT NOT NULL UNIQUE,
22    
23    FOREIGN KEY(PESSOA_ID) REFERENCES PESSOA(ID)
24		ON DELETE RESTRICT ON UPDATE CASCADE
25);
26
27#Atribuição de tag à pessoa
28CREATE TABLE PESSOA_ETIQUETA(
29	PESSOA_ID INT NOT NULL,
30    ETIQUETA_ID INT NOT NULL UNIQUE,
31    
32    PRIMARY KEY(PESSOA_ID, ETIQUETA_ID),
33    
34    FOREIGN KEY(PESSOA_ID) REFERENCES PESSOA(ID)
35		ON DELETE RESTRICT ON UPDATE CASCADE,
36        
37	FOREIGN KEY(ETIQUETA_ID) REFERENCES ETIQUETA(ID)
38		ON DELETE RESTRICT ON UPDATE CASCADE
39);
40
41CREATE TABLE `AREA`(
42	ID INT PRIMARY KEY AUTO_INCREMENT,
43    NOME VARCHAR(150) NOT NULL UNIQUE
44);
45
46CREATE TABLE CAMERA(
47	ID INT PRIMARY KEY AUTO_INCREMENT,
48    NUMERO INT NOT NULL UNIQUE,
49    AREA_ID INT NOT NULL,
50    
51    FOREIGN KEY(AREA_ID) REFERENCES `AREA`(ID)
52		ON DELETE RESTRICT ON UPDATE CASCADE
53);
54
55CREATE TABLE ANTENA(
56	ID INT PRIMARY KEY AUTO_INCREMENT,
57    NUMERO INT NOT NULL UNIQUE,
58    AREA_ID INT NOT NULL,
59    
60    FOREIGN KEY(AREA_ID) REFERENCES `AREA`(ID)
61		ON DELETE RESTRICT ON UPDATE CASCADE
62);
63
64CREATE TABLE ALUNO(
65	ID INT PRIMARY KEY AUTO_INCREMENT,
66    MATRICULA CHAR(14) NOT NULL UNIQUE,
67    PESSOA_ID INT NOT NULL UNIQUE,
68    
69    FOREIGN KEY(PESSOA_ID) REFERENCES PESSOA(ID)
70		ON DELETE RESTRICT ON UPDATE CASCADE
71);
72
73CREATE TABLE SERVIDOR(
74	ID INT PRIMARY KEY AUTO_INCREMENT,
75    MATRICULA CHAR(7) NOT NULL UNIQUE,
76    CARGO VARCHAR(100) NOT NULL,
77    PESSOA_ID INT NOT NULL UNIQUE,
78	
79    FOREIGN KEY(PESSOA_ID) REFERENCES PESSOA(ID)
80		ON DELETE RESTRICT ON UPDATE CASCADE
81);
82
83CREATE TABLE VISITANTE(
84	ID INT PRIMARY KEY AUTO_INCREMENT,
85    ENTRADA DATETIME NOT NULL DEFAULT NOW(),
86    SAIDA DATETIME, #SERA ADICIONADO VIA UPDATE, QUANDO O VISITANTE DEIXAR A INSTITUIÇÃO
87    AREA_DESTINO_ID INT NOT NULL,
88    MOTIVO TEXT,
89    PESSOA_ID INT NOT NULL UNIQUE,
90    
91    FOREIGN KEY (PESSOA_ID) REFERENCES PESSOA(ID)
92		ON DELETE RESTRICT ON UPDATE CASCADE,
93	FOREIGN KEY (AREA_DESTINO_ID) REFERENCES `AREA`(ID)
94		ON DELETE RESTRICT ON UPDATE CASCADE
95);
96
97CREATE TABLE EVENTO(
98	ID INT PRIMARY KEY AUTO_INCREMENT,
99	DATA_INICIO DATETIME NOT NULL DEFAULT NOW(),
100    DATA_FIM DATETIME, #SERIA ADICIONADO VIA UPDATE, QUANDO O EVENTO ACABAR, OU CRIAR NOVA TABELA?
101    TITULO VARCHAR(100) NOT NULL UNIQUE,
102    DESCRICAO TEXT NOT NULL
103);
104
105CREATE TABLE PARTICIPACAO_EVENTO(
106	PESSOA_ID INT NOT NULL,
107    EVENTO_ID INT NOT NULL,
108    
109    FOREIGN KEY(PESSOA_ID) REFERENCES PESSOA(ID)
110		ON DELETE CASCADE ON UPDATE CASCADE,
111	FOREIGN KEY(EVENTO_ID) REFERENCES EVENTO(ID)
112		ON DELETE CASCADE ON UPDATE CASCADE
113);
114
115#TODOS OS ALUNOS
116SELECT P.NOME, P.CPF, P.DATA_DE_NASCIMENTO, A.MATRICULA FROM ALUNO A 
117JOIN PESSOA P ON P.ID = A.PESSOA_ID; 
118
119#TODOS OS SERVIDORES
120SELECT P.NOME, P.CPF, P.DATA_DE_NASCIMENTO, S.MATRICULA, S.CARGO FROM PESSOA P 
121JOIN SERVIDOR S ON S.PESSOA_ID = P.ID;
122
123#TODOS AS TAGS ATIVAS
124SELECT P.NOME, ET.IDENTIFICADOR FROM PESSOA P 
125JOIN PESSOA_ETIQUETA PE ON PE.PESSOA_ID = P.ID 
126JOIN ETIQUETA ET ON PE.ETIQUETA_ID = ET.ID; 
127
128#TODAS AS TAGS DISPONIVEIS
129SELECT * FROM ETIQUETA WHERE ID NOT IN (SELECT ETIQUETA_ID FROM PESSOA_ETIQUETA);
130
131#TODAS AS TAGS COM OS POSSIVEIS USUARIOS
132SELECT ET.*, P.* FROM ETIQUETA ET 
133LEFT JOIN PESSOA_ETIQUETA PE ON ET.ID = PE.ETIQUETA_ID 
134LEFT JOIN PESSOA P ON P.ID = PE.PESSOA_ID; 
135
136#TODOS OS SERVIDORES COM TAG ATIVA
137SELECT ET.*, P.*, S.* FROM ETIQUETA ET JOIN PESSOA_ETIQUETA PE ON E.ID = PE.ETIQUETA_ID 
138JOIN PESSOA P ON P.ID = PE.PESSOA_ID 
139JOIN SERVIDOR S ON S.PESSOA_ID = P.ID;