과제 정보
가족관계등록부를 가지고 ERD를 도출 하는 과제
설계한 ERD
설계를 하였을때 개인정보를 저장하는 Table과 가족관계의 구분을 나타낼 수 있는 Table을 한개씩 그려
두 테이블은 1:1관계에 있는 식으로 구상하였다.
즉 개인의 정보를 저장하고 가족관계구분표에서 아버지, 어머니, 배우자의 Id를 기록하여 각각을 찾을 수 있게끔 하고 자식을 가지고 있다면 자식들 정보역시 아버지,어머니의 ID를 기록하기 때문에 찾을 수 있다고 생각하여 설계하였다.
쿼리를 통한 문제 해결 및 인지
본인이 작성한 ERD를 바탕으로 아래 내용 작성
1. 본인의 가족관계증명서를 뽑는 쿼리를 작성하시오.
2. 내 딸(있다고 가정하고)의 가족관계증명서를 뽑는 쿼리를 작성하시오.
3. 아버지의 형제자매 목록을 추출하시오.
4. 엄마가 재혼한 경우 어머니 목록을 추출하시오.설계한 ERD를 바탕으로 쿼리를 작성하여 정보를 추출하는 과제가 추가적으로 제공되었다.
풀이
김본인씨를 기준으로 찾는 다고 가정
일부일처제를 가정
1번
# 1번 본인의 가족관계증명서를 뽑는 쿼리
select * from population_info
where user_id = 5
UNION
select * from population_info
where user_id = (select father_id from family_info where user_id=5) or
user_id = (select mother_id from family_info where user_id=5) or
user_id = (select partner_id from family_info where user_id=5) or
user_id in (select user_id from family_info where father_id=5) or
user_id in (select user_id from family_info where mother_id=5)
2번
# 2번 내 딸(있다고 가정하고)의 가족관계증명서를 뽑는 쿼리
select * from population_info
where user_id in
(select user_id from population_info
where user_id in (select user_id from family_info where (father_id=5 or mother_id=5) ) and gender ='F')
UNION
select * from population_info
where
user_id in (select father_id from family_info where user_id = (select user_id from population_info
where user_id in (select user_id from family_info where (father_id=5 or mother_id=5) ) and gender ='F') ) or
user_id in (select mother_id from family_info where user_id = (select user_id from population_info
where user_id in (select user_id from family_info where (father_id=5 or mother_id=5) ) and gender ='F') ) or
user_id in (select partner_id from family_info where user_id = (select user_id from population_info
where user_id in (select user_id from family_info where (father_id=5 or mother_id=5) ) and gender ='F') ) or
user_id in (select user_id from family_info where father_id = (select user_id from population_info
where user_id in (select user_id from family_info where (father_id=5 or mother_id=5) ) and gender ='F') ) or
user_id in (select user_id from family_info where mother_id = (select user_id from population_info
where user_id in (select user_id from family_info where (father_id=5 or mother_id=5) ) and gender ='F') )
3번
# 3번 아버지의 형제 자매를 추출하는 쿼리
select * from population_info
where user_id in (
select user_id from family_info
where father_id in (
select father_id from family_info
where user_id = (select father_id from family_info where user_id=5)
)
and user_id not in (select father_id from family_info where user_id=5)
)
4번
현재 구조에서 해결 불가
재혼, 이혼할 경우 family_info table을 업데이트 해야한다.
피드백
과제 리뷰 전 쿼리를 다 작성하지 못해서 쿼리 관련 리뷰는 받지 못하였고 구조적으로
재혼, 이혼할 경우 family_info table을 업데이트 해야 하기 때문에 4번은 해결하지 못한다는 것을 쿼리 과제를 받았을 때
부터 파악할 수 있었다.
구분을 어떻게 처리해야 할지 고민이 많이 되었고 이 과정에서 설계가 많이 꼬이기 시작한 것 같았다.
즉 당연한 말이지만 ERD 설계가 잘못되면 쿼리로 옮기는 것이 불가능 하다는 것을 배울 수 있었다.
직접 그렸던 ERD와 관련해서 부족한 부분이 있었는데 과제 리뷰를 해주신 수석님께서 순환관계를 넣어서 다시 작성해주셨고
타이트한 구조지만 데이터를 어떻게 넣고 뽑을지를 잘 생각하면 충분히 일리가 있는 구조라고 피드백을 해주셨다.
이와 관련해서 추가적으로 더욱 공부가 필요해 보인다.
