Hw#2
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Hw#2 as PDF for free.
More details
- Words: 1,435
- Pages: 8
Prathivadi, Abhinav – Database Project Design [Inf 257]
1 Abhinav Prathivadi Prof. Ray Larson Sep 25th, 2008
Personal Database Project Design Throughout the history of the Internet, the communications platform has produced many revolutions and has impacted other industries surrounding it by largely creating a level playing field. The academia has changed in profound ways in the last decade and the Internet has been pivotal in revolutionizing the way students have exchanged information, ideas and the interaction between teachers and students. A challenging task many people from the academia face is the approach towards Internet many schools and universities have taken over the last few years, automating most of their processes including student data and billing for students. The most important aspect of student data is the transcript (the grades associated with it) and an online integration of student grades and courses within an e-grade book is the purpose of this project and in the process streamline the accessibility and build an application to scale over many universities. This grade book is designed to streamline the process of entering and managing grades from a student as well as a teacher perspective. With a form based reporting system and tabulation of data online, updates to students and parents is very effective and convenient method of reporting grades. The purpose of this paper is to design a robust back-end capable of sustaining large amounts of data load as the grading processes of many universities and schools are time and data intensive work. By focusing on flexible and easy data entry into the gradebook, a lot of time is saved that can be productively put in other aspects of classroom interaction. Although the various entities have been documented (see next few pages) changes are inevitable as bottlenecks will be noticed
Prathivadi, Abhinav – Database Project Design [Inf 257]
2
and optimized to a high level of efficiency in the coming months. The idea of starting off with a rough sketch of the system helps shape the direction of the database system, and by gradually exploring different aspects of grading, various perspectives will emerge and subsequently the gradebook will evolve eventually to a scalable solution for universities to use. Students in many Universities and colleges are eager to know their grades almost immediately after submitting their assignment and the graders are overwhelmed with paperwork amounting to hundreds of papers per assignment per class. Although the amount of work is considerably swifter compared to the traditional calculator approach teachers used to take a decade back, the prospect of a web based grade book is exciting because of the potential communication platform it can setup between students and teachers. Although numerous grade books exist online, most of them are “downloadable” desktop grade book software that has become a pain for the users to use, as web based products are kicking into the industry. With the advent of Google Docs, Windows Office Live, etc. the academia has largely migrated to a web based software world where collaboration and search for information is moving predominantly online. The student – teacher collaboration outside the class room involves the management of numerous assignments and exams and to organize the grades has been a challenge for many years. The academia has developed over a period of time various in-house database systems like bSpace, etc, but an integrated free solution for teachers, students and schools of all sizes and domains is non existent and this project hopes to fill that gap and build a scalable and sustainable solution for an online gradebook system.
Prathivadi, Abhinav – Database Project Design [Inf 257]
Data Dictionary The entities involved in the database are Table: Students *Student_id Type: int (auto-increment)
Example: {1,2,3…123,124,125…. ∞} Description: student id associated with each student. It is unique per student and is a primary key
first_name
Type: varchar (25)
Example: {abhinav, adam, john, mark} Description: first name of the student
last_name
Type: varchar (25)
school_name
Type: varchar (75)
gender
Type: varchar (1)
date_of_birth
Type: date (mm/dd/yyyy) Example: {11/7/1988} Description: date of birth of the student
Example: {prathivadi, smith, henderson} Description: last name of the student Example: {Temecula Valley High School} Description: name of the school Example: {0 OR 1 = > Male or Female} Description: gender of the student
graduation_date Type: date (mm/yyyy) Example: {5/2010} Description: graduation date from high school, college , etc
Table: Student_Logins *Student_id Type: int (auto-increment)
Example: {1,2,3…123,124,125…. ∞} Description: student id associated with each student. It is unique per student and is a primary key
username
Type: varchar(14)
Example: {aprathivadi, jacksmith45, etc} Description: the username associated with the student’s account
3
Prathivadi, Abhinav – Database Project Design [Inf 257]
password
Type: varchar (50)
Example: {aksjd81247897asdh18724, etc} Description: stores MD5 hash of the password the user set while registration
Table: Teacher_Logins *Teacher_id Type: int (auto-increment)
Example: {1,2,3…123,124,125…. ∞} Description: teacher id associated with each teacher. It is unique per teacher and is a primary key
username
Type: varchar(14)
Example: {MacArthur, Allenworth, etc} Description: the username associated with the teacher’s account
password
Type: varchar (50) Example: {aui18279104756, etc} Description: stores MD5 hash of the password the user set while registration
Table: Teachers *Teacher_id Type: int (auto-increment)
Example: {1,2,3…123,124,125…. ∞} Description: teacher id associated with each teacher. It is unique per teacher and is a primary key
school_name
Type: varchar (75)
Example: {Temecula Valley High School} Description: name of the school
firstName
Type: varchar (25) Example: {Jack, Mark, David, etc} Description: first name of the teacher
lastName
Type: varchar (25)
gender
Type: varchar (1)
email
Type: varchar (50)
Example: {Bauer, Heggerbond, Antopi} Description: last name of the teacher Example: {0 OR 1 = > Male or Female} Description: gender of the teacher Example: {[email protected], etc}
4
Prathivadi, Abhinav – Database Project Design [Inf 257] Description: email of the teacher date_of_birth
Type: date/time Example: {5/1/75} (mm/dd/yyyy) Description: date of birth of the teacher
Table: Assignments *Assgn_id Type: int (auto-incremen)
Example: {1,2,3…123,124,125…. ∞} Description: id of the assignment (unique – primary key)
name
Type: varchar (100)
description
Type: text (500)
dueDate
Type: mm/dd/yyyy Example: {11/5/2008 12:03:45,etc } hh:mm:ss Description: due date of the assignment
Class_id
Type: int Example: {1,2,3….} Description: the class this assignment is associated with
Table: Classes *Class_id
Example: {Inf 257 Database Deisng, etc} Description: name of the assignment Example: {This assignment covers what we did in class up till the midterm, etc } Description: description of the assignment
Type: int (auto-increment)
Example: {1,2,3…123,124,125…. ∞} Description: class id associated with each class. It is unique per class and is a primary key
title
Type: varchar (75) Example: {Database Management} Description: The title of the class
Teacher_id
Type: int (5)
start
Type: mm/dd/yyyy hh:mm:ss
Example: {1,2,3…123,124,125…. ∞} Description: teacher id associated with the class Example: {8/5/2008 5:32:1}
5
Prathivadi, Abhinav – Database Project Design [Inf 257] Description: time the class started end
Type: mm/dd/yyyy Example: {4/5/2009 5:32:1} hh:mm:ss Description: time the class ended
Table: Student_Grades G_id Type: int (auto-increment)
Example: {1,2,3…123,124,125…. ∞} Description: grade entry id; unique for each entry of an assignment
*Assgn_id
Type: int
Example: {1,2,3…123,124,125…. ∞} Description: assignment id this grade is related to
Student_id
Type: int
as_maxScore
Type: int Example: {100,200,50,75} Description: max score of the assignment
as_score
Type: int Example: {89,156,45,70} Description: the score this student received for the assignment
as_comments
Type: text (500)
Example: {1,2,3…123,124,125…. ∞} Description: student this grade is being entered for
Example: {“excellent work and quality. good job”} Description: comments associated with this student’s performance in this assignment
Table: Student_Attendance *Class_id Type: int (auto-increment)
Example: {1,2,3…123,124,125…. ∞} Description: id of the course (unique for each class)
*Student_id
Type: int (auto-increment)
Example: {1,2,3…123,124,125…. ∞}
Description: id of the student date
Type: mm/dd/yyyy
Example: {11/5/2008 12:03:45,etc }
6
Prathivadi, Abhinav – Database Project Design [Inf 257] hh:mm:ss Description: date this entry was updated/added status
Type: mm/dd/yyyy Example: {11/5/2008 12:03:45,etc } hh:mm:ss Description: status of the student (Present, Absent, Tardy)
comments
Type: text (500) Example: {11/5/2008 12:03:45,etc } Description: comments about the student’s attendance; reserved for things such as detention, unruly behavior or any other notes
Table: Class_Enrollment Student_id Type: int (auto-increment)
Example: {1,2,3…123,124,125…. ∞} Description: student id associated with each student. It is unique per student and is a primary key
Class_id
Type: int (auto-increment)
status
Type: varchar (20)
date
Type: mm/dd/yyyy Example: {11/5/2008 12:03:45,etc } hh:mm:ss Description: date this entry was updated/added
* - primary key
Example: {1,2,3…123,124,125…. ∞} Description: class id is associated with the class the student is enrolled in Example: {Enrolled, Wait-Listed, Dropped} Description: stores the status of the student’s enrollment; can vary depending on the class size allowed, etc
7
Prathivadi, Abhinav – Database Project Design [Inf 257]
ER Diagram of Gradebook
8
1 Abhinav Prathivadi Prof. Ray Larson Sep 25th, 2008
Personal Database Project Design Throughout the history of the Internet, the communications platform has produced many revolutions and has impacted other industries surrounding it by largely creating a level playing field. The academia has changed in profound ways in the last decade and the Internet has been pivotal in revolutionizing the way students have exchanged information, ideas and the interaction between teachers and students. A challenging task many people from the academia face is the approach towards Internet many schools and universities have taken over the last few years, automating most of their processes including student data and billing for students. The most important aspect of student data is the transcript (the grades associated with it) and an online integration of student grades and courses within an e-grade book is the purpose of this project and in the process streamline the accessibility and build an application to scale over many universities. This grade book is designed to streamline the process of entering and managing grades from a student as well as a teacher perspective. With a form based reporting system and tabulation of data online, updates to students and parents is very effective and convenient method of reporting grades. The purpose of this paper is to design a robust back-end capable of sustaining large amounts of data load as the grading processes of many universities and schools are time and data intensive work. By focusing on flexible and easy data entry into the gradebook, a lot of time is saved that can be productively put in other aspects of classroom interaction. Although the various entities have been documented (see next few pages) changes are inevitable as bottlenecks will be noticed
Prathivadi, Abhinav – Database Project Design [Inf 257]
2
and optimized to a high level of efficiency in the coming months. The idea of starting off with a rough sketch of the system helps shape the direction of the database system, and by gradually exploring different aspects of grading, various perspectives will emerge and subsequently the gradebook will evolve eventually to a scalable solution for universities to use. Students in many Universities and colleges are eager to know their grades almost immediately after submitting their assignment and the graders are overwhelmed with paperwork amounting to hundreds of papers per assignment per class. Although the amount of work is considerably swifter compared to the traditional calculator approach teachers used to take a decade back, the prospect of a web based grade book is exciting because of the potential communication platform it can setup between students and teachers. Although numerous grade books exist online, most of them are “downloadable” desktop grade book software that has become a pain for the users to use, as web based products are kicking into the industry. With the advent of Google Docs, Windows Office Live, etc. the academia has largely migrated to a web based software world where collaboration and search for information is moving predominantly online. The student – teacher collaboration outside the class room involves the management of numerous assignments and exams and to organize the grades has been a challenge for many years. The academia has developed over a period of time various in-house database systems like bSpace, etc, but an integrated free solution for teachers, students and schools of all sizes and domains is non existent and this project hopes to fill that gap and build a scalable and sustainable solution for an online gradebook system.
Prathivadi, Abhinav – Database Project Design [Inf 257]
Data Dictionary The entities involved in the database are Table: Students *Student_id Type: int (auto-increment)
Example: {1,2,3…123,124,125…. ∞} Description: student id associated with each student. It is unique per student and is a primary key
first_name
Type: varchar (25)
Example: {abhinav, adam, john, mark} Description: first name of the student
last_name
Type: varchar (25)
school_name
Type: varchar (75)
gender
Type: varchar (1)
date_of_birth
Type: date (mm/dd/yyyy) Example: {11/7/1988} Description: date of birth of the student
Example: {prathivadi, smith, henderson} Description: last name of the student Example: {Temecula Valley High School} Description: name of the school Example: {0 OR 1 = > Male or Female} Description: gender of the student
graduation_date Type: date (mm/yyyy) Example: {5/2010} Description: graduation date from high school, college , etc
Table: Student_Logins *Student_id Type: int (auto-increment)
Example: {1,2,3…123,124,125…. ∞} Description: student id associated with each student. It is unique per student and is a primary key
username
Type: varchar(14)
Example: {aprathivadi, jacksmith45, etc} Description: the username associated with the student’s account
3
Prathivadi, Abhinav – Database Project Design [Inf 257]
password
Type: varchar (50)
Example: {aksjd81247897asdh18724, etc} Description: stores MD5 hash of the password the user set while registration
Table: Teacher_Logins *Teacher_id Type: int (auto-increment)
Example: {1,2,3…123,124,125…. ∞} Description: teacher id associated with each teacher. It is unique per teacher and is a primary key
username
Type: varchar(14)
Example: {MacArthur, Allenworth, etc} Description: the username associated with the teacher’s account
password
Type: varchar (50) Example: {aui18279104756, etc} Description: stores MD5 hash of the password the user set while registration
Table: Teachers *Teacher_id Type: int (auto-increment)
Example: {1,2,3…123,124,125…. ∞} Description: teacher id associated with each teacher. It is unique per teacher and is a primary key
school_name
Type: varchar (75)
Example: {Temecula Valley High School} Description: name of the school
firstName
Type: varchar (25) Example: {Jack, Mark, David, etc} Description: first name of the teacher
lastName
Type: varchar (25)
gender
Type: varchar (1)
Type: varchar (50)
Example: {Bauer, Heggerbond, Antopi} Description: last name of the teacher Example: {0 OR 1 = > Male or Female} Description: gender of the teacher Example: {[email protected], etc}
4
Prathivadi, Abhinav – Database Project Design [Inf 257] Description: email of the teacher date_of_birth
Type: date/time Example: {5/1/75} (mm/dd/yyyy) Description: date of birth of the teacher
Table: Assignments *Assgn_id Type: int (auto-incremen)
Example: {1,2,3…123,124,125…. ∞} Description: id of the assignment (unique – primary key)
name
Type: varchar (100)
description
Type: text (500)
dueDate
Type: mm/dd/yyyy Example: {11/5/2008 12:03:45,etc } hh:mm:ss Description: due date of the assignment
Class_id
Type: int Example: {1,2,3….} Description: the class this assignment is associated with
Table: Classes *Class_id
Example: {Inf 257 Database Deisng, etc} Description: name of the assignment Example: {This assignment covers what we did in class up till the midterm, etc } Description: description of the assignment
Type: int (auto-increment)
Example: {1,2,3…123,124,125…. ∞} Description: class id associated with each class. It is unique per class and is a primary key
title
Type: varchar (75) Example: {Database Management} Description: The title of the class
Teacher_id
Type: int (5)
start
Type: mm/dd/yyyy hh:mm:ss
Example: {1,2,3…123,124,125…. ∞} Description: teacher id associated with the class Example: {8/5/2008 5:32:1}
5
Prathivadi, Abhinav – Database Project Design [Inf 257] Description: time the class started end
Type: mm/dd/yyyy Example: {4/5/2009 5:32:1} hh:mm:ss Description: time the class ended
Table: Student_Grades G_id Type: int (auto-increment)
Example: {1,2,3…123,124,125…. ∞} Description: grade entry id; unique for each entry of an assignment
*Assgn_id
Type: int
Example: {1,2,3…123,124,125…. ∞} Description: assignment id this grade is related to
Student_id
Type: int
as_maxScore
Type: int Example: {100,200,50,75} Description: max score of the assignment
as_score
Type: int Example: {89,156,45,70} Description: the score this student received for the assignment
as_comments
Type: text (500)
Example: {1,2,3…123,124,125…. ∞} Description: student this grade is being entered for
Example: {“excellent work and quality. good job”} Description: comments associated with this student’s performance in this assignment
Table: Student_Attendance *Class_id Type: int (auto-increment)
Example: {1,2,3…123,124,125…. ∞} Description: id of the course (unique for each class)
*Student_id
Type: int (auto-increment)
Example: {1,2,3…123,124,125…. ∞}
Description: id of the student date
Type: mm/dd/yyyy
Example: {11/5/2008 12:03:45,etc }
6
Prathivadi, Abhinav – Database Project Design [Inf 257] hh:mm:ss Description: date this entry was updated/added status
Type: mm/dd/yyyy Example: {11/5/2008 12:03:45,etc } hh:mm:ss Description: status of the student (Present, Absent, Tardy)
comments
Type: text (500) Example: {11/5/2008 12:03:45,etc } Description: comments about the student’s attendance; reserved for things such as detention, unruly behavior or any other notes
Table: Class_Enrollment Student_id Type: int (auto-increment)
Example: {1,2,3…123,124,125…. ∞} Description: student id associated with each student. It is unique per student and is a primary key
Class_id
Type: int (auto-increment)
status
Type: varchar (20)
date
Type: mm/dd/yyyy Example: {11/5/2008 12:03:45,etc } hh:mm:ss Description: date this entry was updated/added
* - primary key
Example: {1,2,3…123,124,125…. ∞} Description: class id is associated with the class the student is enrolled in Example: {Enrolled, Wait-Listed, Dropped} Description: stores the status of the student’s enrollment; can vary depending on the class size allowed, etc
7
Prathivadi, Abhinav – Database Project Design [Inf 257]
ER Diagram of Gradebook
8
