Example database (warehouse)

create_schema.sql

DROP TABLE IF EXISTS Articles CASCADE;
DROP TABLE IF EXISTS StorageLocations CASCADE;
DROP TABLE IF EXISTS StorageLocationSpecifications CASCADE;
DROP TABLE IF EXISTS Compatibilities CASCADE;
DROP TABLE IF EXISTS StorageUnits CASCADE;


-- object: public."Articles" | type: TABLE --
CREATE TABLE Articles (
	AId varchar(8) NOT NULL,
	AName varchar,
	Weight decimal,
	CONSTRAINT "Article_pk" PRIMARY KEY (AId)

);

-- object: public."StorageUnits" | type: TABLE --
DROP TABLE IF EXISTS public."StorageUnits" CASCADE;
CREATE TABLE StorageUnits (
	SUId varchar(8) NOT NULL,
	AId varchar(8),
	Quantity smallint,
	StoLId varchar(8),
	CONSTRAINT "StorageUnit_pk" PRIMARY KEY (SUId)

);

-- object: public."StorageLocations" | type: TABLE --
CREATE TABLE StorageLocations (
	StoLId varchar(8) NOT NULL,
	Position integer,
	StoSId varchar(8),
	CONSTRAINT "StorageLocation_pk" PRIMARY KEY (StoLId)

);

-- object: public."StorageLocationSpecifications" | type: TABLE --
CREATE TABLE StorageLocationSpecifications (
	StoSId varchar(8) NOT NULL,
	Length integer,
	Width integer,
	Height integer,
	MaxWeight integer,
	CONSTRAINT "StorageLocationSpecification_pk" PRIMARY KEY (StoSId)

);

-- object: public."Compatibilities" | type: TABLE --
CREATE TABLE Compatibilities (
	AId varchar(8) NOT NULL,
	StoLId varchar(8) NOT NULL,
	CONSTRAINT "Compatibility_pk" PRIMARY KEY (AId,StoLId)

);

-- object: "Articles_fk" | type: CONSTRAINT --
-- ALTER TABLE public."Compatibilities" DROP CONSTRAINT IF EXISTS "Articles_fk" CASCADE;
ALTER TABLE Compatibilities ADD CONSTRAINT "Articles_fk" FOREIGN KEY (AId)
REFERENCES Articles (AId) MATCH FULL
ON DELETE SET NULL ON UPDATE CASCADE
DEFERRABLE INITIALLY DEFERRED;
-- ddl-end --

-- object: "StorageLocations_fk" | type: CONSTRAINT --
-- ALTER TABLE public."Compatibilities" DROP CONSTRAINT IF EXISTS "StorageLocations_fk" CASCADE;
ALTER TABLE Compatibilities ADD CONSTRAINT "StorageLocations_fk" FOREIGN KEY (StoLId)
REFERENCES StorageLocations (StoLId) MATCH FULL
ON DELETE SET NULL ON UPDATE CASCADE
DEFERRABLE INITIALLY DEFERRED;
-- ddl-end --

-- object: "Articles_fk" | type: CONSTRAINT --
-- ALTER TABLE public."StorageUnits" DROP CONSTRAINT IF EXISTS "Articles_fk" CASCADE;
ALTER TABLE StorageUnits ADD CONSTRAINT "Articles_fk" FOREIGN KEY (AId)
REFERENCES Articles (AId) MATCH FULL
ON DELETE SET NULL ON UPDATE CASCADE
DEFERRABLE INITIALLY DEFERRED;
-- ddl-end --

-- object: "StorageLocations_fk" | type: CONSTRAINT --
-- ALTER TABLE public."StorageUnits" DROP CONSTRAINT IF EXISTS "StorageLocations_fk" CASCADE;
ALTER TABLE StorageUnits ADD CONSTRAINT "StorageLocations_fk" FOREIGN KEY (StoLId)
REFERENCES StorageLocations (StoLId) MATCH FULL
ON DELETE SET NULL ON UPDATE CASCADE
DEFERRABLE INITIALLY DEFERRED;
-- ddl-end --

-- object: "StorageLocationSpecifications_fk" | type: CONSTRAINT --
-- ALTER TABLE public."StorageLocations" DROP CONSTRAINT IF EXISTS "StorageLocationSpecifications_fk" CASCADE;
ALTER TABLE StorageLocations ADD CONSTRAINT "StorageLocationSpecifications_fk" FOREIGN KEY (StoSId)
REFERENCES StorageLocationSpecifications (StoSId) MATCH FULL
ON DELETE SET NULL ON UPDATE CASCADE
DEFERRABLE INITIALLY DEFERRED;
-- ddl-end --

ingest_data.sql

begin;
-- REM INSERTING into Articles
Insert into Articles (AID,ANAME,Weight) values ('A-001','Schokolade',125);
Insert into Articles (AID,ANAME,Weight) values ('A-002','Waschmittel',2500);
Insert into Articles (AID,ANAME,Weight) values ('A-003','Knuspermuesli',250);
Insert into Articles (AID,ANAME,Weight) values ('A-300','Bananenshake',550);

-- REM INSERTING into StorageUnits
Insert into StorageUnits (SUId,AID,Quantity,StoLId) values ('Le-001','A-001',101,'Lo-001');
Insert into StorageUnits (SUId,AID,Quantity,StoLId) values ('Le-002','A-002',24,'Lo-001');
Insert into StorageUnits (SUId,AID,Quantity,StoLId) values ('Le-003','A-002',24,'Lo-003');
Insert into StorageUnits (SUId,AID,Quantity,StoLId) values ('Le-004','A-003',100,'Lo-002');
Insert into StorageUnits (SUId,AID,Quantity,StoLId) values ('Le-005','A-300',40,'Lo-005');

-- REM INSERTING into StorageLocations
Insert into StorageLocations (StoLId,POSITION,StoSId) values ('Lo-001',1,'Loa-001');
Insert into StorageLocations (StoLId,POSITION,StoSId) values ('Lo-002',2,'Loa-001');
Insert into StorageLocations (StoLId,POSITION,StoSId) values ('Lo-003',10,'Loa-002');
Insert into StorageLocations (StoLId,POSITION,StoSId) values ('Lo-004',8,'Loa-003');
Insert into StorageLocations (StoLId,POSITION,StoSId) values ('Lo-005',11,'Loa-003');

-- REM INSERTING into StorageLocationSpecifications
Insert into StorageLocationSpecifications (StoSId,Length,Width,Height,MaxWeight) values ('Loa-001','1','1','1','100000');
Insert into StorageLocationSpecifications (StoSId,Length,Width,Height,MaxWeight) values ('Loa-002','2','1','2','200000');
Insert into StorageLocationSpecifications (StoSId,Length,Width,Height,MaxWeight) values ('Loa-003','2','2','1','100000');

-- REM INSERTING into Compatibilities
Insert into Compatibilities (AId,StoLId) values ('A-001','Lo-001');
Insert into Compatibilities (AId,StoLId) values ('A-002','Lo-003');
Insert into Compatibilities (AId,StoLId) values ('A-003','Lo-002');
commit;