Need The Basic Structure

Carmy,

Thanks,  

That is a lot of info for an Access neophite (yeah, newbie but with a wee more dignity :-]
) to consume - I can see the logic flow - I want to try and tie your table structure to a conceptual model so I can see how it would flow - then I will either get back to you with a grade or another brick in the wall (question!)

Thanks,

mapper

Carmy,

How do I tie this into a conceptual model?

mapper

Carmy,

I got the DB to function with only 5 tables and set the relationships accordingly - now I want to add a couple of input features to allow students to register and generate a couple of reports - and add grades to students - how hard would that be?

mapper

Not getting any follow up feedback -

I have five tables:

Student:
student ID# (Pri Key)
Last Name
First Name

Course:
Course ID# (P-Key)
Course Name
Department ID# (Foreign Key)
Start Time
Stop Time

Department:
Department ID# (P-Key)
Department Name

Instructor:
Instructor ID# (P-Key)
Last Name
First Name

StudentCourseLink:
Student ID#  (Pri-Key)
Course ID#   (Pri-Key)
Instructor ID#  (F-Key)
Grade
Quarter


Do these keys/relationships look okay?  IF not how should I set them up so I can run the following queries:

Class sked for all students

Students on probation (Any grade < 1.0)

Students on honor roll (GPA > 3.7)

Prof. sked (By class or department)

Average grade assigned by Prof. (by quarter or class) - i.e. all class taught by a Prof. for the quarter or the average GPA of any class taught by a Prof.

All students by department

Carmy,

Wow!  That's a lot of SQL - I am guessing since you only mentioned the quarter relationship that the rest were okay?  I was wondering what was going on with the response that's why I cleared (rejected) the question wondering if that would clear up the problem.  My main malfunction is that I am taking a DB management course and have NO Access experience - Professor Numbnutz is not very good at tying what he wants us to know (SQL queries & how to mangage a DB) to actually doing it with Access.  He will not give any assistance at all on the Access program to make his projects work!  The first part of my question was the first part of the project - I turned it in w/o having the two queries for the average grade assigned by Prof. for a particular class or for all classes in one quarter.  So I am probably going to get whacked pretty good on that project.  Now, we have to grow that part of the DB from the first part to add:

Data Entry:

Allow students to register
Schedule Professors for classes
Enter Course Grades

Reports:

Students on probation by major (new feature) the report can be one big one
Honor Roll students (same here - can be one big report)
Report card by student

All of the above has to be rolled into a User Interface.

That is why I added the class time and need to add student major.

And to top it off, I have to add options for backup and recovery of the database...

Man, from bad to worse!!

mapper

Mapper!
I tought this is a class question, I use to be a lecturer myself...
To answer the first question, your structure is not correct in the pure sense of the term as the assosiation between Prof and Course can only exist with a student, which in fact is not always true.  Some courses will have no student at all (none of them will have student when they are first set up!)  your structure is deficiant in this way, but it CAN work.
As to Registration, a student need only know the course they require not the Prof.
Your basic table structure should use the format I gave you to accommodate all the requirments of the second part of the assignment.

Carmy;

I think I see what you mean - about the Instructor - Course - Student relationships

That's a very good point - I was only thinking about the end result and not the up front scenarios (that you pointed out!)

Hum,  now I will have to grab a cold one and ponder what you have told me...

I am rebuilding the database to encompass the extra elements for the third phase (input and reports) for major and start - stop times for the classes - I was neating up the ID# numbers so they all tied together better too...

Well, at least it's raining today, tomorrow and the next couple of days - so I won't feel like I am missing anything - the kid's soccer season ended last weekend so no responsibility there :-]

I am almost done with adding the extra input - then I will re-do the queries and then start on the input forms and getting those into shape - I am not looking forward to building the table that will let me update the database - we have not even talked about that and the book for Access is very low keyed and only covers the absolute basics...

I am off to chew on your thoughts and see what I can do...

thanks,

mapper

Carmy,

I was able to construct all of the queries using the "New - Design View" and then opening up the right combination of tables - and then all was fine the only one I can not get are the two queries for Average grade given by Instructor per class and Average grade given by Instructor per quarter (average grade for one class taugh and all classes taught by the instructors) - I tried using your SQL suggestion and got the following error:

The Micro$oft Jet Database engine could not execute the SQL statement because it contains a field that has an invalid data type

Any suggestions - we have not covered how to implement the Average expression...

mapper

If you changed the logical structure of the database then the queries I gave you will not work you will need to reconstuct them.  The error you are getting is because a FK in one table is not the same type as the PK in the other.

Use the queries I gave you to see how the Avg is implemented

Carmy,

I finally figured out what I was doing wrong...  I had the GRADE data set up at Text (DUH!) so as soon as I changed that to data - BAM!!  it worked just fine  - I used the Simple Query Wizard to set up the queries (for the two average queries) and they worked great!  

The only thing I was wondering about is backing up the database - what would be the conceptual model to set up something like that?  Would you show a a conceptual model of the orignal database and then have some sort of parallel system (storage wise) to capture all changes (additions to the database) to the database?

I am confused on that one...

mapper

As this is Access, the best way is to backup the entire MDB file.  Its the Server databases like Oracle and SQL etc that offer a method for transaction logs and and roll back and so.  Access also have a Replication method available that actually can act as transaction log as it allow you to replicate the database to other location when ever you want.  this system is very effective if your database is open and used all the time.  Dont dwell on this issue for too long there are probably not enough points in it...