Assignment on Data Modelling & Database Design

Running head: DATA MODELLING & DATABASE DESIGN
DATA MODELLING & DATABASE DESIGN
Name of student
Name of university
Author’s note:

1
DATA MODELLING & DATABASE DESIGN
Table of Contents
Task 1:........................................................................................................................................3
Task 2:........................................................................................................................................4
Task 3:........................................................................................................................................4
Task 4:........................................................................................................................................5
Task 5:........................................................................................................................................5
Task 6:........................................................................................................................................5
Task 7:........................................................................................................................................5
Task 8:........................................................................................................................................5
Task 9:........................................................................................................................................5
Task 10:......................................................................................................................................5
Task 11:......................................................................................................................................5
Task 12:......................................................................................................................................6
Task 13:......................................................................................................................................6
Task 14:......................................................................................................................................6
Task 15:......................................................................................................................................6
Task 16:......................................................................................................................................6
Task 17:......................................................................................................................................6
Task 18:......................................................................................................................................6
Task 19:......................................................................................................................................6
Task 20:......................................................................................................................................7
Task 21:......................................................................................................................................7
Task 22:......................................................................................................................................7
Task 23:......................................................................................................................................7
Task 24:......................................................................................................................................7
Bibliography.............................................................................................................................10

2
DATA MODELLING & DATABASE DESIGN
Task 1:
CREATE TABLE `department` (
`department_ID` int(11) NOT NULL, PRIMARY KEY (department_id),
`deptName` varchar(30) NOT NULL,
`deptLocation` varchar(20) NOT NULL,
`deptFloor` varchar(30) NOT NULL
);
CREATE TABLE `salary` (
`salary_Level` int(11) NOT NULL, PRIMARY KEY (salary_level),
`salaryMin` int(11) NOT NULL,
`salaryMax` int(11) NOT NULL
) ;
CREATE TABLE `employee` (
`Employee_ID` int(11) NOT NULL, PRIMARY KEY (employee_id),
`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,

3
DATA MODELLING & DATABASE DESIGN
`Department_ID` int(11) NOT NULL,
`DOB` date NOT NULL,
`Address` varchar(30) NOT NULL
) ;
Task 2:
INSERT INTO `employee` (`Employee_ID`, `EmpName`, `ManagerID`,
`DateOfHire`, `JobName`, `Salary`, `department_ID`, `DOB`, `Address`) VALUES ('101',
'Robert', '701', '2018-06-12', 'Budget analyst', '7000.00', '301', '1965-06-12', '62 Weemala
Avenue'), ('102', 'Alejandro L. Braun', '702', '2018-05-09', 'Cash manager', '5000.00', '302',
'1983-05-09', '36 Cubbine Road'), ('103', 'Stacy L. Creech', '703', '2019-11-12', 'Treasurer',
'6000.00', '303', '1967-11-12', '36 Shell Road'), ('104', 'Flynn', '704', '2019-09-12', 'Auditor',
'6500.00', '302', '1948-09-12', '47 Argyle Street'), ('105', 'Jesus H. Hanks', '705', '2019-09-18',
'Credit manager', '7500.00', '305', '1984-09-18', '70 Fergusson Street'), ('106', 'Irma R.
Kephart', '706', '2019-08-14', 'Cash manager', '5000.00', '304', '1964-08-14', '12 Bette McNee
Street'), ('107', 'Laura B. Vasquez', '707', '2019-03-14', 'Controller', '4000.00', '303', '1976-03-
14', '8 Girvan Grove'), ('108', 'Justin K. Reynolds', '708', '2019-11-12', 'Bookkeeper',
'6000.00', '302', '1975-09-12', '47 Savages Road'), ('109', 'Chandra S. Lark', '709', '2020-03-
09', 'manager', '7000.00', '302', '1959-03-09', '18 Boughtman Street'), ('110', 'Kristen M.
Goldstein', '710', '2019-10-18', 'Manager', '5650.00', '301', '1968-10-18', '82 Plantation Place');