queSsmUh

1CREATE DATABASE bonus_program; 
2
3
4DROP ROLE IF EXISTS bonus;
5CREATE USER 'bonus'@'localhost' IDENTIFIED BY 'bonus';
6
7USE bonus_program;
8
9CREATE TABLE IF NOT EXISTS profile (
10  uid       INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
11  firstName VARCHAR(32) NOT NULL,
12  lastName  VARCHAR(32) NOT NULL,
13  votes INT NOT NULL DEFAULT 0 
14) ENGINE=InnoDB;
15
16CREATE TABLE IF NOT EXISTS flight
17(
18  uid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
19  dep_airport VARCHAR(128)                NOT NULL CHECK ( dep_airport <> '' ),
20  arr_airport VARCHAR(256)                NOT NULL CHECK ( arr_airport <> '' ),
21  dep_date    TIMESTAMP NOT NULL DEFAULT current_timestamp
22) ENGINE=InnoDB;
23
24CREATE TABLE IF NOT EXISTS ticket
25(
26  uid INT NOT NULL       AUTO_INCREMENT PRIMARY KEY,
27  user_id     INT                         NOT NULL,
28  flight_id   INT                         NOT NULL,
29  dep_airport VARCHAR(128)                NOT NULL CHECK ( dep_airport <> '' ),
30  departure   TIMESTAMP NOT NULL DEFAULT current_timestamp,
31  price       INT                         NOT NULL DEFAULT 0,
32
33  FOREIGN KEY (user_id)   REFERENCES profile (uid),
34  FOREIGN KEY (flight_id) REFERENCES flight  (uid)
35) ENGINE=InnoDB;
36
37CREATE TABLE IF NOT EXISTS detail
38(
39  profile_id  INT                         NOT NULL,
40  ticket_id   INT                         NOT NULL,
41  cur_value   BIGINT                      NOT NULL DEFAULT 0,
42  bonus_date  TIMESTAMP NOT NULL DEFAULT current_timestamp,
43  FOREIGN KEY (profile_id) REFERENCES profile (uid), 
44  FOREIGN KEY (ticket_id)  REFERENCES ticket  (uid)
45) ENGINE=InnoDB;
46
47
48GRANT ALL   PRIVILEGES ON bonus_program TO 'root'@'localhost';
49GRANT ALL   PRIVILEGES ON bonus_program TO 'bonus'@'localhost';
50GRANT USAGE            ON                       SCHEMA public TO bonus_program;
51GRANT ALL   PRIVILEGES ON ALL      TABLES    IN SCHEMA public TO bonus_program;
52GRANT ALL   PRIVILEGES ON ALL      SEQUENCES IN SCHEMA public TO bo nus_program;