CREATE DATABASE ayuda;

USE ayuda;

CREATE TABLE Country (
name VARCHAR(52) NOT NULL,
code CHAR(3) PRIMARY KEY,
surface_area FLOAT,
gnp FLOAT,
population INT,
life_expectancy FLOAT(3, 1),
continent  ENUM('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL,
government VARCHAR(45),
head_of_state VARCHAR(60),
capital INT,
FOREIGN KEY (capital) REFERENCES City(id));

CREATE TABLE City (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(35) NOT NULL,
region VARCHAR(20),
population INT,
country_code CHAR(3) NOT NULL,
FOREIGN KEY (country_code) REFERENCES Country(code));

CREATE TABLE Speaks (
country_code CHAR(3),
language VARCHAR(30),
official ENUM('T', 'F'),
percentage FLOAT(4, 1),
PRIMARY KEY (country_code, language),
FOREIGN KEY (country_code) REFERENCES Country(code));