0

There are many questions that have something to the effect of "What normal form are these data in?" and I admittedly have not combed through every one of them to see if "These data" they are referring to are pivoted. I'm asking this because I think this would be useful for those searching for this for those familiar with this terminology.

Lets say that I have a table with the columns:

personid*, email1, email2, email3
1        , [email protected], NULL  , NULL
2        , [email protected],[email protected], [email protected]
3        , [email protected],[email protected]

Where personid is the primary key and uniquely identifies the table. Each e-mail is functionally dependent on personid but obviously this isn't in 3NF because that would involve having a person table and an e-mail table such as:

personid, email ,email_num
1       ,[email protected] ,1
2       ,[email protected],1
2       ,[email protected] ,2
2       ,[email protected],3
3       ,[email protected],1
3       ,[email protected] ,2

Where email_num takes the place of the n from the previous table.

What normal form is the first (pivoted) table in?

philipxy
  • 14,867
  • 6
  • 39
  • 83
mkingsbu
  • 354
  • 3
  • 20
  • Hi. What academic textbook are you using for NFs? (You are just asking us to rewrite it.) When you get stuck applying its definitions & algorithms then say how & ask a specific question. Your current "because" for "obviously this isn't in 3NF" has nothing to do with NFs. Why do you think it does? You are not clearly appealing to appropriate definitions/algorithms. PS ["1NF" has no standard meaning](https://stackoverflow.com/a/40640962/3404097) so again if you care about that give your reference. PS Pivoting is clearly irrelevant to NFs per se. Also PKs & NULL. FDs, CKs & JDs matter. Textbook. – philipxy Oct 06 '18 at 00:23
  • There are presumably interesting relationships between 2 tables where one is in various ways or some way reasonably describable as a pivot of the other. But that is likely an essay & asking for it is likely too broad a question for SO. But anyway you are not asking such a question even about a specific example. You could. But the problem here is you are asking a question about normalization while not making sense about it. Maybe if you give your reasoning clearly enough we can point out a mistake in it. But right now there's clearly not a clear reasoned appeal to basics to find a fault in. – philipxy Oct 06 '18 at 00:35
  • @philipxy . I can't answer the question myself yet since I posed it, but I was planning on answering it if nobody else answered within that time frame. As you indicated, there are several definitions. I was asked about this. I was asked about it and I thought since others are familiar with the terminology of "pivoting" and might be wondering the question that this would be a useful addition to SO. I was planning on basically paraprhasing the Wiki entry which is, actually, related (using telephones instead of emails) but same basic idea. Its definition is UNF but you could argue 1NF too. – mkingsbu Oct 06 '18 at 01:33
  • A table either is or isn't in a given NF. Your "because" is confused. You probably have in mind that it's not in some notion of 3NF that requires some notion of 1NF & that if you started from the 1st relation it "should" (according to some particular confused definition per my link) first be "normalized" to that "1NF" to get something like the 2nd table. But you *don't say* that. "1NF" is *not standard* so nor are higher NFs so your question has different answers for different definitions. PS Wikipedia is trash in at least part of every relational database article. I *emphasized*: Textbook. – philipxy Oct 06 '18 at 02:31
  • Anyway asking what NF a table is in is *too broad* & asks for a textbook chapter from scratch & bespoke tutorial & should not answered. See [ask] & hits googling 'stackexchage homework' & the downvote arrow mouseover text. Show your work *following a published informed reference* & ask a question about the particular place you are stuck or uncertain how to continue & say why. Your question isn't even about pivoting, that was just how you got a table. – philipxy Oct 06 '18 at 02:35
  • Normal Forms and related algorithms are defined only in the case of non null values. In your first table there are null values so the question cannot have an answer. – Renzo Oct 06 '18 at 06:43

0 Answers0