I'm a little bit confused about normalization.
Can someone give a real life example in this database? If that's ok ? Thanks.
table: account_info
student_number(PK)
student_username
student_password
table: student_info
student_number(PK)
student_name
student_course
student_year
student_section
table: student_subject
student_number(PK)
student_subject_1
.
.
.
student_subject_10
About foreign key, can I use it in the given tables above?
Where and when is it the best time to use it ?
Well, if you have questions about normalization, you probably should read up on the subject and then ask specific questions. That said ...
The only thing unnormalized I see about your schema here is the student_subject_1, student_subject_2, etc. One of the rules of First Normal Form is "no repeating groups". That is, nothing that you would use an array or list for in a program. Any time you start numbering fields, you should step back and ask if this is not a repeating group, i.e. an array.
The by-the-book normalized thing to do is to change this to:
student_number
subject_index
subject_name
The primary key is then student_number + subject_index. Or you could create a synthetic id, like a sequence number.
You then create one record for each subject, and use the index to distinguish them.
If the order doesn't mean anything -- if #2 could just as well have been placed at #3 and #4 at #1 etc -- then I'd most likely go for creating a student_subject_id that's simply a sequence number.
There are many good reasons for doing this.
Is 10 subjects the absolute limit that can never ever possibly change? If not, if someday a student comes along who has 11 subjects, then your program will break. I ran into this once where a system had, I think it was 8 slots for "child". I suppose someone said, "Who would ever have more than 8 children?" And then of course someone came along who had 11 children.
Suppose you want to search for all the students who are taking some subject, say "Database 101". With one subject per record, that's a simple query:
select student_number from student_subject where subject = 'Database 101'
(Maybe you want to join on the student_info table to get his name, etc, but that's not important here.)
But with 10 subjects in each record, you'd have to write:
select student_number from student_subject where subject_1 = 'Database 101'
or subject_2 = 'Database 101'
or subject_3 = 'Database 101'
or subject_4 = 'Database 101'
or subject_5 = 'Database 101'
or subject_6 = 'Database 101'
or subject_7 = 'Database 101'
or subject_8 = 'Database 101'
or subject_9 = 'Database 101'
or subject_10 = 'Database 101'
Not only is that a lot to type, but now there are 10 times as many chances to make a typing mistake. Testing will be less reliable. Suppose you mis-typed the subject name in the test for subject_7. The program could appear to work: It would find students who had this subject for 1, 2, 3, etc, If it so happened that no student in your test had this particular subject as #7, the program would give correct results. I ran into exactly this once: somebody made a record with category_1, category_2, etc (we were putting books in a technical library into categories), then wrote a query in which he made a typing mistake on the test for category_7. In our tests we usually only put in 2 or 3 categories on each book, so it all appeared to work. Then we went to production and, boom, it failed the first time someone had a book with 7 categories.
If you have to write these queries that check all 10 fields, then if someday you need to add a field, you have to find every query and update to test #11 also. If you miss one, you'll start getting subtle errors in the program. Oh, I ran into this once too, sort of. We had a db with I think it was 5 of something, and somebody wrote a query where, by mistake, he tested 1, 2, 4, and 5, and just forgot to test #3.
If you want to be able to search on subject, you'll need indexes on 10 fields instead of 1. Much more database overhead.
Probably other reasons if I think about it some more.