CODEWITHSUNDEEP
databasedatabase-design
Piotr Koller
Piotr Koller

Reputation: 621

Associative entities and third normal form in a simple database

I'm interested in basic database design. I wanted to design a simple database with states and national parks. Because one state can have many national parks and one national park can be located in many states, I think they should be related as many-to-many.

In this type of relation associative entity is required and this is my design.

entity-diagram

I'm not sure about relations between entities and associative entities. I've done it this way, because:

therefore, one(optional)-to-many(optional) between "State" and "state_park"

therefore, many(optional)-to-one(mandatory) between "state_park" and "State"

I want to know whether my way of thinking is correct or not?

I also want to know what type of data would violate Third Normal Form in my database? I don't think population or area violates this, because it depends on both "id (PK)" and "Name".

Upvotes: 1

Views: 873

Answers (1)

Gholamali Irani
Gholamali Irani

Reputation: 4350

As your explanation, I think your first model should be like below (UML Notation):

enter image description here

  1. Each State can have 0 or more (0..*) National Parks. (optional for state)
  2. Each National Park can be located in 1 or more (1..*) State(s). (mandatory for national park)


And if we map this many-to-many relationship:

enter image description here

  1. Each National Park should participate in at least one State_National_Park. (mandatory for national park)
  2. Each State can be participate in 0 or more (0..*) State_National_Park(s). (optional for state)
  3. But, each State_National_Park (each instance/record that exist in that entity/table) should have one State and one National Park.

Notice that: cardinality of the optional and mandatory participation are shown in other side of relationships. For example in first model, National Park has mandatory participation, so in the other side of our model we use 1..* (not 0..*).

Also, this model is in 3NF. (However the definition of Area is ambiguous)

Upvotes: 1

Related Questions