MySQL 과 R 을이용한 데이터베이스입문 1 Version 1.0 김진석 2 2009 년 5 월 18 일 1 본강의노트는 2009 년 1 학기동국대학교정보통계학과응용데이터베이스강의를위하여작 성하였으며, 부분적으로데이터베이스관련서적및자료를인용하였음을밝힘니다. 당분간저 자의허락없이본강의노트의무단전제, 사용및배포를금합니다. 2 Assistant Professor, Department of Statistics and Information Science, Dongguk University, Korea; E-mail: jinseog.kim@gmail.com
차례 제 1 장 데이터베이스개요 4 제 1 절 데이터베이스.............................. 4 제 2 절 데이터베이스관리시스템....................... 4 제 3 절 관계형데이터모델........................... 5 3.1 관계형데이터모델에서용어...................... 5 3.2 테이블특성.............................. 5 3.3 키 (key)................................ 6 3.4 기본키와널 (NULL)......................... 7 3.5 관계형데이터베이스......................... 7 제 4 절 SQL 개요................................ 7 제 2 장 MySQL 9 제 1 절 MYSQL 윈도우즈클라이언트설치.................. 9 1.1 Windows Essentials (x86) 다운로드................. 9 1.2 Client 설치.............................. 9 1.3 서버세팅................................ 9 1.4 외부에서접속시 ip주소및권한추가............... 10 1.5 Client 윈도우커맨드창에서접속.................. 11 제 2 절 MySQL 시작하기............................ 11 2.1 password 변경............................. 12 2.2 주석문 (comment)........................... 12 1
제 3 장 SQL 의활용 14 제 1 절 DATABASE와관련명령들...................... 14 1.1 데이터베이스리스트출력...................... 14 1.2 데이터베이스생성및삭제...................... 14 제 2 절 TABLE관관련된명령들....................... 15 2.1 TABLE 리스트출력......................... 15 2.2 TABLE내의 column 속성출력.................... 15 2.3 TABLE 생성............................. 15 2.4 테이블의삭제 (delete/drop)..................... 18 2.5 테이블의속성변경 (alter)....................... 18 2.6 테이블에자료입력.......................... 19 2.7 오류데이터의수정.......................... 21 제 3 절 테이블로부터의검색.......................... 22 3.1 모든데이터검색........................... 22 3.2 조건에맞는특정행검색 (WHERE문이용 )............ 23 3.3 특정열의선택............................ 25 3.4 특정행과열의조합......................... 26 3.5 행정열 (ORDER BY)........................ 27 제 4 절 패턴일치기능 (LIKE/REGEXP) 을이용한검색........... 29 4.1 LIKE를이용한패턴일치검색................... 29 4.2 정규표현에의한패턴일치 (REGEXP)............... 31 제 5 절 집단함수를이용한요약정보의검색.................. 33 5.1 행수세기 : COUNT......................... 33 5.2 유용한함수들............................. 36 제 6 절 여러개테이블에서의데이터검색.................. 37 6.1 조인 (JOIN).............................. 37 6.2 풀조인 (full join or cross join).................... 39 6.3 외부조인 (full join).......................... 40 6.4 서브쿼리 (sub query, sub select).................. 41 6.5 UNION................................ 41 2
제 4 장 Database 설계 45 제 1 절 데이터베이스설계시고려해야할사항들............... 45 1.1 무결성제약.............................. 46 제 2 절 Entity-Relationship (ER) 모델.................... 47 2.1 ER 모델의 Entity........................... 47 2.2 ER 모델의속성 (Attribute Set)................... 48 2.3 ER 모델의관계집합 (Relationship Set).............. 50 제 3 절 Entity-Relationship (ER) 모델에서관계형스키마로의변환.... 51 제 5 장 데이터베이스설계예제 54 제 1 절 대학교데이터베이스.......................... 54 제 2 절 항공예약관리데이터베이스...................... 54 제 3 절 비디오샵관리데이터베이스...................... 55 제 4 절 도서관데이터베이스.......................... 55 제 5 절 병원데이터베이스........................... 56 제 6 장 릴레이션정규화 57 제 1 절 제 1 정규형............................... 58 제 2 절 제 2 정규형............................... 58 제 3 절 제 3 정규형............................... 58 제 7 장 R 에서 MySQL 을접근방법 61 제 1 절 RODBC의이용............................. 61 제 2 절 RMySQL의이용............................ 63 제 8 장 SAS 에서 MySQL 을접근방법 66 3
제 1 장 데이터베이스개요 제 1 절 데이터베이스 데이터베이스는특정단체의여러응용시스템들이공유하여이용할수있도록통합하여저장한운영데이터들의집합을뜻한다. 따라서데이터베이스는다음과같은특징을지니는데이터의집합이라고생각할수있다. 최소한의중복 (minimal redundancy) 만을허용하는통합데이터 (integrated data) 보조기억공간 ( 디스크등 ) 에저장하여, 필요시에검색하는저장데이터 (stored data) 특정단체의작업수행에필요한운영데이터 (operational data) 여러사용자가응용프로그램, 응용시스템을통해공동으로사용하는공용데이터 (shared data) 제 2 절 데이터베이스관리시스템 데이터베이스시스템또는 DBMS (Database Management System) 는특정조직 ( 또는단체 ) 에서필요로하는자료들을전산화된레코드형식으로유지, 관리하여주는소프트웨어시스템을일컫는다. 다음은 DBMS의활용예이다. 기업 : 제품판매에관한데이터유지 4
학교 : 학생에관련된 ( 성적, 증명, 수업료등 ) 정보관리나도서관의목록, 대출 관리 항공사 : 비행정보, 예약상황관리등 제 3 절 관계형데이터모델 Data model: 물리적혹은추상적으로존재하는현실세계를단순하고정형화된형태로표현하는방식혹은규범 관계형데이터모델 (relational data model): 테이블형식을이용하여데이터를정의하고설명하는 data model 3.1 관계형데이터모델에서용어 그림 1.1: 관계형데이터모델에서의용어 3.2 테이블특성 튜플유일성 (Uniqueness) 규칙 5
테이블내에는 2개이상의동일튜플이존재할수없음 튜플들은순서를갖지않음 튜플내의속성들은순서를갖지않음 속성은반드시원자값 (atomic value) 를가져야함 3.3 키 (key) 데이터베이스에서키 (key) 란테이블에서특정튜플을식별가능하게하는속성혹은속성의집합을의미하며다음과같은종류가있다. 슈퍼키 (super key): 아무런제약없이튜플을구분할수있는속성의집합이다. 예를들면주민등록번호, ( 학번, 주민등록번호 ) 가슈퍼키가될수있다. 후보키 (candidate key) 는키중에서최소한의속성만으로구성된키로주민등록번호가그예이다. 관계형데이터모델에서여러개의후보키중하나만을선택하여식별자로정의할수있다. 이를기본키라고한다. 외래키 (foreign key) 는두개이상의테이블에서정의되며아래의그림을참고하라. 그림 1.2: 외래키 6
3.4 기본키와널 (NULL) 널값 (Null Value) 은아직알려지지않은값 (Unknown value) 으로아직정의되지않은값 (Undefined value) 을의미한다. 데이터베이스에서의널값은숫자 0이나공백또는널문자를뜻하는것은아니며, 수치연산이나검색연산등에적용받지않는다. 데이터베이스에서는개체무결성규칙에따라테이블 T 에서기본키에속한속성값은 T 내의어떤튜플에대해서도 NULL값을허용않는다. 3.5 관계형데이터베이스 관계형데이터베이스는관계형데이터모델에기반하여하나이상의테이블을구현한데이터베이스이다. 데이터베이스스키마는관계형데이터베이스에서테이블스키마 1 의집합을나타내며, 또한데이터베이스인스턴스 (instance) 는관계형데이터베이스에서테이블인스턴스의집합을나타낸다. 제 4 절 SQL 개요 SQL(Structured Query Language) 이란 DBMS 를다루기위한표준언어로서이용자 ( 응 용프로그램 ) 와 DBMS 사이의중간다리역할담당한다. SQL 을이용하여 DBMS 를제 어하고, 입력할데이터를정의하거나, DBMS 에있는데이터를조작할수있다. 그림 1.3: SQL 의역할 1 데이터베이스관리시스템에서데이터구조와그표현법의기술을수용한파일. 데이터정의언어를해석함으로써시스템에서만들어낸다. 데이터조작언어로나타내는프로그램에는데이터구조의정의가포함되어있지않으므로데이터조작언어를컴파일할때나실행할때에데이터베이스스키마를참조한다. 7
SQL 문장은다음과같이그기능에따라두가지 ( 혹은 3 가지 ) 로분류할수있다. 데이터정의문 : 데이터베이스객체생성, 삭제, 갱신 - CREATE - DROP - ALTER - CREATE DATABASE 데이터베이스 _ 이름 데이터제어문 : 데이터베이스객체이용권한부여 - GRANT - DENY - REVOKE - GRANT CREATE TABLE TO 사용자계정 데이터조작문 : 데이터검색, 삽입, 갱신, 삭제 - SELECT - INSERT - UPDATE - DELETE - SELECT * FROM 테이블 _ 이름 8
제 2 장 MySQL 제 1 절 MYSQL 윈도우즈클라이언트설치 1.1 Windows Essentials (x86) 다운로드 http://dev.mysql.com/downloads/mysql/5.0.html#win32 1.2 Client 설치 1. custom설치선택 (MySQL sever Instance Config 는제외한다 ). 2. 폴더지정 (ex, c: mysql) 1.3 서버세팅 > mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 Server version: 5.0.45-community MySQL Community Edition (GPL) Type help; or \h for help. Type \c to clear the buffer. 9
mysql> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select user,host from user -> ; +------+-----------------------+ user host +------+-----------------------+ root 127.0.0.1 root 192.168.248.1 root 222.96.17.205 root localhost root localhost.localdomain +------+-----------------------+ 5 rows in set (0.00 sec) ( 해석 ) 로컬호스트, 192.168.248.1, 222.96.17.205 에대하여 root 권한을부여 1.4 외부에서접속시 ip 주소및권한추가 사용자를생성해주는 sql 구문 grant [PRIVILIEGES] on [db_name].[table_name] to [USER_ID] @ [HOST_NAME] IDENTIFIED BY [PASSWD] ; [PRIVILIEGES] ALL PRIVILIEGES==> 모든권한 SELECT,INSERT,UPDATE,DELETE 10
[db_name].[table_name] *.* ==> 모든 데이터베이스와 모든 테이블에 접근가능 mysql.user ==> 만약,mysql DB의 user 테이블만 허락 mysql> grant all privileges on *.* to root @ipaddres identified by 비밀 번호 ; 1.5 Client 윈도우 커맨드 창에서 접속 서버의 ip address:192.168.248.204 인 경우 : C:\mysql\bin>mysql -h 192.168.248.204 -u [USER_ID] -p ## -h 는 host를 의 미함 Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 Server version: 5.0.45-community MySQL Community Edition (GPL) Type help; or \h for help. Type \c to clear the buffer. mysql> 제2절 MySQL 시작하기 명령프롬프트에서 MySQL 을 시작하려면 다음을 이용한다. -u 는 사용자의 계정을 의미 하고 db name 은 사용하고자 하는 데이터베이스 이름이다. C:\jskim>mysql -u "root" -p db_name Enter password: * Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 to server version: 5.0.22-community-nt 11
그림 2.1: MySQL 초기화면 2.1 password 변경 유저가자신의 password를바꾸기를원할때가있다. 유저에대한정보는 data\mysql 디렉토리에데이터베이스의형태로보관되고있기때문에 PASSWORD() 함수를사용하여데이터베이스에있는정보를변경하여야한다. mysql> use mysql; Database changed mysql> UPDATE user SET Password=PASSWORD( 1234 ) where user= root ; Query OK, 1 row affected (0.08 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> FLUSH PRIVILEGES; 2.2 주석문 (comment) MySQL은주로아래와같은세가지의주석문형태를제공한다. 하지만두번째행의 는사용하지않는것이좋다. 이는음수의뺄셈과혼동되기쉽기때문이다. 예를들어 1-(- 1)) 은 1 1로표현할수가있어혼란의여지가있다. mysql> select 1+1; # This comment continues to the end of line 12
mysql> select 1+1; -- This comment continues to the end of line mysql> select 1 /* this is an in-line comment */ + 1; mysql> select 1+ -> /* this is a -> multiple-line comment */ -> 1; 13
제 3 장 SQL 의활용 데이터정의언어 (Data definition Language: DDL)-데이터의저장구조를정의하는언어로스키마정의수정, 삭제하는명령문과인덱스의생성및삭제 데이터조작언어 (Data Manipulation Language: DML)-데이터에접근및조작하는언어로튜플의검색, 삽입, 삭제수정과같은명령문 제 1 절 DATABASE 와관련명령들 1.1 데이터베이스리스트출력 SHOW DATABASES; 1.2 데이터베이스생성및삭제 CREATE DATABASE [db_name]; 이문장을실행하면 MySQL data 디렉토리에 db name이란서브디렉토리가생성된다. 만일같은이름의 data base디렉토리가있으면에러메시지를내보낸다. ( 예 ) 새로운 database world를만들고, 이를 handling하기위한명령문은다음과같다. mysql> CREATE DATABASE world; mysql> USE world; 14
만일 database 를구축하기위한 sql 문이다른파일로이미만들어진경우이를수행하 기위해서다음과같은 source 명령을이용한다. mysql> SOURCE world.sql; DROP DATABASE [IF EXISTS] db_name DROP DATABASE는데이터베이스와함께데이터베이스안에있는모든테이블을삭제하는명령이다. 따라서조심스럽게사용해야한다. 참고로테이블과관련된파일은 3 가지종류가있는데, 확장자가.ISD,.ISM, 그리고.frm 이다. MySQL은 IF EXISTS라는키워드를제공하는데이는삭제하려는데이터베이스가없는경우에에러메시지를내보내는것을막아준다. 제 2 절 TABLE 관관련된명령들 2.1 TABLE 리스트출력 DB 안에있는 table(or relation) 들의이름을보기위한명령은다음과같다. mysql> show tables; 2.2 TABLE 내의 column 속성출력 또한 database안에있는특정 table이어떤 column들로구성되어있는지를확인하려면다음의명령중하나를사용하면된다. mysql> show columns from album; mysql> describe album; mysql> desc album; 2.3 TABLE 생성 CREATE TABLE [IF NOT EXISTS] tbl_name (create_definition,...); 15
create_definition: col_name type [NOT NULL NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY] type: INTEGER[(length)] [UNSIGNED] [ZEROFILL] or REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] or CHAR(length) [BINARY] or VARCHAR(length) [BINARY] or DATE or TIME or ENUM(value1,value2,value3,...) 2.3.1 column Options PRIMARY KEY : 기본키로설정 NOT NULL : 반드시값을가져야함을의미 AUTO_INCREMENT : 레코드가삽입될때마다자동으로값을증가 2.3.2 컬럼 (or Domain) Type char(n): Fixed length character string, with user-specified length n. varchar(n): Variable length character strings, with user-specified maximum length n. int: Integer (a finite subset of the integers that is machine-dependent). smallint: Small integer (a machine-dependent subset of the integer domain type). numeric(p,d): Fixed point number, with user-specified precision of p digits, with n digits to the right of decimal point. 16
real, double precision: Floating point and double-precision floating point numbers, with machine-dependent precision. float(n): Floating point number, with user-specified precision of at least n digits. 참고 : Null values are allowed in all the domain types. Declaring an attribute to be not null prohibits null values for that attribute. 2.3.3 테이블생성 예제 테이블을 만들기 전에 테이블에 포함할 컬럼의 속성을 정의해야 한다. 만일 A 는 소매점 을 운영하는 사람이고, 고객관리를 위해서 고객정보를 포하하는 고객테이블을 생성하고 싶어한다고 하자. 따라서 고객테이블에는 고객의 정보인 이름과 나이, 전화번호, 이메일 그리고 자택주소가 포함되어 있어야 한다고 하자. 이 경우의 예에서 고객테이블의 명칭 을 customer 라고 정한다면 아래와 같이 고객테이블을 생성할 수 있다. 테이블 명 : customer 컬럼 명 : no: 자동으로 부여되는 일련 번호 name: 이름 age: 나이 phone: 전화번호 email: 전자우편주소 address: 주소 테이블을 만들기 전에 아래와 같이 테이블명세서를 미리 만들어 두면 테이블생성시 의 오류를 피할 수 있다. 테이블명 컬럼명 데이터형식 NULL 유무 기본키 고객테이블 (customer) no name age phone email address INTEGER CHAR(20) TINYINT VARCHAR(20) VARCHAR(30) VARCHAR(50) NOT NULL NOT NULL PK NOT NULL 표 3.1: 테이블 명세서 17 외래키 FK 테이블명 FK 컬럼명
CREATE TABLE customer( no INT PRIMARY KEY NOT NULL AUTO_INCREMENT, name CHAR(20) NOT NULL, age TINYINT, phone VARCHAR(20), email VARCHAR(30) NOT NULL, address VARCHAR(50) ); 2.4 테이블의삭제 (delete/drop) delete all data from table delete from [table_name]; delete from guest; deletes all information about the dropped relation from the database. drop table table_name; drop table guest; 2.5 테이블의속성변경 (alter) 테이블에새로운속성 (attribute) 추가, 제거혹은속성변경 컬럼추가 alter table [table_name] add [col_name] [column_type]; ## student 테이블에나이컬럼을추가하고그도메인을 int 로한다. alter table student add age int; 결과적으로나이컬럼에해당되는값들은모두 null 값을가진다. 18
컬럼 제거 alter table [table_name] drop [col_name]; // student 테이블에서 idnum(주민등록번호) 컬럼 제거 alter table student drop idnum; (주의) 이 명령은 모든 데이터베이스에서 지원하지는 않는다. 칼럼명을 변경 alter table [table_name] change [old_name] [new_name] [column_type]; alter table address change name firstname varchar(15); 테이블에 자료입력 2.6 테이블을 만든 후에 테이블에 자료를 입력하기 위해서는 다음의 두가지 방법을 이용한다. LOAD DATA 구문의 이용 INSERT 문 사용 2.6.1 LOAD DATA 구문의 이용 외부파일을 이용하여 테이블에 자료를 입력할 때의 사용형식은 다음과 같다. LOAD DATA LOCAL INFILE "file_name" INTO TABLE tbl_name; 예를들어 애완 동물과 관련된 자료를 DB 에 아래와 같이 입력하고 싶다고 하자. name owner species sex birth death -------------------------------------------------Fluffy Harold cat f 1993-02-04 Claws Gwen cat m 1994-03-17 19
Buffy Harold dog f 1989-05-13 Fang Benny dog m 1990-08-27 Bowser Dianne dog m 1998-08-31 1995-07-29 Chirpy Gwen bird f 1998-09-11 Whistler Gwen bird 1997-12-09 Slim Benny snake m 1996-04-29 CREATE TABLE을이용하여 table을만든다. create table pet( name varchar (20) not null primary key, owner varchar (20), species varchar (20), sex varchar (1), birth date, death date }; 먼저입력할내용을아래와같이 pet table의컬럼순서에따라텍스트파일 ( 여기 서는 pet.txt) 로만든다. 여기서열의값들은탭 (Tab) 키하나로구분하며, 아직 정해지지않은값이있는경우 ( 즉, NULL값이있는경우 ) 에는 \N 로입력한다. Fluffy Harold cat f 1993-02-04 \N Claws Gwen cat m 1994-03-17 \N Buffy Harold dog f 1989-05-13 \N Fang Benny dog m 1990-08-27 \N Bowser Dianne dog m 1998-08-31 1995-07-29 Chirpy Gwen bird f 1998-09-11 \N Whistler Gwen bird \N 1997-12-09 \N Slim Benny snake m 1996-04-29 \N pet.txt 파일의내용을 LOAD DATA 문을이용하여테이블 pet에입력한다 : LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet; 20
2.6.2 INSERT 문사용한번에한개의레코드를추가하고자할때가있을것이다. CREATE TABLE 문에정의된순서대로각열의값을적어주면된다. insert into pet [column names..] values( Puffball, Diane, hamster, f, 1999-03-30,NULL); 문자열및날짜의입력은작은따옴표 ( ) 를이용 위에서언급한 NULL 값은 NULL 로입력할수있다 ( 주의 : \N 이라고적으면안 됨 ) 2.7 오류데이터의수정 앞의결과를자세히살펴보면 Bower의출생일자가사망일자보다늦은것을확인할수있다. 즉, 죽은뒤에태어났다. 이러한입력오류가발생할경우에이를수정하기위한두가지방법이있다. 여기서 Bower의출생일자가실제로는 1989-08-31이라고가정하자. 원파일 pet.txt를편집하여수정한다. 테이블을 delete한후 pet.txt에서다시읽어들인다 : mysql> DELETE FROM pet; mysql> LOAD DATA LOCAL infile "pet.txt" into TABLE pet; 하지만이렇게하면 3.3 절에서개별적으로 insert 문을이용하여입력한 Bowser 에 대해서다시입력해야한다. UPDATE 문을사용하여잘못된곳만수정한다. mysql> UPDATE pet SET birth="1989-08-31" WHERE name="bowser"; 21
위의예처럼테이블의크기가작은경우에는전체테이블내용을눈으로확인하여 오류를검사할수있다. 하지만테이블크기가커지면이러한작업은불가능하다. 대신 특별한조건을만족하는자료들만뽑아서보길원할것이다. 제 3 절 테이블로부터의검색 테이블로부터정보를검색하기위해서는 SELECT 문을사용하면된다. select 문의일반 형식은다음과같다 : SELECT [target] FROM [tbl_name] WHERE [condition]; 검색대상 (target) 은검색하려는컬럼들을쉼표를이용하여나열한다. 단모든열 을검색하려고할때는 * 를이용한다. WHERE: 생략가능, WHERE 문을쓸때는검색조건 (condition) 을써준다. 3.1 모든데이터검색 가장간단한 SELECT 문의형태로다음처럼사용할수있다 : mysql> SELECT * FROM pet; +----------+--------+---------+------+------------+------------+ name owner species sex birth death +----------+--------+---------+------+------------+------------+ Fluffy Harold cat f 1993-02-04 NULL Claws Gwen cat m 1994-03-17 NULL Buffy Harold dog f 1989-05-13 NULL Fang Benny dog m 1990-08-27 NULL Bowser Diane dog m 1998-08-31 1995-07-29 Chirpy Gwen bird f 1998-09-11 NULL Whistler Gwen bird NULL 1997-12-09 NULL Slim Benny snake m 1996-04-29 NULL 22
Puffball Diane hamster f 1999-03-30 NULL +----------+--------+---------+------+------------+------------+ 하다. 이런식으로 SELECT 문을사용하는것은테이블의전체정보를보고자할때유용 3.2 조건에맞는특정행검색 (WHERE 문이용 ) WHERE 문을이용하면테이블에서조건을만족하는행들만뽑아낼수있다. 아래의예 를살펴보자. pet 테이블에서 name 이 Bower 인레코드만검색 mysql> SELECT * FROM pet WHERE name = "Bower"; +--------+-------+---------+-----+------------+------------+ name owner species sex birth death +--------+-------+---------+-----+------------+------------+ Bowser Diane dog m 1989-08-31 1995-07-29 +--------+-------+---------+-----+------------+------------+ pet 테이블에서 1998 년이후에태어난동물을검색하고싶다면, birth 열을대상 으로검사하면된다 : mysql> SELECT * FROM pet WHERE birth >= "1998-1-1" +----------+-------+---------+-----+------------+------+ name owner species sex birth death +----------+-------+---------+-----+------------+------+ Chirp y Gwen bird f 1998-09-11 NULL Puffball Diane hamster f 1999-03-30 NULL +----------+-------+---------+-----+------------+------+ ( 조건의 AND 조합 ) 개 (species= dog ) 이면서암컷 (sex= f ) 인동물을검색 23
mysql> SELECT * FROM pet WHERE species = "dog" AND sex = "f"; +----------+--------+---------+-----+------------+-------+ name owner species sex birth death +----------+--------+---------+-----+------------+-------+ Buffy Harold dog f 1989-05-13 NULL +----------+--------+---------+-----+------------+-------+ ( 조건의 OR 조합 ) 뱀 (species= snake ) 이거나새 (species= bird ) 인동물을검색 mysql> SELECT * FROM pet WHERE species = "snake" OR species = "bird"; +----------+-------+---------+------+------------+-------+ name owner species sex birth death +----------+-------+---------+------+------------+-------+ Chirpy Gwen bird f 1998-09-11 NULL Whistler Gwen bird NULL 1997-12-09 NULL Slim Benny snake m 1996-04-29 NULL +----------+-------+---------+------+------------+-------+ ( 조건의 AND/OR 조합 ) 이렇게할때는그룹지어지는조건들을괄호로묶는것 이좋다 SELECT * FROM pet WHERE (species = "cat" AND sex = "m") OR (species = "dog" AND sex = "f"); +-------+--------+--------+-----+------------+-------+ name owner species sex birth death +-------+--------+--------+-----+------------+-------+ Claws Gwen cat m 1994-03-17 NULL Buffy Harold dog f 1989-05-13 NULL +-------+--------+--------+-----+------------+-------+ 24
3.3 특정열의선택 테이블에서한행전체를보기보다는관심있는열 (column) 들만보고싶다면보고자 하는열이름을쉼표로구분하여사용다. name, birth 열을검색 mysql> SELECT name, birth FROM pet; +----------+------------+ name birth +----------+------------+ Fluffy 1993-02-04 Claws 1994-03-17 Buffy 1989-05-13 Fang 1990-08-27 Bowser 1989-08-31 Chirpy 1998-09-11 Whistler 1997-12-09 Slim 1996-04-29 Puffball 1999-03-30 +----------+------------+ 소유주 (owner) 만검색 mysql> SELECT owner FROM pet; +--------+ owner +--------+ Harold Gwen Harold Benny 25
Diane Gwen Gwen Benny Diane +--------+ 중복된행을없애기 (distinct 이용 ) mysql> SELECT distinct owner FROM pet; +--------+ owner +--------+ Benny Diane Gwen Harold +--------+ 3.4 특정행과열의조합 (WHERE 절을이용한 ) 행의선택과 ( 열이름을지정한검색으로 ) 열의선택을조합할 수있다. 개 (dog) 와고양이 (cat) 에대해서만이름 (name), 종 (species), 생일 (birth) 을검색 mysql> SELECT name, species, birth FROM pet -> WHERE species = "dog" OR species = "cat"; +--------+---------+------------+ name species birth +--------+---------+------------+ 26
Fluffy cat 1993-02-04 Claws cat 1994-03-17 Buffy dog 1989-05-13 Fang dog 1990-08-27 Bowser dog 1989-08-31 +--------+---------+------------+ 3.5 행정열 (ORDER BY) 지금까지의예에서결과가전혀정렬되어있지않았다. 결과가정렬된다면원하는자료를훨씬쉽게알아볼수있다. 결과를원하는 column에대하여정렬하기위해서는 ORDER BY 절을사용하면된다. 생일 (birth) 을기준으로정렬한것이다. mysql> SELECT name, birth FROM pet ORDER BY birth; +----------+------------+ name birth +----------+------------+ Buffy 1989-05-13 Bowser 1989-08-31 Fang 1990-08-27 Fluffy 1993-02-04 Claws 1994-03-17 Slim 1996-04-29 Whistler 1997-12-09 Chirpy 1998-09-11 Puffball 1999-03-30 +----------+------------+ 역순으로정렬하려먼 desc 키워드를열이름뒤에적어준다 mysql> SELECT name, birth FROM pet ORDER BY birth desc; 27
+----------+------------+ name birth +----------+------------+ Puffball 1999-03-30 Chirpy 1998-09-11 Whistler 1997-12-09 Slim 1996-04-29 Claws 1994-03-17 Fluffy 1993-02-04 Fang 1990-08-27 Bowser 1989-08-31 Buffy 1989-05-13 +----------+------------+ ( 여러열에대해서도정렬 ) 먼저동물이름 (name) 으로정렬하고각동물에대해생일 (birth) 이가장늦은순으로역순정렬 ( 주의 ) DESC 키워드는바로앞의열에만적용된다. 따라서 species에대하여는오름차순정렬이된다. mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC; +----------+---------+------------+ name species birth +----------+---------+------------+ Chirpy bird 1998-09-11 Whistler bird 1997-12-09 Claws cat 1994-03-17 Fluffy cat 1993-02-04 Fang dog 1990-08-27 Bowser dog 1989-08-31 28
Buffy dog 1989-05-13 Puffball hamster 1999-03-30 Slim snake 1996-04-29 +----------+---------+------------+ 제 4 절 패턴일치기능 (LIKE/REGEXP) 을이용한검색 위의예에서이름 (name) 이 b 로시작하는행을검색한다고하자. SQL에서이러한것을지원하는기능이있는데이를패턴일치기능이라고하며, 보다빠르고정교하게원하는조건에대한검색을해주기때문에매우유용하다고하겠다. MySQL의패턴일치기능은 LIKE 혹은 REGEXP( 정규표현식 ) 를이용하여검색할수있다. 4.1 LIKE 를이용한패턴일치검색 SQL 에서패턴일치기능은 LIKE 구문을이용하고패턴의표현을위해서 와 % 기호를 사용한다. 여기서 _ 은임의한문자를의미하며, % 는임의의수의문자 (0개의문자를포함 ) 를나타낸다. 따라서 b 로시작하는패턴은 b% 로표시한다. LIKE를이용할때는패턴에서대소문자는비교하지않는다. 아래는 LIKE 를이용한패턴일치검색의사용예이다. 정확하게 5 개의글자로이루어진이름 (name) 에대한검색 : 밑줄 (_) 5 개를사용 하여검색조건을나타낸다. mysql> SELECT * FROM pet WHERE name LIKE " "; +-------+--------+---------+-----+------------+-------+ name owner species sex birth death +-------+--------+---------+-----+------------+-------+ Claws Gwen cat m 1994-03-17 NULL 29
Buffy Harold dog f 1989-05-13 NULL +-------+--------+---------+-----+------------+-------+ 이름 (name) 이 b 로시작하는패턴검색 mysql> SELECT * FROM pet WHERE name LIKE "b%"; +--------+--------+---------+-----+------------+-----------+ name owner species sex birth death +--------+--------+---------+-----+------------+-----------+ Buffy Harold dog f 1989-05-13 NULL Bowser Diane dog m 1989-08-31 1995-07-29 +--------+--------+---------+-----+------------+-----------+ 이름 (name) 이 fy 로끝나는패턴검색 mysql> SELECT * FROM pet WHERE name LIKE "%fy"; +--------+--------+---------+-----+------------+-------+ name owner species sex birth death +--------+--------+---------+-----+------------+-------+ Fluffy Harold cat f 1993-02-04 NULL Buffy Harold dog f 1989-05-13 NULL +--------+--------+---------+-----+------------+-------+ 이름 (name) 에 w 가포함된패턴에대한검색 mysql> SELECT * FROM pet WHERE name LIKE "%w%"; +----------+-------+---------+------+------------+------------+ name owner species sex birth death +----------+-------+---------+------+------------+------------+ Claws Gwen cat m 1994-03-17 NULL Bowser Diane dog m 1989-08-31 1995-07-29 Whistler Gwen bird NULL 1997-12-09 NULL +----------+-------+---------+------+------------+------------+ 30
정규표현식에사용되는문자설명. 문자하나 * 앞에나온문자의 0개이상의반복 ^ 문자열처음 $ 문자열끝 [] 괄호안의문자들중하나이상과일치 { } 반복연산자, 예를들어 n번반복할때 {n} 으로적는다. 표 3.2: 정규표현식에사용되는문자및용도 4.2 정규표현에의한패턴일치 (REGEXP) 정규표현 (regular expression) 에기반한패턴일치검색은 LIKE대신 REGEXP을사용한다. 여기서 SQL 패턴은전체값과일치해야 일치한다 고하지만정규표현은값의어느부분과일치해도 일치한다 고한다는것을유의해야한다. SELECT * FROM pet WHERE name REGEXP ffy"; //ffy 가포함된것 SELECT * FROM pet WHERE name LIKE ffy"; // 반드시 ffy 인것 문자 a 나 b 나 c 중하나를가르키는표현은 [abc] 이다. 정규표현은대소문자를구별, 대문자던소문자던상관없이알파벳문자를하나라 도포함하는표현은 [a-za-z] 로해야한다. * 는 0 개이상의문자들을의미하므로만일 x* 라는표현은 x, xx, xxx... 를나타 낸다. [0-9]* 는 7, 12, 345, 678 등임의의길이를갖는수를나타낸다. ^abc 는줄처음에 abc 로시작하는패턴을 abc$ 는 abc 로끝나는문자열을의미 한다. 이름 (anme) 이소문자 b 혹은대문자 B 로시작하는조건검색 mysql> SELECT * FROM pet WHERE name REGEXP "^[bb]"; +--------+--------+---------+-----+------------+------------+ 31
집단함수 COUNT SUM AVG MAX MIN 의미행의개수 NULL을제외한모든행의합계 NULL을제외한모든행의평균값 NULL을제외한모든행의최대값 NULL을제외한모든행의최소값 name owner species sex birth death +--------+--------+---------+-----+------------+------------+ Buffy Harold dog f 1989-05-13 NULL Bowser Diane dog m 1989-08-31 1995-07-29 +--------+--------+---------+-----+------------+------------+ fy 로끝나는이름검색 ($ 를사용 ) mysql> SELECT * FROM pet WHERE name REGEXP "fy$"; +--------+--------+---------+-----+------------+-------+ name owner species sex birth death +--------+--------+---------+-----+------------+-------+ Fluffy Harold cat f 1993-02-04 NULL Buffy Harold dog f 1989-05-13 NULL +--------+--------+---------+-----+------------+-------+ 정확하게 5 개의문자로이름어진값검색 SELECT * FROM pet WHERE name REGEXP "^...$"; // 정규표현 SELECT * FROM pet WHERE name REGEXP "^.{5}$"; // 정규표현 ( 반복연산자 ) SELECT * FROM pet WHERE name LIKE " "; //LIKE 표현 32
제 5 절 집단함수를이용한요약정보의검색 5.1 행수세기 : COUNT Gwan 이 bird를몇마리소유하고있는지와같이특정조건에해당되는레코드의수를어떻게알아낼수있을까? 이는 count() 함수를사용하면되며괄호안에해당조건을부여해주면된다. mysql> SELECT COUNT(*) FROM pet; +----------+ COUNT(*) +----------+ 9 +----------+ 각소유주가소유한애완동물의수는다음처럼하면확인할수있다. mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner; +--------+----------+ owner COUNT(*) +--------+----------+ Benny 2 Diane 2 Gwen 3 Harold 2 +--------+----------+ 각 owner의모든레코드들을한데묶기위해 GROUP BY 절을사용한것을주목하라. 이렇게하지않으면에러메시지를보게될것이다. mysql> SELECT owner, COUNT(owner) FROM pet; ERROR 1140 at line 1: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause 33
COUNT() 와 GROUP BY는데이터에여러특성을파악하는데대단히유용하다. 다음 은각종에해당하는동물의수를헤아리는명령이다. mysql> SELECT species, COUNT(*) FROM pet GROUP BY species; +---------+----------+ species COUNT(*) +---------+----------+ bird 2 cat 2 dog 3 hamster 1 snake 1 +---------+----------+ 성에따른동물의수는다음의 SQL문으로알아낼수있다. mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex; +------+----------+ sex COUNT(*) +------+----------+ NULL 1 f 4 m 4 +------+----------+ 다음은종과성에따른동물의수를검색한것이다. mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex; +---------+------+----------+ species sex COUNT(*) +---------+------+----------+ bird NULL 1 bird f 1 34
cat f 1 cat m 1 dog f 1 dog m 2 hamster f 1 snake m 1 +---------+------+----------+ 바로위의경우와는달리, 특정한동물에대해서만조사해볼수도있다. 개와고양 이의경우에만각성에대해몇마리인지조사해보자. mysql> SELECT species, sex, COUNT(*) FROM pet -> WHERE species = "dog" OR species = "cat" -> GROUP BY species, sex; +---------+------+----------+ species sex COUNT(*) +---------+------+----------+ cat f 1 cat m 1 dog f 1 dog m 2 +---------+------+----------+ mysql> SELECT COUNT(*) AS N_STUDENT FROM STUDENT; mysql> SELECT ID_LEC COUNT(*) AS N_REG, AVG(FINAL) AS MEAN_FINAL -> FROM LECTURES -> GROUP BY ID_LEC -> HAVING COUNT(*) >= 5; 여기서 HAVING 절은 GROUP BY 절과같이사용되며, GROUP BY 절에명시된열이 름에대한제약조건을의미한다. 35
5.2 유용한함수들 난수발생 RAND() mysql> SELECT id,name FROM STUDENT ORDER BY RAND() LIMIT 5; +----+--------+ id name +----+--------+ 3 이수만 2 임성훈 6 박수홍 12 정윤호 13 권보아 +----+--------+ 문자열결합 CONCAT() mysql> SELECT id,concat(name,"(",alias,")") AS " 이름 ( 별명 )" FROM STUDENT; +----+---------------------+ id 이름 ( 별명 ) +----+---------------------+ 1 조용필 ( 작은거인 ) 2 임성훈 () 3 이수만 () 4 임예진 () 5 원미경 () 6 박수홍 ( 허우대 ) 7 안칠현 () 8 김태희 ( 헤드뱅잉 ) 9 한채영 ( 바비인형 ) 10 박수애 ( 리틀정윤희 ) 11 허영란 ( 까치 ) 36
12 정윤호(꼬비) 13 권보아() 14 문근영(국민 여동생) +----+---------------------+ 제6절 6.1 여러 개 테이블에서의 데이터 검색 조인 (JOIN) Join 은 여러 테이블에 저장된 데이터를 하나의 SQL 문으로 한번에 검색할 수 있는 기 능이다. 이 조인기능은 현재 관계 DBMS 를 사실상 표준으로 만드는데 결정적인 역할을 하였다. 조인문을 사용하기 위해서는 두 개 이상의 테이블을 연결하기 위한 공통의 값, 즉 왜래키 (foreign key) 가 적어도 하나 이상 있어야 한다. 조인문을 작성하는 순서 1. SELECT 절에서 검색하고 하는 열 이름들을 명시한다 2. FROM 절에서 SELECT 절에 지정된 열 이름들의 소속 테이블 이름을 명시한다 3. FROM 절에 지정된 테이블 이름이 두 개 이상이면, 조인을 위해서 WHERE 절에 조인 조건절을 명시해야 한다 조인에서 두 개 이상의 테이블에서 열 이름이 중복될 가능성이 있다. 이를 해결하기 위해서 열 이름 앞에 테이블 이름을 접두사로 사용한다.그리고 테이블 이름과 열 이름 은 반드시 점 (.) 으로 구분해야 한다. 예를 들면 professor 와 student 테이블에 id 라는 컬럼명을 동시에 사용한다고 하자. 이 경우 조인문에서는 professor.id 와 student.id 를 사용하므로써 서로를 구분한다. 조인에서 테이블 이름을 AS 문을 이용하여 다른 이름으로 변경 (별명) 하여 사용할 수 있다. FROM 절에서 테이블 이름에 별명을 지정한 후, 조인 조건절 (where) 에서 지정된 별명을 사용하면 효율적이다. 교수의 번호, 이름, 학과 이름을 출력하라. SELECT p.id as 교수번호, p.name as 교수이름, d.name as 학과명 37
테이블명 컬럼명 데이터형식 NULL 유무 기본키 외래키 FK-TABLE FK 컬럼명 학과 번호 INTEGER NOT NULL 0 (DEPART) 이름 VARCHAR(50) 교수 번호 INTEGER NOT NULL 0 (PROFESSOR) 이름 VARCHAR(50) 학과번호 INTEGER NOT NULL 0 DEPART 번호 STUDENT 번호 INTEGER NOT NULL 0 ( 학생 ) 이름 VARCHAR(30) 주소 VARCHAR(50) 학년 INTEGER 키 INTEGER 몸무게 INTEGER 별명 VARCHAR(20) 별명이유 VAHRCHAR(50) 학과번호 INTEGER NOT NULL 0 DEPART 번호 CURRICULUM 번호 INTEGER NOT NULL 0 ( 과목 ) 이름 VARCHAR(30) 학점 INTEGER 교수번호 INTEGER NOT NULL 0 PROF 번호 REGIST 번호 INTEGER NOT NULL 0 ( 등록 ) 등록일 DATETIME 학생번호 INTEGER NOT NULL 0 STUDENT 번호 LECTURE 등록번호 INTEGER NOT NULL 0 0 REGIST 번호 ( 수강 ) 과목번호 INTEGER NOT NULL 0 LECTURE 번호 중간성적 DECIMAL(6,2) 기말성적 DECIMAL(6,2) 표 3.3: 테이블명세서 FROM professor as p, depart as d where d.id = p.id; +----------+----------+-------------+ 교수번호 교수이름 학과명 +----------+----------+-------------+ 1 김태길 국문과 2 김봉남 연극영화과 38
3 조정래 영문과 4 이문열 의류학과 5 안성기 전산학과 6 장미희 철학과 +----------+----------+-------------+ t1 t2 +-------+ +---------+ i1 c1 i2 c2 +-------+ +---------+ 1 a 2 c 2 b 3 b 3 c 4 a +-------+ +---------+ 6.2 풀조인 (full join or cross join) SELECT t1.*, t2.* FROM t1,t2; +------+------+------+------+ i1 c1 i2 c2 +------+------+------+------+ 1 a 2 c 2 b 2 c 3 c 2 c 1 a 3 b 2 b 3 b 3 c 3 b 1 a 4 a 2 b 4 a 3 c 4 a +------+------+------+------+ 39
SELECT t1.*, t2.* FROM t1,t2 WHERE t1.i1=t2.i2; +------+------+------+------+ i1 c1 i2 c2 +------+------+------+------+ 2 b 2 c 3 c 3 b +------+------+------+------+ 다음의 SQL문을수행해보고위의결과와비교해보시오. SELECT t1.*, t2.* FROM t1 JOIN t2 WHERE t1.i1=t2.i2; SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.i1=t2.i2; SELECT t1.*, t2.* FROM t1 CROSS JOIN t2 WHERE t1.i1=t2.i2; SELECT t1.*, t2.* FROM t1 CROSS JOIN t2 ON t1.i1=t2.i2; 6.3 외부조인 (full join) SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.i1=t2.i2; +------+------+------+------+ i1 c1 i2 c2 +------+------+------+------+ 1 a NULL NULL 2 b 2 c 3 c 3 b +------+------+------+------+ SELECT t1.*, t2.* FROM t1 RIGHT JOIN t2 ON t1.i1=t2.i2; +------+------+------+------+ i1 c1 i2 c2 +------+------+------+------+ 2 b 2 c 40
3 c 3 b NULL NULL 4 a +------+------+------+------+ SELECT * FROM (Artist INNER JOIN CD ON CD.ArtistID=Artist.ArtistID) WHERE Artist.ArtistID=22; SELECT * FROM Artists, CDs WHERE Artists.ArtistID *= CDs.ArtistID SELECT * FROM CDs, Artists WHERE CDs.ArtistID =* Artists.ArtistID SELECT * FROM Artists LEFT OUTER JOIN CDs ON Artists.ArtistID = CDs.ArtistID SELECT * FROM CDs RIGHT OUTER JOIN Artists ON CDs.ArtistID = Artists.ArtistID 6.4 서브쿼리 (sub query, sub select) 하나의 SQL 문처리결과를다른 SQL 문에전달하여새로운결과를검색하는기능을서브쿼리 (Sub Query) 라고하며, 이경우 ORDER BY 절은사용이불가하다. 다음의예를보자. 이예는학생테이블에서김태희학생과학년이동일한모든학생의이름, 키, 몸무게를검색하라는 sql문장이다. SELECT name AS 이름, weight AS 몸무게, height AS " 키 ",grade AS " 학년 " FROM student WHERE grade=(select grade FROM student WHERE name=" 김태희 "); 문자열문과를포함하는학과이름을가진학과에소속된모든학생의번호, 이름, 학년, 학과번호를검색하라. 에대한예를보자. 단, 서브쿼리의검색결과가하나이상의행이면, IN, EXISTS 등다중행비교연산자중하나를사용해야한다 SELECT id AS 학번, name as 이름, grade as 학년, did as 학과번호 FROM student WHERE did IN (SELECT id FROM depart WHERE name LIKE % 문과 % ); 6.5 UNION SELECT i1 AS i FROM t1 UNION SELECT i2 AS i FROM t2; 41
+------+ i +------+ 1 2 3 4 +------+ SELECT * FROM t1 UNION SELECT * FROM t2; +------+------+ i1 c1 +------+------+ 1 a 2 b 3 c 2 c 3 b 4 a +------+------+ SELECT * FROM t3; +------+------+ i3 c3 +------+------+ 2 e 6 a +------+------+ 2 rows in set (0.00 sec) SELECT * FROM t1 UNION SELECT * FROM t2 UNION SELECT * FROM t3; 42
+------+------+ i1 c1 +------+------+ 1 a 2 b 3 c 2 c 3 b 4 a 2 e 6 a +------+------+ SELECT * FROM t1 LIMIT 1 UNION SELECT * FROM t2 LIMIT 1 UNION SELECT * FROM t3 LIMIT 1; +------+------+ i1 c1 +------+------+ 1 a +------+------+ (SELECT * FROM t1 LIMIT 1) UNION (SELECT * FROM t2 LIMIT 1) UNION (SELECT * FROM t3 LIMIT 1); +------+------+ i1 c1 +------+------+ 1 a 2 c 43
2 e +------+------+ 44
제 4 장 Database 설계 데이터베이스설계는사용자의요구조건이주어진경우이를바탕으로데이터베이스의구조를이끄는과정을말한다. 따라서사용자가사용하기에부족함이없도록데이터베이스의논리적물리적구조를설계하여야한다. 데이터베이스의설계는 3단계로구분되어진행된다. 개념적설계 (conceptual design): 요구분석에서나온결과를특정 DBMS 에독립적이면서추상화된표현기법을이용하여기술하는단계이다. 개체관계모델 (ER model) 이이러한개념적설계에대표적으로사용하는기법이다. 논리적설계 (logical design): 개념적설계를바탕으로관계형데이터모델이논리적데이터베이스구조에맞도록스키마를생성하는단계이다. 개체관계스키마에서관계형스키마로변환하는과정이다. 물리적설계 (physical design): 논리적구조로부터효율적이고구현가능한물리적데이터베이스구조를설계하는단계이다. 물리적설계단계에서는성능을고려한인덱스의사용, 각속성의데이터타입결정등과같은릴레이션의저장구조를고려한다. 제 1 절 데이터베이스설계시고려해야할사항들 데이터베이스를설계할때기본적으로고려해야할사항으로는다음과같다. 45
그림 4.1: Database 설계의순서 사용자와응용프로그램이필요로하는모든정보를표현 : 요구사항을정확히파악하여반드시필요한정보만을표현하여야한다. 단순하고쉽게이해할수있는구조로표현 : 데이터의중복이발생하지않도록설계 : 위에나열한기본적인사항들을고려하고난후데이터베이스가지녀야할제약조건들을충족하고있는지를검토하여야한다. 1.1 무결성제약 테이터베이스의무결성 (integrity) 이란데이터베이스에저장된테이터가실제의데이터를모순없이잘반영시키기위해서필요한제약조건을의미한다. 따라서데이터베이스를설계할때에는무결성을반드시확인하는절차가수반되어야한다. 무결성제약에는다음과같은것들이있다. 개체무결성제약 : 이는테이블에있는개체들은반드시유일해야한다는조건이다. 이는기본키의조건과일치한다. 참조무결성제약 : 한테이블에서다른테이블을참조할때, 반드시참조되는개체 ( 튜플 ) 가존재해야한다. 이는외래키가갖추어야할조건이다. 키제약 : 이는같은키값을가진두개이상의튜플이있으면안된다는조건이다. 도메인제약 : 각속성은정의된도메인에속한값들만갖는다는조건이다. 46
함수적종속 (functional dependency): 논리적설계에중요함위의무결성제약들중개체무결성, 참조무결성그리고키제약은 DBMS에의해서자동적으로검증이된다. 하지만도메인제약의일부는 DBMS가검증할수없다. 이를테면, 학생은학기당 21학점을초과하여수강할수없다는조건은사용자가지정하는제약조건으로써절대로 DBMS가체크할수없는제약조건이다. 이와같은제약조건에대하여데이터베이스의무결성을확보하기위한방법으로는 SQL문의 assertion이나 trigger 구문을이용한다. 이중 trigger는표준 SQL에정의되지않았으므로 assertion 에대하여만살펴보자. assertion 의기본형식은다음과같다. CREATE ASSERTION <name> CHECK <conditin>; 위의에를 SQL문으로바꾸어보면다음과같다. CREATE ASSERTION 수강규정 1 CHECK (NOT EXIST (SELECT COUNT(*) FROM 수강 GROUP BY 연도, 학기, 학번 ) > 21; 제 2 절 Entity-Relationship (ER) 모델 현실세계를 (1) 엔티티집합, (2) 속성집합, 그리고엔티티집합간의 (3) 관계집합을 이용해서개념적으로표현하는데이터모델로서 Peter Chen (1976) 이 ER 모델을직관 적으로표현하는표시방법인 ERD (Entity-Relationship Diagram) 를제안하였다. 2.1 ER 모델의 Entity 엔티티타입 (Entity Type) 또는엔티티집합 (Entity Set) 은현실세계에존재하는대상또는개념적으로존재하는대상중에서꼭필요하고유용한정보이며보통실무에서는엔티티 (entity) 라고함 엔티티인스턴스 (Entity Instance): 엔티티타입이실현된형태이며, 예를들어엔티티 ( 타입 ) 를교수와학생이라고하면교수엔티티의인스턴스는김주환, 조태경, 심규박, 김진석이되며, 학생엔티티의인스턴스는권재민, 박용민, 조승현등을말한다. ERD에서엔티티는직사각형으로표시한다. 47
그림 4.2: E-R 다이어그램 그림 4.3: ERD 에서엔티티표시법 2.2 ER 모델의속성 (Attribute Set) 속성집합 (Attribute Set) 은엔티티의속성이나상태를구체적으로기술한것으로간단히속성이라고한다. 엔티티별로여러개의속성이있을수있다. 예 ) 학생엔티티의속성으로는학번, 이름, 학과, 학년등이있을수있다. 속성값 (Attribute Value) 은엔티티의특성이나상태가현실화된값으로엔티티인스턴스별로해당되는속성값을부여한다. 예 ) 학생엔티티인스턴스에김태희가있다면김태희의속성값즉, 키 =165cm, 학과 = 의류학과등이속성값이다. ERD에서는속성을타원으로표시한다. 엔티티속성 ( 집합 ) 은식별자 (Identifier) 와설명자 (Descriptor) 로아래와같이구분된다. 48
그림 4.4: ERD 에서속성집합 (Attribute Set) 표시법 식별자 (Identifier): 엔티티인스턴스를다른인스턴스와구별할수있게하는하나또는그이상의엔티티속성을말하며, 관계형데이터베이스의 super key 역할을하는속성이다. 이를테면학번이나주민번호가이에속한다. ERD에서식별자인속성은밑줄로표시한다. 그림 4.5: ERD 에서 identifier( 식별자 ) 속성의표시법 설명자 (Descriptor): 식별자이외에엔티티를설명하기위한속성이다. 속성중연락처가둘이상이라든지, 취미가여러개인경우와같이속성들은여러개의속성값들을가질수있다. 이러한속성을다중값속성 (Multivalued Attribute) 이라고한다. 그림 4.6: ERD 에서다중값속성의표시법 또한성명은성과이름으로구분할수있고, 생년월일은생년 + 월 + 일로구분될 수있다. 이렇듯속성이여러개의세부속성으로구분되는경우 ( 혹은포함하는 경우 ) 이를복합속성 (Composite Attribute) 이라고한다. 49
그림 4.7: ERD 에서복합속성의표시법 2.3 ER 모델의관계집합 (Relationship Set) 엔티티와엔티티사이의의미있는연관관계를말하며, 간단히관계 (Relationship) 라고한다. 엔티티간의관계가실현된형태를관계인스턴스 (Relationship Instance) 라고한다. ERD에서는 entity 간의관계를다이아몬드로표시한다. 그림 4.8: ERD 에서 entity 간의 Relationship 의표시법 여러엔티티간에특정한관계에의하여연결된엔티티의수를관계의차수 (Degree) 라고하며, 연결된엔티티의수에따라 1 차, 2 차, 3 차... 등으로표현하며 ERD 에서는아래와같이표시한다. 그림 4.9: ERD 에서 entity 간의 Degree 의표시법 50
하나의관계에실제로참여할수있는인스턴스수를관계의카디낼리티 (Cardinality) 라고한다. 일반적으로카디낼리티는일대일 (1 : 1), 일대다 (1 : M), 혹은다대다 (M : N) 로표현될수있고, ERD 에서는아래와같이표시한다. 그림 4.10: ERD 에서 entity 간의관계차수표시법 제 3 절 Entity-Relationship (ER) 모델에서관계형스키 마로의변환 개체집합의변환 : 엔티티와단순속성변환규칙 ERD에서하나의엔티티는하나의테이블로변환 그엔티티에소속된단순속성은그테이블에소속된열로변환 식별자속성은기본키로변환 복합속성 (composite attribute) 변환 : 복합속성을구성하는단순속성들을해당테이블의열로변환 그림 4.11: 복합속성 (composite attribute) 변환 51
다중값 (multi-valued) 속성의변환 : 다중값속성은신규테이블을생성함 신규테이블에다중값속성에포함되는단순속성을열로표시함 다중값속성을포함하는엔티티의식별자를신규테이블에포함시키고, 외래키로지정 신규테이블의기본키는외래키와다중값속성의조합이된다. 그림 4.12: 다중값 (multi-valued) 속성의변환 관계변환규칙 : ERD 에서관계는테이블구조에서외래키로변환 일대다 : ERD 에서일측엔티티관련테이블의기본키를다측엔티티관련 테이블의속성 ( 열 ) 으로추가 복사한속성을외래키로지정 그림 4.13: 일대다관계의변환 일대일 : 두엔티티중에서부모역할을하는한엔티티를선택하여기본키를 다른테이블에추가 52
그림 4.14: 일대일관계의변환 그림 4.15: 다대다관계의변환 다대다 : 다대다관계는교차엔티티로간주 교차엔티티를테이블로변환 릴레이션의중복과결합 : 53
제 5 장 데이터베이스설계예제 제 1 절 대학교데이터베이스 대학데이터베이스는학생과교수에관한데이터를나타낸다. 학생에관한정보로는이름, 나이, 성, 도시, 출생지 ( 도시 ), 가족들의거주지, 전에살았던장소와기간, 이수한과목 ( 과목명, 코드, 교수, 학점, 날짜 ) 등이있다. 그리고지금이수하고있는과목과매일매일강의하는장소와시간도포함한다. 대학원생에관한정보로는교수이름과지난해총이수학점등이있다. 박사과정학생에관한정보로는논문제목과연구분야등이있다. 교수에관한정보로는이름, 나이, 출생한장소, 소속학과, 전화번호, 직위, 상태, 그리고주제등이있다. 제 2 절 항공예약관리데이터베이스 항공사의예약데이터베이스는비행기와승객예약에대한데이터를저장한다. 우선보유하고있는비행기에대한기종, 엔진종류, 생산연도, 좌석수에대한정보가있다. 또한스케줄이잡힌각비행기에대해, 출발지와도착지및일시를알수있다. 각비행기에대해서는중간기착지없이하나의출발지와도착지에연결되는것으로가정한다. 54
출발지와도착지는공항이름, 국가, 도시이름, 인구에대한정보가있다. 각승객은이 름, 성별, 전화번호및좌석, 흡연여부, 마일리지를알수있고각승객은복수예약이 가능하다. 제 3 절 비디오샵관리데이터베이스 서울동양비디오 (SDV) 사는대여및판매도매상이다. 대여는개인고객들에게, 판매는소규모비디오점을운영하는소매점고객들에게이루어지고있다. SDV 사는보다철저한고객만족을위해새로운정보시스템을구축하기로결정했다. 각고객은고객id로식별되며이름, 전화번호및주소를가진다. 개인고객의경우는신용카드번호가필요하며, 소매고객의경우는사업자등록번호와 FAX 번호가요구된다. SDV 사의고객이되기위해서는적어도한번은 SDV 사로부터비디오테이프를대여했거나구입했어야한다. 고객은한번에여러개의테이프를대여할수있으나대여일로부터 3일이내에반납하지않으면한개당일일 1,000원의벌금을지불해야한다. 반납만기일까지빌린테이프를다보지못한경우, 어떤고객은무더기벌금을피하기위해다본테이프들은반납하고나머지만하루, 이틀더본뒤벌금과함께반납하기도한다. SDV 사는현재테이프가들어와있거나곧들어올모든영화에대해제목, 감독, 판매가격, 대여료등의정보를유지한다. 한영화에대해대여용테이프가여러개인경우는테이프마다일련번호를붙여서관리하며, 각테이프마다테이프형식을기록한다. 소매점고객이테이프를구매할때는구매일, 지불양식, 또각영화당구매수량등이기록된다. SDV 사는최근고객들이영화를고를때특정영화배우가출연했거나대종상에출연배우가후보로지명된영화들을선호하는현상을보고, 고객서비스차원에서대종상연기부문정보 ( 연두, 부문, 상금, 후보영화, 후보배우등 ) 와영화배우정보 ( 이름, 주소, 출연영화등 ) 를갖춰고객의영화선정을도울계획이다. 제 4 절 도서관데이터베이스 대학의도서관에서는도서관전산화를위한데이터베이스구축을위해업무를분석하고 55
있다. 도서관에있는책들은단행본과연속간행물로구분되는데, 모든책들은분류번호, 서명, 출판사, 구입일등의정보를갖고있다. 여기에부가하여단행본의경우에는저자의이름에대한정보가있으며, 연속간행물의경우에는몇권몇호인지에대한권과호에대한정보가있다. 도서관에는직원들이있는데, 각각사원번호, 성명, 직급, 급여등에대한정보를갖고있으며, 부양가족의인원수에따라서가족수당을받고, 가족의생일에는꽃배달서비스를제공한다. 또한, 직원들중에서몇사람의책임자를선임하여다른직원을관리한다. 학생들은최대 5권까지대출할수있으며, 대출장부에는대출한학생의학번, 책의분류번호, 대출일, 그리고반납예정일을기록해둔다. 대출기간은일주일이며, 연체할경우연체료를부과한다. 또한, 연속간행물의경우에는장기대출을할수있는데, 이때에는별도의장기대출장부에대출한학생의이름, 대출해준직원의이름, 책의분류번호, 대출일, 반납예정일등을기록해두며, 1개월단위로 1000원씩의대여료를부과한다. 대학도서관에서는책을출판한출판사에대한정보를갖고있는데, 여기에는출판사이름, 창립연도, 전화번호, 주소등이포함된다. 제 5 절 병원데이터베이스 병원데이터베이스는환자와병원각부서로부터의입원및퇴원, 치료에대한데이터를저장한다. 각환자에대해서는이름, 주소, 성별, 주민등록번호, 보험코드등을알수있어야하고각부서에대해서는부서명, 위치, 책임자, 병상의수, 사용중인병상의수를저장한다. 각부서에는여러명의의사들과간호사가있으며의사중한명이책임자가된다. 각입원환자에는담당의사와간호사가있으며각의사는여러명의입원환자를담당한다. 반면에간호사는여러명의환자를담당하고각환자는한명의의사와여러명의간호사로부터관리를받는다. 각의사와간호사에대해서는사원번호, 이름, 주민등록번호, 재직년수등의정보가있다. 각환자들은지정된날짜에입, 퇴원하며입원기간동안여러번의치료를받는다. 치료에대해서는치료명, 기간, 환자가보일수있는반응등을저장한다. 56
제 6 장 릴레이션정규화 정규화 (Normalization) 의기본원칙은데이터베이스에저장되는데이터의중복을최소 화하기위하여서로독립적인관계는별개의테이블로표현한다는것이다. 그림 6.1: 정규화과정과정규화간의관계 (1) 57
제 1 절 제 1 정규형 제 1 정규형이되기위해서는한셀에하나의데이터만나타나야한다. 그림 6.2: 정규화되지않은테이블 그림 6.3: 1 차정규화가끝난테이블 제 2 절 제 2 정규형 제 1 정규형에서기본키열과기본키가아닌열간의종속관계가있는경우, 이를 별도의테이블로분리하면제 2 정규형이된다. 함수종속성 (Functional Dependencies:FD): 속성 A 의모든원소들이속성 B 의유일한원소와대응될때 그림 6.4: 제 1 정규형테이블 제 3 절 제 3 정규형 제 2 정규형에서기본키가아닌열끼리종속관계가있는경우, 이를별도의테이블로 58
그림 6.5: 2 차정규화가끝난테이블 분리하면제 3 정규형이된다 59
그림 6.6: 제 2 정규형테이블 그림 6.7: 3 차정규화가끝난테이블 60
제 7 장 R 에서 MySQL 을접근방법 그림 7.1: R program 초기화면 제 1 절 RODBC 의이용 엑셀파일이나 Access 파일을 ODBC 와연결하여 R 에서가져오기 1. RODBC 패키지설치 : 패키지 package 인스톨 CRAN mirror 사이트 61
패키지리스트 RODBC 를선택또는 > install.packages("rodbc") 2. DB 연결하기 : odbcconnect 이용 #ODBC에 scoremdb 라는 DSN 이름으로등록되어있는경우, > install.packages("rodbc") > library(rodbc) > channel <- odbcconnect("scoremdb") 3. 데이터베이스의테이블이름가져오기 : sqltables 함수이용 > sqltables(channel) 4. 테이블자료가져오기 : sqlfetch 이용테이블을 data frame 으로가져오는기능을수행. sqlfetch(channel, "midterm") sqlfetch(channel, "endterm") 5. sql 문의수행 : sqlquery 이용데이터베이스에 SQL 문을수행해서자료를가져오는기능을수행. > sqlquery(channel, "select * from endterm") > sqlquery(channel, "select * from endterm where id > 10") > sqlquery(channel, "select * from endterm where math > 70 ") ( 예 ) 두테이블을합친뒤, 평균이나표준편차를구하는예. > midterm <- sqlquery(channel, "select * from midterm") > endterm <- sqlquery(channel, "select * from endterm") > score <- cbind(midterm, endterm[,c(3:5)]) > mean(score["korean"]) > sd(score[3]) 62
6. Excel 파일불러오기 : scorexls 라는 DSN 이름으로 ODBC 에등록되어있는 경우 > library(rodbc) > channel2 <- odbcconnect("scorexls") > sqltables(channel2) > sqlquery(channel2, "select * from [sheet1$]") 제 2 절 RMySQL 의이용 1. MySQL DB 연결 : dbconnect() con<-dbconnect(mysql(),group = "lasers") con<-dbconnect(mysql(), user="opto",password="pure-light",dbname="lasers",host="merced") con<-dbconnect(mysql(), username="root", password="1234", dbmane="htest2") 2. 테이블리스트보기 : dblisttables(con) > dblisttables(con) [1] "depart" "depart2" 3. 테이블의컬럼보기 : dblistfields(con, table name ) > dblistfields(con, "depart2") [1] "id" "name" 4. 테이블데이터를 data.frame 으로가져오기 > dbreadtable(con, "depart2") id name 1 1 aaa 2 2 stats 63
5. data.frame 를 DB 테이블로내보내기 : dbwritetable(con, DBtable, Rdataframe ) > a<-data.frame(x=rnorm(10)) > a x 1 0.3421252 2 0.3129875 3 1.6688226 4-0.3774251 5 1.6476455 6-1.2227376 7-1.3733826 8 0.2277806 9-2.0370210 10-0.8352766 > dbwritetable(con, "aa", a) [1] TRUE > dblisttables(con) [1] "aa" "depart" "depart2" 6. file 을 DB 테이블로내보내기 : dbwritetable(con, DBtable, 파일 ) >dbwritetable(con, "test2", "~/data/test2.csv") 7. SQL 문의수행 : dbgetquery(), dbsendquery() 이용 >dbgetquery(con, "select count(*) from a_table") >dbgetquery(con, "select * from a_table") Run an SQL statement and extract its output in pieces (returns a result set): rs <- dbsendquery(con, "select * from WL where width\_nm between 0.5 and 1") d1 <- fetch(rs, n = 10000) d2 <- fetch(rs, n = -1) #n is maximum number of records. Use n=-1 for all records. 64
8. DB 연결종료 : dbdisconnect(con) 65
제 8 장 SAS 에서 MySQL 을접근방법 LIBNAME 을이용한접근 LIBNAME libref mysql <connection-options><libname-options>; Arguments libref: 데이터베이스와의연결하여사용하려고하는 SAS 이름 mysql: MySQL 과접속하기위한 SAS/ACCESS engine 이름 connection-options: DBMS 와연결하기위한여러가지옵션들, 아래는 MySQL 접속시의옵션들 USER= user login ID PASSWORD=MySQL password DATABASE=MySQL database 이름 SERVER=server name or IP address of the MySQL server PORT=MySQL server 에연결하는 TCP/IP 통신 port 숫자 LIBNAME-options SAS 에서 DBMS 객체를처리하는어떻게처리하는가에대한옵션정의 66
libname mysqllib mysql user=testuser password=testpass database=mysqldb server=mysqlserv port=9876; proc print data=mysqllib.employees; run; where dept= CSR010 ; proc sql 을이용한접근법 : proc sql; connect to mysql (user=testuser password=testpass server=mysqlserv database=mysqldb port=9876); select * from connection to mysql (select * from customers where customer like 1% ); disconnect from mysql; quit; 67
참고문헌 [1] 장재영이상구. 데이터베이스시스템. 영지문화사, 2001. [2] 이종만. 사례로배우는데이터베이스설계와구축. 이한출판사, 2006. [3] Y. Choi. MySQL Reference Manual(in Korean). Software Engineering, 1999. [4] Jeremy Cole David Axmark, MichaelWidenius and Paul DuBois. MySQL Reference Manual, 2001. [5] B. D. Ripley and R. M. Ripley. Applications of r clients and servers. In In Proceedings of the Distributed Statistical Computing 2001 Workshop. Vienna University of Technology, 2001. [6] Brian D. Ripley. Using databases with r. R News, 1(1):18 21, January 2001. [7] R Development Core Team. R data import/export, 2001. [8] Brian D. Ripley Torsten Hothorn, David A. James. R/s interfaces to databases. In Proceedings of the 2nd International Workshop on Distributed Statistical Computing, pages 15 17. Vienna, Austria, 2001. 68