Client: G&L Software

1. Web Based Educational Technology for Secondary SchoolsDecember 7, 2007Team:Will GordonSam ToutounchianWillson DengSebouhDerKiureghianBilalChaudhryBryan Clark Client: G&L Software

2. Executive Summary • Company Review • EER Diagram • Explanation of Queries • Implementation in Access • Q & A

3. 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.

4. EER Diagram Proposed Database Architecture

6. Relationship View – MS Access

7. 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: Student [SUID] Teacher [TUID] Class [CID , TUID2 , title] Class_Period [CID3 , period ] Enrollment [SUID1 , CID4 , period4 ] Assignment [AID , CID3 , title , description, point_value , category] Gradebook [GBID , CID4 , period4 ] 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

8. Normalization Analysis (cont.) 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: Student[SUID] Teacher[TUID] Class[CID, TUID2 , subject , title] ClassPeriod [ CID3, period] Enrolls[SUID1, CID4 , period4] 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] 3NF

9. Database Design Schema

11. Forms

12. Reports

13. 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;

14. 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.score FROM Assignment AS A, Student AS S, Grade AS G WHERE A.category="HW" and S.SID="1534-9583" and S.SUID=G.SUID and G.AID=A.AssignmentID; SQL> SELECT G.score FROM Assignment AS A, Student AS S, Grade AS G WHERE A.category="Test" and S.SID="1534-9583" and S.SUID=G.SUID and G.AID=A.AssignmentID;

15. Query 2 Implementation Access Results

16. 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;

17. Query 3 Implementation Using Student-t Distribution 95% Confidence Interval of Trevor’s final “Test” grade (assuming 3 tests remain): 71.97% -- (Lower Bound) 75.28% -- (Upper Bound) 95% Confidence

18. 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;

19. 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 TUID FROM Teacher));