Problema do Canil - Gabarito

Essa postagem é o gabarito do problema do Canil proposto durante as aulas de restrições em SQL. Na primeira parte do problema (Para acessar o  problema, clique aqui) onde a partir das anotações do cliente você deveria criar um modelo Entidade-Relacionamento. A figura 1 mostra a solução que atende a demanda da primeira parte do problema.

Figura 1. Modelo Entidade Relacionamento do problema do Canil.

Na primeira parte do problema, não havia normalização das tabelas o que irá gerar redundância de dados. Para corrigir esses problemas, a 2ª parte da atividade (Clique aqui para acessar atividade) propunha a normalização das tabelas e a adição do campo colocação. O novo modelo conceitual do problema do Canil pode ser visto na figura 2.

Figura 2. Novo DER realizado após a normalização das tabelas.

Com base no modelo de Entidade-Relacionamento da figura 2, o modelo lógico/relacional foi criado utilizando as regras de derivações de modelos (mapeamento ER). A figura 3 mostra o resultado dessa derivação de modelos.

Figura 3. Modelo lógico/relacional do problema do Canil.

A partir do modelo apresentado na figura 3, bastava criar o banco de dados no Oracle utilizando os comandos discutidos em sala de aula. Por acreditar que é desnecessário deixar o script completo do modelo acima, abaixo deixarei somente o código de como criar a tabela "Participa" que será responsavel por relacionar as tabelas "Cão" e "Competição".

create table participa(
Cod_cao number(4) constraint fk_cao references cao(cod_cao),
Cod_comp number(4) constraint fk_competicao references competicao(cod_comp),
constraint pk_composta_participa  primary key(Cod_cao, Cod_comp)

);

Nesta atividade onde a metodologia PBL(Problem Based Problem) foi aplicada, o aluno treinou:

    - Normalização de tabelas

    - Criação de modelo conceitual (DER)
    - Derivação de modelo conceitual para modelo lógico
    - Criação de banco de dados
    - Implementações de restrições PK e FK
    - Implementações de restrições em relacionamento N:N


*Não lembra como criar as chaves estrangeiras, acesse as notas de aulas clicando aqui....

Não esqueça do like do vídeo e de curtir nossa pagina no facebook no link abaixo:

Inserindo Dados no Oracle 11g

Os comandos do subconjunto DML (Data Manipulation Language), ou Linguagem de Manipulação de Dados, trabalham os registros (dados) de um banco de dados e têm por finalidade criar novas linhas, atualizar o que já foi criado e eliminar qualquer registro de uma tabela. Neste psot aprenderemos inserir dados (registros) em tabelas já criadas usando o SQL PLUS, ferramenta do SGBDR Oracle. Quem não lembra as como criar tabelas ou mesmo se voce ainda não fez o download do Oracle 11g, seguem alguns links para auxiliar seus estudos:

- Como fazer o download do SQL
- Criação de tabelas no SQL (DDL)
- Alterando estruturas das tabelas (DDL)

No Oracle, o comando utilizado para realizar essa ação é o INSERT. Nesse comando são passados os campos e os valores que serão gravados no registro, confira a sintaxe do comando INSERT no Oracle abaixo:

INSERT INTO  nome_da_tabela  (atributo_1 , atributo_2,...)
VALUES (Valor_1, Valor_2,....);

onde atributo é o nome do campo criado na definição dos dados e valor o dado que você deseja inserir no campo.

Com base na sintaxe acima, vamos inserir um registro na tabela
           Cliente(Codigo, nome, telefone, cidade, cpf, sexo e estado_civil)

o comando para inserir os dados na tabela cliente é mostrado na figura 1.

Figura 1. Inserção de regsitro na tabela cliente.

No comando da figura 1 podemos observar que alguns dados são inseridos usado aspas e outro não. Isso remete a criação da tabela onde definimos qual tipo de dados será criado. De maneira geral, dados tipo data(date) e  alfanuméricos devem ser inseridos entre aspas.  Já os dados definidos como numéricos não precisam de aspas. Observe também que cada dado é separado por virgula.

Quando usamos o comando INSERT, não precisamos inserir todos os dados de um registro, para inserir um registro parcial, basta indicar somente os campos que receberão valores conforme exemplo abaixo onde iremos inserir somente o código e o nome do cliente.

              INSERT INTO  Cliente  (Codigo, Nome) VALUES (12, Patalógica);

Espero ter ajudado com esse resumo, qualquer dúvida, mande uma mensagem em nossas redes sociais......

Não esqueça de curtir nossa pagina no facebook no link abaixo:

Notas de aulas - Banco de dados

	Vídeo extra - Modelo lógico
* Vídeo extra- Restrições
	Exercício extra: Clique aqui
Exercício extra: Clique aqui.
	Subquery: Vídeo aula
Aula sobre Joins Exercícios:  Clique aqui	Aula 14
Aula 15	Aula 16
Aula 16	Aula 17

Comando SELECT - Principais cláusulas

Desenvolvida nos anos 70, SQL é uma linguagem de pesquisa declarativa para banco de dados relacional. Muitas das características originais do SQL foram inspiradas na álgebra relacional. Essa caracteristica permite a seleção de dados previamente cadastrados dentro da base atraves da instruções SELECT. A sintaxe abaixo apresenta a sintaxe mais basica da instrução.

SELECT <lista_de_campos ou *> 

FROM <nome_da_tabela>

Exemplos:

SELECT CODIGO, NOME FROM CLIENTE

/*Seleção dos campos CODIGO e NOME da tabela cliente*/

SELECT * FROM CLIENTE

/*Seleção de todos os campos da tabela cliente, onde o "*" indica que todos campos e registros devem ser selecionados*/

Nos exemplos acima, todos os registros da tabela CLIENTE serão selecionados, agora imagine se a tabela CLIENTE tiver 150 mil registros!!!!!

Achar um cliente ou um grupo de clientes especificos dentre dessa seleção não será uma tarefa facil. Para refinar as buscas, diversas cláusulas podem ser adicionadas à instrução permitindo a extração de dados especificos da base. Na sintaxe abaixo temos algumas clasulas que podem ser adicionadas a instrução.

SELECT [DISTINCT | ALL]   * |nomes_das_colunas

FROM nome_da_tabela

[WHERE campo <operador> termo]

[ORDER BY  nome_da_coluna];

Com base na sintaxe acima, vamos definir alguma possibilidades que irão refinar nossas seleções:

Selecionando registros específicos:

Para selecionar registros específicos devemos utilizar a cláusula Where, onde através dessa cláusula poderemos atribuir condições de filtragem para selecionar um ou mais campos da tabela. A partir do exemplo apresentado na figura 1 iremos refinar nossa seleção com a cláusula where.

Figura 1. Clientes cadastrados na tabela CLIENTE da base de dados.

Com base na tabela apresentada na figura 1, vamos selecionar todas as clientes da base de dados, ou seja, vamos selecionar todos registros cujo sexo seja feminino. Para executar a demanda proposta vamos utilizar o comando abaixo cuja execução do comando pode ser visualizadas na figura 2.

SELECT  * FROM Cliente
WHERE sexo=‘F’;

Figura 2. Seleção de todas as clientes da base de dados.

Em outro exemplo, na figura 3 temos um exemplo onde serão selecionados todos clientes de São Paulo.

Figura 3. Seleção de todos clientes que moram em São Paulo.

Dependendo da campo utilizado na consulta, outros operadores relacionais podem ser utilizandos. Na Tabela 1 temos uma relação de operadores relacionais que podem ser utilizados na clausula Where. Na coluna exemplo, o campo preço é utilizado como referencia.

Tabela 1. Operadores relacionais que podem ser utilizando na clausula WHERE.

Em geral, os operadores acima são bem intuitivos(caso voce tenha alguma duvida, pode postar aqui no blog que responderei o mais rapido possivel), devido a essa facilidade, irei comentar somente os operadores LIKE, BETWEEN e IN.

Operador LIKE:

O operador LIKE é utilizando em colunas definidas como dados tipo caractere onde a consulta pode ser realizada utilizando somente parte da string armazenada na base de dados. Imagine que você não lembra o sobrenome do seu cliente (Figura 1), para realizar uma consulta usando somente uma parte da string (neste caso só o primeiro nome do cliente) devemos associar o operador Like e o "%" que servirá como curinga. Na figura 4 temos um exemplo de como selecionar todos os clientes cujo nome começa com "Zé".

Figura 4. Seleção usando operador LIKE e curinga "%"

Para selecionar todos cliente "Zé" não importando o sobrenome, o curinga foi utilizando logo após a palavra "Zé%". O curinga pode seu utilizado em qualquer ponto da string e se necessario, pode ser utilizando mais de um curinda conforme os exemplos das figura 5 e 6.

Figura 5. Exemplo usando operador like e curinga no inicio da string

Figura 6. Exemplo usando operador like e dois curingas.

Alem do curinga "%" que irá substituir uma sequencia de caracteres, podemos utilizar o sublinhado "_" como curinga, substituindo um caractere especifico.

Operador Between:

O operador Between é usado quando precisamos selecionar linhas de uma tabela cujo valor de um campo encontra-se dentro de um intervalo específico. Com diversas aplicações, esse tipo de consulta pode ser usado para filtrar os dados num determinado intervalos de tempo (datas), ou estabelecer intervalos de classe muito utilizandos em estatística. Sua sintaxe é a seguinte: 

SELECT campos FROM tabela 

WHERE campo BETWEEN inicio_intervalo AND fim_intervalo;

Na figura 7 temos um exemplo onde é exibido todos personagens da tabela Ducktales criados entre 1940 e 1950.

Figura 7. Uso do operador Between para filtrar periodo

Usando o mesmo principio apresentado na figura 7, podemos utilizar o operador NOT BETWEEN (sintaxe abaixo) e exibir todos personagens da tabela Ducktales criados fora do intervalo de 1940 a 1950. O resultado da seleção utilizando o NOT BETWEEN pode ser visto na figura 8.

SELECT campos FROM tabela 

WHERE campo NOT BETWEEN inicio_intervalo AND fim_intervalo;

Figura 8. Uso do operador NOT Between selecionando todos personagens fora do intervalo.

Operador IN:

O operador IN trabalha com a ideia de conjuntos selecionando informações que estão ou não contidas nesse conjunto. Diferente do operador relacional de igualdade (=) que seleciona dados a paritr de um valor especifico, o IN permite selecionar diversos valores com base numa lista de valores. Sua sintaxe é a seguinte:

SELECT campos FROM tabela WHERE campo IN (valor1, valor2, valor3);

.....



Ordenando os registros para exibição (order by):

A cláusula ORDER BY é utilizada para mostrar as linhas da consulta seguindo o critério de ordenação (crescente ou decrescente) definido pelo utilizador usando uma coluna como referencia. Nas figura 9 e 10 podemos ver exemplo de ordenação crescente e decrescente.

Figura 9. Seleção ordenada pelo campo NOME em ordem crescente.

Figura 10. Seleção ordenada pelo campo NOME em ordem decrescente.

Pesquisa case sensitive:

O oracle é case sensitive, ou seja, quando vamos realizar uma seleção é preciso diferenciar letras maisculas de letras minusculas. Como nem sempre temos certeza sobre como a string foi inserida na base de dados, em geral são utlizados as funções LOWER() e UPPER() que tornam a seleção case insensitive. Esse assunto será tratado em outra postagem.....

Ainda ta com duvida, curta nossa pagina no facebook no link abaixo e poste sua duvida lá.

Banco de dados - Jogos de Video Game

Essa atividade é baseada no trabalho entregue pelos alunos da disciplina de Desenvolvimento de Banco do Campus Vila Prudente:

GABRIEL DE BARROS PONTES

NATHALYA APARECIDA MUNIZ PELICAS

PAULO CESAR DA SILVA

Instruções sobre a atividade

1ª Atividade DEVE ser realizada de maneira COLABORATIVA em grupos de 2 a 4 alunos. 

2ª Atividade vale 10 ponto e e faz parte da nota da Avaliação 1.

3ª Atividade de Derivação de modelos deverá ser entregue para o professor na próxima aula.

4ª As questões 1, 1.2 e 3 devem ser enviados por email.

5ª O título do e-mail de entrega dever ser [ATIVIDADE DE BD - GAMES].

6ª Somente 1 integrante do grupo deve enviar  o e-mail.

7ª No corpo do e-mail deve constar o nome e RA de todos integrantes do grupo.

8ª Responder individualmente as questões no FORMs no final da atividade.

Os alunos citados acima desenvolveram um Banco de dados para guardar informações sobre jogos de vídeo game. O DER da figura apresenta a modelagem conceitual do Banco de dados criado para armazenar as informações dos jogos.....

Questão 1. Derivação de modelos - Faça no caderno a derivação do  DER abaixo (entregar para o professor na próxima aula com o nome dos alunos do grupo).

OBSERVAÇÕES:  Para facilitar a visualização do modelo, o diagrama abaixo não possui atributos,  use as tabelas do dicionario de dados da Questão 2 para definir os campos do modelo lógico. 

Figura 1. Modelagem conceitual - Diagrama de Entidade Relacionamento do BD jogos.

Questão 1.2. Implementação - Implemente o modelo acima no Oracle 11g usado como referencia o Dicionario abaixo.

OBSERVAÇÕES:  No dicionario abaixo, não há referencias sobre as chaves estrangeiras [FK], mas todas as FK's devem ser criadas no BD.

Questão 2 - Inserção de dados - Inserir 3 registros em cada tabela, se tiver duvida sobre o que inserir, leia a descrição do campo no Dicionario de dados.

Ainda ta com duvida, curta nossa pagina no facebook no link abaixo e poste sua duvida lá.

DEFAULT - Atribuindo valores padrão aos campos

Conforme visto em nossa postagem "Criação de tabelas - Oracle" quando estamos criando uma tabela e seus respectivos campos (atributos ou colunas), ao definir os campos e os tipos dos dados, é possível atribuir um valor padrão ao campo usando a opção DEFAULT. Isto significa que, se um registro for inserido e o usuário não digitar nenhum valor na coluna onde um Default foi definido, o Oracle irá atribuir automaticamente um valor padrão. Para relembrar o comando Create, na Figura 1 temos a sintaxe do comando usando para criação de tabelas.

Figura 1. Sintaxe do comando create table.

Com base na sintaxe acima, uma tabela será criada para armazenar informações sobre personagens da série "DuckTales - Os Caçadores de Aventuras". Como a maioria dos personagens do desenho moram na cidade de Patópolis, vamos definir o campo cidade com o valor de padrão (Default) de "Patópolis", assim só será necessário digitar a cidade para os personagens que não moram em Patópolis. Na Figura 2 temos o comando usado para criar a tabela que chamamos de DuckTales.

Figura 2. Criação da tabela DuckTales onde foi definido um valor padrão para o campo cidade 

Após criar a tabela (Figura 2) vamos realizar a inserção dos dados onde não iremos digitar nenhum valor para o campo cidade (campo que também possui a restrição NOT NULL). A rotina de inserção de dados é mostrada na Figura 3.

Figura 3. Inserção de dados na tabela DuckTales.

O mesmo procedimento de inserção de dados (Figura 4) foi realizada para cadastrar um personagem que não habita na cidade de Patópolis, neste caso, seremos forçados a a digitar a cidade do personagem conforme mostra a linha 6 do comando.

Figura 4. Inserção de dados na tabela DuckTales não usando o valor padrão.

Após inserir os dados (Figuras 3 e 4) a Figura 5 mostra os dados que foram inseridos onde a cidade Patópolis aparece cadastrada mesmo sem ter sido digitada.

Figura 5. Inserção de dados na tabela DuckTales não usando o valor padrão.

Ainda ta com duvida, curta nossa pagina no facebook no link abaixo e poste sua duvida lá.

Operador de Concatenação

Quando uma seleção de dados é realizada através do comando Select você pode vincular colunas à outras colunas assim como adicionar String ao resultado da sua consulta, usando o operador de concatenação  | | . onde as colunas/strings em cada lado do operador são concatenadas para formar uma coluna de saída única. Para entender melhor, observe a figura 1 onde temos uma tabela aluno. Nesta tabela, o endereço do aluno esta dividido em 3 campos: Logradouro, compl_aluno e cidade_aluno e o nome esta dividido em nome_aluno e sobrenome_aluno.

Figura 1. Tabela de alunos

Usando as informações apresentadas na figura 1, a concatenação entre os campos usados para forma o endereço do aluno sera dado por: (logradouro||compl_aluno||cidade_aluno) e resultando da seleção usando a concatenação de colunas é apresentada na figura 2.

Select logradouro||compl_aluno||cidade_aluno From aluno;

Figura 2. Seleção usando concatenação dos campo de endereço

Lembrando que o nome da coluna que será exibido pode ser renomeado usando a clausula AS conforme figura 3.

Figura 3. Seleção usando concatenação e renomeação de colunas usando clausula AS.

Assim como concatenar colunas, é possível criar seleções adicionando Strings ao conteúdo selecionado na tabela. Abaixo é possível verificar o comando usado para realizar a concatenação de colunas e uma string e o resultado desta concatenação é apresentado na figura 4. 

SELECT nome_aluno||' mora na '|| logradouro AS teste_concatenação FROM aluno;

Figura 4. Resultado de seleção usando concatenação de colunas (nome_aluno e logradouro) e da string 'mora na'.

No exemplo acima foi concatenada a String 'mora na' aos campos nome_aluno e logradouro. Não esqueça que todas vez que uma string for concatenada, o valor dessa string deve estar entre aspas simples.

Espero que tenha ajudado, se curtiu nosso conteúdo, curtas nossas redes sociais....

Alterando estrutura da tabela - Alter Table

Nesta postagem iremos apresentar ao leitor o comando ALTER TABLE do SGBD Oracle. Enquadrada na categoria de Data Definition Language (DDL), o comando alter table é usado quando é necessário realizar alguma alteração na estrutura das tabelas já criadas na base de dados. Basicamente o comando se resumirá ao exemplo abaixo:

ALTER TABLE <nome_da_tabela>  CLÁUSULA  (colunas/especificações);

As diversas clausulas realizadas pelo comando serão discutidas na sequencia:


Modificando uma coluna na tabela (MODIFY):

Para modificar a estrutura de uma coluna (já criada), devemos utilizar a clausula MODIFY do comando alter table conforme sintaxe abaixo:

ALTER TABLE <nome_da_tabela>  MODIFY (colunas tipo_do_dado [restrições],..... );

Na figura 1, iremos mostrar um exemplo do comando onde o campo NOME_CLI terá o tamanho de sua variável alterado de 20 para 25 posições. No exemplo o comando describe é utilizado antes e depois do comando ALTER TABLE mostrando a mudança realizada após a execução do comando.

Figura 1. Comando alterando do tamanho da variável nome_cli;

Adicionando uma coluna a tabela (ADD):

Quando é necessário adicionar um novo atributo (coluna) a uma tabela já existente a seguinte sintaxe deve ser usada:

ALTER TABLE <nome_da_tabela>  ADD (colunas tipo_do_dado);

Na figura 2 é possível ver a inclusão do campo CPF a tabela cliente.

Figura 2. Inclusão do campo CPF a tabela ciente.

Com um único comando é possível incluir mais de um campo numa tabela já existente.
Também traves do ADD é possível incluir atributos com restrições, na figura 3 é possível ver a inclusão dos campos logradouro e complemento onde o campo logradouro possui uma restrição não nomeada NOT NULL.

Figura 3.  Inclusão de dois campos (Logradouro, complemento) na tabela cliente.

A clausula ADD também pode ser usada para adicionar uma restrição (Constraint) à um campo já existente conforme a sintaxe abaixo:

ALTER TABLE  nome_da_tabela ADD  CONSTRAINT restrição tipo (coluna);

Usando como base a sintaxe acima, na figura 4 é possível ver como a restrição de chave primaria foi adicionada ao campo COD_CLI da  tabela cliente.

Figura 4. Inclusão de restrição de chave primeira (Primary Key) ao campo COD_CLI usando ALTER TABLE.

Excluindo uma coluna a tabela (DROP COLUMN):

A cláusula que elimina um atribuoda tabela é o DROP COLUMN cuja sintaxe pode ser verificada abaixo:

ALTER TABLE  nome_da_tabela DROP COLUMN nome_da_coluna;

Na figura 5 é possível ver o comando sendo utilizado para excluir o atributo ID_SEXO. O comando describe é utilizado para mostrar o sucesso da alteração.

Figura 5. Excluindo uma coluna usando DROP COLUMN.

Não esqueça que quando a coluna é excluída, todos os dados armazenados nessa coluna serão excluídos também!!!!!

Renomeando uma coluna a tabela (RENAME COLUMN):

Finalizando nossa postagem veremos a cláusula RENAME COLUMN que é responsavel por renomear uma coluna. Abaixo é possível ver a sintaxe do comando:

ALTER TABLE  nome_da_tabela RENAME COLUMN nome_da_coluna TO  novo_ nome_da_coluna ;

Na figura 6 é possível ver um exemplo onde a coluna COMPLEMENTO da tabela cliente foi alterada para COMPL.

Figura 6. Renomeando colunas com clausula RENAME COLUMN.