HAuZ8CFq

1DROP DATABASE IF EXISTS tax_service;
2CREATE DATABASE tax_service;
3USE tax_service;
4
5CREATE TABLE Account (
6  account_id       INT         NOT NULL AUTO_INCREMENT,
7  account_type     VARCHAR(20) NOT NULL,
8  account_name     VARCHAR(10) NOT NULL,
9  account_surname  VARCHAR(20) NOT NULL,
10  account_login    VARCHAR(15) NOT NULL UNIQUE,
11  account_password VARCHAR(20) NOT NULL,
12  account_mail     VARCHAR(36) NOT NULL,
13  PRIMARY KEY (account_id)
14);
15
16CREATE TABLE Client (
17  client_id  INT NOT NULL AUTO_INCREMENT,
18  account_id INT NOT NULL,
19  FOREIGN KEY (account_id) REFERENCES Account (account_id),
20  PRIMARY KEY (client_id)
21);
22
23CREATE TABLE Inspector (
24  insp_id    INT         NOT NULL AUTO_INCREMENT,
25  insp_info  VARCHAR(20) NOT NULL,
26  account_id INT         NOT NULL,
27  FOREIGN KEY (account_id) REFERENCES Account (account_id),
28  PRIMARY KEY (insp_id)
29);
30
31CREATE TABLE Report (
32  report_id          INT         NOT NULL AUTO_INCREMENT,
33  report_percentage  INT         NOT NULL,
34  report_description VARCHAR(40) NOT NULL,
35  report_status      VARCHAR(20) NOT NULL,
36  report_comments    VARCHAR(20) NOT NULL,
37  client_id          INT         NOT NULL,
38  insp_id            INT         NOT NULL,
39  FOREIGN KEY (client_id) REFERENCES Client (client_id),
40  FOREIGN KEY (insp_id) REFERENCES Inspector (insp_id),
41  PRIMARY KEY (report_id)
42);
43
44ALTER TABLE Report
45  ADD UNIQUE unique_index (client_id, report_description);
46
47DELIMITER //
48CREATE PROCEDURE client_registration
49  (
50    IN name     VARCHAR(10),
51    IN surname  VARCHAR(20),
52    IN login    VARCHAR(15),
53    IN password VARCHAR(20),
54    IN mail     VARCHAR(36)
55  )
56  BEGIN
57    DECLARE EXIT HANDLER FOR SQLEXCEPTION
58    BEGIN
59      ROLLBACK;
60    END;
61
62    START TRANSACTION;
63    INSERT INTO Account (
64      account_type,
65      account_name,
66      account_surname,
67      account_login,
68      account_password,
69      account_mail
70    )
71    VALUES ('CLIENT', name, surname, login, password, mail);
72    INSERT INTO Client (
73      account_id
74    )
75    VALUES ((SELECT account_id
76             FROM Account
77             WHERE account_login = login));
78    COMMIT;
79  END //
80
81CREATE PROCEDURE report_creation
82  (
83    IN percentage   INT,
84    IN description  VARCHAR(40),
85    IN id_account    INT,
86    IN id_inspector INT
87  )
88  BEGIN
89    DECLARE EXIT HANDLER FOR SQLEXCEPTION
90    BEGIN
91      ROLLBACK;
92    END;
93
94    START TRANSACTION;
95    INSERT INTO Report (
96      report_percentage,
97      report_description,
98      report_status,
99      report_comments,
100      client_id,
101      insp_id
102    )
103    VALUES (percentage, description, 'OPEN', 'CREATED', (SELECT client_id
104                                                         FROM Client
105                                                         WHERE account_id = id_account), id_inspector);
106    COMMIT;
107  END //
108
109DELIMITER ;