Academy Prep $($AP$)$

Read the following narrative and create a database design at the logical level using the notation shown in class. Specify all relations, attributes, primary and foreign keys, as well as relationships between relations. All relations must be in third normal form. State all assumptions made. Also include the names of your group members in your document. You may use any tool to create the diagram $($e$.$g$.,$ Visio, draw.io$).$

Narrative

Academy Prep $($hereafter referred to as $$AP$)$ serves children of low$-$income families. It is a program for students who qualify for need$-$based scholarships and who have the ability and desire to succeed in a rigorous academic program. Students are educated until $8$th grade and are prepared for high school and college using basic curriculum as well as music, art, and physical education. AP pairs each graduating $8$th grade student with the high school that best suits the needs of the student and seeks graduate scholarships for that student. AP wants to keep track of their graduates and build a database that stores information about how their students do in their life after AP$.$

Requirements

AP wants to record a student$$s name, student ID$,$ social security number, gender, address, phone number, and current grade level. AP also wants to capture contact information for a student$$s legal guardian$($s$)($name$,$ address, phone number, and relation to student$).$ A legal guardian may be a mother, father, grandparent, or anyone else who has legal custody of the student. In addition, AP would like to know from its list of students who is currently enrolled, who is scheduled to join AP the following year, and who is no longer a part of AP $($either due to graduating or dropping out of the program$).$ Finally, AP wants to store comments about a given student.

Before students graduate from AP$,$ they apply to many different high schools. AP wants to capture the name, city, and state of all high schools a student applies to$.$ A student may be accepted to one or more of these high schools, or may not be accepted to any.

AP wants to capture information about a student$$s enrollment in a high school too. Usually a student enrolls in one high school and graduates from there. However, a student may enroll in multiple high schools if he$/$she drops out of or transfers schools. Therefore, for each enrollment, AP wants to capture the student$$s start date and end date, and reason for the end date $($either due to graduating or dropping out of the program$).$

As students graduate from high school, they may apply to many different colleges. AP wants to capture the name, city, and state of all colleges a student applies to$.$ A student may be accepted to one or more of these colleges, or may not be accepted to any.

The database should also capture information about a student$$s enrollment in a college. Similar to high school, a student may enroll in multiple colleges due to dropping out or transferring. Therefore, for each enrollment, AP wants to capture the student$$s start date and graduation, drop, or transfer date.

AP wants to keep track of their students$$ high school and college entrance exam scores too. Currently, AP wants to record three types of test scores: SAT $($college$),$ ACT $($college$),$ and Secondary School Admission Test $($SSAT$)($high school$).$ A student can take these tests as many times as they want. However, not all students send their test scores to AP$.$ For each test a student takes, AP wants to capture the date of the test and the corresponding score.

AP also wants to record information about volunteer work performed by students. AP expects all students to do volunteer work. However, some students do not do volunteer work at all, while others do more than one instance of volunteer work. There are no specific types of volunteer work. Therefore, AP wants to record a description of the work, start date, end date, the name of the person who assigned the work, and related comments about the work.

Finally, AP wants to keep track of their students$$ extra$-$curricular activities too. Each student may be involved in more than one extra$-$curricular activity. Since there are no predefined categories for these activities, AP wants to record a description of the activity, student$$s start date, end date, and any related comments about the activity.