The distinguished Professor Dumbledore has asked me to assist in designing a database to handle the school. I am respectfully passing his request on to you. Build a database, including forms reports and queries, to handle the H. School Management System, or HSMS in short, described below. Remember all data are inserted, updated, deleted, and retrieved only through these forms, reports, and queries. Hence, these forms, reports, and queries, should handle all the work processes that you think are needed.
HSMS will keep a record of all students, their name, address, telephone, and parents. It should also keep track of the single pet each student brings to school. Pets are only owls, cats, rats, or frogs. A pet must be related to a student, have a name, species, gender, and special powers. Special powers may be of many kinds.
Keep track of the professors, their name, address, telephone, and wizard specialty. Also keep track of which classes each professor teaches and the grades the professor gives each student per class and term.
The students are lodged in one of four houses, with a designated professor in change of each house. But, since some professors, such as Snape, have some problems standing some students, such as Harry, you should keep track of that too.
A celebrated activity in the school is Quidditch, which is a witch game of rugby played on brooms. Each of the four houses has its team. Keep track what students play on what team and how many points they scored each season. Also keep track what broom type each Quiddotch playing student prefers. There are a limited number of broom types. There needs to be at least 20 rows in each table and that all processes are supported by forms and reports.
Deliverables:
1. ER diagram that supports the above case.
- Tables in 3NF
2. Working database that supports the above case.
- Tables matching the ER diagram
- Indexes
- Relationships in Relationship window
- Appropriate queries
- Forms, some must be with subforms and all must have buttons to open related
forms and to print current form
- Reports