Exercícios de criação de bancos e tabelas. Modificação de dados

Para o modelo da LOCADORA2021 – crie um banco de mesmo nome e suas respectivas tabelas conforme o diagrama anexado. Não corrija os atributos até que sejam solicitados nas questões a seguir:

PARTE 1 – CRIAÇÃO  DE TABELAS E ATRIBUTOS

A) CRIE O BANCO (LOCADORA2021) CONFORME O MODELO NA ORDEM CORRETA DAS TABELAS.
1) ALTERE O ATRIBUTO PLACA (CARRO) PARA VARCHAR(12) OBRIGATÓRIO E ÚNICO
2) ADICIONE O ATRIBUTO MODELO PARA CARRO
3) ALTERE O ATRIBUTO ANO PARA YEAR
4) REMOVA O ATRIBUTO ADICIONADO NA QUESTAO 2

5) ALTERE O ATRIBUTO DATAALUGUEL PARA DATE NOT NULL
6) ADICIONE O ATRIBUTO VALOR DOUBLE PARA O ALUGUEL
7) ALTERE O ATRIBUTO CPF PARA VARCHAR(11) UNIQUE
8) RENOMEIE O NOME DO ATRIBUTO DATANASCIMENTO PARA DATADENASCIMENTO TIPO DATE
9) ADICIONE O ATRIBUTO CNH (CARTEIRA DE HABILITAÇÃO) PARA O CLIENTE
10) ADICIONE O ATRIBUTO DDD PARA TELEFONE 

PARTE 2 – INSERÇÃO DE DADOS  – (FAZER NA ORDEM ESTABELECIDA)

1) INSIRA  10 CARROS COM TODOS OS ATRIBUTOS 
2 ) INSIRA  6 CLIENTES 
3) INSIRA 2 TELEFONES PARA CADA CLIENTE CADASTRADO
4) INSIRA 10 ALUGUÉIS DE CARROS POR CLIENTES (TABELA ALUGUEL)

5) REMOVA O ÚLTIMO TELEFONE INFORMADO
6) ALTERE A DATA DO PENULTIMO ALUGUEL REGISTRADO PARA 30/06/2021
7) ALTERE OS DADOS DO TERCEIRO CLIENTE PARA O NOME DE UM DOS SEUS AVÓS
8)  INSIRA UM ALUGUEL PARA O PRIMEIRO CARRO CADASTRADO E O ÚLTIMO CLIENTE COM A DATA DE HOJE
9) REMOVA O PRIMEIRO TELEFONE CADASTRADO
10) INSIRA 2 NOVOS CLIENTES

PARTE 3 –  SELEÇÃO DE DADOS

1) MOSTRE OS DADOS DOS CLIENTES ORDENADOS PELO NOME
2) MOSTRE  O TOTAL DOS CARROS AGRUPADOS PELA COR
3) MOSTRE O TOTAL DE ALUGUEIS POR CLIENTES
4) MOSTRE OS CLIENTES QUE NUNCA ALUGARAM CARROS
5) MOSTRE OS 3 CLIENTES MAIS VELHOS
6) MOSTRE OS CLIENTES CUJO NOME EM ALGUMA PARTE TENHA A LETRA “O”
7) MOSTRE O VALOR TOTAL DE ALUGUEL POR CARRO
8) MOSTRE O VALOR TOTAL DE ALUGUEL POR DATA DO ALUGUEL

Banco exemplo: Livraria

Banco  com o script completo aqui [Livraria]

 

1. Mostre os dados dos autores e seus livros
2. Mostre os dados dos clientes e para aqueles que tem vendas, data e total da venda
3. Mostre o total de livros por editora
4. Mostre o o valor total em estoque por gênero.

Parte 2

1 – Mostre o nome dos clientes e seu telefone data e valor da venda, ordenados por nome
2 – Mostre o maior preco de um livro para cada gênero
3 – Mostre  o menor preco de um livro que já foi vendido
4 – Mostre o total de livros escritos por autores
5 – Mostre o total de livros cadastrados e para aqueles que tem vendas a data da venda

6 – Mostre o título do livro e quantidade em estoque para livros que tenham entre 5 e 20 itens no estoque, ordenados pela quantidade de estoque decrescente e suas respectivas editoras e gênero
7 – Mostre os títulos de livros que cada cliente comprou

Banco exemplo: Livraria

Script  para carregar o banco e os dados aqui

Lc: Modelagem

1)Uma Loja de Peças atende pedidos de clientes. A loja mantém um cadastro de todos os clientes com as seguintes informações: RG, CIC, nome, endereço. Um pedido de um cliente contém um número de nota fiscal, preço total e data de realizado e associado a ele todas as mercadorias incluídas nesse pedido.

Para cada mercadoria que a loja vende são registrados código, descrição, preço e quantidade em estoque. Uma mesma mercadoria pode ser fornecida por vários fornecedores para os quais a loja mantém um registro com um código interno único, nome, endereço e telefone.

Existem fornecedores nacionais e internacionais. Para os fornecedores nacionais além das informações anteriores é mantido o CGC e cidade onde registrada a empresa. Para os fornecedores internacionais é mantido adicionalmente qual o meio de transportação, pais e moeda em que são realizadas as negociações.

2. Considere a descrição de um sistema de venda de passagens aéreas dada abaixo e desenhe um diagrama ER. Para um passageiro são registrados o número do documento de identidade e o seu nome. Um avião é caracterizado por um número de série único e por um modelo. Um assento é uma posição única em um avião, identificada por um código. Um assento é da classe econômica ou executiva. Um avião possui vários assentos e pode ser usado em vários voos. Um voo é identificado por um número e utiliza: um avião, um piloto, um aeroporto de partida e outro de chegada. Um passageiro pode reservar um assento em um voo.

3. Num aeroclube, estão inscritos pilotos, instrutores e alunos de pilotagem. Todos sócios (inscritos) são identificados pelo número de matrícula, e caracterizados por nome, endereço e idade. Os pilotos possuem um número de brevê (único). Os instrutores são pilotos com formação adicional de instrutor, e deve ser registrado o nome do curso, a data de obtenção do diploma, bem como a instituição. Para os alunos de pilotagem, guarda-se o registros de todas suas saídas para contabilização de horas para obtenção do brevê.

Para cada saída registra-se a data, instrutor, hora de saída e de chegada, bem como o parecer do instrutor sobre o voo. A escola só ministra cursos básicos, e portanto não há professores que são alunos de cursos avançados.

Para emissão do brevê, é necessário que o aluno comprove ter o número de horas mínimo de voo, bem como apresente os pareceres dos instrutores sobre as habilidades desenvolvidas a cada aula prática.

Vídeos de Joins

LC: Modelo de dados

1 – Uma livraria mantém o cadastro de livros disponíveis para a venda. Para cada livro são armazenados código, nome, língua e ano em que foi escrito. Para os autores é mantido igualmente um cadastro que inclui nome, data de nascimento, pais de nascimento e uma breve nota biográfica.

Cada livro pode ter vários autores e para um mesmo autor podem existir vários livros cadastrados. Um autor pode estar incluído no cadastro ainda quando não exista um livro seu para venda. As editoras são incluídas no cadastro a partir do seu nome, endereço, telefone. Uma editora pode estar cadastrada mesmo quando não existam livros editados por ela em venda. Para um mesmo livro podem existir várias edições realizadas por editoras diferentes ou em anos diferentes.

Cada edição tem um código (ISBN), preço, ano, número de páginas e quantidade em estoque. Considere que um livro pode ser cadastrado se existe pelo menos uma edição do mesmo para venda.

2 – Uma academia de ginástica deseja manter um controle do seu funcionamento. Os alunos são organizados em turmas associadas a um tipo específico de atividade. As informações sobre uma turma são número de alunos, horário da aula, duração da aula, data inicial, data final e tipo de atividade.

Cada turma é orientada por um único instrutor para o qual são cadastrados RG, nome, data de nascimento, titulação e todos os telefones possíveis para sua localização. Um instrutor pode orientar várias turmas que podem ser de diferentes atividades.

Os dados cadastrados dos alunos são: código de matricula, data de matrícula, nome, endereço, telefone, data de nascimento, altura e peso. Um aluno pode estar matriculado em várias turmas se deseja realizar atividades diferentes e para cada matrícula é mantido um registro das ausências do aluno. Para cada turma existe um aluno monitor que auxilia o instrutor da turma, sendo que um aluno pode ser monitor no máximo em uma turma.

Questões usando Joins

Usando o modelo Clinica realize as questões a seguir:

1 – Mostre os funcionários do sexo feminino e o número do seu ambulatório
2 – Mostre o nome das doenças e as datas de atendimentos
3 – Mostre o nome do paciente e dia e hora da consulta
4 – Mostre as doenças atendidas e os médicos envolvidos no atendimento
5 – Mostre os médicos com idade acima de 30 anos que atenderam gripe
6 – Mostre os médicos e suas especialidades
7 – Mostre os pacientes do sexo masculino que foram atendidos em consultas
8 – Mostre a média de idades dos medicos por especialidade
9- Mostre os ambulatórios e para aqueles que tem atendimentos em consultas o total de consultas
10 – Mostre um ranking com o total de atendimentos por especialidade médica

LC: Atividade de modelos de dados

1 – Em uma concessionária são cadastrados cada um dos carros para venda ou já vendidos. Para estes são armazenados: número de chassi, placa, marca, modelo, ano e cor. Cada carro é fornecido por uma montadora. Das montadoras são armazenados CNPJ, Nome, endereço e telefone. Para os clientes é cadastrado RG, nome, telefone e endereço. Clientes podem estar cadastrados sem que tenham realizado uma compra. Eventualmente quando fizeram um orçamento ou teste, seus dados foram pedidos. Para cada venda de carro é registrado um número de nota fiscal, o valor total de venda assim como a forma de pagamento. Cada venda é realizada por um vendedor para o qual é registrado RG, nome, anos de experiência e telefone.

 

2 – Uma agência de financiamento de projetos de pesquisa deseja criar um sistema de banco de dados para gerenciar seu funcionamento. Para cada projeto são cadastrados: um código interno, título, duração do projeto, instituição onde será realizado e área de pesquisa. As áreas de pesquisa estão predefinidas é para cada uma delas são cadastrados código, nome, descrição e um índice que indica sua relevância econômica.

Para cada pesquisador solicitante são cadastrados: RG, CPF, nome, sexo, data de nascimento, grau cientifico e instituição onde foi alcançado esse título. Note-se que um mesmo pesquisador pode ter vários projetos em análise. Um pesquisador é cadastrado no sistema unicamente quando o primeiro dos seus projetos é submetido.

A agência recebe os projetos submetidos pelos pesquisadores e associa cada um destes a um assessor que deve aprovar ou não o financiamento. Para estes assessores são cadastrados: RG, CPF, nome, sexo, data de nascimento, grau científico, instituição onde trabalha e as áreas nas quais tem capacidade de avaliar projetos. Estas áreas de pesquisa devem ser definidas dentre a lista de áreas predefinidas antes mencionadas. Um assessor pode ser cadastrado mesmo sem ter analisado nenhum projeto.

Quando um projeto é enviado a um assessor para análise é cadastrada a data deste envio. Posteriormente, quando o assessor retorna sua avaliação são também cadastrados a data de resposta e o resultado de aprovação ou não do projeto.

Revisão para o terceiro ano

Modelos no Workbench

 

Revisão do comando SELECT

LC: Modelagem – Exercícios

Uma locadora mantém um cadastro de todos seus usuários com as informações básicas: RG, nome, endereço, CNH e idade. Todo usuário cadastrado pelo menos realizou uma locação na empresa. Cada carro da frota é registrado com vários atributos para sua descrição: número de chassi, placa, marca, modelo, ano e cor. Quando um usuário aloca um carro são registradas data e hora de locação.

No banco de dados Os carros da frota são organizados por categorias. Uma categoria é descrita por código, um nome de categoria (Ex: Primeira classe), preço da diária da categoria e uma descrição das características dessa categoria. Todo carro pertence a uma categoria que define suas características e o preço da diária. Para cada carro da frota é mantido um histórico dos concertos realizados, indicando dia, valor, descrição do serviço e oficina que o realizou.

b)

Uma vídeo-locadora trabalha com o aluguel de DVDs, CDs e cartuchos de jogos. Todos os 3 têm um código, o título que o descreve e a categoria. Adicionalmente, os DVDs e os BDs possuem a sinopse e os artistas principais. Já os cartuchos de jogos, possuem adicionalmente apenas o nome do fabricante. A locadora empresta apenas para os clientes cadastrados. Nome, endereço, data de nascimento e telefone dos clientes ficam anotados em uma ficha junto com um código numérico sequencial atribuído ao cliente no momento do cadastro.

Cada cliente pode alugar um ou mais objetos de locação (DVD, BD ou Cartucho), sendo que cada um destes só pode ser alugado por apenas 1 cliente em um determinado momento (data). É interesse da locadora, entretanto, manter um histórico dos aluguéis já realizados, a fim de gerenciar o perfil dos seus clientes.