DB Design suggestions

[Anon]

I am a teacher/techie(newbie) and need to create a simple way of printing an ineligible list each 5 weeks. The criteria for being ineligible are as follows...

IF you are failing 2 or more classes for the first time this year, you are ineligible for a period of 2 weeks.

IF you are failing 2 or more classes (may be different than previous offenses) for the second or more time this year, you are ineligible for a period of 5 weeks.

I export the list of failures from our administrative software every 5 weeks and will "INSERT" it into the appropriate tables so I can "SELECT" 2-week offenders and 5-week offenders appropriately.

Any suggestions on the tables and their design would be appreciated. I figured I would need 1 or 2 tables.

Please reply directly to me since I have little time to "surf" and I do read my mail each day deveral times.

TIA,
Mike
[e-mail]

[cdhogan]

Provided you are running a MySQL server this should work, if your not, the logic is still the same. (Yes, I know its not php, but you can modify)

Charles

Student Table - To keep track of each student

You can also put in a field with the school's id for the

student - to make tracking easier, and less prone to errors

create table student(
id int not null primary key auto_increment,
name varchar(60));

Period table is just so that you can name periods

without all that excess typing or wasted space

create table period(
id int not null primary key auto_increment,
per_name varchar(40));

The one the Students won't like 🙂

create table num_fails(
id int not unll primary key auto_increment,
stu_id int,
period_id int,
num_fail int);

########################################################

Input Statements - Replace '?' With Appropriate Data

########################################################

Period, if you choose to use

id is automatically generated

insert into period (per_name) values('?');

Student, Only use this the first time you enter the students data

Add the school's id field if you wish,

You can probably write a script to generate the input

satements to make it easier

id is automatically generated

Insert into student (name) values ('?');

Enter Number of Failing Classes

First Get the cross refference id for the student

select id, name from student where name = '?';

With that id number use following

insert into num_fail (stu_id, period_id, num_fail) values (?,?,?);

Note quotes not needed as these are integers not strings

Search query - sample perl script

#!/usr/bin/perl

sample perl to get students failing

use DBI;

Initialize Variables

my ($db,$server,$dbh);
my ($query, , $query2, $criteria);
me ($name, $id, $count, $fail);

Set Default Values

$db = ?;

Insert whatever you name the d-base

$server = "127.0.0.1";
$criteria = 1

$dbh = DBI->connect("DBI:mysql:$db:$server", undef, undef);

while($criteria le 2){
if ($criteria eq 1){
print "\n2 Week Ineligibility List\n";
}else{
print "\n\n\n5 Week Ineligibility List\n";
}

$query = $dbh->prepare("select id, name from student");
$query->execute();
($id, $name) = @{$query->fetch};

while($id){
$count = 0;
$query2 = $dbh->prepare("select id from num_fails where num_fail >= 2 and stu_id = ?");
$query2->execute($id);
($fail) = @{$query2->fetch};
while($fail){
$count += 1
($fail) = @{$query2->fetch};
}
$query2->finish;

if($criteria eq 1){
if($count eq 1){
print "$name\n";
}
if($criteria eq 2){
if ($count ge 2){
print "$name\n";
}

($id, $name) = @{$query->fetch};
}
$query->finish;
$criteria += 1;
}
$dbh->disconnect;

exit();