-----------------------------------------------------------------
Week 3: Querying data using SQL
제3주차: 스퀠(SQL)을 이용한 자료 조회
- How to use databases to analyse your data
획득한 자료를 분석하기 위해 데이터베이스 활용하는 법
- Investigating exoplanets in other solar systems
다른 태양계의 외계행성 탐사
-----------------------------------------------------------------
Lesson 1. Organizing your data / 한글자막
-----------------------------------------------------------------
2강: 외계 행성을 찾아서
Lesson 2: Other world, Distant sun: Search for exoplanet / 한글자막
-----------------------------------------------------------------
Lesson 3: Querying Database with SQL / 한글자막
-----------------------------------------------------------------
4강: SQL 약간 고급 사용법
Lesson 4: More Advanced SQL / 한글자막
-----------------------------------------------------------------
5강: SQL의 표 합치기(거주가능 외계행성)
Lesson 5: Joining Table in SQL / 한글자막 / 영문자막
[강의대본]
[00:06] 이제 큰 궁금증에 답할 만한 기법은 거의 배운 셈이다. 그 궁금증은 주성에서 생명체가 존재할만한 위치에 지구만한 크기의 행성들이 있을까? 라는 것이었다. 여기에 한가지 놓친 부분이 있다.
[00:16] 이 궁금증을 해소하려면 행성에 대한 정보를 알 필요가 있다. 그 정보들은 외계행성 표에 저장돼 있다. 아울러 주성에 대한 정보도 알아야 한다. 이 정보들은 별(Star)로 분류된 표에 따로 저장돼 있다. 왜 이 정보들은 다른 표에 저장되어 있는지 어떻게 하면 행성 표와 합칠 수 있을까?
[00:33] 자료와 그 관리체계를 조직하는 방법에 관한 사항은 별개의 연구 과제 인데 소위 데이터베이스 정규화(database normalization)라고 한다. 데이터베이스를 설계할 때 자료가 여러곳에 중복된 경우 중복성의 최소화와 자료 무결성 강화를 목표로 둬야 한다.
[00:50] 일반적으로 서로다른 형을 갖는 자료들 사이의 관계가 일대일로 대응된 경우 단일 표에 합치는 것이 타당하다. 하지만 우리의 예에서는 그렇지 못했다. 천문가들에 의해 관측된 많은 별들은 행성을 가지고 있지 않은 것으로 인식되었다. 동시에 많은 별들이 다수의 행성을 가진 것으로 알려졌다. 게다가 별에 관한 정보는 행성과는 별개로 다른 관측에 의해 얻어진다. 따라서 별과 행성의 정보가 별개의 표로 작성 되었더라도 이상할 것은 없다. 따라서 우리가 가진 행성정보을 담은 표는 이와 같다.
[00:01:19] 각 행성 후보는 기본 식별부호(primary key) 혹은 유일명(unique identifier)을 가진다. 위의 예에서 koi_name 이 이에 해당하는데 중요 케플러 대상(Kepler Object of Interest)라는 뜻이다. 그리고 별정보 표는 다음과 같다.
[01:30] 이에 역시 기본 식별부호를 가지고 있는데 kepler_id 라고 한다. 두 표를 구문을 써서 나열해 놓음으로써 느슨하게 이 두 정보의 합치기를 시도해 보고 싶을 것이다. 애석하지만 예상대로 제대로 되지 않는다. 이렇게 서로다른 두 표를 합치는 것을 교차 합침(cross joint)이라 하는데 별과 행성의 모든 조합을 나열한 것이다.
[01:47] 뭔가 잘못되가는 것이 확실해 보이는데 kepler_id가 일치하지 않은데도 연속적으로 중복된 값을 가지고 있기 때문이다. 좀더 세심한 방법으로 이런 정보들을 합치려면 행성 표에서 별 표에 있는 것과 맞추기 위한 속성(attribute)이 필요하다. 이런 속성을 외부 식별부호(foreign key)라 한다. 이 예에서는 외부 식별부호는 kepler_id 다. 이제 kepler_id 가 일치하는 행들만 가져올 수 있게 됐다. 결국 Kepler-227b가 kepler_id 10797460인 별에 속한 행성이란 것을 알게 됐다.
[02:20] 이 행성의 공전 주기는 9.5일 이다. 따라서 이 행성이 주성으로부터 받는 에너지 량은 지구의 142배에 달한다. 이와 별개로 합침의 또다른 형식이 있는데 조회시 사용 할 수 있는 내부 합침(inner join)으로 위와 같다.
[02:33] 이제 우리의 질문(지구만한 크기의 거주가능한 외계행성을 찾을 수 있을까?)에 답을 할 만큼 필요한 모든 사항을 배웠다. 거주가능한 지역에 존재하는 행성을 찾아내기 위해 행성의 에너지 수지(energy budget)를 고려해 보자. 주성으로부터 받은 에너지를 흡수량과 다시 우주로 내보내 량을 따져보자.
[02:46] 별은 매우 밝은 천체로 전 방향으로 균일하게 에너지를 방출한다. 그 에너지 중 아주 작은 량이 딸려있는 행성에 의해 차단되어 주성의 에너지 강도가 감소한다. 역제곱 법칙에 따라 광원으로부터 떨어진 거리를 A 비율로 증가 시키면 빛의 강도는 A의 제곱분의 일로 감소한다.
* 거리의 역제곱 법칙을 증가하는 면적의 관점에서 보자. 빛은 전방향으로 동일하게 복사한다. 거리가 d=1일 때 전 구면을 통과하는 빛이나 d=2일때 전구면을 통과하는 빛의 량은 같다. 하지만 구면의 표면적은 거리의 제곱이다. 따라서 단위면적당 빛의 세기는 광원에서 거리의 제곱에 반비례한다. 거리에 상관 없이 총에너지량은 보존된다. 없던 에너지가 생기거나 이유없이 사라지지 않는다. 빛이 진행하는 경로상에 차단물체로 인해 흡수되어 열에너지로 바뀔 수도 있지만 어쨌든 총 에너지는 보존되어야 한다.
* 멀리있는 천체의 빛 에너지를 측정할 때 성간 물질(ISM, Inter-Stellar Material)에 의해 차단되는 에너지량도 계산되어야 한다. 이를 성간소광(interstellar extinction)이라 한다. 성간소광을 감안하지 않은 관측 결과로 우주크기에 대한 '대논쟁'의 원인이 되기도 했다.
[03:09] 행성으로 입사하는 에너지량은 명백히 주성의 밝기에 달렸다. 그리고 주성과의 얼마나 가깝게 있는지도 중요하다. 행성에 주입되는 에너지 플럭스(단위 면적당 시간당 통과하는 에너지량)를 계산하기 위해 이 두가지 요인을 합쳐보자.
[03:20] 먼저, 행성이 받는 별빛의 강도다. 지구의 경우 평방 미터당 1361 와트다. 이는 매우 상수에 가깝다. 실제로 이를 태양상수(solar constant)라고 한다.
* 와트(Watt)는 일률(단위 시간당 일)의 단위
1W = 1J/s = 1N.m/s = 1Kg.(m/s)^2/s = 1V.1A
* 구구단만 알아도 미적분(고전역학)
00:03:34.510 --> 00:03:37.720
앞의 동영상 강좌에서 행성에 의해 차단되는 에너지 량이 단면적에 비례한다고 배웠다. 하지만 이 행성이 다시 우주로 내보내는 복사에 대해선 다루지 않았다. 흡수되는 에너지와 방출하는 에너지 모두 행성의 지름에 비례한다. 따라서 흡수량과 복사량을 서로 상쇄할 수 있다. 이는 행성의 지름이 표면온도 공식에 등장하지 않는 이유다.
* 동일한 차원의 항끼리 더하거나 빼도 물리공식에 영향을 주지 않는다.
* 흡수면적에서 복사면적을 빼면 총면적이다. 지구에서 측정하는 별의 복사 에너지량은 총면적에 의한 결과다. 그런데 거주가능한 행성을 찾기 위해 표면적 온도를 구해야 한다. 표면적 온도는 입사량과 방출 에너지량을 알아야 한다.
[03:57] 행성의 표면 온도 공식은 네배의 시그마로 나눈 입사 에너지 플럭스의 네제곱 근이다. 이때 시그마(σ)는 스테판-볼츠만 상수다. 이 공식이 어떻게 나왔는지 지금 알 필요는 없고 그냥 있는대로 사용하자. 만일 알고 싶다면 참고자료를 찾아보라.
[참고]
1. Planetary equilibrium temperature ,
https://en.wikipedia.org/wiki/Planetary_equilibrium_temperature
2. [마구잡이 수학] 1.4 요약 및 추가 문제 ,
문제 1-12, 차원으로 풀어보는 슈테판 볼츠만 공식
http://math-mass-goodkook.blogspot.com/2018/05/14.html
[04:15] 이 공식으로 계산한 불모지와 거주가능지의 온도 차이는 단 10켈빈에 불과하다. 물론 이 계산에서 자연적인 온실효과(natural greenhouse effect) 같은 요인은 무시했다. 이 효과는 대기의 조성과 밀접한 관련이 있고 행성의 표면 온도에 큰 영향을 미친다. 금성의 경우 자연온식효과로 인한 지표온도 상승은 500 켈빈이다. 짙은 대기는 온도를 순환 시키기도 하는데 해를 받는 낮의 온도와 그늘진 밤의 온도차가 작다. (대기가 없는 달의 온도차)
[04:45] 우리 지구 만한 행성의 존재를 알기 위해 주성의 크기가 어느정도 인지 알 필요가 있다. 하지만 우리가 측정하는 것은 행성에 의해 차단되는 빛의 비율일 뿐이다. 즉, 행성의 투영면적 대 주성의 단면적의 비다. 작은 행성이 작은 별을 공전할 때의 별빛 강도 변화비나 큰 행성이 큰 별을 공전하며 가렸을 때의 비는 같다.
[05:04] 별의 지름 정보를 차용하여 이 공식을 사용하여 행성의 지름을 구할 수 있다. 지구의 지름으로 측정된 행성의 지름은 태양의 지름으로 측정한 별의 직경에 횡단 깊이(Transit Depth, 행성이 별 앞을 지날 때 가려지는 별빛의 감소량)를 곱한 것과 같다.
[05:18] 횡단 깊이에 제곱근을 취한 이유는 별빛 감소량을 면적의 비례식인데 우리가 원하는 것은 직경(길이)이기 때문이다. 행성의 반경을 이런식으로 알아냄으로써 이 행성의 딱딱한 지표면을 가지고 있는지 생명채가 존재하기에 충분한지 알아낼 수 있다. 별과 행성의 자료표를 합침으로 분석할 가치있는 다양한 추가 정보를 제공 받을 수 있다. 이는 최신 외계행성 연구에 있어서 특히 유용하다. 수차례 우주개발 임무를 통해 획들한 자료를 동시에 활용할 수 있기 때문이다.
-----------------------------------------------------------------
5강: SQL의 표 합치기(거주가능 외계행성)
Lesson 5: Joining Table in SQL / 한글자막 / 영문자막
[강의대본]
[00:06] 이제 큰 궁금증에 답할 만한 기법은 거의 배운 셈이다. 그 궁금증은 주성에서 생명체가 존재할만한 위치에 지구만한 크기의 행성들이 있을까? 라는 것이었다. 여기에 한가지 놓친 부분이 있다.
[00:16] 이 궁금증을 해소하려면 행성에 대한 정보를 알 필요가 있다. 그 정보들은 외계행성 표에 저장돼 있다. 아울러 주성에 대한 정보도 알아야 한다. 이 정보들은 별(Star)로 분류된 표에 따로 저장돼 있다. 왜 이 정보들은 다른 표에 저장되어 있는지 어떻게 하면 행성 표와 합칠 수 있을까?
[00:33] 자료와 그 관리체계를 조직하는 방법에 관한 사항은 별개의 연구 과제 인데 소위 데이터베이스 정규화(database normalization)라고 한다. 데이터베이스를 설계할 때 자료가 여러곳에 중복된 경우 중복성의 최소화와 자료 무결성 강화를 목표로 둬야 한다.
[00:50] 일반적으로 서로다른 형을 갖는 자료들 사이의 관계가 일대일로 대응된 경우 단일 표에 합치는 것이 타당하다. 하지만 우리의 예에서는 그렇지 못했다. 천문가들에 의해 관측된 많은 별들은 행성을 가지고 있지 않은 것으로 인식되었다. 동시에 많은 별들이 다수의 행성을 가진 것으로 알려졌다. 게다가 별에 관한 정보는 행성과는 별개로 다른 관측에 의해 얻어진다. 따라서 별과 행성의 정보가 별개의 표로 작성 되었더라도 이상할 것은 없다. 따라서 우리가 가진 행성정보을 담은 표는 이와 같다.
[00:01:19] 각 행성 후보는 기본 식별부호(primary key) 혹은 유일명(unique identifier)을 가진다. 위의 예에서 koi_name 이 이에 해당하는데 중요 케플러 대상(Kepler Object of Interest)라는 뜻이다. 그리고 별정보 표는 다음과 같다.
[01:30] 이에 역시 기본 식별부호를 가지고 있는데 kepler_id 라고 한다. 두 표를 구문을 써서 나열해 놓음으로써 느슨하게 이 두 정보의 합치기를 시도해 보고 싶을 것이다. 애석하지만 예상대로 제대로 되지 않는다. 이렇게 서로다른 두 표를 합치는 것을 교차 합침(cross joint)이라 하는데 별과 행성의 모든 조합을 나열한 것이다.
[01:47] 뭔가 잘못되가는 것이 확실해 보이는데 kepler_id가 일치하지 않은데도 연속적으로 중복된 값을 가지고 있기 때문이다. 좀더 세심한 방법으로 이런 정보들을 합치려면 행성 표에서 별 표에 있는 것과 맞추기 위한 속성(attribute)이 필요하다. 이런 속성을 외부 식별부호(foreign key)라 한다. 이 예에서는 외부 식별부호는 kepler_id 다. 이제 kepler_id 가 일치하는 행들만 가져올 수 있게 됐다. 결국 Kepler-227b가 kepler_id 10797460인 별에 속한 행성이란 것을 알게 됐다.
[02:20] 이 행성의 공전 주기는 9.5일 이다. 따라서 이 행성이 주성으로부터 받는 에너지 량은 지구의 142배에 달한다. 이와 별개로 합침의 또다른 형식이 있는데 조회시 사용 할 수 있는 내부 합침(inner join)으로 위와 같다.
[02:33] 이제 우리의 질문(지구만한 크기의 거주가능한 외계행성을 찾을 수 있을까?)에 답을 할 만큼 필요한 모든 사항을 배웠다. 거주가능한 지역에 존재하는 행성을 찾아내기 위해 행성의 에너지 수지(energy budget)를 고려해 보자. 주성으로부터 받은 에너지를 흡수량과 다시 우주로 내보내 량을 따져보자.
[02:46] 별은 매우 밝은 천체로 전 방향으로 균일하게 에너지를 방출한다. 그 에너지 중 아주 작은 량이 딸려있는 행성에 의해 차단되어 주성의 에너지 강도가 감소한다. 역제곱 법칙에 따라 광원으로부터 떨어진 거리를 A 비율로 증가 시키면 빛의 강도는 A의 제곱분의 일로 감소한다.
* 거리의 역제곱 법칙을 증가하는 면적의 관점에서 보자. 빛은 전방향으로 동일하게 복사한다. 거리가 d=1일 때 전 구면을 통과하는 빛이나 d=2일때 전구면을 통과하는 빛의 량은 같다. 하지만 구면의 표면적은 거리의 제곱이다. 따라서 단위면적당 빛의 세기는 광원에서 거리의 제곱에 반비례한다. 거리에 상관 없이 총에너지량은 보존된다. 없던 에너지가 생기거나 이유없이 사라지지 않는다. 빛이 진행하는 경로상에 차단물체로 인해 흡수되어 열에너지로 바뀔 수도 있지만 어쨌든 총 에너지는 보존되어야 한다.
* 멀리있는 천체의 빛 에너지를 측정할 때 성간 물질(ISM, Inter-Stellar Material)에 의해 차단되는 에너지량도 계산되어야 한다. 이를 성간소광(interstellar extinction)이라 한다. 성간소광을 감안하지 않은 관측 결과로 우주크기에 대한 '대논쟁'의 원인이 되기도 했다.
[03:09] 행성으로 입사하는 에너지량은 명백히 주성의 밝기에 달렸다. 그리고 주성과의 얼마나 가깝게 있는지도 중요하다. 행성에 주입되는 에너지 플럭스(단위 면적당 시간당 통과하는 에너지량)를 계산하기 위해 이 두가지 요인을 합쳐보자.
[03:20] 먼저, 행성이 받는 별빛의 강도다. 지구의 경우 평방 미터당 1361 와트다. 이는 매우 상수에 가깝다. 실제로 이를 태양상수(solar constant)라고 한다.
* 와트(Watt)는 일률(단위 시간당 일)의 단위
1W = 1J/s = 1N.m/s = 1Kg.(m/s)^2/s = 1V.1A
* 구구단만 알아도 미적분(고전역학)
00:03:34.510 --> 00:03:37.720
앞의 동영상 강좌에서 행성에 의해 차단되는 에너지 량이 단면적에 비례한다고 배웠다. 하지만 이 행성이 다시 우주로 내보내는 복사에 대해선 다루지 않았다. 흡수되는 에너지와 방출하는 에너지 모두 행성의 지름에 비례한다. 따라서 흡수량과 복사량을 서로 상쇄할 수 있다. 이는 행성의 지름이 표면온도 공식에 등장하지 않는 이유다.
* 동일한 차원의 항끼리 더하거나 빼도 물리공식에 영향을 주지 않는다.
* 흡수면적에서 복사면적을 빼면 총면적이다. 지구에서 측정하는 별의 복사 에너지량은 총면적에 의한 결과다. 그런데 거주가능한 행성을 찾기 위해 표면적 온도를 구해야 한다. 표면적 온도는 입사량과 방출 에너지량을 알아야 한다.
[03:57] 행성의 표면 온도 공식은 네배의 시그마로 나눈 입사 에너지 플럭스의 네제곱 근이다. 이때 시그마(σ)는 스테판-볼츠만 상수다. 이 공식이 어떻게 나왔는지 지금 알 필요는 없고 그냥 있는대로 사용하자. 만일 알고 싶다면 참고자료를 찾아보라.
[참고]
1. Planetary equilibrium temperature ,
https://en.wikipedia.org/wiki/Planetary_equilibrium_temperature
2. [마구잡이 수학] 1.4 요약 및 추가 문제 ,
문제 1-12, 차원으로 풀어보는 슈테판 볼츠만 공식
http://math-mass-goodkook.blogspot.com/2018/05/14.html
[04:15] 이 공식으로 계산한 불모지와 거주가능지의 온도 차이는 단 10켈빈에 불과하다. 물론 이 계산에서 자연적인 온실효과(natural greenhouse effect) 같은 요인은 무시했다. 이 효과는 대기의 조성과 밀접한 관련이 있고 행성의 표면 온도에 큰 영향을 미친다. 금성의 경우 자연온식효과로 인한 지표온도 상승은 500 켈빈이다. 짙은 대기는 온도를 순환 시키기도 하는데 해를 받는 낮의 온도와 그늘진 밤의 온도차가 작다. (대기가 없는 달의 온도차)
[04:45] 우리 지구 만한 행성의 존재를 알기 위해 주성의 크기가 어느정도 인지 알 필요가 있다. 하지만 우리가 측정하는 것은 행성에 의해 차단되는 빛의 비율일 뿐이다. 즉, 행성의 투영면적 대 주성의 단면적의 비다. 작은 행성이 작은 별을 공전할 때의 별빛 강도 변화비나 큰 행성이 큰 별을 공전하며 가렸을 때의 비는 같다.
[05:04] 별의 지름 정보를 차용하여 이 공식을 사용하여 행성의 지름을 구할 수 있다. 지구의 지름으로 측정된 행성의 지름은 태양의 지름으로 측정한 별의 직경에 횡단 깊이(Transit Depth, 행성이 별 앞을 지날 때 가려지는 별빛의 감소량)를 곱한 것과 같다.
[05:18] 횡단 깊이에 제곱근을 취한 이유는 별빛 감소량을 면적의 비례식인데 우리가 원하는 것은 직경(길이)이기 때문이다. 행성의 반경을 이런식으로 알아냄으로써 이 행성의 딱딱한 지표면을 가지고 있는지 생명채가 존재하기에 충분한지 알아낼 수 있다. 별과 행성의 자료표를 합침으로 분석할 가치있는 다양한 추가 정보를 제공 받을 수 있다. 이는 최신 외계행성 연구에 있어서 특히 유용하다. 수차례 우주개발 임무를 통해 획들한 자료를 동시에 활용할 수 있기 때문이다.
