Scenario
An Honor Student record currently has Student ID, Last Name, First Name and Honor Societies. A student may belong to zero or more societies, and each society may have many students. Maintain one shared society record per society and at most one current membership for a given student/society pair. Historical membership periods are outside this exercise.
Question
How would you replace the Honor Societies list with a relational design that retains which student belongs to which society? Name the entities, explain their links and state how duplicate memberships are prevented. A concise paragraph or a small schema outline is sufficient; SQL is not required.
Simply creating a list of society names does not preserve the student-to-society association. Avoid columns such as Society1, Society2 and Society3.
Award 2 points for keeping one Student record per student, 2 for a shared HonorSociety entity with an identifier and name, 4 for a Membership entity with references to both parents and one row per association, and 2 for enforcing uniqueness of the student/society pair. Total: 10 points. This is a self-assessment; the page displays a model answer and does not grade free text.