assignment2

CREATE DATABASE IF NOT EXISTS assignment2;
USE assignment2;

CREATE TABLE continents (
    cont_name VARCHAR(20) NOT NULL PRIMARY KEY,
    cont_area VARCHAR(20) NOT NULL
);

CREATE TABLE islands (
    island_name VARCHAR(20) NOT NULL PRIMARY KEY,
    island_area VARCHAR(20) NOT NULL
);

CREATE TABLE oceans (
    ocean_name VARCHAR(20) NOT NULL PRIMARY KEY,
    ocean_area VARCHAR(20) NOT NULL
);

CREATE TABLE straits (
    strait_name VARCHAR(20) NOT NULL PRIMARY KEY,
    strait_area VARCHAR(20) NOT NULL
);


CREATE TABLE connected_to (
    /* Use the continent name as the primary key */
    cont_name VARCHAR(20) NOT NULL PRIMARY KEY,
    /* the connection is made using a foreign key */
    connected_to VARCHAR(20) NOT NULL,
    FOREIGN KEY (connected_to) REFERENCES continents(cont_name)
);
/*
CREATE TABLE seperated (
    cont_name VARCHAR(20) NOT NULL PRIMARY KEY,
    cont_area VARCHAR(20) NOT NULL
);

CREATE TABLE located_in (
    cont_name VARCHAR(20) NOT NULL PRIMARY KEY,
    cont_area VARCHAR(20) NOT NULL
);
*/


INSERT INTO continents
VALUES
    ('Africa', '11,730,000 sq miles'),
    ('Antartica', '5,400,000 sq miles'),
    ('Asia', '17,212,000 sq miles'),
    ('Europe', '3,930,000 sq miles'),
    ('North America', '9,540,000 sq miles'),
    ('Australia', '3,300,000 sq miles'),
    ('South America', '6,890,000 sq miles');

INSERT INTO oceans
VALUES
    ('Atlantic', '41,100,000 sq miles'),
    ('Artic', '5,427,000 sq miles'),
    ('Southern', '7,849,000 sq miles'),
    ('Indian', '27,240,000 sq miles'),
    ('Pacific', '63,800,000 sq miles');

INSERT INTO islands
VALUES
    /* African Islands */
    ('Addilo Island', '25,000 sq miles'),
    ('Bandarka Island', '32,300 sq miles'),
    ('Bangadini Island', '30,700 sq miles'),
    ('Bavadi Island', '42,500 sq miles'),
    ('Turtle Island', '23,400 sq miles'),
    /* Anartica Islands */
    ('Amherst Island', '24,300 sq miles'),
    ('Davis Island', '18,400 sq miles'),
    ('Frenchs Island', '30,000 sq miles'),
    ('Green Island', '15,500 sq miles'),
    ('Rose Island', '29,100 sq miles'),
    /* Asian Islands */
    ('Kamley Island', ' 10,500 sq miles'),
    ('Okushiri Island', '8,300 sq miles'),
    ('Teuri Island', '5,200 sq miles'),
    ('Kamome Island', '2,200 sq miles'),
    ('Rebun Island', '8,400 sq miles'),
    /* European Islands */
    ('Satava Island', '1,000 sq miles'),
    ('Nagu Island', '3,200 sq miles'),
    ('Jurmo Island', '1,800 sq miles'),
    ('Kataja Island', '4,400 sq miles'),
    ('Pellinki Island', '2,800 sq miles'),
    /* North American Islands */
    ('Adams Island', '800 sq miles'),
    ('Brock Island', '550 sq miles'),
    ('Lougheed Island', '3,200 sq miles'),
    ('Cornwall Island', '650 sq miles'),
    ('Massey Island', '200 sq miles'),
    /* Australian Islands */
    ('Tasmania Island', '25,000 sq miles'),
    ('Melville Island', '2,200 sq miles'),
    ('Kangaroo Island', '1,700 sq miles'),
    ('Groote Island', '2,200 sq miles'),
    ('King Island', '1,000 sq miles'),
    /* South American Islands */
    ('Bermejo Island', '850 sq miles'),
    ('San Martin Island', '1,200 sq miles'),
    ('Cerrito Island', '900 sq miles'),
    ('Martin Garcia Island', '400 sq miles'),
    ('Victoria Island', '950 sq miles');

INSERT INTO straits
VALUES
    /* Between Adams Island and Brock Island North American Strait */
    ('Formosa Strait', '80 miles'),
    /* Between South America and Antartica Strait*/
    ('Drake Passage', '800 miles'),
    /* Between North America and Asia Strait*/
    ('Bering Strait', '50  miles'),
    /* Between Europe and Asia Strait */
    ('Dardanelles Strait', '38 miles'),
    /* Between Satava and Nagu Islands European Strait */
    ('Kvarken Strait', '120 miles'),
    /* Between Tasmania and Kangaroo Island Australian Strait */
    ('Torres Strait', '35 miles'),
    /* Between Antartica and Australia Strait */
    ('Bass Strait', '200 miles');

INSERT INTO connected_to
VALUES
    /* Ensure that any continent can have a connection shown */
    ('Africa', 'Europe'),
    ('Europe', 'Africa'),
    ('Australia', 'Asia'),
    ('Asia', 'Australia'),
    ('North America', 'South America'),
    ('South America', 'North America'),
    ('Antartica', 'South America');