Question on Tables & Relationships

[Kevin Labore]

Hi

First Where are some good places to find example on relationships. I have
written several simple relational databases. The one I am currently working
is a bit more complicated than I have done before.

This database will track grades for students, class, course, college. In
the past I have written a spreadsheet (and modified it every semester) it
did the job, but I wanted something more for reporting.

If this post should be posted some other place, please excuse my error, and
I will repost appropriately

Here is a rundown of what I need the database or
My Wife is an adjunct professor and teaches at up to 4 college per semester.
She can teach the same course at either the same college or different
college in the same semester, the same student could be taking more than one
of here classes.

My Basic design is as follows for tables:

Assignments (AssignID, AssignTypeID, CourseID, ClassID, CollegeID,Assign
Desc(paper1, quiz2, etc), AssignValue(in % of total grade(10%))
AssignmentType (AssignTypeID, AssignTypeDesc(test, quiz, paper, etc) )
Classes(ClassID, CourseID, CollegeID,SemesterID,ClassYearID, ClassDesc(Intro
Fall2006))
Colleges(CollegeID, CollegeName)
Grades(GradeID,CollegeID, CourseID, ClassID, AssignID,StudentID,
Grade(actual grade for the assignment))
ClassYear(ClassYearID, ClassYear(2005,2006, etc)
Semester(SemID,Semster(Fall, Spring, Summer, etc)
Students(StudentID, Student_FName, Student_LName, etc)
StudClass(StudClassID,StudentID,ClassID) (What Students are in what classes)
Courses(CourseID, CourseName, CourseNum(Text ie. PSC-101), CollegeID

I think I am trying to be "too relational" --- my thinking was I wanted to
be to list or enter data in different ways
(i.e.. enter grades for ALL Students for ONE particular assignment)
(show ALL students in a particular class)
(i.e.. enter ALL Assignments for one Class/college)

some other things that could happen a student could with draw from a
particular class
The sum of the assignments for a particular class must = 1(100%)

This was all nicely down with a simply spreadsheet that took me about an
hour to create.
However I want to experiment with a challenging database but I am lost on
where to go
I am open to some suggestions.

Thanks in advance for any help you can offer

Kevin

 

[Smartin]

Hi

First Where are some good places to find example on relationships. I have
written several simple relational databases. The one I am currently working
is a bit more complicated than I have done before.

This database will track grades for students, class, course, college. In
the past I have written a spreadsheet (and modified it every semester) it
did the job, but I wanted something more for reporting.

If this post should be posted some other place, please excuse my error, and
I will repost appropriately

Here is a rundown of what I need the database or
My Wife is an adjunct professor and teaches at up to 4 college per semester.
She can teach the same course at either the same college or different
college in the same semester, the same student could be taking more than one
of here classes.

My Basic design is as follows for tables:

Assignments (AssignID, AssignTypeID, CourseID, ClassID, CollegeID,Assign
Desc(paper1, quiz2, etc), AssignValue(in % of total grade(10%))
AssignmentType (AssignTypeID, AssignTypeDesc(test, quiz, paper, etc) )
Classes(ClassID, CourseID, CollegeID,SemesterID,ClassYearID, ClassDesc(Intro
Fall2006))
Colleges(CollegeID, CollegeName)
Grades(GradeID,CollegeID, CourseID, ClassID, AssignID,StudentID,
Grade(actual grade for the assignment))
ClassYear(ClassYearID, ClassYear(2005,2006, etc)
Semester(SemID,Semster(Fall, Spring, Summer, etc)
Students(StudentID, Student_FName, Student_LName, etc)
StudClass(StudClassID,StudentID,ClassID) (What Students are in what classes)
Courses(CourseID, CourseName, CourseNum(Text ie. PSC-101), CollegeID

I think I am trying to be "too relational" --- my thinking was I wanted to
be to list or enter data in different ways
(i.e.. enter grades for ALL Students for ONE particular assignment)
(show ALL students in a particular class)
(i.e.. enter ALL Assignments for one Class/college)

some other things that could happen a student could with draw from a
particular class
The sum of the assignments for a particular class must = 1(100%)

This was all nicely down with a simply spreadsheet that took me about an
hour to create.
However I want to experiment with a challenging database but I am lost on
where to go
I am open to some suggestions.

Thanks in advance for any help you can offer

Kevin

Hi Kevin

At first whiff it looks like you have given quite a bit of thought to
and have a great start on your design. I will defer hard core comments
to the more experienced, but have a few suggestions to take or leave...

Colleges have Courses (join = CollegeID). Courses have classes (join =
CourseID). This much looks great. But in Classes you have CollegeID,
which seems redundant. Same thing in Assignments and Grades.

By the same token, including CourseID in Assignments and Grades seems
redundant since these link back to Courses by way of ClassID.

Does Classes need a description, or is this better placed at the Courses
level?

I would consider using natural keys for Semester and ClassYear instead
of autonumbers (though some I'm sure will refute this!).

HTH and let us know how it pans out!

 

[John Vinson]

First Where are some good places to find example on relationships.

Jeff Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Especially consider the Database Design 101 link on Jeff's page.

John W. Vinson[MVP]

 

[Kevin Labore]

Hi John

thanks for the links I will check them out.

Kevin

 

[Kevin Labore]

Hi Kevin

At first whiff it looks like you have given quite a bit of thought to
and have a great start on your design. I will defer hard core comments
to the more experienced, but have a few suggestions to take or leave...

Colleges have Courses (join = CollegeID). Courses have classes (join =
CourseID). This much looks great. But in Classes you have CollegeID,
which seems redundant. Same thing in Assignments and Grades.

By the same token, including CourseID in Assignments and Grades seems
redundant since these link back to Courses by way of ClassID.

is there an advantage to having redundancy? I thought it might be neccessary/helpful for some inputs or reports

Does Classes need a description, or is this better placed at the Courses
level?

Classes should have a desc as for example my wife is teaching 2 Intro Classes (same course same classes (different time and day))
so the Desc might be Intro Mon Eve, and Intro Tue Eve for class

once I get the data in, I could use Pivotables (excel) for some reports.

I would consider using natural keys for Semester and ClassYear instead
of autonumbers (though some I'm sure will refute this!).

Out of courosity why use natural keys in this case(less fields?)

HTH and let us know how it pans out!

Thanks for sharing your thoughts.
Kevin

 

[Smartin]

is there an advantage to having redundancy? I thought it might be
neccessary/helpful for some inputs or reports

Your call. My gut says you can always get at the parent ID with a join,
but perhaps it is simpler to retrieve it directly from the child. There
are times, it is said, when denormalizing leads to efficiency. This
might be one of those times. Again, I defer to those with more
experience than me (^:

Classes should have a desc as for example my wife is teaching 2 Intro
Classes (same course same classes (different time and day))
so the Desc might be Intro Mon Eve, and Intro Tue Eve for class

Sounds like a case for breaking down the Classes table a bit further,
e.g., to the day/time slot level. Would you like to track exactly who
attends every session? Do students have some flexibility regarding what
day/times they attend for a class?

once I get the data in, I could use Pivotables (excel) for some reports.


Out of courosity why use natural keys in this case(less fields?)

Yes, but again, it's your call. My gut feeling is, adding a meaningless
autonumber just seems to complicate matters. When you have a discrete
list of values to choose from, it makes sense to me to use those values
as the key. This is a contentious subject however.

FWIW I gave some thought to your model and constructed an ERM (entity
relationship diagram). PDF is here:
http://vfdrake.home.comcast.net/files/students.pdf
The arrows point from the many-side to the one-side of the
relationships. Based on your descriptions of Courses and Classes I may
not have modeled this bit correctly.

The joining tables StudentClass and StudentGrade sould either have the
OptionalPK or a PK built up from the FKs.

I hope it helps!