Let’s make a SQLite database of the 17 United Nations Sustainable Development Goals and some social issues.
CREATE TABLE UNGoals (
id INTEGER PRIMARY KEY,
goal_name TEXT NOT NULL,
explanation TEXT
);
INSERT INTO UNGoals (id, goal_name, explanation) VALUES
(1, 'No Poverty', 'End poverty in all its forms everywhere.'),
(2, 'Zero Hunger', 'End hunger, achieve food security and improved nutrition and promote sustainable agriculture.'),
(3, 'Good Health and Well-being', 'Ensure healthy lives and promote well-being for all at all ages.'),
(4, 'Quality Education', 'Ensure inclusive and equitable quality education and promote lifelong learning opportunities for all.'),
(5, 'Gender Equality', 'Achieve gender equality and empower all women and girls.'),
(6, 'Clean Water and Sanitation', 'Ensure availability and sustainable management of water and sanitation for all.'),
(7, 'Affordable and Clean Energy', 'Ensure access to affordable, reliable, sustainable and modern energy for all.'),
(8, 'Decent Work and Economic Growth', 'Promote sustained, inclusive and sustainable economic growth, full and productive employment and decent work for all.'),
(9, 'Industry, Innovation and Infrastructure', 'Build resilient infrastructure, promote inclusive and sustainable industrialization and foster innovation.'),
(10, 'Reduced Inequality', 'Reduce inequality within and among countries.'),
(11, 'Sustainable Cities and Communities', 'Make cities and human settlements inclusive, safe, resilient and sustainable.'),
(12, 'Responsible Consumption and Production', 'Ensure sustainable consumption and production patterns.'),
(13, 'Climate Action', 'Take urgent action to combat climate change and its impacts.'),
(14, 'Life Below Water', 'Conserve and sustainably use the oceans, seas and marine resources for sustainable development.'),
(15, 'Life on Land', 'Protect, restore and promote sustainable use of terrestrial ecosystems, sustainably manage forests, combat desertification, and halt and reverse land degradation and halt biodiversity loss.'),
(16, 'Peace, Justice and Strong Institutions', 'Promote peaceful and inclusive societies for sustainable development, provide access to justice for all and build effective, accountable and inclusive institutions at all levels.'),
(17, 'Partnerships for the Goals', 'Strengthen the means of implementation and revitalize the global partnership for sustainable development.');
CREATE TABLE Social_Issues (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE NOT NULL,
description TEXT
);
-- Insert Social Issues
INSERT INTO Social_Issues (name, description) VALUES
('Affordable Housing', 'Ensuring people have access to safe, affordable living spaces.'),
('Employment', 'Providing job opportunities and workforce development.'),
('Food Security', 'Ensuring access to nutritious food for all.'),
('Water Access', 'Providing reliable clean water supply.'),
('Sanitation', 'Improving hygiene and waste management to prevent disease.'),
('Environmental Sustainability', 'Promoting eco-friendly practices and conservation efforts.'),
('Healthcare Access', 'Ensuring people have access to essential medical services.'),
('Education Equity', 'Providing equal learning opportunities for all individuals.'),
('Mental Health Support', 'Addressing psychological well-being and access to mental health services.'),
('Financial Inclusion', 'Ensuring access to financial services for underserved populations.'),
('Digital Divide', 'Bridging gaps in technology access and digital literacy.'),
('Gender Equality', 'Promoting equal rights and opportunities regardless of gender.'),
('Indigenous Rights', 'Advancing the rights and well-being of Indigenous communities.'),
('Climate Change Adaptation', 'Helping communities adjust to environmental changes.'),
('Youth Empowerment', 'Supporting young people with education, training, and leadership opportunities.'),
('Disability Inclusion', 'Ensuring accessibility and equal opportunities for people with disabilities.'),
('Refugee Support', 'Providing assistance and resources for displaced populations.'),
('Civic Engagement', 'Encouraging participation in social and political processes.'),
('Employment Equity', 'Promoting fair and inclusive work opportunities.'),
('Senior Support', 'Addressing the needs and well-being of elderly populations.'),
('Prisoner Reintegration', 'Helping formerly incarcerated individuals transition back into society.'),
('Human Trafficking Prevention', 'Combating forced labor and exploitation.'),
('LGBTQ+ Rights', 'Advocating for equal rights and protections for LGBTQ+ individuals.'),
('Addiction Recovery', 'Providing support for individuals struggling with substance abuse.'),
('Disaster Relief', 'Providing emergency aid and long-term recovery support for affected communities.'),
('Rural Development', 'Improving infrastructure and opportunities in rural areas.'),
('Urban Poverty', 'Addressing social and economic challenges in low-income urban areas.'),
('Fair Trade & Ethical Sourcing', 'Promoting fair wages and responsible supply chains.'),
('Child Protection', 'Preventing abuse, exploitation, and neglect of children.'),
('Access to Justice', 'Ensuring legal representation and rights protection for marginalized groups.');
-- Linking table:
-- goal_id references a specific UN goal.
-- issue_id references a specific social issue.
-- The combination of both columns forms the composite primary key, ensuring no duplicate links.
-- FOREIGN KEY constraints maintain referential integrity with the UNGoals and Social_Issues tables.
CREATE TABLE Goal_Issue_Link (
goal_id INTEGER NOT NULL,
issue_id INTEGER NOT NULL,
PRIMARY KEY (goal_id, issue_id),
FOREIGN KEY (goal_id) REFERENCES UNGoals(id),
FOREIGN KEY (issue_id) REFERENCES Social_Issues(id)
);
-- Goal 1: No Poverty
INSERT INTO Goal_Issue_Link (goal_id, issue_id) VALUES
(1, 1), -- Affordable Housing
(1, 2), -- Employment
(1, 26), -- Urban Poverty
(1, 25); -- Rural Development
-- Goal 2: Zero Hunger
INSERT INTO Goal_Issue_Link (goal_id, issue_id) VALUES
(2, 3), -- Food Security
(2, 25); -- Rural Development
-- Goal 3: Good Health and Well-being
INSERT INTO Goal_Issue_Link (goal_id, issue_id) VALUES
(3, 7), -- Healthcare Access
(3, 9), -- Mental Health Support
(3, 23), -- Addiction Recovery
(3, 5); -- Sanitation
-- Goal 4: Quality Education
INSERT INTO Goal_Issue_Link (goal_id, issue_id) VALUES
(4, 8), -- Education Equity
(4, 15), -- Youth Empowerment
(4, 10); -- Digital Divide
-- Goal 5: Gender Equality
INSERT INTO Goal_Issue_Link (goal_id, issue_id) VALUES
(5, 12), -- Gender Equality
(5, 22); -- LGBTQ+ Rights
-- Goal 6: Clean Water and Sanitation
INSERT INTO Goal_Issue_Link (goal_id, issue_id) VALUES
(6, 4), -- Water Access
(6, 5); -- Sanitation
-- Goal 7: Affordable and Clean Energy
-- (Could relate to rural development and sustainability)
INSERT INTO Goal_Issue_Link (goal_id, issue_id) VALUES
(7, 6), -- Environmental Sustainability
(7, 25); -- Rural Development
-- Goal 8: Decent Work and Economic Growth
INSERT INTO Goal_Issue_Link (goal_id, issue_id) VALUES
(8, 2), -- Employment
(8, 19), -- Employment Equity
(8, 10); -- Digital Divide
-- Goal 9: Industry, Innovation and Infrastructure
INSERT INTO Goal_Issue_Link (goal_id, issue_id) VALUES
(9, 10), -- Digital Divide
(9, 25); -- Rural Development
-- Goal 10: Reduced Inequality
INSERT INTO Goal_Issue_Link (goal_id, issue_id) VALUES
(10, 12), -- Gender Equality
(10, 16), -- Disability Inclusion
(10, 22), -- LGBTQ+ Rights
(10, 13); -- Indigenous Rights
-- Goal 11: Sustainable Cities and Communities
INSERT INTO Goal_Issue_Link (goal_id, issue_id) VALUES
(11, 1), -- Affordable Housing
(11, 5), -- Sanitation
(11, 26); -- Urban Poverty
-- Goal 12: Responsible Consumption and Production
INSERT INTO Goal_Issue_Link (goal_id, issue_id) VALUES
(12, 6), -- Environmental Sustainability
(12, 27); -- Fair Trade & Ethical Sourcing
-- Goal 13: Climate Action
INSERT INTO Goal_Issue_Link (goal_id, issue_id) VALUES
(13, 6), -- Environmental Sustainability