(Resolvido) Tabelas com herança

[Jesc]

Bom dia a todos.

De acordo com as informações abaixo, criei algumas tabelas para esse enunciado,

gostaria de saber se as tabelas estão corretas e como colocar as chaves

estrangeiras e os "on update,delete cascade" para quando eu deletar uma linha em uma

tabela os dados referentes a essa linha em outra tabela sejam deletados.

Agradeço qualquer ajuda.

1- Times de futebol desejam vender cotas de passes de seus jogadores.Cada

cota equivale a um número percentual e pode ser vendida para pessoas

físicas e jurídicas.Logicamente, o somatório de cotas de um passe não

pode ultrapassar 100%.

2- Um jogador possui, além dos dados de pessoa física, um apelido, clubes

que está atuando e valor do passe(é importante saber a idade do jogador

antes de comprar seu passe).

3- Uma cota de passe de um jogador específico possui: um dono(pessoa física

ou jurídica), dono anterior, percentual, valor de negociação, data de

negociação e valor de mercado.

4- Uma pessoa física tem nome, cpf, telefone, data de nascimento e idade(

um jogador é uma pessoa física com mais particularidades);

5- Uma pessoa jurídica possui nome,nome fantasia,CNPJ, telefone,data de

fundação(um time de futebol poder ser visto como uma pessoa jurídica);

create database sistema_de_cotas;

use sistema_de_cotas;

create table pessoa(

cod_p int(3) not null,

nome varchar(50) not null,

fone varchar(15) not null,

primary key(cod_p));

create table p_juridica(

cod_p int(3) not null,

cod_pj int(5) not null,

nome_fant varchar(50) not null,

fundacao varchar(15) not null,

cnpj varchar(15) not null,

primary key(cnpj));

create table p_fisica(

cod_p int(3) not null,

cod_pf int(4) not null,

data_nasc varchar(15) not null,

cpf varchar(15) not null,

primary key(cpf));

create table jogador(

cod_p int(3) not null,

cod_pf int(4) not null,

cod_jog int(4) not null,

apelido varchar(25) not null,

clube_atual varchar(50) not null,

clubes_passds varchar(500) not null,

primary key(cod_jog));

create table cota(

cod_p int(3) not null,

cod_passe int(7) not null,

cod_cota int(2) not null,

vlr_negoc decimal(7,2) not null,

data_negoc varchar(15) not null,

primary key(cod_cota));

create table passe(

cod_jog int(4) not null,

cod_passe varchar(7) not null,

vlr_passe decimal(7,2) not null,

primary key(cod_passe));

create table jog_club(

cod_jog int(4) not null,

cod_club int(5) not null,

primary key(cod_jog,cod_club));

create table clube(

cod_p int(3) not null,

cod_pj int(5) not null,

cod_club int(5) not null,

cores varchar(100) not null,

primary key(cod_club));

insert into pessoa values(

001,'Clodoaldo','3254-5689'),

(002,'Batistuta','8800-9966'),

(003,'Fortaleza','3344-5456'),

(004,'Cleilson Sa','34852122');

insert into p_fisica values

(001,200,'1982-03-21','65814453322'),

(002,201,'1981-05-09','11004451598');

insert into jogador values (

001,200,400,'Baixinho',500,501),

(002,201,401,'Bategol',502,503);

insert into p_juridica values(

003,300,'leao','1905-02-05','12365478932452'),

(004,301,'O matador','1989-10-21','02520252375123');

insert into clube values (

003,300,500,'azul-vermelho-branco');

insert into cota values (

001,700,600,35.00,'2005-09-21');

insert into passe values (

400,700,10.000);

insert into jog_club values(

400,500);

[Denis Courcy]

Oi, Jesc!

Pelo que você explicou gerei o modelo em anexo. Avalie e informe onde e o que não entendeu.

clube_futebol.pdf

[Jesc]

Percebi no seu diagrama em anexo o seguinte:

- na tabela cota você não inseriu 'valor de negociação','data de negociação' e 'valor de mercado';

- você retirou os cods para cada tabela, como cod_pessoaJuridica,cod_pessoaFisica.

- Nas tabelas: pessoa_juridica e pessoa_fisica que tipo recebe 'É clube' e 'É jogador' na criação

das tabelas?

- Como colocar as clausulas nas tabelas para quando, por exemplo: deletando uma pessoa_juridica nesta

tabela e os campos referente a essa pessoa na tabela 'pessoa' serem tambem apagados?

- Todas as tabelas terão um único id que será hedado da tabela Pessoa?

[Denis Courcy]

Jesc,

Respondendo suas perguntas temos:

- Todas as tabelas terão um único id que será hedado da tabela Pessoa?

Resp.: Sim. O relacionamento entre estas tabelas é de 1:1 podendo ser 0 na tabela filha.

Daí a necessidade de implementar controles como os da próxima pergunta sua

- Nas tabelas: pessoa_juridica e pessoa_fisica que tipo recebe 'É clube' e 'É jogador' na criação

das tabelas?

É um tipo lógico ou ENUM com sim ou não que informará se aquele registro possui uma especialização. Já que posso ter Pessoa física que não é jogador e pessoa jurídica que não é clube.

Da mesma forma há um controlador em pessoa que me informa que tipo de pessoa é a minha especialização.

- na tabela cota você não inseriu 'valor de negociação','data de negociação' e 'valor de mercado';

Esqueci.

- você retirou os cods para cada tabela, como cod_pessoaJuridica,cod_pessoaFisica.

Veja o porquê na resposta da questão 1

- Como colocar as clausulas nas tabelas para quando, por exemplo: deletando uma pessoa_juridica nesta

tabela e os campos referente a essa pessoa na tabela 'pessoa' serem tambem apagados?

Veja se a resposta que dei neste Tópico te ajuda.

Substitua ON DELETE NO ACTION E ON UPDATE NO ACTION por ON DELETE CASCADE e ON UPDATE CASCADE

[Jesc]

ok Denis, gostaria que verificasse se as criações das tabelas e os 'ALTER TABLE FK..' estão corretos

E que você populace uma linha da 'jogador' pra como é a população.

os 'auto_increment' estão colocados certos; a tabela 'jogador_clube' precisaria de um 'auto_increment'.

Não entendi o porquê de usar 'FK_pessoa_jur' nos 'ALTER TABLE FK...'.

Obrigado por estar ajudando.

Equeci de colocar as tabelas:

create database futebol;

use futebol;

CREATE TABLE pessoa (

id_pessoa integer unsigned not null auto_increment,

nome varchar(40) not null,

fone varchar(15) not null,

tipo_pessoa char(2) not null,

primary key (id_pessoa)

)ENGINE=InnoDB;

CREATE TABLE pessoa_fisica (

id_pessoa_fis integer unsigned not null auto_increment,

data_nasc date not null,

cpf varchar(15) not null,

e_jogador char(2) not null,

primary key (id_pessoa_fis)

)ENGINE=InnoDB;

CREATE TABLE pessoa_juridica (

id_pessoa_jur integer unsigned not null auto_increment,

nome_fant varchar(40) not null,

fundacao date not null,

cnpj varchar(15) not null,

primary key (id_pessoa_jur)

)ENGINE=InnoDB;

CREATE TABLE cotas (

id_jogador integer unsigned not null,

id_pessoa integer unsigned not null,

percentual varchar(5) not null,

valor_negoc decimal(7,2) not null,

data_negoc date not null,

valor_mercado decimal(7,2) not null,

primary key (id_jogador)

)ENGINE=InnoDB;

CREATE TABLE clube (

id_clube integer unsigned not null auto_increment,

cores varchar(100) not null,

primary key (id_clube)

)ENGINE=InnoDB;

CREATE TABLE jogador_clube (

id_clube integer unsigned not null,

id_jogador integer unsigned not null,

primary key (id_clube,id_jogador)

)ENGINE=InnoDB;

CREATE TABLE jogador (

id_jogador integer unsigned not null auto_increment,

apelido varchar(20) not null,

valor_passe decimal(7,2) not null,

primary key (id_jogador)

)ENGINE=InnoDB;

ALTER TABLE pessoa_fisica

ADD CONSTRAINT FK_pessoa_fis FOREIGN KEY (id_pessoa_fis) REFERENCES pessoa (id_pessoa) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE pessoa_juridica

ADD CONSTRAINT FK_pessoa_jur FOREIGN KEY (id_pessoa_jur) REFERENCES pessoa (id_pessoa) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE cotas

ADD CONSTRAINT FK_jogador FOREIGN KEY (id_jogador) REFERENCES jogador (id_jogador) ON UPDATE CASCADE ON DELETE CASCADE,

ADD CONSTRAINT FK_pessoa FOREIGN KEY (id_pessoa) REFERENCES pessoa (id_pessoa) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE clube

ADD CONSTRAINT FK_pessoa_juridica FOREIGN KEY(id_clube) REFERENCES pessoa_juridica (id_pessoa_jur) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE jogador_clube

ADD CONSTRAINT FK_clube FOREIGN KEY(id_clube) REFERENCES clube (id_clube) ON UPDATE CASCADE ON DELETE CASCADE,

ADD CONSTRAINT FK_jogador FOREIGN KEY(id_jogador) REFERENCES jogador (id_jogador) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE jogador

ADD CONSTRAINT FK_pessoa_fisica FOREIGN KEY (id_jogador) REFERENCES pessoa_fisica (id_pessoa_fis) ON UPDATE CASCADE ON DELETE CASCADE;

[Denis Courcy]

ok Denis, gostaria que verificasse se as criações das tabelas e os 'ALTER TABLE FK..' estão corretos

E que você populace uma linha da 'jogador' pra como é a população.

Oi, Jesc!

os 'auto_increment' estão colocados certos; a tabela 'jogador_clube' precisaria de um 'auto_increment'.

Não há auto_increment nos atributos id_pessoa_fis da tabela pessoa_fisica, nem id_pessoa_jur na tabela pessoa_jurid, nem id_clube na tabela clube e nem id_jogador na tabela jogador.

Estes atributos são atributos ALIAS do atributo id_pessoa na tabela pessoa.

Um Atributo ALIAS é o atributo que tem o nome diferenciado para facilitar o entendimento, mas que o conteúdo e tipo são os mesmos do atributo a que ele faz referência.

Assim, modificando suas definições, teremos:

create database futebol;

use futebol;

CREATE TABLE pessoa (
   id_pessoa integer unsigned not null auto_increment,
   nome varchar(40) not null,
   fone varchar(15) not null,
   tipo_pessoa enum('J','F') not null,
   primary key (id_pessoa)
)ENGINE=InnoDB;

CREATE TABLE pessoa_fisica (
   id_pessoa_fis integer unsigned not null,
   data_nasc date not null,
   cpf varchar(15) not null,
   e_jogador enum ("S","N") not null,
)ENGINE=InnoDB;

ALTER TABLE pessoa_fisica
   ADD CONSTRAINT FK_pessoa_fis FOREIGN KEY (id_pessoa_fis) REFERENCES pessoa (id_pessoa) ON UPDATE CASCADE ON DELETE CASCADE;

CREATE TABLE pessoa_juridica (
   id_pessoa_jur integer unsigned not null,
   nome_fant varchar(40) not null,
   fundacao date not null,
   cnpj varchar(15) not null,
   e_clube enum ("S","N") not null,
)ENGINE=InnoDB;

ALTER TABLE pessoa_juridica
   ADD CONSTRAINT FK_pessoa_jur FOREIGN KEY  (id_pessoa_jur) REFERENCES pessoa (id_pessoa) ON UPDATE CASCADE ON DELETE CASCADE;

CREATE TABLE jogador (
   id_jogador integer unsigned not null,
   apelido varchar(20) not null,
   valor_passe decimal(7,2) not null
)ENGINE=InnoDB;

ALTER TABLE jogador
   ADD CONSTRAINT FK_jogador  FOREIGN KEY (id_jogador) REFERENCES pessoa_fisica (id_pessoa_fis) ON UPDATE CASCADE ON DELETE CASCADE;

CREATE TABLE clube (
   id_clube integer unsigned not null,
   cores varchar(100) not null,
)ENGINE=InnoDB;

ALTER TABLE clube
   ADD CONSTRAINT FK_clube FOREIGN KEY(id_clube) REFERENCES pessoa_juridica (id_pessoa_jur) ON UPDATE CASCADE ON DELETE CASCADE;

CREATE TABLE cotas (
   id_jogador integer unsigned not null,
   id_pessoa integer unsigned not null,
   percentual char(5) not null,
   valor_negoc decimal(7,2) not null,
   data_negoc date not null,
   valor_mercado decimal(7,2) not null,
)ENGINE=InnoDB;

ALTER TABLE cotas
   ADD CONSTRAINT FK_jogador FOREIGN KEY (id_jogador) REFERENCES jogador (id_jogador) ON UPDATE CASCADE ON DELETE CASCADE,
   ADD CONSTRAINT FK_pessoa FOREIGN KEY (id_pessoa) REFERENCES pessoa (id_pessoa) ON UPDATE CASCADE ON DELETE CASCADE;

CREATE TABLE jogador_clube (
id_clube integer unsigned not null,
id_jogador integer unsigned not null,
primary key (id_clube,id_jogador)
)ENGINE=InnoDB;

ALTER TABLE jogador_clube
   ADD CONSTRAINT FK_clube FOREIGN KEY(id_clube) REFERENCES clube (id_clube) ON UPDATE CASCADE ON DELETE CASCADE,
   ADD CONSTRAINT FK_jogador FOREIGN KEY(id_jogador) REFERENCES jogador (id_jogador) ON UPDATE CASCADE ON DELETE CASCADE;

Não entendi o porquê de usar 'FK_pessoa_jur' nos 'ALTER TABLE FK...'.

É para manter o relacionamento 1:1 dependente da tabela pai. Só pode incluir um clube, por exemplo, se antes você incluiu uma pessoa jurídica e esta pessoa jurídica, por sua vez, só pode ser incluida se você já incluiu um registro em pessoa. Assim, respondendo a sua solicitação em

E que você populace uma linha da 'jogador' pra como é a população.

para inclusão do clube 'Flamengo', por exemplo, teremos:

USE futebol;
START TRANSACTION;
INSERT INTO pessoa (id_pessoa,  nome,  fone,  tipo_pessoa enum)
VALUES (1, 'CLUBE DE REGATAS FLAMENGO', 9999-9999, 'J');

INSERT INTO pessoa_juridica (id_pessoa_jur,  nome_fant,  fundacao,  cnpj, e_clube)
VALUES (1, 'FLAMENGO', '1900-01-01', 99999999999-99, 'S');

INSERT INTO clube (id_clube,  cores)
VALUES (1, 'VERMELHO e PRETO');

COMMIT;

[Jesc]

Obrigado mais uma vez colega.

- você deixou quase todas as tabelas sem PRIMARY KEY, porque?

- O que significa, quando você populou um clube, as linhas: START TRANSACTION e

COMMIT

[Denis Courcy]

Obrigado mais uma vez colega.

- você deixou quase todas as tabelas sem PRIMARY KEY, porque?

Porque já tem índice ( o foreign key) e a restrição de integridade referencial provocada pela constraint obriga a a que o registro na tabela filha só possa ser incluido se houver incluido o registro na tabela pai antes.

Outra coisa, Você sabia que excesso de índices também podem tornar uma tabela mais lenta?

- O que significa, quando você populou um clube, as linhas: START TRANSACTION e

COMMIT

Fiz com que a tranzação fosse atômica. Ou seja, ou grava tudo ou não grava nada. Se houver falha na gravação um erro será disparado e o que foi gravado será desfeito.

[Jesc]

Ok! Denis. Entendi. Muito obrigado.

Parabéns!!! Você tem um excelente conhecimento em BD. Tudo ficou mais claro após suas

explicações. Obrigado também pela paciência. Espero que continue assim, ajudando e passando

conhecimento.

E pra encerrar: você tem algum material (dica/macetes) para aperfeiçoar a criação de tabelas a

partir do modelo ER(conceitual) e um nome de editor de tabelas.

Tópico Resolvido.

Até mais.

[Denis Courcy]

E pra encerrar: você tem algum material (dica/macetes) para aperfeiçoar a criação de tabelas a

partir do modelo ER(conceitual) e um nome de editor de tabelas.

Verifique o Tópico Para Tutoriais/dicas, e também o Programas Front End Mysql

No primeiro link procure o post do Micheus

No segundo Link O último (SQLYog) é o que eu uso.