· 6 years ago · Dec 06, 2019, 05:28 PM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema mydb
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
15-- -----------------------------------------------------
16-- Schema country
17-- -----------------------------------------------------
18USE `mydb` ;
19
20-- -----------------------------------------------------
21-- Table `mydb`.`countries`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS countries (
24 `country_id` INT NOT NULL,
25 `country_name` VARCHAR(100) NULL,
26 `total_population` INT NULL,
27 `population_density` INT NULL,
28 `most_pop_city` VARCHAR(100) NULL,
29 PRIMARY KEY (country_id));
30
31
32-- -----------------------------------------------------
33-- Table `mydb`.`table1`
34-- -----------------------------------------------------
35
36
37
38-- -----------------------------------------------------
39-- Table `mydb`.`geography`
40-- -----------------------------------------------------
41CREATE TABLE IF NOT EXISTS geography (
42 `country_id` INT NOT NULL,
43 `area` INT NULL,
44 `arable_percentage` DECIMAL(9,1) NULL,
45 `coastal_area` INT NULL,
46 `crops_percentage` DECIMAL(9,1) NULL,
47 `other` INT NULL,
48 `avg_temp` DECIMAL(9,1) NULL,
49 `min_temp` DECIMAL(9,1) NULL,
50 `max_temp` DECIMAL(9,1) NULL,
51 `avg_rain` DECIMAL(9,1) NULL,
52 `min_rain` DECIMAL(9,1) NULL,
53 `max_rain` DECIMAL(9,1) NULL,
54 # INDEX `fk_geography_countries_idx` (`country_id` ASC) VISIBLE,
55
56 CONSTRAINT fk_geography_countries
57 FOREIGN KEY (country_id)
58 REFERENCES countries (country_id)
59 );
60
61
62-- -----------------------------------------------------
63-- Table `mydb`.`health`
64-- -----------------------------------------------------
65CREATE TABLE IF NOT EXISTS health (
66 `country_id` INT NOT NULL,
67 `fertility_rate` DECIMAL(9,1) NULL,
68 `m_life_expectancy` DECIMAL(9,1) NULL,
69 `f_life_expectancy` DECIMAL(9,1) NULL,
70 `healthcol` VARCHAR(45) NULL,
71 `physicians` DECIMAL(9,1) NULL,
72 `dentists` DECIMAL(9,1) NULL,
73 `pharmacists` DECIMAL(9,1) NULL,
74 `nurse_midwives` DECIMAL(9,1) NULL,
75 `expenditure` DECIMAL(9,1) NULL,
76 `domestic_gov_exp` DECIMAL(9,1) NULL,
77 `healthcol1` VARCHAR(45) NULL,
78 #INDEX `fk_health_countries1_idx` (`country_id` ASC) VISIBLE,
79 CONSTRAINT fk_health_countries11
80 FOREIGN KEY (country_id)
81 REFERENCES countries (country_id));
82
83
84-- -----------------------------------------------------
85-- Table `mydb`.`education`
86-- -----------------------------------------------------
87CREATE TABLE IF NOT EXISTS education (
88 `country_id` INT NOT NULL,
89 `literacy_rate` INT NOT NULL,
90 `public_expenditure` DECIMAL(9,1) NULL,
91 `pupil_teacher_ratio_primary` DECIMAL(9,1) NULL,
92 `pupil_teacher_ratio_secondary` DECIMAL(9,1) NULL,
93 `primary_enrollment_ratio` DECIMAL(9,1) NULL,
94 `secondary_enrollment_ratio` DECIMAL(9,1) NULL,
95 `tertiary_enrollment_ratio` DECIMAL(9,1) NULL,
96 `educationcol` VARCHAR(45) NULL,
97 CONSTRAINT fk_education_countries11
98 FOREIGN KEY (country_id)
99 REFERENCES countries (country_id));
100
101
102
103-- -----------------------------------------------------
104-- Table `mydb`.`economy`
105-- -----------------------------------------------------
106CREATE TABLE IF NOT EXISTS economy(
107 `country_id` INT NOT NULL,
108 `gdp` INT NULL,
109 `currency` VARCHAR(45) NULL,
110 `growth_rate` DECIMAL(9,1) NULL,
111 `unemployment_rate` DECIMAL(9,1) NULL,
112 `gdp_agriculture` DECIMAL(9,1) NULL,
113 `gdp_industry` DECIMAL(9,1) NULL,
114 `gdp_services` DECIMAL(9,1) NULL,
115 CONSTRAINT fk_economy_countries1
116 FOREIGN KEY (country_id)
117 REFERENCES countries (country_id));
118
119
120
121-- -----------------------------------------------------
122-- Table `mydb`.`crime`
123-- -----------------------------------------------------
124CREATE TABLE IF NOT EXISTS crime(
125 `country_id` INT NOT NULL,
126 `murder_rate` DECIMAL(9,1) NULL,
127 `arrest_rate` DECIMAL(9,1) NULL,
128 `incarceration_percentage` DECIMAL(9,1) NULL,
129 CONSTRAINT fk_economy_countries1
130 FOREIGN KEY (country_id)
131 REFERENCES countries (country_id));
132
133
134
135-- -----------------------------------------------------
136-- Table `mydb`.`tourism`
137-- -----------------------------------------------------
138CREATE TABLE IF NOT EXISTS tourism (
139 `country_id` INT NOT NULL,
140 `tourists` INT NULL,
141 `tourist_expenditure` INT NULL,
142 INDEX `fk_tourism_countries1_idx` (`country_id` ASC) VISIBLE,
143CONSTRAINT fk_economy_countries1
144 FOREIGN KEY (country_id)
145 REFERENCES countries (country_id));
146
147
148-- -----------------------------------------------------
149-- Table `mydb`.`threatened_species`
150-- -----------------------------------------------------
151CREATE TABLE IF NOT EXISTS threatened_species(
152 `country_id` INT NOT NULL,
153 `vertebrates` INT NULL,
154 `invertebrates` INT NULL,
155 `plants` INT NULL,
156CONSTRAINT fk_economy_countries1
157 FOREIGN KEY (country_id)
158 REFERENCES countries (country_id));
159
160
161-- -----------------------------------------------------
162-- Table `mydb`.`science`
163-- -----------------------------------------------------
164CREATE TABLE IF NOT EXISTS science(
165 `country_id` INT NOT NULL,
166 `researchers` INT NULL,
167 `technicians` INT NULL,
168 `support_staff` INT NULL,
169 `patent_grants` INT NULL,
170 `sciencecol` VARCHAR(45) NULL,
171 CONSTRAINT fk_economy_countries1
172 FOREIGN KEY (country_id)
173 REFERENCES countries (country_id));
174
175*/
176SET SQL_MODE=@OLD_SQL_MODE;
177SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
178SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;