FrontPage

The stakeholders in this program are the parents who want to monitor their child’s success and study habits in this area. Other stakeholders are the teachers who want to be able to more vividly instruct and monitor their students as well as keep them motivated and interested in learning. Lastly, but most importantly, are the students who will learn more efficiently about spelling.

Why are you building this system?

We want to provide a tool to assist teachers with teaching, students with learning and parents with teaching and monitoring.

Who will use it?

The parents, teachers and students who attend classes who want to learn better spelling and English skills will use the program.

What does it do?

The program will allow the teacher or parent of a student to record and enter words into the system. The teacher will then be able to create quizzes of 10 words and assign them to a student or class of students. The students will be able to take the quizzes and then the teacher and parent will be able to check the grades of their assigned students.

Names and Contact Information for Group Members

Name

E-mail Address

Olga Abouazza

olgaAbouazza@yahoo.com

Tyrell Baker

baker@envisioncu.com

Kimberly Fowler

kimberleyfowler69@yahoo.com

Ryan Pendry

ryan.pendry@gmail.com

Gleb Teper

glebteper@gmail.com

Introduction Statement - 3-4 sentences overview of entire project

Our proposed project is a web-based application designed to help children improve their spelling abilities. The target usage for this program will be a school environment in which teachers and parents can use the software to create spelling quizzes for students. The program can also be used in households for parents to spend important learning time with their children. Our main objective is to provide an effective solution to spelling education which can be used by teachers, parents, and students to both improve and track spelling ability.

Purpose - why are you creating this database?

The purpose of this project is for our group to use a database management system and the SQL programming language to design a useable program that multiple users can enter or retrieve information from while maintaining the integrity of the data. Our group has chosen to do this by building the “Anna’s Spelling Helper” application. This application is meant to be useable by teachers to create spelling quizzes for students using words and saved sound files. Additionally, the application can be used by students to take the spelling quizzes and by parents to monitor their child’s grades and progress.

Background - where are you going to acquire information about the database you are designing, people and resources? What standards will apply?

For the design of the spelling helper application, our group will be looking for the most suitable technologies to implement the project design. We believe that parents and teachers would be the most valuable resource to provide information for the desired outcome. If the educators enjoy the application, then the children will as well. During their early years, children can benefit from simple and user-friendly applications to learn spelling.

Resources: The internet will be our best friend to accomplish this desired application. http://www.dictionary.com is a website where the whole dictionary is available for us to add our words.

Standards: We will follow the NIEM naming conventions when naming the fields used in the database creation. These standards are available at http://www.niem.gov.

Outcome - What will be the final product of your work?

Our expected final outcome is the completion and distribution of a fully functional spelling helper application. We anticipate that our application will be able to correctly perform all expected tasks and that we can proudly broaden awareness of the application to schools and households. We will have a complete data dictionary to serve as a reference for future updates and to ensure that our database design is efficient and encompasses all of our goals. Our final application documentation will include stored procedures, plans for security, indexing, and maintenance, and database schema diagrams and scripts. We plan for this work to be deployed in an approved database product. The education that this application provides for students is an intangible final product, yet is also possibly the most important product of all.

Since your documentation is required to be turned in on a wiki or web site, the location of the site should be specified.

Here is the URL of our wiki web site: http://cet4429group4.pbwiki.com

Data Dictionary

Teacher Table - stores the teacher's first and last name, with an identity-specification enabled TeacherID. The TeacherSchoolID is an ID assigned by the school and only exists to uniquely identify teachers in the database (similar to a Social Security Number).

Attributes

Data Type

Null/Not Null

Notes

TeacherID (PK)

int

Not Null

Identifies The teacher Table

TeacherSchoolID (AK)

int

Not Null

A Unique Constraint

TeacherSurName

varchar(50)

Not Null

Identifies Teacher’s Last Name

TeacherGivenName

varchar(50)

Not Null

Identifies Teacher’s First Name

Student Table - ID-spec enabled PK, StudentSchoolID is same purpose as TeacherSchoolID.

Attribute

Data Type

Null/Not Null

Notes

StudentID (PK)

int

Not Null

Identifies the Student Table

TeacherID (FK)

int

Not Null

References the primary key in Teacher

ParentID (FK)

int

Not Null

References the primary key in Parent

StudentSchoolID (AK)

int

Not Null

Unique Constraint

StudentSurName

varchar(50)

Not Null

Identifies the Student Last Name

StudentGivenName

varchar(50)

Not Noll

Identifies the Student Given Name

StudentBirthDate

datetime

Not Null

Identifies the Student Birthday

ClassID

int

Not Null

Identifies the Student Class List

Parent Table - ID-spec enabled PK - The parent table stores information for a parent. Parents are uniquely identified by a ParentID which is the primary key. The parent table also stores a ParentSurName and a ParentGivenName. Parents are referenced to a student using the StudentID foreign key.

Attribute Name

DataType

Null/Not Null

Notes

ParentID (PK)

int

Not Null

Identifies Parents

ParentSurName

varchar(50)

Not Null

Identifies Parent Last Name

ParentGivenName

varchar(50)

Not Null

Identifies Parent First Name

StudentID (FK)

int

Not Null

References the primary key in students

Word Table - ID-spec enabled PK, the word table stores all of the words, their definitions, and the locationn of the sound associated with each word. Each word is identified by a primary key wordID.

Attribute

Data Type

Null/ Not Null

Notes

WordID (PK)

int

Not Null

Identifies the Word table

WordNameText (AK)

varchar(50)

Not Null

Unique Constraint/the actual word

WordDefinitionText

varchar(50)

Null

Definition of word

SoundLocationText

varchar(50)

Not Null

URL to the sound file

QuizWords Table - The QuizWords table uses StudentQuizID as a unique identifier which is the primary key for the table. This table allows a teacher to create a Quiz by entering a StudentQuizID, TeacherID, and QuizDurationTime. In addition, the teacher enters 10 quiz words for the quiz.

Attribute

DataType

Null/NotNull

Notes

StudentQuizID(PK)

int

Not Null

Identifies Quiz Words Table

TeacherID (FK)

int

Not Null

References the primary key in Teacher

QuizDurationTime

datetime

Not Null

The Length of the Quiz

QuizWord1

char(30)

Not Null

The First Word in Quiz

QuizWord2

char(30)

Not Null

The Second Word in Quiz

QuizWord3

char(30)

Not Null

The Third Word in Quiz

QuizWord4

char(30)

Not Null

The Fourth Word in Quiz

QuizWord5

char(30)

Not Null

The Fifth Word in Quiz

QuizWord6

char(30)

Not Null

The Sixth Word in Quiz

QuizWord7

char(30)

Not Null

The Seventh Word in Quiz

QuizWord8

char(30)

Not Null

The Eighth Word in Quiz

QuizWord9

char(30)

Not Null

The Ninth Word in Quiz

QuizWord10

char(30)

Not Null

The Tenth Word in Quiz

Results Table- ID-spec enabled PK, Stores the results of the students attempts to spell the words they hear (QuizResponse#) and also the physical words themselves (Question#Spelling) comparison and grading. StudentQuizID, StudentID and TeacherID are foreign keyed to this table to connect the student and teacher to the quiz being taken and the results.

Attribute

Data Type

Null/ Not Null

Notes

QuizResultsID (PK)

int

Not Null

Identifies Results Table

StudentQuizID (FK)

int

Not Null

References the primary key in QuizWords

StudentID (FK)

int

Not Null

References the primary key in Students

TeacherID (FK)

int

Not Null

References the primary key in Teacher

QuizResponse1

varchar(50)

Not Null

The Student response to First word

QuizResponse2

varchar(50)

Not Null

The Student response to Second word

QuizResponse3

varchar(50)

Not Null

The Student response to Third word

QuizResponse4

varchar(50)

Not Null

The Student response to Fourth word

QuizResponse5

varchar(50)

Not Null

The Student response to Fifth word

QuizResponse6

varchar(50)

Not Null

The Student response to Sixth word

QuizResponse7

varchar(50)

Not Null

The Student response to Seventh word

QuizResponse8

varchar(50)

Not Null

The Student response to Eighth word

QuizResponse9

varchar(50)

Not Null

The Student response to Ninth word

QuizResponse10

varchar(50)

Not Null

The Student response to Tenth word

Question1Spelling

varchar(50)

Not Null

The first word in the quiz

Question2Spelling

varchar(50)

Not Null

The Second word in the quiz

Question3Spelling

varchar(50)

Not Null

The Third word in the quiz

Question4Spelling

varchar(50)

Not Null

The Fourth word in the quiz

Question5Spelling

varchar(50)

Not Null

The fifth word in the quiz

Question6Spelling

varchar(50)

Not Null

The Sixth word in the quiz

Question7Spelling

varchar(50)

Not Null

The Seventh word in the quiz

Question8Spelling

varchar(50)

Not Null

The Eighthword in the quiz

Question9Spelling

varchar(50)

Not Null

The Ninth word in the quiz

Question10Spelling

varchar(50)

Not Null

The Tenth word in the quiz

The Student Results Table – ID-spec enabled PK, Stores the StudentQuizID (quiz assigned to the student), the Results of the Quiz (QuizResultsID) and the QuizScore and ResultsDateTime (when the quiz was taken). This allows the PK Identity field, StudentResults ID, to show the foreign keys (Student ID, StudentQuizID, QuizResultsID) how well or poorly the student did on the quiz and the time it took to complete it.

Attribute

Data Type

Null/Not Null

Notes

StudentResultsID (PK)

int

Not Null

Identifies the Student Results Table

StudentID (FK)

int

Not Null

Refences the primary key in student

StudentQuizID (FK)

int

Not Null

Refences the primary key in QuizWords

QuizResultsID (FK)

int

Null

Refences the primary key in Results Table

QuizScore

int

Null

This will be calculates Using the stored procedure

ResultsDateTime

datetime

Null

The Results Time

Student Progress Table – ID-spec enabled PK, Stores the progress of each student as they complete the quizzes assigned by the teachers with an Identity-enabled StudentProgressID. The Student ID and ParentID are foreign keys to their respective tables so that the NumberofQuizzesTaken and NumberofQuizzesPassed can be recoreded for each Student and enabled for each Student and Parent to see.

Attribute

Data Type

Null/Not Null

Notes

StudentProgressID (PK)

int

Not Null

Identifies the Student Progress Table

StudentID (FK)

int

Not Null

References The Primary key in Student

ParentID (FK)

int

Not Null

References The Primary key in Parent

NumberOfQuizzesTaken

int

Not Null

The Number of times the quiz is taken

NumberofQuizzesPassed

int

Null

The Number of times the quiz is passed

Database Schema Scripts

The Parent Table Script

USE [GroupProject_CET4429]

/****** Object: Table [dbo].[Parent] Script Date: 04/21/2008 02:38:00 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[Parent](

[[ParentID] [int] IDENTITY(1,1) NOT NULL,

[StudentID] [int] NULL,

[ParentSurName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[ParentGivenName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED

(

[ParentID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_PADDING OFF

ALTER TABLE [dbo].[Parent] WITH CHECK ADD CONSTRAINT [FK_Parent_Student] FOREIGN KEY([StudentID])

REFERENCES [dbo].[Student] ([StudentID])

ALTER TABLE [dbo].[Parent] CHECK CONSTRAINT [FK_Parent_Student]

The QuizWords Table Script

USE [GroupProject_CET4429]

/****** Object: Table [dbo].[QuizWords] Script Date: 04/21/2008 02:38:17 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[QuizWords](

[StudentQuizID] [int] IDENTITY(1,1) NOT NULL,

[TeacherID] [int] NOT NULL,

[QuizDurationTime] [datetime] NOT NULL,

[QuizWord1] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[QuizWord2] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[QuizWord3] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[QuizWord4] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[QuizWord5] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[QuizWord6] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[QuizWord7] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[QuizWord8] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[QuizWord9] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[QuizWord10] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [PK_QuizWords] PRIMARY KEY CLUSTERED

(

[StudentQuizID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_PADDING OFF

ALTER TABLE [dbo].[QuizWords] WITH CHECK ADD CONSTRAINT [FK_QuizWords_Teacher] FOREIGN KEY([TeacherID])

REFERENCES [dbo].[Teacher] ([TeacherID])

ALTER TABLE [dbo].[QuizWords] CHECK CONSTRAINT [FK_QuizWords_Teacher]

The Results Table Script

USE [GroupProject_CET4429]

/****** Object: Table [dbo].[Results] Script Date: 04/21/2008 02:38:32 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[Results](

[QuizResultsID] [int] IDENTITY(1,1) NOT NULL,

[StudentQuizID] [int] NOT NULL,

[StudentID] [int] NOT NULL,

[TeacherID] [int] NOT NULL,

[QuizResponse1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[QuizResponse2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[QuizResponse3] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[QuizResponse4] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[QuizResponse5] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[QuizResponse6] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[QuizResponse7] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[QuizResponse8] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[QuizResponse9] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[QuizResponse10] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Question1Spelling] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Question2Spelling] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Question3Spelling] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Question4Spelling] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Question5Spelling] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Question6Spelling] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Question7Spelling] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Question8Spelling] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Question9Spelling] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Question10Spelling] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [PK_Results] PRIMARY KEY CLUSTERED

(

[QuizResultsID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_PADDING OFF

ALTER TABLE [dbo].[Results] WITH CHECK ADD CONSTRAINT [FK_Results_QuizWords] FOREIGN KEY([StudentQuizID])

REFERENCES [dbo].[QuizWords] ([StudentQuizID])

ALTER TABLE [dbo].[Results] CHECK CONSTRAINT [FK_Results_QuizWords]

ALTER TABLE [dbo].[Results] WITH CHECK ADD CONSTRAINT [FK_Results_Student] FOREIGN KEY([StudentID])

REFERENCES [dbo].[Student] ([StudentID])

ALTER TABLE [dbo].[Results] CHECK CONSTRAINT [FK_Results_Student]

ALTER TABLE [dbo].[Results] WITH CHECK ADD CONSTRAINT [FK_Results_Teacher] FOREIGN KEY([TeacherID])

REFERENCES [dbo].[Teacher] ([TeacherID])

GO ALTER TABLE [dbo].[Results] CHECK CONSTRAINT [FK_Results_Teacher]

The Student Table Script

USE [GroupProject_CET4429]

/****** Object: Table [dbo].[Student] Script Date: 04/21/2008 02:38:39 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[Student](

[StudentID] [int] IDENTITY(1,1) NOT NULL,

[TeacherID] [int] NOT NULL,

[ParentID] [int] NOT NULL,

[StudentSchoolID] [int] NOT NULL,

[StudentSurName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[StudentGivenName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[StudentBirthDate] [datetime] NOT NULL,

[ClassID] [int] NULL,

CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED

(

[StudentID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

CONSTRAINT [StudentConstraint] UNIQUE NONCLUSTERED

(

[StudentSchoolID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_PADDING OFF

ALTER TABLE [dbo].[Student] WITH CHECK ADD CONSTRAINT [FK_Student_Parent1] FOREIGN KEY([ParentID])

REFERENCES [dbo].[Parent] ([ParentID])

ALTER TABLE [dbo].[Student] CHECK CONSTRAINT [FK_Student_Parent1]

ALTER TABLE [dbo].[Student] WITH CHECK ADD CONSTRAINT [FK_Student_Teacher1] FOREIGN KEY([TeacherID])

REFERENCES [dbo].[Teacher] ([TeacherID])

ALTER TABLE [dbo].[Student] CHECK CONSTRAINT [FK_Student_Teacher1]

Student Progress Table Script

USE [GroupProject_CET4429]

/****** Object: Table [dbo].[StudentProgress] Script Date: 04/21/2008 02:38:46 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[StudentProgress](

[StudentProgressID] [int] IDENTITY(1,1) NOT NULL,

[StudentID] [int] NOT NULL,

[ParentID] [int] NOT NULL,

[NumberOfQuizzesTaken] [int] NOT NULL,

[NumberofQuizzesPassed] [int] NULL,

CONSTRAINT [PK_StudentProgress] PRIMARY KEY CLUSTERED

(

[StudentProgressID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

ALTER TABLE [dbo].[StudentProgress] WITH CHECK ADD CONSTRAINT [FK_StudentProgress_Parent] FOREIGN KEY([ParentID])

REFERENCES [dbo].[Parent] ([ParentID])

ALTER TABLE [dbo].[StudentProgress] CHECK CONSTRAINT [FK_StudentProgress_Parent]

ALTER TABLE [dbo].[StudentProgress] WITH CHECK ADD CONSTRAINT [FK_StudentProgress_Student] FOREIGN KEY([StudentID])

REFERENCES [dbo].[Student] ([StudentID])

ALTER TABLE [dbo].[StudentProgress] CHECK CONSTRAINT [FK_StudentProgress_Student]

The Student Results Table Script

USE [GroupProject_CET4429]

/****** Object: Table [dbo].[StudentResults] Script Date: 04/21/2008 02:38:56 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[StudentResults](

[StudentResultsID] [int] IDENTITY(1,1) NOT NULL,

[StudentID] [int] NOT NULL,

[StudentQuizID] [int] NOT NULL,

[QuizResultsID] [int] NULL,

[QuizScore] [int] NULL,

[ResultsDateTime] [datetime] NULL,

CONSTRAINT [PK_StudentResults] PRIMARY KEY CLUSTERED

(

[StudentResultsID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

ALTER TABLE [dbo].[StudentResults] WITH CHECK ADD CONSTRAINT [FK_StudentResults_QuizWords] FOREIGN KEY([StudentQuizID])

REFERENCES [dbo].[QuizWords] ([StudentQuizID])

ALTER TABLE [dbo].[StudentResults] CHECK CONSTRAINT [FK_StudentResults_QuizWords]

ALTER TABLE [dbo].[StudentResults] WITH CHECK ADD CONSTRAINT [FK_StudentResults_Results] FOREIGN KEY([QuizResultsID])

REFERENCES [dbo].[Results] ([QuizResultsID])

ALTER TABLE [dbo].[StudentResults] CHECK CONSTRAINT [FK_StudentResults_Results]

ALTER TABLE [dbo].[StudentResults] WITH CHECK ADD CONSTRAINT [FK_StudentResults_Student] FOREIGN KEY([StudentID])

REFERENCES [dbo].[Student] ([StudentID])

ALTER TABLE [dbo].[StudentResults] CHECK CONSTRAINT [FK_StudentResults_Student]

The Teacher Table Script

USE [GroupProject_CET4429]

/****** Object: Table [dbo].[Teacher] Script Date: 04/21/2008 02:39:03 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[Teacher](

[TeacherID] [int] IDENTITY(1,1) NOT NULL,

[TeacherSchoolID] [int] NOT NULL,

[TeacherSurName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[TeacherGivenName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [PK_Teacher] PRIMARY KEY CLUSTERED

(

[TeacherID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

CONSTRAINT [TeacherConstraint] UNIQUE NONCLUSTERED

(

[TeacherSchoolID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_PADDING OFF

The Word Table Script

USE [GroupProject_CET4429]

/****** Object: Table [dbo].[Word] Script Date: 04/21/2008 02:39:10 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[Word](

[WordID] [int] IDENTITY(1,1) NOT NULL,

[WordNameText] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[WordDefinitionText] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SoundLocationText] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [PK_Word] PRIMARY KEY CLUSTERED

(

[WordID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

CONSTRAINT [WordConstraint] UNIQUE NONCLUSTERED

(

[WordNameText] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_PADDING OFF

Database Schema Diagram

Schema of all views (and diagrams)

Parent View

Written by – Ryan Pendry

Purpose - This View allows for Parents to view their child’s (Student’s) progress by displaying columns from the StudentProgress table.

USE [GroupProject_CET4429]

/****** Object: View [dbo].[ParentView] Script Date: 04/21/2008 01:03:33 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE VIEW [dbo].[ParentView]

SELECT dbo.Student.StudentID, dbo.Student.StudentSurName, dbo.Student.StudentGivenName, dbo.StudentProgress.NumberOfQuizzesTaken,

dbo.StudentProgress.NumberofQuizzesPassed

FROM dbo.StudentProgress

INNER JOIN

dbo.Student ON dbo.StudentProgress.StudentID = dbo.Student.StudentID

Student View

Written by – Ryan Pendry

Purpose - This View allows for Students to view their results of a quiz attempt.

USE [GroupProject_CET4429]

/****** Object: View [dbo].[StudentView] Script Date: 04/21/2008 01:10:58 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE VIEW [dbo].[StudentView]

SELECT dbo.Student.StudentID, dbo.Student.StudentSurName, dbo.Student.StudentGivenName, dbo.Results.QuizResultsID, dbo.Results.QuizResponse1,dbo.Results.Question1Spelling, dbo.Results.QuizResponse2,dbo.Results.Question2Spelling, dbo.Results.QuizResponse3,dbo.Results.Question3Spelling, dbo.Results.QuizResponse4,dbo.Results.Question4Spelling, dbo.Results.QuizResponse5,dbo.Results.Question5Spelling, dbo.Results.QuizResponse6,dbo.Results.Question6Spelling, dbo.Results.QuizResponse7,dbo.Results.Question7Spelling, dbo.Results.QuizResponse8,dbo.Results.Question8Spelling, dbo.Results.QuizResponse9,dbo.Results.Question9Spelling, dbo.Results.QuizResponse10, dbo.Results.Question10Spelling

FROM dbo.Results INNER JOIN

dbo.Student ON dbo.Results.StudentID = dbo.Student.StudentID

Teacher View

Written by – Ryan Pendry

Purpose - This View allows for Teachers to view the results of a Student’s quiz attempt.

USE [GroupProject_CET4429]

/****** Object: View [dbo].[StudentView] Script Date: 04/21/2008 01:05:48 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE VIEW [dbo].[TeacherView]

SELECT dbo.Student.StudentID, dbo.Student.StudentSurName,

dbo.Student.StudentGivenName, dbo.Results.QuizResultsID,

dbo.Results.QuizResponse1, dbo.Results.Question1Spelling,

dbo.Results.QuizResponse2, dbo.Results.Question2Spelling,

dbo.Results.QuizResponse3, dbo.Results.Question3Spelling,

dbo.Results.QuizResponse4, dbo.Results.Question4Spelling,

dbo.Results.QuizResponse5, dbo.Results.Question5Spelling,

dbo.Results.QuizResponse6, dbo.Results.Question6Spelling,

dbo.Results.QuizResponse7, dbo.Results.Question7Spelling,

dbo.Results.QuizResponse8, dbo.Results.Question8Spelling,

dbo.Results.QuizResponse9, dbo.Results.Question9Spelling,

dbo.Results.QuizResponse10, dbo.Results.Question10Spelling

FROM dbo.Results INNER JOIN

dbo.Student ON dbo.Results.StudentID = dbo.Student.StudentID

Documentation of all stored procedures

AddNewStudent Stored Procedure

Written by - Tyrell Baker / Gleb Teper

Purpose - This Stored Procedure adds each new Students’ Name, Birthdate, School and ClassID’s as well as their Parents. If a teacher wishes to enter a class of students or enter a student into a certain class, the teacher will use a ClassID such as ClassID 1, and that attribute will be added to the Student

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[AddNewStudent]

@StudentSurName varchar(50),

@StudentGivenName varchar(50),

@StudentBirthDate varchar(50),

@ParentSurName varchar(50),

@ParentGivenName varchar(50),

@ClassID INT, @StudentSchoolID INT

DECLARE @ID1 AS INT

BEGIN

INSERT INTO Student

(StudentSurName, StudentGivenName, StudentBirthDate, ClassID, StudentSchoolID)

VALUES

(@StudentSurName, @StudentGivenName, @StudentBirthDate, @ClassID, @StudentSchoolID)

SET @ID1 = @@IDENTITY

-------------------------------------------------------------

INSERT INTO Parent

(StudentID, ParentSurName, ParentGivenName)

VALUES

(@ID1, @ParentSurName, @ParentGivenName)

END

AddNewTeacher Stored Procedure

Written by - Tyrell Baker

Purpose - This Stored Procedure adds the Teacher’s First, Last Name and School ID into the database.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[AddNewTeacher]

@TeacherSurName varchar(50),

@TeacherGivenName varchar(50),

@TeacherSchoolID int

BEGIN

INSERT INTO Teacher

(TeacherSurName, TeacherGivenName, TeacherSchoolID)

VALUES

(@TeacherSurName, @TeacherGivenName, @TeacherSchoolID)

--------------------------------------------------------------

END

AddNewWord Stored Procedure

Written by - Tyrell Baker

Purpose - This Stored Procedure adds each a new Word by inserting the WordNameText, WordDefinitionText, and SoundLocationText values.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[AddNewStudent]

@StudentSurName varchar(50),

@StudentGivenName varchar(50),

@StudentBirthDate varchar(50),

@ParentSurName varchar(50),

@ParentGivenName varchar(50),

@ClassID INT, @StudentSchoolID INT

DECLARE @ID1 AS INT

BEGIN

INSERT INTO Student

(StudentSurName, StudentGivenName, StudentBirthDate, ClassID, StudentSchoolID)

VALUES

(@StudentSurName, @StudentGivenName, @StudentBirthDate, @ClassID, @StudentSchoolID)

SET @ID1 = @@IDENTITY

-------------------------------------------------------------

INSERT INTO Parent

(StudentID, ParentSurName, ParentGivenName)

VALUES

(@ID1, @ParentSurName, @ParentGivenName)

END

AssignQuiz Stored Procedure

Written by – Gleb Teper / Group

Purpose - This Stored Procedure assigns a QuizID to a Student. Upon running the stored procedure, a StudentResultsID is generated in the StudentResults table after which StudentID and StudentQuizID are added to the StudentResults Table. Upon completion of the Quiz and Teacher grading, the remaining fields in the StudentResults table are updated by the QuizGrade Stored Procedure.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[AssignQuiz] (

@StudentID int,

@StudentQuizID int

)

DECLARE @StudentQuizID1 INT

BEGIN TRANSACTION

SELECT @StudentQuizID1 = StudentQuizID FROM QuizWords

WHERE StudentQuizID = @StudentQuizID

If @studentQuizID = @StudentQuizID1

BEGIN

INSERT INTO StudentResults

(StudentID, StudentQuizID, ResultsDateTime)

VALUES

(@StudentID, @StudentQuizID, getdate())

COMMIT

END

CreateQuiz Stored Procedure

Written by - Group

Purpose - This Stored Procedure Adds the words chosen by the Teacher into the Quiz for a Student. The words are selected from drop down lists. The drop down lists contain every word in the Words table.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[CreateQuiz]

@Word1 varchar(50),

@Word2 varchar (50),

@Word3 varchar (50),

@Word4 varchar (50),

@Word5 varchar (50),

@Word6 varchar (50),

@Word7 varchar (50),

@Word8 varchar (50),

@Word9 varchar (50),

@Word10 varchar (50)

DECLARE @ID1 AS INT

BEGIN

INSERT INTO QuizWords

(StudentQuizID, quizWord1, quizWord2, quizWord3, quizWord4, quizWord5, quizWord6, quizWord7, quizWord8, quizWord9, quizWord10)

VALUES

(@ID1, @Word1, @Word2, @Word3, @Word4, @Word5, @Word6, @Word7, @Word8, @Word9, @Word10)

SET @ID1 = @@IDENTITY

end

ParentNames Stored Procedure

Written by - Ryan Pendry

Purpose - This Stored Procedure allows for Parent names to be properly formatted in a dropdown list.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[ParentNames]

BEGIN

SELECT dbo.Parent.ParentGivenName + ' ' + dbo.Parent.ParentSurName as 'ParentName', dbo.Parent.ParentID as 'ParentID'

FROM Parent

END

ParentViewProgress Stored Procedure

Written by – Ryan Pendry, Olga Abouazza

Purpose - This Stored Procedure allows for variables to pass to the Parent view through the use of the stored procedure.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER Procedure [dbo].[ParentViewProgress]

@StudentID int,

@ParentID int

BEGIN

SELECT dbo.Student.StudentID,

dbo.Student.StudentSurName,

dbo.Student.StudentGivenName,

dbo.StudentProgress.NumberOfQuizzesTaken,

dbo.StudentProgress.NumberofQuizzesPassed

FROM dbo.StudentProgress

INNER JOIN

dbo.Student ON dbo.StudentProgress.StudentID = dbo.Student.StudentID INNER JOIN dbo.Parent ON dbo.StudentProgress.ParentID = dbo.Parent.ParentID

WHERE

dbo.StudentProgress.StudentID = @StudentID and dbo.Parent.ParentID = @ParentID

END

PassedQuiz Stored Procedure

Written by - Group

Purpose - This Stored Procedure counts the number of quizzes taken by the students by StudentID and adds 1 attempt for each quiz taken. It also adds in the Number of Quizzes Passed if the score is greater than 70.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[PassedQuiz]

BEGIN

INSERT INTO StudentProgress

(NumberofQuizzesTaken)

VALUES

(1)

SELECT NumberofQuizzesPassed =

CASE

WHEN QuizScore >= 70 THEN '1'

WHEN QuizScore <= 70 THEN '0'

END

FROM StudentProgress

END

QuizAttempts Stored Procedure

Written by - Group

Purpose - This Stored Procedure captures the words that were typed by the student and placed into the QuizResponse# fields. It also imports the actual correct spelling into the Question#Spelling fields.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[QuizAttempts]

@QuizWord1 varchar(50), @QuizResponse1 varchar(50),

@QuizWord2 varchar(50), @QuizResponse2 varchar(50),

@QuizWord3 varchar(50), @QuizResponse3 varchar(50),

@QuizWord4 varchar(50), @QuizResponse4 varchar(50),

@QuizWord5 varchar(50), @QuizResponse5 varchar(50),

@QuizWord6 varchar(50), @QuizResponse6 varchar(50),

@QuizWord7 varchar(50), @QuizResponse7 varchar(50),

@QuizWord8 varchar(50), @QuizResponse8 varchar(50),

@QuizWord9 varchar(50), @QuizResponse9 varchar(50),

@QuizWord10 varchar(50), @QuizResponse10 varchar(50)

BEGIN

INSERT INTO Results

(QuizResponse1, QuizResponse2, QuizResponse3, QuizResponse4, QuizResponse5, QuizResponse6, QuizResponse7, QuizResponse8, QuizResponse9, QuizResponse10,

Question1Spelling, Question2Spelling, Question3Spelling, Question4Spelling, Question5Spelling, Question6Spelling, Question7Spelling, Question8Spelling, Question9Spelling, Question10Spelling)

VALUES

(@QuizResponse1, @QuizResponse2, @QuizResponse3, @QuizResponse4, @QuizResponse5, @QuizResponse6, @QuizResponse7, @QuizResponse8, @QuizResponse9, @QuizResponse10,

@QuizWord1, @QuizWord2, @QuizWord3, @QuizWord4, @QuizWord5, @QuizWord6, @QuizWord7, @QuizWord8, @QuizWord9, @QuizWord10)

END

QuizGrade Stored Procedure

Written by - Tyrell Baker

Purpose - This Stored Procedure takes the grade entered for the quiz by the Teacher and places into the StudentResults table as the students grade for the attempt. The date that the results were entered is also placed into the ResultsDateTime column.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[QuizGrade]

@Grade int

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

UPDATE StudentResults.QuizScore

SET StudentResults.QuizScore = @Grade

END

StudentNames Stored Procedure

Written by - Ryan Pendry

Purpose - This Stored Procedure allows for Student names to be properly formatted in a dropdown list.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[StudentNames]

BEGIN

SELECT dbo.Student.StudentGivenName + ' ' + dbo.Student.StudentSurName as 'StudentName', dbo.Student.StudentID as 'StudentID'

FROM Student

END

StudentQuizResultsView Stored Procedure

Written by – Ryan Pendry, Olga Abouazza

Purpose - This Stored Procedure allows for variables to pass to the Student view through the use of the stored procedure.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER Procedure [dbo].[StudentQuizResultsView]

@StudentID int,

@StudentQuizID int

BEGIN

SELECT dbo.Student.StudentID, dbo.Student.StudentSurName, dbo.Student.StudentGivenName, dbo.Results.QuizResultsID,

dbo.Results.QuizResponse1, dbo.Results.Question1Spelling, dbo.Results.QuizResponse2, dbo.Results.Question2Spelling,

dbo.Results.QuizResponse3, dbo.Results.Question3Spelling, dbo.Results.QuizResponse4, dbo.Results.Question4Spelling,

dbo.Results.QuizResponse5, dbo.Results.Question5Spelling, dbo.Results.QuizResponse6, dbo.Results.Question6Spelling,

dbo.Results.QuizResponse7, dbo.Results.Question7Spelling, dbo.Results.QuizResponse8, dbo.Results.Question8Spelling,

dbo.Results.QuizResponse9, dbo.Results.Question9Spelling, dbo.Results.QuizResponse10

FROM dbo.Results INNER JOIN

dbo.Student ON dbo.Results.StudentID = dbo.Student.StudentID

WHERE

dbo.Results.StudentID = @StudentID and dbo.Results.StudentQuizID = @StudentQuizID

END

TeacherNames Stored Procedure

Written by - Ryan Pendry

Purpose - This Stored Procedure allows for Teacher names to be properly formatted in a dropdown list.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[TeacherNames]

BEGIN

SELECT dbo.Teacher.TeacherGivenName + ' ' + dbo.Teacher.TeacherSurName as 'TeacherName', dbo.Teacher.TeacherID as 'TeacherID'

FROM Teacher

END

TeacherQuizResultsView Stored Procedure

Written by – Ryan Pendry, Olga Abouazza

Purpose - This Stored Procedure allows for variables to pass to the Teacher view through the use of the stored procedure.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER Procedure [dbo].[TeacherQuizResultsView]

@StudentID int,

@TeacherID int

BEGIN

SELECT dbo.Student.StudentID, dbo.Student.StudentSurName, dbo.Student.StudentGivenName, dbo.Results.QuizResultsID,

dbo.Results.QuizResponse1, dbo.Results.Question1Spelling, dbo.Results.QuizResponse2, dbo.Results.Question2Spelling,

dbo.Results.QuizResponse3, dbo.Results.Question3Spelling, dbo.Results.QuizResponse4, dbo.Results.Question4Spelling,

dbo.Results.QuizResponse5, dbo.Results.Question5Spelling, dbo.Results.QuizResponse6, dbo.Results.Question6Spelling,

dbo.Results.QuizResponse7, dbo.Results.Question7Spelling, dbo.Results.QuizResponse8, dbo.Results.Question8Spelling,

dbo.Results.QuizResponse9, dbo.Results.Question9Spelling, dbo.Results.QuizResponse10

FROM dbo.Results

INNER JOIN

dbo.Student ON dbo.Results.StudentID = dbo.Student.StudentID

INNER JOIN

dbo.Teacher ON dbo.results.TeacherID = dbo.teacher.TeacherID

WHERE

dbo.Results.StudentID = @StudentID and dbo.Results.TeacherID = @TeacherID

END

Documentation of code plan

How Would This Work?

If we build an interface in Visual Studio, we would have a Master page, with three Menus

Teacher Menu
Student Menu
Parent Menu

Each user will have to enter a password to insure privacy. The Teacher will have SchoolTeacherID as a login, and a password will be setup. The Student will have SchoolStudentID as a login, and password will be setup. The Parent will use the SchoolStudentID as a login and a password will be set up.

If the teacher menu is clicked, the teacher can choose to do these tasks in a new page:

Create Sound File
Create Spelling List
Assign Quizzes
Can Create a list of students
Can see the results of student attempts at a quiz

1) Can create a sound file for any spelling word and save this sound file

3 input text boxes for WordNameText, WordDefinitionText, SoundLocationText

submit button to send to database

The teacher will record the word, save it in a folder, the URL will be saved in Word Table using SoundLocationText field. The Stored procedure AddNewWord will run when the submit button is clicked, with WordNameText Field including a Unique Constraint. The Unique constraint will ensure that no word is repeated.

2) Can create spelling lists from sound files (of words)

Using the stored Procedure CreateQuiz, the teacher can create quizzes

input: 10 drop down lists with all of the words

2 text boxes for TeacherID, QuizDurationTime

submit button to send to database

3) Can assign spelling lists and quizzes to students in a class

Using the stored procedure AssignQuiz, the teacher will be able to assign a quiz to a student.

input: drop down list to select quiz, drop down list to select student

submit button to send to database

4) Can create a list of student users as a class

Using the stored procedure AddNewStudent, The teacher can create a list of students as a class, to ensure that no student is entered twice; the StudentSchoolID is a unique constraint. At the same the parents information will be added.

input: 5 text boxes for StudentSurName, StudentGivenName, StudentBirthDate, ClassID, StudentSchoolID

2 text boxes for ParentSurName, ParentGivenName

submit button to send to database

5) Can see the results of student attempts at a quiz

Using the stored Procedure QuizAttempts, the teacher will be able to view the quiz attempt and compare between, the correct answer and the entered answer.

If the Student Menu is chosen, then student can choose to:

View Your Quiz Results

gridview shows the correct answers and entered answers

input: 1 text box for Grade

submit button to send to database

After quizzes are added using the AssignQuiz Procedure, the student will be able to click on the quizzes that have been assigned to the same StudentID, and attempt the quiz.

Using the StudentQuizResultView stored Procedure; the student will be able to view the previous attempts.

The student can view his/her progress using the ParentViewProgress stored Procedure

input: 2 text boxes for StudentID and StudentQuizID

submit button to send to database

If the Parent Menu is chosen, then the parent can choose to:

The Parent has the option to

View the results of Student Progress

input: 2 input boxes for StudentID and ParentID

submit button to send to database

The Parents are assigned to the students when the teacher adds a new student. Once the parent is in the system, they are able to view the progress using ParentViewProgress stored procedure. The parent will be able to view the NumberOfQuizzesTaken, the NumberOfQuizzesPassed. The parent can only view their children’s progress report.

Security plan

(Roles and access levels to tables, views, stored procedures. List all roles and user types.)

1) Administrator

The administrator has full administrative access to the entire database including all tables, view and stored procedures.

Since the database will be deployed in SQL server that runs on Windows, then the SQL server Authentication along with windows authentication, can be used to secure that database.
A login name and password will be needed to make any changes, or updates to the database.

2) Teacher(s)

Can create a sound file for any spelling word and save this sound file.

Can create spelling lists from sound files (of words).

Can assign spelling lists and quizzes to students in a class.

Can create a list of student users as a class.

Can see the results of student attempts at a quiz.

The Teacher will be assigned a login and a password by the administrator.

The privileges will be set where the teacher can modify any of the above tasks.

The Teacher login will be the TeacherSchoolID.

3) Student(s)

Can see and attempt spelling quizzes.

Can see results of all past quiz attempts.

The Student will be assigned so that he/she can take a quiz.

The login will be the StudentSchoolID.

The student will only be able to take the quizzes that have been assigned by the teacher.

Also, the student will only be able to views his/her progress report.

4) Parent(s)

Can view results of student progress for a student or students to which they are assigned.

The Parent can only views the progress report using StudentSchoolID as login.

The parent can’t assign test.

How will appropriate access be ensured?

USING ADO.NET, we can create logins and passwords, and within the C# or VB, code the conditions for how does what

Windows authentication

Windows authentication will be used to assure the login/password combinations needed for the Teachers and Students to log into the system.

This integrated security feature makes it easier to restrict access on the system by allowing the Teachers Read/Write type access to the system, as well as Read/Write access for the Students and Read access for the Parents. Also, we can restrict which areas they have Read/Write access to by how the files of the system are saved and accessed.

Indexing plan and maintenance plan

1) What will be indexed?

The Primary Keys of all the tables in the database are indexed and referenced at different times to keep concurrency throughout the stored procedures that are running against it. We decided that due to the actual number of users and the nature of our program that it is not necessary to implement Indexing. We do not anticipate the number of students, teachers, or parents to reach an amount that would benefit from indexing. In addition, While we do anticipate a lot of words, the words will be updated on a very frequent basis which would make indexing impractical.

2) How will you monitor performance?

The Activity Monitor gives a snapshot of the current database activity on the server.

We can monitor who is logged in, what processes are running and how much system resources are being used.

Also, we can set the Database Mail function to monitor and notify us when the database reaches a certain file size and/or when someone tries to access the system without the right login credentials too many times.

$C:\Documents and Settings\tyrell\Desktop\untitled.bmp$

3) Who will be responsible for maintaining?

Maintenance should be handled by a skilled Database Administrator to oversee the size of the database and if the backup and maintenance logs are showing any errors.

Additionally, the DBA can prepare a disaster recovery plan so the data is not lost.

4) What access will Maintenance have?

A Database Maintenance Plan can be setup in SQL Management Studio to remove the excess space from the database, monitor the transactions on the database, reorganize/rebuild the indexes, and backup the database on a regular schedule. Database Admin will have access to the Database itself through a Database Management system such as SQL Management Studio. This can all be handled with the Maintenance Plan Wizard.

$C:\Documents and Settings\tyrell\Desktop\untitled.bmp$ $C:\Documents and Settings\tyrell\Desktop\untitled.bmp$

Proof of deployment in approved database product

Teacher Table in SQL Server

Student Table In SQL Server

QuizWords Table In SQL Server

Stored Procedures in Object Explorer

Comments (0)

You don't have permission to comment on this page.

To join this workspace, request access.

Already have an account? Log in!

Loading…

This is your Sidebar

Edit this! Put links and content here.

Loading…

FrontPage

Background requirements

Data Dictionary

Database Schema Scripts

Database Schema Diagram

Schema of all views (and diagrams)

Documentation of all stored procedures

Security plan

Indexing plan and maintenance plan

1) What will be indexed?

Proof of deployment in approved database product

FrontPage

Page Tools

Insert links

Comments (0)

Join this workspace

Navigator

SideBar

Recent Activity

FrontPage

Background requirements

Data Dictionary

Database Schema Scripts

Database Schema Diagram

Schema of all views (and diagrams)

Documentation of all stored procedures

Security plan

Indexing plan and maintenance plan 1) What will be indexed?

Proof of deployment in approved database product

FrontPage

Page Tools

Insert links

Comments (0)

Join this workspace

Navigator

SideBar

Recent Activity

Indexing plan and maintenance plan

1) What will be indexed?