멀티캠퍼스 백엔드 과정 24일차[7월5일] - 관계 데이터 모델

GoldenDusk·2023년 7월 11일
KDT국비멀티캠퍼스오라클
0

멀티캠퍼스 백엔드 과정

목록 보기
19/60

관계 데이터 모델

관계 데이터 모델의 개념

01. 릴레이션

개념

  • 릴레이션 = 행과 열로 구성된 테이블 = 엔터티 = 개체(Entity)
  • model
    • 누구든지 딱 알아볼 수 있게
    • 표준, 기준이 명확한
  • relation algebar = 관계 대수
  • Domain : 값들의 집합
  • 릴레이션(테이블) = 배열을 클래스로 패키지 화 한 것
    • 속성 도서이름에 배열 형태로 축구의 역사, 축구 아는 여자 등등의 값들 들어가 있는 느낌

관계(relationship)

  • 릴레이션 내에서 생성되는 관계 : 릴레이션 내 데이터들의 관계
  • 릴레이션 간에 생성되는 관계 : 릴레이션 간의 관계

02. 릴레이션 스키마/ 인스턴스

릴레이션 스키마(정적), 릴레이션 인스턴스(동적)

  • 속성(attribute) : 릴레이션 스키마의 열, 세로 값(컬럼)
  • 튜플(Tuple) : 가로 값(행)
  • 도메인(domain) : 하나의 속성이 가질 수 있는 값의 집합
  • 차수(degree) : 속성의 개수
  • 카디널리티(Cardnality) : 튜플의 개수
  • 인스턴스 = 외연: 정의된 스키마에 따라 테이블에 실제 저장된 데이터 값
  • 스키마 = 내포: 릴레이션이 어떻게 구성되는지, 어떤 정보를 저장하는 지에 대한 기본적인 구조

스키마의 표현

  • 릴레이션 이름(속성1 : 도메인1, 속성2:도메인2, 속성3:도메인3)

03. 릴레이션 특징

특징 6가지

  • 속성은 단일 값을 가진다.(원자값)
    • ,로 안된다.
  • 속성은 서로 다른 이름을 가진다.
  • 한 속성의 값은 모두 같은 도메인 값을 가진다.
    • 문자로 정의하면 문자가 숫자로 정의하면 숫자만 들어가야 한다.
  • 속성은 순서는 상관없다.
  • 릴레이션 내의 중복된 투플을 허용하지 않는다.
  • 투플의 순서는 상관없다.

04. 관계 데이터 모델

  • 관계 데이터 모델은 데이터를 2차원 테이블 형태인 릴레이션으로 표현
  • 릴레이션에 대한 제약조건(constraints)과 관계 연산을 위한 관계대수(relational algebra)를 정의함.

무결성 제약 조건

01. 키

키[KEY]

  • 무결성 제약 조건을 조절
  • 특정 튜플을 구분할 때 사용하는 속성, 속성의 집합
  • 키가 되는 속성은 값이 반드시 달라서 튜플 간의 구별을 할 수 있는 속성
  • 키는 릴레이션 간의 관계 사용
  • 주문(

primary key(userid) unique + not null

);

슈퍼키

  • 튜플을 유일하게 식별할 수 있는 하나의 속성 혹은 속성의 집합
  • 고객 릴레이션은 고객번호와 주민번호를 포함한 모든 속성의 집합이 슈퍼키가 됨
  • (주민번호), (주민번호, 이름), (고객번호, 이름, 주소), (고객번호)

후보키

  • 튜플을 유일하게 식별할 수 있는 속성의 최소 집합
  • 주문 릴레이션의 후보키는 2개의 속성을 합한 (고객번호, 도서번호)가 됨.
  • 참고로 이렇게 2개 이상의 속성으로 이루어진 키를 복합키(composite key)라고 함
  • (주민번호), (고객번호), (주민번호, 도서번호)

기본키

  • 여러 후보 키 중 하나를 선정해서 대표로 삼음
  • 고유한 값(유일성), NULL 허용X, 키 값 변동 없어야 함, 최대한 적은 수의 속성(최소성), 밑줄
  • (고객번호)

대리키

  • 기본키가 보안을 요하거나, 여러 개의 속성으로 구성되어 복잡하거나, 마땅한 기본키
    가 없을 때는 일련번호 같은 가상의 속성을 만들어 기본키로 삼는 경우가 있음. 이러
    한 키를 대리키(surrogate key) 혹은 인조키(artificial key)라고 함.
  • 대리키는 DBMS나 관련 소프트웨어에서 임의로 생성하는 값으로 사용자가 직관적으
    로 그 값의 의미를 알 수 없음

대체키

  • 기본 키로 선정되지 않는 후보 키
  • 고객 릴레이션의 경우 고객번호와 주민번호 중 고객번호를 기본키로 정하면 주민
    번호가 대체키가 됨
  • (주민번호)

외래키

  • 다른 릴레이션의 기본키를 참조하는 속성을 말함
  • 다른 릴레이션의 기본키를 참조하 관계 데이터 모델의 특징인 릴레이션 간의 관계(relationship)를 표현함
  • 외래키 사용 시 참조하는 릴레이션과 참조되는 릴레이션이 꼭 다른 릴레이션일 필요는 없음. 즉 자기 자신의 기본키를 참조할 수도 있음.

02. 무결성 제약 조건

데이터 무결성(신뢰성 있는 데이터) = Integrity

  • 데이터베이스에 저장된 데이터의 일관성과 정확성을 지키는 것

도메인 무결성 제약조건(Domain Constraint)

  • 데이터 형식(Type)
    • NULL/NOT NULL
    • DEFAULT
    • CHECK

개체 무결성 제약조건(기본키 제약)

  • 삽입 : 기본키 값이 같으면 삽입 제약(기본키 중복 허용 X) - NULL 허용하지 않음
  • 수정 : 기본키 값이 같거나 NULL로 수정 금지
  • 삭제 : 특별한 확인이 필요하지 않으면 즉시 수행

참조 무결성 제약조건(외래키)

  • 제약대상(튜플, 속성) - NULL 허용
  • 부모 릴레이션에 삽입
  • 자식 릴레이션에 삽입 : 테이블에 외래키 값이 없으면 삽입 금지

예제

  • SELECT, DELETE, UPDATE
  • 집계함수
--1) BOOK 테이블 bookid = 11 나머지 알아서 값을 입력하세요.
INSERT INTO BOOK VALUES(11, '역도의 기술', '굿스포츠', 80000);

--2) CUSTOMER 테이블 custid = 6 이름 기성용 나머지 알아서
INSERT INTO CUSTOMER VALUES(6, '기성용', '대한민국 서울' , '010-2034-4322');

-- 3) ORDERS 테이블에서 orderid=11 기성용 선수가 bookid=11 번인 책을 주문한 내용을 oprders 테이블
--적용
INSERT INTO ORDERS VALUES(11, 5, 11, 4000, '20/07/11');

commit;

-- 4) 고객 기성용이 주문한 내역 삭제하세요.
DELETE FROM ORDERS WHERE orderid = 11;

-- 5) BOOK테이블에서 bookid = 11 삭제

DELETE FROM BOOK WHERE bookid= 11;

-- 6) customer 테이블 custid = 6 삭제
DELETE FROM CUSTOMER WHERE CUSTID= 6;

commit;

-- 질의 3-15 고객이 주문한 도서의 총 판매액을 구하시오
-- 테이블 : orders 집계함수 : SUM(saleprice)
SELECT SUM(saleprice) AS 총판매액
FROM ORDERS;

-- 질의 3-16 2번 김연아 고객이 주문한 도서의 총 판매액을 구하시오.
SELECT SUM(saleprice) AS "김연아 고객의 총 판매액"
FROM ORDERS
WHERE custid=2;

-- 질의 3-17 고객이 주문한 도서의 총 판매액, 평균값, 최저가, 최고가를 구하시오.
-- 평균값 - AVG(), 최저가 - MIN(), 최고가 - MAX()
SELECT 
    SUM(saleprice) AS 총판매액, 
    AVG(saleprice) AS 평균값, 
    MIN(saleprice) AS 최저가, 
    MAX(saleprice) AS 최고가
FROM ORDERS;

-- 질의 3-18 마당서점의 도서 판매 건수를 구하시오.
-- 총 개수 - COUNT(속성)
SELECT COUNT(*) "총 판매 건수"
FROM ORDERS;
  • GROUP BY

    • GROUP BY를 하게 되면 SELECT에는 GROUP BY에서 지정한 속성과 집계함수만 올 수 있음

      -- 질의 3-19 고객별로 주문한 도서의 총 수량과 총 판매액을 구하시오.
SELECT CUSTID, 
    COUNT(*) AS "주문한 도서의 총 수량", 
    SUM(saleprice) AS "총 판매액"
FROM ORDERS
GROUP BY custid;

-- 질의 3-20 가격이 8,000원 이상인 도서를 구매한 고객에 대하여 고객별 주문 도서의
-- 총 수량을 구하시오. 단, 두 권 이상 구매한 고객만 구한다.
-- GROUP BY를 하게 되면 SELECT에는 GROUP BY에서 한 속성과 집계함수만 올 수 있음
SELECT CUSTID, 
    COUNT(*) AS "주문한 도서의 총 수량"
FROM ORDERS
WHERE saleprice >= 8000
GROUP BY custid
HAVING COUNT(*) >= 2;

조인

  • 박지성 고객이 주문한 도서의 총 구매액(orders) ,고객의 이름(customer)을 알고 싶다.
  • 총 구매액은 orders 테이블의 SUM(saleprice)

SQL은 여러 개의 테이블을 이용하여 질의하는 방법 제공

  • 조인(JOIN)
    • 두 개의 합체 해 보세요
    • customer orders 조건 없이 연결
-- 질의 3-21 고객과 고객의 주문에 관한 데이터를 모두 보이시오.
SELECT *
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CUSTID = ORDERS.CUSTID;

-- 질의 3-22 고객과 고객의 주문에 관한 데이터를 고객번호 순으로 정렬하여 보이시오.
SELECT *
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CUSTID = ORDERS.CUSTID
ORDER BY customer.custid ASC;

-- 질의 3-23 고객의 이름과 고객이 주문한 도서의 판매가격을 검색하시오.
SELECT CUSTOMER.NAME "고객이름", ORDERS.SALEPRICE "고객이 주문한 도서"
FROM CUSTOMER, ORDERS
WHERE customer.custid = orders.orderid;

-- 질의 3-24 고객별로 주문한 모든 도서의 총 판매액을 구하고, 고객별로 정렬하시오.
SELECT CUSTOMER.NAME "이름", SUM(SALEPRICE)"총 판매액"
FROM CUSTOMER, ORDERS
WHERE customer.custid = orders.orderid
GROUP BY customer.name
ORDER BY customer.name;

-- 질의 3-25 고객의 이름과 고객이 주문한 도서의 이름을 구하시오.
SELECT CUSTOMER.NAME "이름", BOOK.BOOKNAME "도서이름"
FROM CUSTOMER, BOOK, ORDERS
WHERE CUSTOMER.CUSTID = ORDERS.CUSTID AND ORDERS.BOOKID=BOOK.BOOKID
ORDER BY CUSTOMER.NAME;

-- 질의 3-26 가격이 20,000원인 도서를 주문한 고객의 이름과 도서의 이름을 구하시오.
SELECT CUSTOMER.NAME "이름", BOOK.BOOKNAME "도서이름"
FROM CUSTOMER, BOOK, ORDERS
WHERE CUSTOMER.CUSTID = ORDERS.CUSTID AND ORDERS.BOOKID=BOOK.BOOKID 
AND book.price = 20000
order by customer.name;

-- INNER 조인
SELECT CUSTOMER.NAME "이름", BOOKID "도서이름"
FROM CUSTOMER inner join orders 
ON CUSTOMER.CUSTID = ORDERS.CUSTID 
order by customer.name;

-- 쫒아가기, 조인 없이 하자면-----------------------------
SELECT CUSTID, NAME
FROM CUSTOMER
WHERE NAME = '추신수';

SELECT CUSTID, BOOKID
FROM ORDERS
WHERE CUSTID = 4;

SELECT BOKKID, BOOKNAME
FROM BOOK
WHERE CUSTID = 7;

SELECT BOKKID, BOOKNAME, PRICE
FROM BOOK
WHERE CUSTID = 8;
--------------------------------------
---- 질의 3-27 도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의
판매가격을 구하시오.
-- LEFT 조인은 NAME(왼쪽)은 완전 SALEPRICE(오른쪽)은 불완전
-- 1. 전용 표현
SELECT c.name, o.saleprice
FROM CUSTOMER c LEFT OUTER JOIN ORDERS o -- 별칭
ON c.custid = o.custid; -- 외부 조인

-- 2. 오라클 전용
SELECT NAME, saleprice
FROM CUSTOMER c, ORDERS o
WHERE c.custid = o.custid(+);

-- 오른쪽 조인
SELECT NAME, saleprice
FROM CUSTOMER c, ORDERS o
WHERE c.custid(+) = o.custid;
  • 부속질의

    • SELECT 문의 WHERE절에 또 다른 테이블의 결과를 이용해서 SELECT문을 괄호로 묶어서 질의어의 결과를 제공 => 부속질의, SUBQUERY

    • 서브 쿼리는 SQL문이다. SQL문의 결과는 테이블, 테이블 결과는 4 가지 중 한 개에 속함

      단일행 - 단일열 * 단일열(1X1)
      다중행 - 단일열 (NX1)
      단일행 - 다중행 (1XN)
      다중행 - 다중행 (NXN)

      -- 질의 3-29 도서를 구매한 적이 있는 고객의 이름을 검색하시오. => 2. 다중행 - 단일열 (NX1)
-- 1.
SELECT DISTINCT CUSTID
FROM ORDERS;
-- 2.
SELECT NAME
FROM CUSTOMER
WHERE CUSTID IN(1, 2, 3, 4);
-- 3.
SELECT NAME
FROM CUSTOMER
WHERE CUSTID 
IN(
SELECT DISTINCT CUSTID
FROM ORDERS);

-- 질의 3-30 대한미디어에서 출판한 도서를 구매한 고객의 이름을 보이시오.
SELECT BOOKID
FROM BOOK
WHERE PUBLISHER = '대한미디어';

SELECT CUSTID
FROM ORDERS
WHERE BOOKID IN(3,4);

SELECT NAME
FROM customer
WHERE CUSTID = 1;

SELECT NAME
FROM customer
WHERE CUSTID IN (
    SELECT CUSTID
    FROM ORDERS
    WHERE BOOKID IN(SELECT BOOKID
                        FROM BOOK
                        WHERE PUBLISHER = '대한미디어'));
  • 상관 부속 질의

    • 상위 부속질의의 투플을 이용하여 하위 부속 질의를 계산함.

    • 상위 부속질의와 하위 부속질의가 독립적이지 않고 서로 관련을 맺고 있음.

      -- 질의 3-31 출판사별로 출판사의 평균 도서 가격보다 비싼 도서이름를 구하시오.
SELECT b.publisher, AVG(b.price)
FROM book b
GROUP BY b.publisher;  

SELECT b1.BOOKNAME, b1.price
FROM book b1
WHERE b1.price > (
    SELECT AVG(b2.price)
    FROM BOOK b2 -- 튜플 변수 : 테이블 이름이 길거나 한 개의 테이블을 구분하기 위해 별칭을 붙여 사용
    WHERE b2.publisher = b1.publisher
    -- BOOK b2 = new BOOK(); b2에는 BOOKID, BOOKNAME, PUBLISHER, PRICE 등이 있음
);
  • 부속질의어와 조인은 여러 테이블을 하나의 SQL문에서 다룬다는 점은 같다.
  • 하지만 부속질의어 SELECT문에 나오는 결과 속성을 FROM 절의 테이블에서만 얻을수 있고, 조인은 조인한 모든 테이블에서 결과 속성을 얻어낼 수 있다.
  • 조인으로 부속질의어가 할 수 있는 모든 작업이 가능
  • 부속질의를 조인을 이용해서 작성해 보면, 부속질의가 편의성이 높다.
  • 한 개의 테이블에서만 결과를 얻는 여러 테이블의 질의는 조인보다 서브쿼리로 작성하는 것이 훨씬 편하기 때문이다.

함수

-- 1. ROUND(숫자, [반올림 위치 0 : 소스첫째자리, 정수로 갈 수록 -1씩 작아진다. 소수 아래 +1])
SELECT ROUND(1234.5678) AS ROUND,
       ROUND(1234.5678, 0) AS ROUND_0,
       ROUND(1234.5678, 1) AS ROUND_1,
       ROUND(1234.5678, 2) AS ROUND_2,
       ROUND(1234.5678, -1) AS ROUND_MINUS1,
       ROUND(1234.5678, -2) AS ROUND_MINUS2
  FROM DUAL;
  
-- 2. TRUNC(숫자, [버림위치])
SELECT TRUNC(1234.5678) AS TRUNC,
       TRUNC(1234.5678, 0) AS TRUNC_0,
       TRUNC(1234.5678, 1) AS TRUNC_1,
       TRUNC(1234.5678, 2) AS TRUNC_2,
       TRUNC(1234.5678, -1) AS TRUNC_MINUS1,
       TRUNC(1234.5678, -2) AS TRUNC_MINUS2
  FROM DUAL;
  
-- 3. CEIL(숫자) : 지정된 숫자와 가까운 정수찾기 FLOOR(숫자)
SELECT CEIL(3.14),
       FLOOR(3.14),
       CEIL(-3.14),
       FLOOR(-3.14)
  FROM DUAL;
  
-- 5. MOD() : 나머지 값 반환
SELECT MOD(15, 6),
       MOD(10, 2),
       MOD(11, 2)
  FROM DUAL

회고

이거 보니까 정보처리기사 공부랑 병행이 되는 것 같아 좋다. 뭔가 학교 다닐 때 둥둥 떠다니던 지식이 합쳐지는 기분?이다.

profile
GoldenDusk
내 지식을 기록하여, 다른 사람들과 공유하여 함께 발전하는 사람이 되고 싶다. 참고로 워드프레스는 아직 수정중
이전 포스트

멀티캠퍼스 백엔드 과정 23일차[7월4일] - SELECT, CREATE

다음 포스트

멀티캠퍼스 백엔드 과정 25일차[7월6일] -조인

0개의 댓글