Please o, I am having problem organizing my database-A secondary school result managing system, My First site on the process of learning programming. I am using sqlalchemy to design students result database and I am stuck on how to link results, subjects, assignment and exam to the db. Please help me. I am still learning please. God bless you. My Erd goes like this

1. table( student )
student_id=primary key
order student details

2. table Class
class_id=primary key
class name

3. table Term
term_id=primarykey
name

4.table( Enrolment )
primarykey=enrolment_id
student_id=foreignkey references student table via one-to-many
class_id = foreignkey references class table via one-to-many
section = column
subjects = a collection of subjects from the many-to-many resolution table( enrolment_subjects ) below the student will choose

5. table enrolment_subjects
enrolment_id = foreignkey references enrolment table
subject_id = foreignkey references subjects table

6.table subjects
subject_id = primarykey
subjectname=column
NB: I have been able to return from query, students enrolled to a particular class including the subjects the students are assigned. I will appreciate any reference. thanks in advance

Hello, I will like you to explain what you mean by linking result, assignment etc to the db. If i am trying to paraphrase your problem you are either

1) Looking for how to create results, subject, assignment and exam table and how to do proper referencing (relationship)
2) You already have results, subject, assignment and exam tables and you want to query multiple tables to form a single report

I am just guesting your problem, please explain what you want well before i can help.

Cheers.

Nekon J: Hello, I will like you to explain what you mean by linking result, assignment etc to the db. If i am trying to paraphrase your problem you are either

1) Looking for how to create results, subject, assignment and exam table and how to do proper referencing (relationship)
2) You already have results, subject, assignment and exam tables and you want to query multiple tables to form a single report

I am just guesting your problem, please explain what you want well before i can help.

Cheers.

Thanks Nekon J, my major problem is the highlighted and also I think the relationship I have done above is not quite good. I understand my entities but the problem is their relationships. Thanks in advance

webcalculator:

Thanks Nekon J, my major problem is the highlighted and also I think the relationship I have done above is not quite good. I understand my entities but the problem is their relationships. Thanks in advance

If the system is for secondary school and assume they still operate as follows
1) JSS student takes all available subjects for JSS
2) SSS student either take science or art subjects
3) From JSS 1 to JSS 3, we normally take the same subject (Though diff topics eg, Maths, Eng, Social Studies etc. are subject name and they dont change, we can't add or subtract subject because JSS 1 to 3 take a fix number of subject)
4) From SSS1 to SSS 3, either science or art student will take the same subjects

If your business domain operates in this manner then
1) Because we can't add or remove subject in either JSS or SSS (After choosing science or art for SSS) and we do repeat each subject every term. (Let say Level is either JSS or SSS) so your enrollment table will relate with Level (levelId) and Student(studentId) but not Class because JSS 2 student will not need to enroll again because they know that all subject taken in JSS 1 will continue till JSS 3.


You won't need Student Subject entity again because each subject is link to a level and level is already linked to Enrolment (If a student enroll for let say Level JSS, we will know all the subjects for that level. Some subjects are offered in both JSS and SSS eg. English, Maths, CRK, Computer Study etc. so we have to introduce Level Subject Entity)

Your Subject table remain
subject_id
Name

Level Table:
level_id
name (this is either JSS or SSS)

Your Term table, Class Table and others here.

LevelSubject will be like
subject_id
level_id

Your Enrollment entity will be
4.table( Enrolment )
primarykey=enrolment_id
student_id=foreignkey references student table via one-to-many
level_id = foreignkey
The rest attribute goes here

Assessment table will look like:
assessment_Id
student_id
class_id
term_id
test1
test2
exam
total


I hope this help. You may name your entity anything else for example i have Level which you may name something else.

Nekon J:

If the system is for secondary school and assume they still operate as follows
1) JSS student takes all available subjects for JSS
2) SSS student either take science or art subjects
3) From JSS 1 to JSS 3, we normally take the same subject (Though diff topics eg, Maths, Eng, Social Studies etc. are subject name and they dont change, we can't add or subtract subject because JSS 1 to 3 take a fix number of subject)
4) From SSS1 to SSS 3, either science or art student will take the same subjects

If your business domain operates in this manner then
1) Because we can't add or remove subject in either JSS or SSS (After choosing science or art for SSS) and we do repeat each subject every term. (Let say Level is either JSS or SSS) so your enrollment table will relate with Level (levelId) and Student(studentId) but not Class because JSS 2 student will not need to enroll again because they know that all subject taken in JSS 1 will continue till JSS 3.


You won't need Student Subject entity again because each subject is link to a level and level is already linked to Enrolment (If a student enroll for let say Level JSS, we will know all the subjects for that level. Some subjects are offered in both JSS and SSS eg. English, Maths, CRK, Computer Study etc. so we have to introduce Level Subject Entity)

Your Subject table remain
subject_id
Name

Level Table:
level_id
name (this is either JSS or SSS)

Your Term table, Class Table and others here.

LevelSubject will be like
subject_id
level_id

Your Enrollment entity will be
4.table( Enrolment )
primarykey=enrolment_id
student_id=foreignkey references student table via one-to-many
level_id = foreignkey
The rest attribute goes here

Assessment table will look like:
assessment_Id
student_id
class_id
term_id
test1
test2
exam
total


I hope this help. You may name your entity anything else for example i have Level which you may name something else.

Nelkon J, thanks very much for the time you have taken to write this, go through my problem; I really appreciate. God will bless you. Let me give it a short.

Am well exprienced in using dreamweaver nd Mx-kollection.It can be done easily.Get back to me as soon as possible

boro43: Am well exprienced in using dreamweaver nd Mx-kollection.It can be done easily.Get back to me as soon as possible

What is dreamweaver and your Mx-kollection at this age?

I am a newbie to programming but i am sure better than you.