bd-indices-acesso-transacoes.md

• 10 de março de 2016
• Universidade Federal da Bahia
• MATA60 - Banco de Dados
• Prof. Rodrigo Rocha

Roteiro: índices

1. Abra um terminal, execute o python e cole o seguinte código:

n = 1000 * 1000
f = open('inserts.csv', 'w')
sql = "\n".join(["Gigante,%d,%d" % (x, 10 + x/2) for x in range(n)])
f.write(sql)
f.close()

Ele vai criar um arquivo inserts.csv, que contém 1 milhão de registros no formato CSV (campos separados por vírgulas). Abra o SQLite Manager e crie um novo banco de dados.

Agora use o Import Wizard para importar o arquivo CSV (menu Database > Import). Vai demorar um pouco.

2. Escreva e execute uma consulta para retornar todos os registros cujo valor da 2ª coluna é '555555'. Registre em um papel ou em um documento vazio o tempo que levou para a consulta retornar (valor ET no canto inferior direito da janela).

3. Rode a consulta novamente, desta vez usando EXPLAIN QUERY PLAN para entender como o SQLite executa a consulta. Registre em um papel ou em um documento vazio o conteúdo da coluna detail do plano de execução da consulta.

4. Crie um índice na 2ª coluna da tabela. Quanto tempo demorou para criar o índice? Como você explica esse tempo?

5. Rode a consulta do item 1 novamente (sem o EXPLAIN QUERY PLAN), registrando o tempo que levou para a consulta retornar. O tempo foi maior ou menor? Por quê?

6. Agora rode a consulta usando o EXPLAIN QUERY PLAN. O plano de execução foi diferente?

7. Se em vez de 1 milhão de registros, tivéssemos 2 milhões de registros, quanto tempo mais a consulta demoraria, considerando que o índice foi criado? O dobro do tempo? 50% a mais de tempo? 10% a mais? Praticamente o mesmo tempo? Por quê?

8. Escreva e execute uma consulta que retorne todos os registros cujo valor da 2ª coluna seja maior do que '99999'. Quanto tempo demorou? Com base nesse tempo, você acha que o índice que o SQLite criou é baseado em árvore ou é baseado em hash? Por quê?

Roteiro: transações

1. Abra o banco de dados universidade.db (link) no SQLite Manager.

2. Inicie uma transação, insira um novo aluno na tabela aluno e não finalize a transação.

3. Abra a pasta onde está o arquivo universidade.db. Existe algum arquivo novo, criado pelo SQLite, nessa pasta?

4. Exiba os registros da tabela aluno. O registro que você inseriu ainda está lá?

5. Agora feche o SQLite Manager e o Firefox. O arquivo ainda está lá? O que aconteceu?

6. Abra novamente o banco de dados no SQLite Manager e exiba os registros da tabela aluno. O registro que você inseriu ainda está lá? Por quê? O que aconteceu?

7. Inicie uma transação e insira um novo aluno. O SQLite criou um novo arquivo na pasta do universidade.db? Agora encerre a transação com um COMMIT. O arquivo ainda está lá? Por quê?

Roteiro: controle de acesso

1. Crie um banco de dados MySQL gratuito em http://www.freemysqlhosting.net/

2. Acesse o MySQL através do phpMyAdmin e crie um banco de dados universidade

CREATE DATABASE universidade;

(P.S.: na verdade o freemysqlhosting não deixa, então pode pular essa questão e assumir que usaremos o banco de dados que foi criado pra você)

3. Acesse o banco universidade e crie as tabelas e os dados:

-- sala de aula
create table sala
    (predio     varchar(15),
     num_sala       varchar(7),
     capacidade     numeric(4,0),
     primary key (predio, num_sala)
    );

create table departamento
    (nome_dep       varchar(20), 
     predio     varchar(15), 
     budget             numeric(12,2) check (budget > 0),
     primary key (nome_dep)
    );

create table disciplina
    (id_disciplina      varchar(8), 
     titulo         varchar(50), 
     nome_dep       varchar(20),
     creditos       numeric(2,0) check (creditos > 0),
     primary key (id_disciplina),
     foreign key (nome_dep) references departamento (nome_dep)
        on delete set null
    );

create table professor
    (id         varchar(5), 
     nome           varchar(20) not null, 
     nome_dep       varchar(20), 
     salario            numeric(8,2) check (salario > 1000),
     primary key (id),
     foreign key (nome_dep) references departamento (nome_dep)
        on delete set null
    );

-- horário de aula de uma disciplina em um semestre
create table aula
    (id_disciplina      varchar(8), 
         id_aula            varchar(8),
     semestre       varchar(6)
        check (semestre in ('1', '2')), 
     ano            numeric(4,0) check (ano > 1701 and ano < 2100), 
     predio     varchar(15),
     num_sala       varchar(7),
     id_horario     varchar(4),
     primary key (id_disciplina, id_aula, semestre, ano),
     foreign key (id_disciplina) references disciplina (id_disciplina)
        on delete cascade,
     foreign key (predio, num_sala) references sala (predio, num_sala)
        on delete set null
    );

-- professor ensina aula
create table ensino
    (id         varchar(5), 
     id_disciplina      varchar(8),
     id_aula            varchar(8), 
     semestre       varchar(6),
     ano            numeric(4,0),
     primary key (id, id_disciplina, id_aula, semestre, ano),
     foreign key (id_disciplina, id_aula, semestre, ano) references aula (id_disciplina, id_aula, semestre, ano)
        on delete cascade,
     foreign key (id) references professor (id)
        on delete cascade
    );

create table aluno
    (id         varchar(5), 
     nome           varchar(20) not null, 
     nome_dep       varchar(20), 
     total_creditos     numeric(3,0) check (total_creditos >= 0),
     primary key (id),
     foreign key (nome_dep) references departamento (nome_dep)
        on delete set null
    );

-- aluno assiste aula
create table matricula
    (id         varchar(5), 
     id_disciplina      varchar(8),
     id_aula            varchar(8), 
     semestre       varchar(1) check (semestre in ('1', '2')),
     ano            numeric(4,0),
     nota               varchar(2),
     primary key (id, id_disciplina, id_aula, semestre, ano),
     foreign key (id_disciplina,id_aula, semestre, ano) references aula (id_disciplina,id_aula, semestre, ano)
        on delete cascade,
     foreign key (id) references aluno (id)
        on delete cascade
    );

create table orientacao
    (id_aluno           varchar(5),
     id_professor           varchar(5),
     primary key (id_aluno),
     foreign key (id_professor) references professor (id)
        on delete set null,
     foreign key (id_aluno) references aluno (id)
        on delete cascade
    );

-- horario de aula
create table horario
    (id_horario     varchar(4),
     dia            varchar(3),
     hora_ini       numeric(2) check (hora_ini >= 0 and hora_ini < 24),
     minuto_ini     numeric(2) check (minuto_ini >= 0 and minuto_ini < 60),
     hora_fim           numeric(2) check (hora_fim >= 0 and hora_fim < 24),
     minuto_fim     numeric(2) check (minuto_fim >= 0 and minuto_fim < 60),
     primary key (id_horario, dia, hora_ini, minuto_ini)
    );

create table prerequisito
    (id_disciplina      varchar(8), 
     id_prerequisito        varchar(8),
     primary key (id_disciplina, id_prerequisito),
     foreign key (id_disciplina) references disciplina (id_disciplina)
        on delete cascade,
     foreign key (id_prerequisito) references disciplina (id_disciplina)
    );

--
-- dados
--

delete from prerequisito;
delete from horario;
delete from orientacao;
delete from matricula;
delete from aluno;
delete from ensino;
delete from aula;
delete from professor;
delete from disciplina;
delete from departamento;
delete from sala;
insert into sala values ('PAF I', '101', '500');
insert into sala values ('PAF III', '514', '10');
insert into sala values ('Inst. Matem.', '3128', '70');
insert into sala values ('PAF V', '100', '30');
insert into sala values ('PAF V', '120', '50');
insert into departamento values ('Biologia', 'PAF V', '90000');
insert into departamento values ('Cienc. Comp.', 'Inst. Matem.', '100000');
insert into departamento values ('Eng. Eletr.', 'Inst. Matem.', '85000');
insert into departamento values ('Economia', 'PAF III', '120000');
insert into departamento values ('Historia', 'PAF III', '50000');
insert into departamento values ('Música', 'PAF I', '80000');
insert into departamento values ('Física', 'PAF V', '70000');
insert into disciplina values ('BIO-101', 'Intro. à Biologia', 'Biologia', '4');
insert into disciplina values ('BIO-301', 'Genética', 'Biologia', '4');
insert into disciplina values ('BIO-399', 'Biologia Computacional', 'Biologia', '3');
insert into disciplina values ('CS-101', 'Programação', 'Cienc. Comp.', '4');
insert into disciplina values ('CS-190', 'Game Design', 'Cienc. Comp.', '4');
insert into disciplina values ('CS-315', 'Robótica', 'Cienc. Comp.', '3');
insert into disciplina values ('CS-319', 'Processamento de Imagens', 'Cienc. Comp.', '3');
insert into disciplina values ('CS-347', 'Banco de Dados', 'Cienc. Comp.', '3');
insert into disciplina values ('EE-181', 'Sistemas Digitais', 'Eng. Eletr.', '3');
insert into disciplina values ('FIN-201', 'Investimento', 'Economia', '3');
insert into disciplina values ('HIS-351', 'História do Mundo', 'Historia', '3');
insert into disciplina values ('MU-199', 'Produção Musical', 'Música', '3');
insert into disciplina values ('PHY-101', 'Princípios da Física', 'Física', '4');
insert into professor values ('10101', 'Rodrigo', 'Cienc. Comp.', '65000');
insert into professor values ('12121', 'Wu', 'Economia', '90000');
insert into professor values ('15151', 'Mozart', 'Música', '40000');
insert into professor values ('22222', 'Einstein', 'Física', '95000');
insert into professor values ('32343', 'El Said', 'Historia', '60000');
insert into professor values ('33456', 'Gold', 'Física', '87000');
insert into professor values ('45565', 'Christina', 'Cienc. Comp.', '75000');
insert into professor values ('58583', 'Califieri', 'Historia', '62000');
insert into professor values ('76543', 'Singh', 'Economia', '80000');
insert into professor values ('76766', 'Crick', 'Biologia', '72000');
insert into professor values ('83821', 'Fred', 'Cienc. Comp.', '92000');
insert into professor values ('98345', 'Kim', 'Eng. Eletr.', '80000');
insert into aula values ('BIO-101', '1', '2', '2009', 'PAF III', '514', 'B');
insert into aula values ('BIO-301', '1', '2', '2010', 'PAF III', '514', 'A');
insert into aula values ('CS-101', '1', '1', '2009', 'PAF I', '101', 'H');
insert into aula values ('CS-101', '1', '2', '2010', 'PAF I', '101', 'F');
insert into aula values ('CS-190', '1', '2', '2009', 'Inst. Matem.', '3128', 'E');
insert into aula values ('CS-190', '2', '2', '2009', 'Inst. Matem.', '3128', 'A');
insert into aula values ('CS-315', '1', '2', '2010', 'PAF V', '120', 'D');
insert into aula values ('CS-319', '1', '2', '2010', 'PAF V', '100', 'B');
insert into aula values ('CS-319', '2', '2', '2010', 'Inst. Matem.', '3128', 'C');
insert into aula values ('CS-347', '1', '1', '2009', 'Inst. Matem.', '3128', 'A');
insert into aula values ('EE-181', '1', '2', '2009', 'Inst. Matem.', '3128', 'C');
insert into aula values ('FIN-201', '1', '2', '2010', 'PAF I', '101', 'B');
insert into aula values ('HIS-351', '1', '2', '2010', 'PAF III', '514', 'C');
insert into aula values ('MU-199', '1', '2', '2010', 'PAF I', '101', 'D');
insert into aula values ('PHY-101', '1', '1', '2009', 'PAF V', '100', 'A');
insert into ensino values ('10101', 'CS-101', '1', '1', '2009');
insert into ensino values ('10101', 'CS-315', '1', '2', '2010');
insert into ensino values ('10101', 'CS-347', '1', '1', '2009');
insert into ensino values ('12121', 'FIN-201', '1', '2', '2010');
insert into ensino values ('15151', 'MU-199', '1', '2', '2010');
insert into ensino values ('22222', 'PHY-101', '1', '1', '2009');
insert into ensino values ('32343', 'HIS-351', '1', '2', '2010');
insert into ensino values ('45565', 'CS-101', '1', '2', '2010');
insert into ensino values ('45565', 'CS-319', '1', '2', '2010');
insert into ensino values ('76766', 'BIO-101', '1', '2', '2009');
insert into ensino values ('76766', 'BIO-301', '1', '2', '2010');
insert into ensino values ('83821', 'CS-190', '1', '2', '2009');
insert into ensino values ('83821', 'CS-190', '2', '2', '2009');
insert into ensino values ('83821', 'CS-319', '2', '2', '2010');
insert into ensino values ('98345', 'EE-181', '1', '2', '2009');
insert into aluno values ('00128', 'Turing', 'Cienc. Comp.', '102');
insert into aluno values ('12345', 'Dijkstra', 'Cienc. Comp.', '32');
insert into aluno values ('19991', 'Fred', 'Historia', '80');
insert into aluno values ('23121', 'Chavez', 'Economia', '110');
insert into aluno values ('44553', 'Knuth', 'Física', '56');
insert into aluno values ('45678', 'Levy', 'Física', '46');
insert into aluno values ('54321', 'Williams', 'Cienc. Comp.', '54');
insert into aluno values ('55739', 'Sanchez', 'Música', '38');
insert into aluno values ('70557', 'Roberto', 'Física', '0');
insert into aluno values ('76543', 'Guido', 'Cienc. Comp.', '58');
insert into aluno values ('76653', 'Parnas', 'Eng. Eletr.', '60');
insert into aluno values ('98765', 'James', 'Eng. Eletr.', '98');
insert into aluno values ('98988', 'Tanaka', 'Biologia', '120');
insert into matricula values ('00128', 'CS-101', '1', '1', '2009', 'A');
insert into matricula values ('00128', 'CS-347', '1', '1', '2009', 'A-');
insert into matricula values ('12345', 'CS-101', '1', '1', '2009', 'C');
insert into matricula values ('12345', 'CS-190', '2', '2', '2009', 'A');
insert into matricula values ('12345', 'CS-315', '1', '2', '2010', 'A');
insert into matricula values ('12345', 'CS-347', '1', '1', '2009', 'A');
insert into matricula values ('19991', 'HIS-351', '1', '2', '2010', 'B');
insert into matricula values ('23121', 'FIN-201', '1', '2', '2010', 'C+');
insert into matricula values ('44553', 'PHY-101', '1', '1', '2009', 'B-');
insert into matricula values ('45678', 'CS-101', '1', '1', '2009', 'F');
insert into matricula values ('45678', 'CS-101', '1', '2', '2010', 'B+');
insert into matricula values ('45678', 'CS-319', '1', '2', '2010', 'B');
insert into matricula values ('54321', 'CS-101', '1', '1', '2009', 'A-');
insert into matricula values ('54321', 'CS-190', '2', '2', '2009', 'B+');
insert into matricula values ('55739', 'MU-199', '1', '2', '2010', 'A-');
insert into matricula values ('76543', 'CS-101', '1', '1', '2009', 'A');
insert into matricula values ('76543', 'CS-319', '2', '2', '2010', 'A');
insert into matricula values ('76653', 'EE-181', '1', '2', '2009', 'C');
insert into matricula values ('98765', 'CS-101', '1', '1', '2009', 'C-');
insert into matricula values ('98765', 'CS-315', '1', '2', '2010', 'B');
insert into matricula values ('98988', 'BIO-101', '1', '2', '2009', 'A');
insert into matricula values ('98988', 'BIO-301', '1', '2', '2010', null);
insert into orientacao values ('00128', '45565');
insert into orientacao values ('12345', '10101');
insert into orientacao values ('23121', '76543');
insert into orientacao values ('44553', '22222');
insert into orientacao values ('45678', '22222');
insert into orientacao values ('76543', '45565');
insert into orientacao values ('76653', '98345');
insert into orientacao values ('98765', '98345');
insert into orientacao values ('98988', '76766');
insert into horario values ('A', 'SEG', '8', '0', '8', '50');
insert into horario values ('A', 'QUA', '8', '0', '8', '50');
insert into horario values ('A', 'SEX', '8', '0', '8', '50');
insert into horario values ('B', 'SEG', '9', '0', '9', '50');
insert into horario values ('B', 'QUA', '9', '0', '9', '50');
insert into horario values ('B', 'SEX', '9', '0', '9', '50');
insert into horario values ('C', 'SEG', '11', '0', '11', '50');
insert into horario values ('C', 'QUA', '11', '0', '11', '50');
insert into horario values ('C', 'SEX', '11', '0', '11', '50');
insert into horario values ('D', 'SEG', '13', '0', '13', '50');
insert into horario values ('D', 'QUA', '13', '0', '13', '50');
insert into horario values ('D', 'SEX', '13', '0', '13', '50');
insert into horario values ('E', 'TER', '10', '30', '11', '45 ');
insert into horario values ('E', 'QUI', '10', '30', '11', '45 ');
insert into horario values ('F', 'TER', '14', '30', '15', '45 ');
insert into horario values ('F', 'QUI', '14', '30', '15', '45 ');
insert into horario values ('G', 'SEG', '16', '0', '16', '50');
insert into horario values ('G', 'QUA', '16', '0', '16', '50');
insert into horario values ('G', 'SEX', '16', '0', '16', '50');
insert into horario values ('H', 'QUA', '10', '0', '12', '30');
insert into prerequisito values ('BIO-301', 'BIO-101');
insert into prerequisito values ('BIO-399', 'BIO-101');
insert into prerequisito values ('CS-190', 'CS-101');
insert into prerequisito values ('CS-315', 'CS-101');
insert into prerequisito values ('CS-319', 'CS-101');
insert into prerequisito values ('CS-347', 'CS-101');
insert into prerequisito values ('EE-181', 'PHY-101');

4. Crie o papel secretario.

5. Crie um usuário chamado secretario1 e dê a ele o papel de secretario.

6. Dê ao papel secretario somente o privilégio de consultar todas as tabelas.

7. Acesse o banco de dados como usuário secretario1 e tente inserir um registro na tabela sala.

8. Crie o papel administrador, com todos os privilégios.

9. Remova o privilégio DROP do papel administrador.