Intro to Database (12) : Saklia Database 실습

2019-07-11

2019-07-12

 

Logical model, ERD을 디자인하고 다른 사용자와 같이 쓰기 위해(틀을 공유하기 위해) SQL, DDL파일로 공유를 하면,

 이 파일을 불러오기만 하면 관계형 모델을 보여주고 어떤 구조인지 확인할 수 있도록 한다. 

(모델 -> 코딩 -> 모델)  

 

 이에 대한 실습으로 Saklia Database를 만들고 실습하고자 합니다.  

Saklia Database는 영화, 배우, 영화-배우 관계, 영화, 상점, 렌털을 연결하는 중앙 재고 테이블과 같은 것들을 특징으로 하는, DVD 대여점을 모델링하는 잘 정상화된 스키마이다. 이 데이터베이스를 만들고, 논리적 구조를 확인해보고, 쿼리문을 짜서 원하는 데이터를 출력하는 실습을 진행하고자 합니다. 

1. 사용자 생성, 권한 부여

system 사용자로 접속해(cmd에서 수행했습니다) 새 사용자 Saklia_connect 를 생성하고, 모든 권한을 부여한 후 2개 옵션 sysdba, sysoper만 빼줍니다.(안 빼주면 접속이 안됨.)  

그리고 +를 눌러 새로 만든 사용자로 접속해줍니다.

2. 테이블 생성

새로 만든 계정에 여러 개의 테이블과 인덱스 - 시퀀스를 생성합니다. UI에서 하나하나 입력하는 방법도 있으나 SQL문으로 한 번에 넣는 편이 더 편하니 저는 그렇게 하겠습니다. 

--create actor table&index&sequence
CREATE TABLE actor (
  actor_id numeric NOT NULL ,
  first_name VARCHAR(45) NOT NULL,
  last_name VARCHAR(45) NOT NULL,
  last_update DATE NOT NULL,
  CONSTRAINT pk_actor PRIMARY KEY  (actor_id)
); 
CREATE  INDEX idx_actor_last_name ON actor(last_name);
CREATE SEQUENCE actor_sequence;
--create country table&sequence
CREATE TABLE country (
  country_id SMALLINT NOT NULL,
  country VARCHAR(50) NOT NULL,
  last_update DATE,
  CONSTRAINT pk_country PRIMARY KEY (country_id)
);
CREATE SEQUENCE country_sequence;
--create city table&index&sequence
CREATE TABLE city (
  city_id int NOT NULL,
  city VARCHAR(50) NOT NULL,
  country_id SMALLINT NOT NULL,
  last_update DATE NOT NULL,
  CONSTRAINT pk_city PRIMARY KEY (city_id),
  CONSTRAINT fk_city_country FOREIGN KEY (country_id) REFERENCES country (country_id)
);
CREATE  INDEX idx_fk_country_id ON city(country_id);
CREATE SEQUENCE city_sequence;
--create address table&sequence
CREATE TABLE address (
  address_id int NOT NULL,
  address VARCHAR(50) NOT NULL,
  address2 VARCHAR(50) DEFAULT NULL,
  district VARCHAR(20) NOT NULL,
  city_id INT  NOT NULL,
  postal_code VARCHAR(10) DEFAULT NULL,
  phone VARCHAR(20) NOT NULL,
  last_update DATE NOT NULL,
  CONSTRAINT pk_address PRIMARY KEY (address_id));
  CREATE  INDEX idx_fk_city_id ON address(city_id);
ALTER TABLE address ADD  CONSTRAINT fk_address_city FOREIGN KEY (city_id) REFERENCES city (city_id);
CREATE SEQUENCE address_sequence;
--create language table&sequence
CREATE TABLE language (
  language_id SMALLINT NOT NULL ,
  name CHAR(20) NOT NULL,
  last_update DATE NOT NULL,
  CONSTRAINT pk_language PRIMARY KEY (language_id));
CREATE SEQUENCE language_sequence;
--create category table&sequence
CREATE TABLE category (
  category_id SMALLINT NOT NULL,
  name VARCHAR(25) NOT NULL,
  last_update DATE NOT NULL,
  CONSTRAINT pk_category PRIMARY KEY  (category_id));
CREATE SEQUENCE category_sequence;
--create customer table&index&sequence
CREATE TABLE customer (
  customer_id INT NOT NULL,
  store_id INT NOT NULL,
  first_name VARCHAR(45) NOT NULL,
  last_name VARCHAR(45) NOT NULL,
  email VARCHAR(50) DEFAULT NULL,
  address_id INT NOT NULL,
  active CHAR(1) DEFAULT 'Y' NOT NULL,
  create_date DATE NOT NULL,
  last_update DATE NOT NULL,
  CONSTRAINT pk_customer PRIMARY KEY  (customer_id),
  CONSTRAINT fk_customer_address FOREIGN KEY (address_id) REFERENCES address(address_id));

CREATE  INDEX idx_customer_fk_store_id ON customer(store_id);
CREATE  INDEX idx_customer_fk_address_id ON customer(address_id);
CREATE  INDEX idx_customer_last_name ON customer(last_name);
CREATE SEQUENCE customer_sequence;
--create film table&index&sequence
CREATE TABLE film (
  film_id int NOT NULL,
  title VARCHAR(255) NOT NULL,
  description CLOB DEFAULT NULL,
  release_year VARCHAR(4) DEFAULT NULL,
  language_id SMALLINT NOT NULL,
  original_language_id SMALLINT DEFAULT NULL,
  rental_duration SMALLINT  DEFAULT 3 NOT NULL,
  rental_rate DECIMAL(4,2) DEFAULT 4.99 NOT NULL,
 length SMALLINT DEFAULT NULL,
  replacement_cost DECIMAL(5,2) DEFAULT 19.99 NOT NULL,
  rating VARCHAR(10) DEFAULT 'G',
  special_features VARCHAR(100) DEFAULT NULL,
  last_update DATE NOT NULL,
  CONSTRAINT pk_film PRIMARY KEY  (film_id),
  CONSTRAINT fk_film_language FOREIGN KEY (language_id) REFERENCES language (language_id) ,
  CONSTRAINT fk_film_language_original FOREIGN KEY (original_language_id) REFERENCES language (language_id));
ALTER TABLE film ADD CONSTRAINT CHECK_special_features CHECK(special_features is null or
                                                              special_features like '%Trailers%' or
                                                              special_features like '%Commentaries%' or
                                                              special_features like '%Deleted Scenes%' or
                                                              special_features like '%Behind the Scenes%');
ALTER TABLE film ADD CONSTRAINT CHECK_special_rating CHECK(rating in ('G','PG','PG-13','R','NC-17'));
CREATE  INDEX idx_fk_language_id ON film(language_id);
CREATE  INDEX idx_fk_original_language_id ON film(original_language_id);
CREATE SEQUENCE film_sequence;
--create actor table&index
CREATE TABLE film_actor (
  actor_id INT NOT NULL,
  film_id  INT NOT NULL,
  last_update DATE NOT NULL,
  CONSTRAINT pk_film_actor PRIMARY KEY  (actor_id,film_id),
  CONSTRAINT fk_film_actor_actor FOREIGN KEY (actor_id) REFERENCES actor (actor_id),
  CONSTRAINT fk_film_actor_film FOREIGN KEY (film_id) REFERENCES film (film_id));
CREATE  INDEX idx_fk_film_actor_film ON film_actor(film_id);
CREATE  INDEX idx_fk_film_actor_actor ON film_actor(actor_id) ;
--create film_category table&index
CREATE TABLE film_category (
  film_id INT NOT NULL,
  category_id SMALLINT  NOT NULL,
  last_update DATE NOT NULL,
  CONSTRAINT pk_film_category PRIMARY KEY (film_id, category_id),
  CONSTRAINT fk_film_category_film FOREIGN KEY (film_id) REFERENCES film (film_id),
  CONSTRAINT fk_film_category_category FOREIGN KEY (category_id) REFERENCES category (category_id));
CREATE  INDEX idx_fk_film_category_film ON film_category(film_id);
CREATE  INDEX idx_fk_film_category_category ON film_category(category_id);
--create film_text table
CREATE TABLE film_text (
  film_id SMALLINT NOT NULL,
  title VARCHAR(255) NOT NULL,
  description CLOB,
  CONSTRAINT pk_film_text PRIMARY KEY  (film_id)
);
--create inventory table&index&sequence
CREATE TABLE inventory (
  inventory_id INT NOT NULL,
  film_id INT NOT NULL,
  store_id INT NOT NULL,
  last_update DATE NOT NULL,
  CONSTRAINT pk_inventory PRIMARY KEY  (inventory_id),
  CONSTRAINT fk_inventory_film FOREIGN KEY (film_id) REFERENCES film (film_id));
CREATE  INDEX idx_fk_film_id ON inventory(film_id);
CREATE  INDEX idx_fk_film_id_store_id ON inventory(store_id,film_id);
CREATE SEQUENCE inventory_sequence;
--create staff table&index&sequence
CREATE TABLE staff (
  staff_id SMALLINT NOT NULL,
  first_name VARCHAR(45) NOT NULL,
  last_name VARCHAR(45) NOT NULL,
  address_id INT NOT NULL,
  picture BLOB DEFAULT NULL,
  email VARCHAR(50) DEFAULT NULL,
  store_id INT NOT NULL,
  active SMALLINT DEFAULT 1 NOT NULL,
  username VARCHAR(16) NOT NULL,
  password VARCHAR(40) DEFAULT NULL,
  last_update DATE NOT NULL,
  CONSTRAINT pk_staff PRIMARY KEY  (staff_id),
  CONSTRAINT fk_staff_address FOREIGN KEY (address_id) REFERENCES address (address_id)
);

CREATE  INDEX idx_fk_staff_store_id ON staff(store_id);
CREATE  INDEX idx_fk_staff_address_id ON staff(address_id);
CREATE SEQUENCE staff_sequence;
--create store table&index&sequence
CREATE TABLE store (
  store_id INT NOT NULL,
  manager_staff_id SMALLINT NOT NULL,
  address_id INT NOT NULL,
  last_update DATE NOT NULL,
  CONSTRAINT pk_store PRIMARY KEY  (store_id),
  CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ,
  CONSTRAINT fk_store_address FOREIGN KEY (address_id) REFERENCES address (address_id));
CREATE  INDEX idx_store_fk_manager_staff_id ON store(manager_staff_id);
CREATE  INDEX idx_fk_store_address ON store(address_id);
CREATE SEQUENCE store_sequence;
--create payment table&index&sequence
CREATE TABLE payment (
  payment_id int NOT NULL,
  customer_id INT  NOT NULL,
  staff_id SMALLINT NOT NULL,
  rental_id INT DEFAULT NULL,
  amount DECIMAL(5,2) NOT NULL,
  payment_date DATE NOT NULL,
  last_update DATE NOT NULL,
  CONSTRAINT pk_payment PRIMARY KEY  (payment_id),
  CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ,
  CONSTRAINT fk_payment_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id)
);
CREATE  INDEX idx_fk_staff_id ON payment(staff_id);
CREATE  INDEX idx_fk_customer_id ON payment(customer_id);
CREATE SEQUENCE payment_sequence;

--create rental table&index&sequence
CREATE TABLE rental (
  rental_id INT NOT NULL,
  rental_date DATE NOT NULL,
  inventory_id INT  NOT NULL,
  customer_id INT  NOT NULL,
  return_date DATE DEFAULT NULL,
  staff_id SMALLINT  NOT NULL,
  last_update DATE NOT NULL,
  CONSTRAINT pk_rental PRIMARY KEY (rental_id),
  CONSTRAINT fk_rental_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id) ,
  CONSTRAINT fk_rental_inventory FOREIGN KEY (inventory_id) REFERENCES inventory (inventory_id) ,
  CONSTRAINT fk_rental_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id));
  CREATE INDEX idx_rental_fk_inventory_id ON rental(inventory_id);
CREATE INDEX idx_rental_fk_customer_id ON rental(customer_id);
CREATE INDEX idx_rental_fk_staff_id ON rental(staff_id);
CREATE UNIQUE INDEX   idx_rental_uq  ON rental (rental_date,inventory_id,customer_id);
CREATE SEQUENCE rental_sequence;

--Foreign Key Constraints
ALTER TABLE customer ADD CONSTRAINT fk_customer_store FOREIGN KEY (store_id) REFERENCES store (store_id);
ALTER TABLE inventory ADD CONSTRAINT fk_inventory_store FOREIGN KEY (store_id) REFERENCES store (store_id);
ALTER TABLE staff ADD CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id);
ALTER TABLE payment ADD CONSTRAINT fk_payment_rental FOREIGN KEY (rental_id) REFERENCES rental (rental_id) ON DELETE SET NULL;

 

3. 만든 테이블의 스키마  

파일 -> datamodeler -> 임포트 -> 데이터 딕셔너리 

이대로 쭉 만들면 된다! 

 

결과로 나온 데이터 모델 

 

4. (Exercise #10) 개인 계정으로 로그인하고, 판매 데이터에 대한 DDL 파일을 다운로드하고 관계형 모델과 논리적 모델을 만들어보자.

(방금까지 하던 실습과 다른 실습입니다.) 

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldevdm/r40/datamodel4genmulti/datamodel4genmulti_otn.html#s4

위 홈페이지에 들어가서 아래 사진의 형광 쳐진 파일을 다운로드 하고 압축을 해제한 뒤 SQLdeveloper에서 불러오면 된다. 

파일 -> datamodeler -> 임포트 -> DDL 파일 -> 데이터베이스 사이트 선택(이 경우엔 11g) 

디자인  DDL 코드에서, SQl 코드를 논리형 모델,  관계형 모델로 눈에 보이는 관계도를 만듦 

(좌) 논리형 (우) 관계형 

이쪽 우클릭하면 논리-관계형 모델 엔지니어링 전환가능 

5. 2에서 만들었던 테이블을 다시 만들기

(데이터, 스키마 파일을 다운로드해서 - 필요하신분은 댓글) 

새 계정을 또 만들고 거기에 다운로드한 파일을 불러오고 코드를 실행하면 바로 테이블, 스키마가 생성된다. 데이터 sql문은 실행되는데 시간이 조금 걸리니 당황하지 말도록 합시다. 

 

그리고 이 만들어진 테이블에서 문제풀이를 진행합니다. 문제를 푸는데 참고할 수 있도록 데이터 모델을 구성했습니다. 3번 실습과 동일한 방법으로 하면 됩니다.

 

지금까지 배운 문법으로 해당 테이블에 대한 예제를 풀어보겠습니다. 

--필름테이블의 데이터 검색
select * from film;
desc film;
--필름테이블의 ID, title, rating, and the special features 검색
select film_id, title, rating, special_features from film; 
--R-rated 필름의 ID, title, rating, and special features 검색
select film_id, title, rating, special_features from film
where rating = 'R';
--rental duration이 3일인 필름의 가격 (rental_rate) 검색
select title, rental_rate from film where rental_duration = 3;
--rental duration이 5일인 필름의 정보를 가격이 싼 순으로 검색
select title, rental_rate from film where rental_duration = 5
order by rental_rate; 
--1번 위치에 있는 가게 고객의 이름과 이메일을 first name 알파벳순으로 검색
select first_name, last_name, email from customer 
where store_id = 1 order by first_name;
--길이 (length)가 3시간이 넘는 99센트짜리 영화검색
select title from film where length >= 180 AND rental_rate =0.99; 
--텍사스나 캘리포니아에 있는 주소정보 검색
select * from address where district in ('Texas','California');
select * from address where district = 'Texas' or district = 'California';
--rental duration이 3일보다 짧거나 5일보다 많은 영화정보 검색
select * from film where rental_duration < 3 or rental_duration >5; 
--Thrilling한 영화검색 (description)
SELECT * FROM FILM 
WHERE DESCRIPTION like '%Thrilling%';
SELECT TITLE FROM FILM 
WHERE DESCRIPTION like '%Thrilling%';
--Korea를 포함하는 나라이름 검색
select * from country where country like '%Korea%'; /*문자열 안에서는 대소문자 꼭 구분해야 한다*/
--도시ID가 113, 268, 357, 539, 553인 주소정보 검색
select * from ADDRESS WHERE CITY_ID in (113,268,357,539,553);
--China관련영화를 싼 가격순으로 검색
select * from film where description like '%China%' order by rental_rate;
--Rating의 종류를 알파벳순으로 검색
select distinct rating from film order by rating; 
--! Address2가 널인 주소 검색
select * from address where address2 is null;
--Rental duration의 종류를 올림차순으로 검색
select distinct Rental_duration from film 
order by Rental_duration;
--Address테이블의 레코드의 갯수
select count(*) from address;
--Address2컬럼의 레코드의 갯수
select count(address2) from address;
--Film테이블의 레코드의 갯수
select count(title) from film;
--Rental_duration종류의 갯수
select count(distinct Rental_duration) from film;
--모든 영화의 길이의 합 검색 (시간단위로 환산)
select sum(length/60) from film;
--Replacement_cost의 종류의 갯수, 평균, 최소, 최대값
select count(distinct replacement_cost), avg(replacement_cost), MIN(replacement_cost), MAX(replacement_cost) from film;
--평균영화의 길이는?
select avg(length) from film;
--최고가격과 최소가격 (amount in payment테이블)
select max(amount), min(amount) from payment;
--!가게번호에 따른 active 고객의 숫자 (active =1)
count 
--!Rating에 따른 영화의 수중 180개 이상인 영화를 갯수가 많은 순으로 검색
select count(film_id) from film 
group by rating; /*rating별 개수*/
select rating,count(film_id) from film 
group by rating having count(film_id)>= 180
order by 2 desc; /*rating별 개수와 많은 수별 내림차순, sql문의 순서에 유의하자*/
--!가격에 따른 영화갯수 (rental rate)
select rental_rate,count(title) from film group by rental_rate;
--고객이 active하면 ‘ACTIVE’, 아니면 ‘INACTIVE’로 출력하고 컬럼이름은 Status로 검색
select active, 
(case when active = 1 then 'Active'
else 'Inactive' end) status from customer;
--!Rental_duration이 3이하면 SHORT, 4이상이면 MEDIUM, 6이상이면 LONG, 그밖의 경우는 NULL로하고 컬럼이름은 Duration으로 검색
select title, rental_duration,
(case when rental_duration <= 3 then 'short'
     when rental_duration >= 4 and rental_duration<6 then 'medium'
     when rental_duration >= 6 then 'long'
else null end) duration from film;
--Amount가 9.99이상이면 High, 3.99초과 9.99미만이면 Medium, 3.99이하면 Low로 하고 컬럼이름은 Payment Amount
select amount, (case when amount <=3.99 then 'Low'
when amount>3.99 and amount <9.99 then 'Medium' /*case이하를 하나의 컬럼취급*/
when amount>9.99 then 'High' end) as Payment from payment;
--South Korea에 있는 주소, 도시 검색
select address, city from address a join city c on a.city_id = c.city_id
join country co on co.country_id = c.country_id 
where country like '%Korea%'; /*join on*/

select address, city from address 
join city using(city_id)
join country using(country_id)
where country = 'South Korea'; /join using/
--$9.99인 영화를 빌린 고객의 정보
select c.* from customer c, payment p 
where c.customer_id = p.customer_id and amount = 9.99; 

--가장 많이 영화를 빌린 고객검색
select c.customer_id, count(rental_id) 
from rental r, customer c
where r.customer_id = c.customer_id
group by c.customer_id 
order by count(rental_id) desc; /*어느테이블에서 왔는지 확실한 출처를 정해줘야 한다. 여기서는 customer_id가 공통이므로*/

select * from (select c.customer_id, count(rental_id) 
from rental r, customer c
where r.customer_id = c.customer_id
group by c.customer_id 
order by count(rental_id) desc) where rownum <=1;
--!날짜별 렌탈의 수
select trunc(rental_date, 'dd'), count(rental_id) 
from rental group by trunc(rental_date, 'dd') order by 1;
--가장 영화를 많이 빌려가는 요일
SELECT 요일 FROM (SELECT TO_CHAR(RENTAL_DATE,'DAY') 요일,COUNT(TO_CHAR(RENTAL_DATE,'DAY')) 
FROM RENTAL 
GROUP BY TO_CHAR(RENTAL_DATE,'DAY') ORDER BY 2 DESC)
WHERE ROWNUM=1;

--고객의 이름의 길이 (fisrt name and last name)와 첫 세글자
select customer_id, length(first_name)+length(last_name),
substr(length(first_name)+length(last_name),1,3)
from customer;

select length(first_name) 이름_길이,
length(last_name) 성_길이,
substr(first_name, 1, 3) 이름_첫_세글자,
substr(last_name, 1, 3) 성_첫_세글자
from customer;

select length(first_name || ' ' || last_name) "Length", substr(first_name || ' ' || last_name,0,3) from customer;
--이메일의 첫세글자뒤에 *를 더해서 학교이메일주소 만들기
select rpad(substr(email,1,3),4,'*') || '' || 
substr(email,4) from customer;

select rpad(substr(email,0,3)
,(instr(email,'0'))-1,'*')|| 'catholic.ac.kr' "학교 이메일" from customer;


SELECT LAST_NAME, CONCAT(RPAD(SUBSTR(EMAIL,1,3),12,'*'),'@...AC.KR') 이메일 FROM CUSTOMER;

select rpad(substr(email,1,3),4,'*') || '' || substr(email,4)from customer; 

 

디클레어 선언하기 

IF-THEN Example

IF-THEN Example

set serveroutput on; 
declare 
v_condition number :=1;
begin
if v_condition = 1 then
dbms_output.put_line('대한');
end if;
end;
/

대한이 출력

Trigger 방아쇠