Growing Light Montessori School's Data Organization with Microsoft Access

2. Introduction EER Diagram Relational Schema Database Normalization Analysis Queries Q&A

3. Introduction | Client Overview “Our goal is to help your child feel good about himself/herself so they can have the confidence it takes to get along well with others.” • Growing Light Montessori Schools serves children ages 2-12 in a variety of different classes • They have campuses in Oakland, Kensington, and Moraga • Currently, Growing Light is using Excel to organize their data • This is leading to problems of many forms in different places and overall clutter • Growing Light would like to move to Microsoft Access • Allows all storage of data in one place, and allows new and better ways to organize information

4. Introduction EER Diagram Relational Schema Database Normalization Analysis Queries Q&A

5. Simplified EER Diagram

6. Introduction EER Diagram Relational Schema Database Normalization Analysis Queries Q&A

7. Relational Schema • Student(SID, Student_SSN, Fname, Lname, MI, DOB, Gender, street, city, state, zip, start_date, house_phone, contract_amount, (Adtype)14) • 14. Ad_medium(AdType, cost, duration, starting_month)

8. Relational Schema

9. Introduction EER Diagram Relational Schema Database Normalization Analysis Queries Q&A

10. Database Form 1 Form allows client to input new employee information

11. Database Form 2 Form allows client to input new student information

12. Introduction EER Diagram Relational Schema Database Normalization Analysis Queries Q&A

13. Normalization Analysis

14. Normalization Analysis

15. Normalization Analysis

16. Introduction EER Diagram Relational Schema Database Normalization Analysis Queries Q&A

17. Query 1: Equipment Order Management Order cycle in days = (EOQ/Demand)*360 Next Order Date = Last Purchase Date + Order Cycle ( In case of stock out, current date becomes the next order date )

18. Query 1: Equipment Order Management

19. SELECT EID, Equip_Name, IIF(Inventory=0, 'Yes', 'No') AS Stockout, round(sqr(2*[FixedCost]*[Demand]/([InterestRate]/100*[UnitCost]))) AS EOQ, round(((sqr(2*[FixedCost]*[Demand]/([InterestRate]/100*[UnitCost])))/[Demand])*360) AS OrderCycle, IIF([Inventory]=0, Date(), ([PurchaseDate]+round(((sqr(2*[FixedCost]*[Demand]/([InterestRate]/100*[UnitCost])))/[Demand])*360))) AS NextOrderDateFROM Equipment_Supply_TokenORDER BY IIF([Inventory]=0, Date(), ([PurchaseDate]+round(((sqr(2*[FixedCost]*[Demand]/([InterestRate]/100*[UnitCost])))/[Demand])*360))) ASC; Equipment Order Management : SQL

20. MS Access Implementation

21. Equipment Order Management : Report Shows next order date & EOQ predicted quantities

22. Query 2: Student Performance Prediction Absolute change in grade with respect to time Percentage change in grade with respect to time

23. Query 2: Student Performance Prediction

24. Student Performance : SQL SELECT [Performance Report Query].SID, (Avg([Month_date]*[AvgOfGrade])-(Avg([Month_date])*Avg([AvgOfGrade])))/(Avg(([Month_date])^2)-(Avg([Month_date]))^2) AS Linearchange, Avg([AvgOfGrade])-[Linearchange]*Avg([Month_date]) AS Linearalpha, [Linearchange]*([max_month]+1)+(Avg([AvgOfGrade])-[Linearchange]*Avg([Month_date])) AS LinearPredictionNextMonth, (Avg([LogMonth_date]*[AvgOfGrade])-(Avg([LogMonth_date])*Avg([AvgOfGrade])))/(Avg(([LogMonth_date])^2)-(Avg([LogMonth_date]))^2) AS Logcoeff, Avg([AvgOfGrade])-([Logcoeff])*Avg([LogMonth_date]) AS Logalpha, ([Logcoeff]*4+(Avg([AvgOfGrade])-[Logcoeff]*Avg([LogMonth_date])))/100 AS LogPercentageChange, [LinearPrediction]*Exp([LogPercentageChange]) AS LogPredictionNextMonth, Max([Performance Report Query].Month_date) AS max_month, [Linearchange]*([max_month])+(Avg([AvgOfGrade])-[Linearchange]*Avg([Month_date])) AS LinearPrediction FROM [Performance Report Query] GROUP BY [Performance Report Query].SID;

25. Student Performance : SQL SELECT [Performance Report Query].SID, (Avg([Month_date]*[AvgOfGrade])-(Avg([Month_date])*Avg([AvgOfGrade])))/(Avg(([Month_date])^2)-(Avg([Month_date]))^2) AS Linearchange, Avg([AvgOfGrade])-[Linearchange]*Avg([Month_date]) AS Linearalpha, [Linearchange]*([max_month]+1)+(Avg([AvgOfGrade])-[Linearchange]*Avg([Month_date])) AS LinearPredictionNextMonth, (Avg([LogMonth_date]*[AvgOfGrade])-(Avg([LogMonth_date])*Avg([AvgOfGrade])))/(Avg(([LogMonth_date])^2)-(Avg([LogMonth_date]))^2) AS Logcoeff, Avg([AvgOfGrade])-([Logcoeff])*Avg([LogMonth_date]) AS Logalpha, ([Logcoeff]*4+(Avg([AvgOfGrade])-[Logcoeff]*Avg([LogMonth_date])))/100 AS LogPercentageChange, [LinearPrediction]*Exp([LogPercentageChange]) AS LogPredictionNextMonth, Max([Performance Report Query].Month_date) AS max_month, [Linearchange]*([max_month])+(Avg([AvgOfGrade])-[Linearchange]*Avg([Month_date])) AS LinearPrediction FROM [Performance Report Query] GROUP BY [Performance Report Query].SID;

26. Student Performance : Execution Prediction based on percentage change Prediction based on absolute change

27. Student Performance : Report Shows student’s current grade and predicted grade for next semester

28. Query 3: Student Balance

29. Query 3: Student Balance Tables Used ... 1 2 Contract-Val is the total charges for the student over the time period indicated under Contract_Start and Contract_End.

30. Student Balance : SQL Contract_Duration SELECT Student.SID, First(DateDiff("m",[Student].[Contract_Start],date())) AS Duration FROM Student ; S_Monthly_Fee SELECT Student.SID, [Student].[Contract_Val]/DateDiff("m",[Student].[Contract_Start],[Student].[Contract_End]))AS Monthly_Fee FROM Student; S_TotalCharged SELECT S_Monthly_Fee.SID, [S_Monthly_Fee].[Monthly_Fee]*[Contract_Duration].[Duration] AS TotalCharged FROM S_Monthly_Fee, Contract_Duration WHERE (((S_Monthly_Fee.SID)=[Contract_Duration].[SID])); S_TotalPaid SELECT Student.SID, Sum(([Student_Account].[MP_Amount])) AS TotalPaid FROM Student_Account, Student WHERE Student.SID=Student_Account.SID GROUP BY Student.SID; Student_Balance SELECT Student.Fname, Student.Lname, [S_TotalCharged].[TotalCharged]-[S_TotalPaid].[TotalPaid] AS Balance, IIf(S_TotalCharged.TotalCharged-S_TotalPaid.TotalPaid>0,(S_TotalCharged.TotalCharged-S_TotalPaid.TotalPaid)*0.1,0) AS PenaltyFee FROM Student, S_TotalCharged, S_TotalPaid WHERE Student.SID=S_TotalCharged.SID And Student.SID=S_TotalPaid.SID And S_TotalPaid.SID=S_TotalCharged.SID;

31. Student Balance : Implementation

32. Student Balance : Implementation

33. Query 4: Ad Medium ROI Calculated monthly

34. Query 4: Ad Medium ROI

35. Ad Medium ROI : SQL • SELECT Ad_medium.Adtype, Ad_medium.monthyear, (Sum([Contract_amt]/[Contract_dur]))/(([cost]*[quantity]))-1 AS ROI • FROM Ad_medium INNER JOIN Student ON (Ad_medium.Adtype = Student.Referred_by) AND (Ad_medium.monthyear = Student.Referred_mon) • GROUP BY Ad_medium.Adtype, Ad_medium.monthyear, ([Cost]*[quantity]) • ORDER BY (Sum([Contract_amt]/[Contract_dur]))/(([cost]*[quantity]))-1 DESC;

36. Ad Medium ROI : SQL

37. Query 5: Classroom Scheduling Put 1 class in each timeslot

38. Query 5: Classroom Scheduling

39. Query 5: Classroom Scheduling Methodology

40. Query 5: Matlab • Main code: • clear; • clear all • clc • M=30; • %number of CHROMOSOMES (not pairs, pairs=2*!) generated every generation • N=70; • %number of generations to run • nogen=500; • classes=[ • 1 31 12 0 2 30 • 2 31 14 0 2 25 • 3 56 12 0 1 30 • 4 54 12 0 1 30 • 5 72 14 0 0 25 • 6 54 14 0 0 25 • 7 56 14 0 0 25 • 8 56 12 0 0 30]; • [noclasses,classproperties]=size(classes); • % initialize blank schedule matrix • schedule=[]; • %initialize matrix of chromosomes • %generate first parentset (random) • for i=1:N+M • chromosomemat(i,:)=randperm(noclasses); • fitnessvalue(i)=fitval(chromosomemat(i,:)); • end • for t=1:nogen • %get rid of N chromosomes with the smallest N values • %find indice of smallest value in fitness value and kill from • %chromosomemat • for i=1:N • [~,I]=min(fitnessvalue); • chromosomemat(I,:)=[]; • fitnessvalue(I)=[]; • end • for i=1:N • chromosomemat(M+i,:)=randperm(noclasses); • fitnessvalue(M+i)=fitval(chromosomemat(M+i)); • end • end • [~, maxchromo]=max(fitnessvalue); • max_chromosome=chromosomemat(maxchromo,:) • maxfitness_value=fitval(max_chromosome) • FitVal function: • function [fitnessvalue, counter]=fitval(chromosome) • %timetable=[ • %'Mo' 9 'room1' 0 1 40 • %'Mo' 10 'room1' 0 1 40 • %'Tu' 9 'room1' 0 1 40 • %'Tu' 10 'room1' 0 1 40 • %'Mo' 9 'room2' 0 2 30 • %'Mo' 10 'room2' 0 2 30 • %'Tu' 9 'room2' 0 2 30 • %'Tu' 10 'room2' 0 2 30]; • timeslot=[ • 1 9 1 0 1 40 • 1 10 1 0 1 40 • 2 9 1 0 1 40 • 2 10 1 0 1 40 • 1 9 2 0 2 30 • 1 10 2 0 2 30 • 2 9 2 0 2 30 • 2 10 2 0 2 30]; • %Classtable=[ • %'Music1A' 'Boleyn' 'Hummingbirds' 0 1 30 • %'Math1A' 'Thurman' 'Hummingbirds' 0 1 30 • %'Gym' 'Lee' %'Froggies' 0 2 30 • %'Gym' 'Lee' 'Froggies' 0 2 25 • %'Reading2' 'Johnson' 'Froggies' 0 0 25 • %'Math1B' 'Thurman' %'Hummingbirds' 0 0 25 • %'Nap' 'Boleyn' 'Froggies' 0 0 25 • %'Reading1' 'Boleyn' 'Hummingbirds' 0 0 30]; • %rearranging such that those with no equipment constraints go last • classes=[ • 1 31 12 0 2 30 • 2 31 14 0 2 25 • 3 56 12 0 1 30 • 4 54 12 0 1 30 • 5 72 14 0 0 25 • 6 54 14 0 0 25 • 7 56 14 0 0 25 • 8 56 12 0 0 30]; • [~,classproperties]=size(classes); • height=length(chromosome); • for i=1:height • chromosomerep(i,:)=classes(chromosome(i),:); • end • counter=0; • [notimeslot,timeproperties]=size(timeslot); • for i=1:height • if chromosomerep(i,5)==timeslot(i,5) || chromosomerep(i,5)==0 • counter=counter+1; • else • counter=counter-100; • end %match the capacity • if chromosomerep(i,4)==timeslot(i,4); • counter=counter+1; • elseifchromosomerep(i,4)==0; • counter=counter+1; • else %match the equipment • counter=counter-100; • end • for j=1:height • if j==i • else • if chromosomerep(i,2)==chromosomerep(j,2) && timeslot(i,1)==timeslot(j,1)&& timeslot(i,2)==timeslot(j,2) • %same teacher & same day &same time • counter=counter-100; • end • %make sure the teachers will not have clashing schedules • if chromosomerep(i,3)==chromosomerep(j,3) && timeslot(i,1)==timeslot(j,1) && timeslot(i,2)==timeslot(j,2) • %same teacher & same day &same time • counter=counter-100; • end • end • end • end • fitnessvalue=counter; • end

41. Query 5: Execution

42. Further Work • Cluster Analysis • Segmentation of students for better management of their performance • Using similarities between students to group them together