need help on design of database for student registration system

Question

Post reply

need help on design of database for student registration system

Viewing 15 posts - 31 through 45 (of 58 total)

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

hlsc1983 SSCommitted Points: 1971 More actions · Answer 1

This is what i have come up with so far. please review it and give necessary input.thanks,.

hlsc1983 SSCommitted Points: 1971 More actions · Answer 2

this is what i have come up with so far. please review it and suggest necessary changes.

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

hlsc1983 (1/15/2014)
This is what i have come up with so far. please review it and give necessary input.thanks,.

That looks pretty decent. I would suggest that you shouldn't store the number of student for a given institute. Generally speaking storing calculated values like this will come back to bite you. It makes maintenance of data very difficult and fraught with errors. I would two alternatives here. One is to simply calculate that value when you need it. Depending on the frequency of requiring that information that may be sufficient. The other alternative would be to use a computed column. You would need to create a function to return the count of students for a given institute. Then you set the value of the computed column using that function.

If it were my design I would just remove the stored value because generally a simple count like that should be lightning fast with some indexing.

If you do keep it, it shouldn't allow NULL. There is always a known value for the number of students. If there are no students the value is 0. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

I agree on the number of students. Also, what's the difference between student_pk and student_id? Seems like one or the other isn't needed unless the ID is some sort of alternate key generated by app code or something.

Also, a student can only be associated with a single department? I'm asking, I don't know something else.

"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

hlsc1983 SSCommitted Points: 1971 More actions · Answer 5

thanks.. i never expected the problems it might create in the future.

hlsc1983 SSCommitted Points: 1971 More actions · Answer 6

Grant Fritchey (1/15/2014)
I agree on the number of students. Also, what's the difference between student_pk and student_id? Seems like one or the other isn't needed unless the ID is some sort of alternate key generated by app code or something.
Also, a student can only be associated with a single department? I'm asking, I don't know something else.

'student_id ' refers to the registration id allotted by the office. May be i should have named it 'student_registrationID' in order to make it easier to understand.

And yes, a student can belong to only one department.that is why the 1 to n relationship.

Sean Lange SSC Guru Points: 286573 More actions · Answer 7

hlsc1983 (1/15/2014)
Grant Fritchey (1/15/2014)
I agree on the number of students. Also, what's the difference between student_pk and student_id? Seems like one or the other isn't needed unless the ID is some sort of alternate key generated by app code or something.
Also, a student can only be associated with a single department? I'm asking, I don't know something else.
'student_id ' refers to the registration id allotted by the office. May be i should have named it 'student_registrationID' in order to make it easier to understand.
And yes, a student can belong to only one department.that is why the 1 to n relationship.

If those numbers are unique couldn't you just use that as your primary key? This would be the natural key that Grant was discussing earlier. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

hlsc1983 SSCommitted Points: 1971 More actions · Answer 8

Sean Lange (1/15/2014)
hlsc1983 (1/15/2014)
Grant Fritchey (1/15/2014)
I agree on the number of students. Also, what's the difference between student_pk and student_id? Seems like one or the other isn't needed unless the ID is some sort of alternate key generated by app code or something.
Also, a student can only be associated with a single department? I'm asking, I don't know something else.
'student_id ' refers to the registration id allotted by the office. May be i should have named it 'student_registrationID' in order to make it easier to understand.
And yes, a student can belong to only one department.that is why the 1 to n relationship.
If those numbers are unique couldn't you just use that as your primary key? This would be the natural key that Grant was discussing earlier. 😉

thanks for the insight on natural primary keys. you do have a point. since i am developing this for a college, i am not sure if they re-allot registration id to their students. if yes i will need both the attributes. If no, i will need only one and can have the option of deleting 'student_id'.thanks

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

[font="Comic Sans MS"]

I am NOT saying or implying that this o.p. falls into the category I am describing below (he clearly does not because he was asking for guidance on how to start and not a full T-SQL solution) and I would be glad to help in this case.

The were OTHER cases where the question was blatantly a word-for-word copy of a homework question (not even an attempt to rephrase it) and just looked like an expectation of complete implementation of the answer (without even a first rudimentary cut at defining tables, let alone trying a T-SQL statement), I do not feel it is any bad behaviour on the part of the members of the forum to dismiss the question as an outright attempt to get grades without making the slightest effort. Such does constitute abuse of the good will of the forum members and should be firmly 'encouraged' away from the forums.

Not to be confused with some outrageous responses given to obvious beginners from experts belittling the person asking a question. We have had (thankfully) only RARE cases and those indeed would poison the forum if such flaming became widespread.[/font]

hlsc1983 SSCommitted Points: 1971 More actions · Answer 10

i have a query regarding my database design. The snapshot of my database was posted in my earlier post.

In my database i wanted to include a table called 'Subjects' to store subject name and subject id.

There are multiple subjects and some departments belonging to same or different institutes share the same subject.

Now in theory there is a many to many relationship between 'Subjects' and 'Students'

Another many to many between 'Subjects' and "Departments'

And one more between 'Institutes' and 'Subjects'

.

Now my question is it necessary to create all three relationships ? or should i create only one?

Please remember that this is my first database.

If the answer 'depends 'on the scenario ,could you please tell me the possible scenarios.

Sean Lange SSC Guru Points: 286573 More actions · Answer 11

hlsc1983 (3/5/2014)
i have a query regarding my database design. The snapshot of my database was posted in my earlier post.
In my database i wanted to include a table called 'Subjects' to store subject name and subject id.
There are multiple subjects and some departments belonging to same or different institutes share the same subject.
Now in theory there is a many to many relationship between 'Subjects' and 'Students'
Another many to many between 'Subjects' and "Departments'
And one more between 'Institutes' and 'Subjects'
.
Now my question is it necessary to create all three relationships ? or should i create only one?
Please remember that this is my first database.
If the answer 'depends 'on the scenario ,could you please tell me the possible scenarios.

For these many to many relationships you will need to create a bridge table that contains nothing but a foreign key reference to each of the main tables, and maybe some auditing columns.

So you would end up with three new tables like StudentSubjects, DepartmentSubjects, InstituteSubjects. Does that make sense?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

hlsc1983 SSCommitted Points: 1971 More actions · Answer 12

Sean Lange (3/5/2014)
hlsc1983 (3/5/2014)
i have a query regarding my database design. The snapshot of my database was posted in my earlier post.
In my database i wanted to include a table called 'Subjects' to store subject name and subject id.
There are multiple subjects and some departments belonging to same or different institutes share the same subject.
Now in theory there is a many to many relationship between 'Subjects' and 'Students'
Another many to many between 'Subjects' and "Departments'
And one more between 'Institutes' and 'Subjects'
.
Now my question is it necessary to create all three relationships ? or should i create only one?
Please remember that this is my first database.
If the answer 'depends 'on the scenario ,could you please tell me the possible scenarios.
For these many to many relationships you will need to create a bridge table that contains nothing but a foreign key reference to each of the main tables, and maybe some auditing columns.
So you would end up with three new tables like StudentSubjects, DepartmentSubjects, InstituteSubjects. Does that make sense?

thanks for reply.. but it doesn't answer my query. I am already aware of junction tables. may be my question wasn't clear enough. let me try again.

what I need to know is whether it is necessary to create relationships for every possibility. will having multiple relationships hamper the front end. ? in my case front end is designed using vb.net.

for example if a create a many to many between Students and Subjects , is it necessary to create a relationship between Institutes and Subjects. ?

Sean Lange SSC Guru Points: 286573 More actions · Answer 13

hlsc1983 (3/5/2014)
Sean Lange (3/5/2014)
hlsc1983 (3/5/2014)
i have a query regarding my database design. The snapshot of my database was posted in my earlier post.
In my database i wanted to include a table called 'Subjects' to store subject name and subject id.
There are multiple subjects and some departments belonging to same or different institutes share the same subject.
Now in theory there is a many to many relationship between 'Subjects' and 'Students'
Another many to many between 'Subjects' and "Departments'
And one more between 'Institutes' and 'Subjects'
.
Now my question is it necessary to create all three relationships ? or should i create only one?
Please remember that this is my first database.
If the answer 'depends 'on the scenario ,could you please tell me the possible scenarios.
For these many to many relationships you will need to create a bridge table that contains nothing but a foreign key reference to each of the main tables, and maybe some auditing columns.
So you would end up with three new tables like StudentSubjects, DepartmentSubjects, InstituteSubjects. Does that make sense?
thanks for reply.. but it doesn't answer my query. I am already aware of junction tables. may be my question wasn't clear enough. let me try again.
what I need to know is whether it is necessary to create relationships for every possibility. will having multiple relationships hamper the front end. ? in my case front end is designed using vb.net.
for example if a create a many to many between Students and Subjects , is it necessary to create a relationship between Institutes and Subjects. ?

Not quite sure what you mean. You have a relationship between Students and Subjects. You also have a relationship between Institutes and Subjects.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Grant Fritchey SSC Guru Points: 398697 More actions · Answer 14

hlsc1983 (3/5/2014)
Sean Lange (3/5/2014)
hlsc1983 (3/5/2014)
i have a query regarding my database design. The snapshot of my database was posted in my earlier post.
In my database i wanted to include a table called 'Subjects' to store subject name and subject id.
There are multiple subjects and some departments belonging to same or different institutes share the same subject.
Now in theory there is a many to many relationship between 'Subjects' and 'Students'
Another many to many between 'Subjects' and "Departments'
And one more between 'Institutes' and 'Subjects'
.
Now my question is it necessary to create all three relationships ? or should i create only one?
Please remember that this is my first database.
If the answer 'depends 'on the scenario ,could you please tell me the possible scenarios.
For these many to many relationships you will need to create a bridge table that contains nothing but a foreign key reference to each of the main tables, and maybe some auditing columns.
So you would end up with three new tables like StudentSubjects, DepartmentSubjects, InstituteSubjects. Does that make sense?
thanks for reply.. but it doesn't answer my query. I am already aware of junction tables. may be my question wasn't clear enough. let me try again.
what I need to know is whether it is necessary to create relationships for every possibility. will having multiple relationships hamper the front end. ? in my case front end is designed using vb.net.
for example if a create a many to many between Students and Subjects , is it necessary to create a relationship between Institutes and Subjects. ?

That's tough to answer without more understanding of the requirements. In fact, it's likely that a "Subject" is unique from one Institution to the next, implying that there's a one (Institution) to many (Subject) relationship there. Although, one could also argue that a given Subject is common across Institutions, then, you would need to create an interim table to relate them. And that would be independent of the student record, although, you would need to validate that a given student is associated with a given institution and that a given institution has a given subject before you can associate a student to that subject.

Data is fun.

"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

hlsc1983 SSCommitted Points: 1971 More actions · Answer 15

thanks again...i think its a very open ended question. I think I will continue with the front end in VB.net and as and when the need arises I will make necessary modifications to my database. I wonder if its the right approach....