The student record system

[Afi83]

I have a problem with creating this database.
I want to create the student record system. I have a table containing Individual information and using of ID as primary key.
For registering grades I use another table for grades. But here is the problem. Here we have 6 or more class with different courses.
Firstly, I should to permit the user for adding more classes and then more courses to these basic assumptions. (But how the user can add another table that the relationship between new table and the Ind information table makes sense).
How can I save the grades in table?
It is better to use different table for different class with using different courses as filed in this table.

Or add all the courses in my filed and for each ID I just fill out the course for that class.

Or something else I don't know?

Another problem is when some person has grades with lower than 15, I have to add another grades for this person. This person failed then must have another grade for taking effect in GPA.

Please help me to find the way through these problems.

[timothybard]

Below is the database structure I would start with:

Student: ID, Last Name, First Name, etc
Course: ID, Course Name
Section: ID, Course ID, Section Name
Enrollment: ID, Section ID, Student ID
Assignment: ID, Section ID, Assignment Name
Grades: ID, Student ID, Assignment ID, Grade

[Afi83]

I can't understand how can i relate different course subject to each grades relate to that subject.
Please say some more guidelines.
Also what about the failed grades and where to save other grades related to this subject and using that for accounting GPA.

[timothybard]

You can join the grade table to the section table and then join that to the course table. If you need to track a subject, you can add a subject table and have a foreign key in the course table to record which subject it is for.

To find out GPA, you simply take the grades in the grade table join to the student table and the section table and perform the necessary calculations. The method of how you calculate GPA will dictate the actual query design you use.

[Afi83]

Excuse me
But if you can send a access file containing the relationship because i can't understand the true relationship in this case.

Also please explain about the failed grades of each student and keep track of them.

[timothybard]

I gave you the tables I would use for this database above. As far as the relationships:

course-section: one-to-many
section-enrollment: one-to-many
section-assignment: one-to-many
grades-assignment: many-to-one
grades-students: many-student

After reviewing the design, I found that you can use enrollment ID in the grades table instead of student ID; using the enrollment ID would probably be best.
You should be able to create your own database in access from there.

[HI2Japan]

This is for school work isn't it?

[Afi83]

No not at all, This is not for school. Really I am graduated. I have M.sc degree in Genetics. But because i did not do any database design I get into trouble for doing this. This is program for my military service I want to use some privilege by doing this.

If someOne can guide me please help.
Thanks in advance for all of responses to this thread

[Afi83]

Dear Friends

The attachment is my effort for creating the ERD for the question.
Please check it for correctness or wrongness.

Thanks in advance

[timothybard]

How does your design track which classes the grades are for? How does your design track each assignment? Is each major a part of a field? What do the penalty and class tables track? How does your table track which class the faculty are teaching?

I would suggest you start with the suggestions I made in this thread, create the ER diagram and go from there.

[Afi83]

Dear Experts
Thanks for truly useful links. I used them and my mind really cleared on the subject.
Here are the requirements:
Create the database for saving the data from individual student and grades and the faculties, department and … each student relate to it.
Each student has some personal information related to each person and some information about the faculty, department and so on.
I have to track which grades are for each student as well as each class grades apart from each other.
Moreover, the department relate to each faculty and classes in each department.
One more thing is penalty session. Each student that failed in two courses, he/she introduced to this session. The result of this session must be documented and the student must pass another exam or … .
Then I use another grade for saving the second or third grades each failed student received.
Please look at the relationship I produce for tracking these requirements. The attachment is the access file for looking at the relationship.
If something is wrong, please let me know because this database is my first work.
Thanks in advance
Afi

[timothybard]

How do you plan to record the grade a student has for each of his/her classes? What is the difference between class and course?

I feel a lot of your relationships are backwards. For example, student and major. In your design, each major can only have one student. Also, each class can only have one student and each field can only have one student.

Again, I would recommend starting with the design I gave you; once you have that set up correctly, then work on adding the additional tables you didn't mention in your original post.

[Afi83]

Here is my last attempt for creating this database please take a look at that.
Also How can i force the auto number to start from 1 after each adding and deleting records.

Thanks in advance for your helps.

[timothybard]

Your database design is a lot closer to what I was expecting. Please note that my original design included course sections in case there were different sections for each course; if you don't need to track course sections, then your design is fine.

As far as the autonumber... the easiest thing to do is copy the table structure as a new table, delete the original table and rename the copy of the table. However, there shouldn't be any need to have the autonumber start at 1. The autonumber is usually used simply to track relationships between data.