Almacén Piezas

create table almacen
(
numalm serial not null,
desaml varchar(150),
diraml varchar (100) default 'Averiguar', --Valor por defecto
constraint pkalmacen primary key (numalm)
);

ALTER  table almacen
owner to userdb;

create table estanteria (
codest varchar(3),
constraint pkestanteria primary key (codest)
);

alter table estanteria
owner to userdb;

alter table estanteria
add column numalm int4;

alter table estanteria
add constraint fkalmest foreign key (numalm)
references almacen(numalm)
on delete restrict on update restrict;

create table pieza(
tippie varchar(2),
molpie int2,
despie varchar(100),
prepie decimal(10,2),
constraint ckprepie check (prepie > 0),
constraint pkpieza primary key (tippie,molpie)
);

alter table pieza
owner to userdb;

CREATE  table almacena
(
codest varchar(3),
tippie varchar(2),
molpie int2,
constraint pkalmacena primary key (codest,tippie,molpie)
);

alter table almacena
owner to userdb;


alter table almacena
add constraint fkpiealm foreign key (tippie, molpie)
references pieza(tippie, molpie)
on delete restrict on update restrict;

alter table almacena
add constraint fkestalm foreign key (codest)
references estanteria(codest)
on delete restrict on update restrict;

CREATE TABLE  componer
(
cantidad int4,
tippie1 varchar(2),
molpie1 int2,
tippie2 varchar(2),
molpie2 int2,
constraint pkcomponer primary key (tippie1,molpie1,tippie2,molpie2)
);

alter table componer
owner to userdb;

alter table componer
add constraint fkpiecom foreign key (tippie1, molpie1)
references pieza(tippie, molpie)
on delete restrict on update restrict;

alter table componer
add constraint fkpiecom2 foreign key (tippie2, molpie2)
references pieza(tippie, molpie)
on delete restrict on update restrict;