Web Based Educational Technology for Secondary Schools

December 7, 2007

Team:Will GordonSam ToutounchianWillson DengSebouh Der KiureghianBilal ChaudhryBryan Clark

Client: G&L Software

Executive Summary

• Company Review

• EER Diagram

• Explanation of Queries

• Implementation in Access

• Q & A

Client BackgroundG&L Software

This product will create a communication forum for everyone involved in education. It represents zero cost to schools and can even be a source of revenue.

Proposed Database Architecture

EER Diagram

INSTITUTIONbelongs

_to

(0,2)

(0,N)

USER

belongs _to

(1,N)

(0,N)

D,T

PARENT

TEACHER

STUDENT

ADMIN

is_parent

(0,M)

(0,M)

CONTENTO,P

ASSIGNMENT

DISC_TOPIC

is_of_type

(1,1)

(1,N)

UPLOADED_FILE

CALNDER_EVNT

links_to

(0,N) (0,N)

WEBPAGE

displays(1,N)

(0,M)

views

(0,M)

(0,N)

PORTFOLIO owns

(1,1)

(1,1)

creates

(0,N)

(1,1)

user_ saves_in

(1,1)

(0,1)

CALENDAR links_to

(1,N)

(0,M)

has

(1,1)

(1,1)

DROPBOX

assign _date

due_ date

(1,1)(1,1)

DISC_RESPNSE

(1,1)(1,1)

has(1,1)

(0,1)

user_ stores_in

(0,1)

(0,N)

INTRACTV_GRP D,T

CLUB

STUDY_GROUP

COMMITTEE

CLASS_TYPE_TCHR

GROUP

belongs

belongs

is_of_type (1,1)

(0,N)

(0,N)

(1,1)

(1,1)

(0,M)

CLASS_INSTANCE

CLASSIFICATION

creates

creates

enrolls_in

teaches

is_member

has

GRADEBOOK

GRADE

receives

(0,1)

(1,1)

stores_in (1,1)(0,N)

gives(1,1)

(1,1)

(0,N)(0,M)

(1,1)(1,N)

creates

(0,N)(0,1)

(0,N)

(0,1)

(0,N)(0,M)

creates

(0,N)

(1,1)

(0,N)(1,1)

creates

(0,N)

(0,1)

creates

(0,N)

(0,1)

(1,1)

(1,1)

has

(1,1)

(1,1)

BULLETIN receives

belongs _to

(0,N)(1,M)

(1,N)

(0,M)

Relationship View – MS Access

Normalization Analysis

To make the ‘User’ relation satisfy 1NF, we change it in the following way:

User [UserID , Lname, Fname, MI, title, bdate, street, city, zip, phone] User [UserID , Lname, Fname, MI, title, bdate, street, city, zip]UserPhone [UserID , phone]

1NF

In our initial schema we had the following tables:1.Student [SUID]2.Teacher [TUID]3.Class [CID , TUID2 , title]4.Class_Period [CID3 , period ]5.Enrollment [SUID1 , CID4 , period4 ]6.Assignment [AID , CID3 , title , description, point_value , category]7.Gradebook [GBID , CID4 , period4 ]8.Grade [ GBID7 , AID6 , SUID1 , score]

In table 8, score is only dependent on AID and SUID, since the class ID can be derived from the AID, and the proper period number can be derived through the SUID. As a solution, we completely removed the Gradebook table (7) because it carried redundant data that was already included in the closure of {AID, SUID}+ = {AID, SUID , CID , period , score}

2NF

Normalization Analysis (cont.)

3NF

In our schema implementation there are no tables that are not in 3NF. However, some tables could possibly violate 3NF if they were written in the following way:1.Student[SUID]2.Teacher[TUID]3.Class[CID, TUID2 , subject , title]4.ClassPeriod [ CID3, period]5.Enrolls[SUID1, CID4 , period4]6.ClassAssignmentDropboxGrade[CID4 , AssignmentTitle , pointvalue , SUID1 , UploadedFile , score]

(6) violates both 2NF and 3NF. 2NF is violated by ‘pointvalue’ because pointvalue is only dependent on CID, period, TUID, and AssignmentTitle. 3NF is violated because ‘score’ and ‘UploadedFile’ are transitively dependent on SUID. The following revisions conform to 3NF:

.1 through 5 stay the same,6.7.Assignment [ AID , CID3 ,AssignmentTitle , point value]8.Dropbox [ DBID , AID7]9.FileUploadtoDropbox [DBID8, UFID , SUID1]10.Grade[AID7 ,SUID1 ,score]

Database Design

Schema

Forms

Reports

Query 1

Correlation between the time students take to finish assignments and the grade they receive on assignments.

SQL> SELECT G.score AS Grade_On_Assignment, (F.Date_Uploaded – U.view_date) AS Time_It_Took_To_Finish_Assignment

FROM Grade G, User_Views_Content U, File_in_Dropbox F, Uploaded_File UF

WHERE G.SUID = U.UID and F.UFID = UF.UFID and UF.uploaded_by_UID = G.SUID and F.uploaded_by_UID = UF.uploaded_by_UID

GROUP BY G.SUID;

Query 2

Comparative distributions of student grades in particular assignment types (i.e. tests, homework) across multiple classes to determine student strengths / weaknesses.

SQL> SELECT G.scoreFROM Assignment AS A, Student AS S, Grade AS GWHERE A.category="HW" and S.SID="1534-9583" and S.SUID=G.SUID and G.AID=A.AssignmentID;

SQL> SELECT G.scoreFROM Assignment AS A, Student AS S, Grade AS GWHERE A.category="Test" and S.SID="1534-9583" and S.SUID=G.SUID and G.AID=A.AssignmentID;

Query 2 Implementation

0

10

20

30

40

50

60

1 2 3 4 5 6

Scor

e (%

)

Test

Test Grades

Access

Results

Query 3

A statistical distribution (i.e. Normal, Weibull, Lognormal, etc.) will be derived from a student’s relevant past performance to forecast, within a specified confidence interval, their final grade(s). (Repeated for relevant class subjects).

SQL> SELECT S.SUID, (G.score) AS Grade_On_HW FROM Grade G, Assignment A, Student S, Class_Subject_and_Teacher CST WHERE G.SUID = S.SUID and A.category = ‘HW’ and CST.subject = ‘History’ and

G.AssignID = A.AssignID and A.belongs_to_CSTID = CST.CSTID UNION SELECT S.SUID, (G.score) AS Grade_On_Tests FROM Grade G, Class_Subject_and_Teacher CST, Assignment A, Student S

WHERE G.SUID = S.SUID and A.category = ‘Exam’ and CST.subject = ‘History’ and G.AssignID = A.AssignID and A.belongs_to_CSTID= CST.CSTID

GROUP BY S.SUID;

Query 3 Implementation

71.97% -- (Lower Bound)

75.28% -- (Upper Bound)

95% Confidence

Using Student-t Distribution 95% Confidence Interval of Trevor’s final “Test” grade (assuming 3 tests remain):

Test Grades Total Score

74.84 100

60.06 100

70.26 100

60.16 100

74.41 100

92.42 100

83.23 100

Total 515.39 700

mean sd T-Stat Remaining Tests

73.62668 11.72238343 2.4469 3

Remaining Total Final Grade Range

Lower 204.32 719.71 0.72

Upper 237.44 752.83 0.75

Query 4

Correlation between the amount of content a student views for a particular class and his or her average grade.

SQL> SELECT S.SUID, Average(G.score) FROM Student S, Grade G, Assignment A, User_belongs_to_IG

UBTIG, Class_Instance CI WHERE UBTIG.SUID = G.SUID and A.belongs_to_IGID =

UBTIG.IGID and G.SUID = UBTIG.UID and CI.CSTID = CST.CSTID and S.SUID = G.SUID

GROUP BY G.SUID UNION SELECT S.SUID, count(*) FROM User_Views_Content UVC, User_Views_Webpage UVW,

Student S WHERE UVC.UID = S.SUID and UVW.UID = S.SUID;

Query 5

Comparative distribution of grades that both teachers assign and students receive to determine outlying performers. This query can determine both school-wide distribution of grades given by teachers, and class-wide distribution of grades.

SQL> SELECT CST.TUID, count(*) AS Number_of_A FROM Grade Class_Subject_and_Teacher CST, Class_Instance CI, Grade

G, User_belongs_to_IG UBTIG, Assignment A WHERE UBTIG.SUID IN (SELECT UBTIG.SUID

WHERE sum(G.score)/sum(A.point_value) >= 90 and UBTIG.SUID = G.SUID and A.belongs_to_IGID = UBTIG.IGID and G.SUID =

UBTIG.UID and CI.CSTID = CST.CSTID and CST.TUID IN(SELECT TUIDFROM Teacher));