Project Report: Complete Database Analysis

Database Schema
The database schema used for this project is depicted below:

Database Creation Queries

The following SQL queries were used to create and populate the database tables.

DEPT Table

CREATE TABLE DEPT (

DEPTNO INT PRIMARY KEY,
DNAME VARCHAR(50),
LOC VARCHAR(50)
);

INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES

(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');
EMP Table

CREATE TABLE EMP (

EMPNO INT PRIMARY KEY,
ENAME VARCHAR(50),
JOB VARCHAR(50),
MGR INT,
HIREDATE DATE,
SAL DECIMAL(10, 2),
COMM DECIMAL(10, 2),
DEPTNO INT,
FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES
(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10),
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30),
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30),
(7900, 'JAMES', 'CLERK', 7698, '1981-12-02', 950, NULL, 30),
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20),
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20),
(7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, NULL, 20),
(7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, NULL, 20),
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);

SALGRADE Table

CREATE TABLE SALGRADE (

GRADE INT PRIMARY KEY,
LOSAL DECIMAL(10, 2),
HISAL DECIMAL(10, 2)
);

INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES

(1, 700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999);
Relational Algebra Queries
SQL Queries
Easy (10 Queries)

Retrieve all employees in the 'ACCOUNTING' department.

sql

CopyEdit

SELECT *

FROM EMP E

JOIN DEPT D ON E.DEPTNO = D.DEPTNO

WHERE D.DNAME = 'ACCOUNTING';

List all employees who are 'CLERKS'.

sql

CopyEdit

SELECT ENAME

FROM EMP

WHERE JOB = 'CLERK';

Retrieve all department names.

sql

CopyEdit

SELECT DNAME

FROM DEPT;

List employees hired after '1981-01-01'.

sql

CopyEdit

SELECT ENAME, HIREDATE

FROM EMP
WHERE HIREDATE > '1981-01-01';

Retrieve employee names and job titles.

sql

CopyEdit

SELECT ENAME, JOB

FROM EMP;

Find employees without a commission.

sql

CopyEdit

SELECT ENAME

FROM EMP

WHERE COMM IS NULL;

Retrieve all department locations.

sql

CopyEdit

SELECT LOC

FROM DEPT;

Retrieve employees earning less than 2000.

sql

CopyEdit

SELECT ENAME

FROM EMP

WHERE SAL < 2000;

List employees reporting to manager 7698.

sql

CopyEdit
SELECT ENAME

FROM EMP

WHERE MGR = 7698;

Retrieve department numbers of employees earning more than 3000.

sql

CopyEdit

SELECT DISTINCT DEPTNO

FROM EMP

WHERE SAL > 3000;

Medium (10 Queries)

Retrieve names and salaries of employees earning a commission.

sql

CopyEdit

SELECT ENAME, SAL

FROM EMP

WHERE COMM IS NOT NULL;

List department names and locations.

sql

CopyEdit

SELECT DNAME, LOC

FROM DEPT;

Retrieve employees working in the 'RESEARCH' department.

sql

CopyEdit

SELECT ENAME
FROM EMP E

JOIN DEPT D ON E.DEPTNO = D.DEPTNO

WHERE D.DNAME = 'RESEARCH';

Retrieve employees' names and their managers' names.

sql

CopyEdit

SELECT E1.ENAME AS Employee, E2.ENAME AS Manager

FROM EMP E1

JOIN EMP E2 ON E1.MGR = E2.EMPNO;

Find employees working in 'CHICAGO'.

sql

CopyEdit

SELECT E.ENAME

FROM EMP E

JOIN DEPT D ON E.DEPTNO = D.DEPTNO

WHERE D.LOC = 'CHICAGO';

Retrieve employees earning more than 1500 in the 'SALES' department.

sql

CopyEdit

SELECT E.ENAME

FROM EMP E

JOIN DEPT D ON E.DEPTNO = D.DEPTNO

WHERE D.DNAME = 'SALES' AND E.SAL > 1500;

Find the highest salary in the 'OPERATIONS' department.

sql

CopyEdit
SELECT MAX(SAL) AS HighestSalary

FROM EMP E

JOIN DEPT D ON E.DEPTNO = D.DEPTNO

WHERE D.DNAME = 'OPERATIONS';

Retrieve employees' names and salary grades.

sql

CopyEdit

SELECT E.ENAME, S.GRADE

FROM EMP E

JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL;

Retrieve the department with the lowest-paid employee.

sql

CopyEdit

SELECT D.DNAME, MIN(E.SAL) AS LowestSalary

FROM EMP E

JOIN DEPT D ON E.DEPTNO = D.DEPTNO

GROUP BY D.DNAME

ORDER BY LowestSalary LIMIT 1;

Retrieve details of employees earning salaries in grade 3.

sql

CopyEdit

SELECT E.*

FROM EMP E

JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL

WHERE S.GRADE = 3;
Hard (10 Queries)

Find employees earning above their department's average salary.

sql

CopyEdit

SELECT E.ENAME, E.SAL

FROM EMP E

JOIN (SELECT DEPTNO, AVG(SAL) AS AvgSal FROM EMP GROUP BY DEPTNO) A

ON E.DEPTNO = A.DEPTNO

WHERE E.SAL > A.AvgSal;

List employees whose salaries do not match their department's average.

sql

CopyEdit

SELECT E.ENAME

FROM EMP E

JOIN (SELECT DEPTNO, AVG(SAL) AS AvgSal FROM EMP GROUP BY DEPTNO) A

ON E.DEPTNO = A.DEPTNO

WHERE E.SAL != A.AvgSal;

Retrieve employees who do not work under any manager.

sql

CopyEdit

SELECT ENAME

FROM EMP

WHERE MGR IS NULL;

List employees working in the same job as 'ALLEN'.

sql

CopyEdit
SELECT E2.ENAME

FROM EMP E1

JOIN EMP E2 ON E1.JOB = E2.JOB

WHERE E1.ENAME = 'ALLEN';

Find departments with no employees earning above 3000.

sql

CopyEdit

SELECT D.DNAME

FROM DEPT D

WHERE D.DEPTNO NOT IN (SELECT DEPTNO FROM EMP WHERE SAL > 3000);

Retrieve employees who have been working for more than 10 years.

sql

CopyEdit

SELECT ENAME

FROM EMP

WHERE HIREDATE < DATE_SUB(CURDATE(), INTERVAL 10 YEAR);

Find the employee with the second-highest salary.

sql

CopyEdit

SELECT ENAME

FROM EMP

WHERE SAL = (SELECT MAX(SAL)

FROM EMP

WHERE SAL < (SELECT MAX(SAL) FROM EMP));

Retrieve the total salary of each department.

sql
CopyEdit

SELECT D.DNAME, SUM(E.SAL) AS TotalSalary

FROM EMP E

JOIN DEPT D ON E.DEPTNO = D.DEPTNO

GROUP BY D.DNAME;

List employees and the grades they fall into based on salary.

sql

CopyEdit

SELECT E.ENAME, S.GRADE

FROM EMP E

JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL;

Retrieve employees sharing jobs across departments.

sql

CopyEdit

SELECT DISTINCT E1.ENAME

FROM EMP E1

JOIN EMP E2

ON E1.JOB = E2.JOB AND E1.DEPTNO != E2.DEPTNO;

4o

You said:

Give 10 advance level queries and queries to understand them

ChatGPT said:

Here are 10 advanced-level SQL queries and corresponding simpler queries to help you
understand their structure and logic.

Advanced-Level Queries
Find employees whose salary is greater than the average salary of their department and
who also earn a commission.

sql

CopyEdit

SELECT ENAME, SAL, DEPTNO

FROM EMP

WHERE SAL > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = EMP.DEPTNO)

AND COMM IS NOT NULL;

Understanding Query:

sql

CopyEdit

SELECT AVG(SAL) AS AvgSal, DEPTNO

FROM EMP

GROUP BY DEPTNO;

Retrieve the names of employees whose salaries match the highest salary in their
department.

sql

CopyEdit

SELECT ENAME, SAL, DEPTNO

FROM EMP

WHERE SAL = (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = EMP.DEPTNO);

Understanding Query:

sql

CopyEdit

SELECT MAX(SAL) AS MaxSal, DEPTNO

FROM EMP
GROUP BY DEPTNO;

Find the departments where the total salary expense exceeds 10,000.

sql

CopyEdit

SELECT DNAME, SUM(SAL) AS TotalSalary

FROM EMP E

JOIN DEPT D ON E.DEPTNO = D.DEPTNO

GROUP BY D.DNAME

HAVING SUM(SAL) > 10000;

Understanding Query:

sql

CopyEdit

SELECT DNAME, SUM(SAL) AS TotalSalary

FROM EMP E

JOIN DEPT D ON E.DEPTNO = D.DEPTNO

GROUP BY D.DNAME;

Find employees who earn more than all the employees in the 'SALES' department.

sql

CopyEdit

SELECT ENAME, SAL

FROM EMP

WHERE SAL > ALL (SELECT SAL FROM EMP WHERE DEPTNO = 30);

Understanding Query:

sql
CopyEdit

SELECT SAL

FROM EMP

WHERE DEPTNO = 30;

Retrieve the name of the department with the highest average salary.

sql

CopyEdit

SELECT DNAME

FROM DEPT D

JOIN (SELECT DEPTNO, AVG(SAL) AS AvgSal FROM EMP GROUP BY DEPTNO) A

ON D.DEPTNO = A.DEPTNO

WHERE A.AvgSal = (SELECT MAX(AvgSal) FROM (SELECT AVG(SAL) AS AvgSal FROM EMP
GROUP BY DEPTNO) AS SubQuery);

Understanding Query:

sql

CopyEdit

SELECT DEPTNO, AVG(SAL) AS AvgSal

FROM EMP

GROUP BY DEPTNO;

List employees whose salaries are higher than the salaries of their managers.

sql

CopyEdit

SELECT E1.ENAME AS Employee, E1.SAL AS EmployeeSalary, E2.ENAME AS Manager,

E2.SAL AS ManagerSalary

FROM EMP E1
JOIN EMP E2 ON E1.MGR = E2.EMPNO

WHERE E1.SAL > E2.SAL;

Understanding Query:

sql

CopyEdit

SELECT ENAME, MGR, SAL

FROM EMP;

Retrieve the top 3 highest-paid employees in each department.

sql

CopyEdit

SELECT ENAME, SAL, DEPTNO

FROM (SELECT ENAME, SAL, DEPTNO, ROW_NUMBER() OVER (PARTITION BY DEPTNO

ORDER BY SAL DESC) AS Rank

FROM EMP) Ranked

WHERE Rank <= 3;

Understanding Query:

sql

CopyEdit

SELECT ENAME, SAL, DEPTNO, ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY

SAL DESC) AS Rank

FROM EMP;

Find the total salary paid for each job role in all departments.

sql

CopyEdit

SELECT JOB, DEPTNO, SUM(SAL) AS TotalSalary

FROM EMP

GROUP BY JOB, DEPTNO

ORDER BY DEPTNO, TotalSalary DESC;

Understanding Query:

sql

CopyEdit

SELECT JOB, SUM(SAL) AS TotalSalary

FROM EMP

GROUP BY JOB;

Retrieve the details of employees who were hired earlier than their managers.

sql

CopyEdit

SELECT E1.ENAME AS Employee, E1.HIREDATE AS EmployeeHireDate, E2.ENAME AS

Manager, E2.HIREDATE AS ManagerHireDate

FROM EMP E1

JOIN EMP E2 ON E1.MGR = E2.EMPNO

WHERE E1.HIREDATE < E2.HIREDATE;

Understanding Query:

sql

CopyEdit

SELECT ENAME, HIREDATE, MGR

FROM EMP;

Find the average salary of employees in each salary grade.

sql

CopyEdit
SELECT S.GRADE, AVG(E.SAL) AS AvgSalary

FROM EMP E

JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL

GROUP BY S.GRADE

ORDER BY S.GRADE;

Understanding Query:

sql

CopyEdit

SELECT GRADE, LOSAL, HISAL

FROM SALGRADE;

How These Queries Work

Nested Subqueries: Queries like #1, #2, and #5 use subqueries to calculate intermediate
results, such as averages or maximums, which are then compared in the main query.

Joins: Queries like #6 and #9 use self-joins to compare data between employees and their
managers.

Analytical Functions: Query #7 uses ROW_NUMBER() to rank employees within each

department based on salary.

Aggregations: Queries like #3, #8, and #10 use aggregation functions (SUM, AVG) to group
and summarize data.

Anomalies in the Database

Here are the anomalies specific to the given database (EMP, DEPT, and SALGRADE) for
insertion, deletion, and update operations:

1. Insertion Anomalies

Missing Data:

If a new employee is added to the EMP table with an invalid or missing DEPTNO, the
insertion fails due to the foreign key constraint referencing the DEPT table.

Example: Attempting to insert an employee without specifying their JOB or SAL.

sql

CopyEdit

INSERT INTO EMP (EMPNO, ENAME, JOB, HIREDATE, SAL, DEPTNO)

VALUES (8001, 'NEW_EMP', NULL, '2025-02-07', 1500, 30); -- Will fail

Unnecessary Duplication:

If department details are redundantly stored in the EMP table, any new employee in the
same department would duplicate department data.

Note: This is avoided in the given schema because DEPT is normalized.

Dependency on Other Data:

A new employee cannot be inserted into the EMP table unless their DEPTNO exists in the
DEPT table.

Example: Inserting an employee with DEPTNO = 50 fails if DEPTNO = 50 is not present in

the DEPT table.

sql

CopyEdit

INSERT INTO EMP (EMPNO, ENAME, JOB, HIREDATE, SAL, DEPTNO)

VALUES (8002, 'NEW_EMP', 'CLERK', '2025-02-07', 1200, 50); -- Will fail

Violation of Salary Grade Logic:

If a new employee’s SAL is not within the ranges defined in the SALGRADE table,
inconsistencies arise when joining SALGRADE to determine salary grades.

sql

CopyEdit

INSERT INTO EMP (EMPNO, ENAME, JOB, HIREDATE, SAL, DEPTNO)

VALUES (8003, 'NEW_EMP', 'CLERK', '2025-02-07', 10000, 10); -- Salary doesn't match any
grade

2. Deletion Anomalies

Loss of Referential Integrity:

Deleting a record from the DEPT table could lead to orphaned rows in the EMP table. For
example, if DEPTNO = 30 is deleted, all employees in that department lose their department
association.

sql

CopyEdit

DELETE FROM DEPT WHERE DEPTNO = 30; -- Orphaned employees in EMP

Loss of Historical Data:

Deleting an employee from the EMP table also deletes their commission (COMM) and hire
date (HIREDATE), losing valuable information.

sql

CopyEdit

DELETE FROM EMP WHERE EMPNO = 7654; -- Deletes MARTIN's hire date and commission

Unintentional Cascade Deletion:

If foreign key constraints are set to cascade deletions, deleting a department record in DEPT
could unintentionally delete all employees in that department.

sql

CopyEdit

DELETE FROM DEPT WHERE DEPTNO = 20; -- Deletes JONES, FORD, SMITH, etc.

Deleting Data that Breaks Relationships:

Deleting a manager (MGR) from EMP could leave employees reporting to that manager
without a valid MGR value.

Example: If EMPNO = 7839 (KING) is deleted, employees reporting to him lose their
manager reference.

sql

CopyEdit

DELETE FROM EMP WHERE EMPNO = 7839; -- Orphaned managers for CLARK, BLAKE,
JONES

3. Update Anomalies
Inconsistent Updates:

If an employee’s DEPTNO is updated in EMP without updating the corresponding

department information in DEPT, data inconsistencies arise.

sql

CopyEdit

UPDATE EMP

SET DEPTNO = 50

WHERE EMPNO = 7698; -- Department 50 doesn't exist

Violation of Referential Integrity:

Updating a department number in DEPT without updating the related rows in EMP creates
mismatches.

Example: Updating DEPTNO = 30 in DEPT to 50 would break references for employees in

the EMP table with DEPTNO = 30.

sql

CopyEdit

UPDATE DEPT

SET DEPTNO = 50

WHERE DEPTNO = 30; -- Breaks EMP.DEPTNO reference

Incorrect Salary Range:

Updating an employee’s salary to a value outside the SALGRADE range causes

inconsistencies when determining the employee’s grade.

sql

CopyEdit

UPDATE EMP

SET SAL = 10000

WHERE EMPNO = 7566; -- Salary exceeds SALGRADE range

Circular Manager Updates:

Updating the MGR field to create a circular reporting hierarchy leads to logical errors.
Example: Setting MGR = 7698 (BLAKE) to report to 7654 (MARTIN), who already reports to
7698.

sql

CopyEdit

UPDATE EMP

SET MGR = 7654

WHERE EMPNO = 7698; -- Creates a circular dependency

Partial Updates in Denormalized Data:

If the schema is denormalized (e.g., storing department names in EMP), updating one
instance of department data leads to inconsistencies unless all instances are updated.

Note: This issue is avoided in the given normalized schema.

Key Takeaways

Insertion Anomalies: Occur due to missing or invalid data, constraint violations, and
dependency issues.

Deletion Anomalies: Lead to loss of data, orphaned records, or cascading unintended

deletions.

Update Anomalies: Result in inconsistencies, referential integrity violations, and logical

errors.

To prevent these anomalies:

Use a normalized schema.

Enforce referential integrity constraints.

Design triggers to handle cascading changes where necessary.

Normalization Process
To normalize the given database schema (EMP, DEPT, SALGRADE) up to BCNF and ensure it
satisfies 3NF, let us go step-by-step.

1. Unnormalized Form (UNF)

The unnormalized form contains redundant data, partial dependencies, or multi-valued
attributes. However, the given database already avoids these issues as it is designed in
tabular format with atomic attributes.

We can begin from 1NF.

2. First Normal Form (1NF)

Definition: A table is in 1NF if all columns contain atomic (indivisible) values and each
record is unique.

Status of Given Schema:

 All columns in EMP, DEPT, and SALGRADE tables contain atomic values (e.g.,
ENAME is a single string, SAL is a single value).

 Primary keys (EMPNO in EMP, DEPTNO in DEPT, GRADE in SALGRADE) ensure

record uniqueness.

The schema satisfies 1NF.

3. Second Normal Form (2NF)

Definition: A table is in 2NF if:

1. It is in 1NF.

2. It does not have partial dependency (no non-prime attribute depends on a part of
the composite key).

Status of Given Schema:

 EMP: The primary key is EMPNO. All non-key attributes (ENAME, JOB, MGR,
HIREDATE, SAL, COMM, DEPTNO) depend entirely on EMPNO.
No partial dependency exists.

 DEPT: The primary key is DEPTNO. Attributes (DNAME, LOC) depend entirely on
DEPTNO.
No partial dependency exists.

 SALGRADE: The primary key is GRADE. Attributes (LOSAL, HISAL) depend entirely
on GRADE.
No partial dependency exists.

The schema satisfies 2NF.

4. Third Normal Form (3NF)

Definition: A table is in 3NF if:

1. It is in 2NF.

2. No transitive dependency exists (non-prime attributes do not depend on other non-

prime attributes).

Checking for Transitive Dependency:

 EMP:

o DEPTNO is a foreign key to DEPT(DEPTNO).

 DNAME and LOC in DEPT indirectly depend on EMP.EMPNO via

DEPTNO.
Solution: No changes required since DEPTNO is a foreign key and the
schema is normalized.

 DEPT:

o No transitive dependencies exist.

 SALGRADE:

o No transitive dependencies exist.

The schema satisfies 3NF.

5. Boyce-Codd Normal Form (BCNF)

Definition: A table is in BCNF if:

1. It is in 3NF.

2. For every functional dependency (FD), the determinant is a superkey.

Checking Functional Dependencies:

 EMP:

o Primary key: EMPNO.

 All non-prime attributes depend on EMPNO.

 No violations of BCNF exist.

  DEPT:

o Primary key: DEPTNO.

 All non-prime attributes depend on DEPTNO.

 No violations of BCNF exist.

 SALGRADE:

o Primary key: GRADE.

 All non-prime attributes depend on GRADE.

 No violations of BCNF exist.

The schema satisfies BCNF.

Summary of Normal Forms

Table 1NF 2NF 3NF BCNF

EMP Yes Yes Yes Yes

DEPT Yes Yes Yes Yes

SALGRADE Yes Yes Yes Yes

The given database schema is fully normalized up to BCNF.

4o

You said:

Consider some example and create anamolies and then apply normal form give 10 for each
examples for above database

ChatGPT said:

To create anomalies in the database and resolve them using normal forms, we will consider
some examples of insertion, deletion, and update anomalies. These anomalies will
demonstrate how normalization (up to BCNF) resolves the issues.

1. Insertion Anomalies

Anomalies that occur when adding new data to the database.

Example Anomalies:

1. Adding a new department without any employees.

o Issue: If department information is only stored in the EMP table, adding a

department without an employee is impossible.
Solution: Normalize the schema to have a separate DEPT table.

2. Adding an employee with an invalid department.

o Issue: If DEPTNO is not in DEPT, the foreign key constraint is violated.

Solution: Normalize by ensuring DEPTNO exists in DEPT.

3. Adding a salary outside the SALGRADE range.

o Issue: No salary grade can be assigned for out-of-range salaries.

Solution: Use the SALGRADE table for validation.

4. Adding an employee without a manager (if required).

o Issue: MGR references EMPNO, and the manager must exist.

Solution: Ensure managers are entered before their subordinates.

5. Adding duplicate department data.

o Issue: Repeated department information in EMP leads to redundancy.

Solution: Normalize into DEPT.

6. Adding an employee with incomplete data (e.g., no salary or job).

o Issue: Constraints like NOT NULL may fail.

Solution: Ensure all required attributes are defined.

7. Adding an employee with a duplicate EMPNO.

o Issue: Violates the primary key constraint.

Solution: Enforce unique keys.

8. Adding an employee with inconsistent department data.

o Issue: Department in EMP doesn’t match the department in DEPT.

Solution: Use foreign key constraints.

9. Adding a salary grade that overlaps with an existing range.

o Issue: Overlapping ranges create conflicts in SALGRADE.

Solution: Normalize SALGRADE with proper range constraints.

10. Adding duplicate employee records.

  Issue: Leads to redundancy.
Solution: Enforce unique constraints.

2. Deletion Anomalies

Anomalies that occur when removing data from the database.

Example Anomalies:

1. Deleting the last employee of a department removes the department itself.

o Solution: Normalize DEPT to store department details independently.

2. Deleting a department removes all employees in that department.

o Solution: Use cascading or restrict constraints on foreign keys.

3. Deleting a manager (MGR) leaves employees without a valid reference.

o Solution: Ensure cascading deletion or update MGR references.

4. Deleting an employee removes their commission (COMM).

o Solution: Normalize EMP to store commissions separately.

5. Deleting an employee with a specific salary grade leaves that grade unused.

o Solution: Ensure salary grade data is not dependent on employees.

6. Deleting a department with an employee causes orphaned employee records.

o Solution: Use proper foreign key constraints.

7. Deleting an employee without updating reports to their MGR creates

inconsistencies.

o Solution: Update dependent records.

8. Deleting an employee record might remove historical data, such as HIREDATE.

o Solution: Store historical data in separate tables.

9. Deleting a department name (DNAME) stored redundantly in EMP removes it

entirely.

o Solution: Normalize into DEPT.

10. Deleting a salary grade range causes inconsistencies in EMP.

  Solution: Ensure SALGRADE table is independent.

3. Update Anomalies

Anomalies that occur when modifying existing data.

Example Anomalies:

1. Updating a department name in one record but not others leads to inconsistencies.

o Solution: Normalize DEPT.

2. Updating an employee’s DEPTNO to an invalid value breaks references.

o Solution: Use foreign key constraints.

3. Updating a manager creates circular references (e.g., manager reporting to

themselves).

o Solution: Validate relationships before updates.

4. Updating a salary to an out-of-range value causes mismatched salary grades.

o Solution: Enforce constraints from SALGRADE.

5. Updating a department's location without updating all records causes

inconsistencies.

o Solution: Normalize DEPT.

6. Updating an employee's MGR without updating their subordinates leads to

inconsistencies.

o Solution: Cascade updates.

7. Updating the SALGRADE ranges to overlap creates conflicts.

o Solution: Ensure non-overlapping ranges.

8. Updating a record with duplicate values (e.g., same EMPNO) creates redundancies.

o Solution: Enforce unique constraints.

9. Updating COMM inconsistently across EMP records leads to errors.

o Solution: Store commissions in a separate table.

10. Updating a department to an invalid DEPTNO causes mismatched data.

  Solution: Use referential integrity constraints.

Applying Normal Forms to Resolve Anomalies

1. 1NF:

o Ensure all columns contain atomic values.

o Remove multi-valued or composite attributes.

2. 2NF:

o Remove partial dependencies (ensure all non-key attributes depend on the

whole primary key).

3. 3NF:

o Remove transitive dependencies (non-key attributes should not depend on

other non-key attributes).

4. BCNF:

o Ensure every determinant is a superkey to eliminate any remaining

redundancy.

Example Resolutions:

1. Separate DEPT into its own table to avoid redundancy and inconsistencies.

sql

CopyEdit

CREATE TABLE DEPT (

DEPTNO INT PRIMARY KEY,

DNAME VARCHAR(50),

LOC VARCHAR(50)

);

2. Create SALGRADE with non-overlapping ranges to avoid salary conflicts.

sql
CopyEdit

CREATE TABLE SALGRADE (

GRADE INT PRIMARY KEY,

LOSAL DECIMAL(10, 2),

HISAL DECIMAL(10, 2)

);

3. Use foreign keys to maintain referential integrity between EMP and DEPT.

sql

CopyEdit

ALTER TABLE EMP ADD CONSTRAINT FK_DEPT FOREIGN KEY (DEPTNO) REFERENCES
DEPT(DEPTNO);

By applying normal forms up to BCNF, all anomalies can be resolved effectively.