Purpose: To design a complete database management system to address a practical database need and implement a relational database in MySQL. The particular database that I chose, in theory, creates a searchable archive of information documenting historical military interventions that the U.S. has taken part in either as the lead or as a partner country. It seeks to compile, consolidate, draw connections, and make accessible a large body of information about an issue that has had profound effects throughout history on global governments and economies.
Potential database users are students, researchers, policymakers, diplomats, activists, and anyone interested in U.S. and global history. This database complements another project in GIS and data analysis that I completed using the Military Intervention Project (MIP) dataset in fall 2023.
Potential Database Queries: Which administration was responsible for the most amount of military interventions? Which administration was responsible for the largest scope of military expenditures, and/or human costs, and/or other key effects? Which region has the U.S. focused the majority of its interventions on, and has this changed over time? Who and where has suffered the most in terms of human cost? Is there a correlation between a country’s military budget and an increase in military intervention? What military objective has been cited the most? Which objective is correlated with the greatest financial impacts and/or human costs?
Results & Next Steps: The final product is a functioning mySQL database that can display answers to the queries above. Some of the data included is directly pulled from MIP and other reliable sources, but in order to create the necessary 17 tables in the time allotted, some tables were populated with dummy data. (A necessary next step would be to ensure content accuracy by populating all tables with historically accurate information).
Methods: Research Datasets; Compile Data; Draft ERD (Entity Relationship Diagram); Create Database in mySQL; Upload Dummy Data; Query the Database; Dump and Host the mySQL code on GitHub
Skills: SQL; Relational Database Design and Development
Tools Used: mySQL Workbench, GitHub, Excel, Google Sheets, Lucidchart
Learning Outcomes: Technology, User-centered Design
Project Background:
This database will facilitate both deep dives into selected military engagements and intervention histories of particular regions, as well as aggregated, big picture analyses of the historical and present-day implications of U.S. military interventions. Analyzing this, in turn, can help streamline individual and journalistic efforts to educate citizens as well as provide momentum towards diplomacy solutions that are rooted in a thorough knowledge of U.S. military history. As researchers on the Military Intervention Project at the Tufts University Center for Strategic Studies assert, “armed with this knowledge, our government can make better informed decisions and become aware of explicit trade-offs when it comes to ongoing and future US military interventions” (2023). To streamline access to this complex set of information, this database focuses on linking major pieces of related historical military intervention information together.
Above: the new database's Entity Relationship Diagram (ERD)
An Entity Relationship (ER) Diagram is a type of flowchart that illustrates how “entities” such as people, objects or concepts relate to each other within a system. ER Diagrams are most often used to design or debug relational databases in the fields of software engineering, business information systems, education and research. Also known as ERDs or ER Models, they use a defined set of symbols such as rectangles, diamonds, ovals and connecting lines to depict the interconnectedness of entities, relationships and their attributes. - Lucidchart
U.S. military history is well documented across sources, but is often categorized under different topics, focused on only certain aspects at one time, such as long-term effects or incident details, or requires detailed research to consolidate and analyze holistically. The information collected and stored in this database should focus on broad variables associated with a particular military intervention, from partner countries to casualties to infrastructure affected to governments and budgets involved.
Two major sources of information are currently being built or are already available. The Military Intervention Project (MIP) has so far produced two datasets, one original and one time-series-focused, built to comprehensively cover “all instances of US military intervention from 1776 until 2019, alongside key drivers and consequences of these interventions” (2023). The original dataset from MIP contains 570 lines of data and over 200 potential variables, though only a third or so of these variables have been populated. There also exist historical data from the U.S. government on budget, expenditure, and more from the Congressional Budget Office, alongside various think tanks and nonprofit organizations that have analyzed data on historical spending on foreign aid and armed interventions over time.
Project Implementation: Queries in Action
The screenshots below showcase the database's implementation, from viewing the initial scope of the data that was entered into the 17 different tables, to running various queries to answer the research questions, to generally highlighting the features that support the user needs I identified in the brief above.
I chose to populate accurate military intervention names, country associations, descriptions, objectives, start and end years, and presidential administrations in those tables. The remaining tables I populated with dummy data as a placeholder for testing the database. A necessary next step would be to ensure content accuracy by populating all of the tables with historically accurate information.
View all U.S. military interventions listed in the intervention table in the database.
View all U.S. leaders in database
View all countries involved with U.S. intervention in database
Examining detailed information about each U.S. military intervention by joining multiple tables.
Aggregate and check minimum, maximum, and average duration of interventions entered in the database.
View frequency and duration information of each U.S. military intervention by country location.
View objective and casualty information about each U.S. military intervention by joining tables. This is primarily dummy data to test the casualty category.
Aggregate interventions by largest casualty numbers (dummy data).
Sources
Chantrill, Christopher. (2023). US Government Defense Spending. https://www.usgovernmentspending.com/defense_spending_history.
Congressional Budget Office. (2023). Defense Budget. https://www.cbo.gov/topics/defense-and-national-security/defense-budget.
Kushi, S., & Toft, M. D. (2023). Introducing the Military Intervention Project: A New Dataset on US Military Interventions, 1776–2019. Journal of Conflict Resolution, 67(4), 752-779. https://doi.org/10.1177/00220027221117546
Military Intervention Project (MIP). (2023). Tufts University Center for Strategic Studies. https://sites.tufts.edu/css/mip-research/.
Peter G. Peterson Foundation. (2023). Budget Basics: National Defense. https://www.pgpf.org/budget-basics/budget-explainer-national-defense.
Singer-Vine, Jeremy. (2023). Data is Plural - Structured Archive. https://docs.google.com/spreadsheets/d/1wZhPLMCHKJvwOkP4juclhjFgqIY8fQFMemwKL2c64vk/edit#gid=0.
Sullivan, Patricia L., and Michael T. Koch. 2009. "Military Intervention by Powerful States, 1945-2003." Journal of Peace Research 46 (5): 707-718.
UC San Diego Library Guides. International Statistics & Comparative Datasets. (2023). Political Science: Conflict, Military & Security. https://ucsd.libguides.com/politicalscience/conflict.
USA Spending: Department of Defense Overview. (2023). https://www.usaspending.gov/agency/department-of-defense?fy=2023.