1 / 40

Buena Vista Elementary School

Buena Vista Elementary School. Avani Jariwala | April Song | Ben Yook | Elaine Huang Elim Kuk | Felicia Angesti | Hyun-Ho Jung Kyle Ong | Mitou Nguyen. Intro. Buena Vista Elementary School at a glance: ◊ Located in Walnut Creek, CA ◊ Features 534 students and 54 total staff members

napoleon-brogan
Download Presentation

Buena Vista Elementary School

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Buena Vista Elementary School Avani Jariwala | April Song | Ben Yook | Elaine Huang Elim Kuk | Felicia Angesti | Hyun-Ho Jung Kyle Ong | Mitou Nguyen

  2. Intro Buena Vista Elementary School at a glance: ◊ Located in Walnut Creek, CA ◊ Features 534 students and 54 total staff members ◊ Currently using web-based database program called AERIES Intro EER Schema Queries Normalization

  3. Intro Current Database Problems: ◊ Too complicated and difficult to use ◊ Web-based ◊ Regular subscription cost Our Database Solutions: ◊ User-friendly interface ◊ Offline functionality ◊ One-time cost for Microsoft Access Intro EER Schema Queries Normalization

  4. EER EER Intro Schema Queries Normalization

  5. Schema Schema Intro EER Queries Normalization

  6. Schema Schema Intro EER Queries Normalization

  7. Schema Schema Intro EER Queries Normalization

  8. Query 1 What is the correlation between tutoring hours and academic performance?

  9. Query 1 Purpose Finding whether or not tutoring is effective in boosting academic performance. Function SQL: Find list of tutoring hours and matching average exam grade. Matlab: Find correlation between tutoring hours and academic performance. Queries Intro EER Schema Normalization

  10. Query 1 SQL: Find list of tutoring hours and matching average exam grade. SELECT * FROM (SELECT Avg(Grade.Score) AS [Exam Grade], Count(TutorHours.SID) AS [Tutoring Amount] FROM [After school Tutoring] AS TutorHours, [In Class Exam Grade] AS Grade WHERE (TutorHours.SID) In (SELECT of_SID FROM [In Class Exam Grade]) GROUP BY Grade.of_SID) WHERE [Tutoring Amount] > 0; Queries Intro EER Schema Normalization

  11. Query 1 Matlab: Find correlation between tutoring hours and academic performance. f = csvread('acadPerf.csv'); x = f(:,1); y = f(:,2); hold on scatter(x,y) p = polyfit(x,y,1); r = p(1) .* x + p(2); plot(x,r) xlabel('Tutoring Hours'); ylabel('Average Test Score'); title('Tutoring on Academic Performance'); Queries Intro EER Schema Normalization

  12. Query 2 How should the cafeteria administration forecast demand?

  13. Query 2 Purpose Minimize the waiting time in the cafeteria and predict the order quantity of meal type. Function SQL: Find mean and standard deviation from data Newsvendor Problem: Find optimal order quantity for a given type of meal. Square Root Law: Find number of servers to attain a given level of service Approximated Waiting Time in Line Formula: Find ideal number of servers for 500 students. Queries Intro EER Schema Normalization

  14. Query 2 SQL: Find demand of different cafeteria foods using mean and standard deviation. SELECT [Cafeteria Order].MealName, Avg([Cafeteria Order].MealOrderQuantity) AS AvgOfMealOrderQuantity, StDev([Cafeteria Order].MealOrderQuantity) AS StDevOfMealOrderQuantity FROM [Cafeteria Order] GROUP BY [Cafeteria Order].MealName; Queries Intro EER Schema Normalization

  15. Query 2 Newsvendor Problem Assumption: 1. Selling price = $4 2. Purchase price = $2 3. Salvage value = $0.50. Assuming that the school will resell the leftover food. So, we find: 4. Overage cost: Co = $2-0.5 = $1.50 5. Underage cost: Cu = 4-2 = $2 F(Q*) = = 0.57 Then, find Z value from the table using the mean and the standard deviation. For example, if the school wants to find out how many pasta to order, they have to calculate: Q* = 114.25 + 18.89 * 0.57 = 117.58. Therefore, the optimal order quantity is 117.58 servings of pasta. Cu Cu + Co Queries Intro EER Schema Normalization

  16. Query 2 Square root law Assumptions: All students have a lunch break is 45 minutes. We assume that all students grab lunch in the first 15 minutes because they need time to finish their lunches. The lambda=25 students/minute and mu=4 students/ minute where lambda is the arrival rate of the students in the cafeteria and mu is the service rate of the cafeteria staff. We also assumed that there is 1 line because 1 line allows for greater utilization of all s servers. Basic equation for number of servers to have to attain a given level of service: N=R+BR.5 N = the number of servers R=(lambda)/(mu) Beta is a parameter related to the service level or P(wait) R = 11/4 = 6.25 N = 6.25+1.7(6.25).5 =10.5 Queries Intro EER Schema Normalization

  17. Query 2 Decision analysis method 2 possible choices: 1. Having 10 servers 10 = 6.25 + ℬ*(6.25)^(.5) => ℬ = 1.5 P{waiting} = 0.0668 ≈6.68% 2. Having 11 servers 11 = 6.25 + ℬ*(6.25)^(.5) => ℬ = 1.9 P{waiting} = 0.0287 ≈2.87% Using formula for approximated waiting time in line 1. Wq = 0.0118 min/students = 0.718 seconds/students • Wq = 0.0058 min/students = 0.35 seconds/students Conclusion: Ideally have 10 servers in a situation where there are 500 students. Queries Intro EER Schema Normalization

  18. Query 3 How can we assign students to classrooms in the most efficient way?

  19. Query 3 Purpose Ensure equal gender distribution in each classroom and allow students to flourish in groups with similar academic performance Function SQL: Group students by average exam grade AMPL: Place students with similar average exam grade into a classroom of 30 students with specific gender distribution Queries Intro EER Schema Normalization

  20. Query 3 SQL: Order students by gender, grade, and average academic grade. SELECT g.of_SID, Avg(g.Score) AS AvgOfScore, Person.gender AS Gender, s.Year FROM ([In Class Exam Grade] AS g INNER JOIN STUDENT AS s ON g.of_SID = s.SID) INNER JOIN Person ON s.SSN = Person.SSN GROUP BY g.of_SID, Person.gender, s.Year, AvgOfScore; Queries Intro EER Schema Normalization

  21. Query 3 AMPL: Set parameters and decision variables for linear programming. Model File • param a; • # number of girls in a particular grade • param b; • # number of boys in a particular grade • param n; • # number of classes in a particular grade • var x{i in 1..a, j in 1..n} binary; • # 1 if female student i is in class j, 0 o/w • var y{k in 1..b, j in 1..n} binary; • # 1 if male student k is in class j, 0 o/w AMPL: Use SQL query results to sort students into classrooms. Data File • param a:= 45; # number of girls in a particular grade • param b:= 45; # number of boys in a particular grade • param n:= 3; # number of classes in a particular grade Queries Intro EER Schema Normalization

  22. Query 3 AMPL: Set objective function and constraints. • minimize students {j in 1..n}: sum{i in 1..a} x[i,j] + sum{k in 1..b} y[k,j]; • subject to maximum_student_per_class {j in 1..n}: • sum{i in 1..a} x[i,j] + sum{k in 1..b} y[k,j] <= 30; • # Maximum of 30 students per class • subject to minimum_female_per_class {j in 1..n}: • sum{i in 1..a} x[i,j] >= 13; • # At least 13 girls in each class • subject to minimum_male_per_class {j in 1..n}: • sum{k in 1..b} y[k,j] >= 13; • # At least 13 boys in each class • subject to female_student {i in 1..a}: • sum{j in 1..n} x[i,j] = 1; • # Each female student is allowed to be allocated in 1 class • subject to male_student {k in 1..b}: • sum{j in 1..n} y[k,j] = 1; • # Each male student is allowed to be allocated in 1 class Queries Intro EER Schema Normalization

  23. Query 3 Queries Intro EER Schema Normalization

  24. Query 4 What donating trends can the school take advantage of for fundraising efforts?

  25. Query 4 Purpose Maximize the school’s revenues from fundraising campaigns. Function SQL: Find the addresses where people are most likely to donate. Python: Map out donations by address and by donation amount. SQL: Find the times of the year when people are most likely to donate. Queries Intro EER Schema Normalization

  26. Query 4 SQL: Find the addresses where people are most likely to donate. SELECT Person.Address, Person.Zipcode, SUM([Finance Data].FinAmount) AS [Donation Amount] FROM (([Donation] INNER JOIN [Finance Data] ON [Donation].[FID] =[Finance Data].[FID]) INNER JOIN Person ON [Donation].SSN = [Person].SSN) GROUP BY Person.Address, Person.Zipcode; Queries Intro EER Schema Normalization

  27. Query 4 Python: Map out addresses that were extracted from SQL onto a map in order to visualize which neighborhoods are donating the most amount of money to the school. Queries Intro EER Schema Normalization

  28. Query 4 Queries Intro EER Schema Normalization

  29. Query 4 SQL: Find the months when people are most likely to donate. SELECT FinName, SUM(f.FinAmount), f.FinDate FROM Financial-account WHERE FinType=‘Donation’ GROUP BY FinDate SORT BY SUM(FinAmount) DESC; Queries Intro EER Schema Normalization

  30. Query 5 How can the school minimize the cost of purchasing library books?

  31. Query 5 Purpose Maximize student satisfaction while optimizing use of limited funds and library space. Function Java: Derive excess waiting time and purchase or sell additional copies of book accordingly. Time Value of Money: Find the rate at which the school will need to replace the book. Queries Intro EER Schema Normalization

  32. Query 5 Java: Derive excess waiting time and purchase or sell additional copies of book accordingly Queries Intro EER Schema Normalization

  33. Query 5 Time Value of Money: Find the rate at which the school will need to replace the book. Assumptions: The school wants to resell books at 40% of the original price and keep one copy of each book. Assuming the rate of book depreciation is at a constant rate of 1% per week, then the school can calculate how long a book should be kept. (Original Price) * (1+r)n = (Current Price) r = (-)1% per week (Original Price) * (1-0.01)n = 40% (Original Price) (0.99)n = 0.4 n ln (0.99) = ln (0.4) n = ln (0.4) / ln (0.99) n = 91.2 weeks Queries Intro EER Schema Normalization

  34. Normalization Decomposing from 1NF to 2NF: Achievement (Type, SID1a, Date, Reward, Comment) To normalize from 1NF to 2NF: AchievementInfo (Type, SID1a, Date, Comment) AchievementReward (Type, Reward) Type SID Date Reward Comment Normalization Intro EER Schema Queries

  35. Normalization Decomposing from 1NF to 2NF: InClassExam (Date, ExamName, Score, Average, Is_in_RID15, Of_SID1a, Scored_bySTID1a) To normalize from 1NF to 2NF: ExamScore (Date, ExamName, Of_SID1a, Score, Is_in_RID15) InClassExam (Date, ExamName, Average, Scored_by1a) Date ExamName SID Score RID Average Scored_By Normalization Intro EER Schema Queries

  36. Normalization Decomposing from 1NF to 2NF: Report_Card (RID, SID1a, CourseName, Grade, Comment, Grade_Level, Reported_to_SSN1, Completed_by_SSN1) To normalize from 1NF to 2NF: Score_Submission (RID, Completed_by_SSN1, Grade_Level, CourseName) ReportCard (RID, SID1a, Comment, Grade, Reported_to_SSN1) RID SID CourseName Grade Comment Grade_Level Reported_By_SSN Completed_By_SSN Normalization Intro EER Schema Queries

  37. Normalization Decomposing from 1NF to 2NF: PTA (PTAID, SSN1, Position, VolunteerHours) To normalize from 1NF to 2NF: PTAOrg (PTAID, Position, VolunteerHours) PTA_Member (PTAID, SSN1) PTAID SSN Position Volunteer_Hours Normalization Intro EER Schema Queries

  38. Normalization Decomposing from 2NF to 3NF: PTAOrg (PTAID, Position, VolunteerHours) PTA_Member (PTAID, SSN1) To normalize from 2NF to 3NF: PTAPositionReq (Position, LeastVolunteerHours) PTAOrg (PTAID, Position) PTAMember (PTAID, SSN1, VolunteerHours) PTAID Position Volunteer_Hours Normalization Intro EER Schema Queries

  39. Closing Statements

  40. Q & A

More Related