VL65QDWJ

1drop database if exists tyovaentalo;
2
3create database tyovaentalo;
4
5use tyovaentalo;
6
7create table asiakas(
8    asnro int primary key auto_increment,
9    nimi varchar(255) not null,
10    puh varchar(10)
11);
12
13create table varaus(
14    varausnro int primary key auto_increment,
15    asnro int not null,
16    foreign key (asnro) references asiakas(asnro)
17    on delete restrict
18);
19
20create table tila(
21    tilanro int primary key auto_increment,
22    nimi varchar(255) not null,
23    hlomaara int not null,
24    hinta int not null
25);
26
27create table ateriapalvelu(
28    aterianro int primary key auto_increment,
29    nimi varchar(255) not null,
30    hinta int not null
31);
32
33create table ohjelmapalvelu(
34    ohjelmanro int primary key auto_increment,
35    nimi varchar(255) not null,
36    hinta int not null
37);
38
39
40create table varausrivi(
41    rivinro int primary key auto_increment,
42    varausnro int not null,
43    foreign key (varausnro) references varaus(varausnro)
44    on delete restrict,
45    tilanro int not null,
46    foreign key (tilanro) references tila(tilanro)
47    on delete restrict,
48    aterianro int not null,
49    foreign key (aterianro) references ateriapalvelu(aterianro)
50    on delete restrict,
51    ohjelmanro int not null,
52    foreign key (ohjelmanro) references ohjelmapalvelu(ohjelmanro)
53    on delete restrict,
54    a_klo time not null,
55    l_klo time not null,
56    pvm date not null,
57    kpl int not null
58);