-3

I am trying to breakdown this table into 1NF: 

STUDENT    AGE    SUBJECT
Nancy      15     Math
Nancy      15     Bio
Peter      14     Math
Hal        17     Math

I first have a separate table of student and age, 

STUDENT    AGE
Nancy      15
Peter      14
Hal        17

I known I should have a separate table for subject as well, but how can I achieve that and makes it relational to the previous table?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Kay
  • 175
  • 9
  • Is the student Nancy repeating in the STUDENT table or are they different ? – Hasindu Dahanayake Jun 01 '20 at 05:58
  • @HasinduDahanayake It is repeating, that's why I think the original table violates 1NF and I wanted it to be 1NF by making it into separate tables. – Kay Jun 01 '20 at 06:03
  • ["1NF" has no single meaning.](https://stackoverflow.com/a/40640962/3404097) So you need to tell us your definition & preferably also textbook name & edition. PS Normalizaation happens to 1 table. Why are you mentioning a 2nd table? Do you mean that's part of your solution? Show all the steps of your work with justification. Be very clear about what you are doing. PS Are you normalizing that value, or a variable/schema that could holld that value? PS By all conceivable definitions your 1st variable is already in 1NF given what you have told us about it--which is only that 1 value. – philipxy Jun 01 '20 at 06:25
  • Your final question is not clear. "makes it relational to the previous table" is not clear language. Are you trying to ask something about declaring foreign keys (so-called "relation(ship)s" between tables)? What do you think that has to do with your title & 1st line re 1NF? What exactly is the 1 clear question you are trying to ask in this post? – philipxy Jun 01 '20 at 06:45

2 Answers2

0

Since you are already maintaining the student's metadata in the second table (such as the age), there is no need to also repeat this information in the first table. Therefore, the first table can simply look like this:

STUDENT    SUBJECT
Nancy      Math
Nancy      Bio
Peter      Math
Hal        Math

In practice, you might not even be using the student and subject names as the primary keys here, but rather some type of general ID. However, the above suggestion at least sets you on the right track.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Whether a 2nd table exists has nothing to do with normalizing a given table. "Normalize to 1NF" doesn't mean "make a good design". – philipxy Jun 01 '20 at 06:28
0

Thanks to @philipxy, As per first normal form, the values in each attribute should be simple value(non-relational value). Your table is already having simple value for every attribute. Age, Student, Subject. So, it is already in first normal form.

For higher normal forms, I would suggest to you to define first Key for tables. It will ensure entity integrity is maintained. Also, create separate table for Subject, as it is not functionally depending on studentId. This will avoid update anomalies for updating subjects.

  • Student: StudentId, StudentName, Age
  • Subject: SubjectId, SubjectName
  • StudentSubject: StudentId, SubjectId
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
  • ["1NF" has no single meaning.](https://stackoverflow.com/a/40640962/3404097) But this doesn't refer to any of them. – philipxy Jun 01 '20 at 06:30
  • @philipxy, thanks. from your answer, I meant to decompose the relation to multiple tables and normalize the design. – Venkataraman R Jun 01 '20 at 06:49
  • @philipxy, agree with you. went through your posts. enlightening. thanks for sharing. Please write these in a blog with examples. – Venkataraman R Jun 01 '20 at 10:06
  • Thanks & I'm glad I helped. However one of my points is **we don't know what anybody means by "1NF" until they define it**. Including here the asker or someone who told the asker to put that "table" into it. So we don't know what is being asked. Moreover you don't define what you mean by it. So my comment on your answer still applies. – philipxy Jun 01 '20 at 10:41
  • 1
    Normalization never introduces new attributes. – Mike Sherrill 'Cat Recall' Jun 12 '20 at 01:07
  • @MikeSherrill'CatRecall' Yeah it's too bad this answer doesn't actually reflect what my posts say. I should have added your comment to my last comment. PS Nowadays I always explicitly add "to higher NFs" when leaving that comment. "normalization" gets used also for "to 1NF". Maybe it's reason enough to be clear re which is meant. But also, if we take "normalization to 1NF" to be changing each attribute with a type with "parts" to have instead the "part" type, then there is a case of needing a new attribute: if the only CK is of such a "partful" type then we must add a surrogate attribute. – philipxy Jun 12 '20 at 02:00