Xgz1z0rA

1create database if not exists hotel;
2
3use hotel;
4
5create table if not exists room(
6id_room int unique primary key,
7nr_max_pers int,
8pret int);
9
10create table if not exists customer(
11id_customer int unique,
12name_customer varchar(30),
13adress_customer varchar(30),
14credit_card int unique primary key);
15
16create table if not exists reservation(
17start_date date,
18end_date date,
19nr_pers int,
20id_room int,
21credit_card int unique,
22id_service int,
23foreign key (id_room) references room(id_room),
24foreign key (credit_card) references customer(credit_card),
25foreign key (id_service) references service(id_service));
26
27insert into room(id_room,nr_max_pers,pret)
28values
29(1,5,300);
30
31insert into customer(id_customer,name_customer,adress_customer,credit_card)
32values
33(1,'Dragos','aaaa',1001),
34(2,'Ale','bbbb',1002);
35
36insert into reservation(start_date,end_date,nr_pers,id_room,credit_card)
37values
38('2017-06-02','2017-06-05',4,1,1001),
39('2017-06-04','2017-06-09',3,1,1002); 
40
41
42create table if not exists service(
43id_service int unique primary key,
44nr_pers int,
45foreign key (nr_pers) references reservation(nr_pers));
46
47create table if not exists demipensiune(
48id_service int,
49price int,
50nr_pers int,
51foreign key (nr_pers) references reservation(nr_pers),
52foreign key (id_service) references service(id_service));
53
54create table if not exists complet(
55id_service int,
56price int,
57nr_pers int,
58foreign key (nr_pers) references reservation(nr_pers),
59foreign key (id_service) references service(id_service));
60
61insert into service(id_service)
62values 
63(001),
64(002);
65
66insert into demipensiune(id_service,price)
67values
68(001,50);
69
70insert into complet(id_service,price)
71values
72(002,80);