Internship Placement

Requirements
The main component of this assignment is a relational data model for business processes that manipulate data about: students, organizations that advertise and sponsor internship positions, and internship placements that students fill when they are apply and get an internship position.

We think of the Internship Program at UNHM as the “client” interested in a database application that will manage internship data. The users of this database applications are:
 * students who search for internship positions,
 * organizations who place internship position ads with UNHM, and
 * staff at UNHM who enter internship-related data and produce various reports on internship data.

An internship position is advertised by an organization which will sponsor internship internship placements for UNHM students. Students see the internship positions and apply for them. If the position is offered to a student, then an internship placement data entry is created to show which students have interned for which positions.

Entity Definitions

 * student: A person with a valid UNH student identification.
 * organization: A company that wishes to post and fill internship positions.
 * internship_position: Internship listings that are posted online or through ads by companies that have been vetted by the University.

student entity has studentID attribute as unique identifier. This is an ID that UNHM generates for each student who is admitted as a degree student or takes course as a continuing education student.

organization entity has organizationID attribute as unique identifier. This is an ID that the internship placement database application generates for each new organization that registers with UNHM to make posts for their internship positions.

internship position entity has internshipPositionID as unique identifier. This is an ID that the internship placement database application generates for each new internship position that is posted by any of the organizations.

Organization(one) posts(many)InternshipPosition
In defining this relationship, we see that: Therefore, this is a one-to-many relationship.
 * Once an organization has registered and has been properly vetted by the University, it can post many internship positions.
 * When a position is listed on the online school bulletin, then that position (whatever it may be) is associated with one and only one company. This means that the same position cannot be sponsored (or posted) by multiple organizations.

Student(many) interns for (many)InternshipPosition
Thus, this is a many-to-many relationship.
 * During his/her program of study at UNHM, a particular Student can intern for many InternshipPositions.
 * A particular InternshipPosition, in turn, can allow for many students to fill it. For example, the position lists the maximum number of students for which it is made available.

Design
Internship Placement Relational Model



Entity Definitions
The attributes of the entities identified in the conceptual model are:
 * organization(organizationID (PK), organizationName, organizationPhone)
 * internship_position(positionID (PK), organizationID (FK), positionName, positionDescription, positionStatus, hours, payRate)
 * student(studentID(PK), lastName, firstName, studentYear, major, GPA)

The logical model has an intersection table that introduces a new entity:
 * internship_placement(studentID(PK,FK), positionID(PK,FK), startDate, endDate, actualPay)

The instances in this intersection table map internship positions with students for a particular time period (from startDate to endDate) and an agreed upon actualPay (can be 0 if the internship is not paid).

internship_placement unique identifier is the combination of values from studentID and 'positionID. Therefore, this entity has a two-attribute unique identifier.

New Relationship Definitions
The intersection table replace the many-to-many relationship between Student and InternshipPosition with two one-to-many relationships as follows:

InternshipPosition(one) accommodates (many)InternshipPlacement
internship_position(positionID (PK), organizationID (FK), positionName, positionDescription, positionStatus, hours, payRate) internship_placement(studentID(PK,FK), positionID(PK,FK), startDate, endDate, actualPay)

Before we explain the one-to-many relationship, we must understand that an instance of the InternshipPlacement entity is uniquely identified by TWO values: (studentID, positionID). Cardinality justification:
 * an instance of the InternshipPosition entity can accommodatemany InternshipPlacement(s) instances. It means that the same positionID value can appear in multiple rows in the InternshipPlacement table. Each such row, however, must have a different studentID. By combining the same positionID with different studentIDs we always point to a single row in the InternshipPlacement table.


 * a specific InternshipPlacement entity instance is associated with at most one InternshipPosition instance. Again, we pick an InternshipPlacement instance by deciding on two pieces of information: studentID and positionID. For that particular instance, the positionID value points to exactly one instance in the InternshipPosition table.

Student(one) fills in (many)InternshipPlacement
student(studentID(PK), lastName, firstName, studentYear, major, GPA) internship_placement(studentID(PK,FK), positionID(PK,FK), startDate, endDate, actualPay)

Again, we remind ourselves that any instance in the InternshipPlacement entity is uniquely identified by TWO values: (studentID, positionID).

Cardinality justification:
 * a particular Student fills, at most, many InternshipPlacement instances. It means that the same studentID value can appear in many rows in the InternshipPlacement rable. Each such row, however, must have a different positionID. By combining the same studentID with different positionIDs, we always point to a single row in the InternshipPlacement table.


 * an instance of the InternshipPlacement entity is associate with at most one Student instance. Let's pick an InternshipPlacement instance. It means that we must decide on two pieces of information: studentID and positionID. For the particular instance we chose, the studentID value points to exactly one instance in the Student table.

Create the database schema
To create the database schema, we write create table statements for the four tables in the database. We then write alter table constraints to define the primary keys, turn on the auto_increment feature for the primary keys, and define foreign key constraints.

The script is saved in the unhm_internship.sql file. It has drop statements and create database statement to be clean up the database before any table is created.

Load sample data
This script is run once, in order to work. Before you run it again, you need to rerun the script that cleans up everything and recreates the database schema. This is because the internahip_placement table has an instance with a composition primary key of (1, 1). By running this scrip multiple times, student and internship_position tables will end up with multiple instances with identical values, except for the primary key values, which get incremented.

The order in which inserts are done matters. Data is placed first in parent tables to created values for their primary keys. Then data is placed in child tables, which need foreign key data from their parents' primary key.

Back to CIS520 Class Notes