Database Management System Assignment: Database Programming Evaluation

Question

Task:
Database Management System Assignment Instructions:
You need to create the database tables as per below entities and complete the tasks listed in this instruction.

Entities:

Task 1: Create three tables with relevant keys as suggested in the above diagram

Task 2: Insert record of 10 employees in the employee table

Task 3: Insert record of 5 departments in the department table

Task 4: Insert record of 5 salary levels in the salary table

Task 5: Write a query to display the information about the employees in the employee table

Task 6: Write a query to display the name of all the employees

Task 7: Write a query to display the name of all the employees and their jobname.

Task 8: Write a query in SQL to display the unique jobname for all the employees

Task 9: Write a query to increase the salary for all the employees by 12%. Display the empname, jobname and salary after the increment

Task 10: Write a query to display the employee names with minimum and maximum salary.

Task 11: Write a query to display the employee id, employee name, jobname of all the employees whose salary is greater than 90,000 P.A.

Task 12: write a query to display the all the details of all the employees whose jobname is Manager. (Hint: While entering the records for employee, make sure to add manager as jobname for a few employees.)

Task 13: Write a query to display the all the details of the employee whose name is Robert. (Hint: While entering the records for employee, make sure to add Robert as empname for at least one employee.)

Task 14: Write a query to display all the details of the employee who work as a manager and have salary greater than 95000 P.A.

Task 15: Write a query to display employeeid, employee name, jobname and date of joining of all the employees who joined after year 2001.

Task 16: Write a query to display the list of all the employees whose annual salary is within the range 55000 and 95000.( Hint: make sure to add the salary in this range while entering records in the employee table)

Task 17: Write a query to display the list of all the employees in the descending order of their salaries.

Task 18: Write a query to count the number of employees in the employee table.

Task 19: Insert a new record in the employee table and add ANALYST as their jobname. The other fields can be added as per your choice

Task 20: Insert a new record in the employee table with the following data fields

employee_id= 1011

empname= Janet

jobname= PROGRAMMER

managerid= 5095

dateofhire= 12-10-2014

salary= 90000

department_id=2011

Task 21: Write a query to delete the record of the employee whose name is ‘Flynn’. (Hint: Make sure to add a record with employee name ‘Flynn’ in the beginning.

Task 22: Write a query to update the salary by 15% of the employee whose employee name is ROBERT.

Task 23: Write a query to find the number of staff working in each department and the sum of their salaries.

Task 24: Write a query to find all employees with the string ‘Avenue’ in their address

Answer

Query for Database Management System Assignment Task1
CREATE TABLE `department` (

`departmentid` int(15) NOT NULL,

`deptname` varchar(45) NOT NULL,

`deptlocation` varchar(45) NOT NULL,

`deptfloor` varchar(45) NOT NULL

);

CREATE TABLE `employee` (

`employee_id` int(15) NOT NULL,

`empname` varchar(45) NOT NULL,

`managerid` int(15) NOT NULL,

`dateofhire` date NOT NULL,

`jobname` varchar(45) NOT NULL,

`department_id` int(15) NOT NULL,

`dob` date NOT NULL,

`address` varchar(45) NOT NULL

);

CREATE TABLE `salary` (

`salary_level` int(15) NOT NULL,

`salarymin` int(15) NOT NULL,

`salarymax` int(15) NOT NULL

)

ALTER TABLE `department`

ADD PRIMARY KEY (`departmentid`);

ALTER TABLE `salary`

ADD PRIMARY KEY (`salary_level`);

ALTER TABLE `employee`

ADD CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `department` (`departmentid`);

ALTER TABLE `employee`

ADD PRIMARY KEY (`employee_id`,`department_id`) USING BTREE,

ADD KEY `department_id` (`department_id`);

Query for Task 2
Data insertion in employee table

INSERT INTO `employee` (`employee_id`, `empname`, `managerid`, `dateofhire`, `jobname`, `salary`, `department_id`, `dob`, `address`) VALUES

(1, 'Robert', 2, '2002-07-14', 'Manager', '127440', 102, '1979-06-15', 'Joanas Avenue'),

(2, 'CLARE', 1, '2017-05-13', 'Ux/Ui Designer', '115680', 102, '1980-07-07', 'Hallway Street'),

(142, ' JONAS', 1, '2012-07-13', 'Developer', '64960', 102, '1980-05-11', 'Gallen Streret, 24 Block'),

(163, 'jonathon Wade', 2, '2018-04-13', 'QAA', '60720', 106, '1970-09-09', 'Garreth Street'),

(1254, 'Scarlet Denewr', 1, '2019-01-07', 'Accountant', '57360', 101, '1988-08-18', 'Preletim Avenue'),

(1365, 'Keneth Lijin', 1, '2017-05-11', 'Programmer', '48917', 102, '1980-07-07', 'Kirsten Lane'),

(1452, 'Julius Flynn', 2, '2015-10-14', 'Manager', '87530', 105, '1965-02-03', 'Raffle Street'),

(1478, 'Jismon Tomy', 2, '2018-11-11', 'Project Manager', '109760', 102, '1987-12-22', 'Refrerand Road, Bloc B'),

(1523, 'Edward Tucker', 1, '2000-06-16', 'Ux/Ui Designer', '90720', 104, '1960-03-17', 'Remenant Street'),

(1524, 'Mendes Edison ', 1, '2012-01-12', 'Analyst', '81560', 106, '1989-07-15', 'Kennigton Road');

Query for Task 3
Insertion of 5 departments in the department table

Department table data insertion

INSERT INTO `department` (`departmentid`, `deptname`, `deptlocation`, `deptfloor`) VALUES ('104', 'MARKETING', 'MELBOURNE', 'FIRST'), ('105', 'DEPLOYMENT', 'SYDNEY', 'FIFTH'), ('106', 'PRODUCTION', 'PERTH', 'FIRST'), ('101', 'FINANCE ', 'BRISBANE', 'Ground'), ('102', 'DEVELOPMENT', 'MELBOURNE', 'THIRD')

INSERT INTO `department` (`departmentid`, `deptname`, `deptlocation`, `deptfloor`) VALUES ('2011', 'Programming', 'PERTH', 'Ground');

Query for Task 4
Insertion of Salary table data

INSERT INTO `salary` (`salary_level`, `salarymin`, `salarymax`) VALUES ('1', '12000', '18000'), ('2', '18001', '24000'), ('3', '24500', '29000'), ('4', '29001', '38000'), ('5', '39000', '48000')

Query for Task 5
SELECT * FROM `employee`

Query for Task 6
SELECT empname FROM employee

Query for Task 7
SELECT empname, jobname FROM employee

Query for Task 8
SELECT DISTINCT jobname FROM employee

Query for Task 9
Salary before update

UPDATE employee

SET salary = (salary * 1.12)

After update

Query for Task 10
select empname,salary from employee where salary in((select min(salary) from employee),(select max(salary) from employee) )

Query for Task 11
SELECT empname, employee_id, jobname, salary FROM employee WHERE salary>=90000

Query for Task 12
SELECT * FROM employee WHERE jobname="Manager"

Query for Task 13
SELECT * FROM employee WHERE empname="Robert"

Query for Task 14
SELECT * FROM employee WHERE jobname="Manager" AND salary>=90000

Query for Task 15
SELECT * FROM `employee` WHERE dateofhire>'2001-01-01'

Query for Task 16
SELECT * from employee WHERE Salary BETWEEN 55000 AND 95000

Query for Task 17
SELECT * FROM employee ORDER BY employee.salary

Query for Task 18
SELECT COUNT (*) FROM employee

Query for Task 19
INSERT INTO `employee` (`employee_id`, `empname`, `managerid`, `dateofhire`, `jobname`, `salary`, `department_id`, `dob`, `address`) VALUES(1543, 'Shelby ', 1, '2016-01-12', 'Analyst', '85560', 106, '1979-07-15', 'Kings Square')

Query for Task 20
INSERT INTO `employee` (`employee_id`, `empname`, `managerid`, `dateofhire`, `jobname`, `salary`, `department_id`, `dob`, `address`) VALUES(1011, 'Janet', 1, '2014-10-12', 'Analyst', '90000',2011, '1979-07-15', 'Kings Square')

Query for Task 21
DELETE FROM employee WHERE empname='Flynn'

Query for Task 22
UPDATE employee

SET salary = (salary * 115)/100

WHERE empname= ‘Robert’

Query for Task 23
SELECT department_id, COUNT(*),SUM(salary) FROM employee GROUP BY department_id

Query for Task 24
SELECT * FROM employee WHERE address LIKE '%Avenue%'

While completing this assignment for the database management using SQLI learned the usage of wide variety of database commands that helps in the data retrieval with different complex requirements for the database administrator. For any database management system, the SQL or Structured Query Language is utilized in order to store, manipulate as well as retrieval of datain from the relational database. This is considered as standard language in case of using the Relational Database System such as MySQL, MS ACCESS, SQLL server and so on.

While creating the tables in the databases for every table a primary key field is defined as depicted in the given diagram for this assignment. The primary key in the table is helpful in unique identifies of every record stored in the database table. Therefore it is intended that the Primary keys in the tables must have unique values (such as unique employee id for every employee). Furthermore, it is also advised that the primary key column in any table cannot have NULL values in it. Any table in the database can have one primary key only that may contain one or multiple fields in it. If there are multiple fields inside it then that primary key is denoted as the composite key.

In relational database table the foreign key is one of the important key that is utilized in order to establish relationship among two tables. The foreign keys are also known as the as the referencing key. The foreign Keys can be a single column or it can be combination of multiple columns. Here it needs to be mentioned that the values of the two tables for the same attribute should match Primary Key value in another table. Established foreign key relationship among the tables must match for the value of the Primary Key in the referenced table.

Among the different commands the BETWEEN condition is used in order to find out the employees who falls under certain salary range. This condition allows the database users inorder to easily evaluate whether the values of any column for some tuples/rows are falling within some specified range while both the boundary values are inclusive. The value to be evaluated can be any text, number or date values stored in the database. This command can be utilized along with the INSERT, UPDATE, SELECT as well as DELETE database statements. It returns the details of the database records whenever the expression value of specific columns is within specified value ranges. Another command used is ORDER BY clause. The ORDER BY cause in the relational database helps in sorting fetched data records in ascending/descending orders as per one or more columns specified choice of the database administrator or user. In a default manner the ORDER BYclause is responsible for sorting results of a query data in ascending order depending on the specified column. In order to sort the values in descending order, users need to use DESC keyword to sort the data results and ASC for ascending order.

In order to count the employees for different departments the GROUP BY clause is utilized in the SQL commands/queries. This clause is helpful in order to arrange different identical data or rows in multiple groups along with help of other functions or clauses. Therefore, if the value of some particular column is same in multiple other different tuples/rows then the group by clause is helpful in arranging those similar rows in the group. The GROUP BY command is utilized along with SELECT command from the tables. In SQL there are different other aggregate functions also one of which is COUNT () which is utilized in one of our tasks as listed above. In relational database the aggregate functions are those which help in grouping of multiple rows together depending upon the certain criteria in order to form a single value.

The SQL Server SUM() function is an aggregate function that calculates the sum of all or distinct values in an expression.

The command is utilized in order to count the rows returned from a specific SELECT statement from a table in case of data retrieval. For multiple tasks, we have used the UPDATE command. It is important to be careful while updating any record for some specific table. It is suggested to use the WHERE clause whenever using UPDATE command on a table. Use of the WHERE clause helps in the specifying the tuples/record(s) that will be updated after the query is executed. If the WHERE clause is not used then all the data rows or records stored in the table will be updated according to the specified criterion.

For the last query in order to find the specific word inside the address field the wildcards are used. The wild card character is utilized in order to substitute one or more than one characters in some string values stored in the tables. The Wildcard characters (%, *, ^) are utilized along with LIKE operator. This LIKE operator is utilized in order to search for some specified pattern in that address column.

Bibliography
Boisvert, C., 2019, January. Teaching relational database fundamentals: a lack-of-progress report. In Proceedings of the 3rd Conference on Computing Education Practice (pp. 1-4).

Chitti, P., Murkin, J. and Chitchyan, R., 2019, June. Data management: Relational vs blockchain databases. In International Conference on Advanced Information Systems Engineering (pp. 189-200). Springer, Cham.

de Aguiar, C.Z., de Almeida Falbo, R. and Souza, V.E.S., 2018, October. Ontological Representation of Relational Databases. Database management system assignment In ONTOBRAS (pp. 140-151).

Fouad, T. and Mohamed, B., 2019, March. Model Transformation From Object Relational Database to NoSQL Document Database. In Proceedings of the 2nd International Conference on Networking, Information Systems & Security (pp. 1-5).

Unal, Y. and Oguztuzun, H., 2018, March. Migration of data from relational database to graph database. In Proceedings of the 8th International Conference on Information Systems and Technologies (pp. 1-5).