Project $6$ Q$7.$

Styles

The School database $($with $4$ entities and $2$ relationships$)$ is described and defined as follows. For Student entity, each student ID has last name, first name, GPA, his$/$her major Dept ID$,$ and his$/$her Advising Professor ID$.$

For Dept entity, each dept ID has dept name, description, and its chair Professor ID$.$

For Professor entity, each prof ID has last name, first name, and his$/$her Dept ID$.$

For Course entity, each Course ID has course name, description, and number of units.

For TAKE relationship, a course can be taken by many students with their grades assigned, and a student can take many courses with their grades assigned as well accordingly.

For TEACH relationship, a course can be taught by many professors with specific years and semesters, and a professor can teach many courses with specific years and semesters.

$($B$)$ Please identify and show all one$-$to$-$many and many$-$to$-$many relationships among those four entities in this database.

Note: Each foreign key $($FK$)$ will cause a one$-$to$-$many relationship. A primary key $($PK$)$ of two columns in a table will cause a many$-$to$-$many relationship between those two entities identified by those two primary keys $($PK$)$ columns.

One$-$to$-$Many Relationships:

R$1$: A professor may advise many students. $($due to FK$1)$

R$2$: A dept may have many students majoring in this dept. $($due to FK$2)$

R$3$: A dept may have many professors. $($due to FK$3)$

R$4$: A professor may be the chair of many departments. $($due to FK$4)$

Many$-$to$-$Many Relationships: $($Note: MDs must come in pairs.$)$

Hint: A table does not have MDs if there is no many$-$to$-$many relationship in this table.

A pair of MDs exists in a table if there is a many$-$to$-$many relationship in this table.

A pair of MDs exists in a table, whose primary key $($PK$)$ has $2$ id fields.

$MD1$: StudentID $$ CourseID

$MD2$: ProfessorID $$ CourseID

$MD3$: StudentID $$ ProfessorID

$MD$ DeptID $$ ProfessorID