Student Tardy Calculator

[Taichi56]

I have work in a high school and I am working on designing a database for a tardy calculator. It will be Internet based. I am not real sure how I am going to set up the database though. So I though I would throw it out there and get suggestions.

Student Table
ID
StuID (this is a number assigned to them by the district)Primary Key
LastName
FirstName
GradeLvl
DOB
Gender

"ClassTime" Table
StuID FK
FistPeriod (the actual times would go here also)
SecondPeriod
ThirdPeriod
FourthPeriod
FifthPeriod
SixthPeriod
SeventhPeriod

This would be hosted on our web server. One person would input the information. When a student is tardy the person would click on their name and then click on the correct class period. Then this would be stored for the year. After getting five tardies they then would go to detention. Not sure how to flag that though. Any suggestions would be great. I am not programmer, just learning out of need. Please advise. Thank you.

[tesuji]

Hi,

student's table is OK (see note below) but classtime table is a bit poor (for theory lovers: it doesnt even fulfill first normal form, 1NF).

You can simply try out whether this table functions well or badly by creating a sql query (select) to answering this question: How often was student Taichy56 too late in class and how many minutes was he absent? Well, how could such query select look like?

A better idea for this table could be:

classtime
---------
stuID   int          -- student's number
dati    timestamp    -- the date/time the student was late
pernb   int          -- 1, 2, 3 ... your period number
tarti   integer      -- how much minutes too late

Primary key: (stuID, dati)
Foreign key: stuID references Student

Above table looks somewhat complicated but is ideal for evaluating by sql queries (even it fulfills 1NF up to 3NF).

This is how my answer to the differencing good/bad question would look like:

select FirstName, LastName, count(*) as HowOften, sum(tarti) as TartiMinutes
 from Student s join Classtime c on s.stuID = c.stuID 
   where c.stuID = stuID_of_Taichi56 [B]group by[/B] FirstName, LastName;

Note: If unique, primary key of Student's table should be StuID as you already stated. Therefore the never-missing "ID" is useless and should be dropped.

-- tesu

[tesuji]

I should seriously observe the following to my proposed classtime table:

If pernb, the period number 1,2 etc, is unequivocally derived from dati, the timestamp column, then there would be a partial dependency dati ---> pernb what strictly speaking means that second normal form is violated. Well, seems to be kind of nit-pickery :D

I also came up with the almost interesting question on how to figure out which students are considered to be late riser. If we decide that students which have more than as 30% tardy time falling in first and second period be late riser, the following query might give us the crucial answer:

select c1.stuID, FirstName, LastName, 
 (select sum(tarti) from classtime c2 where c1.stuID = c2.stuID) as TotalTardy, 
 (select sum(tarti) from classtime c3 where c1.stuID = c3.stuID 
     AND ((c3.pernb=1) or (c3.pernb=2))) as Period_1_and_2,
 case  TotalTardy
    when TotalTardy > 0 then cast(100*Period_1_and_2/TotalTardy as decimal(5,1))
    else 0
 end as inPercent 
 from classtime c1 join student s1 on c1.stuID = s1.stuID
    where inPercent > 30 order by inPercent, TotalTardy desc;

Attention: this code not tested ;)

How to find that out by the inital classtime table?

-- tesu

[Taichi56]

Thank you very much. I will give it a try.

[Garfield89]

hi Taichi56!!
i'm interested in the tardy calculator project that u are working on because now i'm doing my final year project and looking for what system i'm going to develop. I would like to have a brief understanding of the Tardy Calculator(how it works), could u explain that for me??

your help is greatly appreciated!!

[Stylish]

How is this system to be used? Your Student table does not list the School the Student attends - is this ok? How large is the District? Will you be producing District-wide reports or individual Schools? Are all actions based on StudentId (primary key) or a multitude of columns (other indexes)? Where do you want performance - UPDATE/INSERT or SELECTs or is speed not a real priority? Do you want to track the name of each Period (and perhaps the Teacher)?

tesuji's "classtime" table will be good for transactions driven by StudentId.