Help with Simple Database for Science Fair

[MisterPeters]

Hi!

I am a teacher who has been appointed "tech guy" for our school science fair. I'm trying to build a database to handle the information and number crunching for the event. I've been working with Access, but we have Filemaker too. I'm pretty tech savvy, for a teacher (or so I thought!), but this database design has got me stumped!

My plan was to create a Database with these tables/forms (to cover the basic info):

Projects (Fields: ID#, ProjectTitle, Student Names)

Judges (Fields: ID#, JudgesName)

And to create another table for the evaluation forms themselves, where each record is an evaluation form submitted by a judge, more or less matching the papers that the judges fill out.

Evaluations (Fields: ProjectTitle (from Projects) JudgesName (from Judges), Criteria1 (a score out of 4), Criteria2 (a score out of 4), etc ....

So far, so good... (maybe?)

The difficulty I'm having is creating the following reports:

A report that collects and averages all of the evaluations for each project, producing an overall average score for the project. (An individual project report)

A report that takes the overall averages for each project, producing a list of the projects sorted by the overall average score. (ie. who won)

I know this seems like a pretty basic question - hopefully the answer is pretty basic. My eyes are bleeding from watching tutorials, but I can't seem to get a handle on it.

If someone can help me out with these reports (or suggest a better approach altogether), or has a sample of something similar that I can look at, I would be extremely grateful!

Thanks for your time!

[griswolf]

You will want something like this (I have not double checked syntax, have not mentioned the obvious foreign keys, and didn't note any indexes):

create table project (
  id unsigned not null auto_increment,
  when date, /* so you can have the same title at the next fair */
  title varchar(128) not null
);
create table student (
  id unsigned auto_increment,
  name_first varchar(64),
  name_last  varchar(64),
  school_name varchar(64)
  /* maybe some contact info? */
);

create table project_student (
  project_id integer,
  student_id integer,
  student_role varchar(256),
  comment varchar(256)
);
create table judge (
  /* pretty much just like table student */
);

create table evaluation (
  project_id integer,
  judge_id integer,
  score_1 integer,
  score_2 integer,
  /* ... */
  score_n integer
);

• The project cannot have student names directly since there may be a variable count of students, and at least in principle, a student can be in more then one project. Hence the table project_student that associates students to projects (and allows you to say something about the nature of the association)
• Please do not use the plural 'criteria' to refer to one criterion. Better to avoid the fancy words and use 'score' as I have shown in table evaluation.
• MySql on some OSs does not distinguish case in table and column names. Best to use lower_case names.

For the first report, you want to

SELECT p.title, avg(e.score_1) as score_1, avg(e.score_2) as score_2 from project p join evaluation e on e.project_id = p.id where p.when = '2010-11-01' group by e.project_id

(or something pretty close to that) For the second report, you can use the sum() function on the avg(e.score_N) and add ORDER BY clause (order by the sum, DESCending).
I don't have time to create all this and test it (and I apologize in advance for the fact that I didn't have time to check the syntax either). Let us know if you get close enough to need some specific help.

[MisterPeters]

Thanks for the quick and super-detailed response! I have to admit, I find some of the coding and syntax little intimidating, up until now I've just been clicking at Access buttons. But I can already see the major areas where I was going wrong. Time for a MySQL crash course, i guess! I'm going to give it the ole' college try.

[griswolf]

Upon further consideration: Considering that judges are volunteers and human; and that scoring is probably not a tightly controlled system, the evaluation table probably needs a comment varchar(1024), column. You may also want to add a weight for each score (maybe) and for each judge (may not be politically feasible). The sql to generate the reports then becomes even hairier (not impossibly much), but closer to reality.

[Ezzaral]

> I find some of the coding and syntax little intimidating, up until now I've just been clicking at Access buttons

You can certainly still use Access for the database. The table creation code translates pretty easily to your table creation wizard or design view. The SQL queries may require a bit more effort to translate, but the query designers in Access can be helpful when you're just getting started.