Question

This is an individual assignment. Students are referred to the university’s policy on plagiarism. The aim of this assignment is to develop skills for using SQL to create, query and manipulate databases (Task 1) and designing databases using ER modelling techniques (Task 2).

Consider the following employee database:
EMP(empno, ename, job, mgrno, hiredate, sal, comm, deptno)
DEPT(deptno, dname, location)
Employees identified by EMPNO work in departments identified by DEPTNO. Employees
have managers identified by MGRNO. Employees who manage other employees are managers.
Implement the following requests using MariaDB and show both the SQL queries/updates and
query outputs with headings indicating question numbers (2.1 – 2.6). Temporary tables are not
allowed to use to formulate the queries/updates.
2.1 Database Creation and Population
Create the employee database using the above database schema. Populate the tables with data
supplied at the end of this section. Show the table create statements and ensure that primary
and foreign keys are correctly defined.
2.2 Find employees (ENAME) who have the job "SALESMAN" and receive commissions
(COMM). Order by employee name.
2.3 Find employees whose salary (SAL) is higher than their manager’s salary. List name of
both employees (ENAME) and their managers (rename as MNAME).
2.4 For each department, find the employee who earns the highest salary. Show DNAME,
ENAME, and SAL. Order by DNAME.
2.5 Find the department with the largest number of employees. Show DNAME, the number of
employees, and the average salary.
2.6 Update for those employees who work for the departments with DNAME
"CONSULTING" or "MARKETING" by increasing their salaries by 5%. Show all columns of
the EMP table after the update.

Answer 1

In this question, we discuss the correctness of the tables, grouping data differently, and switching between tables.

Step-by-step explanation:

1. Is one of the tables more correct than the other? Why or why not?

In this case, both tables are correct as they represent different entities in the database. The EMP table represents the employees, while the DEPT table represents the departments. They are related through the deptno attribute in the EMP table and the deptno attribute in the DEPT table, which serves as a foreign key. The correctness of the tables depends on their adherence to the defined schema and the data they contain.

2. In general, how could you group the data differently? Are there any advantages to either way of grouping the data?

The data in this scenario is grouped according to the entities being represented - employees and departments. However, you could also group the data differently by considering other attributes such as job title, location, or employee manager. Grouping the data based on different attributes provides different perspectives and insights into the database.

3. Why did you switch between tables, if you did, when answering the question above?

In the case of answering question 2.3, we switch between the EMP and DEPT tables to compare the salary of an employee with their manager's salary. This requires accessing data from both tables to retrieve the necessary information for the comparison. By using joins between the tables, we can combine the relevant attributes and perform the desired comparison.