Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


need help on design of database for student registration system


need help on design of database for student registration system

Author
Message
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3934 Visits: 6660
Yes, definitely do a logical design first. Don't even think about artificial keys yet.

You need to identify "entity" and "attribute". Roughly:

Entity = things about which you store unique data
Attribute = a single piece of data you need to store
[Naturally you have to accurately match each attribute to the appropriate entity -- sounds obvious, but can be tricky at times.]

OK, let's see what potential entities we have. Some are pretty clear:
Student
Institute
Department
Semester
Course [not directly stated in your list but common knowledge]

But what about:
Mark Sheet?
Admit Card?
You have to decide if these are true Entities, which deserve data stored about them, or if they are just attributes about relationships between the entities above.

Determine the attributes you need and assign them to the appropriate entity.
CRITICAL RULE: An attribute is a single piece of data, never repeating or a list.

You will need to create a new, special "relationship entity" for any data about the relationship between two entities.

Maybe, since a student takes courses, you will ultimately need an entity(ies) that relate Course and Student somehow.

As part of this process, you will determine and document the cardinality (1 to 1, 1 to Many, Many to Many) between entities. Naturally not all entities will have a direct relationship with every other entity.


Edit: corrected typo.

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Sigerson
Sigerson
Mr or Mrs. 500
Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)

Group: General Forum Members
Points: 551 Visits: 1232
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.


I agree completely about using a key structure that is most natural to the problem at hand. You are quite right and I am not an identity evangelist by any means. I just wanted to give the OP a visual example of laying out tables with foreign keys, and thinking about what should go into each table. Integer keys seemed to simplify my example, that's all.

But this has turned into a more interesting thread than I'd expected, so thanks again for giving a student a hand.

Sigerson

"No pressure, no diamonds." - Thomas Carlyle
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17524 Visits: 32250
This is why I still like standard discussion style forums as opposed to Question/Answer forums like StackOverflow. Yeah, if you have a very clear and specific question and want a clear and specific answer, SO works. But, it doesn't allow for the give & take and interaction that can provide for a wider & deeper learning experience that the discussion forum allows.

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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
prabhu.st
prabhu.st
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 309
because most of them don't seem to really want to learn & understand. They just want an answer


What Grant is willing to say is, hlsc1983 should get how to design the table structure from the Use Cases given by him, but unfortunately, because of being helping minded, Sigerson have Most probably the layout of the Student Database which was expected by hlsc1983.

I would like to stand with Grant's Side, as the Newbies could experience what is happening implicitly in SQL while working with..

I have a bad friend who is just alike what Grant Said..

Thanks,
Prabhu
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3934 Visits: 6660
Sigerson (12/19/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.


I agree completely about using a key structure that is most natural to the problem at hand. You are quite right and I am not an identity evangelist by any means. I just wanted to give the OP a visual example of laying out tables with foreign keys, and thinking about what should go into each table. Integer keys seemed to simplify my example, that's all.

But this has turned into a more interesting thread than I'd expected, so thanks again for giving a student a hand.



The real problem is you're trying to assign keys before you've even determined the needed entities and their attributes! 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.

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Sigerson
Sigerson
Mr or Mrs. 500
Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)

Group: General Forum Members
Points: 551 Visits: 1232
@ScottPletcher:

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
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17524 Visits: 32250

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 Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Sigerson
Sigerson
Mr or Mrs. 500
Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)

Group: General Forum Members
Points: 551 Visits: 1232
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
hlsc1983
hlsc1983
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 248
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 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_student_institute". The conflict occurred in database "sims", table "dbo.institute", column 'institute_name'.

Attachments
tables.png (298 views, 9.00 KB)
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17524 Visits: 32250
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 Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search