We have been asked to design a database for Hogwarts School of Witchcraft and Wizardry. We need to store information about students (including id, first and last name, and class year); professors (including id and first and last name); houses (including a unique house name, a mascot, a motto, and a house ghost); courses (including course id, name, description, required textbook, and required equipment); and spells (including the name of the spell, the effect, the category, and the duration). To be a student at Hogwarts, a person must be enrolled in at least one class. When students arrive, a magical sorting hat assigns each student to a house for the duration of the student's stay at Hogwarts (houses can be empty, but seldom are). Empty or not, each house must have exactly one professor serve as head. Professors are very busy so can only head at most one house.

Classes are not taught every semester, but when they are, each class is taught by exactly one professor. (We also know that Professor Snape is teaching three classes this semester; Professor Dumbledore is teaching none.) We need to track the grade a student gets in each course taken.

The students attend Hogwarts to learn about spells in their classes. Spells with the same name have different results depending on which course they are taught in, so each is considered unique. For example, the water making spell taught in Defence Against the Dark Arts is actually different from the water making spell taught in Herbology. Thus, a name does not uniquely identify a spell. We do know that two spells with the same name are never taught in the same class.