help to develop table in sql db

Question

0.00/5 (No votes)

See more:

I have below tasks like

Each student has a

• Unique Roll Number

. Has 3 exams in a year

. Has 4 subjects

• Student roll numbers are unique across classes & sections

SQL

CREATE TABLE students(
Roll no INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
subject-1 VARCHAR(30) NOT NULL,
subject-2 VARCHAR(30) NOT NULL,
subject-3 VARCHAR(50),
)

But I am confused how to create "has 3 exams in a year" in a table please any one help me

Posted 14-Mar-15 1:56am

Member 11406049

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2015-03-14T02:07:00

Solution 1

It's complicated, because I have no idea what data you need to store as the "exam" (and I doubt if "subject" should be stored that way either).

Personally, I'd have a number of tables:

Students:
Id    Name        Address
1     Joe Ninety  2, Patpong Road, Bangkok
2     Mike Green  17, 1st Avenue, New York

Subjects
Id    Name
1     Maths
2     English

Exams
Id    Subject   Date
1     1         2014-05-15
2     1         2015-05-16

And tie them together with other tables:

StudentSubjects
Id    Student   Subject
1     1         1
2     1         2
3     2         2

StudentExams
Id    Student   Exam    Result
1     1         2       75

Does that make sense?

Posted 14-Mar-15 2:07am

OriginalGriff

Comments

Member 11406049 14-Mar-15 8:23am

thank you very much. its pretty clear but i need to work on it. can we use two tables instead of these many? for exams in year and for students and subjects? sorry, I am beginner in db

OriginalGriff 14-Mar-15 8:48am

You could...but you could also do it with a single table! :laugh:
The trouble is that when you do that, you restrict the flexibility of the system, and vastly increase the amount of duplicated information.

Think about your classes at school:
Class : 30 students.
Student: 5 subjects.
Subject: 2 exams per year.

You could store all that in a single table - but then you have to restrict your education to 30 students, and a total of 10 exams per year. And you need a separate row each year! And when that bright kid joins, and is doing 7 subjects you have no way to store the info.

Multiple tables may look complex - but it better reflects the "real world" that the data comes from, and actually makes all the subsequent queries a lot, lot easier. Honest! :laugh:
And it really isn't difficult to work with, once you get your head around it.

Think about it for a moment: if a student drops out of a course and switches to a different one, how difficult is it to update a single table to reflect that? Pretty hard - because you have to check each and every column to find the "old" course and replace it with the "new" . With multiple tables it's trivial: one simple UPDATE query with a basic WHERE clause, because all the "links" between student and subject are independent, just as in the real world.

Peter Leow · Answer 2 · 2015-03-14T02:14:00

Solution 2

In addition to Solution 1 by OriginalGiff

Quote:
. Has 3 exams in a year
. Has 4 subjects

These are business rules that should be implemented and enforced at the code level not the database.
Check this out: Introduction to database design[^]

Posted 14-Mar-15 2:14am

Peter Leow

Updated 14-Mar-15 2:16am

v2