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 Monday, December 16, 2013 10:53 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 4:06 AM
Points: 20, Visits: 83
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.
Post #1523529
Posted Tuesday, December 17, 2013 3:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:31 AM
Points: 14,205, Visits: 28,533
In short, homework.

Here's how it's done. Think about each of the objects you've just defined. There's a school, a student and student grades. Now think about how those objects relate to each other. The school exists completely independently of all other objects, right? A student must belong to a school, but, can they belong to more than one at the same time? Let's assume just one at a time (because it's easier and no one has said different). That means you have a relationship between the school and the student. You can map that out as what's called a foreign key. Then, you have classes. The classes, presumably, also belong to the school. Each student can take more than one class, so there isn't a direct relationship between student and class. Instead you have to create an interim table that allows you to map students to classes directly. But, one student can only be in a given class once (per year anyway, if you flunk, you have to take it again, different problem). So, the unique identifier for the StudentClass table should be the student identifier (whatever it is) and the Class identifier (whatever it is). Then, the marks a student gets are part of that relationship between class and student, but... are they multiple marks or just one mark per class?

You just work through it that way, identifying the objects, the fields contained within them, and the relationships to other objects. That will build out your database.


----------------------------------------------------
"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 #1523586
Posted Wednesday, December 18, 2013 8:03 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 306, Visits: 851
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.


Sigerson

"No pressure, no diamonds." - Thomas Carlyle
Post #1524151
Posted Wednesday, December 18, 2013 8:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:31 AM
Points: 14,205, Visits: 28,533
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.



Thank you.

I'm pretty adverse to answering homework questions too because most of them don't seem to really want to learn & understand. They just want an answer. But, I've taken to answering them in a way that keeps me happy, even if it doesn't quite give them exactly what they want. But you should see some of the responses when you don't spoon feed them. People get quite upset for some reason.

But you're right to call any of us on bad behavior. We want this place to be welcoming.


----------------------------------------------------
"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 #1524154
Posted Wednesday, December 18, 2013 11:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 4:06 AM
Points: 20, Visits: 83
thank you...so much
no it is not homework. like i said, it the first time, thats why i did not know where to start.
Each student will have multiple classes(subjects) and each each subject will have multiple marks( theory and practical marks).
Post #1524272
Posted Wednesday, December 18, 2013 11:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:31 AM
Points: 14,205, Visits: 28,533
So that still breaks down the same way. A class will have a relationship with multiple students. And a student will have a relationship with multiple classes. So, the way to get that done is an interim table, one that connects the two together through a many-to-many relationship. Then, you're probably going to need a marks table that is related back to the StudentClass table. Now that one could be slightly harder to set up due to the fact that I assume there will be multiple marks per class. So, you'll need another table, we'll call it MarksType, that designates what any given set of marks is. You could also add another table called MarksPeriod (or something like that) that designates the time frame, end of quarter, end of semester, mid-semester, whatever. Then, all that will be the identifying key for the marks table so that for a given class and student, for a given mark type, for a given time frame, they get one grade.

But, that assumes a lot that you haven't stated. But you should be getting the idea of how to start to lay stuff out. When you have the tables put together and a specific clarifying question, get on back to us.


----------------------------------------------------
"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 #1524278
Posted Wednesday, December 18, 2013 12:47 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 4,043, Visits: 9,196
This might help you as a guide:
http://www.sqlservercentral.com/stairway/72400/



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1524322
Posted Wednesday, December 18, 2013 12:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:03 AM
Points: 160, Visits: 368
I will also add that doing your conceptual design, logical design and physical design and actually drawing it out can and will help you a ton. When you do the conceptual model, you can just put stuff down as it is logical in your head and not necessarily in a "database" architecture. This helps you to understand how the eventual database will look and how every object will relate to each other. It lets you get all of your data elements out there and its just visually easier to see a way ahead when you do the modeling. Ultimately, this will also help you determine your one to one joins and one to many etc... as the other posters have already given you a great head start you could now take that and do your modeling.

If this is unfamiliar then there is a lot of great information on google if you type "conceptual database design"
Post #1524326
Posted Wednesday, December 18, 2013 2:27 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 306, Visits: 851
Another quibble I would throw in is to make each table's primary key an identity field, meaning "an integer that SQL Server assigns automatically to every new record when it is inserted into the table." So your Student table might have something like this:

Student
--------------------------------------------
StudentID StudentName AdministrativeNo
--------------------------------------------
1 John Smith 435
2 Mary Jones 123
3 Smith Barney 067B
4 Don Ho 221



The StudentID is absolute, but the other data in the can be edited. The "AdministrativeNo" field is the number assigned to that student by the Registrar, for example. Now for the Class table:

Class
-----------------------------------------------
ClassID Class Department
-----------------------------------------------
1 Chem 1A Chemistry
2 English 100 English
3 Algebra 2B Mathematics
4 Calculus 1A Mathematics
5 Chem 1B Chemistry
6 Chem 2A Chemistry




Then, the StudentClass table that links your students to all their classes becomes very compact because it only contains the foreign keys to your other two tables. Remember, it gets its own integer key, another identity field.

StudentClass
-------------------------------------------
StudentClassID StudentID ClassID
-------------------------------------------
1 1 5 John Smith is taking Chem 1B
2 3 6 Smith Barney - Chem 2A
3 4 1 Don Ho - Chem 1A
4 3 2 and so on....
5 3 3





Now, you're on your own... Oh, and read up on JOINs. You're going to be using them. A lot.


Sigerson

"No pressure, no diamonds." - Thomas Carlyle
Post #1524360
Posted Wednesday, December 18, 2013 3:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:31 AM
Points: 14,205, Visits: 28,533
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.

----------------------------------------------------
"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 #1524381
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse