College of Engineering, Engineering Cooperative

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

6. interviews (0,N) (0,N) advises Student Affairs Advisor Contact Rep (0,N) (0,N) Student (1,1) (1,1) has Intern (1,N) represents (0,N) (0,N) Alumni (0,N) hires 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 (0,N) (1,1) offers looks_ for (0,N) at Location (0,N) (1,1) (1,1) (0,N) has requires Skills Job (0,N) (0,N) (0,N) (0,N)

7. Relational Schema

10. Relationship View in Access

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

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

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.COMPANYNAME=COM.COMPANYNAME And LS.COMPANYNAME=COM.COMPANYNAME And L2.COMPANYNAME=COM.COMPANYNAME And L3.COMPANYNAME=COM.COMPANYNAME And L4.COMPANYNAME=COM.COMPANYNAME;

26. V

27. Queries (cont’d) • 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 id]=s.[skill id] ORDER BY [major];

30. Queries (cont’d) • 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 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];

33. Queries (cont’d) • 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.Answer>22.36,"Accept","Reject")) AS Interpretation FROM [STAT ANALYSIS] AS sa;

36. Conclusion Thank the co-op personnel

37. Questions & Answers