m6VwLN7W

1USE demonstration;
2DROP TABLE IF EXISTS `demonstration`.`transactions`;
3DROP TABLE IF EXISTS `demonstration`.`credit_cards`;
4DROP TABLE IF EXISTS `demonstration`.`accounts`;
5DROP TABLE IF EXISTS `demonstration`.`customers`;
6DROP TABLE IF EXISTS `demonstration`.`geographic_areas`;
7DROP TABLE IF EXISTS `demonstration`.`branches`;
8
9# CREATION OF DB
10CREATE TABLE geographic_areas (
11	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
12    area_name VARCHAR (50) NOT NULL,
13    population INT NOT NULL,
14    avg_income DOUBLE NOT NULL,
15	updated_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
16    created_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
17);
18
19CREATE TABLE branches (
20	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
21	branch_code VARCHAR (50) NOT NULL,
22    branch_name VARCHAR (50) NOT NULL,
23    service_type INT NOT NULL,
24	updated_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
25    created_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
26);
27
28CREATE TABLE customers (
29	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
30	customer_code VARCHAR (50) NOT NULL,
31    area_id INT UNSIGNED NOT NULL,
32	firstname VARCHAR(50) NOT NULL,
33	lastname VARCHAR(30) NOT NULL,
34    vat_number VARCHAR (30) NOT NULL UNIQUE,
35    phone_number VARCHAR (15) NOT NULL,
36	updated_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
37    created_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
38    FOREIGN KEY (area_id) REFERENCES geographic_areas(id)
39);
40
41CREATE TABLE accounts (
42	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
43    customer_id INT UNSIGNED NOT NULL,
44	iban VARCHAR (30) NOT NULL UNIQUE,
45    balance DOUBLE NOT NULL,
46    interest_rate DOUBLE,
47    withdrawal_amount DOUBLE,
48	updated_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
49    created_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
50    FOREIGN KEY (customer_id) REFERENCES customers(id),
51    CONSTRAINT account_type CHECK ( (interest_rate IS NULL AND withdrawal_amount IS NOT NULL) OR (withdrawal_amount IS NULL AND interest_rate IS NOT NULL))
52
53);
54
55CREATE TABLE credit_cards (
56	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
57    customer_id INT UNSIGNED NOT NULL,
58    account_id INT UNSIGNED NOT NULL,
59    card_number VARCHAR (50) NOT NULL,
60    issue_date DATE NOT NULL,
61    expiration_date DATE NOT NULL,
62    credit_limit DOUBLE NOT NULL,
63    borrowing_rate DOUBLE NOT NULL,
64    balance DOUBLE NOT NULL,
65    card_level VARCHAR (30) NOT NULL,
66	updated_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
67    created_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
68    FOREIGN KEY (customer_id) REFERENCES customers(id),
69    FOREIGN KEY (account_id) REFERENCES accounts(id)
70);
71
72CREATE TABLE transactions (
73	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
74    card_id INT UNSIGNED NOT NULL,
75    branch_id INT UNSIGNED,
76    charged_amount DOUBLE NOT NULL,
77	updated_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
78    created_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
79    FOREIGN KEY (card_id) REFERENCES credit_cards(id),
80    FOREIGN KEY (branch_id) REFERENCES branches(id)
81);
82
83# POPULATION OF DB
84INSERT INTO `geographic_areas` (`area_name`, `population`, `avg_income`) VALUES ('natus', 35477, '38655');
85INSERT INTO `geographic_areas` (`area_name`, `population`, `avg_income`) VALUES ('rerum', 96787, '87543');
86INSERT INTO `geographic_areas` (`area_name`, `population`, `avg_income`) VALUES ('iure', 54379, '46436');
87INSERT INTO `geographic_areas` (`area_name`, `population`, `avg_income`) VALUES ('tuce', 56490, '63596');
88INSERT INTO `geographic_areas` (`area_name`, `population`, `avg_income`) VALUES ('huri', 47921, '32557');
89
90INSERT INTO `customers` (`area_id`, `firstname`, `lastname`, `vat_number`, `phone_number`, `customer_code`) VALUES (1, 'Katlyn', 'Borer', '911141250', '519-221-0332', '3457284876');
91INSERT INTO `customers` (`area_id`, `firstname`, `lastname`, `vat_number`, `phone_number`, `customer_code`) VALUES (2, 'Bob', 'Borer', '987441523', '519-074-0762', '4345684876');
92INSERT INTO `customers` (`area_id`, `firstname`, `lastname`, `vat_number`, `phone_number`, `customer_code`) VALUES (3, 'Lamak', 'Borer', '914321964', '519-537-0542', '2137284876');
93INSERT INTO `customers` (`area_id`, `firstname`, `lastname`, `vat_number`, `phone_number`, `customer_code`) VALUES (4, 'Phillip', 'Borer', '845148750', '519-846-0907', '9957284876');
94INSERT INTO `customers` (`area_id`, `firstname`, `lastname`, `vat_number`, `phone_number`, `customer_code`) VALUES (1, 'Cathrine', 'Borer', '365107050', '519-452-0738', '9007284876');
95
96INSERT INTO `accounts` (`customer_id`, `iban`, `balance`, `withdrawal_amount`, `interest_rate`) VALUES (1, '800945673678545788', 4563.85, 1000.0, NULL);
97INSERT INTO `accounts` (`customer_id`, `iban`, `balance`, `withdrawal_amount`, `interest_rate`) VALUES (2, '946748593678545788', 20663.85, 1000.0, NULL);
98INSERT INTO `accounts` (`customer_id`, `iban`, `balance`, `withdrawal_amount`, `interest_rate`) VALUES (3, '604945673678545788', 10863.85, 1000.0, NULL);
99INSERT INTO `accounts` (`customer_id`, `iban`, `balance`, `withdrawal_amount`, `interest_rate`) VALUES (4, '253945673678545788', 1463.85, 1000.0, NULL);
100INSERT INTO `accounts` (`customer_id`, `iban`, `balance`, `withdrawal_amount`, `interest_rate`) VALUES (5, '132945673678545788', 17463.85, 1000.0, NULL);
101INSERT INTO `accounts` (`customer_id`, `iban`, `balance`, `withdrawal_amount`, `interest_rate`) VALUES (5, '032945673678545788', 17463.85, 1000.0, NULL);
102
103INSERT INTO `credit_cards` (`customer_id`, `account_id`, `issue_date`, `expiration_date`, `credit_limit`, `borrowing_rate`, `balance`, `card_level`, `card_number`) VALUES (2, 1, '2002-06-16', '2020-04-24', '6000', 0.12, 3248, 2, '5487214963664243');
104INSERT INTO `credit_cards` (`customer_id`, `account_id`, `issue_date`, `expiration_date`, `credit_limit`, `borrowing_rate`, `balance`, `card_level`, `card_number`) VALUES (3, 1, '2004-04-11', '2020-03-30', '12000', 0.11, 9228, 3, '2345114963664243');
105INSERT INTO `credit_cards` (`customer_id`, `account_id`, `issue_date`, `expiration_date`, `credit_limit`, `borrowing_rate`, `balance`, `card_level`, `card_number`) VALUES (1, 2, '2006-02-11', '2016-04-01', '3000', 0.1, 6323, 1, '2547614963664243');
106INSERT INTO `credit_cards` (`customer_id`, `account_id`, `issue_date`, `expiration_date`, `credit_limit`, `borrowing_rate`, `balance`, `card_level`, `card_number`) VALUES (5, 4, '2008-01-09', '2018-07-01', '9000', 0.9, 2134, 2, '9678314963664243');
107INSERT INTO `credit_cards` (`customer_id`, `account_id`, `issue_date`, `expiration_date`, `credit_limit`, `borrowing_rate`, `balance`, `card_level`, `card_number`) VALUES (4, 3, '2012-11-01', '2022-03-01', '15000', 0.5, 4545, 3, '3334214963664243');
108INSERT INTO `credit_cards` (`customer_id`, `account_id`, `issue_date`, `expiration_date`, `credit_limit`, `borrowing_rate`, `balance`, `card_level`, `card_number`) VALUES (5, 4, '2008-01-09', '2018-07-01', '9000', 0.9, 2134, 2, '2228314963664243');
109INSERT INTO `credit_cards` (`customer_id`, `account_id`, `issue_date`, `expiration_date`, `credit_limit`, `borrowing_rate`, `balance`, `card_level`, `card_number`) VALUES (4, 3, '2012-11-01', '2022-03-01', '15000', 0.5, 4545, 3, '1114214963664243');
110INSERT INTO `credit_cards` (`customer_id`, `account_id`, `issue_date`, `expiration_date`, `credit_limit`, `borrowing_rate`, `balance`, `card_level`, `card_number`) VALUES (5, 4, '2008-01-09', '2018-07-01', '9000', 0.9, 2134, 2, '9448314963664243');
111INSERT INTO `credit_cards` (`customer_id`, `account_id`, `issue_date`, `expiration_date`, `credit_limit`, `borrowing_rate`, `balance`, `card_level`, `card_number`) VALUES (4, 3, '2012-11-01', '2022-03-01', '15000', 0.5, 4545, 1, '2364214963664243');
112INSERT INTO `credit_cards` (`customer_id`, `account_id`, `issue_date`, `expiration_date`, `credit_limit`, `borrowing_rate`, `balance`, `card_level`, `card_number`) VALUES (5, 4, '2008-01-09', '2018-07-01', '9000', 0.9, 2134, 1, '0978314963664243');
113INSERT INTO `credit_cards` (`customer_id`, `account_id`, `issue_date`, `expiration_date`, `credit_limit`, `borrowing_rate`, `balance`, `card_level`, `card_number`) VALUES (4, 3, '2012-11-01', '2022-03-01', '15000', 0.5, 4545, 1, '3435614963664243');
114
115INSERT INTO `branches` (`branch_name`, `service_type`, `branch_code`) VALUES ('earum', 7, '245093597');
116INSERT INTO `branches` (`branch_name`, `service_type`, `branch_code`) VALUES ('leirm', 18, '097097587');
117INSERT INTO `branches` (`branch_name`, `service_type`, `branch_code`) VALUES ('gurtsa', 18, '364093597');
118INSERT INTO `branches` (`branch_name`, `service_type`, `branch_code`) VALUES ('iopto', 18, '175093597');
119INSERT INTO `branches` (`branch_name`, `service_type`, `branch_code`) VALUES ('quore', 17, '998093597');
120
121
122INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2017-01-01 18:06:19');
123INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2017-02-01 18:06:19');
124INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2017-03-01 18:06:19');
125INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2017-04-01 18:06:19');
126INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2017-05-01 18:06:19');
127INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2017-06-01 18:06:19');
128INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2017-07-01 18:06:19');
129INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2017-08-01 18:06:19');
130INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2017-09-01 18:06:19');
131INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2017-10-01 18:06:19');
132INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2017-11-01 18:06:19');
133INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2017-12-01 18:06:19');
134
135INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2018-05-13 18:06:19');
136INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (2, 2, 104, '2018-05-16 18:06:19');
137INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (3, 3, 203, '2018-06-03 18:06:19');
138INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (4, 4, 24, '2018-06-23 18:06:19');
139INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (5, 5, 13, '2018-05-30 18:06:19');
140INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 5, 123, '2018-06-23 18:06:19');
141INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (2, 4, 133, '2018-06-13 18:06:19');
142INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (3, 3, 1378, '2018-06-10 18:06:19');
143INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (4, 2, 2413, '2018-10-06 18:06:19');
144INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (5, 2, 313, '2018-06-30 18:06:19');
145INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 913, '2018-12-09 18:06:19');
146INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 5, 51, '2018-06-30 18:06:19');
147INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 5, 51, '2018-06-30 18:06:19');
148INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 5, 51, '2018-06-30 18:06:19');
149INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 5, 51, '2018-06-30 18:06:19');
150INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 5, 51, '2018-06-30 18:06:19');
151INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 5, 51, '2018-06-30 18:06:19');