728x90
1. 개체-관계 모델의 실습
① E-R모델의 작성개요(1)
| ∘E-R 모델은 업무분석 작업에서 얻어진 내용을 토대로 만듬 ∘개체형태(Entity type)와 개체 내에 속성형태(Attribute type)를 구성하고 개체간의 관계(Relationship)를 정의하는 개념적 데이터베이스 모델임 ∘학사시스템의 학생과 과목 E-R모델 (등록관계) -업무조사 및 ERD작성 : 개강 전에 각 학생들은 과목을 등록하고, 학교에서는 그 각 학생별 과목에 대한 중간, 기말, 과제물, 출석 성적을 처리함  [그림 1] 학사시스템 E-R 다이어그램 ∘논리적 데이터모델로 변환  [그림 2] 학사시스템 논리적 데이터 모델 |
② E-R모델의 작성개요(2)
| ∘인사시스템의 부서과 사원 E-R모델 (소속관계) -업무조사 및 ERD작성 : 각 부서에는 사원들이 소속되어 있고, 부서에는 부서코드, 부서명, 부서정원을, 또 사원은 사번, 이름, 입사일, 주민등록번호, 연락처 등을 처리하고자 함  [그림 3] 인사시스템 E-R 다이어그램 ∘논리적 데이터모델로 변환 -간단하게 다음과 같이 논리적 데이터모델로 변환할 수 있음  [그림 4] 인사시스템 논리적 데이터 모델(1) -일반적으로 관계가 1:n일 경우에 ‘소속’ 릴레이션 스킴을 별도로 구성하지 않고 다음과 같이 n쪽 ‘사원’ 릴레이션 스킴에 포함하는 것이 데이터베이스 조작하는데 효율적임  [그림 5] 인사시스템 논리적 데이터 모델(2) |
※ E-R 다이어그램의 다양한 표기법
∘카디널리티(cardinality) 비율 1:N으로 표기 혹은 아래 그림같이 표기 [그림 6] 부서와 사원의 소속관계 표기법(1) ∘필수(mandatory,전체참여)/선택(optional,부분참여)의 설정  [그림 7] 부서와 사원의 소속관계 표기법(2) |
※ E-R Diagram의 작성 요령
| ∘ER Diagram의 작성 순서 - 업무분석에서 얻어진 개체를 선택함 - 핵심이 되는 개체를 중앙에 배치함 - 각 개체를 구성되는 속성을 도출함 - 개체를 구별할 식별자(key)를 선택함 - 개체간에 참여관계를 파악함 - 그 관계의 카디널리티 비율을 명시함 |
③ 영화정보 검색시스템의 E-R모델(1)
| ∎ 업무분석(요구사항분석) ∘영화가 어느 극장에서 상영되며, 어떤 배우가 출연하고, 또 어떤 감독이 연출하는지의 정보를 알고자 함 ∘영화에 대한 제목, 장르, 관람기준 등의 정보를 관리하기를 원함 ∘극장의 위치와 교통안내 등의 내용 ∘배우 및 감독의 인적사항 ∘영화의 상영시간 및 배우들의 출연료 등 ∎ 개체(entity) 도출 ∘명사 위주의 내용을 개체로 도출함 ∘큰 범주의 명사는 영화, 극장, 배우, 감독에 해당됨 ∘가장 중심이 되는 개체를 중앙에 위치함  [그림 8] 영화정보 검색시스템 ERD - 개체 도출 ∎ 속성(attribute) 도출 및 식별자 선택 ∘영화: 영화번호, 영화제목, 장르, 관람기준, 제작비 등 ∘배우: 배우번호, 배우이름, 성별, 인기도, 메일주소 등 ∘극장: 극장코드, 극장명, 전화번호, 극장위치, 대중교통 등 ∘감독: 감독번호, 감독이름, 연락처 등  [그림 9] 영화정보 검색시스템 ERD - 영화 개체의 속성 도출 |
④ 영화정보 검색시스템의 E-R모델(2)
| ∎ 개체-관계(Entity Relationship)설정 ∘동사위주의 내용을 관계로 설정 ∘그 영화는 모든 극장에서 상영함 ∘그 영화에 인기배우가 출연함 ∘그 영화에는 그 감독이 연출함  [그림 10] 영화정보 검색시스템 ERD - 각 개체 간 관계 설정 ∎ 카디널리티 비율 파악 ∘영화는 1개 이상의 극장에서 상영하고, 1개 극장은 1개 이상의 영화를 상영함 ∘각 영화에는 1명 이상의 배우가 출연하고, 배우는 1개 이상의 영화에 출연함 ∘각 영화는 1명의 감독만 있고, 감독은 하나 이상의 영화를 제작함  [그림 11] 영화정보 검색시스템 ERD - 카디널리티 비율 적용 ∎ 완성된 ER 다이어그램  [그림 12] 완성된 영화정보 검색시스템 ERD |
⑤ 영화정보 검색시스템의 논리적 데이터 모델로의 변환
∎ 영화정보 검색시스템의 논리적 데이터 모델 [그림 13] 영화정보 검색시스템 논리적 데이터 모델 |
2. SQL에 대한 예제 중심의 실습
① 오라클 DBMS의 접속 및 기본사용법
| ∘사용자 이름/암호를 입력함 - 사용자 이름[U] : SCOTT - 암호[P] : TIGER ∘SQL> 프롬프트에서 sample테이블을 확인해 봄 (대소문자 관계없으며, SQL문 마침은 ‘;’) - SQL> select * from tab; ∘테이블의 구조가 어떤지 파악함 - SQL> desc customer - SQL PLUS명령은 세미콜론 ‘;’ 을 사용치 않아도 됨 - customer 테이블 구조 파악이 가능함. ∘SQL에서 종료 문자 ‘;’을 입력치 않으면, 다음 줄(2)이 나타남. ∘입력 내용이 긴 것은 미리 메모장(note pad) 혹은 편집-편집기를 이용하면 사용이 편리함 ∘사용자 ID를 변경하고자 할 경우에는 connect 명령을 사용함 - SQL> connect - 사용자명 입력: scott - 암호 입력: ***** 연결되었습니다. SQL> ∘connect 명령라인에 사용자명과 암호를 바로 입력해도 됨 - SQL> connect scott/tiger ∘connect 클라이언트에서 접속할 경우에는 데이터베이스 식별자를 주면 됨 - SQL> connect scott/tiger @DBMSSID - DBMSSID는 오라클 설치시 고유식별자이며 예를 들어서 작성한 것임 ∘접속에 관한 문법은 다음과 같음 - 사용법: CONN[ECT] [로그온] [AS {SYSDBA|SYSOPER}] <logon> ::= <username>[/<password>][@<connect_string>] | / |
※ iSQL*PLUS 사용하여 접속하기
| ∘오라클에서 아파치(Apache)웹 서버를 구동시킨 다음 ∘http://호스트 이름.도메인 이름(혹은 오라클 DBMS 서버 IP)/isqlplus |
② DDL(Data Definition Language) 실습
| ∎ 생성(create) - 영화 테이블 생성 create table 영화 (영화번호 numeric(5) not null, 영화제목 varchar2(40), 장르 varchar2(20), 관람기준 numeric(3), 상영시간 numeric(5), 감독이름 char(20), primary key (영화번호)); ∘ 개체명 및 속성명을 한글로 표기가 가능하지만, 실무구축 시에는 영문표기가 바람직함 ∘ desc 영화 <--- 입력하여 그 구조가 맞게 생성되었는지를 확인 ∎ 변경(alter) - 영화 테이블 구조 변경 ∘‘동원인원’이란 속성을 추가해 봄 alter table 영화 add 동원인원 number(7); ∘관람기준의 값이 0~20세의 값만 유지하도록 제약조건 추가함 alter table 영화 add check (관람기준>=0 and 관람기준<21); ∘감독이름을 가변길이 20자에서 고정길이 24자리로 변경함 : varchar2(20) char(24) alter table 영화 modify 감독이름 char(24); ∘desc 영화 <--- 입력하여 그 구조가 맞게 변경되었는지를 확인 ∎제거(drop) - 영화 테이블 구조 제거 ∘drop table 영화; ∘SQL> desc 영화 오류: ORA-04043: 개체 영화가 존재하지 않습니다 |
※ 영화, 배우, 출연의 create table 예
|
create table movie
(mid char(5) not null, mtitle varchar2(40), mjr varchar2(20), mage numeric(3), mtime numeric(5), mpnm char(20), check (mage>=0 and mage<21), primary key (mid)); create table actor
(aid char(5) not null, aname varchar2(15), asex char(5), apop char(1), asite varchar2(80), primary key (aid)); create table mov_act (ma_mid char(5) not null, ma_aid char(5) not null, ma_pay number(15), ma_role varchar(20), primary key (ma_mid,ma_aid), foreign key (ma_mid) references movie(mid), foreign key (ma_aid) references actor(aid)); |
③ DML(Data Manipulation Language) 실습(1)
| ∎ 예제 테이블 준비 ∘[표 1] 영화(Movie) table  * 각 테이블 내용은 실습을 위한 테스트 수집자료 임, 사실과 차이가 있음 ∘[표 2] 배우(Actor) table  ∘[표 3] 출연(mov_act) table  |
④ DML(Data Manipulation Language) 실습(2)
| ∎삽입(insert) 연산 ∘영화테이블의 맨 첫 행을 삽입해 보면, SQL> insert into movie(mid, mtitle, mjr, mage, mtime, mpnm) values('MV01','쉬리','액션',15,120,'김상진'); 혹은 SQL> insert into movie values('MV01','쉬리','액션',15,120,'김상진'); ∘입력되었는지 내용을 검색해 볼 경우는 SQL> select * from movie; ∘만약 위의 똑같은 삽입문을 한번 더 시도하면 ORA-00001: 무결성 제약 조건(DEMO.SYS_C001269)에 위배됩니다. ∎갱신(update) 연산 ∘입력된 자료에서 감독을 김상진강제규로 변경 SQL> update movie set mpnm = '강제규' where mid = 'MV01'; ∘수정되었는지 내용을 검색 확인 SQL> select * from movie; ∎삭제(delete) 연산 ∘입력된 자료 mid가 MV01인 것을 삭제하려면, SQL> delete movie where mid = 'MV01'; ∘삭제되었는지 내용을 검색 확인 SQL> select * from movie; ∘만약 delete movie; 하면 내용이 전부 삭제됨 ∎영화(movie) 테이블 전체 삽입(insert) insert into movie values('MV01','쉬리','액션',15,120,'강제규'); insert into movie values('MV02','여고괴담','공포',15,103,'박기형'); insert into movie values('MV03','주유소습격사건','코미디',18,117,'김상진'); insert into movie values('MV04','영웅','무협',12,97,'장이모우'); insert into movie values('MV05','하늘정원','드라마',0,95,'이동현'); insert into movie values('MV06','8월의 크리스마스','드라마',15,97,'허진호'); insert into movie values('MV07','대한민국 헌법 제1조','코미디',18,118,'송경식'); insert into movie values('MV08','동갑내기 과외하기','로맨스',12,110,'김경영'); |
⑤ DML(Data Manipulation Language) 실습(3)
| ∎ 테이블 삽입(insert) 시 참고사항 ∘열 이름 명세 (속성 명)를 나타낸 경우 insert into movie (mid, mtitle, mpnm) values('MV01', '쉬리', '강제규'); ∘이 경우에 mjr, mage, mtime의 속성값은 NULL값이 됨 ∘열 이름 명세 (속성 명)를 생략한 경우 insert into movie values('MV02','여고괴담','공포',15,103,'박기형'); ∘영화 테이블 생성 시 정의 구문인 check (mage>=0 and mage<21) 확인 insert into movie (mid, mtitle, mjr, mage, mtime, mpnm) values('MV09','신규영화','로맨스',25,120,'강신규'); - 테이블 정의 시 mage의 0~20의 값을 제약조건으로 인한 오류 발생 ∎ 배우(actor) 테이블 전체 삽입(insert) insert into actor (Aid, Aname, Asex, Apop, Asite) values('AT01','예지원','여','A','my.dreamwiz.com/fanpage'); insert into actor values('AT02','송강호','남','B','공동경비구역JSA'); insert into actor values('AT03','김하늘','여','A','KHN.com'); insert into actor values('AT04','안재욱','남','A','www.jaewookan.com'); insert into actor values('AT05','이성재','남','B','Sungjae.com'); insert into actor values('AT06','김규리','여','B','www.ysstar.com/kimkyulee/'); insert into actor values('AT07','심은하','여','A','my.dreamwiz.com/minpop07/'); insert into actor values('AT08','이연걸','남','A','황비홍'); insert into actor values('AT09','이미연','여','B','물고기자리'); insert into actor values('AT10','최민식','남','B','파이란'); insert into actor values('AT11','한석규','남','A','http://hansukgyu.net/'); ∎출연(mov_act) 테이블 전체 삽입(insert) insert into mov_act (Ma_mid,Ma_aid,Ma_pay,Ma_role) values('MV01','AT11',50,'유중원'); insert into mov_act values('MV01','AT10',50,'박무영'); insert into mov_act values('MV02','AT09',30,'은영'); insert into mov_act values('MV03','AT05',25,'노마크'); insert into mov_act values('MV04','AT08',45,'무명'); insert into mov_act values('MV06','AT07',35,'다림'); insert into mov_act values('MV07','AT01',25,'고은비'); insert into mov_act values('MV08','AT03',30,'최수완'); insert into mov_act values('MV06','AT11',25,'정원'); insert into mov_act values('MV01','AT02',45,'이장길'); insert into mov_act values('MV05','AT04',35,'최오성'); insert into mov_act values('MV02','AT06',25,'지오'); |
⑥ DML(Data Manipulation Language) 실습(4)
| ∘영화 이름과 감독 이름을 조회하라 SQL> select mtitle, mpnm from movie; MTITLE MPNM ------------------------ --------- 쉬리 강제규 여고괴담 박기형 주유소습격사건 김상진 영웅 장이모우 8월의 크리스마스 허진호 대한민국 헌법 제1조 송경식 동갑내기 과외하기 김경영 하늘정원 이동현 ∘영화의 장르를 조회하라 (단 중복레코드를 배제할 것) SQL> select distinct mjr from movie; MJR -------- 공포 드라마 로맨스 무협 액션 코미디 ∘인기도 A급의 배우이름을 가나다순으로 검색하라 (aname을 배우이름이라고 표시할 것) SQL> select aname as "배우이름", apop from actor where apop = 'A' order by aname asc; 배우이름 APOP ------------ ---- 김하늘 A 심은하 A 안재욱 A 예지원 A 이연걸 A 한석규 A ∘영화 쉬리에 출연한 인기배우를 검색하라 (테이블을 조인에 관한 실습) SQL> select mtitle, aname from movie, actor, mov_act where mtitle='쉬리' and mid=ma_mid and aid=ma_aid; MTITLE ANAME ---------------- 쉬리 한석규 쉬리 최민식 쉬리 송강호 |
⑦ DML(Data Manipulation Language) 실습(5)
| ∘부속질의문을 사용하여 김하늘이 출연한 영화제목을 검색하라 SQL> select mtitle from movie where mid in (select ma_mid from mov_act where ma_aid in (select aid from actor where aname='김하늘')); MTITLE ----------------- 동갑내기 과외하기 ∘모든 영화의 영화편수 및 평균상영시간은 얼마인지 표시하라 SQL> select count(*) "영화편수", avg(mtime) "평균상영시간" from movie; 영화편수 평균상영시간 -------- ---------- 8 107.125 ∘영화제목이 "대한"으로 시작되는 영화의 이름, 배우 및 그 배역을 검색하라 SQL> select m.mtitle,a.aname,ma_role from movie m, actor a, mov_act where m.mtitle like '대한%' and m.mid=ma_mid and a.aid=ma_aid; MTITLE ANAME MA_ROLE ---------------------------------------------- 대한민국 헌법 제1조 예지원 고은비 ∎뷰(view)의 생성 및 조작 ∘영화장르가 드라마 혹은 코미디의 제목과 감독을 뷰로 생성함 SQL> create view view_movie as select mtitle,mpnm from movie where mjr='드라마' or mjr='코미디'; 뷰가 생성되었습니다. ∘뷰의 검색은 테이블의 검색과 같이 사용할 수 있음 SQL> select * from view_movie; MTITLE MPNM ----------------------------------- 주유소습격사건 김상진 8월의 크리스마스 허진호 대한민국 헌법 제1조 송경식 하늘정원 이동현 ∘뷰를 삭제하기 SQL> drop view view_movie; |
3. ERwin 도구의 조작
① ERwin 도구 조작(1)
| ∎ERwin 실행하기 ∘[시작]→[프로그램]→[Computer Associates]→[All Fusion]→[ERwin Data Modeller]→ [ERwin] 선택 후 ERwin 초기 대화상자에서 항목을 선택(파워포인트 초기화면과 비슷함)  [그림 14] ERwin 초기 대화상자 ∎'Create Model' 대화상자 표시 ∘모델의 유형 선택('Logical/Physical' 옵션 버튼 선택) ∘'Target Database' 항목에서 사용할 데이터베이스 선택.  [그림 15] 모델 유형 및 목표 DBMS 선택 화면 ∎ERwin 메인 화면 표시 ∘왼쪽: 모델 탐색기(Model Explorer) 창 / 오른쪽: 다이어그램 윈도우 (Diagram Window)  [그림 16] ERwin 조작을 위한 main 화면 형태 |
② ERwin 도구 조작(2)
| ∎ERWin 표기 방식 선택 ∘IE(Information Engineering) 방식 - 정보 공학 표기 방식으로 모델링할 때 가장 많이 사용하는 유형 임 ∘Ideflx(Integration DEFinition for Information Modeling) 방식 - 미국방성에서 프로젝트 표준안으로 개발한 표기 방식임 - ERwin을 초기 설치하면 Ideflx 방식이 선택되어짐 - IE 표기방식으로 변경하여 사용하는 것을 일반적으로 권장함 ∘표기방식의 변경 요령 : 상단 메뉴바 있는 Model에서 조작함 [Model]→[Model Properties...]메뉴 선택→ 모델속성(Model Properties) 대화상자 표시됨  [그림 17] ERwin 표기 방식 변경을 위한 대화상자 ∎모델링 방법 선택 ∘논리적 데이터 모델링과 물리적 데이터 모델링 지원. ∘ERWin 도구 모음의 오른쪽의 콤보상자에서 선택  [그림 18] ERwin 모델링 방법 선택 |
③ ERwin 도구 조작(3)
| ∎개체(Entity) 생성하기 ∘논리적 모델에서 개체는 데이터베이스에서의 테이블 의미 개체 생성 방법 - ERwin Toolbox에서 버튼을 클릭하거나 모델 탐색기(Model Explorer) ‘Entities’ 폴더에서 마우스 오른쪽 버튼을 클릭하고 New 메뉴 선택 ∎개체의 형태 및 세부 내역  [그림 19] 개체의 형태 ∎관계 설정하기 ∘관계란 두 개체 사이의 업무적인 연관성을 말함 ∘관계의 유형 - 식별 관계(Identifying Relationship) : 부모 테이블의 기본키나 복합키가 자식 테이블의 기본키나 복합키의 구성원으로 전이되는 관계 - 비식별 관계(Non-Identifying Relationship) : 자식 테이블의 일반 속성(Attribute) 그룹의 구성원으로 전이되는 관계 ∘ERwin Toolbox 도구를 사용하여 관계 설정  [그림 20] 관계 도구 형태 및 관계 설정 요령 |
④ ERwin 도구 조작(4)
| ∎관계의 속성 설정하기 ∘개체 사이의 관계선을 선택한 후, 마우스 오른쪽 버튼을 눌러 팝업 메뉴에서 [Relationship Properties] 메뉴를 선택하여 ‘Relationships’ 대화상자 표시됨  [그림 21] 관계 설정 대화상자 ∘카디날리티(Cardinality)는 두 개체 사이에 레코드의 연결 정보를 나타냄 ∘Zero, One or More 항목 : 일 대 다 혹은 대상이 없는 카디널리티도 있음(선택적) ∘One or More 항목 : 일 대 다 반드시 카디널리티가 있어야 함(필수적) ∘Zero or One 항목 : 일 대 일 혹은 대상이 없는 카디널리티도 있음(선택적) ∘Exactly 항목 : 관계되는 정확한 카디널리티 숫자를 표시함 - 다이그램에 나타내는 방법은 ERwin 다이어그램 빈 공간에서 오른쪽 버튼을 누른 뒤 팝업 메뉴에서 [Relationship Display] → [Cardinality] 메뉴 선택(체크 표시됨) ∎다대다 관계 ∘다대다 관계는 논리적으로 존재할 수 있지만, 물리적으로는 존재할 수 없음 ∘ERwin Toolbox의 관계선을 이용하여 다대다 관계 표현 가능함 ∘다대다 관계에 있는 개체들은 부모와 자식의 관계가 아니므로 다대다 관계선을 선택한 후 순서에 상관없이 두 엔티티를 차례로 선택하면 관계가 설정됨. ∎다대다 관계 변환 ∘다대다 관계선에서 오른쪽 버튼을 누른 뒤 팝업 메뉴에서 [Create Association Entity] 메뉴를 선택하거나 다대다 관계선을 선택한 후 'ERWin Transform Toolbar'에서 'Many to Many Transform'버튼 클릭하여 순서에 따라 조작하면 됨 |
⑤ ERwin 도구 조작(5)
∘영화정보 검색시스템을 ERwin도구로 작성하면 아래와 같음 [그림 22] ERwin을 이용한 영화정보 검색시스템 다이어그램 |
4. 정리하기
■ 요약정리
| ∘ER Diagram의 작성 순서 - 업무분석에서 얻어진 개체를 선택함 - 핵심이 되는 개체를 중앙에 배치함 - 각 개체를 구성되는 속성을 도출함 - 개체를 구별할 식별자(key)를 선택함 - 개체간에 참여관계를 파악함 - 그 관계의 카디널리티 비율을 명시함 ∘ERD는 시각적으로 쉽게 파악할 수 있는 것이며, 데이터베이스를 구축하기 위해서 논리적 데이터모델로 변환함 ∘일반적으로 관계가 1:n일 경우에 관계 릴레이션 스킴을 별도로 구성하지 않고, n쪽 개체 릴레이션 스킴에 포함하는 것이 데이터베이스 조작하는데 효율적임 ∘SQL에서 테이블의 생성(create), 변경(alter), 제거(drop) 및 데이터의 삽입(insert), 갱신(update), 삭제(delete)와 뷰(view)의 실습 조작은 표준문법에 따르나 DBMS에 따라 사용법이 조금씩 다를 수가 있음 ∘ERwin 도구는 개념적 모델과 논리적 모델을 용이하게 해 주는 도구로서, 각 DBMS의 SQL언어를 생성해 주는 역할도 함 |
■ 참고자료
| 자료명 | 자료설명 |
| • Elmasri. Navathe, Fundamentals of Database System 3rd Edition, Addison-wesley, 2000 • 황규영 외, 데이터베이스 시스템(4판), ITC, 2004 • 정선호, 데이터베이스 개론과 실습, 한빛미디어, 2005 • 김연홍 외, 데이터베이스 모델링, FreeLec, 2005 • 승현우, ACCESS 2000과 VISUAL BASIC의 만남, 이한출판사, 2001 |
• 강의 자료의 영화정보 검색시스템은 승현우의 “ACCESS 2000과 VISUAL BASIC의 만남” 교재의 데이터베이스 응용 프로그램 설계 및 구현의 내용을 참조하였고, ERwin 툴의 조작법은 정선호의 “데이터베이스 개론과 실습”을 참고했으며, 제시된 다이어그램들은 ERwin 4.0 버전의 툴을 사용하여 작성했음 |
