Tietokanta sisältää viisi taulukkoa: animals, countries, favorite_animals, favorite_countries and person. Löydät tietokannan ERD:n täältä. Seuraavaksi tietoja taulukoiden sisällöistä.

animals

• animal_id: a primary key to identify different animal from list
• animal: the name of the animal
• created_on: timestamp when the animal was added to the database

countries

• country_id: a primary key to identify different country from list
• country: the name of the country
• created_on: timestamp when the country was added to the database

favorite_animals

• person_id: a foreign key pointing at the person table, identifies whose a favorite animal
• favorite_animal_id: a foreign key pointing at the animals table, identifies what is person's favorite animal
• other_animal: optional, the person can enter their favorite animal if the animal list doesn't include it
• created_on: timestamp when the favorite animal was added to the database

favorite_countries

• person_id: a foreign key pointing at the person table, identifies whose a favorite country
• favorite_country _id: a foreign key pointing at the countries table, identifies what is person's favorite country
• created_on: timestamp when the favorite country was added to the database

person

• person_id: a primary key to identify different person from list
  
• first_name: the person's first name
  
• last_name: the person's last name
• email: the person's email address, must be unique
  
• own_country_id: a foreign key pointing at the countries table, indentifies the person's own country
  
• created_on: timestamp when the person was added to the database

-- create tables

CREATE TABLE animals(
animal_id SERIAL PRIMARY KEY,
animal VARCHAR(250) UNIQUE,
created_on TIMESTAMP NOT NULL
);

CREATE TABLE countries(
country_id SERIAL PRIMARY KEY,
country VARCHAR(250) UNIQUE,
created_on TIMESTAMP NOT NULL
);

CREATE TABLE person(
person_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(100) NOT NULL,
email VARCHAR(250) UNIQUE NOT NULL CHECK (email LIKE '%_@_%._%'),
own_country_id INTEGER NOT NULL REFERENCES countries(country_id),
created_on TIMESTAMP NOT NULL
);

CREATE TABLE favorite_animals(
person_id INTEGER NOT NULL REFERENCES person(person_id),
favorite_animal_id INTEGER NOT NULL REFERENCES animals(animal_id),
other_animal VARCHAR(250)
);

CREATE TABLE favorite_countries(
person_id INTEGER NOT NULL REFERENCES person(person_id),
favorite_country_id INTEGER NOT NULL REFERENCES countries(country_id)
);

-- ChatGPT has been used to generate the values

-- add values to tables named animals and countries

INSERT INTO animals(animal, created_on)

VALUES

('Other', CURRENT_TIMESTAMP),

('Aardvark', CURRENT_TIMESTAMP),

('Albatross', CURRENT_TIMESTAMP),

('Alligator', CURRENT_TIMESTAMP),

('Ant', CURRENT_TIMESTAMP), 

-- continue --

('Zebra', CURRENT_TIMESTAMP);

INSERT INTO countries(country, created_on)

VALUES

('Afghanistan', CURRENT_TIMESTAMP),

('Albania', CURRENT_TIMESTAMP),

('Algeria', CURRENT_TIMESTAMP),

('Andorra', CURRENT_TIMESTAMP),

('Angola', CURRENT_TIMESTAMP),

-- continue --

('Zimbabwe', CURRENT_TIMESTAMP);

-- fictional persons, random values to the person table

INSERT INTO person(first_name, last_name, email, own_country_id, created_on)

VALUES

('John', 'Doe', 'johndoe@example.com', 56, CURRENT_TIMESTAMP),

('Alice', 'Smith', 'alicesmith@example.com', 123, CURRENT_TIMESTAMP),

('Michael', 'Johnson', 'michaeljohnson@example.com', 87, CURRENT_TIMESTAMP),

('Emily', 'Williams', 'emilywilliams@example.com', 41, CURRENT_TIMESTAMP),

('Daniel', 'Brown', 'danielbrown@example.com', 29, CURRENT_TIMESTAMP),

-- continue --

('Lucas', 'Harris', 'lucasharris@example.com', 132, CURRENT_TIMESTAMP);

-- random values to the favorite_countries table

INSERT INTO favorite_countries(person_id, favorite_country_id)

VALUES

(1, 56),

(2, 123),

(3, 87),

(4, 41),

(5, 29),

-- continue --

(103, 103);

-- random values to the favorite_animals table

INSERT INTO favorite_animals(person_id, favorite_animal_id)

VALUES

(1, 120),

(2, 184),

(3, 85),

(4, 33),

(5, 165),

-- continue --

(103, 154);

-- new column to the table named favorite_animals
-- must be column "created_on", because a person can add more than one favorite animal and country in the future

ALTER TABLE favorite_animals
ADD COLUMN created_on TIMESTAMP;

UPDATE favorite_animals
SET created_on = person.created_on
FROM person
WHERE favorite_animals.person_id = person.person_id;

ALTER TABLE favorite_animals
ALTER COLUMN created_on
SET NOT NULL; 

-- same to the table named favorite_countries

ALTER TABLE favorite_countries
ADD COLUMN created_on TIMESTAMP;

UPDATE favorite_countries
SET created_on = person.created_on
FROM person
WHERE favorite_countries.person_id = person.person_id;

ALTER TABLE favorite_countries
ALTER COLUMN created_on
SET NOT NULL;