개정판머리말 데이터베이스설계분야에처음입문하는분들을위해가장쉽게이해할수있는교재를만들어보자는의도하에초판이나온지도여러해가지났습니다. 그동안개정의필요성이꾸준히제기되어이번에개정판을새롭게내게되었습니다. 이번개정판은초판의기본적인내용을유지하면서데이터베이스설계환경의변화에따라변경이필요한부분들을고쳐담았습니다. 주요개정내용은다음과같습니다. 1. 초판에서사용된설계도구인 Case Studio 도구의버전을업그레이드하였습니다. 현재 Case Studio 는 Toad Data Modeler 라는제품명으로개명하여사용되고있으며, 엔티티를한꺼번에 6개이상저장할수없었던제약이사라져보다편리하게실습에활용할수있게되었습니다. 몇가지부가기능도추가되어설계작업시유용하게활용할수있습니다. 2. 데이터베이스설계의예제로사용하던 ʻ비디오대여점관리업무ʼ를 ʻ도서관관리업무ʼ로전환하여설명하였습니다. 현재는비디오대여점이거의사라져가고있어서예제의내용이피부에와닿지않는다는지적이있어서바꾸게되었습니다. 비디오대여점업무와도서관관리업무는유사한점이많아초판의비디오대여점업무를이해한분들은쉽게도서관관리업무도이해할수있을것입니다. 3. 연습문제를보강하였습니다. 각단원별로연습문제가부족하다고판단되거나보다많은연습이필요하다고판단되는단원을집중적으로보강하였습니다. 데이터베이스설계는이론에대한이해만으로는설계능력을기르기어렵기때문에다양한연습문제해결을통해연습해보는것이중요합니다. 머리말 3
4. 실전예제를추가하였습니다. 각단원별로실제현실세계에서접할수있는문제들을하나씩골라서설계문제를해결해가는과정을단계적으로설명하였습니다. 독자들은실전예제의이해를통해현실감있는설계능력을기를수있을것입니다. 이번개정판이처음데이터베이스설계를접하는모든분들에게유익한책이되기를바라며그동안의관심에감사를드립니다. 2012년 12월오세종 4 머리말
머리말 오늘날웹을포함한응용프로그램의개발에있어서데이터베이스를제외하고생각하기는어려울정도로데이터베이스가일반화되었고, 데이터베이스연동프로그래밍은프로그램개발자들이알아야할기본지식중의하나가되었습니다. 그러나많은개발자들은데이터베이스를데이터를저장하기위한수단정도로이해하고있으며, 몇개의간단한테이블을만들고여기에 SQL 문을이용해데이터를저장하거나프로그램으로불러오는정도의수준에서데이터베이스를이용하고있는실정입니다. 그러나데이터베이스를효과적으로이용하기위해서는데이터베이스설계에대한지식이있어야합니다. 개발하고자하는시스템의규모가커지면커질수록데이터베이스를효율적으로설계하는일이중요해집니다. 그러나데이터베이스를설계하는일은쉬운일이아닙니다. 단순히설계에대한지식이있다고할수있는일이아니기때문입니다. 마치 Java 프로그래밍에대한문법을알고있다고해서 Java 프로그래밍을잘할수있는것은아닌것과마찬가지입니다. 그동안데이터베이스설계에관한책들이국내에많이소개되었습니다. 활용중심의책들은설계에대한이론적인설명이부족하고, 실무적관점에서쓰여진책들은유용한지식들을많이담고있지만처음설계를배우고자하는사람이이해하기에는내용이방대하고어려운경우가많았습니다. 번역서의경우번역의한계와역자가원서의내용을충분히이해하지못한가운데직역한것들이많아역시내용의이해가쉽지않았습니다. 데이터베이스설계에관심을가지고있는이들이쉽게이해할수있으면서도이론적으로, 실무적으로단단한기반을제공할수있는저서가필요한상황입니다. 본저서는이러한필요성을충족시키고자노력하였습니다. 설계에관련된이론적인부분들을최대한쉽게설명하였으며, 다양한예제를통하여독자의이해를돕고자노력하였습니다. 또한설계이론의습득에그치지않고학습과정동안다양한실습및 머리말 5
머리말 그룹프로젝트를통하여설계를직접경험할수있도록하였습니다. 따라서본저서는처음데이터베이스설계를배우고자하는이들에게좋은입문서가될것입니다. 최근각대학에서는공학교육인증제의시행에따라공학설계에관련된교과목을보강하고자노력하고있습니다. 그러나마땅히설계과목으로할수있는과목이부족하여어려움을겪고있는실정입니다. 데이터베이스의설계는문제의분석부터시작하여설계및구축에이르는전과정을포함하고있기때문에훌륭한공학설계과목이될수있습니다. 본저서는공학설계과목이필요로하는요소들을포함하고있으며강의자들에게는강의자료및프로젝트자료가제공될것입니다. 본저서를통하여데이터베이스를배우고자하는이들이조금이나마도움을얻게되기를바라며, 저서의내용에대해서또는개선의견이있다면언제든지환영합니다. (sejongoh@dku.edu) 2005년 12월오세종 6 머리말
이책을강의하는분들께 본교재는머리말에소개한바와같이공학인증의설계교과과정을위해저술되었으며, 총 16주강의를할수있도록교재를구성하였습니다. 설계교과목은이론강의보다는설계실습을중요시하기때문에이론강의와설계실습의비율을 3:7 또는 4:6 정도로하시면됩니다. 설계실습은이론강의에이어서진행하며 3인을 1조로편성하여조별로실습을하도록합니다. 설계실습을위해매시간실습과제가주어지고, 학생들은수업을마칠때완성된과제를제출하여평가를받도록합니다. 설계실습은대부분종이와연필만있으면가능하기때문에특별한시설을필요로하지않는장점이있습니다. 수업시간에진행하는설계실습과는별도로각조는조별프로젝트를한학기동안수행하여프로젝트보고서 ( 설계포트폴리오 ) 를제출하여야하며기말고사직전에발표회를갖습니다. 강의하시는분들을위하여생능출판사홈페이지를통해충분한수업자료를제공해드릴예정이며, 매년자료가새롭게추가될것입니다.( 상세한강의계획서, 설계실습자료, 프로젝트보고서양식및샘플등 ) 다음은 16주강의계획서의요약입니다. ʻ4장모델링도구ʼ의경우도구사용법을설명하는것이기때문에강의일수가모자라는경우학생들이각자해보는것으로하고생략하여도무방합니다. 이책을강의하는분들께 7
주이론강의설계실습조별프로젝트 1 과목소개및조편성 ( 강의계획서배부 ) 2 1 장관계형데이터베이스주요개념 - 3 2 장정보시스템구축절차와데이터베이스설계설계실습 #1 4 3 장데이터모델링의주요개념설계실습 #2 5 4 장모델링도구설계실습 #3 6 5 장업무분석설계실습 #4 7 6 장엔티티의정의설계실습 #5 8 중간고사 9 7 장식별자, 관계, 속성의정의설계실습 #6 프로젝트계획서제출 10 8 장정규화설계실습 #7 중간보고서제출 11 9 장도메인과용어사전의정의설계실습 #8 12 10 장모델의검토설계실습 #9 13 11 장논리적설계를물리적설계로전환하기설계실습 #10 14 12 장데이터베이스의구축설계실습 #11 15 조별프로젝트발표회최종보고서제출 16 기말고사 8 이책을강의하는분들께
차례 PART 01 데이터베이스개요 제1장관계형데이터베이스의주요개념 15 1.1 데이터베이스의역사 16 1.2 관계형데이터베이스용어 24 1.3 기본키와외래키 26 1.4 뷰 35 1.5 SQL 언어 39 연습문제 47 제2장정보시스템구축절차와데이터베이스설계 49 2.1 추상화와모델링 50 2.2 데이터베이스설계의개념 54 2.3 정보시스템구축과데이터베이스설계 57 2.4 데이터베이스설계의상세과정 66 연습문제 68 PART 02 논리적데이터베이스설계 제3장데이터모델링의주요개념 73 3.1 개요 74 3.2 엔티티 75 3.3 속성 78 3.4 관계 80 3.5 주식별자와외래식별자 88 3.6 ERD 표기법 90 연습문제 93 실전예제 96 차례 9
제4장모델링도구 99 4.1 모델링도구개요 100 4.2 모델링도구따라하기 102 4.3 관계의설정 120 4.4 ERD를레벨별로보기 123 4.5 주석문의삽입 126 연습문제 128 제5장업무분석 131 5.1 개요 132 5.2 문서및자료수집 134 5.3 담당자인터뷰 137 5.4 분석내용의정리 140 5.5 업무분석사례 : 도서관관리 144 연습문제 158 제6장엔티티의정의 161 6.1 개요 162 6.2 엔티티다시보기 163 6.3 엔티티도출과정 164 6.4 업무기술서에서엔티티도출의예 167 6.5 장부 / 전표에서엔티티도출의예 172 6.6 엔티티도출의예 : 도서관관리 175 연습문제 178 실전예제 182 제7장식별자, 관계, 속성의정의 187 7.1 개요 188 7.2 주식별자의정의 188 7.3 관계 / 외래식별자의정의 200 7.4 업무규칙의정의 207 10 차례
차례 7.5 속성의정의 211 7.6 관계, 식별자, 속성의도출사례 : 도서관관리 214 연습문제 217 실전예제 221 제8장정규화 225 8.1 정규화개요 226 8.2 정규화과정 227 8.3 정규화에서엔티티의분리 231 8.4 정규화가필요없는설계기법 234 8.5 정규화사례 : 도서관관리 239 연습문제 242 실전예제 245 제9장도메인과용어사전의정의 249 9.1 도메인의정의 250 9.2 용어사전의정의 257 9.3 모델링도구를이용한도메인의작성 260 9.4 도메인, 용어사전의사례 : 도서관관리 264 연습문제 266 제10장모델의검토 267 10.1 개요 268 10.2 엔티티의검토 268 10.3 속성의검토 276 10.4 관계의검토 281 10.5 M:N 관계의해소 282 10.6 프로세스모델과의통합검토 284 10.7 모델의검토사례 : 도서관관리 286 연습문제 287 실전예제 290 차례 11
PART 03 물리적데이터베이스설계 제11장논리적설계를물리적설계로전환하기 295 11.1 개요 296 11.2 테이블, 컬럼, 키로의전환 296 11.3 반정규화 302 11.4 뷰의설계 306 11.5 인덱스의설계 309 11.6 테이블기술서 315 11.7 물리적설계의예 : 도서관관리 316 연습문제 321 제12장데이터베이스의구축 323 12.1 개요 324 12.2 수작업에의한데이터베이스구축 324 12.3 도구에의한데이터베이스구축 336 12.4 역공학에의한데이터베이스분석 344 12.5 사용자, 역할, 권한관리 351 12.6 DB 구축의예 : 도서관관리 359 연습문제 368 APPENDIX 부록 01 설계사전 371 02 웹사이트게시판을위한 DB 설계 383 03 Toad Data Modeler 사용매뉴얼 391 04 mysql 사용매뉴얼 399 05 ODBC 설치방법 407 찾아보기 414 12 차례
PART 01 데이터베이스개요 제 1 부의목표 제1장관계형데이터베이스의주요개념제2장정보시스템구축절차와데이터베이스설계 데이터베이스의기본개념에대해이해한다. 정보시스템에서데이터베이스의역할에대해이해한다. 정보시스템구축절차와데이터베이스의설계절차와의관계성을이해한다. 데이터베이스를설계한다는것이무엇인지를이해한다. 추상화과정으로서의데이터모델링에대해이해한다. P A R T 1
CHAPTER 01 01 관계형데이터베이스의주요개념 Database CHAPTER 관계형데이터베이스의주요개념 단원목표 데이터베이스의필요성에대해이해한다. 관계형데이터베이스의기본용어를익힌다. 기본키와외래키의개념을이해한다. 기본적인 SQL 문법을숙지한다. 15
PART 1 데이터베이스개요 1.1 데이터베이스의역사 1.1.1 파일시스템의위기컴퓨터분야에서어떤개념을이해하고자할때좋은방법중의하나는그개념이나오게된배경과역사를살펴보는것이다. 1946 년최초의전자식컴퓨터인에니악 (ENIAC) 이개발된이후컴퓨터발전의역사는데이터처리의발전사와그맥을함께하였다. 1980 년대까지컴퓨터시스템을지칭하는데사용되었던 EDPS(Electronic Data Processing System) 라는용어는컴퓨터와데이터처리가얼마나밀접한관계에있었는지를보여준다. 제1세대컴퓨터시스템들은소프트웨어나저장장치등의개발이부족했기때문에주로기술분야의계산, 자료분류등에사용되었다. 운영체제가도입되고 FORTRAN, COBOL 과같은프로그래밍언어를사용하는제2세대컴퓨터가등장하면서부터컴퓨터시스템은자료를분석하고처리하는일에본격적으로사용되기시작하였다. 이시기에도입된 ʻ파일 (File)ʼ 개념은자료를저장하는기본적인방법으로사용되었으며오늘날까지도널리이용되고있다. 파일에기초하여자료나정보를처리하는시스템을파일시스템 (file system) 이라고부른다. < 그림 1.1> 은파일을이용한일반적인자료처리의모델을보여준다. 파일시스템에서는개별응용프로그램이직접파일에접근하여기록, 갱신, 삭제를할수있으며, 파일에있는데이터의올바른관리여부는전적으로응용프로그램에달려있다. < 그림 1.1> 파일시스템에기초한자료처리모델 컴퓨터시스템이일반적인비즈니스업무에효과적으로적용될수있음이알려지면서각기업들은앞다투어정보시스템을구축하기시작하였다. 그결과컴퓨터의저장장치에는방대한양의데이터들이축적되기시작하였다. 파일에기초한정보시스템에서데이터의급속한증가는하드웨어나소프트웨어의성능향상에도불구하고다음과같은문제점들을드러내었다. 16
01 관계형데이터베이스의주요개념 CHAPTER 데이터종속성 (data dependency) 데이터종속성이란데이터를사용하는프로그램의구조가데이터구조 ( 파일구조 ) 의영향을받는다는것을의미한다. < 그림 1.2> 와같이파일에서학생정보를읽어처리하는 COBOL 프로그램을생각해보자. 이경우응용프로그램은파일에저장된학생정보레코드 (record) 에대한구조를기술하는부분을포함하며한레코드가읽혀오면이구조정보에따라여러필드 (field) 들로분리하여처리하게된다. 어떤이유에의해학생의이름을저장하는필드의길이를현재 20자리에서 30자리로늘려야하는경우가발생한다면학생정보파일을읽어서처리하는모든응용프로그램을변경해야하는문제가생긴다. 만일 10개의프로그램중 9개는변경을했는데나머지 1개의프로그램을변경하지못한채로시스템을운영하게되면, 변경되지못한프로그램이학생정보파일에잘못된데이터를기록하여다른프로그램에문제를일으킬수있다. 이와비슷하게학생정보파일에서필드의순서를바꾸어저장하고자하는경우에도이를이용하는모든프로그램을변경해야한다. 이와같이데이터의구조변화에따라응용프로그램이영향을받을때 ʻ프로그램이데이터에종속되어있다ʼ라고말한다. 이와같은데이터종속성은프로그램의개발과유지보수를어렵게한다. < 그림 1.2> 학생정보를읽어처리하는 COBOL 프로그램 데이터무결성 (data integrity) 의침해데이터무결성이란저장된데이터의내용이본래의도했던데이터의형식, 범위를준수해야한다는성질이다. 예를들면 < 그림 1.2> 의학생정보파일에서나이 (AGE) 필드는숫자형식이어야하고음수가아닌양수이어야한다. 또한그범위는 20~60 사이가일반적일것이다. 이러한조건을위배하는데이터가저장될때데이터의무결성이침해되었다고말한다. 파일을이용하는과거의정보시스템에서는데이터무결성을지켜야할책임이프로 17
PART 1 데이터베이스개요 그래머에게있었다. 즉, 응용프로그램에서사용자가데이터를올바르게입력했는지검사하는기능을구현해야했다. 만일응용프로그램이올바르지않은데이터가저장되는것을허용하게된다면저장된데이터에근거해서어떤판단을내려야하는경우문제가된다. 정보화사회에서는인간의삶이많은부분컴퓨터시스템에저장된데이터에의존하고있기때문에데이터무결성의침해는매우심각한문제가된다. 데이터중복성 (data redundancy) 데이터중복성이란같은내용의데이터가여러곳에중복하여저장되는것을의미한다. 과거의정보시스템에서는개별부서나응용프로그램에서필요로하는데이터파일을각각만들어사용하는일이많았고, 그결과동일데이터가여러파일에중복저장되는일이많았다. 데이터가중복저장되면저장공간의낭비라는문제외에도다음에설명할데이터의불일치, 보안의어려움과같은문제들이발생할수있다. 데이터불일치 (data inconsistency) 데이터불일치란중복저장된데이터들이서로일치하지않는것을의미한다. 예를들어이사를하게되면변경된주소를학교나직장, 은행, 가입된웹사이트등에통보를해야한다. 그렇지않으면우편물이이전주소로배달될것이다. 이와같은현상은주소정보가여러기관에중복저장되어있다는데서기인한다. 하나의조직내에서도이와같은문제가발생할수있다. 어떤학생이교무과에휴학신청을하여휴학을했는데, 그사실을모르는재무과에서는등록금고지서를그학생에게발송할수있다. 이는교무과의학생정보와재무과의학생정보가각각관리되면서불일치할때발생할수있는문제이다. 데이터표준화 (data standard) 의어려움일정규모이상의정보시스템을개발하기위해서는많은수의개발자들이협력작업을해야한다. 이러한환경에서는작업방법의표준화가필수적이다. 예를들면개발자 A는응용프로그램에서학생이름을 ʻS-NAMEʼ 으로길이는 20자리로사용하는데, 개발자 B는학생이름을 ʻSNMEʼ 로길이는 15자리로사용한다면제3자가프로그램을이해하기도어렵고두응용프로그램간의호환성에도문제가된다. 학생이름을지칭하고표현하는표준화된규칙이있다하더라도응용프로그래머가이를지키지않을수있는여지가있기때문에과거의정보시스템개발환경에서는표준화가어려운문제였다. 18
01 관계형데이터베이스의주요개념 CHAPTER 데이터보안성 (data security) 의결여데이터가저장되어있는파일은그내용이 Text 형식이나잘알려진형식으로저장되기때문에응용프로그램없이도쉽게파일을열어내용을볼수가있고파일의공유를위해접근이쉬운위치에파일을저장했기때문에보안을유지하기가어려웠다. 현대의정보시스템에는기업의영업비밀이나고객의사생활정보와같은보안을필요로하는데이터가많이저장되기때문에보안성의결여는심각한문제가된다. < 그림 1.3> 파일시스템의위기 이상의문제들은파일을이용하는정보시스템에서는해결하기어려운문제였다. 이를해결하기위한대안으로제시된것이데이터베이스이다. 1.1.2 데이터베이스의등장파일시스템의단점을극복하면서도다수의사용자들이정보를공유할수있어야한다는시대적요구에부응하기위하여연구자들은데이터베이스개념을제안하였다. 데이터베이스의철학은간단하다. 첫째는파일형태로여기저기에흩어져있는데이터, 정보들을하나로모아관리하자는것이고, 둘째는응용프로그램들이운영체제를통해시스템자원을이용하는것처럼모아놓은데이터들을관리하고사용자 ( 응용프로그램 ) 와데이터사이에인터페이스역할을할수있는 S/W를만들자는것이다. 이때모아놓은데이터의집합을데이터베이스 (database), 데이터를관리하는 S/W를데이터베이스관리시스템 (DBMS: Database Management System) 이라부른다. 또한데이터베이스에기초해서데이터나정보를처리하는체제를데이터베이스시스템 (database system) 이라고부르는데, 이는파일시스템에대응되는개념이다. < 그림 1.4> 는데이터베이스시스템의개요를보여준다. 19
PART 1 데이터베이스개요 < 그림 1.4> 데이터베이스시스템의개요 데이터베이스라는용어는 1963년 6월미국 SDC(System Development Corporation) 가산타모니카에서개최한 ʻDevelopment and Management of a Computer-centred Data Baseʼ 심포지엄제목에서처음공식적으로사용하였다. 현대적인의미의데이터베이스개념을확립한사람은당시제너럴일렉트릭 (General Electric) 사에있던 C. Bachman 으로, 그는 1963년 IDS(Integrated-Data Store) 라는데이터베이스관리시스템을만들었다. 데이터베이스의역사를살펴보면 1960 년대와 1970 년대초까지는 IBM의 IMS, MRI System Corp. 에서설계제작한 System 2000 등으로대표되는계층형 (hierarchical) 데이터베이스시스템과 Calliname Corp. 의 IDMS, CINCOM System 의 TOTAL 등의네트워크 (network) 데이터베이스시스템이주도를하였다. 이후 1970년 E. F. Codd에의해제안된관계형 (relational) 데이터모델은튼튼한이론적기반과선언적질의를통한사용의용이성을바탕으로기존의계층형데이터베이스와네트워크데이터베이스를대체하기시작하였다. 현재는상업용데이터베이스시스템의대부분을관계형시스템이차지하고있다. 데이터베이스시스템은파일시스템에비하여다음과같은특징을갖는다. 데이터독립성 (independency) 지원데이터베이스시스템에서는사용자혹은응용프로그램이직접데이터베이스에접근할수없고반드시 DBMS 를통해서만접근이가능하다. DBMS 는데이터베이스내에있는데이터의물리적, 논리적변화가응용시스템에영향을미치지않도록함으로써데이터독립성을보장한다. 20
01 관계형데이터베이스의주요개념 CHAPTER 데이터무결성유지 DBMS 는데이터베이스내에저장될데이터에대하여데이터의타입 (type), 길이, 값의범위등에대한정보를가지고있으며, 이를위반하는데이터가들어올경우처리를거절함으로써데이터의무결성을지원한다. 데이터중복성및불일치최소화데이터베이스내의데이터는한개인의관점이나특정부서의관점에서관리되는것이아니라데이터베이스를공용하는조직전체의관점에서관리를한다. 그때문에동일데이터가여러부서에서사용하는경우이를하나로관리함으로써중복성을방지하며그결과로서중복된데이터간의불일치문제를해결한다. 데이터표준화의용이성데이터베이스시스템에는응용프로그래머와는별도의데이터베이스관리자 (DBA) 가존재하며 DBA는데이터베이스의설계과정을주도함으로써부서간이해를조정하고관리될데이터를표준화시킬수있다. 또한 DBMS 는데이터의구조에관한정보 ( 예 : 테이블이름, 컬럼이름, 컬럼의데이터타입 ) 를가지고있으며응용프로그램에서데이터에접근하기위해서는 DBMS 가가지고있는구조정보에따라야하기때문에자연스럽게표준화가이루어질수있다. 높은데이터보안성 DBMS 는사용자의권한에따라데이터베이스내에있는데이터에대한접근을제한할수있다. 또한저장된데이터베이스는일반적으로 DBMS 를통하지않고는외부에서내용을알아내기매우어렵기때문에데이터가보호될수있다. 데이터공유 (data sharing) 의용이성데이터베이스시스템의기본철학이데이터를통합관리하고이를여러부서, 사용자들이공유하도록하는것이다. DBMS 는여러사용자의요구를동시적으로처리할수있는능력을가지고있으며데이터를쉽게이용할수있는수단을제공한다. 21
PART 1 데이터베이스개요 < 그림 1.5> 데이터베이스시스템의장점 1.1.3 관계형데이터베이스모델사용자의입장에서보았을때데이터가어떤모양으로표현되고관리되는가에따라데이터베이스는계층형모델, 네트워크모델, 관계형모델로분류된다. 최근에는객체지향 (object-oriented) 모델, 객체-관계형 (object-relational) 모델이발표되었으나상용 DBMS 제품에서는관계형데이터베이스모델이압도적으로많이사용되고있다. 관계형모델의특징은 < 그림 1.6> 과같이데이터가테이블형태로표현되며, 사용자가데이터를쉽게다룰수있도록해주는질의어 (SQL) 가제공된다는것이다. 테이블형태로표현된데이터는단순해서누구나쉽게이해할수있다. SQL은자연어에가까운문법을가지고있어서배우기쉽고, 데이터를어떻게 (how) 가져올것인가대신에어떤 (what) 데이터를원하는지만기술해주면되기때문에사용자나개발자의입장에서는데이터를다루는작업이매우단순해진다. 또한 SQL 명령어나문법은표준화되어있기때문에대부분의명령어는모든관계형데이터베이스제품에서공통적으로사용될수있다. 22
01 관계형데이터베이스의주요개념 CHAPTER Oracle 제품명제조사비고 MS SQL Server Informix Adaptive Server Enterprise DB2 Progres Ingres < 표 1.1> 상용관계형데이터베이스제품들 Oracle Microsoft Informix software Sybase IBM Progres Software Corporation Ingress Corporation 임베디드 DBMS 분야에서강세 MySQL MySQL AB 공개용버전제공 Access Microsoft 개인용 EMPLOYER empno ename dept tel salary 100 김기훈 영업 1241 200 101 홍성범 기획 5621 200 102 이만수 영업 5251 250 103 강나미 생산 1231 300 영업부에속한모든사원의이름과전화번호를보이시오 SELECT ename, tel FROM employer WHERE dept = ' 영업 ' < 그림 1.6> 사원테이블과질의의예 본저서에서다루는데이터베이스설계는어떤데이터베이스모델을선택하는가에따라방법론이달라질수있다. 현재까지는관계형데이터베이스제품이주류를이루고있으므로관계형모델에기초한방법론을따르도록한다. 관계형데이터베이스에대한설계에익숙해지면객체지향데이터베이스의설계개념에대해서도쉽게이해할수있게된다. 관계형데이터베이스를설계할수있기위해서는관계형모델의개념을이해하고있어야한다. 1장의나머지부분에서는관계형데이터베이스설계를위해반드시알아야하는내용들을요약하여설명한다. 23
PART 1 데이터베이스개요 1.2 관계형데이터베이스용어 앞에서설명한바와같이관계형모델에서는데이터가테이블형태로표현된다. < 그림 1.7> 을통하여테이블과관련된용어들을설명하기로한다. < 그림 1.7> 에서사용한용어들은 E. F. Codd 가정의한것으로오늘날일반적으로사용하는용어와는차이가있다. < 그림 1.7> 학생릴레이션 릴레이션 (relation) 릴레이션은우리가지금까지테이블이라고지칭했던것으로관계형데이터베이스에서정보를구분하여저장하는기본단위가된다. 예를들면학생에관한정보를저장하기위해서는 STUDENT 릴레이션이필요하고과목에대한정보를저장하기위해서는 SUBJECT 라는릴레이션이필요하다. 릴레이션들은서로를구분할수있는이름을가지며동일한데이터베이스내에서는같은이름을가진릴레이션이존재할수없다. 일반적으로데이터베이스는많은수의릴레이션들을포함하게된다. 24
01 관계형데이터베이스의주요개념 CHAPTER 속성 (attribute) 하나의릴레이션은현실세계의어떤개체 (entity) 를표현하고저장하는데사용되는데, 표현할개체의구체적인정보항목에해당하는것이속성이다. 현실세계의개체 ( 예 : 학생, 교수, 과목, ) 들은많은속성들을가지는데, 그중에서관리해야할필요가있는속성들만을선택하여릴레이션에포함시킨다. 속성역시고유한이름을가지며동일릴레이션내에서는같은이름의속성이존재할수없다. 단, 릴레이션이다르면같은속성이름을공통으로사용할수있다. 튜플 (tuple) 릴레이션이현실세계의어떤개체를표현한다면튜플은그개체에속한구성원들개개의정보를표현한다고할수있다. 예를들면 ʻ학생ʼ은개체를나타내는이름이고 ʻ김철수ʼ, ʻ박선하ʼ, ʻ안미희ʼ, 등은 ʻ학생ʼ 개체의구성원이라고할수있다. 개체의각구성원에대해관리해야할정보의항목은동일하지만그내용은서로다르므로이를튜플이라는형태로릴레이션안에표현하는것이다. 한릴레이션에포함된튜플의개수는시간에따라변할수있다. 한릴레이션은적게는수십개, 많게는수십만개의튜플을포함할수있다. < 그림 1.7> 의 STUDENT 릴레이션에서첫번째튜플의의미는학번이 ʻ2003001ʼ인학생의이름은 ʻ김철수ʼ이고, 전공은 ʻ국문학ʼ이며, 전화번호는 ʻ02-121-2121ʼ 이고, 나이는 ʻ20ʼ세임을나타낸다. 김철수라는동일인의정보를담고있는튜플은 STUDENT 릴레이션내에서오직하나만존재해야한다 ( 동명이인의경우는서로다른사람으로간주 ). 도메인 (domain) 도메인이란릴레이션에포함된각각의속성들이가질수있는값들의집합이라고할수있다. 도메인이라는개념이필요한이유는릴레이션에저장되는데이터값들이본래의도했던값들만저장되고관리되도록하는데있다. 예를들면 ʻ성별ʼ이라는속성이있다면이속성이가질수있는값은 { 남, 여 } 이다. 데이터베이스설계자는성별의도메인으로 ʻSEXʼ를정의하고그값으로 { 남, 여 } 를지정한뒤, ʻ성별ʼ이라는속성은 ʻSEXʼ 도메인에있는값만을가질수있다고지정해놓으면사용자들이실수로남, 여이외의값을입력하는것을 DBMS 가막을수있다. 도메인의이름은속성이름과같을수도있고다를수도있다. 또한하나의도메인을여러속성에서공유할수있다. 도메인의유용성에도불구하고본래의관계형모델에서의도했던도메인의개념을 100% 구현하고있는 DBMS 제품은없다. 그이유는 ʻ성별ʼ, ʻ색상ʼ, ʻ전공ʼ 등과같이도메인에포함해야할값을미리알수있고선언해놓을수있는속성도있지만 ʻ이 25
PART 1 데이터베이스개요 름ʼ, ʻ주소ʼ와같이도메인에포함될값의경우의수가너무많아서미리선언할수없는속성도있기때문이다. 현실적으로는특정속성에대해그속성과관련없는값이들어오는것을완벽하게막을수있는방법은없다. DBMS 제품들이기본적으로제공하는방법은각속성에대해데이터타입과길이를미리지정하여그에맞는값들만들어오도록하는것과, 데이터값의범위를지정하여범위에맞는값만저장되도록하는것이다. 릴레이션, 튜플, 속성등의용어는과거에파일시스템을사용하던개발자들이사용하던용어와도다르고, 일반사용자들에게익숙하지않기때문에상용 DBMS 제품이일반화되면서보다쉬운용어가등장하였다. < 표 1.2> 는이를정리한것인데, 오늘날에는여러용어가혼용되고있으므로이름은다르지만유사개념인용어들을알고있는것이필요하다. 본저서에서는앞으로테이블, 컬럼, 튜플이라는용어를사용하기로한다. < 표 1.2> 용어대비표 E. F. Codd의용어 File 시스템의용어 자주사용되는용어 릴레이션 (relation) 파일 (file) 테이블 (table) 속성 (attribute) 필드 (field) 열 (column), 컬럼 튜플 (tuple) 레코드 (record) 행 (row) 1.3 기본키와외래키 데이터베이스설계를위해반드시이해해야할개념중의하나가기본키 (primary key) 와외래키 (foreign key) 이다. 우리는이미일상생활에서 ʻ키 (key)ʼ라는용어를열쇠의의미로사용하고있는데, 데이터베이스에서의키개념과는다르기때문에키에대해이해하는데어려움이있다. 키의개념에대해설명하기전에 < 그림 1.8> 을통하여키의필요성에대해이해하도록한다. 26
01 관계형데이터베이스의주요개념 CHAPTER < 그림 1.8> 중복된튜플의삽입 < 그림 1.8> 은 STUDENT 테이블에두개의새로운튜플을삽입하려고하는상황을표현한것이다. 1번튜플은각컬럼의값이 STUDENT 테이블의두번째튜플과완전히일치한다. 만일 1번튜플이정상적으로삽입된다면 STUDENT 테이블은중복된두개의튜플을가지게되는데, 이렇게되면여러문제가발생할수있다. 예를들면전체학생수가몇명인지를알아내기위해튜플수를카운트하는연산을수행시키면중복된튜플이각각카운트되므로실제학생수보다더많은학생수를반환하게되므로문제가된다. 2번튜플은 ʻdeptʼ 컬럼이 STUDENT 테이블의두번째튜플과다르기때문에중복된튜플의삽입은아니다. 그러나 2번튜플의내용을보면학번이 2003002 인박선하학생에대한정보이고 STUDENT 테이블의두번째튜플역시동일학생에대한정보이므로의미적으로중복이라고할수가있다. 2번튜플이정상적으로삽입된다면 ʻ학번이 2003002 인학생의전공은무엇인가ʼ라는질의에대하여 DBMS 는 ʻ국문학ʼ 과 ʻ영문학ʼ이라는답을할것이고, 사용자는어느것이맞는지혼란에빠질것이다. 이와같은문제를방지하기위하여관계형데이터베이스에서는중복된튜플의삽입을막아야할필요가있다. 그런데 DBMS 를구현해야하는입장에서보면중복된튜플이삽입되는지를확인하는것은쉬운일이아니다. 1번튜플이삽입되는경우는이미존재하는모든튜플에대해모든컬럼이같은지를일일이검사해보면중복여부를알수있지만, 튜플수가수십만개에달하는경우는비교시간이매우오래걸리기때문에현실적인방법이아니다. 2번튜플의경우는의미적중복이지만데이터의의미를이해할수없는 DBMS 로서는 2번튜플이의미적으로중복인지를알아낼방법이없다. 27
PART 1 데이터베이스개요 그렇다면어떻게중복여부를효과적으로확인할수있을까? 이에대한답이바로키 (key), 보다정확히말하면후보키 (candidate key) 이다. 후보키를간단히정의하면다음과같다. 후보키 (candidate key) 란테이블에서각튜플을구별하는데기준이되는하나혹은그이상의컬럼들의집합이다 ( 후보키는테이블에있는각튜플을고유하게식별할수있어야한다 ). < 그림 1.8> 의 STUDENT 테이블을살펴보자. STUDENT 테이블은학생한명한명의정보를관리하기위한것이다. 현실세계에서학생한명한명을무엇으로구분할수있는지를생각해보자. 이름은동명이인이존재할수있으므로구분의기준이될수없다. 전공역시한전공이같은학생이많으므로구분의기준이될수없다. 같은이유로전화번호나나이역시구분의기준이될수없다. 그래서현실세계에서는학생한명한명을구분하기위해학번 ( 학생번호 ) 이라는것을부여하여사용한다. 따라서 STUDENT 테이블에서 sid 컬럼이각튜플을구분하는기준으로사용될수있으며, 이때 sid를 STUDENT 테이블에대한후보키라고부른다. DBMS 는새로운튜플이릴레이션에삽입될때새로운튜플의후보키값이기존의튜플들의후보키값과동일한지여부를비교하여중복여부를확인하게된다. 후보키값의비교는모든컬럼들을비교하여보는것에비해시간이절약되고, 인덱스기법을이용하면매우빠른시간에중복여부를알아낼수있다. < 그림 1.8> 의경우 1, 2번튜플모두후보키값이이미존재하고있으므로중복으로간주되어 DBMS 는삽입연산을거절하게된다. 키는일반적으로테이블이생성될때지정한다. 후보키 (candidate key) 는기본키 (primary key) 와대체키 (alternate key) 로구분된다. < 그림 1.9> 를통해각각의개념을이해하도록한다. < 그림 1.9> 후보키, 기본키, 대체키 28
01 관계형데이터베이스의주요개념 CHAPTER < 그림 1.9> 의새로운테이블에는주민등록번호 (pid) 컬럼이추가되었다. 이경우학번 (sid) 도각튜플을구분하는기준으로사용될수있지만, 주민등록번호도우리나라국민이라면개인별로유일하기때문에튜플을구분하는기준으로사용될수있다. 이와같이하나의테이블은한개이상의후보키를가질수있다. < 그림 1.9> 의경우는 sid와 pid 컬럼이후보키가될수있다. 설계자는후보키중어느것을튜플을구분하는기준으로사용할지를선택해야하는데이때선택된후보키를기본키, 선택되지않은후보키를대체키라고한다. 후보키중어느것을기본키로정할것인가에는정해진규칙은없으나대체로데이터의길이가짧고현실세계에서빈번히이용하는컬럼을기본키로정한다. < 그림 1.9> 의경우는일반적으로학교에서는주민등록번호보다는학번이더많이사용되므로학번을기본키로정하는것이유리하다. 어떤릴레이션에후보키가하나만존재한다면당연히그후보키가기본키가된다. 기본키를지정하는문제는데이터베이스설계에있어서매우중요한부분이므로나중에자세히설명하도록한다. 후보키, 기본키, 대체키와더불어서키와관련된용어가하나더있는데그것은복합키 (composite key) 이다. 앞에서살펴본릴레이션들은하나의컬럼이후보키의역할을하지만어떤릴레이션에서는두개이상의컬럼이합쳐져야만후보키의역할을할수있다. 이렇게두개이상의컬럼이모여키의역할을하는경우를복합키라고부른다. < 그림 1.10> 은복합키의예를보여준다. < 그림 1.10> 복합키의예 < 그림 1.10> 에서 STUDENT_CLUB 테이블은학생들이가입한동아리정보를관리하는테이블이다. 튜플들에서보는바와같이한학생은여러동아리에가입하는것이가능하다. STUDENT_CLUB 테이블에서후보키를찾아보자. 먼저학번 (sid) 이키가될수있는지검토해본다. 만일학번이키가될수있다면각튜플의학번컬럼은중 29
PART 1 데이터베이스개요 복된값이들어올수없으므로유일해야한다. 그런데한학생은여러취미가있을수있으므로학번 2003001 은첫번째와두번째튜플에저장되어있다. 즉, 2003001 은튜플들사이에서유일하지않으며, 결과적으로후보키의자격이없다. 이번에는동아리이름 (club) 이키가될수있는지살펴보자. 한동아리는여러학생이가입할수있으므로같은동아리이름이여러튜플에나타날수있다. 결론적으로동아리이름도튜플들사이에서유일하지않으므로후보키가될자격이없다. 동아리회장 (club_student) 도마찬가지로유일하지않으므로후보키가될수없다. < 그림 1.10> 에는혼자서후보키의역할을할수있는컬럼이없다. 그렇다면학번 (sid) 과동아리이름 (club) 을합친경우는어떠한가. STUDENT_CLUB 테이블이학생들이가입한동아리정보를관리하는것이기때문에 A라는학생이 B라는동아리에가입했다는정보는오직한번만기록되어야한다. 그렇지않다면중복이다. < 그림 1.10> 에서 2003001 영어회화반정보는모든튜플을통틀어오직한번만나타나야한다. 만일두번나타난다면 2003001 학생이영어회화반에가입했다는정보가두번기록된것이기때문에중복이다. 따라서두컬럼의집합 {sid, club} 은후보키가될수있으며두개이상의컬럼이합쳐져서후보키의역할을하므로복합키이다. 복합키의극단적인경우는테이블의모든컬럼을합쳐야후보키의역할을하는경우이다. < 그림 1.11> 모든컬럼들로구성된기본키 < 그림 1.11> 의테이블은매일어떤모델의제품을누구에게얼마나판매했는지의정보를포함하고있다. 이테이블에서는어떤식으로컬럼을조합해도키의역할을할수가없다. 예를들어판매일자 (sdate) 와모델 (model) 을복합키라고가정하고테이블생성시기본키로지정했다고하자. 2005년 5월 6일에 PHONE 200대를하니통신에추가로판매하게되어이정보를입력하려고하면 2005.5.6 PHONE 정보가마 30
01 관계형데이터베이스의주요개념 CHAPTER 지막튜플에이미존재하기때문에기본키규칙에따라중복입력을할수없다. 그러나테이블의마지막튜플은한미산업에판매한정보이고새로입력하려는정보는하니통신에판매한정보이기때문에의미상중복이아니다. 그러므로두컬럼의집합 {sdate, model} 은키가될수없는것이다. 이와같이여러컬럼의조합에대해검토해보면결국모든컬럼을조합해야만키의역할을할수있게된다. 실제 DBMS 제품에있어서기본키와대체키 앞에서기본키는어떤튜플이새로추가되려고할때중복된튜플이이미존재하는지를확인하는데사용이된다고설명하였다. 중복을확인하는방법은추가되려고하는튜플의기본키컬럼의값과추가대상테이블의기본키컬럼의값을비교하는것이다. 만일추가대상테이블의모든튜플들에대해서순차적으로비교를해나간다면역시많은시간을소모하게될것이다. 따라서대부분의 DBMS 제품들에서테이블의기본키컬럼들에대해서는자동으로인덱스 (index) 를생성하여중복성여부를빠르게검사할수있도록지원한다 ( 인덱스에대해서는물리적데이터베이스설계에서다시다루도록한다 ). 그에비해대체키로지정된컬럼에대해서는중복성여부는체크하되자동으로인덱스가만들어지지는않는다. 기본키컬럼에다른컬럼이추가된다면 만일기본키의역할을잘수행하고있는컬럼 ( 들 ) 에다른컬럼을추가하면어떻게될까? 여전히기본키의역할을수행할수있지만다른문제가발생하게된다. < 그림 1.9> 의 STUDENT 테이블을가지고설명해보자. < 그림 1.9> 에서 sid는혼자서키의역할을잘수행할수있다. 그런데 {sid, sname} 을함께기본키로지정하면어떻게될까? 의미상 sid는학번을의미하고학번으로도모든학생을구별할수있으므로여기에학생이름 (sname) 을추가하여도역시학생개개인을구별하는데문제가없다. 그런데이렇게지정하는경우는 < 그림 1.12> 와같은튜플이추가될수있다. 31
PART 1 데이터베이스개요 < 그림 1.12> 불필요한컬럼이포함된기본키 그림에서추가하려는튜플은 STUDENT 테이블의첫번째튜플과 sid 값이같지만이름이다르므로 {sid, sname} 을합쳐서기본키로하게되면두튜플은서로다른튜플로간주되므로새로이테이블에추가될것이다. 그러나현실에서는같은학번을갖는학생두명이존재하지는않을것이므로데이터의무결성이침해된것이다. 여기에후보키를정할때고려해야할규칙이있다. 즉, 최소한의컬럼혹은컬럼들의집합으로후보키를구성해야한다는것이다. 후보키는하나의테이블내에서튜플이중복되지않음을보증하는수단이다. 이와는달리테이블간의데이터의일치와무결성을보증해주는수단이외래키 (foreign key) 이다. < 그림 1.13> 은사원과부서의정보를담고있는테이블을보여준다. 사원 (emp) 테이블에서사원이속한부서의이름은알수가없다. 부서의이름을알려면부서번호 (deptid) 를가지고부서 (dept) 테이블에가서부서번호에해당하는부서명 (dname) 을찾아보아야한다. 결국사원테이블은부서에대한여러정보를포함하는대신정보를찾을수있는키를가지고있는셈이다. 이러한관계에있을때 ʻ사원테이블이부서테이블을참조한다ʼ라고말한다. 이를일반화시켜서설명하면테이블 A 가테이블 B의기본키에해당하는컬럼을가지고있을때테이블 A가테이블 B를참조한다고말한다. 그리고테이블 A에있는테이블 B의기본키컬럼을외래키라고부른다. 외래키는참조하는테이블 ( 사원테이블 ) 과참조되는테이블 ( 부서테이블 ) 의연결고리역할을한다. < 그림 1.14> 는이러한관계를보여준다. 32
01 관계형데이터베이스의주요개념 CHAPTER < 그림 1.13> 사원과부서정보테이블 < 그림 1.14> 기본키와외래키 이와같이어떤테이블에외래키가설정되어있으면어떤좋은점이있을까? 인사담당자가사원테이블에아래의튜플을삽입하는경우를생각해보자. 만일이튜플이삽입된다면데이터의무결성에문제가생긴다. 왜냐하면 900번부서는존재하지않기때문이다. 그러나사원테이블에외래키가설정되어있다면 DBMS 는튜플이삽입될때부서테이블을찾아가서 900번부서가있는지를확인하게되고없는것을확인하면튜플의삽입을거절함으로써데이터의무결성을유지할수있다. 33
PART 1 데이터베이스개요 이번에는 < 그림 1.13> 의부서테이블에서세번째튜플 ( 부서번호 ʻ300ʼ) 이삭제되는경우를생각해보자. 만일튜플이그냥삭제된다면이를참조하는사원테이블의다섯번째튜플 ( 사원번호 ʻ1004ʼ) 에문제가생긴다. 사원번호 ʻ1004ʼ인사원의부서번호가 ʻ300ʼ인데, 이부서의정보가삭제되었기때문에 ʻ300ʼ은더이상존재하지않는오류데이터가되는것이다. 이와같이참조되는쪽테이블의튜플이삭제되거나기본키값이변경되므로해서참조하는쪽테이블에무결성이깨어지게되면이를막기위해 DBMS 는다음과같이몇가지조치를취할수있다. 제한 (restricted) 만일삭제하려는튜플의부서번호값을사원테이블에서가지고있는튜플이있으면삭제연산을거절한다. 연쇄 (cascade) 삭제연산을수행한뒤삭제된부서번호값을갖는사원테이블의튜플도함께삭제한다. 만일또다른테이블이사원테이블을참조하고있다면삭제된사원의사원번호값을가지고있는튜플을삭제한다. 이와같이참조관계를따라가면서연쇄적으로관련된튜플들을삭제한다. 널값으로대체 (nullify) 삭제연산을수행한뒤삭제된부서번호값을갖는사원테이블의튜플에서부서번호를 null값으로바꾸어준다. 이와같이외래키를통해두테이블간의데이터무결성을유지하는것을 ʻ참조무결성제약조건ʼ이라고한다. 외래키는사람이지정하는가, DBMS 가알아서처리해주는가? 사람이라면 < 그림 1.13> 의두테이블을보고참조관계에있음을쉽게알수있다. 그러나 DBMS는사람과같이지능이있는것이아니므로테이블간, 데이터간연관성을알수가없다. 따라서참조무결성제약조건을 DBMS에게알려주려면데이터베이스설계자가명시적으로외래키를지정해주어야한다. 34
01 관계형데이터베이스의주요개념 CHAPTER 자기참조 (self reference) < 그림 1.13> 의사원테이블에서홍성길사원의직장상사 (manager) 이름을알려면직장상사의사원번호 ʻ1002ʼ를가지고다시사원테이블의튜플중사원번호가 ʻ1002ʼ인것을찾아야한다. 그결과 ʻ곽희준ʼ이 ʻ홍성길ʼ의상사인것을알수있다. < 그림 1.15> 가이러한상황을보여준다. 이와같이한테이블이자신을참조하는경우를 ʻ자기참조ʼ라고한다. < 그림 1.15> 자기참조의예 1.4 뷰 관계형데이터베이스를이해하는데빼놓을수없는것이바로뷰 (view) 이다. 뷰는단어의뜻그대로하나의테이블, 혹은여러테이블에대하여특정사용자나조직의관점에서데이터를바라볼수있도록해주는수단으로서가상테이블이라고도부른다. 어떤회사의사원정보를생각해보자. 회사내에서업무를위해사원정보를필요로하는부서가많이있을것이다. 인사팀에서는급여지급을위해 { 사번, 이름, 입사일자, 급여액 } 이필요하고기획실에서는인력배치를위해 { 사번, 이름, 근무부서, 담당업무 } 를필요로한다. 사내복지팀에서는사원의생일에선물을보내기위해 { 사번, 이름, 생년월일, 주소 } 를필요로한다. 같은사원에대한정보이지만이렇게부서별로필요로하는세부항목은다를수있다. 과거의파일시스템환경에서는이런경우세개의부서에서필요한사원파일을각각생성하여사용하였다. 그결과데이터의중복성, 불일치와같은문제가발생하였다. 관계형데이터베이스에서는데이터를개인이나개별부서단위의관점에서보는것이아니라조직전체의차원에서보기때문에세개의사원정보를관리하지않고하나의사원정보테이블에모아서관리한다. < 그림 1.16> 은통합된사원정보테이블을보여준다. 35
PART 1 데이터베이스개요 < 그림 1.16> 전체조직관점에서의사원테이블 이와같은사원테이블이인사팀, 기획실, 사내복지팀에주어진다면각부서에서는자신들에게불필요한정보가포함되어주어지기때문에불편할수도있고, 또한다른부서에보여서는안되는급여정보가함께제공되는문제가생길수있다. 이를위해데이터베이스관리자는 EMP 테이블을제공하는대신에 < 그림 1.17> 과같이각부서의필요에맞는뷰를생성하여제공할수있다. < 그림 1.17> 사원테이블에대한세가지뷰 36
01 관계형데이터베이스의주요개념 CHAPTER 사용자의관점에서보면뷰는일반테이블 (base table) 과거의구분이되지않는다. 일반테이블과마찬가지로뷰에대해서도질의가가능하기때문이다. 일반테이블과뷰의중요한차이점은일반테이블이실제로물리적인데이터를갖고있는반면에뷰는물리적인데이터를갖고있지않고, 뷰가정의된일반테이블로부터데이터를가져다보여준다는것이다. 따라서뷰는일반테이블이있어야정의가가능하다. 뷰의정의는다음과같은 SQL 명령을사용한다. CREATE VIEW view_emp1 AS SELECT empid, ename, hire_date, salary FROM emp; CREATE VIEW high_salary AS SELECT empid, ename, dept, salary FROM emp WHERE salary >= 350; 만일사용자가뷰 high_salary 에대하여다음과같은질의를한다면 SELECT empid, ename, salary FROM high_salary WHERE dept = 영업부 ; DBMS 는다음과같이일반테이블에대한질의로바꾸어실행한다. SELECT empid, ename, salary FROM emp WHERE salary >= 350 AND dept = 영업부 ; 일반적으로뷰는다음과같은목적으로사용한다. < 그림 1.17> 의경우와같이하나의테이블에대하여여러부서에서서로다른관점으로보기를원할때 테이블에급여와같이일반사용자에게는감추어야할컬럼이있을때그것을제외하고뷰를만들어제공함으로써보안을유지할필요가있을때 자주사용하는복잡한질의문을미리뷰로정의하여두고간편하게쓰고자할때 37
PART 1 데이터베이스개요 뷰에대해서도튜플의삽입과삭제가가능한가? 뷰에대해서는일반테이블에적용되는모든 SELECT문이적용된다. INSERT, UPDATE, DELETE문의실행여부는경우에따라다르게처리된다. 즉, 어떤경우는튜플의삽입, 삭제가가능한경우도있고그렇지않은경우도있다. 상식적으로생각했을때튜플의삽입이나삭제가테이블에문제를일으키지않고, 무결성규칙을만족한다면실행될것이고문제가된다면 DBMS는실행을시키지않을것이다. < 그림 1.17> 의뷰 view_emp2 에튜플을삽입하는상황을생각해보자. view_emp2에대한 INSET 연산은일반테이블 emp에대하여실행될것이고 < 그림 1.18> 과같이 view_emp2에포함되지않은나머지컬럼들에대해서는 null값이적용된다. < 그림 1.18> 뷰에대한튜플의삽입 < 그림 1.18> 에서튜플의삽입가능여부는 null값이저장될컬럼들 (hire_date, birthday, salary) 이 null값을허용하는가에달려있다. 만일이들중어느하나라도 null값을허용하지않는컬럼으로정의되어있다면튜플은삽입되지못할것이다. 세컬럼이모두 null 값을허용한다면 view_emp2를통한튜플의삽입은허용될수있다. 튜플의삭제에대해서도비슷한판단기준이적용된다. 일반적으로여러테이블을조인 (join) 하여뷰를만들거나원래테이블에있던값을 SUM, AVG 등의함수를사용하여가공한뒤뷰를만든경우는뷰에대하여삽입과삭제가불가능하다. 38
01 관계형데이터베이스의주요개념 CHAPTER 1.5 SQL 언어 관계형데이터베이스의장점중하나는사용자들이쉽게사용할수있는 SQL(Structured Query Language) 언어를제공한다는것이다. 사용자는간단한 SQL문을사용하여 DBMS 에게작업을요청할수있다. 또한기본적인 SQL문들은표준화되어있기때문에거의대부분의 DBMS 제품에동일하게적용이된다. SQL은 1974년 IBM연구소에서 System R 프로젝트를통해개발되었고 1986년미국표준기구 ANSI 에서 SQL 표준을제정하였다. 현재 SQL-89, SQL2(SQL-92), SQL3(SQL-99) 등여러버전이존재한다. SQL 언어는비절차적언어 (non-procedural language) 이다. 사용자는자신이원하는것만을명시하며, 원하는것을 DBMS 가어떻게처리할지는명시할필요가없다. 또한 SQL 언어는자연어와비슷한문법체계를가지고있기때문에처음보는사람도 SQL문의의미를이해하는것이어렵지않다. 이러한요인으로해서사용자들은 SQL 이다른언어에비해배우기쉬운것으로느낀다. 사용자 / 개발자는 < 그림 1.19> 와같이두가지방식으로 SQL을이용할수있다. < 그림 1.19> SQL 을이용하는두가지방법 대화식 SQL이란 DBMS 회사에서제공하는유틸리티프로그램 ( 예 : ORACLE 의 SQL* Plus) 을이용하여사용자가직접 SQL문을입력하고실행결과를확인하는방식을말한다. 그에비해내장 SQL은 SQL문이 C, Java와같은삭제프로그램안에포함되어져서사용되는방식이다. < 그림 1.20> 은대화식 SQL의예를, < 그림 1.21> 은내장 SQL의예를보여준다. 39
PART 1 데이터베이스개요 < 그림 1.20> 대화식 SQL 을지원하는유틸리티의예 <HTML> <HEAD> <TITLE> Dept </TITLE> </HEAD> <BODY> <center><h2> 부서정보조회 ( 전체 )</h2> <% String deptid, dname, dloc ; Class.forName("org.gjt.mm.mysql.Driver"); con = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD); String query = "SELECT * FROM dept"; PreparedStatement pstmt = con.preparestatement(query); ResultSet result = pstmt.executequery(); %> <tr> while (result.next()){ deptid = result.getstring(1); dname = result.getstring(2); dloc = result.getstring("loc"); 40
01 관계형데이터베이스의주요개념 CHAPTER <td><%=deptid%></td><td><%=dname%></td><td> <%=dloc%></td> </tr> <% pstmt.close(); %> </table> </BODY> </HTML> < 그림 1.21> JSP 에포함된내장 SQL 의예 데이터베이스를설계하기위해서는 SQL 언어에익숙할필요가있다. 데이터베이스설계의목표중하나는개발자나사용자들의 SQL 질의를효과적으로처리해줄수있는구조로데이터베이스를구성하는것이다. 설계자는사용자들이어떤 SQL을사용하게될지를사전에파악하고그에맞추어데이터베이스를설계해야한다. SQL 언어에는다음과같은명령어가있다. SELECT INSERT UPDATE DELETE CREATE DROP SQL 언어의문법에대해서는자세히설명한책들이많으므로여기서는각명령어에대하여여러형태의 SQL문을살펴보는것으로설명을대신한다. 각 SQL문은 < 그림 1.13> 의사원 (emp) 과부서 (dept) 테이블상에서실행됨을가정한다. DBMS는 SQL문을실행할때대소문자를구분하지않는다. 테이블이름, 컬럼의이름에대해서도마찬가지이다. 다음의예에서는편의상 SQL 문법에있는예약어 (reserved word) 에대해서는대문자로, 테이블이나컬럼이름은소문자로기술하기로한다. 또한대화식 SQL에서많은경우 SQL문이끝났음을알려주기위해 ʻ ; ʼ를사용하므로예제에서도 SQL문끝에 ʻ ; ʼ를붙였다. SQL문은한줄에이어서기술해도되고여러줄에나누어기술해도된다. 41
PART 1 데이터베이스개요 SELECT SELECT 는테이블에저장된정보를조회하는데사용한다. 데이터베이스의목적이정보를한곳에모아관리하면서다수가공유하는것임을생각할때 SQL 명령어중에서가장빈번히사용되는명령어가 SELECT 임을쉽게짐작할수있다. 따라서 SELECT 는다양한형태의문법을갖는다. 사원에대한모든정보를보이시오. SELECT * FROM emp ; 곽희준사원의담당업무는무엇인가? SELECT job FROM emp WHERE ename = ' 곽희준 ' ; 급여가 300 을넘고담당업무가영업관리인사원의모든정보를보이시오. SELECT * FROM emp WHERE salary > 300 AND job = ' 영업관리 ' ; 모든사원의이름과급여를보이되사원이름을가나다순으로보이시오. SELECT ename, salary FROM emp ORDER BY ename ; 부서번호와그부서에속한사원들의급여액합계를보이시오. SELECT deptid, SUM(salary) FROM emp GROUP BY deptid ; 42
01 관계형데이터베이스의주요개념 CHAPTER 가장많은급여를받는사원의급여액과가장적은급여를받는사원의급여액을보이시오. SELECT MAX(salary), MIN(salary) FROM emp ; 가장많은급여를받는사원의이름을보이시오. SELECT ename FROM emp WHERE salary = ( SELECT MAX(salary) FROM emp ) ; 사원들의현재급여와급여를 10% 인상했을때의예상급여를보이시오. SELECT ename, salary, salary*1.1 FROM emp ; 모든사원들의이름과부서이름을보이시오. SELECT emp.ename, dept.name FROM emp, dept WHERE emp.deptid = dept.deptid ; 또는 SELECT e.ename, d.name FROM emp e, dept d WHERE e.deptid = d.deptid ; 곽희준사원이속한부서의예산은얼마인가? SELECT d.dname, d.budget FROM emp e, dept d WHERE e.deptid = d.deptid AND e.ename = ' 곽희준 '; 43
PART 1 데이터베이스개요 사원의이름과그사원의상사 (manager) 이름을보이시오. SELECT e.ename, m.ename FROM emp e, emp m WHERE e.manager = m.empid; INSERT INSERT 는테이블에튜플을삽입할때사용한다. 기본적으로 INSERT 문은한번에하나의튜플을삽입할수있다. INSERT INTO emp (empid, ename, deptid, hire_date, job, salary) VALUES (106, 강윤호, 200, 2001-01-10, 연말정산, 400); ( 사원정보에튜플을삽입. 위와같이모든컬럼의값에대해테이블에있는컬럼순서대로저장하는경우는다음과같이간단히기술할수있다.) INSERT INTO emp VALUES (106, 강윤호, 200, 2001-01-10, 연말정산, 400); INSERT INTO emp (empid, ename, salary) VALUES (107, 남진선, 500); ( 위와같이일부컬럼을생략하고튜플을삽입하는경우, 생략된컬럼들의값은 null 로저장된다.) UPDATE UPDATE 는테이블에저장되어있는튜플의값을변경할때사용한다. 홍성길사원의부서번호를 400 으로, 급여를 500 으로변경하시오. UPDATE emp SET deptid=400, salary=500 WHERE ename=' 홍성길 ; 44
01 관계형데이터베이스의주요개념 CHAPTER 영업부에속한사원의급여를 20% 인상하시오. UPDATE emp SET salary = salary*1.2 WHERE deptid = (SELECT deptid FROM dept WHERE dname = ' 영업부 ') ; DELETE DELETE 는테이블에있는튜플을삭제할때사용한다. 홍성길사원의정보를사원정보에서삭제하시오. DELETE FROM emp WHERE ename=' 홍성길 ; 모든사원의정보를사원정보에서삭제하시오. DELETE FROM emp ; CREATE CREATE 는테이블, 뷰, 사용자등데이터베이스내의객체들을생성하는데사용된다. 앞에서살펴본 SELECT, INSERT, UPDATE, DELETE가테이블내에있는튜플들에대한명령어이고일반사용자들이이용할수있는명령어이나 CREATE 는데이터베이스의구조와관련이있기때문에데이터베이스관리자나설계자들이사용하게된다. 또한 CREATE 문은표준화된문법을가지고있지만 DBMS 제품마다상이한부분이있으므로사용할제품에서어떤문법을제공하는지살펴보아야한다. 45
PART 1 데이터베이스개요 다음은 dept, emp 테이블을생성하는문장이다. CREATE TABLE emp ( deptid int(10) NOT NULL, dname char(20), budget char(5), PRIMARY KEY(deptid) ); CREATE TABLE emp ( empid int(10) NOT NULL, ename char(20), deptid int(5), hire_date date, job char(20), salary int(10) NOT NULL, PRIMARY KEY(empid), FOREIGN KEY (deptid) REFERENCES dept(deptid) ); 다음은 dept, emp 테이블을연결하여뷰를만드는문장이다. CREATE VIEW new_emp AS SELECT e.empid, e.ename, d.dname, e.salary FROM emp e, dept d WHERE e.deptid = d.deptid ; DROP DROP 은 CREATE 의반대역할을하는명령어로데이터베이스내의객체를제거하는역할을한다. 다음은 dept 테이블과뷰 new_emp 를제거하는문장이다. DROP TABLE dept ; DROP VIEW new_emp ; 46
01 관계형데이터베이스의주요개념 CHAPTER Database 연습문제 E X E R C I S E 01 동일데이터가중복하여존재할때발생할수있는문제가무엇인지설명하시오. 02 데이터베이스 (database) 와 DBMS 의차이점을설명하시오. 03 릴레이션, 컬럼, 튜플에대해간단히설명하시오. 04 다음테이블에서기본키와외래키는무엇인지찾아보시오. 05 뷰 (view) 의필요성에대해설명하시오. 06 mysql DBMS 를이용하여 < 그림 1.13> 의사원과부서정보테이블을생성하시오. (44페이지의 CREATE문참조 ) 07 사원과부서정보테이블에 < 그림 1.13> 과동일한튜플을입력하시오. 앞에서생성한사원과부서정보테이블에대해다음명령에해당하는 SQL을작성하여실행하시오. 08 담당업무 (job) 가 ʻ 영업관리 ʼ 인사원의모든정보를보이시오. 09 급여 (salary) 가 400 이상인사원의이름과그사원이속한부서이름을보이시오. 47
PART 1 데이터베이스개요 10 ʻ 영업부 ʼ 와 ʻ 구매부 ʼ 사원들의급여 (salary) 합계를보이시오. 11 가장급여를많이받는사원과가장적게받는사원의급여액차이는얼마인지보이시오. 12 ʻ 영업 ʼ 사원중급여가 380 이상인사원의이름, 담당업무 (job), 급여액을보이시오. 13 ʻ 성재규 ʼ 사원의소속부서이름을보이시오. 14 ʻ구매부ʼ에속한사원을제외한나머지사원들에대해 ʻ사원번호ʼ, ʻ이름ʼ, ʻ부서명ʼ, ʻ담당업무ʼ, ʻ급여ʼ를조회할수있는뷰 (view) 를생성하시오. 그리고그뷰를조회한결과를보이시오. 48
저자약력 오세종 89 : 서강대학교컴퓨터학과졸업 ( 공학사 ) 91 : 서강대학교대학원컴퓨터학과졸업 ( 공학석사 ) 01 : 서강대학교대학원컴퓨터학과졸업 ( 공학박사 ) 91 97 : 대우정보시스템 ( 주 ) 근무 01 03 : George Mason University ( 美 ) Lab. for Information Security Technology (LIST) Post Doc. Researcher 03 현재 : 단국대학교공학대학컴퓨터과학전공, 대학원나노바이오의과학과부교수 05 현재 : 정보보호학회논문지편집위원 저자와의협의에의해인지를생략합니다. DB 설계입문자를위한 데이터베이스설계및구축 개정판 오세종지음 초판발행 : 2006. 1. 20 개정 2 판 : 2012. 2. 15 발 행 인 : 김승기 발 행 처 : 생능출판사 신고 번호 : 제406-2005-000002호 신고 일자 : 2005. 1. 21 I S B N : 978-89-7050-719-4 - 경기도파주시문발동 507-12 파주출판도시대표전화 : 031-955-0761 FAX : 031-955-0768 생능 URL : http://www.booksr.co.kr 파본및잘못된책은바꾸어드립니다. 값 : 20,000 원