-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathBD Script.txt
104 lines (98 loc) · 3.42 KB
/
BD Script.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
{\rtf1\ansi\ansicpg1252\cocoartf2580
\cocoatextscaling0\cocoaplatform0{\fonttbl\f0\fswiss\fcharset0 Helvetica;\f1\froman\fcharset0 Times-Roman;}
{\colortbl;\red255\green255\blue255;\red0\green0\blue0;}
{\*\expandedcolortbl;;\cssrgb\c0\c0\c0;}
\paperw11900\paperh16840\margl1440\margr1440\vieww12220\viewh16840\viewkind0
\pard\tx566\tx1133\tx1700\tx2267\tx2834\tx3401\tx3968\tx4535\tx5102\tx5669\tx6236\tx6803\pardirnatural\partightenfactor0
\f0\fs24 \cf0 \
\
\fs26 \cf2 \expnd0\expndtw0\kerning0
\outl0\strokewidth0 \strokec2 1-Fa\'e7a o modelo f\'edsico.
\f1\fs24 \
\f0 \cf0 \kerning1\expnd0\expndtw0 \outl0\strokewidth0 \
\
USE db_lojaVino ;\
\
-- -----------------------------------------------------\
-- Table `db_lojaVino`.`Regiao`\
-- -----------------------------------------------------\
CREATE TABLE db_lojaVino.Regiao (\
`codRegiao` BIGINT NOT NULL,\
`nomeRegiao` VARCHAR(100) NOT NULL,\
`descricaoRegiao` TEXT NULL,\
PRIMARY KEY (`codRegiao`));\
\
-- -----------------------------------------------------\
-- Table `db_lojaVino`.`Vinicola`\
-- -----------------------------------------------------\
CREATE TABLE db_lojaVino.Vinicola (\
`codVinicola` BIGINT NOT NULL,\
`nomeVinicola` VARCHAR(100) NOT NULL,\
`descricaoVinicula` TEXT NULL,\
`foneVinicola` VARCHAR(15) NULL,\
`emailVinicola` VARCHAR(15) NULL,\
`codRegiao` BIGINT NOT NULL,\
PRIMARY KEY (`codVinicola`),\
FOREIGN KEY (`codRegiao`)\
REFERENCES db_lojaVino.Regiao (`codRegiao`));\
\
-- -----------------------------------------------------\
-- Table `db_lojaVino`.`Vinho`\
-- -----------------------------------------------------\
CREATE TABLE IF NOT EXISTS db_lojaVino.Vinho (\
`codVinho` BIGINT NOT NULL,\
`nomeVinho` VARCHAR(50) NOT NULL,\
`tipoVinho` VARCHAR(30) NOT NULL,\
`anoVinho` INT NOT NULL DEFAULT CURRENT_TIMESTAMP,\
`descricaoVinho` TEXT NULL,\
`codVinicola` BIGINT NOT NULL,\
PRIMARY KEY (`codVinho`),\
FOREIGN KEY (`codVinicola`)\
REFERENCES db_lojaVino.Vinicola (`codVinicola`));\
\
\
\fs26 Parte 2 - \cf2 \expnd0\expndtw0\kerning0
\outl0\strokewidth0 \strokec2 Insira pelo menos 5 registros em cada tabela, mantendo as integridades referenciais:\
\f1\fs24 \
\f0 \cf0 \kerning1\expnd0\expndtw0 \outl0\strokewidth0 \
insert into db_lojaVino.Regiao(codRegiao,nomeRegiao) values\
(55,"Brasil"),\
(88,"Chile"),\
(22,"Argentina"),\
(77,"Portugal"),\
(99,"Espanha");\
\
insert into db_lojaVino.Vinho(codVinho,nomeVinho,tipoVinho,anoVinho,codVinicola) values\
(1111, "Concha y toro","Carbenet Sauvignon",2013,100),\
(2222, "La Rosa","Carbenet Sauvignon",2019,200),\
(3333, "El toro","Carbenet Sauvignon",2013,300),\
(4444, "Concha y toro","Carbenet Sauvignon",2020,400),\
(5555, "La Pasaje","Carbenet Sauvignon",2009,200);\
\
insert into db_lojaVino.Vinicola(codVinicola,nomeVinicola,codRegiao) values\
(100,"Vinicola Azul",88),\
(200,"Vinicola Roja",22),\
(300,"Vinicola Verde",77),\
(400,"Vinicola Rosa",55);\
\
Parte 3 - Consulta\
\
select * \
from db_lojaVino.Vinicola\
INNER JOIN db_lojaVino.Vinho\
on Vinho.codVinicola = Vinicola.codVinicola\
inner join db_lojaVino.Regiao\
on Vinicola.codRegiao = Regiao.codRegiao;\
\
Parte 4 - Cria\'e7\'e3o do Usu\'e1rio, select e consultas:\
\
create user Somellier@"localhost"\
with MAX_QUERIES_PER_HOUR 40;\
\
grant select on db_lojaVino.Vinho to Somellier@"localhost";\
\
grant select (codVinicola, nomeVinicola) on db_lojaVino.Vinicola to Somellier@"localhost";\
\
\
\
}