The conceptual process of filling related tables in a database (MySql) from a CSV file

Question

I am new to relational databases and to all the materials that I read, I considered primary and foreign keys, normal forms and joins, but forgot to fill out the database after it was created.

How do you import a CSV file so that the fields match their linked table?

Say that you contacted creating a beer base and had a CSV file with each line as a record.

Title: Brewer, beer name, country, city, state, beer_category, beer_type, alcohol_content

Record 1: Anheuser-Busch, Budweiser, USA, St. Louis, Moe, Black Lager, Regular, 5.0%

Record 2: Anheuser-Busch, Bud Light, USA, St. Louis, Mo, Black Lager Light, 4.2%

Record 3: Miller Brewing Company, Miller Lite, USA, Milwaukee, WI, Pale lager, Light, 4.2%

You can create a Brewer table and a Beer table. When importing, how do you connect primary keys between tables?

Answer 1

You define the relationship of the primary and foreign keys when creating tables. Once they are configured, you can simply forget them if the database does not throw an error, if you try to do something that breaks the relationship.

For example, if you try to add an entry to a child (foreign) table that does not matter in the parent table, the database will complain if the relationship is established correctly.

eg. Adding entry 1 to the Beer table will fail if you have not already added Anheuser-Busch to the brewer table.

:

Brewer
    id         bigint
    name       varchar(50)
    country    varchar(50)
    state      varchar(10)
    city       varchar(50)


Beer
    id         bigint
    brewerId   bigint
    name       varchar(70)
    category   varchar(50)
    type       varchar(50)
    alcohol    decimal(3,1)

Set Brewer.Id as the Primary Key of the Brewer table.
Set Beer.Id as the Primary Key of the Beer table.
Then create a foreign key relationship between Brewer.Id = Beer.brewerId

( )

Brewer
    1    Anheuser-Busch            United States    Mo    St. Louis
    2    Miller Brewing Company    United States    WI    Milwaukee

CSV , Beer:

Beer
    1    1    Budweiser    Pale lager    Regular    5.0
    2    1    Bud Light    Pale lager    Light      4.2
    3    2    Miller Lite  Pale lager    Regular    4.2

: BrewerId . - , brewerId ( ).

• 1 (Budweiser) Brewer 1 (Anheuser-Busch)
• Beer 2 (Bud Light) Brewer 1 (Anheuser-Busch)
• 3 ( ) Brewer 2 (Miller)

. , ... , , , - , , . Brewer, ..

, . . , . WHOLE, .