[SQL 활용] 퀴즈 3

비디오 대여점 운영에 필요한 5개의 테이블을 생성하시오.

※ 테이블명 뒤에 본인 이름의 약자를 모두 붙이시오. [ex. 테이블명_이름약자]

a. 관계 다이어그램 및 다음 테이블 인스턴스 차트를 기반으로 생성하시오.

b. 테이블 생성 시 제약조건을 추가하시오.

c. 제약조건 추가 시 제약조건 이름을 생략하지 말고 고유하게 부여하시오.

d. 테이블 생성 후 테이블 구조를 확인하시오.(캡처)

e. 데이터 딕셔너리를 참조하여 테이블의 제약조건이 제대로 생성되었는지 확인하시오.(캡처)

- 데이터딕셔너리명 : user_constraints

- 조회 컬럼명 : constraint_name, constraint_type, search_condition, r_constriant_name

※ 비디오 응용 프로그램 엔티티 관계 다이어그램 파악하기

[테이블1] MEMBER_OOO

Column Name	MEMBER _ID	LAST _NAME	FIRST _NAME	ADDRESS	CITYm	PHONEm	JOINm
Key Type	PK
Null/Unique		NN					NN
Default							sysdate
Data Type	NUMBER	VARCHAR2	VARCHAR2	VARCHAR2	VARCHAR2	VARCHAR2	DATE
Length	10	25	25	200	30	15

CREATE TABLE MEMBER_KSJ
(
    MEMBER_ID NUMBER(10) CONSTRAINT member_id_pk PRIMARY KEY,
    LAST_NAME VARCHAR2(25) CONSTRAINT last_name_nn NOT NULL,
    ADDRESS VARCHAR2(25),
    CITY VARCHAR2(30),
    PHONE VARCHAR2(15),
    JOIN_DATE DATE DEFAULT SYSDATE CONSTRAINT join_date_nn NOT NULL
);

1

DESC MEMBER_KSJ

cs

1 2 3

SELECT constraint_name, constraint_type, search_condition, r_constraint_name FROM user_constraints WHERE table_name = 'MEMBER_KSJ';

[테이블2] TITLE_OOO

Column Name	TITLE_ID	TITLE	DESCRIPTION	RATING	CATEGORY	RELEASE_ DATE
Key Type	PK
Null/Unique		NN	NN
Check				G, PG, R, NC17, NR	DRAMA, COMEDY, ACTION, CHILD, SCIFI, DOCUMENTARY
Data type	NUMBER	VARCHAR2	VARCHAR2	VARCHAR2	VARCHAR2	DATE
Length	10	60	400	4	20

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

CREATE TABLE TITLE_KSJ (     TITLE_ID NUMBER(10) CONSTRAINT title_id_pk PRIMARY KEY,     TITLE VARCHAR2(60) CONSTRAINT title_nn NOT NULL,     DESCRIPTION VARCHAR2(400) CONSTRAINT description_nn NOT NULL,     RATING VARCHAR2(4) CONSTRAINT rating_ck CHECK         (             RATING IN ( 'G', 'PG', 'R', 'NC17', 'NR' )         ),     CATEGORY VARCHAR2(2) CONSTRAINT category_ck CHECK         (             CATEGORY IN ( 'DRAMA', 'COMEDY', 'ACTION', 'CHILD', 'SCIFI', 'DOCUMENTARY' )         ),     RELEASE_DATE DATE );

1

DESC TITLE_KSJ

cs

1 2 3

SELECT constraint_name, constraint_type, search_condition, r_constraint_name FROM user_constraints WHERE table_name = 'TITLE_KSJ';

[테이블3] TITLE_COPY_OOO

Column Name	COPY_ID	TITLE_ID	STATUS
Key Type	PK	PK, FK	NN
Null/Unique			NN
Check			AVAILABLE, DESTROYED, RENTED, RESERVED
FK Table		TITLE_JJY
FK Column		TITLE_ID
Data type	NUMBER	NUMBER	VARCHAR2
Length	10	10	15

1 2 3 4 5 6 7 8 9 10 11 12 13 14

CREATE TABLE TITLE_COPY_KSJ (     COPY_IN NUMBER(10),     TITLE_ID NUMBER(10) CONSTRAINT title_id_fk REFERENCES TITLE_KSJ(TITLE_ID),     STATUS VARCHAR2(15) CONSTRAINT status_nn NOT NULL                         CONSTRAINT status_ck CHECK         (             STATUS IN ( 'AVAILABLE',                         'DESTROYED',                         'RENTED',                         'RESERVED')         ),     CONSTRAINT copy_id_title_id_pk PRIMARY KEY (COPY_ID, TITLE_ID) ); Colored by Color Scripter

cs

1

DESC TITLE_COPY_KSJ

cs

1 2 3

SELECT constraint_name, constraint_type, search_condition, r_constraint_name FROM user_constraints WHERE table_name = 'TITLE_COPY_KSJ';

[테이블4] RENTAL_OOO

Column Na​me	BOOK_DATE	MEMBER_ID	COPY_ID	ACT_RET_DATE	EXP_RET_DATE	TITLE_ID
Key Type	PK	PK, FK1	PK, FK2			PK, FK2
Default	sysdate				sysdate +2days
FK Table		MEMBER_JJY	TITLE_COPY_JJY			TITLE_COPY_JJY
FK Column		MEMBER_ID	COPY_ID			TITLE_ID
Data type	DATE	NUMBER	NUMBER	DATE	DATE	NUMBER
Length		10	10			10

1 2 3 4 5 6 7 8 9 10 11 12 13 14

CREATE TABLE RENTAL_KSJ (     BOOK_DATE DATE DEFAULT SYSDATE,     MEMBER_ID NUMBER(10),     COPY_ID NUMBER(10),     ACT_RET_DATE DATE,     EXP_RET_DATE DATE DEFAULT SYSDATE+2,     TITLE_ID NUMBER(10),     CONSTRAINT rental_ksj_pk PRIMARY KEY (BOOK_DATE, MEMBER_ID, COPY_ID, TITLE_ID),     CONSTRAINT rental_ksj_fk1 FOREIGN KEY (MEMBER_ID)         REFERENCES MEMBER_KSJ (MEMBER_ID),     CONSTRAINT rental_ksj_fk2 FOREIGN KEY (COPY_ID, TITLE_ID)         REFERENCES TITLE_COPY_KSJ (COPY_ID, TITLE_ID) );

1

DESC RENTAL_KSJ

cs

1 2 3

SELECT constraint_name, constraint_type, search_condition, r_constraint_name FROM user_constraints WHERE table_name = 'RENTAL_KSJ';

[테이블5] RESERVATION_OOO

Column Name	RES_DATE	MEMBER_ID	TITLE_ID
Key Type	PK	PK, FK1	PK, FK2
Default
FK Table		MEMBER_JJY	TITLE_JJY
FK Column		MEMBER_ID	TITLE_ID
Data type	DATE	NUMBER	NUMBER
Length		10	10

1 2 3 4 5 6 7 8 9 10 11

CREATE TABLE RESERVATION_KSJ (     RES_DATE DATE,     MEMBER_ID NUMBER(10),     TITLE_ID NUMBER(10),     CONSTRAINT reservation_pk PRIMARY KEY (RES_DATE, MEMBER_ID, TITLE_ID),     CONSTRAINT member_id_fk1 FOREIGN KEY (MEMBER_ID)         REFERENCES MEMBER_KSJ (MEMBER_ID),     CONSTRAINT title_id_fk2 FOREIGN KEY (TITLE_ID)         REFERENCES TITLE_KSJ (TITLE_ID) ); Colored by Color Scripter

cs

1

DESC RESERVATION_KSJ

cs

1 2 3

SELECT constraint_name, constraint_type, search_condition, r_constraint_name FROM user_constraints WHERE table_name = 'RESERVATION_KSJ';