Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

need help on design of database for student registration system Expand / Collapse
Author
Message
Posted Wednesday, December 18, 2013 4:13 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:19 PM
Points: 2,328, Visits: 3,505
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1524396
Posted Thursday, December 19, 2013 6:30 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:56 AM
Points: 306, Visits: 849
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
Post #1524543
Posted Thursday, December 19, 2013 6:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:17 AM
Points: 14,204, Visits: 28,531
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
Post #1524560
Posted Thursday, December 19, 2013 7:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 8:25 AM
Points: 86, Visits: 224
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
Post #1524572
Posted Thursday, December 19, 2013 8:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:19 PM
Points: 2,328, Visits: 3,505
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1524604
Posted Thursday, December 19, 2013 12:18 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:56 AM
Points: 306, Visits: 849
@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
Post #1524747
Posted Thursday, December 19, 2013 12:24 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:17 AM
Points: 14,204, Visits: 28,531

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
Post #1524752
Posted Thursday, December 19, 2013 2:15 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:56 AM
Points: 306, Visits: 849
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
Post #1524806
Posted Saturday, December 28, 2013 11:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 4:06 AM
Points: 20, Visits: 83
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'.





  Post Attachments 
tables.png (191 views, 9.37 KB)
Post #1526307
Posted Sunday, December 29, 2013 5:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:17 AM
Points: 14,204, Visits: 28,531
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
Post #1526338
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse