ASSIGNMENT1:
Given table:
EMPLOYEE(EMPNO, ENAME, SAL, DEPTNO, DNAME, LOC)
🔹 Step 1: Identify Functional Dependencies
EMPNO → ENAME, SAL, DEPTNO
(Employee number uniquely identifies employee details)
DEPTNO → DNAME, LOC
(Department number determines department name and location)
🔹 Step 2: Check Normal Form
✅ 1NF (First Normal Form)
All attributes are atomic ✔️
So, the table is in 1NF
✅ 2NF (Second Normal Form)
Primary key = EMPNO (single attribute)
No partial dependency (since key is not composite) ✔️
So, the table is in 2NF
❌ 3NF (Third Normal Form)
There is a transitive dependency:
o EMPNO → DEPTNO
⇒ EMPNO → DNAME, LOC (transitive)
o DEPTNO → DNAME, LOC
So, the table is NOT in 3NF
🔹 Step 3: Convert to 3NF
To remove transitive dependency, split the table:
1️⃣ EMPLOYEE Table
EMPLOYEE(EMPNO, ENAME, SAL, DEPTNO)
Primary Key: EMPNO
2️⃣ DEPARTMENT Table
DEPARTMENT(DEPTNO, DNAME, LOC)
Primary Key: DEPTNO
ASSIGNMENT2:
Given table:
STUDENT(ROLLNO, NAME, AGE, EXAM, MARKS, GRADE)
🔹 Step 1: Identify Functional Dependencies
ROLLNO → NAME, AGE
(Student details depend on roll number)
(ROLLNO, EXAM) → MARKS
(Marks depend on student and exam)
MARKS → GRADE
(Grade depends on marks)
🔹 Step 2: Identify Key
Candidate Key = (ROLLNO, EXAM)
(Because a student can have multiple exams)
🔹 Step 3: Check Normal Forms
✅ 1NF
All attributes are atomic ✔️
So, the table is in 1NF
❌ 2NF
Partial dependency exists:
o ROLLNO → NAME, AGE
(depends only on part of composite key)
So, the table is NOT in 2NF
❌ 3NF
Transitive dependency:
o MARKS → GRADE
So, also NOT in 3NF
🔹 Step 4: Convert to 2NF
Separate student details:
1️⃣ STUDENT Table
STUDENT(ROLLNO, NAME, AGE)
Primary Key: ROLLNO
2️⃣ RESULT Table
RESULT(ROLLNO, EXAM, MARKS, GRADE)
Primary Key: (ROLLNO, EXAM)
🔹 Step 5: Convert to 3NF
Remove transitive dependency (MARKS → GRADE):
Final Tables:
1️⃣ STUDENT
(ROLLNO, NAME, AGE)
2️⃣ RESULT
(ROLLNO, EXAM, MARKS)
3️⃣ GRADE
(MARKS, GRADE)
ASSIGNMNET3:
Given table:
EMPLOYEE(EMPNO, PROJECT_NO, NO_OF_DAYS, CUSTOMERNAME)
Composite Primary Key: (EMPNO, PROJECT_NO)
🔹 Step 1: Identify Functional Dependencies
(EMPNO, PROJECT_NO) → NO_OF_DAYS
PROJECT_NO → CUSTOMERNAME
(Customer name depends only on project, not on employee)
🔹 Step 2: Identify Problem
❌ Partial Dependency
CUSTOMERNAME depends only on PROJECT_NO (part of composite key), not
the whole key
👉 This violates Second Normal Form (2NF)
🔹 Step 3: Problems in the Table
Because of partial dependency, the table has:
Redundancy: CUSTOMERNAME repeated for same project
Update anomaly: Changing customer name requires multiple updates
Insertion anomaly: Cannot insert project without employee
Deletion anomaly: Deleting last employee removes project info
🔹 Step 4: Solution (Convert to 2NF)
Decompose the table:
1️⃣ EMPLOYEE_PROJECT Table
(EMPNO, PROJECT_NO, NO_OF_DAYS)
Primary Key: (EMPNO, PROJECT_NO)
2️⃣ PROJECT Table
(PROJECT_NO, CUSTOMERNAME)
Primary Key: PROJECT_NO