Data Exam Pop Quiz

Data Exam Pop Quiz

This quiz covers key topics from your study plan, including JOINS, Subqueries, Functional
Dependencies, Normalization, and Relational Algebra. Use the schema provided for SQL
questions and show your work where applicable.

Schema for SQL Questions

• Students(student_id, name)

• Courses(course_id, title)

• Enrollments(student_id, course_id, grade)

Section 1: JOINS
1. Fill in the Blank: A __________ JOIN returns all records from the right table
and the matched records from the left table.

2. Short Answer: Briefly explain the difference between a FULL OUTER JOIN and a
CROSS JOIN.

3. SQL Query: Write a SQL query to list the names of all students and the titles of the
courses they are enrolled in. Students who are not enrolled in any course should not
appear in the result.

4. SQL Query: Write a SQL query to find the names of all students who have not
enrolled in any courses.

5. SQL Query: Write a SQL query to show every course title and the number of students
enrolled in each course. Courses with zero students should be included in the result
with a count of 0.

1
Section 2: Subqueries & Correlated Queries
6. Multiple Choice: What is the primary characteristic of a correlated subquery?
a) The inner query runs only once and provides a list of values to the outer query.
b) The inner query is executed repeatedly, once for each row processed by the outer
query.
c) It can only be used in the FROM clause of a SQL statement.
d) It is another name for an INNER JOIN.
7. SQL Query: Write a query to find the names of all students who have a grade of ’A’
in the ’Data Systems’ course. Use a subquery in your answer.
8. SQL Query: Write a query to select the names of students who are enrolled in at
least one of the same courses as the student with student_id = 101. Do not include
student 101 in the result.
9. SQL Query: Using a correlated subquery, find the name of the student with the
highest grade in each course.

Section 3: Functional Dependencies (FDs), Closure, &

Minimal Cover
10. Short Answer: In the context of functional dependencies, what is a candidate key?
11. Problem-Solving: Consider a relation with schema R(A, B, C, D, E) and the follow-
ing set of functional dependencies F :
F = {A → B, BC → D, E → A, D → E}
a) Calculate the closure of (A, C), denoted as (AC)+ .
b) Find all candidate keys for the relation R.
12. Problem-Solving: Find the minimal cover for the following set of functional depen-
dencies G:
G = {A → BC, B → C, A → B, AB → C, AC → D}

Section 4: Normalization & Anomalies

Anomaly Type Description
1. Insertion A. Changing data in o
to other rows because
1. Deletion B. The loss of unrelate
13. Matching: Match the anomaly type with its description.
record is removed.
1. Update C. A record cannot be
tain field, not part of
yet known.

2
 14. Short Answer: What is the difference between a partial dependency and a transitive
dependency? Your answer should reference 2NF and 3NF.

15. Problem-Solving: Consider a relation with schema R(A, B, C, D, E) and FDs:

F = {A → B, BC → D, D → E}

a) The only candidate key is (A, C). Determine the highest normal form (1NF, 2NF,
3NF, or BCNF) that this relation satisfies.
b) Justify your answer by explaining which, if any, normal forms are violated.

Section 5: Relational Algebra

16. Translate: Write the relational algebra expression for the following SQL query:

SELECT name, title

FROM Students s
INNER JOIN Enrollments e ON s.student_id = e.student_id
INNER JOIN Courses c ON e.course_id = c.course_id
WHERE e.grade = 'A';

17. Translate: Write the relational algebra expression for the following request: “Find
the names of all students who are enrolled in course course_id = 'CS101' but not in
course course_id = 'EE201'.”

Answer Key
1. RIGHT JOIN
Reason: A RIGHT JOIN ensures all records from the right table are included, with
matched records from the left table or NULL where no match exists.

2. A FULL OUTER JOIN combines the results of both a LEFT JOIN and a RIGHT JOIN,
returning all records from both tables, with NULLs in places where the join condition
is not met. A CROSS JOIN produces the Cartesian product of the two tables, pairing
every row from the first table with every row from the second table, without any
condition.
Reason: The FULL OUTER JOIN preserves all rows from both tables, while a CROSS
JOIN generates all possible row combinations, regardless of any matching condition.

3. SELECT s.name, c.title

FROM Students s
INNER JOIN Enrollments e ON s.student_id = e.student_id
INNER JOIN Courses c ON e.course_id = c.course_id;

3
 Reason: The INNER JOIN ensures only students enrolled in courses are included,
matching student_id and course_id across tables.

4. SELECT s.name
FROM Students s
LEFT JOIN Enrollments e ON s.student_id = e.student_id
WHERE e.student_id IS NULL;

Reason: A LEFT JOIN includes all students, and the WHERE e.studenti dISN U LLf iltersf orstudent

5. SELECT c.title, COUNT(e.student_id) AS number_of_students

FROM Courses c
LEFT JOIN Enrollments e ON c.course_id = e.course_id
GROUP BY c.course_id, c.title;

FocalPoint: The LEFT JOIN ensures all courses are included, even those with no en-
rollments, and COUNT aggregates the number of students per course.

6. b) The inner query is executed repeatedly, once for each row processed by the outer
query.
Reason: A correlated subquery depends on values from the outer query, requiring
repeated execution for each outer row.

7. SELECT name
FROM Students
WHERE student_id IN (
SELECT student_id
FROM Enrollments
WHERE grade = 'A' AND course_id = (
SELECT course_id
FROM Courses
WHERE title = 'Data Systems'
)
);

Reason: The subquery identifies the course_id for ’Data Systems’ and filters enroll-
ments with grade ’A’, then matches students by student_id.

8. SELECT DISTINCT s.name

FROM Students s
JOIN Enrollments e1 ON s.student_id = e1.student_id
WHERE e1.course_id IN (
SELECT e2.course_id
FROM Enrollments e2
WHERE e2.student_id = 101

4
 ) AND s.student_id != 101;

Reason: The subquery finds courses for student_id = 101, and the outer query
selects distinct students enrolled in those courses, excluding 101.

9. SELECT s.name, c.title

FROM Students s, Courses c, Enrollments e1
WHERE s.student_id = e1.student_id
AND c.course_id = e1.course_id
AND e1.grade = (
SELECT MAX(e2.grade)
FROM Enrollments e2
WHERE e2.course_id = e1.course_id
);

Reason: The correlated subquery finds the maximum grade for each course, and the
outer query matches students and courses with that grade.

10. A candidate key is a minimal set of attributes in a relation that can uniquely determine
all other attributes in that relation.
Reason: It is minimal because no proper subset of the candidate key can uniquely
identify all attributes.

11. a) (AC)+ = {A, B, C, D, E}

Reason: Start with (AC)+ = {A, C}. Apply A → B to add B, then BC → D
to add D, and D → E to add E.
b) Candidate keys: (AC), (CE)
Reason: Both (AC) and (CE) determine all attributes (A → B, BC → D,
D → E for AC; E → A, A → B, BC → D for CE), and no proper subset is
sufficient.

12. Minimal cover: {A → B, B → C, A → D}

Reason: Decompose A → BC into A → B, A → C. Remove redundant FDs: A → C
(via A → B, B → C), AB → C (via B → C), AC → D (simplify to A → D).

13. 1-C, 2-B, 3-A

Reason: Insertion anomaly (C) occurs when data cannot be added due to missing
non-key attributes. Deletion anomaly (B) loses unrelated data. Update anomaly (A)
requires multiple row updates due to redundancy.

14. A partial dependency exists when a non-prime attribute depends on only part of a
multi-attribute candidate key, violating 2NF. A transitive dependency exists when
a non-prime attribute depends on another non-prime attribute, violating 3NF.
Reason: 2NF requires non-prime attributes to depend fully on the candidate key,
while 3NF eliminates dependencies between non-prime attributes.

5
15. a) Highest normal form: 1NF
Reason: The relation satisfies 1NF (atomic attributes). It violates 2NF due
to partial dependency A → B (B depends on part of the candidate key AC).
It violates 3NF due to transitive dependency A → D → E. It violates BCNF
because A → B and D → E have non-superkey determinants.
b) See above.

16.
πname,title (σgrade=′ A′ (Students ▷◁ Enrollments ▷◁ Courses))

Reason: The joins combine the tables, σgrade=′ A′ filters for grade ’A’, and πname,title
projects the required attributes.

17.

πname (σcourse_id=′ CS101′ (Students ▷◁ Enrollments))−πname (σcourse_id=′ EE201′ (Students ▷◁ Enrollmen

Reason: Select students enrolled in ’CS101’ using join and selection, subtract those
enrolled in ’EE201’, and project the names.