need help on design of database for student registration system
-
The real problem is you're trying to assign keys before you've even determined the needed entities and their attributes! [/i] That's the big problem with the false notion that identity should be a "default" key: there should be NO such thing. Each PK and alternate key should be carefully chosen based specifically on the data itself.
Of course you are right and I'm sure you understand these concepts inside and out. But the OP sounded to me like a complete beginner and your rigorous analysis may be quite beyond him in this early stage of his explorations. I just suggested one quick way he might get up and running.
Knowing how much help to offer is always a sticky question, isnt' it?
BTW, at no time did I tell say that people should always use identity fields as 'default' keys. False notion or not, I didn't say that.
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
-
Of course you are right and I'm sure you understand these concepts inside and out. But the OP sounded to me like a complete beginner and your rigorous analysis may be quite beyond him in this early stage of his explorations. I just suggested one quick way he might get up and running.
Knowing how much help to offer is always a sticky question, isnt' it?
BTW, at no time did I tell say that people should always use identity fields as 'default' keys. False notion or not, I didn't say that.
I thought what you posted was perfectly reasonable. I just had a little fun with the PKs. I think you're right on track for helping out the OP.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Thank you. I am still learning and am always grateful for the help I get here and how gracious most of the posters are.
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
-
thank u all for your help...i have tried to create a relationship but have run into an error. please help. thank you.
k i have created two tables as shown in the image
each record in the 'institute table' will have multiple related records in the student.
the sql statment i used to create the foreign key relationship is this:
alter table student add constraint fk_student_institute foreign key (student_institute) references institute(institute_name)and the error is this:
Msg 547, Level 16, State 0, Line 1The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_student_institute". The conflict occurred in database "sims", table "dbo.institute", column 'institute_name'.
-
You either already have that foreign key in place, or the data there is different between the two tables and is preventing you from putting it into place.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I found out the solution... I already had records in the student table n these records were violating the constraint...the query worked fine after I deleted the records.
is it necessary to have empty tables before creating constraints all the time?
-
Sigerson (12/18/2013)
Grant,Yes it was homework, but I thank you for taking your time to craft such a gracious (and clear!) answer. Some posters here scorn helping students and I've never liked seeing that here. Thanks again.
I've never had a problem helping students that try. When it comes to students (or anyone else for that matter) that want "us" to do all of their work for them, then any scorn that comes from me is a simple reflection. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Grant Fritchey (12/18/2013)
I could, but won't, argue that identities are a crutch. Natural keys can work fine. I try not to worry too much about the key structure while I get things laid out.Heh... Ok. Give me a natural key that I could use for a Customer table. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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.
There are those that have taken exception to some of the details in the following article (see the link below) but it's an incredible start. It "materializes" exactly what Grant and some of the others have been suggesting. The "idenfication of entities" (in English, stuff that should be in one table or another) is probably the most important concept to start with followed very closely by the idea of "no duplicates and no nulls" in most tables.
Here's the link to the article. The article is based on a different project but the concepts for projects in general is all pretty much the same.
http://www.sqlservercentral.com/articles/Database+Design/72054/
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
hlsc1983 (12/29/2013)
I found out the solution... I already had records in the student table n these records were violating the constraint...the query worked fine after I deleted the records.is it necessary to have empty tables before creating constraints all the time?
No.
Shifting gears a bit, I'm absolutely certain that most existing sets of data (table or not) will have some "constraint" problems and that they will need to be cleaned up to make a viable database. But, I'm pretty sure that I wouldn't have simply deleted the rows. I would have copied them to a separate non-constained table in an "Archive" database just in case I missed something about the rows and needed to get them back later. To wit, I wish I could disable the DELETE statement at work because people delete the darnedest things.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
hlsc1983 (12/28/2013)
thank u all for your help...i have tried to create a relationship but have run into an error. please help. thank you.k i have created two tables as shown in the image
each record in the 'institute table' will have multiple related records in the student.
the sql statment i used to create the foreign key relationship is this:
alter table student add constraint fk_student_institute foreign key (student_institute) references institute(institute_name)and the error is this:
Msg 547, Level 16, State 0, Line 1The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_student_institute". The conflict occurred in database "sims", table "dbo.institute", column 'institute_name'.
I wouldn't have a table based on InstituteName as the only key nor would I make only it the PrimaryKey. As with companies, many institutes are businesses and they can suffer a name change just like anything can. At the very least, I'd make that table a "TYPE 2 SCD" (Type 2 Slowly Changing Dimension) which will withstand the winds of time especially if all other tables make reference to an "InsituteID" rather than name. Please see the following WIKI article for more on that. http://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_2
And, yes... I use a standardized "high date" for open items but I don't use 9999-12-31 because you can't add 1 to that date for certain date comparisons. I just use the year of "9999" which get's stored as 9999-01-01. I try to never use a NULL to indicate an EndDate that hasn't happened yet because that complicates lookups, as well.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (12/29/2013)
Grant Fritchey (12/18/2013)
I could, but won't, argue that identities are a crutch. Natural keys can work fine. I try not to worry too much about the key structure while I get things laid out.Heh... Ok. Give me a natural key that I could use for a Customer table. 😉
Operative word, "can." Back off Santa. Feeling frisky after your big day?
HA!!
😛
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
[Edited]
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
-
Grant Fritchey (12/30/2013)
Jeff Moden (12/29/2013)
Grant Fritchey (12/18/2013)
I could, but won't, argue that identities are a crutch. Natural keys can work fine. I try not to worry too much about the key structure while I get things laid out.Heh... Ok. Give me a natural key that I could use for a Customer table. 😉
Operative word, "can." Back off Santa. Feeling frisky after your big day?
HA!!
😛
BWAAA-HAAA!!! Sorry Grant... I may have misread what you originally posted. I thought that you were arguing that the use of identity columns IS a crutch that should be avoided or even disallowed. I couldn't disagree with that more and thought you had gone over to the dark side or or had hit yourself in the head with your splitting-maul in a wood chopping accident or somethin'. 😛
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (12/30/2013)
Grant Fritchey (12/30/2013)
Jeff Moden (12/29/2013)
Grant Fritchey (12/18/2013)
I could, but won't, argue that identities are a crutch. Natural keys can work fine. I try not to worry too much about the key structure while I get things laid out.Heh... Ok. Give me a natural key that I could use for a Customer table. 😉
Operative word, "can." Back off Santa. Feeling frisky after your big day?
HA!!
😛
BWAAA-HAAA!!! Sorry Grant... I may have misread what you originally posted. I thought that you were arguing that the use of identity columns IS a crutch that should be avoided or even disallowed. I couldn't disagree with that more and thought you had gone over to the dark side or or had hit yourself in the head with your splitting-maul in a wood chopping accident or somethin'. 😛
Ha!
Nah, no new damage yet. I'm still pretty convinced a combination of artificial and natural keys is the way to go. Although the "natural" keys I make are usually combinations of artificial keys. Still... No new head trauma.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 16 through 30 (of 58 total)
You must be logged in to reply to this topic. Login to reply