Skip to content
Table: EMPTable: DEPT
What it is: A high-level visual model used to understand the structure of the data and its real-world relationships, before choosing a specific database system (DBMS).Key Components:Entities: Nouns or things (e.g., Branch, Employee).Attributes: Properties of the entity (e.g., Name, Annual_salary).Relationships: Associations between entities (e.g., an Employee works for a Branch).Purpose: To clearly define the business rules and data needs with stakeholders who may not be technical.
What it is: A model that represents the database as a collection of two-dimensional tables (relations). It defines the structure of the tables that will be created in a relational database management system (like SQL Server or PostgreSQL).Key Components:Tables: The equivalent of "Entities" from the ER Diagram.Columns: The equivalent of "Attributes" from the ER Diagram.Keys: Primary Keys (PK) to uniquely identify rows, and Foreign Keys (FK) to link tables together and represent the relationships.Purpose: To provide a precise, structured definition for the database developer to use when building and querying the physical database.
SQL, ER Diagram, and Relational Diagram
This is the project I contributed to Academic Assignment for the subject
“Data Management for the Big Data Age”. 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), and Relational Diagram (Task 3)
Task 1: SQL
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).
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.
INSERT INTO EMP(EMPNO,ENAME, JOB, MGRNO, HIREDATE, SAL,COMM,DEPTNO)
VALUES
(7839, 'ALAN','TRAINER', NULL, '2013-11-17', 5000, NULL,10),
(7698, 'LARRY','TRAINER', 7839, '2015-05-01', 3000, NULL,30),
(7782, 'RICHARD','TRAINER', 7839, '2017-06-09', 4200, NULL,10),
(7566, 'DEAN','TRAINER', 7839, '2017-04-02', 3200, NULL,40),
(7654, 'MIKE','SALESMAN', 7698, '2012-09-28', 3300, 3500,30),
(7499, 'JIM', 'SALESMAN', 7698, '2013-02-20', 1600, 1000,30),
(7844, 'JEAN','SALESMAN', 7698, '2013-09-08', 1500, NULL,30),
(7900, 'JAMES','PROGRAMMER', 7698, '2015-12-03', 3500, NULL,30),
(7521, 'JILL', 'SALESMAN', 7698, '2015-02-22', 2300, 500,30),
(7902, 'HARRY','ANALYST', 7566, '2017-12-03', 3000, NULL,40),
(7369, 'ANNE','PROGRAMMER', 7902, '2016-12-17', 1200, NULL,20),
(7788, 'KIM','ANALYST', 7566, '2016-12-09', 3000, NULL,20),
(7876, 'ADAM', 'PROGRAMMER', 7788, '2018-01-12', 3100, NULL,20),
(7934, 'TIM','PROGRAMMER', 7782, '2018-01-23', 4500, NULL,10);
INSERT INTO DEPT
VALUES
(10,'CONSULTING','ADELAIDE'),
(20,'SALES','ADELAIDE'),
(30,'MARKETING','SYDNEY'),
(40,'EDUCATION','MELBOURNE');
2.2 Find employees (ENAME) who have the job “SALESMAN” and receive commissions(COMM).Order by employee name.
SELECT * FROM EMP
WHERE JOB='SALESMAN'
AND COMM IS NOT NULL
ORDER BY ENAME;
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).
SELECT E.ENAME,M.ENAME AS MNAME
FROM EMP AS E
LEFT JOIN EMP AS M ON E.MGRNO = M.EMPNO
where E.SAL > M.SAL
2.4 For each department, find the employee who earns the highest salary. Show DNAME,
ENAME, and SAL. Order by DNAME.
SELECT DEPT.DNAME, EMP.ENAME, MAX(EMP.SAL)
FROM DEPT
LEFT JOIN EMP ON DEPT.DEPTNO=EMP.DEPTNO
GROUP BY DEPT.DNAME
ORDER BY DEPT.DNAME
2.5 Find the department with the largest number of employees. Show DNAME, the number of
employees, and the average salary.
SELECT DEPT.DNAME, COUNT(EMP.EMPNO) AS EMPCOUNT,AVG(EMP.SAL) AS AVGSAL
FROM DEPT
LEFT JOIN EMP ON DEPT.DEPTNO=EMP.DEPTNO
GROUP BY DEPT.DNAME
ORDER BY EMPCOUNT DESC LIMIT 1;
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.
UPDATE EMP
SET EMP.SAL = 1.05 * EMP.SAL
WHERE EMP.DEPTNO IN (
SELECT DEPTNO FROM DEPT
WHERE DEPT.DNAME IN ('CONSULTING','MARKETING'));
SELECT * FROM EMP
Task 2: ER Diagram
The ER Diagram is a conceptual blueprint of a database. It focuses on the real-world view of the data.
Task 3: Relational Diagram
The Relational Diagram (or Relational Schema) is the logical implementation of the ER Diagram. It focuses on how the data is stored in tables.
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ··· in the right corner or using a keyboard shortcut (
CtrlP
) instead.