College of Engineering,

1
College of Engineering, Engineering
Cooperative
Education Program
Jonas Corona Lisa Ho Milan Lee Monica Leung
Gloria Lo Angela Lu Olivia Tandra Jenny Wang
2
Organization Background

• A cooperative venture between educational and
  employment communities
• Provides students with opportunity to work with
  professional employers for six months

3
Estimates of Data Size

• An average of 150 applicants per year
• Some good years reach up to 300 applicants
• Contact with 75 employers regularly

4
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

5
(No Transcript)
6
interviews
(0,N)
(0,N)
advises
Contact Rep
Student Affairs Advisor
(0,N)
(0,N)
Student
(1,1)
has
(1,1)
represents
Intern
(1,N)
(0,N)
(0,N)
Alumni
hires
(0,N)
o
receives
(1,N)
Benefits
(0,N)
(0,N)
prefers
Major
Department
applies_ to
(0,N)
(1,1)
(0,N)
accepts
offers
(0,N)
(0,N)
(0,N)
(0,N)
(0,N)
(0,N)
(0,1)
working
has
Company
offers
(1,1)
(0,N)
looks_ for
(0,N)
at
Location
(1,1)
(0,N)
(1,1)
(0,N)
requires
has
Skills
Job
(0,N)
(0,N)
(0,N)
(0,N)
7
Relational Schema
8
(No Transcript)
9
(No Transcript)
10
Relationship View in Access
11
(No Transcript)
12
(No Transcript)
13

14
(No Transcript)
15
(No Transcript)
16
(No Transcript)
17
(No Transcript)
18
(No Transcript)
19
(No Transcript)
20
Queries

• 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 underrepresented
• student groups are and they can tailor their
• marketing strategies to target and increase job
• placement for those segments.

21
Demographics Query
SELECT a.ethnicity, sum(a.num_of_students)/sum(b.n
um_of_students) AS percentage FROM
student_with_offers_by_m_e_s AS a,
student_applied_by_m_e_s AS b WHERE a.sexb.sex
And a.majorb.major And a.ethnicityb.ethnicity GR
OUP 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)/su
m(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.sexb.sex
And a.majorb.major And a.ethnicityb.ethnicity GR
OUP BY a.sex
22
(No Transcript)
23
(No Transcript)
24
Queries (contd)

• 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 companys inclination
  to participate in this program.

25
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.COMPANYNAMECOM.COMPANYNAME And
  LS.COMPANYNAMECOM.COMPANYNAME And
  L2.COMPANYNAMECOM.COMPANYNAME And
  L3.COMPANYNAMECOM.COMPANYNAME And
  L4.COMPANYNAMECOM.COMPANYNAME

26
V
27
Queries (contd)

• 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.

28
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 ids.skill id
• ORDER BY major

29
(No Transcript)
30
Queries (contd)

• 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.

31
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 namej.company name And
  s.SIDi.SID
• And i.SIDa.SID And i.job idj.job id
  And s.graduation date Between Start Date And
  End Date

32
(No Transcript)
33
Queries (contd)

• 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.

34
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.Answergt22.36,"Accept","Reject"))
  AS Interpretation
• FROM STAT ANALYSIS AS sa

35
(No Transcript)
36
Conclusion

• Thank the co-op personnel

37
Questions Answers