School Exams Database

[Bonifes]

Dear Experts, I'm quite new to design of databases. With much reading about database designs, normalizations and seeking help here and there, I've designed the table described by the tables below.

Class/level (LevelD, LevelName, SchoolYrID fk to tblSchoolYrs)
SchoolYears (SchYrID, YrStart, YrEnd)
SchYrTerms( SchYrTermID, YrID fk toSchoolYrs, TermID fk to Terms) There are three terms/ trimesters per year
Terms (TermID, TermName)
ExamTypes (ExamTypeID, ExamTypeName, TermID)
Subjects(SubjectID, SubjectName)
SubjectLevel (SubjectLevelID, SubjectID fk to Subjects, LevelID fk to Level, SubjectLevelTeacher fk to Teachers)
Teachers (TeacherID, TeacherName)
Students (StudentID, FName, LName, Gender, other details)
SubjectStudent (SubjectStudentID, StudentID fk to Students, SubjectID fk to Subjects)
LevelStudent (LevelStudentID, LevelID fk to Levels, StudentID fk to Students, SchoolYr, fk to SchoolYr)
StudentGrades (StudentGradesID, SubjectStudentID, ExamType fk to ExamTypes, Date, Grade) Grade is the actual marks scored by the student for that exam.

I've also attached a snapshot of the relationships

This compounds my problem: I only have experience in designing forms for simple databases, and find it difficult to design for the above database.

I'd like help to create forms to enter student details and the final results the ultimate form would have some of the fields populated, e.g with the current date and list of students, but no grade.

Thanks a lot.

 

[plog]

You're not ready for forms quite yet. You're individual tables look pretty good by themselves. That's not to say you have all the tables you need or don't have redundant tables. I just mean within each table the fields seem normalized.

However, the relationships among the tables are not structured properly. As a general rule, there should only be one path from one table to another. Yours is more spider webby. A simple example is from Level to teachers--I see two paths between them (one direct, one via subjectLevel).

My gut tells me you don't have the right tables or the tables aren't connected at the right level. I would rip everything out and start with your main entities: Students, Teachers, Classes. Then add in tables, being careful where they are linked until you have rebuilt your relationships so that there is only one path between tables.

 

[Bonifes]

I get you Plog. I might have mixed up things a bit, but my Idea- about teacher-level is that There's one teacher in-charge of each level, and there's a teacher in charge of a subject area in a level. One teacher can only be incharge of one level, but can also be incharge of several subjects in one or more levels. eg Maths level2, Phy level4,

Also, the main focus of the DB is exam results. I can even do OK without Teachers.

The whole point is: Students take 3Exams per Term, 3Terms per Year, then students move to the next level. Total Number of Subjects is 11. After complete design, I'll be implementing other features such as Term Averages and dropping the lowest grade in the calculations.

 

[plog]

A teacher can be assigned to a level and to a subject area. From a relationship point of view this means bringing in the teacher table twice--once for each connection. In your relationships you don't circle back to the same instance of the table, you bring it in again.

However, if Exam results is the main data, I would start there. However again--I see no table called 'Exam Results'--You need to be very careful in using terms. Declare a term and stick to it, don't allow synonyms or this thing will get even more confusing.

I would clear out your relationships all except for your Exam Results (whichever table that may be. Then bring in each other table one by one to build back your relationships.

 

[Bonifes]

Thanks for the challenge, I took the question of a one path seriously and deleted some of the relationships that may not have been necessary. This has resulted in a more linear linkage. The Exams Results I'm talking about are what I've called StudentGrades

Then, Each student is in a level and that relationship has not been defined. Is it ok to use a field in student file? Many levels--- one student

If the structure is OK now, I'll proceed to queries and forms.

 

[plog]

No I don't think the relationships are correct. First, you still only have one instance of teachers--the way you explained is that you needed 2 instances of teachers--one linked to the Level table and one linked to the subject level.

Also, you've eliminated some double paths, but you still have one big circuit. Your relationships should look like a tree, not a circle. And now you say you students have a level, which means they are connected to the Level table 3 different ways--directly via the red line, and then via the upper and lower paths of your circuit.

The more I think about this, the more confused I am getting. I think the best method for getting the proper structure is to create one big unnormalized spreadsheet with all the fields you will want. Then slowly peeling pieces off into their own tables.

Can you create a spreadsheet with sample data for all the fields you want in your database?

 

[Solo712]

No I don't think the relationships are correct. First, you still only have one instance of teachers--the way you explained is that you needed 2 instances of teachers--one linked to the Level table and one linked to the subject level.

Also, you've eliminated some double paths, but you still have one big circuit. Your relationships should look like a tree, not a circle. And now you say you students have a level, which means they are connected to the Level table 3 different ways--directly via the red line, and then via the upper and lower paths of your circuit.

The more I think about this, the more confused I am getting. I think the best method for getting the proper structure is to create one big unnormalized spreadsheet with all the fields you will want. Then slowly peeling pieces off into their own tables.

Can you create a spreadsheet with sample data for all the fields you want in your database?

Adding to plog's comments. There seems to be some inconsistencies around Class-Subject-Level relationships. I assume that there are multiple classes to a subject and level, and again there would be multiple levels to a subject. If so, your model needs some work.

StudentsSubject table is unclear. Does this relate to major-minor selected field of study ? If so, I would add a field to a student table indicating the chosen academic program and drop this table in favour of AcademicPrograms table.

The SubjectLevel table will likely cause some trouble, or at any rate, unnecessary work to keep the db up to date. Teachers will teach classes at different levels, often teaching different levels and subjects, in a given academic year. Your db needs to establish the relationship between teacher and specific class. Everything else are dependent relationships for the purposes of your db (which I trust is not tracking competence of teachers to teach different subjects or levels).

StudentGrades table is almost certainly wrong. You need to establish first the student's performance in a given academic year (for academic record) and second, performance in a specific class. A more detailed table, eg. StudentClassPerformance will track score of specific exams. This is where you need the ExamType info. Btw, most schools have adopted a system which grades type of exams, individual and group assignments, attendance etc. to compute the final grade.

Best of luck with your project.

J.

 

[Bonifes]

My understanding of the concept is still limited, but there's that Aha! moment that's close, or far.

Playing around with the tables, I'm rethinking the relationships, more like I'm questioning than --expert.
I'm in a four-year High School Kenyan system is 8-4-4 mine is thee middle four.

A student is admitted to the school Form1-> 2 ->3 ->4)... I've called these Levels. Each level there are 11 subjects, so the student will automatically be enrolled into Level1 Eng, Level1 Math... (for my understanding, is this logical? could I make a query that displays all Level 1 students? Then I'll drop the LevelStudents Table) I'm yet to decide how to link up a teacher who is in charge of a level.

That being my narrative, What should go to the ExamResults Table?
Students take an exam for each of the 11 subjects (of course in level 3 and 4 they drop to 8 )

I have also made a sketch dummy report which is my ultimate goal, except for the letter grades I've not added.

 

[Solo712]

My understanding of the concept is still limited, but there's that Aha! moment that's close, or far.

Playing around with the tables, I'm rethinking the relationships, more like I'm questioning than --expert.
I'm in a four-year High School Kenyan system is 8-4-4 mine is thee middle four.

A student is admitted to the school Form1-> 2 ->3 ->4)... I've called these Levels. Each level there are 11 subjects, so the student will automatically be enrolled into Level1 Eng, Level1 Math... (for my understanding, is this logical? could I make a query that displays all Level 1 students? Then I'll drop the LevelStudents Table) I'm yet to decide how to link up a teacher who is in charge of a level.

That being my narrative, What should go to the ExamResults Table?
Students take an exam for each of the 11 subjects (of course in level 3 and 4 they drop to 8 )

I have also made a sketch dummy report which is my ultimate goal, except for the letter grades I've not added.

Hi Bonifes,
ok, I understand better what you are trying to do. It's not a college setting where students pick their class. If the enrollment into individual class is automatic for a level and is the same for every student on that level then the structure will be a bit different. You are right, you don't need the StudentSubject table. But you need a StudentLevel table. This table assures that a student record for a given level and year is retrievable If student repeats the same level will have two school years, ie, there will be two records for the same student on the same level. The table needs StudentID, LevelID, and SchoolYearID as foreign keys.

The curriculum will be spelled out by a SubjectLevel table. Students will relate to it by StudentLevel table, via the LevelID FK. You may also want to include SchoolYearID in the SubjectLevel table which will allow to track changes in the curriculum for the level. Maybe you don't need that.

Now there is one other important thing that needs to be determined. When I was in high school many, many moons ago, we had classes with the same subject on the same Level taught more than one teacher. Is that the case in your school ? If yes, you will need a new table, called say "Class" that would have SubjectLevelID, SchoolYrID( or SchoolYearTermID), and TeacherID. Then you would also have to assign students to a particular class.

For the exams results you will need to capture the StudentLevelID, SubjectLevelID (or ClassID), ExamTypeID as FKs.

That's it for now. I think you are well on your way. You are determined and thinking about what you are doing which are the most important things. Those assure you will get it done in the end.

Best,
J.

 

[Bonifes]

Hi Solo, thanks for the reply. Earlier, I was advised against multiple paths between tables. Thus, will it be ok for the studentLevel table to not link directly to schoolyear? Level table already links to it.

Thanks for your observation about Class, it applies also in some schools (multiple streams) but my case is a single class per level.

Finally, the relationship structure still becomes like a circle... a complete loop. Anything wrong with that? Remembering @Plog's comment about tree-like structure.

 

[Bonifes]

I have here posted the expected report for a level. You will note it also requires lettergrades which I think are not necessarily required in the relationships... could just be queried in relation to the marks scored by the student at the . Or is there a better way?

I've also posted a snapshot of the redesigned database, thanks for your input.

 

[Solo712]

Hi Solo, thanks for the reply. Earlier, I was advised against multiple paths between tables. Thus, will it be ok for the studentLevel table to not link directly to schoolyear? Level table already links to it.

I think that is a mistake, Bonifes. School Year is not an attribute of Level. Look at the diagram below.

Thanks for your observation about Class, it applies also in some schools (multiple streams) but my case is a single class per level.

That will simplify things for you.


Finally, the relationship structure still becomes like a circle... a complete loop. Anything wrong with that? Remembering @Plog's comment about tree-like structure.[/QUOTE]

I am not sure where you see circularity. Basically what I am proposing is this reference hierarchy:

-------------------------------Level SchoolYear Subject Teacher

------------------------------------1-------2---------- 3------4
---------------------Student------S u b j e c t L e v e l------ ie. SubjectLevel needs to ref. the 4 above
--------------------------5--------1-------2--------- 6
-----------------------S t u d e n t L e v e l--------------StudentLevel needs to ref. Student, Level, Yr
------------------------------------------7-------6-----
-------------------------------------StudentGrade---StudentGrade needs SubjectLevel,StudentLevel
(StudentGradeID PK, SubjectLevelID FK
StudentLevelID FK, ExamType, Grade
etc.. ) ​

If it looks to you like the Level & School Year for the StudentLevel are redundant, since the two can be had through the SubjectLevel, it is not so. You will need to establish what Level in what Year a particular Student is to know what subject he/she will be graded on. ​

Best,
Jiri​

 

[Bonifes]

Thanks a lot guys, and I think I should have been posting the DB instead of snapshots.

Here it is