First normal form and temporary data

Question

The first normal form says that the order of the lines does not matter. Does this mean that the table with the date as part of the key is not 1NF? for example, Consider a ticker price table where the date / time is part of the PC. In this case, you get the latest price by ordering data by date and select the top 1 row. Does this mean that to execute 1NF you need to split the table into: 1) TickerCurrentPrice (1 line per ticker) 2) TickerHistoricalPrice Thanks

Answer 1

1NF is an aspect of a table representing a relationship, not a table as such.

If your attitude says ticket HAS price , that is a 1NF violation, because you cannot determine if there is a ticket HAS or HAS NOT price by looking at one record. You will need to get all the prices for this ticket and choose the last one, which violates the non-ordering rule 1NF .

If your relationship says ticket HAD BEGUN TO COST price ON date , then it is all right in 1NF , because each entry says that it says: this ticket costs this price from this date .

Thus, we say that this table does not correspond to 1NF when representing the first relationship, but corresponds to the second representation.

The table itself remains unchanged.

This does not necessarily mean that you need to split your tables.

The whole point of relational databases is that you can use relational operators to transform one relationship into another.

What is relation in terms of RDBMS ? This is a table containing all combinations of all possible values ​​that are in this respect among themselves.

For example, if we need to build an equality relation on natural numbers from 1 to 5 , we have the following table:

 1 1 2 2 3 3 4 4 5 5 

All pairs that appear in this table are in equality; all pairs that do not appear are not. We do not see (2, 3) or (4, 5) , since they are not equal.

But you do not need to keep the whole pair in the database. Instead, you save individual values ​​and write the query:

 SELECT n1.number, n2.number FROM number n1, number n2 WHERE n1.number = n2.number 

which gives you the same result.

Actually, normal forms allow you to store the simplest possible relationship tables in the database and build more complex relationships from them using SQL queries.

In your case, if you write a query (or define a view) as follows:

 SELECT ticket, price FROM mytable WHERE (ticket, date) IN ( SELECT ticket, MAX(date) FROM mytable GROUP BY ticket ) 

you get the relation ( ticket HAS price ) from ( ticket HAD BEGUN TO COST price ON date ) in the same way as if you saved the entire table in the database.

Answer 2

No, "select ... order by ..." does not violate 1NF. The row (and column) that violates 1NF is more related to situations along the “select * from XYZ” rows, and then selects the third row from the top and fourth columns on the left. "Yes, I saw such database projects.

Answer 3

It is understood that if it is necessary to record some order of data (for example, by date), then it must be written explicitly , for example. in the date column. It would be wrong to have order in an implicit ordered order in the physical order of the lines on the disk (provided that you can still control it). In other words, you must ORDER in some column to return the data in that order.

Answer 4

Not. This means that there is no internal order. If you need the date of the last price, you must select max(date) from your table.