DATA FINAL EXAM - ROBUST 1.
5-DAY STUDY PLAN WITH PRACTICE + EXPLANATIONS
DAY 1 (FULL DAY - 10 TO 12 HOURS)
1. JOINS (2 HOURS)
Concepts Covered:
• INNER JOIN: Fetches records with matching values in both tables.
• LEFT JOIN: Fetches all records from left table and matched from right.
• RIGHT JOIN: All from right table and matched from left.
• FULL OUTER JOIN: Combines LEFT and RIGHT JOIN.
• SELF JOIN: A table joins with itself.
• CROSS JOIN: Cartesian product.
Example Schema:
• Students(student_id, name)
• Courses(course_id, title)
• Enrollments(student_id, course_id)
Practice:
1. List all students with their enrolled course titles.
2. Show students who didn’t enroll in any course.
3. Count number of courses each student is taking.
4. Use RIGHT JOIN to show courses with or without enrolled students.
2. SUBQUERIES & NESTED/CO-RELATED QUERIES (2.5 HOURS)
Concepts Covered:
• Subqueries return intermediate results for the outer query.
• Nested queries: Subquery within WHERE or FROM.
• Correlated queries: Inner query depends on outer row.
Examples:
1. Students scoring above average:
SELECT name FROM Students WHERE marks > (SELECT AVG(marks) FROM Students);
1. Students who took same course as "Ali":
1
SELECT name FROM Enrollments e1 WHERE course_id IN
(SELECT course_id FROM Enrollments e2 WHERE e2.student_id = 'Ali');
1. Correlated Query:
SELECT s1.name FROM Students s1
WHERE marks = (SELECT MAX(s2.marks) FROM Students s2 WHERE s1.course_id =
s2.course_id);
3. FUNCTIONAL DEPENDENCIES + CLOSURE + MINIMAL COVER (3 HOURS)
Concepts:
• Functional Dependency: X -> Y means Y depends on X.
• Closure: X+ gives all attributes determined by X.
• Candidate Key: Minimal attribute set that determines all attributes.
• Minimal Cover:
• RHS should have one attribute.
• Remove redundant FDs.
• Remove redundant attributes from LHS.
Example FD Set: R(A,B,C,D), F = {A->B, B->C, A->D}
• A+ = {A, B, C, D} ✓
• Candidate Key = A
• Minimal Cover: Already minimal.
Practice:
• Find closure for multiple subsets.
• Find candidate keys for schema.
• Reduce FDs to minimal form.
DAY 2 (HALF DAY - 5 TO 6 HOURS)
1. NORMALIZATION & ANOMALIES (2 HOURS)
Types of Anomalies:
• Insertion: Cannot insert data due to missing dependent field.
• Update: Redundancy causes inconsistent updates.
• Deletion: Removing one row causes data loss.
Normal Forms:
• 1NF: No multivalued or composite attributes.
• 2NF: No partial dependency.
2
• 3NF: No transitive dependency.
• BCNF: LHS of every FD must be a superkey.
Example (from slides): FDs = {AB->CDE, D->BE}
• Candidate keys = AB, AD
• D is not superkey → violates BCNF, 2NF
• Highest normal form = 1NF
Practice:
• Normalize schemas to 3NF and BCNF.
• Explain normalization step-by-step.
2. RELATIONAL ALGEBRA (1.5 HOURS)
Operations:
• Selection (σ): Choose rows.
• Projection (π): Choose columns.
• Join (⋈), Rename (ρ), Union (∪), Difference (-), Intersection (∩), Division (÷)
Example:
σ_{marks > 50}(Students)
π_{name}(Students ⋈ Enrollments)
Practice:
• Write RA for given SQL.
• Translate JOINs and subqueries to RA.
3. FINAL COMBO PRACTICE QUIZ (1.5 HOURS)
Q1: SQL Join
List names of students enrolled in more than 2 courses.
Q2: Subquery
Show all courses taken by students who scored above average.
Q3: Functional Dependency
Given R(A,B,C,D), F={A->B, B->C, A->D}, find candidate keys.
Q4: Normalize
3
Normalize a schema with FDs: A->B, B->C, A->D to 3NF.
Q5: Relational Algebra
Select students who have enrolled but not submitted assignment.
END OF PLAN
Let me know if you want this as a printable PDF or want additional visual diagrams (FDs, normalization,
etc.)
