College of Engineering, Engineering Cooperative Education Program

Jonas CoronaLisa Ho

Milan LeeMonica Leung

Gloria LoAngela Lu

Olivia TandraJenny Wang

Organization Background

A cooperative venture between educational and employment communities

Provides students with opportunity to work with professional employers for six months

Estimates of Data Size

An average of 150 applicants per year Some good years reach up to 300 applicants

Contact with 75 employers regularly

Current System

Use MS Excel and Word to support processing of paper applications to store student and company information

Placement cards system maintain student records

Contact Rep

Skills

Student Affairs Advisor

Alumni

Companyworking

prefers

at

has

hires

requireshas

offers

advises

has

o

represents

Location

Student

Benefits receives

offers

Intern

Department

Job

interviews(0,N)

(0,N)(0,N)(0,N)(0,N)

(0,N)

(0,N) (0,N)

(0,N)

(1,1)(0,N)

(1,N)

(0,N)

(1,1)(0,N)

(1,1)

(0,N)

(0,N)

(1,1)

(0,N)

(1,1)

(1,N)

(0,1)

(0,N)

(0,N)

(0,N)

(0,N)

looks_for

Major

(0,N)

(0,N)

(0,N)

applies_to

(0,N)

(0,N)

accepts

(1,1)

(0,N)

Relational SchemaENTITY

1 PERSON

Person ID First name Last nameMiddle initial

Email address

a STUDENT

Person ID SID Major Ethnicity Disability Sex Work

eligibility Perm

address Perm phone

Loc address Loc phone Entry date Grad date Degree Transcript Resume Current technical classes

I INTERN

Person ID SID1a Waived SHIP

Telebears access

Home IP reactivate

F-1 student FAO

Postcard sent

Volunteer

ii ALUMNI

Person ID SID1a Company

b CONTACT REP

Person ID Phone Address Dept Title

c Person ID AID SID

STUDENT AFFAIRS ADVISOR

2 COMPANY

Company name Address WebsitePhone number

Fax number Hire periodActive status

On-campus interview

3 DEPARTMENT

Dname Company

name2

4 LOCATION

Location ID Location name

5 JOB

Job ID Company

name2

Department

name3Job

descriptionJob skill

level

6 SKILLS

Skill ID Skill description Proficiency

7 BENEFITS

Benefits IDBenefit

description

N-M RELATIONSHIP

8

Skill ID6 SID1a

9

SID1aii Company

name2Full time job

start date

10

SID1a Location ID4

11

SID1a Rep--First

name1b

Rep--Middle

initial1b

Rep--Last

name1b

Rep--Email

address1bInterview

dateInterview

timeInterview location

Interviewing preferences

12

Dname3 Location ID4

13

Job ID5 Skill ID6

Students_prefer_location

Student_has_skills

Alumni_works_for_company

Job_requires_skills

Department_locates_in _location

Students_interviews_with_Rep

Relationship View in Access

Queries

1. DEMOGRAPHICS QUERY

Out of all applicants in 2001, the percentage of students who actually got job offer; this is broken down by major, by ethnicity, then by sex.

Clients can recognize what the underrepresentedstudent groups are and they can tailor theirmarketing strategies to target and increase jobplacement for those segments.

Demographics Query

SELECT a.ethnicity, sum(a.num_of_students)/sum(b.num_of_students) AS percentage

FROM student_with_offers_by_m_e_s AS a, student_applied_by_m_e_s AS b

WHERE a.sex=b.sex And a.major=b.major And a.ethnicity=b.ethnicity

GROUP BY a.ethnicity;

SELECT a.major, Sum(a.num_of_students)/Sum(b.num_of_students) AS percentage

FROM student_with_offers_by_m_e_s AS a, student_applied_by_m_e_s AS b

WHERE (((a.Sex)=[b].[sex]) AND ((a.major)=[b].[major]) AND ((a.Ethnicity)=[b].[ethnicity]))

GROUP BY a.major;

SELECT a.sex, (sum(a.num_of_students)/sum(b.num_of_students)*100) AS percentage

FROM student_with_offers_by_m_e_s AS a, student_applied_by_m_e_s AS b

WHERE a.sex=b.sex And a.major=b.major And a.ethnicity=b.ethnicity

GROUP BY a.sex;

Queries (cont’d)

2. COMPANY INVOLVEMENT QUERY

List the company, which has a constant decrease of the internship position availability for the past two years (5 semesters).

Clients can observe each company’s inclination to participate in this program.

Company Involvement Query

SELECT COM.COMPANYNAME AS NAME, CS.JOBNUMBER AS [CURR SEM], LS.JOBNUMBER AS [LAST SEM], L2.JOBNUMBER AS [LAST 2 SEM], L3.JOBNUMBER AS [LAST 3 SEM], L4.JOBNUMBER AS [LAST 4 SEM]

FROM [CURRENT SEMESTER] AS CS, [LAST SEMESTER] AS LS, [LAST 2 SEMESTER] AS L2, [LAST 3 SEMESTER] AS L3, [LAST 4 SEMESTER] AS L4, [COMPANY WITH DECREASING JOB OFFERED] AS COM

WHERE CS.COMPANYNAME=COM.COMPANYNAME And LS.COMPANYNAME=COM.COMPANYNAME And L2.COMPANYNAME=COM.COMPANYNAME And L3.COMPANYNAME=COM.COMPANYNAME And

L4.COMPANYNAME=COM.COMPANYNAME;

V

Queries (cont’d)

3. CURRICULUM EVALUATION QUERY

List the skills that a job would require that have never been met by students in this year, categorized by each major. (year consists of both semesters).

Provide suggestions to improve each engineering curriculum.

Curriculum Evaluation Query

SELECT DISTINCT m.major, sk.[skill ID]

FROM job_requires_skills AS jrs, job AS j, skill AS sk, major AS m

WHERE jrs.[skill id] = sk.[skill id] AND jrs.[job id] = j.[job id] AND j.[major wanted] = m.[major id] AND not exists (SELECT *

FROM Student_has_skills AS ss, student s WHERE ss.sid = s.sid and ss.[skill id] =

jrs.[skill id] and j.[major wanted] = s.major);

SELECT [major], s.[skill description], s.proficiency

FROM [CURRICULUM EVALUATION] AS c, skill AS s

WHERE c.[skill id]=s.[skill id]

ORDER BY [major];

Queries (cont’d)

4. PROGRAMME EFFECTIVENESS QUERY

List the alumnus who graduated this year, has been hired by the same company, whom he/she had internship with.

Measure the effectiveness of the co-op program, if the program really helps students to find a job after their graduation.

Program Effectiveness Query

SELECT s.[first name], s.[last name], s.[email address], s.[permanent address], s.[permanent city], s.[permanent state], s.

[permanent zip], s.[permanent phone number], s.[Graduation date], j.[Company Name]

FROM alumnus AS a, intern AS i, student AS s, job AS j

WHERE a.[company name]=j.[company name] And s.SID=i.SID

And i.SID=a.SID And i.[job id]=j.[job id] And s.[graduation date] Between [Start Date] And [End Date];

Queries (cont’d)

5. STATISTICAL ANALYSIS QUERY

Test the independency among the majors and job offered. We want to know if a major will affect the chance of a student to get an internship.

If there is a difference, our client can target those majors, which have lower chances of getting an internship, by recruiting more companies in the related field of jobs.

Statistical Analysis Query SELECT sum((num_of_students - (select sum(num_of_students) from students_by_major sbm1

where sbm1.major = sbm.major)(select sum(num_of_students) from students_by_major sbm2 where sbm2.has_offer =

sbm.has_offer)\/ (select sum(num_of_students) from students_by_major sbm3))*(num_of_students

- (select sum(num_of_students) from students_by_major sbm7 where sbm7.major = sbm.major)* (select sum(num_of_students) from students_by_major sbm8 where sbm8.has_offer =

sbm.has_offer)/ (select sum(num_of_students) from students_by_major sbm9))

/ ( (select sum(num_of_students) from students_by_major sbm4 where sbm4.major = sbm.major)* (select sum(num_of_students) from students_by_major sbm5 where sbm5.has_offer = sbm.has_offer)/ (select sum(num_of_students) from students_by_major sbm6))) AS Answer

FROM students_by_major AS sbm;SELECT (IIf(sa.Answer>22.36,"Accept","Reject")) AS InterpretationFROM [STAT ANALYSIS] AS sa;

Conclusion

Thank the co-op personnel

Questions & Answers