Design choices
Entities: INSTRUCTOR, ADJUNCT, FULL-TIME, TT, NTT, COURSE, PHD_STUDENT, MILESTONE, SCHOLARSHIP, SCHOLASRHIP_SUPPORT, GRA, GTA, SELF_SUPPORT, GRANT, SECTION
Relationship: TAUGHT, SUPERVISOR, COMMITTEE, ASSIGNEDTO, OFFERS, PASSED, ASSIGNED, CO-PI, PI
Entity INSTRUCTOR has attributes: InstructorID, which is a unique key attribute and that is why it is underlined, composite attribute Name (FName and LName), Degree, Rank, and StartDate. Instructors are of two main types: ADJUNCT and FULL_TIME. So we create two subclasses with the superclass INSTRUCTOR. Each professor must be either adjunct or full-time and due to that we add disjoint full participation constraint. Full-time professors can be TT or NTT. We add two more subclasses which have disjoint full participation constraint. They are disjointed as a professor can only either be a NTT or a TT and not both the same applies for adjunct and full-time professors. NTT professors are related to the courses they teach. The relationship NTT TAUGHT COURSE, TT SUPERVISOR PHD_STUDENT, TT COMMITTEE PHD_STUDENT, TT CO-PI GRANT, TT PI GRANT. Entity TT has an attribute NoOfPHDSupervised to track the number of supervised students. Entity PHD_STUDENT has attributes: composite attribute Name (FName and LName), StudentID is unique and underlined, composite attribute StartSemester (Semester and Year). PHD_STUDENT can be either SCHOLARSHIP_SUPPORT, GTA, GRA, SELF_SUPPORT. Because PhD students can be of both types at time and exist in these categories, we added overlapping full participation constraint. Entity SCHOLARSHIP has attributes: Type, ScholarshipID is unique and underlined, and FundSource. SCHOLARSHIP is assigned to PhD students in the category SCHOLARSHIP_SUPPORT, and we added relationship between them SCHOLARSHIP ASSIGNED TO SCHOLASRHIP_SUPPORT with full participation constraint N-1. One student can be granted many scholarships and full participation on both sides because there is at least one scholarship assigned to a scholarship student and vice versa. Entity GRANT has attributes: GrantTitle, StartDate, StartAmount, EndDate, Type, Source, CurrBalance, and AccuntNo (unique and underlined). Grants are given to GRAs, so we add the relationship GRANT FUNDS GRA with total participation constraints N-1. Entity Course has two attributes: key attribute CourseID and CourseName. Entity SECTION has a unique underlined attribute SectionID and SecionName. Course is related to the section, so we added the relationship COURSE OFFERS SECTION with full participation constraints in section side and partial on course side N-1. One course can have multiple sections and that is why it is N-1 cardinality. Each section must have a course and that is why it is full participation. Not every course has a section and it is partial participation. GTA is associated with the section so we added the relationship GTA ASSIGNED SECTION with full participation constraints 1-1. Each GTA is assigned with a section and that is why it is 1:1 cardinality. Each GTA must have a section associated with it and that is why it is total participation and vice versa. Entity MILESTONE has attributes: MName and Mid. Each PhD student must pass a number of Milestones, so we added the relationship PHD_STUDENT PASSES MILESTONE and the attribute Date with full participation constraint on phd side and partial on milstone N-1. NTT professors are associated with the courses they teach, we added the relationship NTT TAUGHT COURSE with M-N relationship and full participation constraint on NTT side and partial on COURSE side. The cardinality is such due to the fact that there can be multiple courses taught by many professors and vice versa. Each professor has to be teaching at least one course and that is why it is total participation constraints. Courses can be taught by other than NTT professors and that is why it is partial participation. Each PhD student must have one TT professor supervisor and few committee TT professors. We added the relationships TT SUPERVISOR PHD_STUDENT 1-1 with partial participation constraint on TT side and full participation constraint on PHD_STUDENT side. It is 1-1 because each student must be assigned 1 TT professor and each professor has to supervise a student. Not every TT professor is a supervisor and that is why it is a partial constraint. However, each student must have a supervisor and it is total participation. Also, we added TT COMMITTEE PHD_STUDENT 1-N relationship with partial participation constraint on TT side and full participation constraint on PHD_STUDENT side.Each PHD student can have N number of TT faculty associated with them and hence the relation between TT and PHD_STUDENT is N:1. The participation constraint is partial for TT to committee because there can be a TT faculty member that does not belong to any committee and the participation constraint on the PHD_STUDENT to the committee is total participation as each PHD_STUDENT must be assigned a committee. The TT is related to the grant through two relations the pi and co-pi. They are both N:1 constraints and there is a total participation from the TT to Grant. There can be multiple TT faculty members associated with one grant and hence the cardinality is N:1. Each TT faculty member must have at least one grant associated with it and so the participation constraint between the TT and the pi or co-pi is a total participation. On the other hand a grant can have 0 or more associations with a TT faculty and hence it is a partial constraint.
For the schema we chose the option 8A approach in the slides which was multiple relations-superclass and subclass because it is applicable for most cases. We made a separate and new relation called taught as the cardinality for it is M:N in our schema.