tabelas aula31/08

CREATE TABLE funcionario(

cod NUMBER(5),
nome VARCHAR2(100),
mae VARCHAR2(100),
pai NUMBER(5),
dt_nasc DATE,
rg VARCHAR2(20),
cpf NUMBER(11),
sexo CHAR(1),
rua VARCHAR2(100),
cidade VARCHAR2(100)
CONSTRAINT pkfunc PRIMARY KEY(cod),
CONSTRAINT ukfuncid UNIQUE KEY(nome,mae,dt_nasc),
CONSTRAINT ukfunccpf UNIQUE KEY(cpf),
CONSTRAINT ukfuncrg UNIQUE KEY(rg),
CONSTRAINT fkfuncpai foreign KEY(pai) references funcionario(cod)
);


CREATE TABLE empresa(

cod NUMBER(5)
nome VARCHAR2(100),
rua VARCHAR2(200),
cidade VARCHAR2(100),
CONSTRAINT pkemp PRIMARY KEY(cod)
);

CREATE TABLE trabalha(

codf NUMBER(5),
code NUMBER(5),
salario NUMBER(10),

CONSTRAINT pktrab PRIMARY KEY(codf,code),
CONSTRAINT fktrabcodf foreign KEY(codf) references funcionario(cod),
CONSTRAINT fktrabcode foreign KEY(code) references empresa(cod)
);

CREATE TABLE gerente(

codf NUMBER(5),
codg NUMBER(5),
CONSTRAINT pkger PRIMARY KEY(codf,codg),
CONSTRAINT fkgercodf foreign KEY(codf) references funcionario(cod),
CONSTRAINT fkgercodg foreign KEY(codg) references funcionario(cod)
);