~~NOTOC~~ |<100% 25% - >| ^ \\ DATA ANALYTICS REFERENCE DOCUMENT\\ \\ ^^ ^ Document Title:|Applied Databases week 5 worksheet| ^ Document No.:|1550745911| ^ Author(s):|Gerhard van der Linde| ^ Contributor(s):| | **REVISION HISTORY** |< 100% 10% - - 10% 17% 10% >| ^ \\ Revision\\ \\ ^\\ Details of Modification(s)^\\ Reason for modification^ \\ Date ^ \\ By ^ | [[:doku.php?id=worksheet:databases:topic5&do=revisions|0]] |Draft release|Applied Databases week 5 worksheet| 2019/02/21 10:45 | Gerhard van der Linde | ---- ====== Applied Databases - Week 5 ====== ===== 1. Get bus.sql from Moodle and import it into MySQL. ===== MySQL -u root -p ===== 2. How are the tables in the database related? ===== {{submissions:worksheet:databases:busreg.png?nolink&400|}} 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. ===== 3. Add the following drivers: ===== * “Mary” * “Bob” – licence number “RN2423” * “Sean” – licence number “FF88345” who drives bus “191-G-123” * What happens and why? 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. ===== 4. Add the following buses: ===== * "12-G-1323" that can hold up to 34 passengers and runs on "Diesel" * "171-G-885" that can hold up to 84 passengers and runs on "Petrol" * "191-D-45890" that can hold up to 120 passengers and runs on "Ethanol" * 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 | | 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. CREATE TABLE `bus` ( `reg` varchar(15) NOT NULL, `maxPassengers` int(11) DEFAULT NULL, `fuel` enum('Diesel','Petrol','Electric') DEFAULT 'Diesel', PRIMARY KEY (`reg`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | ===== 5. Update driver’s licences that contain the letters “F” or “R” to have the letters “T-“ before their current licence number. ===== 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) ===== 6. Delete driver “Alan”. ===== 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? ===== 7. Delete bus “161-d-1323”. ===== 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. ===== 8. Get bus2.sql from Moodle and import it into MySQL. ===== To use this database type use bus2; MySQL -u root -p ===== 9. Delete bus “161-d-1323”. ===== 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 ===== 10. Get employees2.sql from Moodle and import it into MySQL. ===== To use this database type use employees2; MySQL -u root -p ===== 11. Show the emp_no, first_name and last_name of employees born in the average year. ===== The average year should be rounded down to the nearest whole number. For example, * 1949.1 becomes 1949. * 1949.9 becomes 1949. * 1949.0 becomes 1949. 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) ===== 12. Show the emp_no, first_name, last_name and name of the department each employee is in. ===== ==== First determine the relationships.... ==== 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) {{submissions:worksheet:databases:emp2_diagram.png?nolink&400 |}} So the master table is salaries and contains foreign key to the employees and departments tables. ==== Construct the query ==== 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 ==== Concatenated Query Result ==== ^ 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)