Database Modelling Assignment | Task

Running head: DATABASE MODELLING
Database Modelling
Name of the Student
Name of the University
Author Note

DATABASE MODELLING
1
Task 1: Create three tables with relevant keys as suggested in the above diagram
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));
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,
PRIMARY KEY (employee_id),
FOREIGN KEY (Department_id) REFERENCES Department(Department_id));
CREATE TABLE salary(
salary_level int(11) NOT NULL,
Salarymin int(11) NOT NULL,
Salarymax int(11) NOT NULL,
PRIMARY KEY (salary_level));
Task 2: Insert record of 10 employees in the employee table. However for inserting in
employee table will invoke foreign key constraint as there are no data about departments.
Hence, first depart table will be populated with data.
INSERT INTO department (Department_id, Deptname, DeptLocation,
DeptFloor) VALUES
(2011, 'DEVELOPMENT', 'Hobart', '7th'),
(2012, 'ANALYST', 'Melobourne', '12th'),
(2013, 'TECHNICAL', 'Sydney', '1th'),
(2014, 'HUMAN RESOURCES', 'Perth', '5th'),

DATABASE MODELLING
2
(2015, 'ACCOUNTS', 'Brisbane', '1st');
Task 3: As department table is already populated in previous task. Next employee tbale will
be populated.
INSERT INTO employee (employee_id, Empname, Managerid, Dateofhire,
Jobname, Salary, Department_id, DOB, Address) VALUES
(1001, 'James', 5091, '2019-10-15', 'PROGRAMMER', '65500.00', 2011,
'1995-10-08', 'Hobart'),
(1003, 'Minie', 5095, '2019-07-23', 'TECHNICIAN', '99000.00', 2012,
'1995-10-22', 'Melobourne'),
(1002, 'Flynn', 5092, '2017-10-08', 'PROGRAMMER', '45000.00', 2011,
'2000-10-16', 'Melobourne'),
(1004, 'Kane', 5093, '2012-10-16', 'MANAGER', '95000.00', 2013,
'2001-01-17', 'Melobourne'),
(1006, 'Smith', 5094, '2013-04-08', 'ANALYST', '99500.00', 2014,
'1993-12-17', 'Brisbane'),
(1005, 'Wilson', 5096, '2016-05-09', 'ACCOUNTANT', '95050.00', 2013,
'1998-12-18', 'Queens'),
(1007, 'Robert', 5096, '2015-10-15', 'PROGRAMMER', '75000.00', 2011,
'1999-02-25', 'Brisbane'),
(1009, 'Nick', 5092, '2015-12-06', 'ACCOUNTANT', '65000.00', 2013,
'1997-06-06', 'Havana Avenue'),
(1008, 'Samuel', 5097, '2015-10-02', 'ACCOUNTANT', '59050.00', 2013,
'1992-12-12', 'Brisbane'),
(1010, 'Vencie', 5096, '2012-12-12', 'MANAGER', '55500.00',
2015, '1996-04-18', 'St George Avenue');
Task 4: Insert record of 5 salary levels in the salary table
INSERT INTO salary (salary_level, Salarymin, Salarymax) VALUES
(1, 15000, 29999),
(2, 30000, 44999),
(3, 45000, 54999),
(4, 55000, 94999),
(5, 95000, 99999);

DATABASE MODELLING
3
Task 5: Write a query to display the information about the employees in the employee table.
select * from employee;
Task 6: Write a query to display the name of all the employees
select empname from employee;