PERSON(Fname,Minit,Lname,Ssn,Bdate,Sex,AddrNo,AddrStreet,AddrApt_no,AddrCity,AddrState,AddrZip) STUDENT(Ssn,Class,minor,major) FACULTY(Ssn,Rank,Foffice,Fphone,Salary) GRAD_STUDENT(Ssn,advisor) INSTRUCTOR_RESEARCHER(Ssn) DEGREES(college,degree,year,student) GRANT(Title,No,Agency,St_date,PI) DEPARTMENT(Dname,Dphone,Office,chair,college) COURSE(C#,Cname,Cdesc,dept) SECTION(Sec#,Year,Qtr,course,instructor) CURRENT_SECTION(Sec#) BELONGS(faculty,dept) SUPPORT(grant,person,start,end,time) REGISTERED(student,section) TRANSCRIPT(student,section,grade) COMMITTEE(faculty,student)
Each entity type becomes a relation with the attributes of the entity type and a primary key corresponding to the key of the entity type.
GRANT(Title,No,Agency,St_date) DEPARTMENT(Dname,Dphone,Office) COLLEGE(Cname,Dean,Coffice) COURSE(C#,Cname,Cdesc)
The other entity types are all involved in specialization and/or category types, so will be handled later.
There aren't any weak entity types.
Binary 1:1 relationships where neither entity type has total participation can be handled with the foreign key or cross-reference approaches.
The advantage of the foreign key approach is fewer relations, and adding the chair to the DEPARTMENT is both conceptually reasonable (an attribute of a department is its chair) and is not likely to result in too many NULL values as one would expect most departments to have chairs. (The other direction, where the department chaired is added to FACULTY is less satisfying as most faculty would not chair a department, resulting in many NULL values.)
Binary 1:N relationship types can be mapped using the foreign key approach (adding the relationship information to the N side) or the cross-reference approach.
Either approach works here since there is no total participation. The foreign key approach results in fewer relations, and can be preferable if adding the foreign key to a relation makes conceptual sense.
GRANT(Title,No,Agency,St_date,PI) GRAD_STUDENT(...,advisor) STUDENT(...,minor,major) SECTION(...,course,instructor) COURSE(C#,Cname,Cdesc,dept) DEPARTMENT(Dname,Dphone,Office,chair,college)
Binary M:N relationships are mapped using the cross reference approach. The attributes of the new relation are the primary key attributes of the participating entity types, and all attributes form the primary key.
BELONGS(faculty,dept) SUPPORT(grant,person,start,end,time) REGISTERED(student,section) TRANSCRIPT(student,section,grade) COMMITTEE(faculty,student)
Multivalued attributes are mapped by creating a relation with the attribute and the primary key of the relation corresponding to the attribute's entity type. All attributes constitute the primary key for the new relation.
There aren't any relationships involving more than two entity types.
There are four options for handling specialization: relations for every subclass and superclass, relations for just the subclasses, a single relation for all with a single type attribute, and a single relation with binary type attributes. Not all options work in all situations.
For PERSON, FACULTY, STUDENT, the disjoint specialization means there is an inconsistency potential for the "single relation multiple type attributes" approach because it would allow for a person to be both faculty and student. A single relation with one type attribute is undesirable because there are many relationships as well as some attributes applicable to only students or only faculty, resulting in the potential for many irrelevant attributes with NULL values. And since the specialization isn't total (there could be people who aren't faculty or students), the "multiple relations with superclass relation" approach seems best. (minor and major in STUDENT come from the handling of relationships.)
PERSON(Fname,Minit,Lname,Ssn,Bdate,Sex,AddrNo,AddrStreet,AddrApt_no,AddrCity,AddrState,AddrZip) FACULTY(Ssn,Rank,Foffice,Fphone,Salary) STUDENT(Ssn,Class,minor,major)
For STUDENT and GRAD_STUDENT, observe that the two "single relation" options do not need to be considered because having only one subclass means that "multiple relations subclass only" results in only one relation and the presence of the Class attribute means there is already a way to determine subclass membership so there is no need to add one or more type attributes. Because the multivalued attribute Degrees and the M:N relationship COMMITTEE have been handled with their own relations, the "subclass only" approach only adds one attribute (advisor) to STUDENT so irrelevant attributes/NULL values aren't such a problem...but there is no way within the schema to require that degrees and committees only involve grad students. Instead, with a separate GRAD_STUDENT relation, the foreign keys from DEGREES and COMMITTEE can be constrained to refer only to grad students - so this approach is preferable even though there is only one extra attribute in GRAD_STUDENT.
Similar considerations apply to SECTION and CURRENT_SECTION.
Category types are mapped using a relation containing any attributes of the category itself plus the surrogate or superclass key, in addition to the relations for the individual categories (with the surrogate key).
Since FACULTY and GRAD_STUDENT already have Ssn in common, no surrogate key is needed. (Note, too, that the semantics are such that the Ssns of actual faculty will be distinct from those of actual students, so there is no possibility of a faculty instructor-researcher and a grad student instructor-researcher having the same Ssn - i.e. Ssn can correctly be a key in INSTRUCTOR_RESEARCHER.) Those relations remain the same except for the addition of a foreign key constraint referring to INSTRUCTOR_RESEARCHER.
INSTRUCTOR_RESEARCHER(Ssn) FACULTY(Ssn,Rank,Foffice,Fphone,Salary) GRAD_STUDENT(Ssn,advisor)