[DB]_23.02.13

연습문제 4번 풀이

🔽 문제

• 4-1) 새로운 도서 ('스포츠 세계', '대한미디어', 10000원)이 마당서점에 입고되었다. 삽입이 안 될 경우 필요한 데이터가 더 있는지 찾아보시오

INSERT INTO BOOK(BOOKNAME, PUBLISHER, PRICE)
VALUES('스포츠 세계', '대한미디어', '10000원');

• 4-2) '삼성당'에서 출판한 도서를 삭제하시오.

DELETE FROM BOOK WHERE PUBLISHER='삼성당';

• 4-3) '이상미디어'에서 출판한 도서를 삭제하시오, 삭제가 안 되면 원인을 생각해 보시오.
  • 다른 테이블에서 PUBLISHER를 참조하고 있을 것으로 예상(참조 무결성 제약조건이 위배)

DELETE FROM BOOK WHERE PUBLISHER='이상미디어';

delete from orders where orders.bookid in(select bookid from book where publisher = '이상미디어) ; 

• 4-4) 출판사 '대한미디어'를 '대한출판사'로 이름을 바꾸시오

UPDATE BOOK
SET PUBLISHER='대한출판사'
WHERE PUBLISHER='대한미디어';

• 4-5) (테이블 생성) 출판사에 대한 정보를 저장하는 테이블 Bookcompany(name, address,
  ,begin)를 생성하고자 한다. name은 기본키이며 VARCHAR(20), address는 VARCHAR(20),
  begin은 DATE 타입으로 선언하여 생성하시오.

CREATE TABLE Bookcompany (
	name VARCHAR(20) PRIMARY KEY,
	address VARCHAR(20),
	begin DATE);

• 4-6) (테이블 수정) Bookcompany 테이블에 인터넷 주소를 저장하는 webaddress 속성을 VARCHAR(30)으로 추가하시오

ALTER TABLE Bookcompany
ADD webaddress VARCHAR(30);

• 4-7) Bookcompany 테이블에 임의의 투플 name=한빛아카데미, address=서울시 마포구,begin=1993-01-01, webaddress=http://hanbit.co.kr을 삽입하시오.

INSERT INTO Bookcompany
(name, address, begin, webaddress)
VALUES 
('한빛아카데미', '서울시 마포구', 
'1993-01-01', 'http://hanbit.co.kr');

연습문제 11번 풀이

🔽 문제

• create table 하고 insert into로 튜플 내 값 넣고, 제약조건 check로 수행한 후 10개 쿼리문 작성

DROP TABLE THEATER;
DROP TABLE USERS;
DROP TABLE CINEMA;

-- 테이블 생성 : 극장 (theater)

CREATE TABLE theater (
    theaterNum NUMBER PRIMARY KEY,
    theaterName VARCHAR(20),
    theaterLocation VARCHAR(20)
);

-- 튜플 내 값 생성
INSERT INTO theater(theaterNum, theaterName, theaterLocation)
VALUES (1, '롯데', '잠실');
INSERT INTO theater(theaterNum, theaterName, theaterLocation)
VALUES(2, '메가', '강남');
INSERT INTO theater(theaterNum, theaterName, theaterLocation)
VALUES (3, '대한', '잠실');

-- 테이블 생성 : 고객 (Users)
CREATE TABLE users (
userNum INTEGER PRIMARY KEY,
userName VARCHAR(20),
userAddress VARCHAR(20)
);

-- 튜플 내 값 생성
INSERT INTO users(userNum, userName, userAddress)
VALUES (3, '홍길동', '강남');
INSERT INTO users(userNum, userName, userAddress)
VALUES (4, '김철수', '잠실');
INSERT INTO users(userNum, userName, userAddress)
VALUES (9, '박영희', '강남');

-- 테이블 생성 : 상영관 (Cinema)
CREATE TABLE CINEMA (
theaterNum NUMBER REFERENCES THEATER(theaterNum),
cinemaNum NUMBER CHECK(cinemaNum >= 1 AND cinemaNum <= 10) PRIMARY KEY,
movieName VARCHAR(20),
price NUMBER CHECK (price <= 20000),
seat NUMBER
);

-- 튜플 내 값 생성
INSERT INTO cinema(theaterNum, cinemaNum, movieName, price, seat)
VALUES (1, 1, '어려운 영화', 15000, 48);
INSERT INTO cinema(theaterNum, cinemaNum, movieName, price, seat)
VALUES(3, 1, '멋진 영화', 7500, 120);
INSERT INTO cinema(theaterNum, cinemaNum, movieName, price, seat)
VALUES(3, 2, '재밌는 영화', 8000, 110);

-- 테이블 생성 : 예약 (reservation)
CREATE TABLE reservation (
theaterNum INTEGER NOT NULL REFERENCES theater(theaterNum),
cinemaNum INTEGER NOT NULL REFERENCES cinema (cinemaNum),
userNum INTEGER NOT NULL REFERENCES users (userNum),
seatNum INTEGER,
MOVIE_date DATE,
PRIMARY KEY(theaterNum, cinemaNum, userNum)
);

-- 튜플 내 값 생성
INSERT INTO reservation (theaterNum,cinemaNum, userNum, seatNum, MOVIE_date)
VALUES (3, 2, 3, 15, '2020-09-01');
INSERT INTO reservation (theaterNum,cinemaNum, userNum, seatNum, MOVIE_date)
VALUES(3, 1, 4, 16, '2020-09-01');
INSERT INTO reservation (theaterNum,cinemaNum, userNum, seatNum, MOVIE_date)
VALUES (1, 1, 9, 48, '2020-09-01');

SELECT * FROM reservation;

11-1-1)

SELECT 극장이름, 위치 
FROM 극장; 

11-1-2)

SELECT 극장이름
FROM 극장
WHERE 위치 = '극장'; 

11-1-3)

SELECT 이름
FROM 고객 
WHERE 주소 = '잠실'
ORDER BY 이름; 

11-1-4)

SELECT 극장번호, 상영관번호, 영화제목
FROM 상영관 
WHERE 가격 <= 8000; 

11-1-5)

SELECT DISTINCT 극장.위치 , 고객.주소 
FROM 극장, 고객   
WHERE 극장.위치 = 고객.주소;  

11-2-1)

SELECT COUNT(*)
FROM 극장;

11-2-2)

SELECT AVG(가격) 
FROM 상영관; 

11-2-3)

SELECT COUNT(고객번호)
FROM 예약 
WHERE 날짜 = '2020-09-01'; 

SELECT COUNT(이름) 
FROM 고객 JOIN 예약 ON (예약.고객번호=고객.고객번호) 
WHERE 날짜 = TO_DATE('2020-09-01', 'YYYY-MM-DD'); 

11-4-1)

SELECT 극장.극장이름, COUNT(상영관번호)
FROM 극장, 상영관
WHERE 극장.극장번호 = 상영관.극장번호
GROUP BY 극장이름;

11-4-2)

 SELECT DISTINCT 극장이름, 상영관번호
FROM 극장, 상영관
WHERE 극장.극장번호 = 상영관.극장번호
AND 위치 = '잠실';

<1장 연습문제 풀이>

1. 4번
2. 3번
3. 2번 (풀이: 일반 사용자가 읽는 것까지는 되지만, 직접 갱신이 x(쓰기 권한 없음))
4. 2번 (풀이: 네부 레코드의 물리적 순서는 내부 스키마 담당)
5. 1번
6. 1번 (풀이 dml이 아니라 ddl)
7. 2번
8. 1번 (풀이: 동일 수준의 보안이 불가능-> 여러 군데 데이터 분산 돼 보안 관리 어려움)
9. -데이터베이스 : 조직에 필요한 정보를 얻기 위해 논리적으로 연관된 데이터를 모아 구조적으로 통합해 놓은 것
   -DBMS : 주기억장치에 저장된 소프트웨어, 사용자와 데이터베이스를 연결시켜주는 소프트웨어
   -데이터 모델 : 눈에 보이지 않는 논리적인 개념으로 데이터가 저장되는 기법
   -데이터 베이스 시스템 : 데이터 베이스, 데이터베이스 관리시스템(DBMS), 데이터 모델 3가지로 구성
10. -파일 시스템: 응용 프로그램이 파일에 직접 접근
    -DBMS: 응용 프로그램이 DBMS에 파일 접근을 요청

정규화

이상현상

• 잘못 설계된 데이터베이스가 어떤 이상현상(anomaly)을 일으키는지 알아보기
  •  삭제이상(deletion anomly)
    • 투플 삭제 시 같이 저장된 다른 정보까지 연쇄적으로 삭제되는 현상
    • 연쇄삭제(triggered deletion) 문제 발생
  •  삽입이상(insertion anomly)
    • 투플 삽입 시 특정 속성에 해당하는 값이 없어 NULL 값을 입력해야 하는 현상
  • → NULL 값 문제 발생
  •  수정이상(update anomly)
    • 투플 수정 시 중복된 데이터의 일부만 수정되어 데이터의 불일치 문제가 일어나는 형상
    • 불일치 문제 발생
• Summer 테이블을 생성하고 데이터를 삽입하는 SQL 문

DROP TABLE SUMMER; 
CREATE TABLE SUMMER(
SID NUMBER, 
CLASS VARCHAR2(20) 
PRICE NUMBER
); 

INSERT INTO SUMMER VALUES(100,'FORTRAN', 20000); 
INSERT INTO SUMMER VALUES(150, 'PASCAL', 15000);
INSERT INTO SUMMER VALUES(200, 'C', 10000);
INSERT INTO SUMMER VALUES(250, 'FORTRAN', 20000); 

SELECT * 
FROM SUMMER; 

🔽

• 질의 7-1) 200번 학생의 계잘학기 수강신청을 취소하시오.

-- C강좌 수강료 조회 
SELECT PRICE "C 수강료" 
FROM SUMMER
WHERE CLASS='C'; 
-- 200번 학생의 수강신청 취소 
DELETE FROM SUMMER
WHERE SID=200; 
-- C강좌 수강료 다시 조회
SELECT PRICE "C 수강료"
FROM SUMMER
WHERE CLASS='C'; 

• 질의 7-2) 계절 학기에 새로운 자바 강좌를 개설하시오.

-- 자바 강좌 삽입 
INSERT INTO SUMMER VALUES(NULL, 'JAVA', 25000);
-- SUMMER 테이블 조회
SELECT * 
FROM SUMMER; 

--NULL 값이 있는 경우 주의할 질의: 투플은 다섯 개이지만 수강학생은 총 4명임. 
SELECT COUNT(*) "수강인원"
FROM SUMMER; 

🔽

SELECT COUNT(SID) "수강인원"
FROM SUMMER;

SELECT COUNT(*) "수강인원"
FROM SUMMER
WHERE SID IS NOT NULL; 

🔽

• 수정 이상
• 질의 7-3) FORTRAN 강좌의 수강료를 20000원에서 15000원으로 수정하시오.

-- 만약 UPDATE 문을 다음과 같이 작성하면 데이터 불일치 문제가 발생함 
UPDATE SUMMER 
SET PRICE=15000
WHERE CLASS='FORTRAN' AND SID=100; 
-- SUMMER 테이블을 조회해보면 FORTRAN 강좌의 수강료가 한 건만 수정되었음 
SELECT * 
FROM SUMMER; 

• 테이블의 구조를 수정하여 이상현상이 발생하지 않는 사례
  🔽 SUMMER 테이블 분리
  

• SUMMERPRICE 테이블과 SUMMERENROLL 테이블을 생성하는 SQL문

DROP TABLE SUMMERPRICE
DROP TABLE SUMMERENROLL 

CREATE TABLE SUMMERPRICE 
CLASS VARCHAR(20), 
PRICE INTEGER
); 

INSERT INTO SUMMERPRICE VALUES('FORTRAN', 20000);
INSERT INTO SUMMERPRICE VALUES('PASCAL', 15000); 
INSERT INTO SUMMERPRICE VALUES('C', 10000);

SELECT * 
FROM SUMMERENROLL; 

• 삭제 이상 없음
• 질의 7-4) 200번 학생의 계절학기 수강신청을 취소하시오
  🔽
  

SELECT PRICE "C 수강료"
FROM SUMMERPRICE
WHERE CLASS='C'; 

DELETE FROM SUMMERENROLL 
WHERE SID 200; 

SELECT * 
FROM SUMMERENROLL; 

SELECT PRICE "C 수강료" 
FROM SUMMERPRICE
WHERE CLASS='C'; 

• 삽입 이상 없음
• 질의 7-5) 계절학기에 새로운 자바 강좌를 개설하시오

-- 자바 강좌 삽입, NULL 값을 입력할 필요가 없음 
INSERT INTO SUMMERPRICE VALUES('JAVA', 25000);

SELECT * 
FROM SUMMERPRICE; 

-- 수강 신청 정보 확인 
SELECT * 
FROM SUMMERENROLL; 

• 질의 7-6) FORTRAN 강좌의 수강료를 20000원에서 15000원으로 수정하시오.

UPDATE SUMMERPRICE
SET PRICE=15000
WHERE CLASS='FORTRAN'

SELECT PRICE "FORTRAN 수강료"
FROM SUMMERPRICE
WHERE CLASS='FORTRAN';

함수 종속성

• 학생수강성적 릴레이션의 각 속성 사이에는 의존성이 존재함
  •  어떤 속성 A의 값을 알면 다른 속성 B의 값이 유일하게 정해지는 의존 관계를 ‘속성 B는 속성 A에 종속한다(dependent)’ 혹은 ‘속성 A는 속성 B를 결정한다(determine)’라고 함.
  •  속성 A의 값이 같으면 속성 B의 값도 같

• 학생수강성적 릴레이션에서 종속관계에 있는 예
   학생번호 → 학생이름
   학생번호 → 주소
   강좌이름 → 강의실
   학과 → 학과사무실

• 종속하지 않는 예
   학생이름 → 강좌이름
   학과 → 학생번호

• 종속하는 것처럼 보이지만 실제로는 그렇지 않은 예
   학생이름 → 학과 (동명이인 존재 가능)

🔽 학생 수강 성적 릴레이션의 종속 관계

• 함수 종속성 다이어그램(functional dependency diagram)은 함수 종속성을 나타내는 표기법
   릴레이션의 속성: 직사각형
   속성 간의 함수 종속성: 화살

🔽 학생 수강 성적 릴레이션의 함수 종속성 다이어그램

Q&A

• 참조 무결성 문제 해결 관련 ✔

• delete from orders where orders.bookid in(select bookid from book where publisher = '이상미디어) ;

🔽

book 테이블에서 이상미디어가 포함된 튜플의 bookid 인 7,8을 참조하고 있는 orders 테이블의 튜플을 먼저 삭제해줘야 참조 무결성 문제를 해결하고 book 테이블에 있는 이상미디어 관련 튜플을 정상적으로 삭제할 수 있음.