Write the database table design to represent the data model below, including tables, the proper placement of the foreign key, and referential integrity constraint. Illustrate the implementation of valid minimum cardinalities that can be present this relationship? '
ANSWER The 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)); Answer & Explanation
[size=1.875] Solved by verified expert[size=1.875]
Answer) Here we are having the Student table and the Advisor table. The Student relation is as StudentID(Primary key), StuName, StuMajor, StuPhone Advisor relation is as AdvisorID(Primary key), AdvName, AdvOffice, AdvPhone
[size=2.25]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)
| 
