Write the database table design to represent the data model below, including...
Write the database table design to represent thedata model below, including tables, the proper placement of the foreign key,and referential integrity constraint. Illustrate the implementation of valid minimum cardinalities that can bepresent this relationship?'static/image/hrline/1.gif
ANSWERThe cardinality of the above design is MANY-To-ONE. This means many students can have only one advisor. Or One advisor can have many students.Table design for the given diagram is as follows.Create table STUDENT(StudentID int, StuName varchar(20), StuMajor varchar(20), StuPhone varchar(15), primary key(StudentID));Create table ADVISOR(AdvisorID int, AdvName varchar(20), AdvOffice varchar(20), AdvPhone varchar(15), primary key(AdvisorID));Create table STUDENTADVISOR(StudentID int, AdvisorID int, primary key(StudentID,AdvisorID), foreign key(StudentID) references STUDENT(StudentID), foreign key(AdvisorID) references ADVISOR(AdvisorID));
static/image/hrline/2.gif
Answer & Explanation
https://www.coursehero.com/assets/img/qa/icon-verified.svgSolved by verified expert
Answer)Here we are having the Student table and the Advisor table.The Student relation is as StudentID(Primary key), StuName, StuMajor, StuPhoneAdvisor relation is as AdvisorID(Primary key), AdvName, AdvOffice, AdvPhone
Step-by-step explanation
The valid minimum cardinalities that can be present this relationship is:Advisor : Student = 1 : N
That is 1 Advisor that can be related to more than one student.
Thus the table design would be like:Student(StudentID(Primary key), StuName, StuMajor, StuPhone, AdvisorID (the foreign key which references the AdvisorID of Advisor table))
Advisor(AdvisorID(Primary key), AdvName, AdvOffice, AdvPhone)
Thus the final design for the above requirement is as already explained above:Student(StudentID, StuName, StuMajor, StuPhone, AdvisorID)Advisor(AdvisorID, AdvName, AdvOffice, AdvPhone)
页:
[1]