Chapter 6, Problem 9RQ

(a)

To determine

Change the requirements specified in question 7 if the student must be assigned an advisor who is in the department in which the student is majoring.

Explanation of Solution

The given DBDL in question 7 is as follows:

$\begin{array}{l}\text{Student}\left(\underset{\_}{\text{StudentNum}}\text{,StudentName,CourseCode,AdvisorNum}\right)\\ \text{AK StudentName}\\ \text{FK CourseCode}\to \text{Course}\\ \text{FK AdvisorNum}\to \text{Advisor}\end{array}$

$\begin{array}{l}\text{Advisor}\left(\underset{\_}{\text{AdvisorNum}}\text{,AdvisorName,DepartmentNum}\right)\\ \text{AK AdvisorName}\\ \text{FK DepartmentNum}\to \text{Department}\end{array}$

$\begin{array}{l}\text{Department}\left(\underset{\_}{\text{DepartmentNum}}\text{,DepartmentName}\right)\\ \text{AK DepartmentName}\end{array}$

$\begin{array}{l}\text{Course}\left(\underset{\_}{\text{CouseCode}}\text{,CourseName,CourseDescription}\right)\\ \text{AK CourseName}\\ \text{SK CourseDescription}\end{array}$

$\begin{array}{l}\text{StudentAdvisor}\left(\underset{\_}{\text{StudentNum}}\text{,}\underset{\_}{\text{AdvisorNum}}\right)\\ \text{FK StudentNum}\to \text{Student}\\ \text{FK AdvisorNum}\to \text{Advisor}\end{array}$

The changes in the DBDL after assigning an advisor who is in the department in which the student is majoring is as follows:

$\text{Student}(\underset{\_}{\text{StudentNum}}\text{,StudentName,CourseCode)}$

$\begin{array}{l}\text{AK StudentName}\\ \text{FK CourseCode}\to \text{Course}\end{array}$

$\begin{array}{l}\text{StudentAdvisorDept }\left(\text{S}\underset{\_}{\text{tudentNum}}\text{, }\underset{\_}{\text{AdvisorNum}}\text{, DepartmentNum}\right)\\ \text{FK}\text{AdvisorNum}\to \text{Advisor}\\ \text{FK}\text{DepartmentNum}\to \text{Department}\end{array}$

$\begin{array}{l}\text{Advisor}\left(\underset{\_}{\text{AdvisorNum}}\text{,AdvisorName,DepartmentNum}\right)\\ \text{AK AdvisorName}\\ \text{FK DepartmentNum}\to \text{Department}\end{array}$

$\begin{array}{l}\text{Department}\left(\underset{\_}{\text{DepartmentNum}}\text{,DepartmentName}\right)\\ \text{AK DepartmentName}\end{array}$

$\begin{array}{l}\text{Course}\left(\underset{\_}{\text{CouseCode}}\text{,CourseName,CourseDescription}\right)\\ \text{AK CourseName}\\ \text{SK CourseDescription}\end{array}$

Since the department from which the student is majoring and the department of the advisor must be the same. Therefore, a new relation studentAdvisordept should be created in which each combination of StudentNum and AdvisorNum will be related to a common department.

(b)

To determine

Change the requirements specified in question 7 if the student’s advisor does not necessarily have to be in the department in which the student is majoring.

Explanation of Solution

The given DBDL in question 7 is as follows:

$\begin{array}{l}\text{Department}\left(\underset{\_}{\text{DepartmentNum}}\text{,DepartmentName}\right)\\ \text{AK DepartmentName}\end{array}$

$\begin{array}{l}\text{Advisor}\left(\underset{\_}{\text{AdvisorNum}}\text{,AdvisorName,DepartmentNum}\right)\\ \text{AK AdvisorName}\\ \text{FK DepartmentNum}\to \text{Department}\end{array}$

$\begin{array}{l}\text{Course}\left(\underset{\_}{\text{CouseCode}}\text{,CourseName,CourseDescription}\right)\\ \text{AK CourseName}\end{array}$

$\begin{array}{l}\text{Student}\left(\underset{\_}{\text{StudentNum}}\text{,StudentName,CourseCode,AdvisorNum,DepartmentNum}\right)\\ \text{AK StudentName}\\ \text{FK CourseCode}\to \text{Course}\\ \text{FK AdvisorNum}\to \text{Advisor}\\ \text{FK DepartmentNum}\to \text{Department}\end{array}$

$\begin{array}{l}\text{StudentAdvisor}\left(\underset{\_}{\text{StudentNum}}\text{,}\underset{\_}{\text{AdvisorNum}}\right)\\ \text{FK StudentNum}\to \text{Student}\\ \text{FK AdvisorNum}\to \text{Advisor}\end{array}$

The changes in the DBDL after assigning an advisor who is not required to be in the department in which the student is majoring is as follows:

$\begin{array}{l}\text{Department}\left(\underset{\_}{\text{DepartmentNum}}\text{,DepartmentName}\right)\\ \text{AK DepartmentName}\end{array}$

$\begin{array}{l}\text{Advisor}\left(\underset{\_}{\text{AdvisorNum}}\text{,AdvisorName,DepartmentNum}\right)\\ \text{AK AdvisorName}\\ \text{FK DepartmentNum}\to \text{Department}\end{array}$

$\begin{array}{l}\text{Course}\left(\underset{\_}{\text{CouseCode}}\text{,CourseName,CourseDescription}\right)\\ \text{AK CourseName}\end{array}$

$\begin{array}{l}\text{Student}(\underset{\_}{\text{StudentNum}}\text{,StudentName,CourseCode,AdvisorNum,}\text{DepartmentNum})\\ \text{AK StudentName}\end{array}$

$\begin{array}{l}\text{FK CourseCode}\to \text{Course}\\ \text{FK AdvisorNum}\to \text{Advisor}\\ \text{FK DepartmentNum}\to \text{Department}\end{array}$

Since the department from which the student is majoring and the department of the advisor need not be the same. So, the DeprtmenttNum in Student table will refer to the DepartmentNum in Department table.