We have been asked to design a database for Hogwarts School of Witchcraft and Wi

Question

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.

Explanation / Answer

Database Name: Hogwarts

Table Name: Student

Column Name:
Student_ID
FNAME
LNAME
Class_Year

Table Name: Professors

Column Name:
Proffesor_ID
FNAME
LNAME
House_Name

Table Name: Houses

Column Name:
House_Name
Mascot
Motto
House Ghost
Head_Professor

Table Name: Courses

Column Name:
Course_ID
Course_Name
Course_Desc
Book_Name
Equipment_Name
Semester_Number (foreign key referenced in Class)

Table Name: Spells

Column Name:
Spell_Name
Effect
Category
Duration

Table Name: Class

Column Name:
Semester_Number
Professor_Name
Spell_Name

Relation in Tables:

1. One House head by One Professor (one to one)
2. One House can have many Students (one to many)
3. One Spell in One Class (One to One)
4. One Professor can teach more than one course (one to many)
5. Many spell can be used in one Course (many to one)
6. Any number of courses can have any number of Students (many to many)

Unique Key:

Spell_Name + Course_Name

Foreign Key:

Semester_Number

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.