데이터베이스 과제#6

제출되어야 할 내용
▣ 각 문제 (총4문제)에 대한 결과물
▣ 자신의 과제 수행 결과에 대한 자체 평가 (숙제 맨 마지막 장을 할당하여 작성할 것)
◈ 완성여부 완성 인 경우 “완성”이라고 적음
◈ 미완성인 경우 “미완성”이라고 적음 (어떤 부분이 미완성인지 기술)
◈ 과제를 하면서 느낀 점 (어떠한 내용이어도 상관없음)

---

개념적 데이터 모델링 : ER 다이어그램
▣ 다음과 같은 ER 다이어그램이 주어진다.

---

논리적 데이터 모델링

문제 1. 앞장에서 정의된 ER다이어그램을 관계형 데이터베이스 테이블로 변환하고자 한다. 이를 위한 테이블 생성문을 SQL문으로 작성하라.

◈ 주의사항)
해당 결과는 오라클 데이터베이스에서 SQL문을 사용하여 작성하도록 한다.
무결성 제약조건도 모두 고려하여 SQL문에 작성되어야 한다.
무결성 제약조건은 ER다이어그램에 나타난 내용을 토대로 하되,
ER다이어그램상에 나타나 있지 않더라도 속성,이름 등을 통해 제약조건이
필요하다고 판단되는 경우, 제약조건을 설정하여 사용할 수 있음

◈ 결과물)
SQL로 이루어진 테이블 생성문(CREATE문)
CREATE TABLE BOOK (
BOOK_NUMBER NUMBER(6),
BOOK_NAME CHAR(30) NOT NULL,
BOOK_AUTHOR CHAR(10) NOT NULL,
BOOK_PRICE NUMBER(6),
PRIMARY KEY(BOOK_NUMBER)
);

CREATE TABLE CUSTOMER (
CUSTOMER_NUMBER NUMBER(6),
CUSTOMER_NAME CHAR(10),
CUSTOMER_PHONE_NUMBER VARCHAR(30),
PRIMARY KEY (CUSTOMER_NUMBER)
);

CREATE TABLE ORDERS (
B_NUMBER NUMBER(6),
C_NUMBER NUMBER(6),
ORDER_DATA DATE NOT NULL,
PRIMARY KEY(B_NUMBER,C_NUMBER),
FOREIGN KEY(B_NUMBER) REFERENCES BOOK(BOOK_NUMBER) ON DELETE CASCADE,
FOREIGN KEY(C_NUMBER) REFERENCES CUSTOMER(CUSTOMER_NUMBER) ON DELETE CASCADE
);

---

데이터베이스 구축
▣ 문제2. 문제1로부터 작성된 테이블에 임의의 데이터를 입력하고자 한다. 이를 위한 SQL문과 입력 결과를 보여라.
◈ 주의사항)
해당 결과는 오라클 데이터베이스에서 SQL문을 사용하여 작성하도록 한다.
입력된 튜플은 데이터 검색시 결과가 (최소1행이상) 나올 수 있도록 작성해야 한다. ◈결과물)
입력을 위해 사용한 SQL문
테이블당 입력된 데이터를 오라클 데이터베이스에서 디스플레이한 결과

INSERT INTO BOOK VALUES(1,'데이터베이스 개론','해윤',14500);
INSERT INTO BOOK VALUES(2,'지금 이대로 좋다','법륜',14800);
INSERT INTO BOOK VALUES(3,'여행의 이유','김영하',13500);
INSERT INTO BOOK VALUES(4,'일의 기쁨과 슬픔','장류진',14000);
INSERT INTO BOOK VALUES(5,'넛지','리처드',18000);
INSERT INTO BOOK VALUES(6,'심신 단련','이슬아',15000);
INSERT INTO BOOK VALUES(7,'호텔 창문','편혜영',12000);
INSERT INTO BOOK VALUES(8,'공부머리 독서법','최승필',16500);
INSERT INTO BOOK VALUES(9,'방랑자들','올가',16000);
INSERT INTO BOOK VALUES(10,'5년 후 나에게','포터',16800);

insert into customer values(1,'이진서','010-0000-9249');
insert into customer values(2,'권민준','010-1233-1249');
insert into customer values(3,'김서준','010-1992-1249');
insert into customer values(4,'박예준','010-1924-1827');
insert into customer values(5,'김도윤','010-1295-8157');
insert into customer values(6,'김시우','010-1289-9584');
insert into customer values(7,'고주원','010-1258-9864');
insert into customer values(8,'이지후','010-5984-8359');
insert into customer values(9,'신준서','010-5053-4279');
insert into customer values(10,'신준우','010-1285-2539');

insert into orders values(4,2,'20121102');
insert into orders values(6,3,'20141105');
insert into orders values(6,1,'20111225');
insert into orders values(2,3,'20131226');
insert into orders values(1,1,'20141127');
insert into orders values(2,4,'20130402');
insert into orders values(3,6,'20140111');
insert into orders values(5,7,'20150515');
insert into orders values(1,2,'20120214');
insert into orders values(7,4,'20110603');
insert into orders values(8,8,'20110302');
insert into orders values(9,3,'20130601');
insert into orders values(3,4,'20120514');

---

데이터 검색(1)

문제 3. 문제2에서 입력된 데이터로부터, 다음과 같은 검색 질의(문제3-1~ 3-7)를 SQL문을 이용해 수행하라.

◈ 주의사항)
해당 결과는 오라클 데이터베이스에서 SQL문을 사용하여 작성하도록 한다.

◈결과물)
(각 문제당) 사용한SQL문
(각 문제당 사용한 SQL문으로부터)
검색된 결과 :오라클 데이터베이스상에서 검색되어 디스플레이된 결과를 보여야 함

---

문제 3-1. 도서이름이 ‘데이터베이스 개론’인 도서의 저자명을 검색하라.

SELECT book_author
FROM BOOK
WHERE BOOK_NAME='데이터베이스 개론';

---

문제 3-2. 고객의 이름이 '김'씨로 시작하는 고객의 고객번호와 고객이름을 검색하라.

SELECT CUSTOMER_NUMBER,CUSTOMER_NAME
FROM CUSTOMER
WHERE CUSTOMER_NAME LIKE '김%';

---

문제 3-3. 주문된 도서의 도서이름과 주문일자를 검색하라.
SELECT BOOK_NAME,ORDER_DATA
FROM BOOK,ORDERS
WHERE BOOK.BOOK_NUMBER = ORDERS.B_NUMBER;

---

문제 3-4. 주문되지 않은 도서의 도서번호를 검색하라.

SELECT BOOK_NUMBER
FROM BOOK
MINUS
SELECT BOOK_NUMBER
FROM BOOK,ORDERS
WHERE BOOK.BOOK_NUMBER = ORDERS.B_NUMBER;

---

문제 3-5. 도서를 주문한 고객의 고객번호와 고객이름을 고객이름 순으로 검색하라.
(이때 동일한 고객번호가 중복되어서 나타나지 않도록 한다.)

SELECT DISTINCT C_NUMBER,CUSTOMER_NAME
FROM CUSTOMER,ORDERS
WHERE C_NUMBER = customer.customer_number
ORDER BY customer.customer_name;

---

문제 3-6. 도서의 가격이 2만원 이상 인 도서를 주문한 고객의 전화번호와 도서이름을 검색하라. (주의사항: 부속질의를 사용할 것 )

SELECT CUSTOMER_PHONE_NUMBER, BOOK_NAME
FROM (SELECT C_NUMBER, BOOK_NAME
FROM BOOK, ORDERS
WHERE BOOK.BOOK_NUMBER = ORDERS.B_NUMBER AND BOOK_PRICE >=17500)
ORDERSBOOK,CUSTOMER
WHERE CUSTOMER.CUSTOMER_NUMBER = ORDERSBOOK.C_NUMBER;

제가 INCERT한 내용 안에는 2만원 이상인 BOOK이 존재하지 않아서,
제일 큰 금액인 17500원을 기준으로 작성하였습니다.
기능은 정상 작동한다는 걸 보여주기 위해 17500원을 기준으로 문제풀이 하였습니다.

---

문제 3-7. 2개 이상의 도서를 주문한 고객의 고객번호에 대해, 고객번호와 해당 고객이 주문한 도서의 총 개수를 검색하라.

select C_number, count(b_number)
from orders
group by c_number
having count(b_number)>=2
order by C_number;

---

데이터 무결성 제약조건

문제 4. 작성된 테이블의 데이터를 변경하고자 한다. 이때, 무결성 제약조건에 위배되도록 데이터를 변경하고, 해당 결과를 출력하라. 또한, 해당 오류가 발생한 이유를 설명하라.

◈ 주의사항)
위배되는 제약조건 종류가 모두 다르도록 구성하여, 2개 이상 제시해야 함

◈ 결과물)
(각 변경 당) 사용한SQL문들
(각 변경 당 사용한 SQL문으로부터) 무 결성 제약조건이 위배 된 결과 :
오라클 데이터베이스 상에서 발생된 오류가 디스플레이 된 결과를 보여야함
(각 변경 당 사용한 SQL문으로부터) 해당 무 결성 제약조건에 위배된 이유 설명

BOOK_NAME에 NOT NULL을 설정하여서 NULL값을 넣을 수 없기 때문에
개체 무 결성 제약조건에 위배 되어 실행되지 않습니다.

전화번호를 뜻하는 customer_phone_number에는 전화번호에 해당하는 값이 들어가 있어야하지만 위의 경우 ‘전화번호’라는 도메인의 맞지 않는 값을 넣을려고 해서 실행되지 않습니다. 위와 같은 경우는 도메인 무 결성 제약조건에 위배되어 실행되지 않습니다.

완성여부 : 완성
느낀 점 : CREATE문을 사용할 때 주문 TABLE을 생성하려고 ORDER을 테이블 명으로 생성하려하였으나, ORDER은 테이블 명으로 사용할 수 없었습니다.

DISTINCT를 사용할 때, DISTINCT를 먼저 사용하지 않고 뒤에 사용할 수 없었습니다.
SELECT DISTINCT C_NUMBER, CUSTOMER_NAME 은 가능하지만,
SELECT CUSTOMER_NAME, DISTINCT C_NUMBER 는 불가능하다는 걸 알 수 있었습니다.

FROM에 부속질의를 사용 할 경우 동적으로 테이블을 생성하여서 그 순간에만 사용할 수 있다는 것을 문제3.6을 풀이하면서 알 수 있었습니다.

order by 문을 사용하면서 asc는 내림차순, desc는 오름차순을 알게 되었습니다.