제7장 SQL-99: 스키마 정의, 제약조건, 질의어, 뷰

Presentation on theme: "제7장 SQL-99: 스키마 정의, 제약조건, 질의어, 뷰"— Presentation transcript:

1 제7장 SQL-99: 스키마 정의, 제약조건, 질의어, 뷰
7.7 주장으로 일반적인 제약조건 명시 7.8 SQL에서 뷰

2 [그림 4.5] COMPANY 관계 데이터베이스 스키마

3 [그림 4.6] COMPANY 스키마의 관계 데이터베이스 인스턴스

4 7.1 SQL 데이터 정의와 데이터 타입 7.1.1 SQL에서 스키마와 카탈로그 개념
7.1.2 SQL의 CREATE TABLE 명령

5 7.1.1 SQL에서 스키마와 카탈로그 개념 스키마 동일한 데이터베이스 응용에 속하는 테이블, 제약조건, 뷰, 도메인, 기타 구성 요소들을 그룹화 하기 위해 SQL2에 포함된 개념 스키마는 CREATE SCHEMA 문을 이용하여 생성. 다음 문장은 Jsmith라는 권한부여 식별자를 갖는 사용자가 소유하고 있는 COMPANY라는 스키마를 생성한다. CREATE SCHEMA COMPANY AUTHORIZATION Jsmith ; 카달로그와 INFORMATION_SCHEMA

6 7.1.2 SQL의 CREATE TABLE 명령 CREATE TABLE
릴레이션 이름, 애트리뷰트 이름, 데이터 타입 및 제약 조건들을 명세하여 새로운 릴레이션을 생성하는데 사용. 데이타 타입 : INTEGER, FLOAT, REAL, DECIMAL( i, j ), CHAR(n), VARCHAR(n), DATE, TIME, … 애트리뷰트에 NULL값이 허락되지 않는다면 제약조건 NOT NULL 명시. 애트리뷰트 정의에 ‘DEFAULT <값>’절을 추가하여 애트리뷰트의 디폴트 값을 명시 할 수 있다.

7 PRIMARY KEY절은 릴레이션의 기본 키를 구성하는 하나 이상의 애트리뷰트들을 지정.
FOREIGN KEY절에서는 참조 무결성을 지정. UNIQUE절은 대체키를 명시 함. CREATE DOMAIN 도메인 생성 예) CREATE DOMAIN SSN_TYPE AS CHAR(9) ;

8 (a) CREATE TABLE EMPLOYEE
( Fname VARCHAR(15) NOT NULL, Minit CHAR, Lname VARCHAR(15) NOT NULL, Ssn CHAR(9) NOT NULL, Bdate DATE, Address VARCHAR(30), Sex CHAR, Salary DECIMAL(10, 2), Super_ssn CHAR(9), Dno INT NOT NULL, PRIMARY KEY (Ssn), FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn), FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber)) ; [그림 7.1] SQL 데이터 정의 (a) 그림 4.5의 COMPANY 스키마를 정의하는 SQL 문장들

9 CREATE TABLE DEPARTMENT
( Dname VARCHAR(15) NOT NULL, Dnumber INT NOT NULL, Mgr_ssn CHAR(9) NOT NULL, Mgr_start_date DATE, PRIMARY KEY (Dnumber), UNIQUE (Dname), FOREIGN KEY (Mgr_ssn) REFERENCE EMPLOYEE(Ssn) ) ; CREATE TABLE DEPT_LOCATONS ( ……….) ………... [그림 7.1] SQL 데이터 정의 (a) 그림 4.5의 COMPANY 스키마를 정의하는 SQL 문장들 (cont.)

10 7.2 SQL에서 기본 제약조건의 명시 애트리뷰트 제약조건과 디폴트 값 명시 애트리뷰트에 제약조건 NOT NULL 명시.
‘DEFAULT <값>’절로 애트리뷰트의 디폴트 값을 명시. 키와 참조 무결성 제약조건의 명시 기본키가 하나의 속성으로 구성된 경우 해당 열에서 기본키 명시 가능. 예) Dnumber INT PRIMARY KEY; 외래키 정의 시 참조 무결성을 위배했을 때 취할 동작을 명시할 수 있다. 그 동작의 종류에는 SET NULL, CASCADE, SET DEFAULT가 있으며, 위반된 경우를 가리키는 ON DELETE나 ON UPDATE와 함께 사용

11 제약 조건에 이름 부여 CONSTRAINT 제약조건이름: CHECK를 사용하여 투플에 제약 조건을 명시하기 CHECK(Dept_create_date <= Mgr_Start_date) ;

12 (b) CREATE TABLE EMPLOYEE
( Dno INT NOT NULL DEFAULT 1, CONSTRAINT EMPPK PRIMARY KEY (Ssn), CONSTRAINT EMPSUPERFK FOREIGN KEY (Super_ssn) PEFERENCES EMPLOYEE(Ssn) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT EMPDEPTFK FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber) ON DELETE SET DEFAULT ON UPDATE CASCADE ) ; [그림 7.1] SQL 데이터 정의 (b) 참조 트리거된 동작 (referential triggered action) 명시

13 CREATE TABLE DEPARTMENT
( Mgr_ssn CHAR(9) NOT NULL DEFAULT ‘ ’ CONSTRAINT DEPTPK PRIMARY KEY (Dnumber), CONSTRAINT DEPTSK UNIQUE (Dname), CONSTRAINT DEPTMGRFK FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn) ON DELETE SET DEFAULT ON UPDATE CASCADE ) ; [그림 7.1] SQL 데이터 정의 (b) 참조 트리거된 동작 (referential triggered action) 명시 (cont.)

14 CREATE TABLE DEPT_LOCATIONS
( PRIMARY KEY (Dnumber, Dlocation ), FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber) ON DELETE CASCADE ON UPDATE CASCADE ) ; [그림 7.1] SQL 데이터 정의 (b) 참조 트리거된 동작 (referential triggered action) 명시 (cont.)

15 7.3 SQL에서 스키마 변경문 7.3.1 DROP 명령 7.3.2 ALTER TABLE 명령

16 7.3.1 DROP 명령 DROP SCHEMA DROP SCHEMA COMPANY CASCADE;
DROP SCHEMA COMPANY RESTRICT;

17 DROP TABLE DROP TABLE 명령으로 릴레이션과 그 정의를 삭제할 수 있다. RESTRICT 선택사항은 임의의 제약조건들(다른 릴레이션에 있는 외래키 정의처럼)이나 뷰들에서 참조되지 않는 테이블만을 제거한다. CASCADE 선택사항은 테이블 뿐만 아니라 테이블을 참조하는 모든 제약조건과 뷰들을 스키마에서 자동적으로 제거 예) DROP TABLE DEPENDENT CASCADE ;

18 7.3.2 ALTER TABLE 명령 ALTER TABLE
열(애트리뷰트)의 추가/제거, 열 정의의 변경, 테이블 제약 조건들의 추가/제거 등에 사용 예) ALTER TABLE COMPANY.EMPLOYEE ADD COLUMN Job VARCHAR(12) ; 디폴트 절의 지정, 또는 UPDATE 명령을 사용하여 각 튜플에 대해서 새 애트리뷰트 JOB의 값을 입력해야 함 디폴트 절을 지정하지 않으면, 릴레이션의 모든 튜플내 JOB의 값은 NULL로 지정된다 => NOT NULL 사용 불가

19 기존 열을 삭제할 경우 CASCADE 선택사항은 열과 함께 열을 참조하는 모든 제약 조건들과 뷰들을 스키마로부터 자동 제거한다.
예) ALTER TABLE COMPANY.EMPLOYEE DROP COLUMN Address CASCADE; 만약 RESTRICT를 선택한다면, 그 열을 참조하는 뷰들과 제약 조건들이 없어야만 열을 제거할 수 있다.

20 디폴트 절의 제거/정의를 통한 애트리뷰트 정의의 변경
ALTER TABLE COMPANY.DEPARTMENT ALTER Mgr_ssn DROP DEFAULT ; ALTER TABLE COMPANY.DEPARTMENT ALTER COLUMN Mgr_ssn SET DEFAULT “ ” ; 제약 조건의 추가/제거 ALTER TABLE COMPANY.EMPLOYEE DROP CONSTRAINT EMPSUPERFK CASCADE ; 제약 조건의 추가는 새로운 제약조건 앞에 키워드 ADD를 사용하여 명시

21 7.4 SQL에서의 기본 질의 7.4.1 기본 SQL 질의의 SELECT-FROM-WHERE 구조
모호한 애트리뷰트 이름, 재명명 및 투플 변수 WHERE 절의 생략과 ‘*’의 사용 SQL 에서 집합으로서의 테이블 7.4.5 부분 문자열 패턴 비교와 산술 연산자

22 7.4.1 기본 SQL 질의의 SELECT-FROM-WHERE 구조
<애트리뷰트 리스트> : 질의를 통해 값이 검색되는 애트리뷰트 이름들의 리스트 <테이블 리스트> : 질의 처리를 위해 필요한 릴레이션들의 리스트 <조건> : 질의를 통해 검색되는 튜플을 식별하기 위한 조건(부울) 탐색식

23 예) 이름이 'John B. Smith'인 종업원의 생일과 주소를 검색하시오.
SELECT Bdate, Address FROM EMPLOYEE WHERE Fname='John' AND Minit='B' AND Lname='Smith’ ;

24 예) 'Research' 부서에서 일하는 모든 종업원들의 이름과 주소를 검색하시오.
SELECT Fname, Lname, Address FROM EMPLOYEE, DEPARTMENT WHERE Dname='Research' AND Dnumber=Dno ;

25 예 ) 'Stafford'에 위치한 모든 프로젝트에 대한 프로젝트 번호(Pnumber), 담당부서 번호(Dnum), 부서 관리자의 성(Lname), 주소(Address), 생일(Bdate)을 열거하시오. SELECT Pnumber, Dnum, Lname, Address, Bdate FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE Dnum=Dnumber AND Mgr_ssn=Ssn AND Plocation='Stafford’ ;

26

27 7.4.2 모호한 애트리뷰트 이름, 재명명 및 투플 변수 동일한 이름을 갖는 두개 이상의 애트리뷰트에 대한 질의 시 “릴레이션 이름.애트리뷰트 이름”을 사용하여 모호성 제거 SELECT Fname, EMPLOYEE.Name, Address FROM EMPLOYEE, DEPARTMENT WHERE DEPARTMENT.Name='Research' AND DEPARTMENT.Dnumber=EMPLOYEE.Dnumber ;

28 모호성은 같은 릴레이션을 두 번 참조하는 질의에서도 나타난다 => 별명(alias) 이용
예) 각 종업원에 대해, 종업원의 성과 이름, 그리고 직속 감독자의 성과 이름을 검색하시오. SELECT E.Fname, E.Lname, S.Fname, S.Lname FROM EMPLOYEE AS E, EMPLOYEE AS S WHERE E.Super_ssn=S.Ssn ;

29 별명은 EMPLOYEE AS E처럼 키워드 AS를 이용하여 선언 가능
질의 내에서 별명을 주어 릴레이션의 애트리뷰트를 재명명할 수도 있다. 예) EMPLOYEE AS E( Fn, Mi, Ln, Ssn, Bd, Addr, Sex, Sal, Sssn, Dno) ; Fn은 Fname, Mi는 Minit, Ln은 Lname 등의 별명 관계가 이루어진다.

30 7.4.3 WHERE 절의 생략과 ‘*’의 사용 WHERE절을 생략하면 투플 선택에 대한 조건이 없다는 것을 의미. FROM절에서 지정한 릴레이션의 모든 투플들이 질의 결과로 선택된다 예) (1)데이타베이스에서 EMPLOYEE의 모든 Ssn을 선택하고, (2) EMPLOYEE의 Ssn과 DEPARTMENT의 Dname의 모든 조합을 선택하시오 (1) SELECT Ssn FROM EMPLOYEE ; (2) SELECT Ssn, Dname FROM EMPLOYEE, DEPARTMENT ;

31 선택된 튜플들의 모든 애트리뷰트 값들을 검색할 경우 애트리뷰트 이름을 명시적으로 열거하는 대신 '＊'를 사용할 수 있다.
질의 (1): 5번 DEPARTMENT에서 일하는 EMPLOYEE 튜플들의 모든 애트리뷰트 값들을 검색한다. SELECT * FROM EMPLOYEE WHERE Dno=5 ;

32 질의 (2): 'Research' 부서에서 일하는 모든 종업원들에 대하여 EMPLOYEE와 DEPARTMENT의 모든 애트리뷰트들을 검색한다.
SELECT * FROM EMPLOYEE, DEPARTMENT WHERE Dname='Research' AND Dno=Dnumber ;

33 7.4.4 SQL에서 집합으로서의 테이블 SQL에서는 질의의 결과로 중복된 튜플들이 나타날 수 있다. SQL 질의 결과에서 중복된 튜플들을 삭제하려면 SELECT 항목에서 키워드 DISTINCT를 사용해야 한다. 예) SELECT DISTINCT Salary FROM EMPLOYEE ;

34 집합 연산 : 합집합, 차집합, 교집합 SQL에서는 합집합(UNION)연산, 차집합(EXCEPT)연산, 교집합(INTERSECT)연산을 직접 구현하였다. 릴레이션에 대한 이들 관계연산의 결과는 투플들의 집합이다. 즉 중복된 투플을 결과에서 제거한다. UNION의 사용 예 : 일반 직원이든 프로젝트를 담당하는 부서의 관리자이든 간에 성이 'Smith'인 종업원을 포함하는 프로젝트에 대한 모든 프로젝트 번호의 목록을 작성하시오.

35 (SELECT DISTINCT Pnumber
FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE Dnum=Dnumber AND Mgr_ssn=Ssn AND Lname='Smith') UNION FROM PROJECT, WORKS_ON, EMPLOYEE WHERE Pnumber=Pno AND Essn=Ssn AND Lname='Smith') ;

36 7.4.5 부분 문자열 패턴 비교와 산술 연산자 SQL은 LIKE 비교 연산자를 사용하여 일부분의 문자열에 대해 비교조건을 적용할 수 있다. 부분 문자열을 표현할 때 사용하는 문자 중에서 ‘%'는 임의의 개수의 문자로 대체되고, '_'는 임의의 한 문자로 대체된다. 예) 주소가 Houston, Texas인 모든 사원의 이름과 성을 검색하시오. SELECT Fname, Lname FROM EMPLOYEE WHERE Address LIKE '%Houston,TX%’ ;

37 예) 1950년대에 테어난 모든 사원의 이름과 성을 검색하시오.
SELECT Fname, Lname FROM EMPLOYEE WHERE Bdate LIKE ‘__5_______’ ;

38 SQL은 질의 내에서 산술식을 허용한다. 질의 내에서 표준 산술 연산자 '＋', '－', '＊', '／'를 산술 값들에 적용할 수 있다
예) 'ProductX' 프로젝트에 참여하는 모든 종업원들의 봉급을 10％ 올린 경우의 봉급액수를 구하시오. SELECT Fname, Lname, 1.1*Salary AS Inc_sal FROM EMPLOYEE, WORKS_ON, PROJECT WHERE Ssn=Essn AND Pno=Pnumber AND Pname='ProductX’ ;

39 BETWEEN 연산자 예) 급여가 30,000달러에서 40,000달러 사이에 있는 5번 부서의 모든 사원을 검색하라 SELECT * FROM EMPLOYEE WHERE (Salary BETWEEN AND 40000) AND Dno=5 ;

40 7.4.6 질의 결과의 정렬 ORDER BY: 질의의 결과 튜플들을 정렬
예) 프로젝트에 참여하는 종업원을 부서의 알파벳 순서대로, 그리고 각 부서 내에서는 성과 이름의 알파벳 순서대로 검색 SELECT Dname, Lname, Fname, Pname FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT WHERE Dnumber=Dno AND Ssn=Essn AND Pno=Pnumber ORDER BY Dname, Lname ASC, Fname DESC ;