Wu4ZMfb2

11. Create table jbmanager and give bonuses to all department managers.
2CREATE TABLE IF NOT EXISTS jbmanager(id INT NOT NULL PRIMARY KEY, bonus INT NULL);
3INSERT INTO jbmanager(id) SELECT manager FROM jbemployee WHERE manager IS NOT NULL ON DUPLICATE KEY IGNORE;
4UPDATE jbmanager SET bonus = 10000 WHERE id in (SELECT manager FROM jbdept);
5
6mysql> select * from jbmanager;
7+-----+-------+
8| id  | bonus |
9+-----+-------+
10|  10 | 10000 |
11|  13 | 10000 |
12|  26 | 10000 |
13|  32 | 10000 |
14|  33 | 10000 |
15|  55 | 10000 |
16| 129 | 10000 |
17| 199 |  NULL |
18+-----+-------+
198 rows in set (0.00 sec)
20
212. Create table jbcustomer
22CREATE TABLE IF NOT EXISTS jbcustomer(id INT NOT NULL PRIMARY KEY, name VARCHAR(30), adress VARCHAR(50), city INT, FOREIGN KEY(city) REFERENCES jbcity(id));
23
24mysql> describe jbcustomer;
25+--------+-------------+------+-----+---------+-------+
26| Field  | Type        | Null | Key | Default | Extra |
27+--------+-------------+------+-----+---------+-------+
28| id     | int(11)     | NO   | PRI | NULL    |       |
29| name   | varchar(30) | YES  |     | NULL    |       |
30| adress | varchar(50) | YES  |     | NULL    |       |
31| city   | int(11)     | YES  | MUL | NULL    |       |
32+--------+-------------+------+-----+---------+-------+
334 rows in set (0.00 sec)
34
353. Create table jbaccount.
36CREATE TABLE IF NOT EXISTS jbaccount(id INT NOT NULL PRIMARY KEY, balance INT, customer int, FOREIGN KEY(customer) REFERENCES jbcustomer(id));
37
38mysql> describe jbaccount;
39+----------+---------+------+-----+---------+-------+
40| Field    | Type    | Null | Key | Default | Extra |
41+----------+---------+------+-----+---------+-------+
42| id       | int(11) | NO   | PRI | NULL    |       |
43| balance  | int(11) | YES  |     | NULL    |       |
44| customer | int(11) | YES  | MUL | NULL    |       |
45+----------+---------+------+-----+---------+-------+
463 rows in set (0.00 sec)
47
48
494. Create table jbtransations.
50CREATE TABLE IF NOT EXISTS jbtransactions(id INT NOT NULL PRIMARY KEY, amount INT, date DATETIME, account INT, FOREIGN KEY(account) REFERENCES jbaccount(id));
51
52mysql> describe jbtransactions;
53+---------+----------+------+-----+---------+-------+
54| Field   | Type     | Null | Key | Default | Extra |
55+---------+----------+------+-----+---------+-------+
56| id      | int(11)  | NO   | PRI | NULL    |       |
57| amount  | int(11)  | YES  |     | NULL    |       |
58| date    | datetime | YES  |     | NULL    |       |
59| account | int(11)  | YES  | MUL | NULL    |       |
60+---------+----------+------+-----+---------+-------+
614 rows in set (0.00 sec)
62
63
645. Create table jbdeposit and jbwithdrawal. 
65CREATE TABLE IF NOT EXISTS jbdeposit(id INT NOT NULL PRIMARY KEY, transaction INT, FOREIGN KEY(transaction) REFERENCES jbtransactions(id));
66CREATE TABLE IF NOT EXISTS jbwithdrawal(id INT NOT NULL PRIMARY KEY, transaction INT, FOREIGN KEY(transaction) REFERENCES jbtransactions(id));
67
68mysql> describe jbdeposit;
69+-------------+---------+------+-----+---------+-------+
70| Field       | Type    | Null | Key | Default | Extra |
71+-------------+---------+------+-----+---------+-------+
72| id          | int(11) | NO   | PRI | NULL    |       |
73| transaction | int(11) | YES  | MUL | NULL    |       |
74+-------------+---------+------+-----+---------+-------+
752 rows in set (0.00 sec)
76
77mysql> describe jbwithdrawal;
78+-------------+---------+------+-----+---------+-------+
79| Field       | Type    | Null | Key | Default | Extra |
80+-------------+---------+------+-----+---------+-------+
81| id          | int(11) | NO   | PRI | NULL    |       |
82| transaction | int(11) | YES  | MUL | NULL    |       |
83+-------------+---------+------+-----+---------+-------+
842 rows in set (0.00 sec)
85
86
876. Add transaction id as foreign key  to jbdebit and remove employee, account and sdate. No changes to jbsale required. 
88ALTER TABLE jbdebit DROP COLUMN sdate;
89ALTER TABLE jbdebit DROP COLUMN account;
90SHOW CREATE TABLE jbdebit; <- find foreign key
91ALTER TABLE jbdebit DROP FOREIGN KEY fk_debit_employee;
92ALTER TABLE jbdebit DROP COLUMN employee;
93ALTER TABLE jbdebit ADD COLUMN transaction INT, ADD FOREIGN KEY fk_debit_transaction(transaction) REFERENCES jbtransactions(id);
94
95mysql> describe jbdebit;
96+-------------+---------+------+-----+---------+-------+
97| Field       | Type    | Null | Key | Default | Extra |
98+-------------+---------+------+-----+---------+-------+
99| id          | int(11) | NO   | PRI | NULL    |       |
100| transaction | int(11) | YES  | MUL | NULL    |       |
101+-------------+---------+------+-----+---------+-------+
102
1032 rows in set (0.00 sec)
104
1057.  Done!