CS405 Database Programming using Oracle 11g starts with data modeling before any SQL. Assignment 1 for Fall 2026 carries 20 marks and the due date is 4 November 2026, and it has two questions. Question 1 asks for an entity relationship diagram showing all entities and their key attributes, proper relationships with cardinality and optionality including the specialization matching constraint, and at least two many-to-many relationships resolved with associative tables. Question 2 asks for a SQL query based on that ERD.
The solution file linked below covers both questions in full. It contains the complete ERD with five entities, the relationship and cardinality details, both many-to-many resolutions, the specialization constraint, and the SQL query with an explanation of how it works.
What Does the Solution File Cover?
The file covers the course enrollment design end to end. The entities are Learner, Course, Instructor, and two associative tables, Enrollment and CourseAssignment. Every entity lists its key attributes, every relationship states its cardinality, and the diagram shows how the pieces connect.
What Are the Entities and Key Attributes?
Learner has Learner_ID as its primary key. Course has Course_ID as its primary key, plus Course_Title and Required_Specialization. Instructor has Instructor_ID as its primary key, plus Instructor_Name and Specialization. Enrollment is the associative table between Learner and Course, with the composite primary key Learner_ID plus Course_ID. CourseAssignment is the associative table between Instructor and Course, with the composite primary key Instructor_ID plus Course_ID.
How Are the Many-to-Many Relationships Resolved?
First, Learner and Course have a many-to-many relationship: one learner can enroll in many courses, and one course can have many learners. It is resolved by Enrollment, which turns the many-to-many link into two one-to-many links. Second, Instructor and Course have a many-to-many relationship: one instructor can teach many courses, and one course can be assigned to many instructors. It is resolved by CourseAssignment in the same way. The specialization constraint is also enforced: a course is assigned only to instructors whose Specialization matches the course Required_Specialization.
What Does the SQL Query Do?
The query lists the course title and the number of learners enrolled in each course, showing only courses with more than 2 enrolled learners, sorted by enrollment count in descending order. It joins Course to Enrollment on Course_ID, groups the rows per course, counts distinct learners with COUNT(DISTINCT Learner_ID), filters with HAVING COUNT(DISTINCT Learner_ID) greater than 2, and sorts with ORDER BY Total_Learners DESC.
How Do You Download the Solution File?
You can download the complete solution file from Google Drive using the link below. Study the diagram first, then the tables, then the query, and make sure you can explain each part before submitting. Also check your work against the exact assignment file on VULMS, since question wording can vary.
How Should You Use This Solution?
A solution file helps most when you use it to learn, not just to submit. First, redraw the ERD yourself from the entity list and check it against the diagram. Then explain why each many-to-many link needs an associative table, and why the specialization constraint matters for course assignments. Finally, trace the SQL query step by step: join, group, count, filter, sort. Also check your work against the exact assignment file on VULMS, since question wording can vary.
- Redraw the ERD yourself and compare it with the diagram.
- Explain each associative table and its composite key.
- State the specialization constraint in your own words.
- Trace the SQL query: join, group, count, filter, sort.
- Check every part against your own question file on VULMS.
For more study material, you can download course handouts from the VU handouts page and compact revision notes from the VU short notes page. Past papers are also useful for ERD and SQL practice, and you can find them on the VU midterm past papers page.
Frequently Asked Questions
Is this the complete solution for CS405 Assignment 1 Fall 2026?
Yes. The file covers both questions: the complete ERD with entities, cardinality, the specialization constraint, and two many-to-many resolutions, plus the SQL query. The assignment carries 20 marks and the due date is 4 November 2026.
Why does a many-to-many relationship need an associative table?
Because a relational database cannot store a direct many-to-many link. The associative table breaks it into two one-to-many links and holds the pair of foreign keys as its composite primary key, which is exactly what Enrollment and CourseAssignment do here.
What does the specialization constraint mean in practice?
It means a course can only be assigned to an instructor whose Specialization matches the course Required_Specialization. An assignment that breaks this rule is invalid, even if the tables would otherwise accept it.
Last updated: October 2026.
