University Database Design and Data Modelling Assignment

This assignment solution demonstrates the creation of database tables for employees, departments, and salaries, along with the insertion of sample data. The solution includes SQL queries to update salaries, retrieve employees with minimum and maximum salaries, find employees who joined after a specific year, filter employees within a salary range, update an employee's salary, and calculate the number of staff and total salary per department. The assignment covers key aspects of database design, including primary and foreign keys, data manipulation, and data retrieval using various SQL commands, providing a comprehensive example of database implementation and query writing.

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

1DATA MODELLING & DATABASE DESIGN
Task 1:
Department Table:
CREATE TABLE department (
Department_id int(11) NOT NULL,
Deptname varchar(30) NOT NULL,
DeptLocation varchar(20) NOT NULL,
DeptFloor varchar(20) NOT NULL,
PRIMARY KEY (Department_id)
);
Employee Table:
CREATE TABLE employee(
employee_id int(11) NOT NULL,
Empname varchar(25) NOT NULL,
Managerid int(11) NOT NULL,

2DATA MODELLING & DATABASE DESIGN
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,
PRIMARY KEY (employee_id),
FOREIGN KEY (Department_id) REFERENCES Department(Department_id));
Salary Table:
CREATE TABLE salary(
salary_level int(11) NOT NULL,

3DATA MODELLING & DATABASE DESIGN
Salarymin int(11) NOT NULL,
Salarymax int(11) NOT NULL,
PRIMARY KEY (salary_level));
Task 2:
Due to foreign key, Department table need to populate first in order to populate the employee
table:
INSERT INTO department (Department_id, Deptname, DeptLocation, DeptFloor) VALUES
(101, 'account', 'WA', '5th'),
(102, 'HR', 'NSW', '1st'),
(103, 'IT support', 'Sydney', '3rd'),
(104, 'Marketing', 'WA', '5th'),
(105, 'R & D', 'Sydney', '10st');

4DATA MODELLING & DATABASE DESIGN
Task 3:
After populating the department table, now we can insert data in employee table:
INSERT INTO employee (employee_id, Empname, Managerid, Dateofhire, Jobname, Salary,
Department_id, DOB, Address) VALUES
(111, 'Morgan', 2001, '2010-1-1', 'accountant', '50000.00', 101, '1980-1-17', 'NSW'),
(112, 'Ben', 2002, ‘1990-03-13', 'accountant', '30000.00', 101, '1996-1-12', 'WA'),
(113, 'Andrian', 2003, '2018-12-18', 'HR', '40000.00', 102, '1996-1-18', 'sydney'),
(114, 'Wilson', 2004, '2015-11-26', 'HR', '5000.00', 102, '1992-11-27', 'park Avenue '),
(115, 'Robert', 2005, '2019-06-17', 'IT support', '55500.00', 103, '1995-10-28', 'sydney'),
(116, 'Mathew', 2006, '2018-10-29', 'IT support', '90000.00', 103, '1991-2-10', 'Brisbane'),
(117, 'Amy', 2007, '2019-11-22', 'marketing', '50000.00', 104, '1990-12-22', 'WA'),
(118, 'Max', 2008, '2017-2-26', 'marketing', '62000.00', 104, '1989-12-20', 'NSW'),
(119, 'David', 2009, ‘1995-11-21’, 'R&D', '90000.00', 105, '1980-1-22', 'Sydney'),
(120, 'Andy', 2010, '2012-10-22', 'R&D', '50000.00', 105, '1990-05-21', 'Sydney');
Task 4:
INSERT INTO salary (salary_level, Salarymin, Salarymax) VALUES

5DATA MODELLING & DATABASE DESIGN
(1, 15000, 29999),
(2, 30000, 44999),
(3, 45000, 54999),
(4, 55000, 94999),
(5, 95000, 99999);
Task 5:
update employee
set salary = (Salary*(12/100)+Salary);

6DATA MODELLING & DATABASE DESIGN
Task 6:
select employee.EMPNAME, employee.SALARY from employee
where employee.SALARY IN (select max(salary) from employee)
OR
employee.SALARY IN (select MIN(salary) from employee);
Task 7:
select employee_id,Empname, jobname, Dateofhire from employee where
year(Dateofhire)>2001;

7DATA MODELLING & DATABASE DESIGN
Task 8:
SELECT * from employee where Salary>55000 and Salary<95000;
Task 9:
update employee
set salary = (Salary*(15/100)+Salary)
where Empname='Robert';

8DATA MODELLING & DATABASE DESIGN
Task 10:
select department.deptname, count(employee.employee_id) as `number of staff`,
sum(employee.salary) as sum from department, employee where
employee.Department_id=department.Department_id GROUP by (department.Deptname);
Task 11:
select * from employee where Address like '%Avenue%';