Database Design and SQL

Running head: DATABASE DESIGN AND SQL
DATABASE DESIGN AND SQL
Name of the Student
Name of the University
Author Note

1DATABASE DESIGN AND SQL
Question 1:
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
);
CREATE TABLE `department` ( `department_id` INT NOT NULL
AUTO_INCREMENT ,`deptname` VARCHAR(30) NOT NULL , `deptlocation`
VARCHAR(20) NOT NULL , `deptfloor`VARCHAR(20) NOT NULL , PRIMARY KEY
(`departmentid`));
CREATE TABLE `salary` ( `salary_level` INT NOT NULL , `salarymin` INT NOTNULL ,
`salarymax` INT NOT NULL , PRIMARY KEY (`salary_level`));

2DATABASE DESIGN AND SQL
Question 2:
INSERT INTO `employee` (`employee_id`, `empname`, `managerid`, `dateofhire`, `jobname`,
`salary`, `department_id`, `DOB`, `address`) VALUES
(1, 'Flynn Jones', 105, '2010-07-21', 'Finance manager', '40000.00', 4, '1980-07-21', 'Sydney '),
(2, 'Smith James', 102, '2015-02-23', 'Executive manag', '59214.68', 2, '0000-00-00', 'Wesley'),
(3, 'Nick Jonas', 102, '2016-03-08', 'Operational Man', '78952.90', 1, '1987-08-21', 'Sydney '),
(4, 'Harry Smith', 102, '2014-03-21', 'Executive manag', '59214.68', 4, '1980-03-21', 'Sydney '),
(5, 'Rachel smith', 104, '2010-07-21', 'Intern ', '19738.22', 3, '1980-07-21', 'Sydney '),