Take-Home Assignment 2
Mode: Individual Submission
Deadline: 3rd September
Deliverables: Word file
Instructions
1. This is an individual assignment. Collaboration or group work is not permitted.
2. Ensure clarity, precision, and conciseness in your answers. Proofread your work before
submission.
4. Cite examples where necessary and ensure originality. Plagiarism will result in penalties.
5. Submit your assignment by the deadline on Google Classroom.
Case Background
You are a Database Consultant hired by a growing EdTech startup, EduPro, which manages
online courses, instructors, and student enrollments. The company has been facing
challenges with their database design leading to redundancy, inconsistent data, and
difficulty in querying. They have approached you to help redesign and query their system.
The startup currently stores information in a single table:
Studen StudentN Email Cours CourseN Instruc InstructorE Dept DeptNa HOD
tID ame eID ame tor mail ID me
S1 Aditi aditi@edu. C1 Database Dr. Rao [email protected] D1 CS Prof.
com m Shar
ma
S2 Rahul rahul@edu. C2 AI Basics Dr. mehta@edu D2 AI Prof.
com Mehta .com Singh
S3 Aditi aditi@edu. C2 AI Basics Dr. mehta@edu D2 AI Prof.
com Mehta .com Singh
Your Tasks
Task 1 – Identify Problems
a) Highlight at least two anomalies (insertion, update, deletion) in this design.
b) Suggest why this design leads to redundancy.
Task 2 – Normalization Activity
a) Normalize the table step by step into 1NF → 2NF → 3NF → BCNF. Show the resulting
relations at each step.
b) Identify Primary Keys, Candidate Keys, and Foreign Keys in the final design.
Task 3 – Relational Algebra Application
Using your normalized schema, write Relational Algebra expressions for:
1. Find names of students enrolled in “AI Basics.”
2. List all instructors in the “CS” department.
3. Get unique student names (remove duplicates).
Task 4 – Relational Calculus
Express the following using Tuple Relational Calculus (TRC) and Domain Relational Calculus
(DRC):
1. Retrieve all students with DeptName = “AI.”
2. Find the names of students taught by “Dr. Mehta.”
Task 5 – SQL Implementation
Based on your final schema, write SQL queries for:
1. Find all students enrolled in the “Database” course.
2. List students who are not enrolled in any course (anti-join logic).
3. Retrieve departments where no student has enrolled yet.
4. Insert a new student without assigning a course (show how normalization fixes the
insertion anomaly).
