HSW5GEPy

1drop table if exists User;
2create table User (
3        id integer primary key autoincrement,
4        login varchar(20) not null unique,
5        password varchar not null,
6        phone_number varchar(9) not null unique,
7        email varchar(20) not null,
8        name varchar(20),
9        surname varchar(20),
10        birth_date varchar(10),
11        sex varchar(1) check sex in ('M', 'W') 
12);
13
14drop table if exists Call;
15create table Call ( 
16        id integer primary key autoincrement,
17        user1_id integer not null,
18        user2_id integer not null,
19        tarrif_id integer not null,
20        call_date text not null,
21        duration integer,
22        quality integer check(quality > 0 and quality < 11),
23        FOREIGN KEY(user1_id) REFERENCES User(id),
24        FOREIGN KEY(user2_id) REFERENCES User(id),
25        FOREIGN KEY(tarrif_id) REFERENCES Tarrif(id)
26);
27
28drop table if exists Tarrif;
29create table Tarrif (
30        id integer primary key autoincrement,
31        operator_id integer not null,
32        name varchar(20) not null unique,
33        cost_per_minute float not null
34);
35
36drop table if exists Operator;
37create table Operator (
38        id integer primary key autoincrement,
39        name varchar(20) not null unique
40);