Transcript Designing a Database for an 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 ◊ Currently using web-based database program called AERIES
Intro
EER Schema Queries Normalization
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
EER
Intro
EER
Schema Queries Normalization
Schema
Intro EER
Schema
Queries Normalization
Schema
Intro EER
Schema
Queries Normalization
Schema
Intro EER
Schema
Queries Normalization
Query 1
What is the correlation between tutoring hours and academic performance?
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.
Intro EER Schema
Queries
Normalization
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; EER Schema
Queries
Normalization Intro
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
Query 2
How should the cafeteria administration forecast demand?
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.
Schema
Queries
Normalization Intro EER
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; Intro EER Schema
Queries
Normalization
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
Cu + Co
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.
Schema
Queries
Normalization Intro EER
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
Schema
Queries
Normalization Intro EER
Query 2
Decision analysis method 2 possible choices: 1. Having 10 servers 10 = 6.25 + ℬ *(6.25)^(.5) => ℬ P{waiting} = 0.0668 ≈6.68% = 1.5
2. Having 11 servers 11 = 6.25 + ℬ *(6.25)^(.5) => ℬ P{waiting} = 0.0287 ≈2.87% = 1.9
Using formula for approximated waiting time in line 1. Wq = 0.0118 min/students = 0.718 seconds/students 2.
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
Query 3
How can we assign students to classrooms in the most efficient way?
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 EER Schema
Queries
Normalization Intro
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; EER Schema
Queries
Normalization Intro
Query 3
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 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 EER Schema
Queries
Normalization Intro
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
Query 3
Intro EER Schema
Queries
Normalization
Query 4
What donating trends can the school take advantage of for fundraising efforts?
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 donate.
likely to EER Schema
Queries
Normalization Intro
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; Intro EER Schema
Queries
Normalization
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.
Intro EER Schema
Queries
Normalization
Query 4
Intro EER Schema
Queries
Normalization
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; Intro EER Schema
Queries
Normalization
Query 5
How can the school minimize the cost of purchasing library books?
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 replace the book.
will need to Intro EER Schema
Queries
Normalization
Query 5
Java: Derive excess waiting time and purchase or sell additional copies of book accordingly Intro EER Schema
Queries
Normalization
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
Normalization Intro EER Schema
Normalization
Decomposing from 1NF to 2NF: Achievement (Type, SID 1a , Date, Reward, Comment)
To normalize from 1NF to 2NF:
AchievementInfo (Type, SID 1a , Date, Comment) AchievementReward (Type, Reward) Type SID Date Reward Comment Intro EER Schema Queries Normalization
Normalization
Decomposing from 1NF to 2NF: InClassExam (Date, ExamName, Score, Average, Is_in_RID 15 , Of_SID 1a , Scored_bySTID 1a )
To normalize from 1NF to 2NF:
ExamScore (Date, ExamName, Of_SID 1a , Score, Is_in_RID 15 ) InClassExam (Date, ExamName, Average, Scored_by 1a ) Date ExamName SID Score RID Average Scored_By Intro EER Schema Queries Normalization
Normalization
Decomposing from 1NF to 2NF: Report_Card (RID, SID 1a , CourseName, Grade, Comment, Grade_Level, Reported_to_SSN 1 , Completed_by_SSN 1 )
To normalize from 1NF to 2NF:
Score_Submission (RID, Completed_by_SSN 1 , Grade_Level, CourseName) ReportCard (RID, SID 1a , Comment, Grade, Reported_to_SSN 1 ) RID SID CourseName Grade Comment Grade_Level Reported_By_SSN Completed_By_SSN Intro EER Schema Queries Normalization
Normalization
Decomposing from 1NF to 2NF: PTA (PTAID, SSN 1 , Position, VolunteerHours)
To normalize from 1NF to 2NF:
PTAOrg (PTAID, Position, VolunteerHours) PTA_Member (PTAID, SSN 1 ) PTAID SSN Position Volunteer_Hours Intro EER Schema Queries Normalization
Normalization
Decomposing from 2NF to 3NF: PTAOrg (PTAID, Position, VolunteerHours) PTA_Member (PTAID, SSN 1 )
To normalize from 2NF to 3NF:
PTAPositionReq (Position, LeastVolunteerHours) PTAOrg (PTAID, Position) PTAMember (PTAID, SSN 1 , VolunteerHours) PTAID Position Volunteer_Hours Intro EER Schema Queries Normalization