MIS602: Database Programming Evaluation - Data Modelling Practical

This assignment solution covers practical SQL tasks related to data modelling and database design. It includes creating tables (department, salary, employee), inserting data, and executing various SQL queries to retrieve, update, and delete data. The solution demonstrates SQL commands such as CREATE TABLE, INSERT INTO, SELECT, UPDATE, and DELETE. The assignment also involves writing an experience report summarizing the learning process, including the use of SQL for database management, benefits of using databases, and the challenges encountered. The solution includes a detailed breakdown of the tasks, providing the SQL code for each, and a comprehensive reflection on the learning experience, highlighting the practical application of SQL in database design and management. The assignment also references several academic papers to support the understanding of database management systems.

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

4
DATA MODELLING & DATABASE DESIGN
Task 3:
INSERT INTO `department` (`department_ID`, `deptName`, `deptLocation`,
`deptFloor`) VALUES ('301', 'Marketing', 'NSW', 'Third'), ('302', ' Finance', 'Perth', 'First'),
('303', ' Operations management', 'Sydney', 'Second'), ('304', 'Human Resource', 'Sydney',
'First'), (2011, 'IT', 'Melbourne', 'Second');
Task 4:
INSERT INTO `salary` (`salary_Level`, `salaryMin`, `salaryMax`) VALUES ('3',
'6000.00', '8000.00'), ('2', '5000.00', '4000.00'), ('1', '5000.00', '3000.00'), ('4', '4000.00',
'3000.00'), ('5', '2000.00', '5000.00');
Task 5:
SELECT * FROM `employee`;
Task 6:
SELECT EmpName FROM `employee`
Task 7:
SELECT EmpName, JobName FROM `employee`
Task 8:
SELECT DISTINCT JobName FROM `employee`
Task 9:
UPDATE employee SET Salary = Salary + (salary * 12 / 100)
Task 10:
SELECT EmpName,salary FROM employee

5
DATA MODELLING & DATABASE DESIGN
where salary = (select max(salary) from employee) OR salary = (select min(salary)
from employee);
Task 11:
SELECT Employee_ID, EmpName,Jobname FROM `employee` WHERE (Salary*12
) > 90000
Task 12:
SELECT * FROM `employee` WHERE JobName = "Manager"
Task 13:
SELECT * FROM `employee` WHERE EmpName = "Robert"
Task 14:
SELECT * FROM `employee` WHERE (Salary*12) > 95000 and JobName = "Mana
ger"
Task 15:
SELECT Employee_ID, EmpName,Jobname, DateOfHire FROM `employee` WHER
E YEAR(DateOfHire) > 2001
Task 16:
SELECT EmpName,Employee_ID, Jobname FROM `employee` WHERE (Salary*12
) < 95000 AND (Salary*12) > 55000
Task 17:
SELECT EmpName,Salary FROM `employee` ORDER BY Salary DESC

6
DATA MODELLING & DATABASE DESIGN
Task 18:
SELECT COUNT(EmpName) FROM `employee`
Task 19:
INSERT INTO `employee` (`Employee_ID`, `EmpName`, `ManagerID`,
`DateOfHire`, `JobName`, `Salary`, `department_ID`, `DOB`, `Address`) VALUES ('111',
'Preston M. Collins', '711', '2019-09-19', 'ANALYST', '8000.00', '303', '1986-11-21', '16
Chapman Avenue');
Task 20:
INSERT INTO `employee` (`Employee_ID`, `EmpName`, `ManagerID`,
`DateOfHire`, `JobName`, `Salary`, `department_ID`, `DOB`, `Address`) VALUES ('1011',
'Janet', '5095', '2014-10-12', 'PROGRAMMER', '90000.00', '2011', '1975-09-12', '39 Peterho
Boulevard');
Task 21:
DELETE FROM employee WHERE EmpName = "Flynn"
Task 22:
UPDATE employee SET Salary = Salary + (salary * 15 / 100) WHERE EmpName =
"Robert"
Task 23:
SELECT COUNT department.DeptName,(employee.EmpName),
SUM(employee.Salary)
FROM department
INNER JOIN employee ON department.Department_ID = employee.Department_ID

7
DATA MODELLING & DATABASE DESIGN
GROUP BY department.DeptName;
Task 24:
SELECT * FROM employee WHERE Address LIKE '%Avenue%'
While working on this assignment, I have learnt several aspects of working with the
databases. Before working on this assignment, I studied about SQL and I have learnt that
SQL stands for Structured Query Language and any query language could be considered as
the programming language that has been designed for facilitating the retrieval of the
particular information from various databases and this is the primary task of the SQL. I have
researched that the several companies store all their data in the various databases. The
database management system could be considered as the combination of the various
programs that helps with the management of the database structure as well as the controlling
of access to all the data that has been stored in the database. The DBMS serves as effective
intermediary among the users as well as database. The database structure could be easily
stored as collection of the various files and it allows the accessing of data in all those files
using the DBMS. The DBMS mainly gains the application requests and then executes the
translation of the requests into the complicated operations that are needed for fulfilling the
requests. The DBMS mainly helps with the hiding of the internal complexity from application
programs as well as the users. While forming this database, I have learnt that database
provides various benefits to the users. Some of the common benefits of using the database are
the improved sharing of data, improved security of the data, improved integration of the data,
the reduced inconsistency of the data, improved access to the data. Improved decision
undertaking, and finally the increased end-user productivity. I have realised that even though
the database provides several benefits to the users, there are some aspects that where the

8
DATA MODELLING & DATABASE DESIGN
database systems are not preferred by the users. The main disadvantages of the database
system includes the increased costs, the complexity of management, maintaining the
currency, and the frequency of the upgrading and replacement cycles. The organisations are
presently becoming more concerned regarding what kinds of benefits could be gained from
the use of database system but the introduction of database in any organisation helps with the
development of improved methods of data sharing and data access.

9
DATA MODELLING & DATABASE DESIGN
Bibliography
Yu, X., Xia, Y., Pavlo, A., Sanchez, D., Rudolph, L., & Devadas, S. (2018). Sundial:
Harmonizing concurrency control and caching in a distributed OLTP database
management system. Proceedings of the VLDB Endowment, 11(10), 1289-1302.
Zhang, B., Van Aken, D., Wang, J., Dai, T., Jiang, S., Lao, J., ... & Gordon, G. J. (2018). A
demonstration of the ottertune automatic database management system tuning
service. Proceedings of the VLDB Endowment, 11(12), 1910-1913.
Nidzwetzki, J. K., & Güting, R. H. (2017). Distributed secondo: an extensible and scalable
database management system. Distributed and Parallel Databases, 35(3-4), 197-248.
Arulraj, J. (2017). The Design and Implementation of a Non-Volatile Memory Database
Management System (Doctoral dissertation, Microsoft Research).
Van Aken, D., Pavlo, A., Gordon, G. J., & Zhang, B. (2017, May). Automatic database
management system tuning through large-scale machine learning. In Proceedings of
the 2017 ACM International Conference on Management of Data (pp. 1009-1024).
Arulraj, J., & Pavlo, A. (2017, May). How to build a non-volatile memory database
management system. In Proceedings of the 2017 ACM International Conference on
Management of Data (pp. 1753-1758).
Htet, S. Y. K., & Yee, N. N. (2018, February). Concurrency Control in Distributed Database
Management System (Case Study in Online Shopping System). Sixteenth
International Conferences on Computer Applications (ICCA 2018).

10
DATA MODELLING & DATABASE DESIGN