database_sql_code_with_queries

USE tracker_analysis;

CREATE TABLE countries (
    country_id INT AUTO_INCREMENT PRIMARY KEY,
    country_name VARCHAR(255) NOT NULL
);

CREATE TABLE org_type (
    org_type_id INT AUTO_INCREMENT PRIMARY KEY,
    org_type VARCHAR(255) NOT NULL
);

INSERT INTO org_type (org_type_id, org_type) VALUES
(1, 'Government'),
(2, 'Private Corporation'),
(3, 'Education Provider'),
(4, 'Religious Institutions'),
(5, 'Non-Profit Organisation'),
(6, 'Association'),
(7, 'Local Government'),
(8, 'Statutory Corporation'),
(9, 'Commonwealth');

CREATE TABLE organisations (
    organisation_id INT AUTO_INCREMENT PRIMARY KEY,
    organisation_name VARCHAR(255) NOT NULL,
    country_id INT,
    org_type_id INT,
    FOREIGN KEY (country_id) REFERENCES countries(country_id),
    FOREIGN KEY (org_type_id) REFERENCES org_type(org_type_id)
);

CREATE TABLE websites (
    website_id INT AUTO_INCREMENT PRIMARY KEY,
    website_name VARCHAR(255) NOT NULL,
    website_url VARCHAR(255) NOT NULL,
    organisation_id INT,
    FOREIGN KEY (organisation_id) REFERENCES organisations(organisation_id)
);

CREATE TABLE domains (
    domain_id INT AUTO_INCREMENT PRIMARY KEY,
    domain_url VARCHAR(255) NOT NULL
);

CREATE TABLE domain_owners (
    domain_id INT,
    organisation_id INT,
    PRIMARY KEY (domain_id, organisation_id),
    FOREIGN KEY (domain_id) REFERENCES domains(domain_id),
    FOREIGN KEY (organisation_id) REFERENCES organisations(organisation_id)
);

CREATE TABLE trackers (
    tracker_id INT AUTO_INCREMENT PRIMARY KEY,
    tracker_url VARCHAR(255) NOT NULL,
    domain_id INT,
    FOREIGN KEY (domain_id) REFERENCES domains(domain_id)
);

CREATE TABLE website_tracker (
    website_id INT,
    tracker_id INT,
    PRIMARY KEY (website_id, tracker_id),
    FOREIGN KEY (website_id) REFERENCES websites(website_id),
    FOREIGN KEY (tracker_id) REFERENCES trackers(tracker_id)
);
DESCRIBE countries;
DESCRIBE websites;
DESCRIBE trackers;
DESCRIBE organisations;
DESCRIBE website_tracker;
DESCRIBE domains;
DESCRIBE domain_owners;
DESCRIBE websites;
DESCRIBE org_type;


SELECT * FROM countries;

SELECT * FROM org_type;

SELECT 
    organisations.organisation_id,
    organisations.organisation_name,
    countries.country_name,
    org_type.org_type
FROM organisations
JOIN countries ON organisations.country_id = countries.country_id
JOIN org_type ON organisations.org_type_id = org_type.org_type_id;

SELECT 
    websites.website_id,
    websites.website_name,
    websites.website_url,
    organisations.organisation_name,
    countries.country_name
FROM websites
JOIN organisations ON websites.organisation_id = organisations.organisation_id
JOIN countries ON organisations.country_id = countries.country_id;

SELECT 
    trackers.tracker_id,
    trackers.tracker_url,
    domains.domain_url,
    organisations.organisation_name,
    countries.country_name
FROM trackers
JOIN domains ON trackers.domain_id = domains.domain_id
JOIN domain_owners ON domains.domain_id = domain_owners.domain_id
JOIN organisations ON domain_owners.organisation_id = organisations.organisation_id
JOIN countries ON organisations.country_id = countries.country_id;

SELECT 
    websites.website_name,
    websites.website_url,
    trackers.tracker_url,
    domains.domain_url
FROM website_tracker
JOIN websites ON website_tracker.website_id = websites.website_id
JOIN trackers ON website_tracker.tracker_id = trackers.tracker_id
JOIN domains ON trackers.domain_id = domains.domain_id;

SELECT DISTINCT
    countries.country_name AS website_owner_country,
    tracker_countries.country_name AS tracker_owner_country
FROM websites
JOIN organisations ON websites.organisation_id = organisations.organisation_id
JOIN countries ON organisations.country_id = countries.country_id
JOIN website_tracker ON websites.website_id = website_tracker.website_id
JOIN trackers ON website_tracker.tracker_id = trackers.tracker_id
JOIN domains ON trackers.domain_id = domains.domain_id
JOIN domain_owners ON domains.domain_id = domain_owners.domain_id
JOIN organisations AS tracker_owners ON domain_owners.organisation_id = tracker_owners.organisation_id
JOIN countries AS tracker_countries ON tracker_owners.country_id = tracker_countries.country_id;

SELECT
    countries.country_name,
    SUM(CASE WHEN org_type.org_type = 'Government' THEN 1 ELSE 0 END) AS government_websites,
    SUM(CASE WHEN org_type.org_type = 'Private Corporation' THEN 1 ELSE 0 END) AS private_corporation_websites,
    SUM(CASE WHEN org_type.org_type = 'Education Provider' THEN 1 ELSE 0 END) AS education_provider_websites,
    SUM(CASE WHEN org_type.org_type = 'Religious Institutions' THEN 1 ELSE 0 END) AS religious_institution_websites,
    SUM(CASE WHEN org_type.org_type = 'Non-Profit Organisation' THEN 1 ELSE 0 END) AS non_profit_websites,
    SUM(CASE WHEN org_type.org_type = 'Association' THEN 1 ELSE 0 END) AS association_websites,
    SUM(CASE WHEN org_type.org_type = 'Local Government' THEN 1 ELSE 0 END) AS local_government_websites,
    SUM(CASE WHEN org_type.org_type = 'Statutory Corporation' THEN 1 ELSE 0 END) AS statutory_corporation_websites,
    SUM(CASE WHEN org_type.org_type = 'Commonwealth' THEN 1 ELSE 0 END) AS commonwealth_websites
FROM websites
JOIN organisations ON websites.organisation_id = organisations.organisation_id
JOIN countries ON organisations.country_id = countries.country_id
JOIN org_type ON organisations.org_type_id = org_type.org_type_id
JOIN website_tracker ON websites.website_id = website_tracker.website_id
JOIN trackers ON website_tracker.tracker_id = trackers.tracker_id
JOIN domains ON trackers.domain_id = domains.domain_id
WHERE domains.domain_url = 'tiktok.com'
GROUP BY countries.country_name
ORDER BY countries.country_name;