MIS602: Practical Assessment on Data Modelling and Database Design

This assignment solution demonstrates data modeling and database design principles through the creation and manipulation of database tables using SQL. The solution starts by creating three tables: `salary`, `employee`, and `department`, with appropriate data types and keys. It then populates these tables with sample data, including employee details, salary levels, and department information. The core of the assignment involves writing a series of SQL queries to extract and modify data. These queries cover a range of operations, including selecting all data, filtering data based on various criteria (salary ranges, job titles, and date of hire), updating salaries, deleting records, and performing aggregate functions such as counting employees and calculating salary sums per department. The solution also includes the addition of new employee records and a bibliography of relevant database systems and design resources.

Running head: DATA MODELLING & DATABASE DESIGN
Data Modelling & Database Design
Name of the Student
Name of the University
Author’s Note

1
DATA MODELLING & DATABASE DESIGN
Task 1: Creation of three tables with relevant keys as given in the diagram
Salary Table
CREATE TABLE `salary` (
`salary_level` int(11) NOT NULL,
`salarymin` int(11) NOT NULL,
`salarymax` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Employee Table
CREATE TABLE `employee` (
`employee_id` int(11) NOT NULL,
`Empname` varchar(25) NOT NULL,
`Managerid` int(11) NOT NULL,
`Dateofhire` date NOT NULL,
`Jobname` varchar(15) NOT NULL,
`Salary` decimal(10,2) NOT NULL,
`department_id` int(11) NOT NULL,
`DOB` date NOT NULL,
`address` varchar(30) NOT NULL

2
DATA MODELLING & DATABASE DESIGN
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Department table
CREATE TABLE `department` (
`departmnt_id` int(11) NOT NULL,
`Deptname` varchar(30) NOT NULL,
`deptLocation` varchar(20) NOT NULL,
`deptFloor` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Task 2: Insertion of record of 10 employees in the employee table
INSERT INTO `employee`(`employee_id`, `Empname`, `Managerid`, `Dateofhire`, `Jobname`,
`Salary`, `department_id`, `DOB`, `address`) VALUES
(101, 'Hannah', 002, '2015-06-19', 'Developer', 35000, 1011, '1990-05-01', '1778 Bombardier
Way'),
(102, 'Jane', 001, '2015-05-18', 'Analyst', 45000, 1022, '1986-04-06', '671 Rockford Mountain
Lane'),
(103, 'Karen', 003, '2000-04-17', 'Analyst', 65000, 1033, '1987-07-01', '2005 Harter Street'),
(104, 'Lisa', 003, '2001-03-16', 'Analyst', 95000, 1055, '1988-07-02', '996 Tree Top Lane'),
(105, 'Ruth', 001, '2015-02-14', 'Tester', 100000, 1022, '1989-02-20', '2670 Morgan Street'),
(106, 'Samantha', 003, '2013-01-14', 'Analyst', 85000, 1033, '1990-08-10', '3657 Smith Road'),

3
DATA MODELLING & DATABASE DESIGN
(107, 'Connor', 002, '2010-07-13', 'Manager', 95000, 1044, '1991-06-11', '898 Ruckman Road'),
(108, 'Flynn', 003, '2011-08-12', 'Developer', 65000, 1011, '1992-04-13', '746 Fraggle Drive'),
(109, 'Luke', 003, '2015-09-11', 'Developer', 75000, 1011, '2001-05-14', '2787 Cambridge Place'),
(110, 'Robert', 004, '2018-10-10', 'Analyst', 55000, 1011, '2004-02-20', '4315 Lochmere Avenue')
Task 3: Insertion of record for 5 department in the department table
INSERT INTO `department` (`departmnt_id`, `Deptname`, `deptLocation`, `deptFloor`)
VALUES
(1011, 'Development', 'Kentucky', '8th'),
(1022, 'Testing', 'Cannabera', '7th'),
(1033, 'Analysis', 'Adelaide', 'Ground'),
(1044, 'Sales', 'Connecticut', '6th'),
(1055, 'Marketing', 'London', '5th');
Task 4: Insertion of 5 record of salary level in salary table
INSERT INTO `salary`(`salary_level`, `salarymin`, `salarymax`) VALUES
(1,25000,35000),
(2,360000,45000),
(3,46000,55000),
(4,56000,65000),
(5,66000,100000);

4
DATA MODELLING & DATABASE DESIGN
Task 5: Query for displaying information about the employees in the employee table
SELECT * FROM employee
Task 6: Query to display the name of all employees
SELECT empname FROM employee;
Task 7: Query to display the name of all employees and their jobname
SELECT empname, jobname FROM employee;
Task 8: Query for displaying unique jobname for all employees
SELECT DISTINCT jobname FROM `employee`;
Task 9: Query to increase salary of all employees by 12%. Displaying empname, jobname
and salary after increment
UPDATE employee
SET salary = (salary * 112)/100;
Task 10: Query for displaying employee names with minimum and maximum salary
select empname,salary from employee where salary in((select min(salary) from employee),
(select max(salary) from employee) );
Task 11: Query for displaying employee id, employee name, jobname for the employees
with salary greater than 90,000 P.A.
SELECT employee_id, empname, jobname, salary
FROM employee
WHERE salary>=90000;

5
DATA MODELLING & DATABASE DESIGN
Task 12: Query for displaying details of the employees with jobname Manager
SELECT * FROM employee WHERE jobname="Manager";
Task 13: Query for displaying all details of employee with name Robert
SELECT * FROM employee WHERE empname="Robert";
Task 14: Query for displaying details of the employee who worked as manager and having
salary greater than 95000 P.A.
SELECT *
FROM employee
WHERE salary>=90000 AND jobname="Manager";
Task 15: Query for displaying employeeid, employee name, jobname and date of joining of
the employees joined after 2001.
SELECT employee_id, Empname, Jobname, Dateofhire FROM employee WHERE dateofhire >
('2001-1-1');
Task 16: Query for displaying list of employees having annual salary within the range
55000 and 95000.
SELECT * FROM employee
WHERE Salary BETWEEN 55000 AND 95000;
Task 17: Query for displaying list of employees in descending order of the salaries
SELECT *
FROM employee emp

6
DATA MODELLING & DATABASE DESIGN
ORDER BY emp.salary DESC;
Task 18: Query for counting number of employees in the employee table
SELECT COUNT(*) FROM employee;
Task 19: Insertion of new record in employee table and addition of ANALYST as the
jobname
INSERT INTO `employee` (`employee_id`, `empname`, `managerid`, `dateofhire`, `jobname`,
`salary`, `department_id`, `dob`, `address`) VALUES ('111', ' Sanderson', '005', '2019-05-10',
'Analyst', '65000', '11011', '1993-04-28', ' 1236 Buena Vista Avenue');
Task 20: Insertion of new record in employee table with following data fields
“employee_id= 1011 empname= Janet jobname= PROGRAMMER managerid= 5095
dateofhire= 12-10-2014 salary= 90000 department_id=2011”
INSERT INTO `employee` (`employee_id`, `empname`, `managerid`, `dateofhire`, `jobname`,
`salary`, `department_id`, `dob`, `address`) VALUES ('1101', 'Janet', '5095', '2014-10-12',
'Programmer', '90000', '2011', '1975-05-11', '2502 Prospect Street');
Task 21: Query for deleting record of employee having name ‘Flynn’
DELETE FROM employee
WHERE empname="%Flynn%";
Task 22: Query for updating salary by 15% of the employees having name ROBERT
update employee
SET Salary = (Salary * 115)/100

7
DATA MODELLING & DATABASE DESIGN
WHERE Empname="Robert";
Task 23: Query for finding the number of staffs working in each department and
summation of their salaries
SELECT department_id, COUNT(*),SUM(salary) FROM employee GROUP BY
department_id;
Task 24: Query for finding the employees with string ‘Avenue’ in their address.
SELECT * FROM employee WHERE address LIKE '%Avenue'

8
DATA MODELLING & DATABASE DESIGN
Bibliography
Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management.
Cengage Learning.
Hogan, R. (2018). A practical guide to database design. Chapman and Hall/CRC.
Liao, Y. T., Zhou, J., Lu, C. H., Chen, S. C., Hsu, C. H., Chen, W., ... & Chung, Y. C. (2016).
Data adapter for querying and transformation between SQL and NoSQL database. Future
Generation Computer Systems, 65, 111-121.
Ma, L., Van Aken, D., Hefny, A., Mezerhane, G., Pavlo, A., & Gordon, G. J. (2018, May).
Query-based workload forecasting for self-driving database management systems.
In Proceedings of the 2018 International Conference on Management of Data (pp. 631-
645). ACM.
Poljak, R., Poščić, P., & Jakšić, D. (2017, May). Comparative analysis of the selected relational
database management systems. In 2017 40th International Convention on Information
and Communication Technology, Electronics and Microelectronics (MIPRO) (pp. 1496-
1500). IEEE.
Pujayanto, P., Budiharti, R., Adhitama, E., Nuraini, N. R. A., & Putri, H. V. (2018). The
development of a web-based assessment system to identify students’ misconception
automatically on linear kinematics with a four-tier instrument test. Physics
Education, 53(4), 045022.
Samonte, M. J. C., Mullen, R. C. D., Endaya, S. C. M. B., & Huang, P. C. T. (2018, August).
Development of Online Hospital Document Management with SMS Notification System.

9
DATA MODELLING & DATABASE DESIGN
In Proceedings of the 2nd International Conference on E-Society, E-Education and E-
Technology (pp. 150-154). ACM.
Sánchez-de-Madariaga, R., Muñoz, A., Castro, A. L., Moreno, O., & Pascual, M. (2018).
Executing Complexity-Increasing Queries in Relational (MySQL) and NoSQL
(MongoDB and EXist) Size-Growing ISO/EN 13606 Standardized EHR
Databases. JoVE (Journal of Visualized Experiments), (133), e57439.