01. 연구 (2) 원전 정책 담론 연구 06 연구 인프라 부록

이 글은 이전 문서(00. 개요)의 후속 내용으로,

해당 연구 설계 및 구현 구조를 보다 구체적으로 정리했다.

 

블로그에 공개한 01–06 문서는 당시 컨택 과정에서 제출했던 18페이지 분량의 영어 연구계획서 가운데

방법론과 연구 구현에 해당한다. 

최종 제출본에는 분량 제한 때문에 일부만 반영되었지만,

방법론 부분이 가장 오래 수정 및 검토되었다.

 

연구계획서를 작성하는 일련의 과정 속에서,

나는 연구 과정을 스스로 설명할 수 있어야 한다는 점을 중요하게 여겼다.

 

예상과 다른 결과가 나오면 원인을 다시 확인했고, 구현 과정이 바뀌면 방법론도 함께 수정했다.

연구 질문과 분석 구조가 맞지 않는다고 판단한 부분은 여러 번 다시 설계했다.

 

그래서 블로그에 공개한 문서에는 완성된 결과보다 시행착오와 수정 과정, 

그리고 연구를 진행하면서 마주했던 한계와 고민이 더 많이 남아 있다.

 

본론

---

06. Appendix (Infrastructure Appendix)

---

본문에서는 연구의 전체 흐름만 다루었기 때문에 세부적인 데이터 구조와 코드 일부는 부록으로 분리했다.

ERD와 SQL 구조, 데이터 처리 과정을 함께 정리한 이유는

결과보다 연구가 어떤 과정을 거쳐 재현되는지를 남기고 싶었기 때문이다.

가능한 한 당시 구현했던 형태를 그대로 보존했다.

 

 

---

A. Data Processing Pipeline (Data Processing Pipeline)

The data processing pipeline of this study is an execution system that directly implements the structural framework defined in the research methodology (Chapter 5).

---

A.1 Overall Processing Flow

Literature Review and Theoretical Background
→ Query Design
→ BigKinds News Data Collection
→ Data Reduction
→ Data Cleaning
→ Core Keyword Construction
→ Corpus Construction
→ Analysis Filtering
→ TF-IDF Analysis (Python)
→ Discourse Interpretation and Visualization

---

A.2 Structural Characteristics

• Data Reduction: Reducing the dataset and removing noise
• Data Cleaning: Structural refinement and text normalization
• Core Keyword: Ensuring semantic alignment between concepts and data
• Analysis Filtering: Constructing sub-corpora based on research objectives
• Python-centric analysis structure (SQL is used only for aggregation and querying)

---

A.3 Core Principles

• Data processing ≠ analysis
• SQL ≠ analytical tool (serves only aggregation/query roles)
• All analytical operations are performed entirely in Python

---

B. Database Schema and ERD Structure (Relational Discourse Analysis Structure)

---

B.1 Overall Structural Flow

news_article
→ article_keyword_map
→ keyword_dictionary
→ nuclear_event
→ international_framework (extended)

---

B.2 Core Analytical Table: article_keyword_map

Column Type Constraint Description

article_id	INT	PK, FK	References news_article
keyword_id	INT	PK, FK	References keyword_dictionary
frequency	INT	DEFAULT 1	Keyword frequency within article
tfidf_score	DECIMAL(10,6)	NULL allowed	TF-IDF result from Python

---

[CORE ANALYSIS TABLES]
- news_article
- article_keyword_map
- keyword_dictionary
- nuclear_event

[EXTENSION / OPTIONAL MODULES]
- radiation_data
- international_framework
- plant_event_map

---

B.3 Core Analytical Data Structure (TiDB-based)

The TF-IDF scores and keyword frequency metrics are managed through the article_keyword_map table in TiDB.

// TiDB Core Analytical Interface Table Structure
Table article_keyword_map{
article_id int [ref:>news_article.article_id]
keyword_id int [ref:>keyword_dictionary.keyword_id]
frequency int [default:1,note:"Keyword frequency within article"]
tfidf_score decimal(10,6) [note:"Output from Python analysis"]

Indexes{
    (article_id,keyword_id) [pk]
keyword_id
tfidf_score
  }
}

---

B.5 TiDB Cloud ERD and Schema Structure

The overall schema implemented in the TiDB Cloud environment integrates news discourse data and policy event data.

• Core tables: news_article, keyword_dictionary, article_keyword_map
• Policy linkage: nuclear_event, news_event_map, plant

// Nuclear Plant Management
Table plant{
plant_id int [pk]
plant_name varchar(100) [notnull]
}

// Policy Event Structure
Table nuclear_event{
event_id int [pk]
event_name varchar(255)
period_stage varchar(50) [note:"CONFLICT, MODEL, SAFETY, etc."]
}

// News Article Main Table
Table news_article{
article_id int [pk]
plant_id int [ref:>plant.plant_id]
title varchar(255) [notnull]
content text
source varchar(100)
url varchar(255)
published_date date
sentiment_score decimal(3,2)
period_type varchar(50)
}

// Keyword Dictionary
Table keyword_dictionary{
keyword_id int [pk]
keyword varchar(100) [notnull]
category varchar(50)
}

// Many-to-Many Analysis Mapping Table
Table article_keyword_map{
article_id int [ref:>news_article.article_id]
keyword_id int [ref:>keyword_dictionary.keyword_id]
frequency int
tfidf_score decimal(10,6)
}

// News-Event Mapping Table
Table news_event_map{
article_id int [ref:>news_article.article_id]
event_id int [ref:>nuclear_event.event_id]
}

---

B.6 DBML Full ERD Structure

Table nuclear_plant {
  plant_id int [pk, increment]
  name varchar(100) [not null]
  location varchar(255)
  status varchar(20)
  built_year int
}

Table nuclear_event {
  event_id int [pk, increment]
  name varchar(100) [not null]
  event_date date [not null]
  event_end_date date
  event_type varchar(50)
  importance_level int
  period_stage int
  description text
}

Table news_article {
  article_id int [pk, increment]
  title varchar(500) [not null]
  content text [not null]
  source varchar(100) [not null]
  url text
  published_date date [not null]
  period_type varchar(10)
  plant_id int
}

Table keyword_dictionary {
  keyword_id int [pk, increment]
  keyword varchar(50) [unique, not null]
  category varchar(50)
}

Table keyword_synonym {
  synonym_id int [pk, increment]
  keyword_id int [not null]
  synonym varchar(50) [not null]
}

Table article_keyword_map {
  map_id int [pk, increment]
  article_id int [not null]
  keyword_id int [not null]
  frequency int
  tfidf_score decimal(10,6)
}

Table news_event_map {
  map_id int [pk, increment]
  article_id int [not null]
  event_id int [not null]
  period_type varchar(10)
}

Table plant_event_map {
  map_id int [pk, increment]
  plant_id int [not null]
  event_id int [not null]
}

Table radiation_data {
  data_id int [pk, increment]
  plant_id int [not null]
  measured_at datetime [not null]
  radiation_lvl float [not null]
  unit varchar(10)
}

Table international_organization {
  org_id int [pk, increment]
  name varchar(100) [not null]
  type varchar(50)
}

Table international_framework {
  framework_id int [pk, increment]
  org_id int [not null]
  name varchar(200) [not null]
  published_year int
  framework_type varchar(50)
  description text
}

Table event_framework_map {
  map_id int [pk, increment]
  event_id int [not null]
  framework_id int [not null]
}

Table framework_keyword_map {
  map_id int [pk, increment]
  framework_id int [not null]
  keyword_id int [not null]
}

---

B.7 Relationship Definitions

Ref:news_event_map.article_id>news_article.article_id [delete:cascade]
Ref:news_event_map.event_id>nuclear_event.event_id [delete:cascade]

Ref:article_keyword_map.article_id>news_article.article_id [delete:cascade]
Ref:article_keyword_map.keyword_id>keyword_dictionary.keyword_id [delete:cascade]

Ref:keyword_synonym.keyword_id>keyword_dictionary.keyword_id [delete:cascade]

Ref:plant_event_map.plant_id>nuclear_plant.plant_id [delete:cascade]
Ref:plant_event_map.event_id>nuclear_event.event_id [delete:cascade]

Ref:radiation_data.plant_id>nuclear_plant.plant_id [delete:cascade]

Ref:event_framework_map.event_id>nuclear_event.event_id [delete:cascade]
Ref:event_framework_map.framework_id>international_framework.framework_id [delete:cascade]

Ref:framework_keyword_map.framework_id>international_framework.framework_id [delete:cascade]
Ref:framework_keyword_map.keyword_id>keyword_dictionary.keyword_id [delete:cascade]

Ref:international_framework.org_id>international_organization.org_id [delete:cascade]

---

B.8 ERD Relationship Structure

1. Core analytical structure: news_article → article_keyword_map → keyword_dictionary
2. Policy event structure: news_article → news_event_map → nuclear_event
3. International extension structure: nuclear_event → event_framework_map → international_framework

---

C. SQL Aggregation Layer

• SQL role: aggregation and querying
• Analysis performed in Python (TF-IDF)

-- Keyword frequency aggregation
SELECT k.keyword, SUM(ak.frequency) AS total_count
FROM article_keyword_map ak
JOIN keyword_dictionary k ON ak.keyword_id = k.keyword_id
GROUP BY k.keyword;

-- Event-based keyword aggregation
SELECT ne.name, k.keyword, SUM(ak.frequency) AS total_count
FROM news_event_map nem
JOIN nuclear_event ne ON nem.event_id = ne.event_id
JOIN article_keyword_map ak ON nem.article_id = ak.article_id
JOIN keyword_dictionary k ON ak.keyword_id = k.keyword_id
GROUP BY ne.name, k.keyword;

---

D. Python-based TF-IDF Analysis

D.1 Preprocessing Code

importpandasaspd
fromcollectionsimportCounter

STOPWORDS= {...}
TARGET_RULES= [(4000,3000),(3000,2000),(2000,1500)]

defmerge_rank_files(files,top_n=10):
    ...

defextract_keywords(df,top_n=10):
    ...

defrun_preprocessing(fp,keywords,out_dir):
    ...

---

D.2 Analysis Structure

Python → TF-IDF Analysis
SQL    → Aggregation & Query
Researcher → Interpretation

---

E. Visualization & System Architecture (Web + Chart.js)

E.1 System Flow

DB → Spring Boot → Mapper → JSP View → Chart.js

---

E.2 Features

• Temporal discourse change analysis
• Pre/Post policy event comparison
• Keyword trend tracking

---

F. Deployment (RENDER)

The system is deployed on the RENDER cloud environment, integrating a Python-based TF-IDF analysis engine with a Spring Boot web application to provide real-time visualization of temporal and keyword-based discourse analysis results.

F.1 Architecture

Python TF-IDF Engine → MySQL DB → Spring Boot Application → JSP/Chart.js
                             ↑
                         RENDER Cloud

---

F.2 Deployment Steps

1. GitHub integration → automatic deployment via main branch
2. Build: ./gradlew bootBuild, Runtime: Java 21 + Spring Boot 4.0.5
3. Database environment configuration
4. Python TF-IDF executed via API or batch jobs during deployment

---

F.3 CI/CD

• Git push → automatic deployment
• Gradle build → Spring Boot execution → JSP/Chart.js rendering

---

G. Summary

Layer Role

Python	TF-IDF analysis, data processing
SQL	Aggregation/query, data provision
DB (MySQL)	Data storage, relational integrity
Spring Boot	API layer, JSP integration
Chart.js	Visualization
RENDER	Cloud deployment

Core Table: article_keyword_map

→ Integrated analytical interface for TF-IDF scores, frequency, and article-keyword relationships.

---

This appendix represents the implemented system at the prototype stage using a subset of the full dataset for demonstration purposes. The complete dataset and extended processing pipeline will be fully applied in the final system implementation phase.