IEOR 115 Database Design Project

Growing Light Montessori

School

Ran Bi Corey Busay

Joanne ChiewJewon Kim

Derek LinLinh Tran

Fall 2010

Isabella Yamin[TEAM 5]

AgendaIntroduction

EER Diagram

Relational Schema

Database

Normalization Analysis

Queries

Q&A

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

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

AgendaIntroduction

EER Diagram

Relational Schema

Database

Normalization Analysis

Queries

Q&A

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Simplified EER Diagram

AgendaIntroduction

EER Diagram

Relational Schema

Database

Normalization Analysis

Queries

Q&A

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Relational Schema

1. 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)

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Relational Schema

AgendaIntroduction

EER Diagram

Relational Schema

Database

Normalization Analysis

Queries

Q&A

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Database Form 1

Form allows client to input new employee information

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Database Form 2

Form allows client to input new student information

AgendaIntroduction

EER Diagram

Relational Schema

Database

Normalization Analysis

Queries

Q&A

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Normalization Analysis

• Relation is in 1NF• Not in 2NF because {ActivityID Act_type}• Normalizing into 2NF:

• Enrolled_in(SID, Activity ID)• Actype(ActivityID, Act_type)

• It is also in 3NF because there is no non-prime attribute determining another

1) Enrolled_in(SID, Act_type, ActivityID)

• Relation is in 1NF• Relation is also in 2NF as there is no partial dependency. • Relation is in 3NF because none of the non-prime attribute

can determine another non-prime attribute

2) Purchase(PID, Time, Purchase_date, Amount, Employee_SSN, AID)

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Normalization Analysis

• Relation is in 1NF as there are no variable repeating groups. One type of checkup is organized for each medical condition. (Eg. Medical Condition: Polio, Checkuptype: Immunization)

• However, relation violates 2NF because Medical condition alone can determine the type of check up.

• Normalizing in to 3NF:• Medical_checkup_needed(SID,med_condition)• Checkup_type(med_condition,checkuptype)

Attends_medical (SID, med_condition, checkuptype)

• Relation is in 1NF. Contact person, Address of affiliate and phone number are single valued attribute.

• Relation is also in 2NF primary key is single valued thus determine all other non-prime attribute.

• However, 3NF is violated because a combination of {contactperson, Add} can determine {phone}

• Normalizing into 3NF:• Affiliate (Affname, Contact person, Add)• Affiliate_phone (Contact_person, Add, phone)

Affiliates(Aff_name, Contact_person, Add, Phone)

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Normalization Analysis

• Relation in 1NF because each advertising type started on a certain date {adtype,start_mmyy} has one cost, one duration and one total quantity. We assume that cost for adtype differs each time period

• Relation is in because adtype or start month and year alone cannot determine any of non-prime attributes.

• Relation is also in 3NF because information on a subset of {cost, duration, quantity} of advertisements cannot determine the other two.

Ad_medium(adtype, cost, duration, start_mmyy, quantity)

AgendaIntroduction

EER Diagram

Relational Schema

Database

Normalization Analysis

Queries

Q&A

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Query 1: Equipment Order Management

Objective

• For each equipment, calculate EOQ (economic order quantity) and order cycle, checks whether there is stock out, and suggests the next order date.

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 )

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Query 1: Equipment Order Management

• Allows our client to know each equipment’s optimal order quantity and next order date that minimizes the average total cost

• Query results are ordered by next order date in an ascending order so that our client knows which item to order first

Justification

• EID, Equipment Name, Stock-out (Yes/No), EOQ, order cycle in days, and next order date

Output

• The overall annual demand rate is known, and it is almost constant• ( still there is a chance of stock-out ). • Fixed costs, unit costs, and interest rate are known • There is no order lead time • 360 school days• Last purchase date is updated every time they order

Assumptions

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

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 NextOrderDate

FROM Equipment_Supply_Token

ORDER BY IIF([Inventory]=0, Date(), ([PurchaseDate]+round(((sqr(2*[FixedCost]*[Demand]/([InterestRate]/100*[UnitCost])))/[Demand])*360))) ASC;

Equipment Order Management : SQL

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

MS Access Implementation

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Equipment Order Management : Report

Shows next order date & EOQ predicted quantities

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Query 2: Student Performance Prediction

Objective

• Predicts student’s future grade based on past grades.• Based on linear regression (univariate)

Absolute change in grade with respect to time

Percentage change in grade with respect to time

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Query 2: Student Performance Prediction

• Allows our client to track progress of individual students and predict future performance

• Track which students might need extra attention

Justification

• Future grade prediction - absolute change and percentage change

Output

• Linear approximation is valid • No other factors besides time is taken into account (e.g.

subject matter, teacher etc.)

Assumptions

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

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 LinearPredictionFROM [Performance Report Query]GROUP BY [Performance Report Query].SID;

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

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 LinearPredictionFROM [Performance Report Query]GROUP BY [Performance Report Query].SID;

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Student Performance : ExecutionPrediction based on percentage change

Prediction based on absolute change

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Student Performance : Report

Shows student’s current grade and predicted grade for next semester

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Query 3: Student Balance

• What is the current balance on all students? • Also calculates how long student has been under their contract, the amount they owe,

the amount they have paid, and their balance

Objective

• Allows client to keep track of balance due on each student’s account

Justification

• Contract_duration, S_Monthy_Fee, S_TotalCharged, S_TotalPaid, Student_Balance

Output

• Fees are updated in the system every month• Payments are updated in the system every month• No discounts or penalties

Assumptions

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Query 3: Student Balance

Contract-Val is the total charges for the student over the time period indicated under Contract_Start and Contract_End.

Tables Used

...1

2

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Student Balance : SQL

SELECT Student.SID, First(DateDiff("m",[Student].[Contract_Start],date())) AS DurationFROM Student ;

SELECT Student.SID, [Student].[Contract_Val]/DateDiff("m",[Student].[Contract_Start],[Student].[Contract_End]))AS Monthly_FeeFROM Student;

SELECT S_Monthly_Fee.SID, [S_Monthly_Fee].[Monthly_Fee]*[Contract_Duration].[Duration] AS TotalChargedFROM S_Monthly_Fee, Contract_DurationWHERE (((S_Monthly_Fee.SID)=[Contract_Duration].[SID]));

SELECT Student.SID, Sum(([Student_Account].[MP_Amount])) AS TotalPaidFROM Student_Account, StudentWHERE Student.SID=Student_Account.SIDGROUP BY Student.SID;

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 PenaltyFeeFROM Student, S_TotalCharged, S_TotalPaidWHERE Student.SID=S_TotalCharged.SID And Student.SID=S_TotalPaid.SID And S_TotalPaid.SID=S_TotalCharged.SID;

Contract_Duration

S_Monthly_Fee

S_TotalCharged

S_TotalPaid

Student_Balance

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Student Balance : Implementation

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Student Balance : Implementation

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Query 4: Ad Medium ROI

Objective

• This query finds the return on investment (ROI) of each particular ad-type in a given month.

Calculated monthly

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Query 4: Ad Medium ROI

• Allows our client to invest more heavily in the advertising types which result in the most business/revenue (if they so wish).

Justification

• Returns the particular ad-type, the month for that ad-type, and its return on investment.

• Results sorted in a descending order, with the highest ROI on top for easy viewing by our client of what advertising has been the most effective.

Output

• Fees are updated in the system every month• Payments are updated in the system every month• No discounts or penalties

Assumptions

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

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;

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Ad Medium ROI : SQL

Sep-10 Oct-10 Nov-10 Dec-100

10

20

30

40

50

60

70

InternetPaperTV

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Query 5: Classroom Scheduling

• How can we schedule classes into each location to make sure that there will not be clashes for teachers or students?

• Genetic Algorithm• Fit each class into its optimum location and timeslot

Objective

Put 1 class in each timeslot

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Query 5: Classroom Scheduling

• Allows client to find most efficient way to schedule classes, taking into account constraints such as classroom capacity, teacher and student schedule

Justification

• Returns the optimal ordering of classes in timeslots

Output

• Genetic algorithm is heuristic, does not guarantee a feasible solution

• Dependent on number of iterations and optimality factors

Assumptions

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Import into Matlab•SELECT VIEW class( teacher. eid, activity.ACTID, group.gname, activities.duration, activities.equipment, group.capacity) FROM Teacher, Location, Group, Activities;

•CREATE TABLE location_time (Day char(10), Start_time number, location_name char(50),location_capacity number, location_equipment char(50));

Assigns fitness value to each class in each classroom

Create M random timetables and calculate fitness value of each time table• If class size smaller than room capacity, we increase fitness value by one.• If equipment of location matches class need, fitness value is increased one.• If no teacher is scheduled in different rooms for the same day and time more than once, fitness value is increased by one.• If no student group is scheduled in different rooms for the same day and time, we increment fitness value by one.

Retain the N schedules with the highest fitness value

Generate M-N new schedules

Repeat for x iterations

Output optimal schedule

Import back into Access• INSERT INTO Scheduling_Results•SELECT *•FROM [Excel 8.0;DATAbase=C:\sched_results.xls;HDR=Yes].[Sheet1$];

Methodology

Query 5: Classroom Scheduling

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Query 5: MatlabMain code:clear;clear allclc M=30;%number of CHROMOSOMES (not pairs, pairs=2*!) generated every generationN=70; %number of generations to runnogen=500;classes=[ 1 31 12 0 2 302 31 14 0 2 253 56 12 0 1 304 54 12 0 1 305 72 14 0 0 256 54 14 0 0 257 56 14 0 0 258 56 12 0 0 30]; [noclasses,classproperties]=size(classes); % initialize blank schedule matrixschedule=[]; %initialize matrix of chromosomes %generate first parentset (random)for i=1:N+Mchromosomemat(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:Nchromosomemat(M+i,:)=randperm(noclasses);fitnessvalue(M+i)=fitval(chromosomemat(M+i)); endend [~, 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 401 10 1 0 1 402 9 1 0 1 402 10 1 0 1 401 9 2 0 2 301 10 2 0 2 302 9 2 0 2 302 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 lastclasses=[ 1 31 12 0 2 302 31 14 0 2 253 56 12 0 1 304 54 12 0 1 305 72 14 0 0 256 54 14 0 0 257 56 14 0 0 258 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:heightif 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; elseif chromosomerep(i,4)==0; counter=counter+1; else %match the equipment counter=counter-100; endfor j=1:height if j==i elseif 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

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Query 5: Execution

Gro

win

g Li

ght M

onte

ssor

i Sch

ool

Further Work

Classroom Scheduling

• Planning optimal schedule of classes and classrooms• Working in VBA- using genetic algorithm

• Segmentation of students for better management of their performance

• Using similarities between students to group them together

Cluster Analysis

Q&A