한 주를 마무리 하는 신명나는 평가!!
3시간 동안 배운 걸 평가하자는 것도 웃기지만, 그래도 나름 열심히 준비해서 답안 작성을 했다.
그래도 뭔가 아쉽다😩
아직 결과는 안 나옴... 주말이라
무튼 이번에도 서술형, 문제해결 유형이 나왔다.
서술형
1. 데이터베이스 관리자 계정과 사용자 계정에 대해 서술하시오.
DBMS는 기존의 파일시스템의 보안성을 극복하기 위해서 계정이라는 객체를 만들어냈다.
이 중 관리자 계정은 DBMS 전체의 시스템 계정으로서 데이터를 관리하는 용도가 아닌, 내부의 데이터 관리하는 사용자 계정을 생성, 삭제, 수정 / 동시에 해당 계정에 대한 권한을 부여하는 용도로 쓰인다.
주로 create와 grant 명령어를 사용해서 사용자 계정을 생성하고 권한을 부여한다.
이와 달리 사용자 계정은 실질적으로 관리자 계정 내부에 존재한다. 관리자 계정이 사용자 계정은 생성하면, 사용자 계정 객체는 자신만의 특정한 영역을 가지고
거기에 테이블들을 생성하여 실 데이터를 관리, 조작하는 용도로 사용된다.
2. 제약조건 중 PRIMARY KEY, FOREIGN KEY, CHECK 에 대하여 서술하시오
제약 조건은 들어오는 데이터를 통제하기 위한 수단으로 사용된다.
1) PRIMARY KEY(주 키 설정), 식별자 데이터와 같은 중복과 공백이 허용되지 않는 컬럼에 적용된다.
따라서 그 특징은 다음과 같다.
- 공백과 중복데이터의 입력이 허용되지 않음
- 컬럼 레벨의 경우, 단 하나의 컬럼에만 적용할 수 있다.
- 테이블 레벨의 경우, 여러 컬럼을 적용할 수 있다.
사용법
create table admin(
id number primary key -- 컬럼 레벨
);
create table admin(
id number,
index number,
primary key (id, index) -- 테이블 레벨
); 이런 primary key는 unique not null 과 큰 차이는 없지만, 그 자체가 식별자 데이터를 나타낸다는 점이 중요하다.
2) FOREIGN KEY(외래키)
외래키는 데이터의 무결성을 보완하기 위한 수단으로서 사용된다.
먼저 데이터의 무결성은 DBMS의 특징 중 하나로 특정 양식과 조건을 통해 잘못된 데이터가 들어오지 못하도록 막는 것이다.
예를 들어, 동일 데이터를 개별 테이블로 운용하게 된다면 잘못된 데이터가 입력될 가능성이 있다.
따라서 외래키를 통해 특정 테이블의 컬럼값을 끌어옴으로서 다른 데이터가 입력되지 못하도록 한다.
이런 참조관계로 인해 자바의 상속과 유사하게 부모 키(참조 대상)-자식 키(참조 주체)의 관계가 형성된다. (자식 키가 부모 키의 데이터를 끌어오는 것)
이런 외래키의 특징은 다음과 같다.
- 컬럼명 references 테이블명(참조 컬럼명) 의 문법을 사용한다.
- 부모 키의 경우 제약조건이 primary key / unique 로 설정되어 있어야 한다. (검증된 데이터 사용)
- 참조를 하는 시점에서 부모 키가 존재하는 테이블로 넘어가서 데이터를 끌어온다.
사용법
create table admin(
id number primary key -- 부모 키
);
create table article(
id references admin(id) -- 자식 키
);다만 한 가지 문제점은 부모 키의 데이터를 삭제할 때이다.
이 경우는 부모 키를 삭제해도 자식 키의 해당 데이터들이 남아있기 때문에 데이터의 무결성이 깨지기 때문이다.
따라서 외래키로 설정된 데이터를 삭제하는 방법은 3가지가 있다.
- 외래키 관계를 삭제 : 자식 키가 있는 테이블 삭제 후 재생성하면서 외래키 관계를 없앤다.
- 자식 키를 먼저 삭제 후, 부모 키를 삭제 : 위 원인처럼, 자식 키가 삭제되면 해당 계정 내부에는 같은 데이터가 없기 때문에 부모 키를 삭제할 수 있다.
- 외래키 설정 시 삭제 옵션을 부여 : on delete 라는 제약조건을 걸어주며, 부모 키 삭제 시 일어날 동작을 설정해준다.
1) cascade : 부모 키 삭제시 외래키 관게에 있는 모든 자식 키들도 함께 삭제된다.
create table article(
id references admin(id) on delete cascade
);2) set null : 부모 키 삭제시 외래키 관게에 있는 모든 자식 키들의 값을 null으로 바꿔준다.
create table article(
id references admin(id) on delete set null
);3) CHECK
CHECK역시 컬럼 안에 들어갈 값을 통제하는 용도로 사용되는데, 구체적으로 컬럼에 들어갈 데이터 양식을 미리 조건으로 걸어둠으로서 해당 양식에 맞는 데이터만 입력할 수 있게 하는 것이다.
사용법
create table menu(
iced vachar(1) check (iced in ('y', 'n'))
);이렇게 설정을 해두면 해당 컬럼에 값을 입력 시 y, n 중 하나만 입력할 수 있다.
3. 데이터 오브젝트를 정의하기 위한 DDL(Data Definition Language)에 대해 서술하시오.
DDL은 DBMS 내부의 모든 객체를 생성, 수정, 삭제하는 명령어들을 의미한다.
크게 create(생성), alter(수정), drop(삭제)의 명령어가 여기에 포함된다.
문제해결
[ 문제 ]
계정 생성 및 테이블 생성 SQL문을 분석하여 원인과 조치내용(조치 방법 + 수정된 SQL)을 작성하시오
요구사항
- 관리자 계정으로 접속하여 계정명 'test01', 비밀번호 '1234' 의 사용자 계정 생성
- test01 계정에 DB 접속 및 객체 생성 권한 부여
- 생성한 사용자 계정으로 접속하여 MEMBERS, BOARDS 테이블 생성
- MEMBERS 테이블
1) MEMBER_ID 컬럼에 PRIMARY KEY 제약조건 설정.
2) MEMBER_PWD 컬럼에 NOT NULL 제약조건 설정. - BOARDS 테이블
1) BOARD_NO 컬럼에 PRIMARY KEY 제약조건 설정.
2) BOARD_WRITER 컬럼에 MEMERS 테이블의 MEMBER_ID를 참조하는 FOREIGN KEY 제약조건 설정.
3) BOARD_REG_DATE(작성일) 컬럼에 기본값 SYSDATE 설정 [ 작성한 SQL문 ]
-- 사용자 계정 생성
CREATE test01 IDENTIFIED BY 1234;
GRANT RESOURCE TO test01;
-- MEMBERS 테이블 생성
CREATE TABLE MEMBERS(
MEMBER_ID VARCHAR2(20),
MEMBER_PWD VARCHAR2(20),
MEMBER_NAME VARCHAR2(30),
MEMBER_AGE NUMBER,
MEMBER_EMAIL VARCHAR2(50),
CONSTRAINT PRIMARY KEY(MEMBER_ID),
NOT NULL(MEMBER_PWD)
);
-- BOARDS 테이블 생성
CREATE TABLE BOARDS(
BOARD_NO NUMBER PRIMARY KEY,
BOARD_TITLE VARCHAR2(100),
BOARD_CONTENT VARCHAR2(4000),
BOARD_WRITER VARCHAR2(20) FOREIGN KEY MEMBERS(MEMBER_ID),
BOARD_REG_DATE DATE DEFAULT SYSDATE
);원인
1. 사용자 계정 생성 : CREATE test01 IDENTIFIED BY 1234;
create 명령어는 항상 뒤에 객체 유형이 와줘야하는데, 누락되었다.2. 접속 권한 미부여 : GRANT RESOURCE TO test01;
자원권리 권한만 부여하고, 접속 권한은 부여하지 않았다.3. PRIMARY KEY 설정 오류 : CONSTRAINT PRIMARY KEY(MEMBER_ID)
의도는 테이블 레벨로 작성하고자 했지만, 잘못 작성하였다.
이 경우 데이터타입을 인식하지 못하여 에러가 발생한다.4. 제약 조건 입력 오류 : NOT NULL(MEMBER_PWD)
제약 조건의 기본 형식은 { 컬럼명 + 자료형 + 제약조건 }이다.
웬만한 제약조건은 컬럼 선언과 함께 따라온다. 따라서 not null을 개별 작성하면 안된다. 5. 외래키 설정 오류 : BOARD_WRITER VARCHAR2(20) FOREIGN KEY MEMBERS(MEMBER_ID)
외래키를 설정하는 명령어를 잘못 입력하였다.해결
1. 사용자 계정 문법 교정
CREATE test01 IDENTIFIED BY 1234; (X)
CREATE user test01 IDENTIFIED BY 1234; (O)
-- create 뒤에, 계정 객체인 user을 넣어줌으로서 계정 생성2. 접속 권한 추가 부여
GRANT RESOURCE TO test01; (X)
GRANT RESOURCE, connect TO test01; (O)
--resource는 자원관리 권한이다. 따라서 DB접속 권한인 connect를 추가해줬다.3. CONSTRAINT PRIMARY KEY(MEMBER_ID)
해당 주 키 설정은 두 가지 방식으로 가능하다.
1) MEMBER_ID VARCHAR2(20) PRIMARY KEY
2) PRIMARY KEY(MEMBER_ID)만약 식별자 강조를 강하게 사용할 것이라면 1번을 선택하여, 컬럼 레벨로 사용하는 것이 좋고
테이블 레벨로 추가적인 PRIMARY KEY를 설정할 가능성이 있다면 2번을 선택하면 된다.
4. 제약 조건 재입력
--제약_ 조건의 기본 형식인 { 컬럼명 + 자료형 + 제약조건 }을 따라
--기존 NOT NULL(MEMBER_PWD)을 삭제 후, 수정한다.
MEMBER_PWD VARCHAR2(20) not null (O)5. references 로 외래키 설정
-- 외래키 설정시 사용하는 명령어는 references이다.
BOARD_WRITER VARCHAR2(20) references MEMBERS(MEMBER_ID) 