Sistema de Banco de Dados - Trabalho Prático

Prévia do material em texto

Sistemas de Banco de Dados Prof. Dr. Fernando Martins
PONTIFÍCIA UNIVERSIDADE CATÓLICA
Faculdade de Informática
INF 1341 - Sistemas de Banco de Dados
Trabalho Prático 2 - SQL e Normalização
Professor: Dr. Fernando Martins
Monitores: Juliana Santos — Rafael Oliveira
Peŕıodo: 2024.2
Valor: 15 pontos
Entrega: 25/11/2024 até 23:59 via Moodle
Formato de Entrega:
• Arquivo ZIP contendo: scripts SQL (.sql) + relatório PDF
• Nomeie: TP2 SeuNome Matricula.zip
• Scripts devem executar sem erros no MySQL 8.0+
• Relatório: máximo 10 páginas, fonte 12pt
• Trabalhos copiados: nota zero para todos os envolvidos
1 Contexto do Sistema (Obrigatório - Leia com atenção)
Você foi contratado para desenvolver o banco de dados de um Sistema de Gerencia-
mento de Biblioteca Universitária. O sistema deve gerenciar:
• Livros: ISBN, t́ıtulo, editora, ano, número de exemplares
• Autores: CPF, nome, nacionalidade, data de nascimento
• Usuários: matŕıcula, nome, tipo (aluno, professor, funcionário), email, telefone
• Empréstimos: data de empréstimo, data de devolução prevista, data de devolução
real
• Multas: valor, data de aplicação, status (paga/pendente)
Regras de Negócio:
1. Um livro pode ter vários autores e um autor pode ter escrito vários livros
2. Alunos podem emprestar até 3 livros por 14 dias
3. Professores podem emprestar até 6 livros por 30 dias
4. Funcionários podem emprestar até 4 livros por 21 dias
5. Multa de R$ 2,00 por dia de atraso
6. Usuários com multas pendentes não podem fazer novos empréstimos
7. Um exemplar não pode ser emprestado se já estiver emprestado
1
Sistemas de Banco de Dados Prof. Dr. Fernando Martins
2 Parte 1 - Modelagem e Normalização (4,0 pontos)
Exerćıcio 1.1 - Modelo Conceitual (1,5 pontos)
Crie o Diagrama Entidade-Relacionamento (DER) completo para o sistema des-
crito. Seu diagrama deve incluir:
• Todas as entidades necessárias
• Atributos (incluindo chaves primárias e atributos multivalorados)
• Relacionamentos com cardinalidades corretas
• Atributos dos relacionamentos (quando aplicável)
Entrega: Desenhe usando ferramenta digital (draw.io, Lucidchart, MySQL Work-
bench) e inclua no relatório.
Exerćıcio 1.2 - Normalização (1,5 pontos)
Considere a seguinte tabela não normalizada:
MatriculaUsuario NomeUsuario ISBN TituloLivro Autores
2021001 João Silva 123456 BD Avançado Ana Costa, Pedro Lima
2021001 João Silva 789012 SQL na Prática Maria Santos
2021002 Ana Santos 123456 BD Avançado Ana Costa, Pedro Lima
Pede-se:
(a) Identifique as dependências funcionais
(b) Em qual forma normal a tabela se encontra? Justifique.
(c) Normalize até a Terceira Forma Normal (3FN), mostrando cada etapa
(d) Apresente o esquema final normalizado
Exerćıcio 1.3 - Modelo Lógico (1,0 ponto)
Converta o DER do Exerćıcio 1.1 para o Modelo Relacional. Para cada tabela, espe-
cifique:
• Nome da tabela
• Atributos e tipos de dados
• Chave primária (PK)
• Chaves estrangeiras (FK) com ações de integridade referencial
2
Sistemas de Banco de Dados Prof. Dr. Fernando Martins
3 Parte 2 - Implementação SQL (5,0 pontos)
Exerćıcio 2.1 - DDL - Criação do Banco (2,0 pontos)
Implemente em SQL o esquema do banco de dados. Seu script deve:
• Criar o banco de dados biblioteca
• Criar todas as tabelas com:
– Tipos de dados apropriados
– Constraints de chave primária e estrangeira
– Constraints CHECK quando aplicável
– Índices em colunas frequentemente consultadas
• Incluir comentários explicativos
Exemplo de estrutura esperada:
1 CREATE TABLE Livro (
2 ISBN VARCHAR (13) PRIMARY KEY ,
3 titulo VARCHAR (200) NOT NULL ,
4 editora VARCHAR (100) ,
5 ano_publicacao YEAR ,
6 num_exemplares INT DEFAULT 1,
7 CONSTRAINT chk_ano CHECK (ano_publicacao >= 1500 AND
ano_publicacao