need help on design of database for student registration system

Question

need help on design of database for student registration system

Viewing 14 posts - 46 through 58 (of 58 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 100946 More actions · Answer 1

The core thing is not to skimp on the design. It may look 'leaner' to just leave out some relationship tables, or to combine some relationship tables, but doing that will ultimately cause you big grief and pain. In short, do a full design, whether that means 20 tables or 150 tables.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

hlsc1983 SSCommitted Points: 1971 More actions · Answer 2

ScottPletcher (3/5/2014)
The core thing is not to skimp on the design. It may look 'leaner' to just leave out some relationship tables, or to combine some relationship tables, but doing that will ultimately cause you big grief and pain. In short, do a full design, whether that means 20 tables or 150 tables.

thank you so much for your suggestion. so I should create the tables right? i posted my query because I thought that too many tables might actually restrict my front end. thanks for the warning.

hlsc1983 SSCommitted Points: 1971 More actions · Answer 3

hi am back after a long time . so sorry for that. Now i need to enter 'Marks ' . Each student will have two types of marks for the Subjects that he is taking . The marks are 'Progressive Marks' and 'End Term Marks'. As you can see I have already added two tables for Subject and Student. How can i account for the marks in the database?

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 4

Assuming I'm reading this correctly, we can see the Student to Subject Relationship through the Semester to Subject table? I find that a little confusing. The Student isn't related to a Semester. A class is. Students are related to classes within a Semester. But, whatever, if that works for you, then the marks are a table hung off the SemesterSubject table.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Rich Mechaber SSChampion Points: 11130 More actions · Answer 5

hlsc1983 (12/16/2013)
this is my first SQL server project.. student registration system using SQL server 2008 and VB.net.
can you please help me in designing the database?
the system will be used to store student details, edit , delete and print them. it also needs to accept marks of students for each subject, and print his mark sheet and his admit card as well.
there are five different institutes, each with around four different departments, and a total of eight semesters in all. thanks.

I'm late to this thread, but it still seems to have a life, so I thought I'd add an idea. You say you have "five different institutes, each with around four different departments, and a total of eight semesters." As I work in higher ed., it's often the case that you need to create both a "master" course table -- which serves as a more-or-less invariant template for each semester's course, with course name, institute, department, course number, credits, etc. -- and a per-semester table, containing specific instances of the courses that students actually register for.

The master course table "MasterCourses" would contain:

- a primary key

- foreign keys to your tables for institutes and/or departments (if your departments table already has a foreign key back to institutes table, then you just need a key to departments table)

- and whatever non-term-specific data about the course you want to include (course title, number of credits, whether it's repeatable, etc.)

The per-semester course table -- let's call this "Sections" would contain:

- a primary key

- foreign keys to your master course table and to your terms table. This is how you create an instance of, say, Macroeconomics (FK to CourseMaster table) for the Fall 2015 term (FK to your Terms table).

- foreign key to your instructors table, so you can identify who teaches the course

- any columns you need to describe this specific term's section, such as meeting days, building and room number, enrollment cap, grading mode (though this might belong in the master course table, depending on your requirements), etc.

- section number. If you have 3 sections of Macroeconomics in a term, you get three rows in the Sections table, each with their own value for the PK

Having set up a maintainable way to create new courses every term, you can now think about a Registration table. This is easy: it's a table with one row for every student-section. So something like this for columns:

- a primary key

- foreign key to the Sections table. Notice there's no need to repeat any of the course information here. By joining Registration to Sections to MasterCourses, you've handled all of the information for term, course name, credits, section number, etc.

- foreign key to the Students table

- columns for interim and final grades (This is a violation of normal form by having repeating columns. But if you're positive you'll never have to record anything but these 2 grades, I might be inclined to just break the rule and put the grades here, as I think it's rare to unheard-of to officially record anything else. The instructors, of course, need to record grades for every quiz, exam, and assignment, but I've never heard of those being centrally stored in perpetuity.)

Do you see the overall concept? You only store the data that relates to the table's primary key (no term-specific information is contained in MasterCourses; Registration doesn't contain course departments, b/c that's in the MasterCourses table; Registration doesn't contain any student data other than the FK to the Students table).

Good luck,

Rich

hlsc1983 SSCommitted Points: 1971 More actions · Answer 6

Grant Fritchey (10/20/2015)
Assuming I'm reading this correctly, we can see the Student to Subject Relationship through the Semester to Subject table? I find that a little confusing. The Student isn't related to a Semester. A class is. Students are related to classes within a Semester. But, whatever, if that works for you, then the marks are a table hung off the SemesterSubject table.

In our educational system, it is like this. There are several institutes. Each institutes has a number of departments eg Computer Science, Civil Engineering, Mechanical Engineering. Each Department has a total of eight semesters. Yes there are classes but each class is in fact one semester. For examples, there are eight classes or eight semesters for Civil Engineering.

I think the current design if fine unless i have missed something big.

hlsc1983 SSCommitted Points: 1971 More actions · Answer 7

rmechaber (10/20/2015)
hlsc1983 (12/16/2013)
this is my first SQL server project.. student registration system using SQL server 2008 and VB.net.
can you please help me in designing the database?
the system will be used to store student details, edit , delete and print them. it also needs to accept marks of students for each subject, and print his mark sheet and his admit card as well.
there are five different institutes, each with around four different departments, and a total of eight semesters in all. thanks.
I'm late to this thread, but it still seems to have a life, so I thought I'd add an idea. You say you have "five different institutes, each with around four different departments, and a total of eight semesters." As I work in higher ed., it's often the case that you need to create both a "master" course table -- which serves as a more-or-less invariant template for each semester's course, with course name, institute, department, course number, credits, etc. -- and a per-semester table, containing specific instances of the courses that students actually register for.
The master course table "MasterCourses" would contain:
- a primary key
- foreign keys to your tables for institutes and/or departments (if your departments table already has a foreign key back to institutes table, then you just need a key to departments table)
- and whatever non-term-specific data about the course you want to include (course title, number of credits, whether it's repeatable, etc.)
The per-semester course table -- let's call this "Sections" would contain:
- a primary key
- foreign keys to your master course table and to your terms table. This is how you create an instance of, say, Macroeconomics (FK to CourseMaster table) for the Fall 2015 term (FK to your Terms table).
- foreign key to your instructors table, so you can identify who teaches the course
- any columns you need to describe this specific term's section, such as meeting days, building and room number, enrollment cap, grading mode (though this might belong in the master course table, depending on your requirements), etc.
- section number. If you have 3 sections of Macroeconomics in a term, you get three rows in the Sections table, each with their own value for the PK
Having set up a maintainable way to create new courses every term, you can now think about a Registration table. This is easy: it's a table with one row for every student-section. So something like this for columns:
- a primary key
- foreign key to the Sections table. Notice there's no need to repeat any of the course information here. By joining Registration to Sections to MasterCourses, you've handled all of the information for term, course name, credits, section number, etc.
- foreign key to the Students table
- columns for interim and final grades (This is a violation of normal form by having repeating columns. But if you're positive you'll never have to record anything but these 2 grades, I might be inclined to just break the rule and put the grades here, as I think it's rare to unheard-of to officially record anything else. The instructors, of course, need to record grades for every quiz, exam, and assignment, but I've never heard of those being centrally stored in perpetuity.)
Do you see the overall concept? You only store the data that relates to the table's primary key (no term-specific information is contained in MasterCourses; Registration doesn't contain course departments, b/c that's in the MasterCourses table; Registration doesn't contain any student data other than the FK to the Students table).
Good luck,
Rich

Thanks for joining in. I think i have too far to add Master table. I will go ahead wit my design this time.

hlsc1983 SSCommitted Points: 1971 More actions · Answer 8

hi, I have come so far. Now i need to know something. In the Subjects table , I have columns for four types of marks(THPA, THET, PRPA, PRET) for each subject. However there are some subjects for which we dont have all four types of marks. In other words for some subjects, the students are given only two or three types of marks, say THPA ,THET and PRPA . Whereas some other subjects students are alloted only two types, say THET and PRET. How can i account for this in the database table? or I have to take care of this in my front end in VB.net?

joshdbguy SSCertifiable Points: 7707 More actions · Answer 9

hlsc1983 (10/20/2015)
hi, I have come so far. Now i need to know something. In the Subjects table , I have columns for four types of marks(THPA, THET, PRPA, PRET) for each subject. However there are some subjects for which we dont have all four types of marks. In other words for some subjects, the students are given only two or three types of marks, say THPA ,THET and PRPA . Whereas some other subjects students are alloted only two types, say THET and PRET. How can i account for this in the database table? or I have to take care of this in my front end in VB.net?

It depends on what you want to see when you return results but as long as the fields are nullable, you'll see nulls. You can also default the values to be whatever you want.

ScottPletcher SSC Guru Points: 100946 More actions · Answer 10

hlsc1983 (10/19/2015)
hi am back after a long time . so sorry for that. Now i need to enter 'Marks ' . Each student will have two types of marks for the Subjects that he is taking . The marks are 'Progressive Marks' and 'End Term Marks'. As you can see I have already added two tables for Subject and Student. How can i account for the marks in the database?

Create a junction table to model that relationship. To match the existing model, you'd name it:

Students_Subjects_junction

The key to the table would be the main parents' keys, student_pk and subject_Id. To that table, you'd then two columns: progressive_marks and end_term_marks. This type of data is called junction data, or intersection data. This data is specifically and only about the relationship of student and subject.

In other words, all data about student alone goes in the student "master" table. All data about subject alone goes in the subject "master" table. But data about the relationship between the two goes into the junction table, because it applies only to the combination, not to either one separately.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Michael L John One Orange Chip Points: 27038 More actions · Answer 11

There are couple things I see that don't make sense to me.

1. The "semester_id" in the Student table. This is a lookup to the Semester table.

If a student is registered in the first semester, and then registers in the second semester, does another Student record get created? This probably violates 3NF.

Or, does the semester_id field get updated to the new semester? This definitely violates 3NF because this creates an update anomaly. In other words, what happens when we need to know information about a previous semester?

Or, something else?

To me, this relationship is not needed. The student-class relationship takes care of this. If a student is registered for a class in this semester, joining these tables gets you who is enrolled for a given semester.

2.

thank you so much for your suggestion. so I should create the tables right? i posted my query because I thought that too many tables might actually restrict my front end. thanks for the warning.

What is "too many tables"? How can having a lot of tables restrict the front end design?

The database needs to enforce the business rules.

3. The suggestion of a "master course" table was right on. The same concept can be applied to students.

4. The concept of "marks". In any of the colleges I have attended, there may have been multiple possibilities that were chosen by the student, not the subject.

For example, most students could receive credit for a course, and get a letter grade.

Some "audited" the course, which means they attended the class, did all of the work, but received no credit or grade. Others students only received a pass/fail for the course.

5. Many to many for institutes and classes. If the engineering school required "Programming in C++" it was given the code "ENG140". If the computer science school required "Programming in C++" it was given the code "MIS140". Same class, same instructor, different departments.

Along the same lines, I have seen the same class required in different majors where the number of credits that can be earned were different. The class may be a 4 credit class for XYZ majors and a 3 credit class for ABC majors.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

j-1064772 SSCertifiable Points: 5316 More actions · Answer 12

As an additional complication, consider that a course may have pre-requisite course(s) which must be completed successfully. This should be a business rule to enforced as well.

hlsc1983 SSCommitted Points: 1971 More actions · Answer 13

Michael L John (10/21/2015)
There are couple things I see that don't make sense to me.
1. The "semester_id" in the Student table. This is a lookup to the Semester table.
If a student is registered in the first semester, and then registers in the second semester, does another Student record get created? This probably violates 3NF.
Or, does the semester_id field get updated to the new semester? This definitely violates 3NF because this creates an update anomaly. In other words, what happens when we need to know information about a previous semester?
Or, something else?
To me, this relationship is not needed. The student-class relationship takes care of this. If a student is registered for a class in this semester, joining these tables gets you who is enrolled for a given semester.
2.
thank you so much for your suggestion. so I should create the tables right? i posted my query because I thought that too many tables might actually restrict my front end. thanks for the warning.
What is "too many tables"? How can having a lot of tables restrict the front end design?
The database needs to enforce the business rules.
3. The suggestion of a "master course" table was right on. The same concept can be applied to students.
4. The concept of "marks". In any of the colleges I have attended, there may have been multiple possibilities that were chosen by the student, not the subject.
For example, most students could receive credit for a course, and get a letter grade.
Some "audited" the course, which means they attended the class, did all of the work, but received no credit or grade. Others students only received a pass/fail for the course.
5. Many to many for institutes and classes. If the engineering school required "Programming in C++" it was given the code "ENG140". If the computer science school required "Programming in C++" it was given the code "MIS140". Same class, same instructor, different departments.
Along the same lines, I have seen the same class required in different majors where the number of credits that can be earned were different. The class may be a 4 credit class for XYZ majors and a 3 credit class for ABC majors.

1)Regarding "student_id " in the student_table. Should i just delete the Semester table and the student-semester relatio ship? if so , then how can i account for 'semester'? Should i create another column in the subject table?

2) that was the thought initially. that is why created a semester table without hesitation.

5)The same subject code is given to all the subjects. i think there are differences in our educational systems.

hlsc1983 SSCommitted Points: 1971 More actions · Answer 14

ScottPletcher (10/21/2015)
hlsc1983 (10/19/2015)
hi am back after a long time . so sorry for that. Now i need to enter 'Marks ' . Each student will have two types of marks for the Subjects that he is taking . The marks are 'Progressive Marks' and 'End Term Marks'. As you can see I have already added two tables for Subject and Student. How can i account for the marks in the database?
Create a junction table to model that relationship. To match the existing model, you'd name it:
Students_Subjects_junction
The key to the table would be the main parents' keys, student_pk and subject_Id. To that table, you'd then two columns: progressive_marks and end_term_marks. This type of data is called junction data, or intersection data. This data is specifically and only about the relationship of student and subject.
In other words, all data about student alone goes in the student "master" table. All data about subject alone goes in the subject "master" table. But data about the relationship between the two goes into the junction table, because it applies only to the combination, not to either one separately.

Thanks . Now there are more constriants i havent mentioned yet. First, There are four types of marks i)Theory End Term ii)Theory Progressive assessment marks ii)Practical end term ii)Practical Progressive marks. Secondly,some subjects will have all four marks whereas other subjects will have only two or three of these marks. For eg, students are given all four marks for Java Programming subject whereas students are given only Theory End term and theory progressive marks for Mobile Communications. How should i implement these rules?