0

I am currently studying SQL normal forms.

Lets say I have the following table the primary key is userid

userid    FirstName    LastName    Phone
1         John         Smith       555-555
1         Tim          Jack        432-213
2         Sarah        Mit         454-541
3         Tom          jones       987-125

The book I'm reading states the following conditions must be true in order for a table to be in 1st normal form.

  1. Rows contain data about an entity.
  2. Columns contain data about attributes of the entities.
  3. All entries in a column are of the same kind.
  4. Each column has a unique name.
  5. Cells of the table hold a single value.
  6. The order of the columns is unimportant.
  7. The order of the rows is unimportant.
  8. No two rows may be identical.
  9. A primary key Must be assigned

I'm not sure if my table violates the 8th rule No two rows may be identical.

Because the first two records in my table

1         John         Smith       555-555
1         Tim          Jack        432-213

share the same userid does that mean that they are considered duplicate rows?

Or does duplicate records mean that every peace of data in the row has to be the same for the record to be considered a duplicate row see example below?

1         John         Smith       555-555
1         John         Smith       555-555

EDIT1: Sorry for the confusion

The question I was trying to ask is simple

Is this table below in 1st normal form?

userid    FirstName    LastName    Phone
1         John         Smith       555-555
1         Tim          Jack        432-213
2         Sarah        Mit         454-541
3         Tom          jones       987-125

Based on the 9 rules given in the textbook I think it is but I wasn't sure that

if rule 8 No two rows may be identical

was being violated because of two records that use the same primary key.

The class text book and prof isn't really that clear on this subject which is why I am asking this question.

teckuser55
  • 27
  • 6
  • It's good that you put the criteria in your question. But it would be good to quote/paraphrase what the book says about "identical". There are many variations on relations/tables & "1NF". If you're taking a course you should find out what that textbook defines things. What is the book? – philipxy Mar 13 '19 at 00:28
  • 1
    What does your book say about duplicate primary keys? – Bill_Stewart Mar 13 '19 at 00:48
  • Your title doesn't make sense. But it seems like it's trying to ask a different question than you ask in the body of your question. The body doesn't ask about PKs, it asks about entire rows. But if you want to ask the question in your title, please post a new question now that there are answers. (Maybe you mean, Can a table that has rows that share a PK be in 1NF? But all PK subrow values in a relation must be different by the definition of PK.) – philipxy Mar 13 '19 at 00:59
  • Sorry for the confusion I should have written my question a little more clearer. The Question I was trying to ask was is the first table I mentioned in my question meet all the requirements to be in first normal form? – teckuser55 Mar 13 '19 at 01:03
  • That's very different from what is in your question. Please edit your title to agree with the question then make a new (self-contained) post for the new question. If you ask, you need to read your textbook to make clear what it means by "identical" & the details of "PK" & point 9, and incorporate that in your question. See my answer for some of the issues around not being clear. PS What's your book? – philipxy Mar 13 '19 at 01:10
  • Hi. If you want an answer to the question in your EDIT, rather than just having it give context, please ask it in a new post. (You accepted my answer--thanks--but it only answers the pre-EDIT.) You can cut & paste the text you can get by clicking on "edited" & the "source" of a version. PS Please don't add EDITs or UPDATEs to clarify or modify posts, just edit to the best presentation possible. Please ask one question per post. [ask] Also: In any new post please paraphrase or quote your textbook re the unclear things that we mentioned here. Most people cannot get at your textbook. – philipxy Mar 13 '19 at 02:32
  • 1
    The set {userid} cannot be the PK of a this table value or a variable holding it because the subrows under its columns (well, it has just one) are not all unique. You need to learn how to identify the CKs of a relation value or variable, from which you can pick a PK. PS Contrary to your quoted list/textbook, being a relation(al table) or being in 1NF doesn't actually have anything to do with entities or PKs (or CKs). (If you have a question about anything please ask in a new post.) – philipxy Mar 13 '19 at 02:47
  • You said your textbook is Database Processing: Fundamentals, Design, & Implementation 15th. I'll discuss the 12th. It gives 1-8 only "for a table to be a relation"--not 1NF (yet). Which they contradict with "and neither repeating groups nor arrays are allowed as values". It continues to CKs (defined wrongly) & PKs. Then it says 1-8 define 1NF. But they don't say 1NF involves PKs. Are you mixing things up & not actually seeing what is in it? (Are you only using notes from the web and/or class?) Read its text not just figures & when you use terms appeal to their definitions. – philipxy Mar 13 '19 at 05:05

1 Answers1

1

Or does duplicate records mean that every peace of data in the row has to be the same for the record to be considered a duplicate row see example below?

They mean that--the latter of your choices. Entire rows are what must be "identical". It's ok if two rows share the same values for one or more columns as long as one or more columns differ.

That's because a relation holds a set of values that are tuples/rows/records, and set is a collection of values that are all different.

But SQL & some relational algebras have different notions of "identical" in the case of NULLs compared to the relational model without NULLs. You should read what your textbook says about it if you want to know exactly what they mean by it. Two rows that have NULL in the same column are considered different. (Point 9 might be summarizing something involving NULLs. Depends on the explanation in the book.)

PS

There's no single notion of what a relation is. There is no single notion of "identical". There is no single notion of 1NF.

Points 3-8 are better described as (poor) ways of restricting how to interpret a picture of a table to get a relation. Your textbook seems to be (strangely) making "1NF" a property of such an interpretation of a picture of a table. Normally we simply define a relation to be a certain thing so if you have one then it has to have the defined properties. Then "in 1NF" applies to a relation & either means "is a relation" & isn't further used or it means certain further restrictions hold. A relation is a set of tuples/rows/records, and in the kind of relation your 3-8 describes they are sets of attribute/column/field name-value pairs & the values paired with a name have to be of the type paired with that name in some schema/heading that is a set of name-type pairs that is defined either as part of the relation or external to it.

Your textbook doesn't seem to present things clearly. It's definition of "1NF" is also idiosyncratic in that although 3-8 are mathematical, 1 & 2 are informal/heuristic (& 9 could be either or both).

philipxy
  • 14,867
  • 6
  • 39
  • 83