Database System Implementation and SQL Queries - MIS602 Assignment

This document presents a comprehensive solution to a database system assignment, focusing on practical implementation using SQL queries. The assignment begins with the creation of tables, including 'department', 'employee', and 'salary', designed based on an ERD diagram to ensure data integrity and efficient management. The solution details the SQL commands for creating these tables, establishing primary and foreign keys, and inserting data into each table. It covers a range of SQL operations, such as inserting data into the employee, department, and salary tables, fetching data, displaying specific employee information, updating salaries, and deleting records. The solution also demonstrates how to retrieve data based on various criteria, including job titles, salary ranges, and date of hire, and includes queries to perform tasks like increasing salaries, counting employees, and finding employees with specific characteristics. The assignment concludes with a reflection on the learning experience, highlighting the knowledge gained in database management and SQL query construction.

Running head: DATABASE SYSTEM
DATABASE SYSTEM
Name of the Student:
Name of the University:
Author note

1
DATABASE SYSTEM
Table of Contents
Task 1...............................................................................................................................................3
Task 2...............................................................................................................................................5
Task 3...............................................................................................................................................6
Task 4...............................................................................................................................................6
Task 5...............................................................................................................................................7
Task 6...............................................................................................................................................7
Task 7...............................................................................................................................................7
Task 8...............................................................................................................................................8
Task 9...............................................................................................................................................8
Task 10.............................................................................................................................................8
Task 11.............................................................................................................................................8
Task 12.............................................................................................................................................8
Task 13.............................................................................................................................................9
Task 14.............................................................................................................................................9
Task 15.............................................................................................................................................9
Task 16.............................................................................................................................................9
Task 17...........................................................................................................................................10
Task 18...........................................................................................................................................10
Task 19...........................................................................................................................................10

2
DATABASE SYSTEM
Task 20...........................................................................................................................................10
Task 21...........................................................................................................................................11
The is 22........................................................................................................................................11
Task 23...........................................................................................................................................11
Task 24...........................................................................................................................................11
Overall experience.........................................................................................................................12
Bibliography..................................................................................................................................13

3
DATABASE SYSTEM
Task 1
In order to develop the database that I have used SQL queries. In order to develop the
SQL, query I have prepared the database using the ERD table so that it becomes easy to store the
data. In order to link all the data with the developed table it is important to manage and store data
properly. Firstly, the department table for the ERD diagram has been developed so that it
becomes easy to link it with the employee table. The following queries were used for the
development of the database:
CREATE TABLE `department` (
`Department_ID` int(11) NOT NULL,
`DeptName` varchar(30) NOT NULL,
`DeptLocation` varchar(20) NOT NULL,
`DeptFloor` varchar(30) NOT NULL
);
CREATE TABLE `employee` (
`Employee_ID` int(11) NOT NULL,
`EmpName` varchar(25) NOT NULL,
`ManagerID` int(11) NOT NULL,
`DateOfHire` date NOT NULL,

4
DATABASE SYSTEM
`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
) ;
CREATE TABLE `salary` (
`Salary_Level` int(11) NOT NULL,
`SalaryMin` int(11) NOT NULL,
`SalaryMax` int(11) NOT NULL
) ;
The department table was designed with all the necessary attributes and primary key so that it
becomes easy to manage the data present within the table. The primary key and foreign were
established with the query as described below:
ALTER TABLE `department`
ADD PRIMARY KEY (`Department_ID`);
ALTER TABLE `employee`
ADD PRIMARY KEY (`Employee_ID`),

5
DATABASE SYSTEM
ADD KEY `Department_ID` (`Department_ID`);
ALTER TABLE `salary`
ADD PRIMARY KEY (`Salary_Level`);
ALTER TABLE `employee`
ADD CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`Department_ID`) REFERENCES
`department` (`Department_ID`);
Task 2
The second part of the assessment was focused on inserting 10 data within the employee
table. However while inserting the data within the table it is important to ensure that every
employee is linked with proper department. In order to manage all the data efficiently task three
was completed at first. This helped in fetching the data and linking it effectively. In order to
insert data within the employee table the following command has been used:
INSERT INTO `employee` (`Employee_ID`, `EmpName`, `ManagerID`, `DateOfHire`,
`JobName`, `Salary`, `Department_ID`, `DOB`, `Address`) VALUES
(1001, 'Steven', 5003, '2014-10-14', 'Senior associate', '4000.00', 2004, '1994-09-18', '98 broad
avenue'),
(1002, 'Garry', 5001, '2015-09-17', 'Manager', '8200.00', 2001, '1990-05-11', '67 Fr Avenue'),
(1003, 'Flynn', 5002, '2016-03-04', 'Junior Associat', '4300.00', 2007, '1990-12-31', '44 Jf Stree'),

6
DATABASE SYSTEM
(1004, 'Roberto', 5001, '2017-06-12', 'Chief Engineer', '8100.00', 2011, '1990-08-23', '55 Rm
road'),
(1005, 'Shiela', 5003, '2018-09-17', 'Analyst', '4700.00', 2008, '1998-12-10', '45 west end'),
(1006, 'Robert', 5004, '2019-09-10', 'Engineer', '5700.00', 2007, '1990-12-31', '56 Ternt road'),
(1007, 'Matt', 5003, '2014-08-05', 'Analyst', '5100.00', 2001, '1998-04-21', '45 River road'),
(1008, 'Paula', 5002, '2013-05-14', 'Manager', '8300.00', 2004, '2019-06-12', '46 Rd street'),
(1009, 'Louis', 5004, '2012-08-14', 'Engineer', '6300.00', 2004, '2019-12-10', '54 De road'),
(1010, 'Mike', 5001, '2015-11-06', 'Manager', '8500.00', 2004, '1990-08-07', 'CS Avenue street');
Task 3
In order to insert data within the department table the following command has been used:
INSERT INTO `department` (`Department_ID`, `DeptName`, `DeptLocation`, `DeptFloor`)
VALUES
(2001, 'Production', 'Sydney', 'Second'),
(2004, 'Sales', 'Melbourne', 'First'),
(2007, 'Human Resources', 'Sydney', 'Second'),
(2008, 'IT', 'Hobbart', 'First'),
(2011, 'Accounts', 'Sydney', 'Second');

7
DATABASE SYSTEM
Task 4
The below command shows the way data are instead in salary table:
INSERT INTO `salary` (`Salary_Level`, `SalaryMin`, `SalaryMax`) VALUES
(1, 3000, 4000),
(2, 400, 5000),
(3, 5000, 7000),
(4, 7000, 9000),
(5, 9000, 10000);
Task 5
The below given query is used for fetching the information from employees table:
SELECT * FROM `employee` ;
Task 6
The query below is used for displaying the names of every employee is present within the
table:
SELECT EmpName FROM `employee` ;

8
DATABASE SYSTEM
Task 7
The below query is used for fetching the list of Employee names and their job name from
employee table:
SELECT EmpName, Jobname FROM `employee` ;
Task 8
The query is used for identifying unique job name in the employee table:
SELECT DISTINCT JobName FROM `employee` ;
Task 9
UPDATE employee SET Salary = Salary + (salary * 12 / 100); The query has been used
for increasing salary by 12%.
Task 10
The query is used for displaying imply names with minimum and maximum salary:
ELECT EmpName,salary FROM employee where salary = (select max(salary) from employee)
OR salary = (select min(salary) from employee);
Task 11
The query has been developed for displaying employee ID, employee name and job name
from employee table with salary greater than 90,000:
SELECT Employee_ID, EmpName,Jobname FROM `employee`

9
DATABASE SYSTEM
HAVING (Salary*12) > 90000;
Task 12
The query has been used for displaying employee details with job name as manager:
SELECT * FROM `employee` WHERE JobName = "Manager";
Task 13
The employees with the name Robert are displayed with this query:
SELECT * FROM `employee` WHERE EmpName = "Robert";
Task 14
For displaying details of employees with Germany manager and have salary more than
95,000 per annum:
SELECT * FROM `employee` HAVING (Salary*12) > 90000 and JobName = "Manager";
Task 15
Query used to display Employee id, Employee name, job name and date of joining for the
employees who joined before 2001:
SELECT Employee_ID, EmpName,Jobname, DateOfHire FROM `employee` WHERE
YEAR(DateOfHire) > 2001;

10
DATABASE SYSTEM
Task 16
Query for displaying list of employees with annual salary between 55,000 and 95000:
SELECT Employee_ID, EmpName,Jobname FROM `employee` HAVING (Salary*12) < 95000
AND (Salary*12) > 55000;
Task 17
Query used for displaying list of employs in descending order as per their salary:
SELECT EmpName,Salary FROM `employee` ORDER BY Salary DESC;
Task 18
To count the number of employees in employee table the query has been described
below:
SELECT COUNT(EmpName) FROM `employee`
Task 19
The query for new recording session in employee table is listed below:
INSERT INTO `employee` (`Employee_ID`, `EmpName`, `ManagerID`, `DateOfHire`,
`JobName`, `Salary`, `Department_ID`, `DOB`, `Address`) VALUES (11, 'James', 3, '2019-10-
14', 'Analyst', '5000.00', 4, '1994-09-18', '98 Rd street');
Task 20
To insert a new record in employee table the query has been used:

11
DATABASE SYSTEM
INSERT INTO `employee` (`Employee_ID`, `EmpName`, `ManagerID`, `DateOfHire`,
`JobName`, `Salary`, `Department_ID`, `DOB`, `Address`) VALUES (1011, 'Janet', 5095, '2014-
10-12', 'Programmer', '90000.00', 2011, '1994-09-18', '98 Rd street');
Task 21
For deleting the record of the employee with the name Flynn, the query has been used:
DELETE FROM employee WHERE EmpName = "Flynn";
The is 22
The query for updating salary by 15% of the employees with the name Robert stated
below:
UPDATE employee SET Salary = Salary + (salary * 15 / 100) WHERE EmpName = "Robert";
Task 23
The query for finding number of staff working in each department is listed below:
SELECT COUNT department.DeptName,(employee.EmpName), SUM(employee.Salary)
FROM department INNER JOIN employee ON department.Department_ID =
employee.Department_ID
GROUP BY department.DeptName;
Task 24
The query for finding all employees with string Avenue in their address is listed below:

12
DATABASE SYSTEM
SELECT * FROM employee WHERE Address LIKE '%Avenue%';
Overall experience
I have gained useful knowledge through this assessment. These It will help me in future
for managing the database and for managing links between different tables. This has been helpful
for me in getting through the basics and would help me in advanced concepts in future.

13
DATABASE SYSTEM
Bibliography
Eghtesad, A., Zecevic, M., Lebensohn, R. A., McCabe, R. J., & Knezevic, M. (2018). Spectral
database constitutive representation within a spectral micromechanical solver for
computationally efficient polycrystal plasticity modelling. Computational Mechanics,
61(1-2), 89-104.
Galdos, M. V., Soares, J., Olmez, S., Vargas, V., Degaspari, I., do Carmo, J. B., & Cantarella, H.
(2019). Application of Statistical Modelling to a Field Experiment Database to Predict N
2 O Emissions in Sugarcane Production. AGUFM, 2019, GC31J-1345.