linking tables

[SONCH]

Hi!
I wish to design a data base to manage student enrollment and their Test scores.


I have two tables:
1) the enrollment table which caries: StdId; StdName; Sex; Class etc
NB: each Class has 2 streams i.e. Class 1A; 1B...; Class 2A, 2B ...Class 5A, 5B...
2) Table 2 is design to hold: Class; StdId; Test1; Test2; Test3

actually i am thinking there should be many tables of the form of table2 which hold info about each
such that when you input a student in the enrollment table, an assign a class (e.g. Class 1A) that student should automatically appear in the Class 1A Talbe and test scores can be input there. I hope someone will understand me and propose something for me.

[June7]

No, one table. If class stream is not in the enrollment table then have another field in table 2 for this identifier.

[ArviLaanemets]

tblStudents: StdID, ForeName, LastName, Sex; ...
tblClasses: Class (Class values will be "1A","1B", "2A", ...)
tblStudentClasses: SCID, StdID, YearNo, Class, ...
tblStudentTests: STID, SCID, TestNo, TestResult

In case you want to get statistics per Test too, you can consider a table tblClassTests: CTID, Class, YearNo, TestNo, TestDescription.
Then tblStudentTests: STID, SCID, CTID, TestResult

In case a test containc several questions, and you want to save info about how every student did every question, you need additionally
tblClassTestCuestions: CTQID, CTID, Question
tblStudentTestQuestions: STQID, SCID, CTQID, TestResult

[SONCH]

Thanks for the response. It is actually working.
Please can you help define the primary key for the table=> tblStudentClasses: SCID, StdID, YearNo, Class, ...
Will the SCID be the same as the Class Values i.e. 1A, 1B, 2B for example

[ArviLaanemets]

Will the SCID be the same as the Class Values i.e. 1A, 1B, 2B for example

No! Primary key must be unique. In same class usually are several students. And also the same class can exist in different years - with different set of students. And in case leaving weak students for 2nd year in same calss is practiced, the same student can be in same class more than once - but in different years.

For best, use autonumeric fields for all PK's (Except for tblClasses, where field Class will be unique anyway)

[SONCH]

Thanks for the info.
I have some difficulty with the tblStudentsTest. I want us to modify it. it may take two or more tables again.
This is the problem: in a school year we have three terms and students study a set of subjects per term e.g. English, french, Maths ....
so I am struggling to create a table for 1st, 2nd and 3rd term results.
i.e tblTerm1: studentID, ClassId, English, French, Maths
tblTerm2: studentID, ClassId, English, French, Maths
tblTerm3: studentID, ClassId, English, French, Maths
Please help modify this for me

[ArviLaanemets]

Then maybe it will be better to use in tblStudentsTests TestDate instead of TestNo. (And probably you add a field TestSubject too)

You have to add at table where study year terms are registered too
tblStudyTearTerms: STTID, YearNo, TermNo, TermFrom, TermTo.

The test belongs to term where TermFrom <= TestDate AND TermTo >= TestDate