Data Modelling & Database Design tasks 2022

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'),