NdGTv5jw

1# noinspection SqlNoDataSourceInspectionForFile
2
3CREATE DATABASE  IF NOT EXISTS `licenta_db`;
4USE `licenta_db`;
5
6CREATE TABLE IF NOT EXISTS `user_groups` (
7  `id` int(11) NOT NULL AUTO_INCREMENT,
8  `name` varchar(45) NOT NULL,
9  PRIMARY KEY (`id`),
10  UNIQUE KEY `name_UNIQUE` (`name`)
11) ENGINE=InnoDB DEFAULT CHARSET=utf8;
12
13CREATE TABLE IF NOT EXISTS `user` (
14  `id` int(11) NOT NULL AUTO_INCREMENT,
15  `email` varchar(50) NOT NULL,
16  `password` varchar(50) NOT NULL,
17  `first name` varchar(50) NOT NULL,
18  `lastname` varchar(50) NOT NULL,
19  PRIMARY KEY (`id`),
20  UNIQUE KEY `username_UNIQUE` (`email`)
21) ENGINE=InnoDB DEFAULT CHARSET=utf8;
22
23CREATE TABLE IF NOT EXISTS `account` (
24  `id` int(11) NOT NULL AUTO_INCREMENT,
25  `IBAN` int(30) NOT NULL,
26  `currency` varchar(5) NOT NULL,
27  `accountOwner` int(11) NOT NULL,
28  `amount` float(11) NOT NULL,
29  PRIMARY KEY (`id`),
30  UNIQUE KEY `IBAN_UNIQUE` (`IBAN`),
31  KEY `user_FK_idx` (`accountOwner`),
32  CONSTRAINT `user_FK` FOREIGN KEY (`accountOwner`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON
33    UPDATE NO ACTION
34) ENGINE=InnoDB DEFAULT CHARSET=utf8;
35
36CREATE TABLE IF NOT EXISTS `transaction` (
37  `id` int(11) NOT NULL AUTO_INCREMENT,
38  `fromAccount` int(11) NOT NULL,
39  `toAccount` int(11) NOT NULL,
40  `amount` float(11) NOT NULL,
41  `currency` varchar(5) NOT NULL,
42  `date` date,
43  PRIMARY KEY (`id`),
44  KEY `userFrom_FK_idx` (`fromAccount`),
45  KEY `userTo_FK_idx` (`toAccount`),
46  CONSTRAINT `userFrom_FK` FOREIGN KEY (`fromAccount`) REFERENCES `user` (`id`) ON DELETE NO
47    ACTION ON UPDATE NO ACTION,
48  CONSTRAINT `userTo_FK` FOREIGN KEY (`toAccount`) REFERENCES `user` (`id`) ON DELETE NO
49    ACTION ON UPDATE NO ACTION
50) ENGINE=InnoDB DEFAULT CHARSET=utf8;
51
52CREATE TABLE IF NOT EXISTS `roles` (
53  `id` int(11) NOT NULL AUTO_INCREMENT,
54  `role` varchar(45) NOT NULL,
55  PRIMARY KEY (`id`),
56  UNIQUE KEY `type_UNIQUE` (`role`)
57) ENGINE=InnoDB DEFAULT CHARSET=utf8;
58
59CREATE TABLE IF NOT EXISTS `user_roles` (
60  `id` int(11) NOT NULL AUTO_INCREMENT,
61  `user_id` int(11) NOT NULL,
62  `role_id` int(11) NOT NULL,
63  PRIMARY KEY (`id`),
64  KEY `user_FK_idx` (`user_id`),
65  KEY `role_idx` (`role_id`),
66  CONSTRAINT `role_FK2` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE NO ACTION ON
67    UPDATE NO ACTION,
68  CONSTRAINT `user_FK2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON
69    UPDATE NO ACTION
70) ENGINE=InnoDB DEFAULT CHARSET=utf8;