교육 34DAY SQL

▶▶ 데이터 무결성과 트랜잭션 : 데이터베이스 운영 기본 규칙
▶ 데이터 무결성
- 데이터베이스에 저장된 데이터 값과 사용자가 의도한 데이터 값은 일치해야 함
- 데이터는 정확성, 유효성, 일관성, 신뢰성이 지켜져야 하며
  이를 어기는 잘못된 입력과 갱신 또는 삭제로부터 보호되어야 함
- 예를 들어 employees 테이블의 employee_id 열은 기본키로 주민등록번호와 같은 것이므로
  데이터 값을 구별할 수 있는 유일한 값이어야함
- 기본 키 값에 중복 값이 입력되거나 null 값이 입력된다면 데이터 식별에 큰 문제가 생길 수 있음
- salary 열의 데이터 값에 숫자가 아닌 '이천이백'과 같은 문자 값이 입력되어 있다면 이 문자 값을 salary로 
  인정해야 할지 말지 결정이 어려움
- 데이터베이스 관리의 기본 규칙이 지켜지지 않으면 데이터베이스 시스템을 운영하는 데 큰 혼란이 올 수 있음
- 데이터베이스의 데이터는 사용자의 목적에 맞게 입력되고 저장되어야 하며 규칙을 위배하지 않아야 함

데이터 무결성의 종류

▶ 제약 조건
- 정해 놓은 규칙에 맞는 데이터만 입력받고 규칙에 어긋나는 데이터는 거부하여 데이터 무결성을 지키는 방법

Ⅰ ) 기본 키 제약 조건 : UNIQUE + NOT NULL을 만족해야 함
                       테이블을 대표하여 각 행을 유일하게 식별하는 값이이어야 함

Ⅱ ) 외래 키 제약 조건 : 열 값이 부모 테이블의 참조 열의 값을 반드시 참조해야 함
                        참조되는 열은 UNIQUE하거나 기본키

Ⅲ ) 유일 키 : 중복된 값을 허용하지 않음 / 유일한 값으로 존재해야 함(null 값 허용 가능)

Ⅳ ) NOT NULL : null 값을 허용하지 않음 / 값을 반드시 입력해야 함

Ⅴ ) CHECK : 범위나 조건 등 지정된 값만 허용함

▶ 트랜잭션 : 데이터 처리의 기본 작업 단위
- 트랜잭션(transaction)이란 데이터베이스의 DML, 즉 삽입, 갱신, 삭제와 관련된 논리적인 작업을 말함
- 트랜잭션은 DML 실행과 동시성 제어를 위한 중요한 개념
- 트랜잭션은 데이터베이스의 데이터 무결성이 보장되는 상태에서 DML 작업을 완수하기 위한 기본 작업 단위
- 관계형 데이터베이스 시스템은 데이터를 처리할 때 트랜잭션을 통해 정상 종료나
  사용자 프로세스 실패나 시스템 실패와 같은 비정상 종료에 대해 데이터의 신뢰성과 일관성을 보장함
- 이슈에 필요한 대응 논리가 트랜잭션임
- 일반적으로 DML 실행과 실행에 대한 커밋/롤백 단계까지를 트랜잭션이라고 부름
- 실무에서는 데이터베이스에서 SELECT 문으로 데이터를 조회하고
  DML을 실행하여 종료하는 과정까지를 트랜잭션이라고 부름
- 트랜잭션의 성공과 실패 여부를 따라 '트랜잭션이 성공했다'와 '트랜잭션이 철회되었다'라고 표현함
- 트랜잭션은 정상적인 상황이라면 마지막 단계에서 커밋이나 롤백으로 종료됨

트랜잭션 과정

트랜잭션의 특징 (ACID)

▶ 트랜잭션의 특징
- 100만원의 잔액을 보유한 A계좌와 보유 금액이 없는 B계좌 사이의 계좌 이체를 예로 들어 ACID를 살펴보자

■ 원자성
- 이체 금액이 10만원이면 10만원이 완전히 이체되거나 전혀 이체되지 않는다는 의미
- 5만원까지만 이체되거나 9만원만 부분적으로 이체되는 경우는 없음 (all or nothing)

■ 일관성
- 트랜잭션이 완료되면 데이터베이스의 데이터는 일관되게 유지되어야함
- 예를 들어 A 계좌에서 10만원이 출금되었으면 B 계좌에는 10만원 입금되어야함

■ 고립성
- 트랜잭션이 완료되지 않은 동안에는 다른 트랜잭션이 참조하거나 변경할 수 없음
- 예를 들어 '가' 사용자의 A 계좌에서 이체가 실행되는 것에 대해서 '나' 사용자는 관여할 수 없음
- '가' 사용자의 이체 실행이 완료되지 않으면, '나' 사용자는 최대의 경우 계좌를 조회할 수 있을 뿐
  다른 계좌로 이체처리를 할 수 없음

■ 보존성
- 트랜잭션이 정상적으로 완료되면 해당 데이터는 저장되어 보존되어야 함
- 보존을 보장함으로써 데이터베이스 시스템의 신뢰성과 일관성을 유지할 수 있으며
  장애가 발생했을 때 경우 복구가 가능

▶ 동시성 제어
- 동시성 제어는 동시에 실행되는 여러 개의 트랜잭션이 작업을 성공적으로 마칠 수 있도록 지원함
- 은행이나 증권사 등 다중 사용자 (multi-user) 환경으로 구성된
  기업의 데이터베이스 시스템에서는 반드시 필요한 작업
- 트랜잭션은 동시성 제어와 매우 밀접하게 관련되어 있음

동시성 제어 기법

- 데이터 처리 작업을 할 때는 트랜잭션과 동시성 제어의 개념을 인지하고
  DML 명령어를 실행하여 신중하게 작업하기 바람
- 사실 동시성 제어는 데이터베이스 시스템이 자동으로 수행하므로
  사용자가 동시성 제어에 직접 관여할 일은 없음
- 동시성 제어는 다중 사용자와 트랜잭션을 제어하기 위한 관계형 데이터베이스의 기본원리

▼ ▼ ▼ ▼ ▼ 데이터베이스 설계 연습문제  ▼ ▼ ▼ ▼ ▼

<학생 데이터베이스 설계>

1. 방법학생 테이블(Student)방법
   - 학번(StudentID) - 기본키
   - 이름(Name)
   - 학년(Grade) 
   - 전공(Major)
   - 연락처(Phone)

2. 방법수강 테이블(Enrollment)방법
   - 수강ID(EnrollmentID) - 기본키
   - 학번(StudentID) - 외래키 (Student 테이블의 StudentID 참조)
   - 과목코드(CourseCode) - 외래키 (Course 테이블의 CourseCode 참조) 
   - 학점(Credits)
   - 성적(Grade)

3. 방법과목 테이블(Course)방법
   - 과목코드(CourseCode) - 기본키
   - 과목명(CourseName)
   - 학점(Credits)
   - 교수ID(ProfessorID) - 외래키 (Professor 테이블의 ProfessorID 참조)

4. 방법교수 테이블(Professor)방법
   - 교수ID(ProfessorID) - 기본키
   - 이름(Name)
   - 학과(Department)
   - 이메일(Email)

<학생 테이블(Student)>
StudentID: 20230001, Name: 김민수, Grade: 2, Major: 경영학과, Phone: 010-1234-5678
StudentID: 20230002, Name: 이영희, Grade: 3, Major: 컴퓨터공학과, Phone: 010-2345-6789
StudentID: 20230003, Name: 박철수, Grade: 1, Major: 경제학과, Phone: 010-3456-7890
StudentID: 20230004, Name: 최지영, Grade: 4, Major: 법학과, Phone: 010-4567-8901
StudentID: 20230005, Name: 강민호, Grade: 2, Major: 건축학과, Phone: 010-5678-9012

<수강 테이블(Enrollment)>
EnrollmentID: 1001, StudentID: 20230001, CourseCode: CS101, Credits: 3, Grade: 4.0
EnrollmentID: 1002, StudentID: 20230002, CourseCode: BUS201, Credits: 4, Grade: 3.5
EnrollmentID: 1003, StudentID: 20230003, CourseCode: ECO301, Credits: 3, Grade: 4.3
EnrollmentID: 1004, StudentID: 20230004, CourseCode: LAW401, Credits: 4, Grade: 4.0
EnrollmentID: 1005, StudentID: 20230005, CourseCode: ARC501, Credits: 3, Grade: 3.7

<과목 테이블(Course)>
CourseCode: CS101, CourseName: 프로그래밍 기초, Credits: 3, ProfessorID: P001
CourseCode: BUS201, CourseName: 경영학 원론, Credits: 4, ProfessorID: P002
CourseCode: ECO301, CourseName: 미시경제학, Credits: 3, ProfessorID: P003
CourseCode: LAW401, CourseName: 민법 기초, Credits: 4, ProfessorID: P004
CourseCode: ARC501, CourseName: 건축 설계, Credits: 3, ProfessorID: P005

<교수 테이블(Professor)>
ProfessorID: P001, Name: 김교수, Department: 컴퓨터공학과, Email: kim@university.edu
ProfessorID: P002, Name: 이교수, Department: 경영학과, Email: lee@university.edu
ProfessorID: P003, Name: 박교수, Department: 경제학과, Email: park@university.edu
ProfessorID: P004, Name: 최교수, Department: 법학과, Email: choi@university.edu
ProfessorID: P005, Name: 강교수, Department: 건축학과, Email: kang@university.edu

CREATE TABLE students (
student_id VARCHAR2(30) PRIMARY KEY, 
student_name VARCHAR2(30) NOT NULL, 
grade NUMBER,
major VARCHAR2(20), 
phone VARCHAR2(20) 
);

CREATE TABLE enrollment (
enrollment_id NUMBER PRIMARY KEY,
student_id VARCHAR2(30),
course_code VARCHAR2(20),
credits NUMBER,
grade VARCHAR2(10),

CONSTRAINT FK_student_id 
FOREIGN KEY (student_id) 
REFERENCES students (student_id),

CONSTRAINT FK_course_code
FOREIGN KEY (course_code)
REFERENCES course(course_code)
);

CREATE TABLE course (
course_code VARCHAR2(20) PRIMARY KEY,
course_name VARCHAR2(50),
credits NUMBER,
professor_id VARCHAR2(20),

CONSTRAINT FK_professor_id
FOREIGN KEY (professor_id)
REFERENCES professor (professor_id)
);

CREATE TABLE professor (
professor_id VARCHAR2(20) PRIMARY KEY,
professor_name VARCHAR2(20),
department VARCHAR2(20),
email VARCHAR2(50)
);

INSERT INTO students VALUES ('20230001', '김민수', 2, '경영학과', '010-1234-5678');
INSERT INTO students VALUES ('20230002', '이영희', 1, '컴퓨터공학과', '010-2345-6789');
INSERT INTO students VALUES ('20230003', '박철수', 3, '경제학과', '010-3456-7890');
INSERT INTO students VALUES ('20230004', '최지영', 4, '법학과', '010-4567-8901');
INSERT INTO students VALUES ('20230005', '강민호', 2, '건축학과', '010-5678-9012');
commit;


INSERT INTO enrollment VALUES (1001, '20230001', 'CS101', 3, '4.0');
INSERT INTO enrollment VALUES (1002, '20230002', 'BUS201', 4, '3.5');
INSERT INTO enrollment VALUES (1003, '20230003', 'ECO301', 3, '4.3');
INSERT INTO enrollment VALUES (1004, '20230004', 'LAW401', 4, '4.0');
INSERT INTO enrollment VALUES (1005, '20230005', 'ARC501', 3, '3.7');
commit;

INSERT INTO course VALUES ('CS101', '프로그래밍 기초', 3, 'P001');
INSERT INTO course VALUES ('BUS201', '경영학 원론', 4, 'P002');
INSERT INTO course VALUES ('ECO301', '미시경제학', 3, 'P003');
INSERT INTO course VALUES ('LAW401', '민법 기초', 4, 'P004');
INSERT INTO course VALUES ('ARC501', '건축 설계', 3, 'P005');
commit;

INSERT INTO professor VALUES ('P001', '김교수', '컴퓨터공학과', 'kim@university,edu');
INSERT INTO professor VALUES ('P002', '이교수', '경영학과', 'lee@university,edu');
INSERT INTO professor VALUES ('P003', '박교수', '경제학과', 'park@university,edu');
INSERT INTO professor VALUES ('P004', '최교수', '법학과', 'choi@university,edu');
INSERT INTO professor VALUES ('P005', '강교수', '건축학과', 'kang@university,edu');
commit;

-- 1번 학생 테이블에서 모든 학생의 이름과 학년을 조회하시오.
SELECT student_name, grade
FROM students;

-- 2번 과목 테이블에서 3학점 과목의 과목명과 교수 ID를 조회하시오.
SELECT course_name, professor_id
FROM course
WHERE credits = 3;

-- 3번 학생 테이블에서 경영학과 학생들의 정보를 조회하시오.
SELECT *
FROM students
WHERE major = '경영학과';

-- 4번 수강 테이블에서 4.0 이상의 성적을 받은 학생의 수강 내역을 조회하시오.
SELECT *
FROM enrollment
WHERE grade >= '4.0';

-- 5번 학생 테이블에서 학년 순으로 정렬하여 학생 정보를 조회하시오.
SELECT *
FROM students
ORDER BY grade;

-- 6번 교수 테이블에서 이름 순으로 정렬하여 교수 정보를 조회하시오.
SELECT *
FROM professor
ORDER BY professor_name; 

-- 7번 학생 테이블에서 학생 수와 평균 학년을 구하시오.
SELECT COUNT(*) AS 학생수,
       AVG(grade) AS 평균학년
FROM students;

-- 8번 과목 테이블에서 개설된 과목의 총 학점 수를 구하시오.
SELECT TO_NUMBER (SUM(credits)) AS 총학점
FROM course;

-- 9번 학생 테이블과 수강 테이블을 JOIN하여 학생 이름, 과목명, 학점, 성적을 조회하시오.
SELECT s.student_name AS 학생이름,
       c.course_name AS 과목명,
       e.credits AS 학점,
       e.grade AS 성적
FROM enrollment e
JOIN students s ON e.student_id = s.student_id
JOIN course c ON e.course_code = c.course_code;

-- 10번 과목 테이블과 교수 테이블을 JOIN하여 과목명, 교수명, 소속 학과를 조회하시오.
SELECT c.course_name AS 과목명,
       p.professor_name AS 교수명,
       p.department AS 소속학과
FROM course c
JOIN professor p ON c.professor_id = p.professor_id;

-- 11번 수강 테이블에서 4학점 과목을 수강한 학생의 이름, 과목명, 성적을 조회하시오.
SELECT s.student_name AS 학생이름,
       c.course_name AS 과목명,
       e.grade AS 성적
FROM enrollment e
JOIN students s ON e.student_id = s.student_id
JOIN course c ON e.course_code = c.course_code
WHERE c.credits = 4;

-- 12번 학생 테이블과 수강 테이블, 과목 테이블을 JOIN하여 경영학과 학생들의 평균 성적을 구하시오.
SELECT AVG(e.grade) AS 경영학과_평균성적
FROM students s
JOIN enrollment e ON s.student_id = e.student_id
JOIN course c ON e.course_code = c.course_code
WHERE s.major = '경영학과';

-- 13번 학생들이 3학점 이상의 과목을 수강한 경우, 해당 학생의 이름, 수강한 과목명, 성적, 
--     그리고 담당 교수명을 조회하는 SQL 쿼리를 작성하시오.
SELECT s.student_name AS 학생이름,
       c.course_name AS 과목명,
       e.grade AS 성적,
       p.professor_name AS 교수명
FROM enrollment e
JOIN students s ON e.student_id = s.student_id
JOIN course c ON e.course_code = c.course_code
JOIN professor p ON c.professor_id = p.professor_id
WHERE c.credits >= 3;