3

I'm an A-level Computer Science student, and my teacher can't explain how to get me from UNF to 1NF. I've researched a little, but I'm not sure as to whether this table (below) is in 1NF or not. I've left the UNF format for you with what I think is the 1NF.

UNF format

UNF

What I think is 1NF

1NF

  • 1
    Explain why you did split the table the way you did, and how you define 1NF. Learning normalization is a process (not as easy as one might think at first), so you should try to formulate the decisions that lead you to your current design. Why do you consider it to be in 1NF, or why do you suspect that it is not in 1NF? – Lennart Nov 7 '15 at 20:38
6

I think you need to reconsider whether the payment for the dates is done on the hire (or rather the transaction) or the customer.

When thinking about normalization you need to think in terms of properties of an entity rather than something abstract so I want you to think if day in and out, and the paid column are really a property of the customer rather than the transaction (I guess the price of the transaction is a giveaway).

I think, if you think about it that way (entities-properties) you will feel naturally that the price and the dates and the paid go together with the renting transaction rather than the customer. (possibly needing a date for the transaction in the case of returning customers, but that isn't included in your original table)

You might also want to reconsider storing the item name on the transaction instead of just the item number. Items could be a table in itself.

It's usually not abstract theory but what you feel as properties of an object that is right. It's not that much different from class/object design in programming.

| improve this answer | |
  • Doesn't 1NF mean only that that there are no tables within tables? – Joshua Nov 7 '15 at 17:12
  • 1
    @Joshua I'm not sure what you mean by 'tables within tables' – Tom V - try topanswers.xyz Nov 8 '15 at 8:08
  • 1
    By which I mean a table within a table cell. – Joshua Nov 8 '15 at 16:09
2

Fabian Pascal recently blogged on the definition of first normal form. Also, his Practical Database Foundation Series includes a treatment of normalization and 1NF. If one purchases the entire series two additional papers are included that specifically address what is and what is not 1NF, the former paper written by CJ Date. I will make an attempt to summarize the key points with regard to your question.

For a table to be in 1NF, and thus be a relational table (R-table), certain rules must be followed in its design and population to ensure the table can acquire the properties of a mathematical relation. The rules summarized are:

  1. Distinct un-ordered rows
  2. Uniquely named un-ordered columns
  3. Single Value Columns (SVCs)
  4. No missing column values

Regarding rule one, a mathematical relation by definition has unique tuples but when adapted for use in data management Codd added a key to serve as a unique identifier for each row as in the real world entity types are identified by one or more of their characteristics (attributes). Note there can by more than one candidate key so I just use the word key here.

Regarding rule four, Codd initially intended that no column could contain a relational valued domain (RVD). It was later shown that RVDs do not violate the relational model and instead any domain of any complexity, including another relational variable, could be contained in the column of an R-Table so long as that column contained only one value.

The table you have shown in the question and labeled as UNF format is not in 1NF as it violates a portion of the first rule - distinct rows - if I interpret the diagram correctly that Customer Number is the key based on the single underline under its name. There are three rows with customer number C002.

Of the two tables shown which you believe to be in 1NF, the first is still not in 1NF as it still contains three rows with the same customer number C002. The second table is in 1NF as it has unique rows as defined by the key of customer number and item reference. Note that the original table does not require splitting to achieve normalization. Instead, the original table could be changed to have as its key customer number plus item reference and it would be in 1NF.

| improve this answer | |
  • 2
    The rows in the (UNF) OP's example are distinct. Why would 2 rows with same CustomerNumber violate 1NF? The rows differ (in other columns). I don't understand really how you came to that conclusion. – ypercubeᵀᴹ Nov 20 '15 at 9:45
  • The table heading for UNF had an underline under the column heading for customer number. This is commonly an indication that customer number is designated as the key to the table. I was just pointing out that if in fact customer number is the key then the table as shown is not in first normal form as it contains duplicate values for the given key. – Todd Everett Nov 20 '15 at 11:36
  • OK but I don't think that the 1NF requirements have any connection or require tables to have keys. Only to have distinct rows. – ypercubeᵀᴹ Nov 20 '15 at 11:43
  • 2
    Date made this comment in his paper "What First Normal Form Really Means.": "you have to know what the table means. To be specific, you have to know whether any significance attaches to row or column ordering, you have to know whether duplicate rows or nulls are allowed, and you have to know what the domains are." I took that to mean that if the table's data does not obey the declared key it is not in 1NF. This is why I interpret the requirement "distinct rows" to mean distinct with respect to the declared key. A table cannot be an R-Table without at least 1 candidate key. – Todd Everett Nov 20 '15 at 12:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.