Data Modelling & Database Design | Assignment

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%';