Summary - 무결성 제약 조건의 특성, QUERY 연습

 

무결성 제약 조건의 특성

·         제약조건은 데이터의 적정성과 무결성을 확보하기 위한 조건들을 의미한다.

·         모든 제약조건들은 데이터 사전(data dictionary)에 저장된다.

·         부적절한 데이터 입력방지를 위한 NOT NULL의 제약조건은 반드시 열(컬럼) 레벨에서만 정의가 가능하다.

·         NOT NUll, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK 등이 대표적인 제약조건의 유형들이다.

 

https://codedragon.tistory.com/4272

 

 

 

 

기본키(Primary key)

·         기본키 제약조건은 일반적으로 테이블의 각 행을 고유하게 식별하는 값을 가진 열 또는 열 조합을 말한다. ·         기본키 제약 조건에 포함되는 열에는 Null 값을 사용할 수 없다. ·         기본키 제약 조건을 지정하면 데이터베이스는 해당 열에 대해 고유 인덱스를 만들어 데이터 고유성을 적용한다. ·         쿼리에서 기본키 제약 조건을 사용한 열을 사용하는 경우 이 인덱스를 사용하여 빠르게 액세스할 수 있다.

https://codedragon.tistory.com/4534

https://codedragon.tistory.com/8065

 

 

 

 

외래키(FOREIGN KEY)

·         외래키 제약조건은 두 테이블의 데이터 간 연결을 설정하고 강제 적용하는 데 사용되는 열을 말한다. ·         외래키 테이블의 기본 키 값을 가지고 있는 열을 다른 테이블의 열이 참조할 때 두 테이블 간에 연결이 생성된다.  이때 두 번째 테이블에 추가되는 열은 이 제약 조건으로 생성된다. ·         외래키 제약 조건을 지정하면 데이터베이스는 해당 열에 대해 고유 인덱스를 만들어 데이터 고유성을 적용한다. ·          쿼리에서 이 제약 조건을 사용한 열을 사용하는 경우 이 인덱스를 사용하여 빠르게 액세스할 수 있다.

https://codedragon.tistory.com/4540

 

 

 

 

QUERY  연습 1

오브젝트 조회, 테이블 조회, 컬럼 정보 조회 , 인덱스 조회, 뷰 조회 쿼리문 예시입니다.

조회 정보	예제
오브젝트 조회	SELECT object_name, object_type FROM user_objects;
테이블 조회	SELECT table_name FROM user_tables; SELECT * FROM tab;
컬럼 정보 조회	SELECT column_name, data_type, data_length, data_precision, data_scale, nullable, data_default FROM user_tab_columns WHERE table_name = 'DEPT' ;
인덱스 조회	SELECT column_name, data_type, data_length, data_precision, data_scale, nullable, data_default FROM use_indexes WHERE table_name = 'DEPT' ;
뷰 조회	SELECT text FROM user_views where view_name='EMP_vw';

https://codedragon.tistory.com/8067

 

 

 

 

QUERY  연습 2

1.아래의 조건에 맞는 테이블을 설계하고 데이터를 조작하여 테스트하는 쿼리문을 작성하시오

 

 

인사관리에 필요한 테이블(employees)을 생성하기

컬럼	조건
사원번호 empno	PK
사원명 empname	Not Null
성별 gender	m 또는 w의 값만 허용
부서코드 deptno	부서테이블과 관계 설정
메일주소 email	중복불허
휴대폰번호 phone	중복불허
학력 school	코드(학력테이블)
주소 address	-

 

 

 

인사 기본테이블(employees)  구성된 테이블 생성

CREATE TABLE employees( empno NUMBER(4) PRIMARYKEY, empname VARCHAR2(10) NOT NULL, gender VARCHAR2(1) CONSTRAINT emp_gender_CK CHECK( gender IN('M', 'F') ), gender VARCHAR2(10) constraint check_gender check( gender IN('남성','여성') ), deptno VARCHAR2(10) NOT NULL FOREIGN KEY REFERENCES departments(deptno), email VARCHAR2(10) UNIQUE, phone NUMBER(4) UNIQUE, school VARCHAR2(10), address VARCHAR2(10) );

 

 

부서테이블(departments)  생성하기

컬럼	조건
부서코드 (deptno)	PK
부서명 (deptname)	not Null
지역 (region)	-
부서 전화번호 (depttel)	-

 

CREATE TABLE departments( deptno VARCHAR2(10) PRIMARYKEY, deptname VARCHAR2(10)NOT NULL, region VARCHAR2(10) depttel VARCHAR2(10) );

 

 

 

2. 두 테이블간의 관계도를 도식화하여 나타내시오

 

 

 

 

3. 인사기본테이블의 성별컬럼을 기준으로 B 트리 인덱스를 생성하려고 할 때 문제점을 설명하시오

사원정보 테이블에서 성별 컬럼의 데이터는 m과 w의 값만을 가질 수 있으며, 카디널리티가 낮은 컬럼을 대상으로 할 때는 bitmap 인덱스를 생성하는 것이 더 효율적이다.

 

 

 

 

 

4. 사원번호와 사원명, 부서명, 메일주소, 휴대폰번호의 컬럼을 갖는 View(empl_view)를 생성하시오

CREATE OR REPLACE VIEW empl_view AS SELECT emp.사원명, dept.부서명, emp.메일주소, emp.휴대폰번호 FROM employees emp, department dept;

 

 

 

 

QUERY  연습 3

제약조건(Constraints)은 DB에서 데이터의 적정성과 무결성을 확보하기 위한 조건들을 의미합니다. 모든 제약조건들은 원본 데이터와 따로 데이터 사전(Data dictionary)에도 저장되며, 보통 NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY< CHECK등이 대표적인 제약조건의 유형을 표시합니다.

ERD를 보고 Table을 생성하는 SQL 명령어 예시입니다.

 

테이블간의 관계(Relationship)

 

 

도서 테이블명: Book

컬럼명	제약조건
bookid	기본키(PK) 숫자(2)
bookname	가변문자열(40)
publisher	가변문자열(40)
price	숫자(8)

 

 

고객 테이블명: Customer

컬럼명	제약조건
custid	기본키(PK) 숫자(2)
name	가변문자열(40)
address	가변문자열(50)
phone	숫자(8)

 

 

주문 테이블명: Orders

컬럼명	제약조건
orderid	기본키(PK) 숫자(2)
custid	외래키(FK) 숫자(2)
bookid	외래키(FK) 숫자(2)
salesprice	숫자(8)
orderdate	날짜

 

CREATE TABLE Book( bookid NUMBER(2) PRIMARY KEY, bookname VARCHAR2(40), publisher VARCHAR2(40), price NUMBER(2) )     CREATE TABLE Customer( custid NUMBER(2) PRIMARY KEY, name VARCHAR2(40), address VARCHAR2(50), phone VARCHAR2(20) )   CREATE TABLE Order( orderid NUMBER(2) PRIMARY KEY, custid NUMBER(2) REFERENCES Customer(custid), bookid NUMBER(2) REFERENCES Book(bookid), saleprice NUMBER(8), orderdate DATE )