Associative entities and third normal form in a simple database

Piotr Koller

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.

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

individual state doesn't have to be a location of any national park
one state can be listed with many national parks

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

each national park has to be located in at least one state
each national park can be located in many states

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".

Gholamali-Irani

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

Each State can have 0 or more (0..*) National Parks. (optional for state)
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:

Each National Park should participate in at least one State_National_Park. (mandatory for national park)
Each State can be participate in 0 or more (0..*) State_National_Park(s). (optional for state)
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)

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2020-11-26

Comments

0 comments

TOP Ranking

Article

Associative entities and third normal form in a simple database

Associative entities and third normal form in a simple database

pump.io port in URL

Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

How to import an asset in swift using Bundle.main.path() in a react-native native module

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

Using Response.Redirect with Friendly URLS in ASP.NET

Can a 32-bit antivirus program protect you from 64-bit threats

Double spacing in rmarkdown pdf

How to fix "pickle_module.load(f, **pickle_load_args) _pickle.UnpicklingError: invalid load key, '<'" using YOLOv3?

3D Touch Peek Swipe Like Mail

Bootstrap 5 Static Modal Still Closes when I Click Outside

Assembly definition can't resolve namespaces from external packages

Vector input in shiny R and then use it

Emulator wrong screen resolution in Android Studio 1.3

Svchost high CPU from Microsoft.BingWeather app errors

Graphics Context misaligned on first paint

Python connect to firebird docker database

Is this docker-for-mac password dialog legit?

How to save models trained locally in Amazon SageMaker?