Kitchen furniture example database

/* 
Table ptype defines the kind of elements that belong 
to the assortment. 
*/ 

create table ptype ( 
ptypeid varchar(8) not null primary key,  // identifier
productname varchar(40)                   // name
);

/* 
Model is a category of products that share a common design.
Table model identifies the categories. 
*/ 

create table model ( 
modelid varchar(12) not null primary key,  // identifier
modelname varchar(30) );                   // name

/* 
Table unit defines the elementary products that may be used as 
construction parts in kitchen furniture. A unit is typically 
manufactured in many models and in many coloring. Producttypeid
connects a unit to its type. Unitid uniquely identifies a unit. 
Height,width and depth specify the meassures of the unit. 
They may have null values.
*/ 

create table unit ( 
  unitid varchar(30) not null primary key, // identifier
  producttypeid varchar(12),               // reference to product type
  unitname varchar(40),                    // name
  height decimal(6,2),                     // dinesions 
  width decimal(6,2), 
  depth decimal(6,2), 
  foreign key (producttypeid) references ptype 
);
 
/* 
The materials used in products depend on the type of the product
and the model the product belongs to. Table modelmaterial defines 
the model specific main material for products. 
*/

create table modelmaterial (
  producttypeid varchar(12) not null,   // type of product
  modelid varchar(12) not null,         // reference to model
  material varchar(24) not null,        // main material
  primary key (producttypeid, modelid),
  foreign key (modelid) references model,
  foreign key (producttypeid) references ptype
);

/* 
Products that belong to a specific model are available in many colors. 
Table coloring defines the coloring scheme for the furniture models. 
*/ 

create table coloring ( 
 modelid varchar(12) not null,          // model 
 color varchar(24) not null,            // color
 primary key (modelid,color), 
 foreign key (modelid) references model
);
 
/* 
Table unitprice contains the price list. Prices depend on the product and 
the model the product belongs to. 
/* 
 
create table unitprice ( 
  unitid varchar(12) not null,          // product
  modelid varchar(12) not null,         // model
  price integer not null,            
  primary key (unitid, modelid),
  foreign key (unitid) references unit, 
  foreign key (modelid) references model 
); 

/* 
Pieces of furniture may be combined into compositions. 
Table composition specifies some standard compositions. 
These compositions do not include without legs and handles.
Compositions have identifier and name. 
*/ 

create table composition ( 
  compositionid varchar(12) not null primary key,  // identifier
  compositionname varchar(80)                      // name
);
 
/* 
Table partof defines the elements of the standard  compositions. 
Column partcount specifies number of elements included in a single composition.
*/ 

create table partof ( 
  compositionid varchar(12) not null,   // the whole
  partid varchar(12) not null,          // part of the whole
  partcount integer,                    // how many needed
  primary key (compositionid,partid),
  foreign key (compositionid) references composition, 
  foreign key (partid) references unit 
); 

/* 
Table PLAN defines the kitchen plans and orders made according to these plans. 
A plan is identified by a plannumber.
If column dateordered is not null the plan acts also as a purchase order.
If the order has been delivered column whendeliverde has a non null value.
*/

create table plan ( 
  plannumber integer not null primary key,  // identifier
  customername varchar(30) not null,        // customer
  customerphone varchar(20), 
  datemade date not null,                   // when made 
  dateordered date,                         // when converted to order
  howtodeliver varchar(12),                 // how should be delivered
  whentodeliver date,                       // when should be delivered
  whendelivered date,                       // whwn was delivered
  customeraddress varchar(64) not null,     // street address
  customercity varchar(64) not null         // city
); 

/*
Table PLANELEMENT defines what items are included in plans.
One may include both compositions and separate elementary units.
If the included element is a composition, its identifier is stored in 
column compositionid. If the element is a separate unit its identifier is 
stored in column unitid. On each row one of the columns compositionid or 
unitid is null. 
In case of compositions the model and color of the body cabinet are defined 
in bodymodel and bodycolor colums. In case of separate units these colums 
are used for defining the model and color of the unit. 
Columns frontmodel and froncolor have null values in case of separate units 
and real values in case of compositions.  
*/

create table planelement ( 
  plannumber integer not null,      // which plan
  itemnumber integer not null,      // item sequence number within plan
  compositionid varchar(12),        // composition includet, if any
  unitid varchar(12),               // unit included if any
                                    // one of the above two columns is null
  bodymodel varchar(12),            // composition bodymodel or unit model
  bodycolor varchar(24),            // composition bodycolor or unit color
  frontmodel varchar(12),           // composition door model
  frontcolor varchar(24),           // composition door color
  amount integer,                   // how many pieces
  primary key (plannumber, itemnumber), 
  foreign key (compositionid) references composition, 
  foreign key (unitid) references unit, 
  foreign key (bodymodel,bodycolor) references coloring, 
  foreign key (frontmodel,frontcolor) references coloring 
) ;