[Oracle] 초간단 Oracle DB 설계

짧게나마 DBA가 되어보자
오늘의 프로젝트는 충북대학교 공동훈련센터에서 진행하는 IoT기반 스마트솔루션 개발자 양성과정 교육 중 하루만 진행되는 미니프로젝트로 진행되었다

미니프로젝트이다 보니 모두 계획적으로 진행되진 않았지만 순서를 맞추어 진행하다 보니 역할 분담이 착실히 되어 각자의 영역에서 몰두하는 시간을 가질 수 있었다
나는 DB 유저 생성부터 속성 값 기입 후 최종 sql 구문 조회까지 진행하는 역을 맡았고,
모두 sql 구문으로 진행하였다

create user c##RESTAURANT identified by 1234;

해당 내용으로 c##RESTAURANT 유저를 생성해주자 비밀번호 : 1234
* 오라클이 업데이트 되면서
공용계정에는 c##을 붙여줘야 한다는
규칙이 생겼다

위 내용과 같이 권한을 가지고 있지 않아 로그인 실패라고 뜨는데 권한 설정을 해주지 않아서 그렇다
권한 설정을 진행하자

grant connect, resource to c##RESTAURANT;

성공으로 뜨는것을 잘 볼 수 있다
이제 RESTAURANT 유저로 접속을 해서 sql로 테이블 및 속성값 기입을 해주자
2. 테이블 생성

create table RESTAURANT(
restid int not null primary key,
restname varchar(50),
address varchar(100),
phone varchar(20),
openhours varchar(50),
restrating int);

create table MENU (
menuid int primary key,
restid int not null,
menuname varchar(50),
describe varchar(255),
price int,

foreign key (restid) references restaurant (restid));

create table REVIEW (
reviewid int primary key,
restid int not null,
menuid int,
orderer varchar(50),
r_text varchar(255),
reviewrating int,
wrottendate date,

foreign key (restid) references restaurant (restid),
foreign key (menuid) references menu (menuid));

REVIEW 테이블까지 생성하면 끝이다
이렇게 설계하면 된다 😎
가 아니라.. 본래는 여러 단계에 걸쳐 설계 과정에 있어 결정해야 할 사항들이 많은데 팀 프로젝트 이전 주제를 정하고 안에 들어갈 데이터 등을 초간단으로 만들어보기만 맛만 보는것이기에 앞의 개념 ▶ 논리 로의 과정을 건너뛰었다
개념 ▶ 논리 ▶ 물리
세 단계의 모델링 단계를 거치면서 요구사항 수집, 개체 추출, 다이어그램 작성 등을 해보며 효율적인 DB의 구조와 좋은 성능을 위해 고민해야 하는 단계이지만
미니 프로젝트인 만큼 관계되는 릴레이션의 구성과 내부의 속성값만을 빠르게 기입해보고 직접 조회해보는 시간을 가졌다
3. 데이터 삽입
이제 INSERT INTO 를 통해 데이터를 넣어주자

INSERT INTO Restaurant (restId, restName, address, phone, openHours, restRating)
select 1, '롯데리아', '서울특별시 강남구 도산대로 95길 20', '02-1234-5678', '10:00 - 22:00', 5 from dual union all
select 2, '맥도날드', '부산광역시 해운대구 해운대해변로 280', '051-9876-5432', '11:00 - 23:00', 3 from dual union all
select 3, '버거킹', '대구광역시 수성구 수성로 45길 8', '053-4567-1234', '09:00 - 21:00', 4 from dual union all
select 4, '맘스터치', '인천광역시 연수구 컨벤시아대로 123', '032-7890-1234', '11:00 - 23:00', 4 from dual;

대량의 데이터 기입을 위해 INSERT INTO 를 여러번 반복해야 하는 일이 많을텐데 구글링을 통해 벌크 인서트 혹은 bulk insert 라고 검색해보면 여러가지 방법 혹은 비교 글들이 많이 나온다
개중 나는 위와 같은 구성으로 진행하였다 보통은 반복문을 통해 필요한 데이터를 넣어주거나 기존에 있던 분산 데이터를 필요한 테이블로 옮길때 쓰여지기도 하고 insert 성능 자체도 엄청나게 좋아지니

⭐⭐ 꼭 기억해두고 활용하도록 하자 ⭐⭐
그리고 위 코드를 기입해주면

여기에도 권한 없음 오류가 뜬다
해당 오류에 대해 찾아보자 ORA-01950
https://dololak.tistory.com/756
위 링크에서 살펴본 결과로는

오라클에는 테이블 스페이스라 하여 테이블이 저장되는 공간이 있는데, 이 스페이스(공간)에 대해 크기를 정해주지 않아 발생하는 오류이다

alter user c##restaurant default tablespace users quota unlimited on users;

unlimited 를 통해 크기 제한 없이 지정해주고 다시 INSERT 구문을 시행해보면

INSERT INTO Menu (menuId, restId, menuName, describe, price)
select 1, 1, '불고기버거', '한국 전통의 불고기 맛 햄버거.', 4500 from dual union all
select 2, 1, '새우버거', '당신들이 새우 맛을 아는가.', 4500 from dual union all
select 3, 2, '빅맥', '빅맥지수의 그 빅맥.', 5200 from dual union all
select 4, 2, '맥치킨', '맥치킨이지만 사실 햄버거다.', 3500 from dual union all
select 5, 3, '와퍼', '불맛나는 햄버거.', 6900 from dual union all
select 6, 3, '킹치킨버거', '버거킹 최약체.', 3600 from dual union all
select 7, 4, '싸이버거', '월드스타 싸이와 연관 없다.', 4300 from dual union all
select 8, 4, '화이트갈릭버거', '힘을 숨긴 햄버거.', 4600 from dual;

INSERT INTO Review (reviewId, restId, menuid, orderer, r_text, reviewRating, wrottendate)
select 1, 1, 1, '레드클리프', '이 햄버거의 육즙이 넘치고, 소스도 완벽하게 어우러져 있어요!', 1, '2023-02-15' from dual union all
select 2, 1, 2, '다스', '햄버거가 크고 든든해서 배부르게 먹을 수 있어 좋았습니다.', 3, '2023-01-28' from dual union all
select 3, 4, 7, '잭 블랙', '햄버거가 짜요.', 1, '2023-02-20' from dual union all
select 4, 4, 8, '윌 스미스', '신선한 야채와 바삭한 튀김이 어우러진 햄버거가 인상적이었습니다.', 2, '2023-03-05' from dual union all
select 5, 3, 5, '도널드 트럼프', '햄버거 빵이 부드럽고 담백한 맛이 좋았어요.', 4, '2023-01-10' from dual union all
select 6, 3, 5, '레드클리프', '스파이시한 소스가 들어간 햄버거로, 매콤한 맛을 좋아하는 사람들에게 추천합니다.', 1, '2023-03-12' from dual union all
select 7, 2, 4, '레드클리프', '스파게티가 참 맛있어요 매콤달달해서 개인적으로 만족합니다.', 4,'2023-03-21' from dual union all
select 8, 4, 7, '잭 블랙', '매장이 더러워요', 2,'2023-02-20' from dual union all
select 9, 4, 7, '잭 블랙', '알바가 노려봐요', 1,'2023-01-20' from dual union all
select 10, 4, 7, '다스', '뭘로 만들었는지 모르겠어요', 1,'2023-03-11' from dual union all
select 11, 2, 4, '다스', '이 햄버거는 고기와 야채의 균형이 잘 맞아, 깔끔한 맛을 즐길 수 있었습니다.', 1, '2023-02-05' from dual;

모든 테이블에 데이터를 넣어줬다 이제 각 릴레이션 간 조인을 활용하여 조회를 할 수 있는지 생각해보면서 작성해보자 🧐

--1 등록된 점포를 별점순으로 모두 출력
SELECT *
FROM Restaurant
ORDER BY restRating desc;
--2 메뉴 릴레이션에서 가격이 4500이상 5500미만인 메뉴를 출력
SELECT *
FROM Menu
WHERE price >= 4500 and price <5500;
--3 리뷰를 날짜순으로 전체 출력
SELECT *
FROM Review
ORDER BY wrottendate;
--4 별점4점 이상인 레스토랑과 별점을 출력
SELECT restName, restRating
FROM Restaurant
WHERE restRating >= 4;
--5 메뉴 이름에 '치킨'이 들어간 메뉴의 제품이름, 제품가격을 출력 
SELECT menuName, price
FROM Menu
WHERE Menuname LIKE '%치킨%'
ORDER BY price;
--6 소비자 평점 3점 이상인 리뷰 내용을 별점순으로 출력
SELECT reviewRating, r_text
FROM Review
WHERE reviewRating >= 3
ORDER BY reviewRating;
--7 서울지역이 아닌 레스토랑과 영업시간을 출력
SELECT restName, openHours 
FROM Restaurant
WHERE address not like '서울%';
--8 각 레스토랑의 메뉴, 메뉴별 설명상세와 가격을 출력
SELECT  restName 레스토랑명, Menuname 메뉴이름, Describe 메뉴상세, price 가격
FROM Restaurant,Menu
WHERE Restaurant.restid = Menu.restid
ORDER BY Restaurant.restid;
--9 싸이버거에 대한 평가내용만 모아서 세 건만 볼 수 있게
SELECT orderer 주문자, reviewRating 별점 , r_text 평가내용
FROM (select * from Review where menuid=(SELECT menuid from menu where menuname = '싸이버거' ) 
order by reviewRating DESC) 
WHERE ROWNUM <=3;
--10 주문자 레드클리프의 리뷰만 모아보기
SELECT reviewRating 평점, r_text 리뷰내용
FROM Review
WHERE orderer = '레드클리프'
ORDER BY reviewRating;
--11 불고기버거의 메뉴 상세설명, 가격정보를 보고싶다.
SELECT Menuname 메뉴명, describe 상세설명
FROM Menu
WHERE menuName = '불고기버거';
--12 잭 블랙씨의 총 주문액
SELECT orderer 주문자, sum(price) 총주문액
FROM  menu, review
WHERE menu.menuid = review.menuid
GROUP BY orderer
HAVING orderer = '잭 블랙';

⭐ 오류 발생시 항상 오류의 원인을 살피고 진행하자 ⭐ 구글링의 결과가 나에게 발생한 오류와 동일한 오류가 아닐 수 있고, 나에게 발생한 오류의 정확한 원인은 나밖에 모른다 항상 오류를 거슬러 올라가자