Database design, one to one relation split into multiple tables

Question

Let's assume we have following database:

Or in pseudo sql format (all columns are not null):

create table road (
  id int PK
) 
create table item_on_road (
  id int PK
  road_id int FK road.id
  position_order int
  UNIQUE (road_id, position_order)
)
create table rock (
  id int PK FK item_on_road.id
  size float
)
// and so on

We have a road on which we can travel in one direction.

Along the road we can find different things item_on_road in order we encounter them position_order like 1,2,3

Things that we find on the road can be different, from sign to gas_station.

Now I would like to ask a questions:

1. Does this database design corresponds to db 1-5th normal forms? I would assume not, because we can create item_on_road entry without corresponding rock

2. If it doesn't corresponds 5th normal form, how to do so?

Optional questions:

3. If answer to 1 question is yes, how to ensure existence of rock if item_on_road created?

4. If answer to 1 question is yes, how to ensure we can't create rock and sign pointing to the same id?

Answer 1

Fifth Normal Form is about defining the set of valid combinations, if you have a need to enforce that in the database design.

It's not about requiring that a row exist in a referencing table. I don't know of any Normal Form that does that.

For example, if you needed to make the database design enforce that rocks can only occur on roads 12 and 34, you would decompose into another table to represent valid combinations.

Your current design allows rocks to appear on any road. If that's your intention, then it complies with Fifth Normal Form.

Question 3 & 4: how to ensure that there exists a child row, and that an item can only be one type of item?

You would need to reverse the reference:

Add a foreign key column for rock in item_on_road, which references the rock table:

alter table item_on_road
  add column rock_id int,
  add foreign key (rock_id) references rock(id);

This foreign key column is nullable. If the item is a rock, then fill in the value. If the item is not a rock, then leave it null.

Add more columns for the other types of items.

Then add a CHECK constraint to make sure that exactly one of these foreign key columns is non-null.

alter table item_on_road
  add check (
    case when rock_id is null then 1 else 0 end
    + case when sign_id is null then 1 else 0 end
    ...
  ) = 1;