December 16, 2013 at 10:53 pm
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.
December 17, 2013 at 3:34 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 18, 2013 at 8:03 am
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
December 18, 2013 at 8:10 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 18, 2013 at 11:42 am
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).
December 18, 2013 at 11:51 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 18, 2013 at 12:47 pm
This might help you as a guide:
December 18, 2013 at 12:54 pm
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"
December 18, 2013 at 2:27 pm
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:
[font="Courier New"]
Student
--------------------------------------------
StudentID StudentName AdministrativeNo
--------------------------------------------
1 John Smith 435
2 Mary Jones 123
3 Smith Barney 067B
4 Don Ho 221[/font]
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:
[font="Courier New"]
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
[/font]
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.
[font="Courier New"]
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
[/font]
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
December 18, 2013 at 3:28 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 18, 2013 at 4:13 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 19, 2013 at 6:30 am
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
December 19, 2013 at 6:54 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 19, 2013 at 7:18 am
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
December 19, 2013 at 8:10 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 15 (of 58 total)
You must be logged in to reply to this topic. Login to reply