Modelo Conceitual, Lógico e Físico de Banco de Dados

LABORATORIO DE BANCO DE DADOS
MODELO CONCEITUAL, LOGICO E FISICO DE BANCO DE DADOS
MODELO CONCEITUAL
Considere o modelo conceitual hipotético e incompleto
* matr
O nome
O data admissao
PROFESSOR
* matricula
O nome
(1,N)
ministrada
O sexo
ALUNO
(0,1)
(1,N)
Cursa
DISCIPLINA
(0N)
O nome
O carga horaria
O nota
* codigo
MODELO LÓGICO
O modelo lógico, também referenciado como esquema de banco de dados , é gerado aplicando-se as
regras de derivação de tabelas para um modelo relacional de banco de dados. Dependendo da situação é
possível definir mais de um modelo logico. Neste caso será gerado duas soluções. :
Solução 01
Esquema da base de dados:
ALUNO(matricula, nome, sexo)
CURSA(código, matricula, nota)
DISCIPLINA(código, nome, carga_horária, matri)
PROFESSOR(matri, nome, data_admissão)
Obs: os atributos em vermelho são as chaves estrangeiras
Solução 02
Esquema da base de dados:
ALUNO(matricula, nome, sexo)
CURSA(código, matricula, nota)
DISCIPLINA(código, nome, carga_horária)
ALOCA_PROF_DISCIPLINA(matri, codigo)
PROFESSOR(matri, nome, data_admissão)
Obs: os atributos em vermelho são as chaves estrangeiras
Nota de Aula
Prof. Sidney Vieira
1
MODELO FISICO
O Modelo físico do banco de dados é definido empregando-se a DDL(linguagem de definição de
Dados) da SQL e outros recursos específicos de cada SGBD..
Os comandos que criam as tabelas descrevem o modelo físico do banco de dados. O Modelo físico
será gerado com base na solução 01 de esquema de banco de dados para o SGDB POSTGRES. Será dada 3
soluções físicas para o a solução logica 01.
Solução 01
CREATE TABLE aluno(
matricula INTEGER NOT NULL,
nome VARCHAR(40),
sexo VARCHAR(1)
);
CREATE TABLE professor(
matric INTEGER NOT NULL,
nome VARCHAR(40),
data_admissao DATE
);
CREATE TABLE disciplina(
codigo INTEGER NOT NULL PRIMARY KEY,
nome VARCHAR(15),
carga_horaria INTEGER,
matric INTEGER
);
CREATE TABLE cursa(
codigo INTEGER,
matricula INTEGER NOT NULL,
nota INTEGER,
PRIMARY KEY (codigo,matricula)
);
INSTÂNCIA DO BANCO DE DADOS possível SEGUNDO A SOLUÇÃO 1:
ALUNO
Matricula
Nome
sexo
1
Ze
M
2
Ivo
M
3
Eva
F
2
Val
F
PROFESSOR
matr
nome
data_admissao
1
Ema
2000-10-29
2
Vilma
2001-08-25
DISCIPLINA
Nota de Aula
Prof. Sidney Vieira
2
Codigo
Nome
Carga_horaria
matric
1
matematica
80
1
2
fisicas
60
1
3
portugues
100
2
4
Ciencias
70
3
CURSA
Codigo
Matricula
nota
1
2
5
null
1
8
1
1
4
2
1
6
5
2
0
Problemas de inconsistência desta solução desta solução:
• Poderá ocorrer diversos alunos com a mesma matricula (integridade da identidade)
• Poderá ocorrer diversos professores com a mesma matri(integridade da identidade)
•
Solução: utilização de chaves primaria para garantir a unicidade de cada tupla, a integridade de identidade,
que descreve cada elemento criado na entidade, , conforme solução 02.
Solução 02
CREATE TABLE aluno(
matricula INTEGER NOT NULL PRIMARY KEY,
nome CHAR(40),
sexo VARCHAR(1)
);
CREATE TABLE professor(
matric INTEGER NOT NULL PRIMARY KEY,
nome CHAR(40),
data_admissao DATE
);
CREATE TABLE disciplina(
codigo INTEGER NOT NULL PRIMARY KEY,
nome CHAR(15),
carga_horaria INTEGER,
matric INTEGER
);
CREATE TABLE cursa(
codigo INTEGER NOT NULL,
matricula INTEGER NOT NULL,
nota INTEGER,
PRIMARY KEY (codigo,matricula)
);
Problemas de inconsistência desta solução desta solução:
Nota de Aula
Prof. Sidney Vieira
3
•
•
•
•
•
Poderá ocorrer disciplinas sendo cursadas por professores que não existem ( integridade referencial)
Poderá ocorrer alunos que cursam disciplinas que não existem
Poderá ocorrer alunos cursando duas vezes a mesma disciplina com notas distintas
Se uma linha de uma disciplina for eliminada( exclusão de uma tupla da tabela disciplina) teremos
que a tabela cursa poderá fazer referência a um aluno, cuja disciplina tenha sido excluída
Se uma linha de uma professor for eliminada( exclusão de uma tupla da tabela professor) teremos
que a tabela disciplina poderá fazer referência a um professor, na tabela professor, tenha sido
excluída
Solução: Inclusão de técnicas de integridade referencial, conforme solução 03
Solução 03
CREATE TABLE aluno(
matricula INTEGER NOT NULL PRIMARY KEY,
nome VARCHAR(40),
sexo VARCHAR(1)
);
CREATE TABLE professor(
matric INTEGER NOT NULL PRIMARY KEY,
nome VARCHAR(40),
data_admissao DATE
);
CREATE TABLE disciplina(
codigo INTEGER NOT NULL PRIMARY KEY,
nome VARCHAR(15),
carga_horaria INTEGER NOT NULL,
matric INTEGER,
FOREIGN KEY (matric) REFERENCES professor (matric)) ON DELETE SET NULL
);
CREATE TABLE cursa(
codigo INTEGER NOT NULL,
matricula INTEGER NOT NULL,
nota INTEGER,
FOREIGN KEY (matricula) REFERENCES aluno (matricula) ON DELETE CASCADE;
FOREIGN KEY (codigo) REFERENCES disciplina (codigo) ON DELETE NO ACTION;
PRIMARY KEY (codigo,matricula)
);
Nota de Aula
Prof. Sidney Vieira
4