Download
1 / 21
210 likes | 375 Views
UC Berkeley Undergraduate Orientation Database. Project Review 3 – December 5, 2008. Group 5: Peter Chang • Eric Follis • Justin Hsu • Jason Tan • James Tong. Mission Statement.
E N D
UC Berkeley UndergraduateOrientation Database Project Review 3 – December 5, 2008 Group 5: Peter Chang • Eric Follis • Justin Hsu • Jason Tan • James Tong
Mission Statement "New students' initial encounters with the institution may have profound effects on subsequent levels of involvement, and these encounters should be carefully designed to socialize students to the institution's highest educational values and goals." (How College Affects Students, 1991)
CalSO, New Student Orientation Design a comprehensive, all-encompassing database to facilitate the operation of CalSO Student Information Counselor Information Event Information
Normalization Analysis: 1NF R is in 1NF if all attribute domains include only values that are atomic (indivisible) and single-valued. 1NF: Training(Training_ID, Name, Hours, Prerequisites, Required) TrainingName(Training_ID, Name, Hours, Required) TrainingPrerequisites(Training_ID, Prerequisite)
Normalization Analysis: 2NF R is in 2NF if it is in 1NF, and every non-prime attribute is fully functionally dependent on the Primary Key 2NF: TourBuilding(Counselor_ID, CFname, CLname, Tour_ID, TourName, Location_ID, Building) CounselorName (Counselor_ID, CFname, CLname) TourID (Tour_ID, TourName) Location (Location_ID, Building) TourLocation (Counselor_ID, Tour_ID, Location_ID, Building)
Normalization Analysis: 3NF R is in 3NF if R is in 2NF and non-prime attributes of R are transitively dependent on the primary key 3NF: TourLoc (Counselor_ID, Tour_ID, Location_ID, Building) LocationID(Location_ID, Building) TourLocation (Counselor_ID, Tour_ID, Location_ID)
Query 1: Absence Analysis In order to improve student outreach effectiveness, find the economic & geographical demographic information of students who did not attend CalSO Assumptions: • NewStudent.Attended = 1 if student attended CalSO, 0 if not. • Economic & geographical demographic information is fully described by county of residence and financial aid status. SELECT P.FName, P.MName, P.LName, P.Email, P.Phone, P.Major, NS.Financial_Aid, P.Class_Standing FROM NewStudent as NS, People as P WHERE P.PID = NS.PID, NS.Attended = 0 GROUP BY P.County, NS.Financial_Aid;
Query 2: Event Effectiveness Find the interest level associated with each optional event in CalSO. Assumptions: • Students are required to attend at least one event of their choice. • Interest level is determined based on a weighted function of the CalSO event attendance level, event survey score, and the counselor performance score. • InterestLevel = 4*AttendanceRate + 6*P.Score + 3*S.Score. SELECT CE.Event_ID, PP.Year, SUM(CE.No_of_Attendee) / COUNT(S.NewStudent) as AttendanceRate, P.Score, 4*AttendanceRate + 6*P.Score + 3*S.Score as InterestLevel FROM CalSO_Event as CE, Survey_Rating as S, Counselor as C, NewStudent as NS, Performance_Rating as P, People as PP WHERE CE.Event_ID = S.Event_ID, C.Counselor_ID = CE.Counselor_ID, C.Counselor_ID = NS.Counselor_ID, P.Counselor_ID = C.Counselor_ID GROUP BY CE.Event_ID, PP.Year;
Query 3: Parents Attendance Forecast Forecast parent attendance for CalSO events using regression analysis in order to optimize the resource allocation for future years. Assumptions: • There exists a relationship between the attendance levels of students and the attendance level of parents. • Regression Formula: y=Xβ+ε • X = # students in attendance • Y = # students in attendance • SQL code below extracts necessary inputs to perform the proposed regression analysis. All calculations will be executed in MS Excel using macros written in Visual Basic for Applications (VBA). SELECT COUNT(Pa.PID), COUNT(NS.PID), NS.Sem_Admit, PP.Year FROM People as PP, Parent as Pa, NewStudent as NS WHERE PP.PID = Pa.PID, Pa.Student_SID = NS.Student_SID GROUP BY PP.Year;
Query 4: Correlation of Training & Performance Review What is the correlation between the amount of optional training received and counselor performance? Assumptions: • Correlation Equation: • X = # optional training hours received by counselor • Y = performance score received by counselor • SQL code below extracts necessary inputs to perform the proposed correlation analysis. All calculations will be executed in MS Excel using macros written in Visual Basic for Applications (VBA). SELECT C.Counselor_ID, T.Training_ID, SUM(T.Hours), P.Score, P.Year FROM Peformance as PF, Training as T, Counselor as C, People P WHERE P.PID = S.PID, PF.Counselor_ID = C.Counselor_ID, T.Training_ID = S.Training_ID, T.Required = ‘No’ GROUP BY C.Counselor_ID, P.Year;
Query 5: Optimal Number of Employees to Hire Uses linear programming to determine optimal number of employees to recruit and hire in order to meet student demand. Assumptions: • Optimality is defined as minimizing costs while meeting a required service levels. • Coordinators do not contribute to the required student to staff employment ratio. • SQL code below extracts necessary inputs to perform the proposed linear programming analysis. All calculations will be executed in MS Excel using macros written in Visual Basic for Applications (VBA).
Query 5: Optimal Number of Employees to Hire Decision Variables X1 = # new hires X2 = # experience hires X3 = # coordinators Fixed Variables C1 = wage of new hires C2 = wage of experienced hires C3 = wage of coordinators S = # new students User Inputs B = total budget G = required counselor to coordinator ratio R = required student to staff employment ratio P = required percentage of experienced hires per total number of hires The following linear program is used: SELECT C.Wage, CO.Wage, CO.Experience, P.Year FROM People as P, Counselor as C, Coordinator as CO WHERE P.PID = C.PID, C.Supervisor = CO.PID, C.Counselor_ID = NS.Counselor_ID GROUP BY P.Year, CO.Experience UNION SELECT COUNT(NS.PID) FROM NewStudent as NS;
Query 5: Optimal Number of Employees to Hire Access Output Excel
