CONCEPTS OF DATABASE MANAGEMENT
CONCEPTS OF DATABASE MANAGEMENT
9th Edition
ISBN: 9780357261354
Author: Pratt
Publisher: CENGAGE C
Question
Book Icon
Chapter 6, Problem 8RQ

(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.

(a)

Expert Solution
Check Mark

Explanation of Solution

The given DBDL in question 7 is as follows:

Student(StudentNum_,StudentName,CourseCode,AdvisorNum)AK StudentNameFK CourseCodeCourseFK AdvisorNumAdvisor

Advisor(AdvisorNum_,AdvisorName,DepartmentNum)AK AdvisorNameFK DepartmentNumDepartment

Department(DepartmentNum_,DepartmentName)AK DepartmentName

Course(CouseCode_,CourseName,CourseDescription)AK CourseNameSK CourseDescription

StudentAdvisor(StudentNum_,AdvisorNum_)FK StudentNumStudentFK AdvisorNumAdvisor

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

Student(StudentNum_,StudentName,CourseCode)

AK StudentNameFK CourseCodeCourse

StudentAdvisorDept (StudentNum_AdvisorNum_, DepartmentNum)FKAdvisorNumAdvisorFKDepartmentNumDepartment

Advisor(AdvisorNum_,AdvisorName,DepartmentNum)AK AdvisorNameFK DepartmentNumDepartment

Department(DepartmentNum_,DepartmentName)AK DepartmentName

Course(CouseCode_,CourseName,CourseDescription)AK CourseNameSK CourseDescription

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.

(b)

Expert Solution
Check Mark

Explanation of Solution

The given DBDL in question 7 is as follows:

Department(DepartmentNum_,DepartmentName)AK DepartmentName

Advisor(AdvisorNum_,AdvisorName,DepartmentNum)AK AdvisorNameFK DepartmentNumDepartment

Course(CouseCode_,CourseName,CourseDescription)AK CourseName

Student(StudentNum_,StudentName,CourseCode,AdvisorNum,DepartmentNum)AK StudentNameFK CourseCodeCourseFK AdvisorNumAdvisorFK DepartmentNumDepartment

StudentAdvisor(StudentNum_,AdvisorNum_)FK StudentNumStudentFK AdvisorNumAdvisor

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:

Department(DepartmentNum_,DepartmentName)AK DepartmentName

Advisor(AdvisorNum_,AdvisorName,DepartmentNum)AK AdvisorNameFK DepartmentNumDepartment

Course(CouseCode_,CourseName,CourseDescription)AK CourseName

Student(StudentNum_,StudentName,CourseCode,AdvisorNum,DepartmentNum)AK StudentName

FK CourseCodeCourseFK AdvisorNumAdvisorFK DepartmentNumDepartment

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.

Want to see more full solutions like this?

Subscribe now to access step-by-step solutions to millions of textbook problems written by subject matter experts!
Students have asked these similar questions
Manhattan distance
Aiwhat cost from Oradea to Goal which Eforie us A* Algorithm G(n)+H(n) last what ıs the cost
What is the SELECT statement? give one reference with your answer What is a URL and what is it used for? give one reference with your answer  What is e-mail, and what are its advantages? Give one reference with your answer
Knowledge Booster
Background pattern image
Similar questions
SEE MORE QUESTIONS
Recommended textbooks for you
Text book image
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning