Keittiökalustekanta, SQL harjoitteluun

/* 
Taulu ptype esittelee yrityksen tuotevalikoimaan kuuluvat tuotetyypit 
*/ 

create table ptype ( 
ptypeid varchar(8) not null primary key,  // tuotetyypin tunnus
productname varchar(40)                   // tuotetyypin nimi
);

/* 
Taulussa Model esitellään mallisarjat. 
Samaa tuotetta voidaan valmistaa useassa eri sarjassa. 
*/ 

create table model ( 
modelid varchar(12) not null primary key,  // mallisarjan tunnus
modelname varchar(30) );                   // mallisarjan nimi

/* 
Taulussa unit määritellään tarjolla olevat perustuotteet.
Näistä voidaan koota isompia kokonaisuuksia. 
Samaa tuotetta voi olla saatavissa useina eri malli- ja
värivaihtoehtoina. Sarake producttypeid yhdistää tuotteen ja sen
tyyppiin. Sarake unitid yksilöi tuotteen. Tällä hetkellä
tuotetunnuksesta selviää tuotteen tyyppi ja erilaista mittatietoa, 
mutta näin ei välttämättä ole jatkossa. Tuotetiedoissa kerrotaan 
lähinnä tuotteiden mittoja. Mittoja voi puuttua. 
*/ 

create table unit ( 
  unitid varchar(30) not null primary key, // tuotteen tunnus
  producttypeid varchar(12),               // tuotteen tuotetyypin tunnus
  unitname varchar(40),                    // tuotteen nimi
  height decimal(6,2),                     // mittoja, korkeus cm 
  width decimal(6,2),                      // leveys cm
  depth decimal(6,2),                      // syvyys cm
  foreign key (producttypeid) references ptype 
);
 
/* 
Tuoteen valmistukseen käytetty materiaali riippuu tuotetyypista ja 
mallisarjasta. Taulussa modelmaterial voidaan tuotetyyppi, malli -yhdistelmälle
määritellä yksi materiaali. 
*/

create table modelmaterial (
  producttypeid varchar(12) not null,      // tuoteen tyyppitunnus
  modelid varchar(12) not null,            // mallisarjan tunnus
  material varchar(24) not null,           // valmistusmateriaali
  primary key (producttypeid, modelid),
  foreign key (modelid) references model,
  foreign key (producttypeid) references ptype
);

/* 
Tuotteiden värivaihtoehdot on määritelty malliriippuvina. Kaikkia mallin
tuotteita saa kaikissa mallikohtaisesti kiinnitetyissä väreissä. 
*/ 

create table coloring ( 
 modelid varchar(12) not null,            // mallisarjan tunnus 
 color varchar(24) not null,              // värin nimi englanniksi
 primary key (modelid,color), 
 foreign key (modelid) references model
);
 
/* 
Taulussa unitprice määritellään perustuotteiden hinnat yleisimmin kysytyille
tuotteille. Muitakin voi saada tilauksesta. Hinnat riippuvat tuotteesta ja 
mallisarjasta. 
/* 
 
create table unitprice ( 
  unitid varchar(12) not null,            // tuotteen tunnus
  modelid varchar(12) not null,           // mallisarjan tunnus
  price integer not null,                 // hinta euroina (huom kokonaisluku)
  primary key (unitid, modelid),
  foreign key (unitid) references unit, 
  foreign key (modelid) references model 
); 

/* 
Perustuotteita voidaan yhdistää yhdistelmakalusteeksi. Taulussa composition
määritellään joukko valmiita yhdistelmiä. Yhdistelmiin kuuluu lähinnä runkoja,
hyllyjä ja vetolaatikoita. Niihin ei kuulu jalkoja eikä kahvoja. 
Kullakin yhdistelmällä on tunnus ja nimi. 
*/ 

create table composition ( 
  compositionid varchar(12) not null primary key,  // yhdistelmän tunnus
  compositionname varchar(80)                      // nimi
);
 
/* 
Taulussa partof määritellään miten yhdistelmät koostuvat perusosista.
Samaa perusosaa voi olla yhdistelmässä useita kappaleita. 
Sarake partcount ilmoittaa kuinka monta. Yhtä yhdistelmää 
kohden taulussa on useita rivejä.
*/ 

create table partof ( 
  compositionid varchar(12) not null,   // yhdistelmän tunnus
  partid varchar(12) not null,          // yhdistelmään kuuluvan osan tunnus
  partcount integer,                    // osien lukumäärä yhdistelmässä
  primary key (compositionid,partid),
  foreign key (compositionid) references composition, 
  foreign key (partid) references unit 
); 

/* 
Asiakkaat voivat laatia keittiösuunnitelmia suunnittelutyökalulla.
Suunnittelutyökalu kirjaa suunnitelman tauluun PLAN ja suunnitelman sisällön 
tauluun PLANELEMENT. Suunnitelma voidaan haluttaessa muuntaa tilaukseksi. 
Taulun PLAN rivi sisältää sunnitelman tunnustietojen ja erilaisten aikatietojen 
lisäksi myös asiakkaan henkilötiedot. Näitä ei ole sijoitettu omaksi taulukseen 
koska on oletettu, ettei samalla asiakkaalla ole monia suunnitelmia. 
Näin voi kuitenkin olla, jolloin asiakastiedot joudutaan toistamaan.
Tilauksen erottaa pelkästä suunnitelmasta se, että tilauspäivämäärä on 
kirjattu sarakkeeseen dateordered. Toimitetun tilauksen kohdalla on 
myös toimituspäivä kirjattuna.
*/

create table plan ( 
  plannumber integer not null primary key,  // suunnitelman numero
  customername varchar(30) not null,        // asiakkaan nimi
  customerphone varchar(20),                // asiakkaan puhelinnumero
  datemade date not null,                   // milloin suunnitelma on tehty 
  dateordered date,                         // milloin suunnitelma on muutettu 
                                            // tilaukseksi
  howtodeliver varchar(12),                 // miten pitäisi toimitta:
                                            //   home:kotiin, store: liikkeeseen
  whentodeliver date,                       // milloin toimitus halutaan
  whendelivered date,                       // milloin toimitettiin
  customeraddress varchar(64) not null,     // katuosoite
  customercity varchar(64) not null         // kaupunki
); 

/*
Taulussa PLANELEMENT esitetään suunnitelman sisältö: mitä osia 
suunnitelmaan kuuluuu. Suunnitelman osaksi voidaan ottaa joko 
yhdistelmäkalusteita tai erillisiä perustuotteita.
Jos suunniteltu osa on yhdistelmäkaluste, annetaan sen tunnus sarakkeessa 
compositionid. Jos suunniteltu osa on erillinen perustuote, annetaan
sen tunnus sarakkeessa unitid. Yhdellä PLANELEMENT rivillä voidaan 
määritellä vain yhdenlainen osa. Täten toinen sarakkeista compositionid 
tai unitid on aina tyhjä. 

Yhdistelmäkalusteet muodostuvat rungosta ja ovista tai etupaneeleista.
Niiden kohdalla annetaan rungon mallisarja ja väri (sarakkeet bodymodel 
ja bodycolor) sekä erikseen ovien mallisarja ja väri (sarakkeet fronmodel ja
frontcolor). Erillisten perustuotteiden malli ja väri annetaan sarakkeissa 
bodymodel ja bodycolor.  

Sarakkeessa amount kerrotaan montako kertaa osa toistuu suunnitelmassa.  
*/

create table planelement ( 
  plannumber integer not null,      // suunnitelman tunnus
  itemnumber integer not null,      // osan numero suunnitelmman sisällä
  compositionid varchar(12),        // yhdistelmäkalusteen tunnus, jos
                                    // suunniteltu osa on yhdistelmäkaluste,
                                    // muuten tyhjä
  unitid varchar(12),               // perustuotteen tunnus, jos suunniteltu
                                    // osa on perustuote, muuten tyhjä
  bodymodel varchar(12),            // yhdistelmän rungon malli tai
                                    // perustuotteen malli 
  bodycolor varchar(24),            // yhdistelmän rungon väri tai 
                                    // perustuotteen väri
  frontmodel varchar(12),           // yhdistelmän ovien malli
  frontcolor varchar(24),           // yhdistelmän ovien väri
  amount integer,                   // kuinka monta kappaletta
  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 
) ;