How do I change this schema into 3NF?

Question

I have created the following two tables for use in a history classroom setting. These tables will contain data about wars throughout history and the nations that were a part of them.

My problem is that the Wars table is not in 3NF because of the multiple values in the Combatants field, as there can be many combatants (i.e. nations) in a war.

How do I change this schema to 3NF without creating any artificial keys (i.e. only using the fields that I currently have)?

CREATE TABLE Wars (
  Name CHAR(50) PRIMARY KEY,
  StartingDate DATE NOT NULL,
  EndingDate DATE NOT NULL,
  Cause CHAR(50) NOT NULL,
  Combatants CHAR(50) NOT NULL,
  TodaysDate DATE DEFAULT SYSDATE,
  CONSTRAINT CHK_TD CHECK(EndingDate < TodaysDate),
  CONSTRAINT CHK_SD CHECK(StartingDate > 0);

CREATE TABLE Nations (
  Name CHAR(50) PRIMARY KEY,
  StartingDate DATE NOT NULL,
  EndingDate DATE,
  TodaysDate DATE DEFAULT SYSDATE,
  CONSTRAINT CHK_TD CHECK(EndingDate < TodaysDate);

Answer 1

What you've got here is a many-to-many relationship where a war can feature multiple nations, and a nation can feature in multiple wars.

To represent this in your schema:

1) Remove the "combatants" field from your Wars table

2) Create a "NationsWars" table (or whatever you want to call it). It need only contain the Nation Name and War Name.

3) Each of these two fields will each have a foreign key back to the primary key of their respective parent tables.

4) The primary key of this new table will be a compound key consisting of both the fields mentioned above.

This is the classic, standard way to represent a many-to-many relationship such as this.

---

N.B. I'm unsure why you have an objection to artificial keys here. I'd say numeric auto-increment IDs would be ideal keys for the nations and wars tables...that way if a nation changes its name, or you realise you made a typo, or you want to re-label a war, then you can do so without violating any key constraints. It's generally considered bad practice to use name or description fields as keys, because they are almost always subject to change in the long term. The primary key should be something which uniquely and permanently identifies the specific record, and has no other contextual meaning.