sponsor: is an organization that advertises an internship position and sponsors an internship placement for a CIS student. (UID = sponsorID)
contact: is a person that is the point of contact for the sponsor. (UID = contactID)
course: is a course in which students get internship credits. (UID = courseID)
instructor: is a CIS faculty who teaches the internship class. (UID = instructorID)
cis_faculty: is the faculty member in the CIS program who advises CIS majors. (UID = facultyID)
performance: is the review of the work the student has done on at the internship by the supervisor. (UID = performanceID)
position: is an internship position that’s advertised by a sponsor with the intention of generating a placement. (UID = positionID)
supervisor: is the person that supervises the placement of a CIS major. (UID = supervisorID)
class: is a scheduled class during a semester for an internship course. (UID = classID)
cis_major: is a UNHM student who majors in CIS. (UID = studentID)
roster: is an association between CIS majors, the placement of their internship position and the internship class. (UID = rosterID)
placement: is the association of a position with a CIS major who is taking the internship class. (UID = placementID)
sponsor-contact: is a one to many relationship. A sponsor can have more than one contact. A contact can represent only one sponsor.
sponsor-position: is a one to many relationship. A sponsor can offer more than one position. A position will only be offered by one sponsor.
class-course: is a one to many relationship. A class will point back to one course record. A course record can point to several classes if more than one is offered.
roster-class: is a one to many relationship. Each class will appear many times in the roster records. Each roster record will only point back to a certain class.
class-instructor: is a one to many relationship. A class will be taught by only one instructor. An instructor can teach more than one class.
position-placement: is a one to many relationship. A position many result in many placements. Each placement record will only point back to one position.
placement-roster: is a one to many relationship. Each placement record will be associated with exactly one roster instance. Each roster entry can be associated with more than one placement record.
placement-supervisor: is a one to many relationship. A placement record will only point back to one supervisor. A supervisor may be associated with more than one placement record.
placement-performance: is a one to one relationship. A placement record may only result in a total of one performance review. A performance review will only be completed for one internship placement record.
roster-cis_major: is a one to many relationship. A roster record will only point back to one cis-major. A cis-major can appear in many roster records.
cis_major-cis-faculty: is a one to many relationship. A cis-major is advised by only one cis-faculty member. A cis-faculty member can advise more than one cis-major.
sponsor: is an organization that offers internship positions for students through UNHM. The unique identifier is sponsorID. Attributes contained in the sponsor entity are name, address, city, state, zip and url.
contact: is a person representing the sponsor organization for which all communication will flow through. The unique identifier is contactID. Attributes contained in the sponsor entity are lastName, firstName, email, phone, title, status, lastContactDate. The sponsorID is inserted into the contact table in order to create relationship between sponsor and contact.
course: is the course in which students participating in the internship program will receive credit. The unique identifier is courseID. Attributes contained in the course entity are name and numberOfCredits. The catalogID is inserted into the course table in order to create a relationship between course and the class table.
instructor: is a UNHM professor who maintains an internship course. The unique identifier is instructorID. Attributes in the instructor entity are lastName, firstName and email.
cis_faculty: is a UNHM staff member who advises students. The unique identifier is facultyID. Attributes in the instructor entity are lastName, firstName, and email.
performance: is an evaluation of an intern's performance written by the either the intern's supervisor. The unique identifier is performanceID. Attributes in the performance table are problemSolving, professionalism, taskCompletion, followDirections, solicitAdvice, initiative, oralCommunication, writtenCommunication and collaboration.
supervisor: is a person at the sponsoring organization who will be responsible for evaluating the intern. The unique identifier is supervisorID. Attributes in the supervisor entity are lastName, firstName, phone, email and title.
class: is an internship course. The unique identifier is classID. Attributes in the class table are sectionNumber and term. CourseID and instructorID are inserted into the class table in order to create relationships between class and course as well as class and instructor.
cis_major: is a student at UNHM who is currently majoring in the CIS program. The unique identifier is studentID. Attributes in the cis_major table are lastName, firstName and email. FacultyID is located in the cis_major table to create a relationship between cis_faculty and cis_major.
position: is an internship opportunity advertized by a an internship sponsor. The unique identifier is positionID. Attributes located in the position entity are title, weeklyHours, wage, postDate, startDate, endDate and offeredPositions. SponsorID is located in the position table in order to create a relationship between sponsor and position.
roster: is the association between a class a cis_major. The unique identifier is rosterID. The attributes in the roster table are grade isResearchProject (Y or N) and complete (Y or N). ClassID and studentID are inserted into the roster table to create relationships between cis_major roster and class.
placement: is a designation for a cis_major to a given internship position. The unique identifier is placementID. Attributes in the placement table are placementNumber, actualWage, actualStartDate and actualEndDate.The placement entity contains the supervisorID, positionID, performanceID and rosterID as foreign keys.
In creation of the internship_2_career database schema, the entities must be created in the order listed below. The creation of the tables is broken down into four groups. Group 1 must be created first then group 2 and so on. The arrows in the UML diagram below don't necessarily show relationships rather just the order of creation. Ordering the creation is critical because of the relationships between the tables. If for instance you try to create a table in the third or fourth group prior to the creation of a related table in the first or second group mysql will give you an error and the tables will relate.
Below resides the internship_2_career database script for the creation of the database, tables, users and relationships.