Data Modeling and Database Design Practical Assessment - MIS602

This document presents a comprehensive solution for a data modeling and database design assignment, focusing on practical database programming evaluation. The assignment involves creating three tables: 'employee,' 'department,' and 'salary,' and populating them with sample data. It covers a range of SQL queries, including data insertion, selection, filtering, updating, and deletion. The queries demonstrate how to retrieve employee information, filter by job and salary, calculate aggregates, and perform data modifications. The solution showcases the creation of the database using phpMyAdmin and includes a personal reflection on the learning experience, highlighting the application of database management principles and the practical use of SQL in a business context. The assignment concludes with a bibliography citing relevant database systems textbooks.

Running head: DATA MODELLING AND DATA BASE DESIGN
DATA MODELLING AND DATA BASE DESIGN
Name of the Student:
Name of the University:
Author Note:

1DATA MODELLING AND DATA BASE DESIGN
Task 1: Table Creation
Employee
create table employee (employee_id integer (10) primary key NOT NULL, empname varchar2
(25), managerid integer (10), Dateofhire date, Jobbname varchar2 (25), Salary float (10, 2),
department_id integer (10), DOB date, address varchar2(30), foreign key (department_id)
references department (department_id));

2DATA MODELLING AND DATA BASE DESIGN
Department
create table department (department_id integer (10) primary key NOT NULL, Deptname
varchar2 (30), deptLocation varchar2 (30), deptFloor varchar2 (30));

3DATA MODELLING AND DATA BASE DESIGN
Salary
create table salary (salary_level integer (10) primary key NOT NULL, salarymin integer (10),
salarymax integer (10));

4DATA MODELLING AND DATA BASE DESIGN
Task 2:
insert into employee values (01,'Robert', 1011, '1994-10-20', 'Manager', 85000.00, 2011, '1978-
10-12', '22 Sydney');
insert into employee values (02,'Flynn', 1012, '1998-04-22', 'Programmer', 105000.00, 101,
'1982-02-10', '87 Adelaide');
insert into employee values (03,'Andrew', 1013, '2000-12-20', 'Accountant', 65000.00, 102,
'1974-02-22', '23 Central Avenue');
insert into employee values (04,'Steve', 1014, '2002-05-13', 'Manager', 96000.00, 103, '1989-11-
24', '54 Sydney');
insert into employee values (05,'Ricky', 1015, '1999-08-24', 'Tester', 45000.00, 104, '1988-05-16',
'93 Perth');

5DATA MODELLING AND DATA BASE DESIGN
insert into employee values (06,'Mathew', 1016, '1997-11-20', 'Project lead', 98000.00, 104,
'1982-02-18', '32 Canberra');
insert into employee values (07,'Adam', 1017, '1996-12-10', 'Programmer', 65000.00, 2011,
'1984-08-13', '101 Victoria');
insert into employee values (08,'Lee', 1018, '1998-03-11', 'Tester', 46000.00, 2011, '1979-02-12',
'192 New South Wales');
insert into employee values (09,'Kane', 1019, '1997-11-29', 'Accountant', 66000.00, 102, '1983-
02-24', '223 Perth');
insert into employee values (10,'James', 1020, '1993-03-22', 'Manager', 35000.00, 101, '1981-05-
12', '03 Melbourne');

6DATA MODELLING AND DATA BASE DESIGN
Task 3:
insert into department values (101, 'Manager', 'A2', 'E1');
insert into department values (102, 'IT', 'A5', 'E3');
insert into department values (103, 'Finance', 'A4', 'E7');
insert into department values (104, 'Finance', 'A15', 'E32');

7DATA MODELLING AND DATA BASE DESIGN
insert into department values (2011, 'IT', 'A6', 'E13');
Task 4:
insert into salary values (10, 15000, 25000);

8DATA MODELLING AND DATA BASE DESIGN
insert into salary values (11, 26000, 35000);
insert into salary values (12, 36000, 55000);
insert into salary values (13, 56000, 85000);
insert into salary values (14, 86000, 120000);

9DATA MODELLING AND DATA BASE DESIGN
Task 5:
SELECT * FROM employee;
Task 6:
SELECT empname FROM employee;

10DATA MODELLING AND DATA BASE DESIGN
Task 7:
Select empname, jobname from employee;

11DATA MODELLING AND DATA BASE DESIGN
Task 8:
select DISTINCT (Jobname) from employee;