DATA ANALYTICS REFERENCE DOCUMENT |
|
---|---|
Document Title: | Applied Databases week 5 worksheet |
Document No.: | 1550745911 |
Author(s): | Gerhard van der Linde |
Contributor(s): |
REVISION HISTORY
Revision | Details of Modification(s) | Reason for modification | Date | By |
---|---|---|---|---|
0 | Draft release | Applied Databases week 5 worksheet | 2019/02/21 10:45 | Gerhard van der Linde |
MySQL -u root -p <bus.sql
mysql> show create table driver; +--------+-----------------------------------------------+ | Table | Create Table | +--------+-----------------------------------------------+ | driver | CREATE TABLE `driver` ( `licenceNo` varchar(20) NOT NULL, `name` varchar(30) DEFAULT NULL, `busReg` varchar(15) DEFAULT NULL, PRIMARY KEY (`licenceNo`), KEY `busReg` (`busReg`), CONSTRAINT `driver_ibfk_1` FOREIGN KEY (`busReg`) REFERENCES `bus` (`reg`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +--------+-----------------------------------------------+ 1 row in set (0.00 sec)
The driver has a foreign key constraint busReg that requires a valid entry that already exists in the table bus.reg to be present. When the bus entry is deleted from thus bus table, the “ON DELET CASCADE” will also delete the driver associated with this bus entry.
mysql> insert into driver (name) values("Mary"); ERROR 1364 (HY000): Field 'licenceNo' doesn't have a default value
This insert fails because the licenseNo field is a primary key value and cannot be NULL and fails when no value is assigned during the insert.
mysql> insert into driver (licenceNo,name) values("RN2423","Bob"); Query OK, 1 row affected (0.01 sec) mysql> select * from driver; +-----------+------+------------+ | licenceNo | name | busReg | +-----------+------+------------+ | F2233 | Alan | 191-G-123 | | L23423 | John | 12-G-1323 | | RN2423 | Bob | NULL | | X98983 | Tom | 161-D-1323 | +-----------+------+------------+ 4 rows in set (0.00 sec)
The second query works since Bo is added with a licensee number and the minimum criteria is being met.
mysql> insert into driver (licenceNo,name,busReg) values("FF88345","Sean","191-G-123"); Query OK, 1 row affected (0.01 sec) mysql> select * from driver; +-----------+------+------------+ | licenceNo | name | busReg | +-----------+------+------------+ | F2233 | Alan | 191-G-123 | | FF88345 | Sean | 191-G-123 | | L23423 | John | 12-G-1323 | | RN2423 | Bob | NULL | | X98983 | Tom | 161-D-1323 | +-----------+------+------------+ 5 rows in set (0.00 sec)
The last insert also works since all the fields are populated with valid values.
mysql> select * from bus; +------------+---------------+----------+ | reg | maxPassengers | fuel | +------------+---------------+----------+ | 12-G-1323 | 34 | Petrol | | 161-D-1323 | 80 | Diesel | | 162-D-3433 | 120 | Electric | | 191-G-123 | 56 | Diesel | +------------+---------------+----------+ 4 rows in set (0.00 sec) mysql> insert into bus values("12-G-1323",34,"Diesel"); ERROR 1062 (23000): Duplicate entry '12-G-1323' for key 'PRIMARY'
The bus already exists in the table and the primary key constraint prevents duplication in this reg column.
mysql> insert into bus values("171-G885",84,"Petrol"); Query OK, 1 row affected (0.01 sec) mysql> select * from bus; +------------+---------------+----------+ | reg | maxPassengers | fuel | +------------+---------------+----------+ | 12-G-1323 | 34 | Petrol | | 161-D-1323 | 80 | Diesel | | 162-D-3433 | 120 | Electric | | 171-G885 | 84 | Petrol | | 191-G-123 | 56 | Diesel | +------------+---------------+----------+ 5 rows in set (0.00 sec)
All the criteria entered mets the requirements and is successfully added to the table.
mysql> insert into bus values("191-D-45890",120,"Ethanol"); ERROR 1265 (01000): Data truncated for column 'fuel' at row 1 mysql> select * from bus; +------------+---------------+----------+ | reg | maxPassengers | fuel | +------------+---------------+----------+ | 12-G-1323 | 34 | Petrol | | 161-D-1323 | 80 | Diesel | | 162-D-3433 | 120 | Electric | | 171-G885 | 84 | Petrol | | 191-G-123 | 56 | Diesel | +------------+---------------+----------+ 5 rows in set (0.00 sec)
Ethanol is not in the enumeration list of fuels and is therefore rejected.
Update driver set licenceNo = concat("T-", licenceNo) where licenceNo like "F%" or licenceNo like "R%"; select * from driver; +-----------+------+------------+ | licenceNo | name | busReg | +-----------+------+------------+ | L23423 | John | 12-G-1323 | | T-F2233 | Alan | 191-G-123 | | T-FF88345 | Sean | 191-G-123 | | T-RN2423 | Bob | NULL | | X98983 | Tom | 161-D-1323 | +-----------+------+------------+ 5 rows in set (0.00 sec)
What happens and why?
mysql> select * from driver; +-----------+------+------------+ | licenceNo | name | busReg | +-----------+------+------------+ | L23423 | John | 12-G-1323 | | T-F2233 | Alan | 191-G-123 | | T-FF88345 | Sean | 191-G-123 | | T-RN2423 | Bob | NULL | | X98983 | Tom | 161-D-1323 | +-----------+------+------------+ 5 rows in set (0.00 sec) mysql> select * from bus; +------------+---------------+----------+ | reg | maxPassengers | fuel | +------------+---------------+----------+ | 12-G-1323 | 34 | Petrol | | 161-D-1323 | 80 | Diesel | | 162-D-3433 | 120 | Electric | | 171-G885 | 84 | Petrol | | 191-G-123 | 56 | Diesel | +------------+---------------+----------+ 5 rows in set (0.00 sec) mysql> delete from driver -> where name = "Alan"; Query OK, 1 row affected (0.01 sec) mysql> select * from driver; +-----------+------+------------+ | licenceNo | name | busReg | +-----------+------+------------+ | L23423 | John | 12-G-1323 | | T-FF88345 | Sean | 191-G-123 | | T-RN2423 | Bob | NULL | | X98983 | Tom | 161-D-1323 | +-----------+------+------------+ 4 rows in set (0.00 sec) mysql> select * from bus; +------------+---------------+----------+ | reg | maxPassengers | fuel | +------------+---------------+----------+ | 12-G-1323 | 34 | Petrol | | 161-D-1323 | 80 | Diesel | | 162-D-3433 | 120 | Electric | | 171-G885 | 84 | Petrol | | 191-G-123 | 56 | Diesel | +------------+---------------+----------+ 5 rows in set (0.00 sec)
Alan was deleted and nothing else happens. Assuming the question is asked to highlight the fworking of the foreign key constraint busReg and the “ON DELET CASCADE” clause.
So in this case nothing happens but if the bus assigned to Alan was deleted Alan would have disappeared too. Is this right?
What happens and why?
mysql> select * from bus; +------------+---------------+----------+ | reg | maxPassengers | fuel | +------------+---------------+----------+ | 12-G-1323 | 34 | Petrol | | 161-D-1323 | 80 | Diesel | | 162-D-3433 | 120 | Electric | | 171-G885 | 84 | Petrol | | 191-G-123 | 56 | Diesel | +------------+---------------+----------+ 5 rows in set (0.00 sec) mysql> select * from driver; +-----------+------+------------+ | licenceNo | name | busReg | +-----------+------+------------+ | L23423 | John | 12-G-1323 | | T-FF88345 | Sean | 191-G-123 | | T-RN2423 | Bob | NULL | | X98983 | Tom | 161-D-1323 | +-----------+------+------------+ 4 rows in set (0.00 sec) mysql> delete from bus -> where reg = "161-D-1323"; Query OK, 1 row affected (0.01 sec) mysql> select * from bus; +------------+---------------+----------+ | reg | maxPassengers | fuel | +------------+---------------+----------+ | 12-G-1323 | 34 | Petrol | | 162-D-3433 | 120 | Electric | | 171-G885 | 84 | Petrol | | 191-G-123 | 56 | Diesel | +------------+---------------+----------+ 4 rows in set (0.00 sec) mysql> select * from driver; +-----------+------+-----------+ | licenceNo | name | busReg | +-----------+------+-----------+ | L23423 | John | 12-G-1323 | | T-FF88345 | Sean | 191-G-123 | | T-RN2423 | Bob | NULL | +-----------+------+-----------+ 3 rows in set (0.00 sec)
So bus “161-D-1323” and driver “Tom” was deleted because of the “ON DELETE CASCADE” foreign key reference applied on the driver table.
To use this database type use bus2;
MySQL -u root -p <bus2.mysql
What happens and why?
mysql> use bus2; Database changed mysql> select * from driver; +-----------+------+------------+ | licenceNo | name | busReg | +-----------+------+------------+ | F2233 | Alan | 191-G-123 | | L23423 | John | 12-G-1323 | | X98983 | Tom | 161-D-1323 | +-----------+------+------------+ 3 rows in set (0.00 sec) mysql> select * from bus; +------------+---------------+----------+ | reg | maxPassengers | fuel | +------------+---------------+----------+ | 12-G-1323 | 34 | Petrol | | 161-D-1323 | 80 | Diesel | | 162-D-3433 | 120 | Electric | | 191-G-123 | 56 | Diesel | +------------+---------------+----------+ 4 rows in set (0.00 sec) mysql> delete from bus -> where reg = "161-D-1323"; Query OK, 1 row affected (0.01 sec) mysql> select * from driver; +-----------+------+-----------+ | licenceNo | name | busReg | +-----------+------+-----------+ | F2233 | Alan | 191-G-123 | | L23423 | John | 12-G-1323 | | X98983 | Tom | NULL | +-----------+------+-----------+ 3 rows in set (0.00 sec) mysql> select * from bus; +------------+---------------+----------+ | reg | maxPassengers | fuel | +------------+---------------+----------+ | 12-G-1323 | 34 | Petrol | | 162-D-3433 | 120 | Electric | | 191-G-123 | 56 | Diesel | +------------+---------------+----------+ 3 rows in set (0.00 sec)
The behavior changed by setting driver Tom busReg foreign key to NULL. This happened because of the amended foreign key clause “ON DELETE SET NULL”
mysql> show create table driver; CREATE TABLE `driver` ( `licenceNo` varchar(20) NOT NULL, `name` varchar(30) DEFAULT NULL, `busReg` varchar(15) DEFAULT NULL, PRIMARY KEY (`licenceNo`), KEY `busReg` (`busReg`), CONSTRAINT `driver_ibfk_1` FOREIGN KEY (`busReg`) REFERENCES `bus` (`reg`) ON DELETE SET NULL
To use this database type use employees2;
MySQL -u root -p <employees2.sql use employees2; Database changed
The average year should be rounded down to the nearest whole number.
For example,
mysql> SELECT emp_no, first_name, last_name, year(birth_date) as bd -> FROM employees2.employees -> where year(birth_date) in ( -> SELECT truncate(avg(year(birth_date)),0) FROM employees2.employees -> ) -> ; +--------+------------+--------------+------+ | emp_no | first_name | last_name | bd | +--------+------------+--------------+------+ | 10007 | Tzvetan | Zielinski | 1957 | | 10045 | Moss | Shanbhogue | 1957 | | 10054 | Mayumi | Schueller | 1957 | | 10080 | Premal | Baek | 1957 | | 10094 | Arumugam | Ossenbruggen | 1957 | +--------+------------+--------------+------+ 5 rows in set (0.00 sec)
or with rounding instead ….
mysql> SELECT emp_no, first_name, last_name, year(birth_date) as bd -> FROM employees2.employees -> where year(birth_date) in ( -> SELECT round(avg(year(birth_date)),0) FROM employees2.employees -> ) -> ; +--------+------------+-----------+------+ | emp_no | first_name | last_name | bd | +--------+------------+-----------+------+ | 10008 | Saniya | Kalloufi | 1958 | | 10017 | Cristinel | Bouloucos | 1958 | | 10024 | Suzette | Pettey | 1958 | | 10025 | Prasadram | Heyers | 1958 | | 10030 | Elvis | Demeyer | 1958 | | 10050 | Yinghua | Dredge | 1958 | | 10071 | Hisao | Lipner | 1958 | +--------+------------+-----------+------+ 7 rows in set (0.00 sec)
mysql> show tables; +----------------------+ | Tables_in_employees2 | +----------------------+ | dept | | employees | | salaries | +----------------------+ 3 rows in set (0.00 sec) mysql> show create table dept; CREATE TABLE `dept` ( `dept_no` varchar(10) NOT NULL, `name` varchar(50) NOT NULL, PRIMARY KEY (`dept_no`) ) mysql> show create table employees; CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` enum('M','F') NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`) ) 1 row in set (0.00 sec) mysql> show create table salaries; CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, `dept_no` varchar(10) DEFAULT NULL, PRIMARY KEY (`emp_no`,`from_date`), KEY `emp_no` (`emp_no`), KEY `dept_no` (`dept_no`), CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE, CONSTRAINT `salaries_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `dept` (`dept_no`) ON DELETE CASCADE )1 row in set (0.00 sec)
So the master table is salaries and contains foreign key to the employees and departments tables.
SELECT DISTINCT(ee.emp_no), ee.first_name, ee.last_name, ed.name AS department FROM employees2.employees AS ee LEFT JOIN employees2.salaries AS es ON ee.emp_no=es.emp_no LEFT JOIN employees2.dept AS ed ON es.dept_no=ed.dept_no
emp_no | first_name | last_name | department |
---|---|---|---|
10001 | Georgi | Facello | Human Resources |
10002 | Bezalel | Simmel | Human Resources |
10003 | Parto | Bamford | Human Resources |
… | |||
10028 | Domenick | Tempesti | Human Resources |
10029 | Otmar | Herbst | Human Resources |
10030 | Elvis | Demeyer | Human Resources |
10031 | Karsten | Joslin | Research & Development |
10032 | Jeong | Reistad | Research & Development |
10033 | Arif | Merlo | Research & Development |
… | |||
10068 | Charlene | Brattka | Research & Development |
10069 | Margareta | Bierman | Research & Development |
10070 | Reuven | Garigliano | Research & Development |
10071 | Hisao | Lipner | Sales |
10072 | Hironoby | Sidou | Sales |
10073 | Shir | McClurg | Sales |
… | |||
10098 | Sreekrishna | Servieres | Sales |
10099 | Valter | Sullins | Sales |
10100 | Hironobu | Haraldson | Sales |
100 rows in set (0.00 sec)