Gradebook Databse Setup

[Lewis Clark]

Hello!

I currently use Excel to hold my gradebook (each semester is a different
workbook, with a worksheet for each class), and since I would like to start
learning how to use Access, I would like to create a gradebook database. I
currently teach at three colleges, and in some cases I teach the same course
at more than one college. I don't expect that I will have the same student
at more than one college, but will certainly have the some of the same
students in more than 1 class at a particular college.

I would like a single database to hold all student grade and course data for
every class I teach.

I know that the table structure is important, and would like to get some
advice before I start creating the tables. I think I need the four main
tables with the fields below. The numbers in parentheses are the number of
different fields for that data type.

Main Table Fields:

Student ID # (Primary key)

First name

Middle Initial

Last name

College

Phone

Eail



Class Table Fields: (Will need one table for each class I teach each
semester to hold the actual grade data. This will mean adding 6-10 tables
every semester depending on how many classes I teach.)

Manual Data Entry Fields

Student ID #

Quiz grades (5 separate quizzes, so will need 5 fields)

Quiz Max Score (5 fields)

Homework grades (5)

HW Max Score (5)

Class Participation (15)

Participation Max Score

Exams (3)

Exam Max Score (3)

Weight of each item (about 6 fields)

Extra credit points (if any)

Grade curve (if any)

Grade Awarded (Will usually be the same as the Grade Earned below, but may
be adjusted upward)



Calculated Fields

Participation Grade

Quiz average

HW average

Exam score in % (3)

Overall average

Grade Earned





Course List Table Fields:

College

Course ID (ex: MAT 0024)

Course Name (ex: Algebra)





Grade Scale Table Fields:

Overall average

Grade Earned (some kind of lookup table to automatically find the Grade
Earned based on the Overall average. Something similar to Vlookup in Excel)



Is my goal reasonable, given that I will have to add a bunch of new tables
each semester? I hope to be able to copy and paste the new tables since
they would be very similar to tables previously used. I think I would need
a new table for each class each semester because the mix of assignments and
weights for each will vary on occasion.



Thank you in advance for any advice you can provide.

 

[Ed Warren]

If your are 'adding tables' and/or 'adding columns' your design is not right
(period, end of discussion)

Sounds like you have:
students
colleges
courses
college-semester (each college has a different semester, tri-mester
arrangement)
(college-semester)(course)
courseEnrollment (people in a course taught during a semester)
grades.

each college has many semesters, each semester has many courses, each
Semester/course has many student enrollment, each student (enrollment) has
many grades in a course. Each grade is earned by a student, enrolled in a
course, during a semester, at a college. (their final grade is calculated
based on the grades earned during the semester).

Also might want to pull out email and phone as a 'child table' of students.
at least some students will have multiple phones and multiple email
addresses. They also may need to have more than one address (daddy, mommy,
school)

The above logic should put you in the right ball-park. Table normalization
is both easy and complex!

Lots of luck

Ed Warren

 

[tina]

I know that the table structure is important, and would like to get

some advice before I start creating the tables.

you're right, Lewis. designing the tables/relationships is the FIRST, *and*
most important, step in developing your database. suggest you stop where you
are, step away from your computer, and read up on relational data modeling.

to learn to do it right, so you don't have to keep re-doing and re-doing it
all school year long, one good text is Database Design For Mere Mortals by
Michael Hernandez. you can also find helpful links (as well as tons of links
to many other aspects of database development) at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
beginning with the Starting Out and Database Design 101 links.

these newsgroups are also an excellent resource, when you have a specific
problem or question that you need a hand with.

hth

 

[Lewis Clark]

Tina and Ed,

Thank you for your replies. I'm off to the library and/or bookstore before
I go any further.

Lewis

 

[Guest]

But wait.....here's some free resources that you can access from home:

Database Design
You should spend some time gaining an understanding of database design and
normalization before attempting to build something in Access (or any RDBMS
software for that matter). Here are some links to get you started. Don't
underestimate the importance of gaining a good understanding of database
design. Brew a good pot of tea or coffee and enjoy reading!

http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf

http://www.datadynamicsnw.com/accesssig/downloads.htm
(See the last download titled "Understanding Normalization")

http://support.microsoft.com/?id=234208

http://support.microsoft.com/?id=289533

Also recommended: Find the copy of Northwind.mdb that is probably already
installed on your hard drive. Study the relationships between the various
tables (Tools > Relationships...)


Naming Conventions
Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm
http://www.xoc.net/standards/default.asp

Using a Naming Convention
http://msdn.microsoft.com/library/d...us/odeopg/html/deconusingnamingconvention.asp


Reserved Words
Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266


Don't attempt to store the results of any calculations. Doing so violates
both 2nd and 3rd normal form of database design. To amplify a bit on what Ed
Warren stated:

"If your are 'adding tables' and/or 'adding columns' your
design is not right (period, end of discussion)"

The need to add new fields or tables to accomodate similar data should be a
big red flag. Something to remember about good database design: "Fields are
expensive, records are cheap". Anytime you need to add more fields (or
tables) you will need to modify existing queries, forms and reports.


Tom
________________________________________

:

Tina and Ed,

Thank you for your replies. I'm off to the library and/or bookstore before
I go any further.

Lewis