6월 27일_SQL(데이터의 무결성, 제약조건)

# 데이터의 무결성

• 데이터베이스에 저장된 데이터 값과 사용자가 의도한 데이터 값은 일치해야 함
• 정확성, 유효성, 일관성, 신뢰성이 지켜져야 함
• 잘못된 입력과 갱신 또는 삭제로부터 보호되어야 함
• 데이터를 처리할 때마다 부적절한 데이터가 입력되는지 검사하여 데이터의 결점 없음, 즉 무결성을 유지
• 이를 지키기 위한 기본 규칙을 데이터 무결성(data integrity)이라고 함

 

** 데이터 무결성의 종류

개체 무결성 (entity integrity)	기본 키(primary key)로 선택된 열은 고유해야 하며 null 값을 가질 수 없다.
참조 무결성 (reference integrity)	기본 키와 외래 키의 관계이다. 외래 키가 있는 테이블의 경우에는 기본 키와 외래 키 간의 관계가 항상 유지됨을 보장한다. 참조하는 외래 키가 존재하면 행은 삭제될 수 없고 기본 키도 변경될 수 없다.
영역 무결성 (domain integrity)	데이터 형태, 범위, 기본값, 유일성에 관한 제한이다. 주어진 속성 값은 그 속성이 정의된 도메인에 속한 값이어야 한다.
비즈니스 무결성 (business integrity)	사용자의 업무 규칙에 따른 비즈니스적인 제약 조건이다.

 

 

# 제약조건

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

** 제약조건의 종류

기본 키 제약 조건	UNIQUE + NOT NULL을 만족. 또한 테이블을 대표하여 각 행을 유일하게 식별하는 값이어야 함
외래 키 제약 조건	열 값이 부모 테이블의 참조 열의 값을 반드시 참조. 참조되는 열은 UNIQUE하거나 기본 키.
유일 키	중복된 값을 허용하지 않음. 유일한 값으로 존재해야 한다(null 값 허용 가능).
NOT NULL	null 값을 허용하지 않음. 값을 반드시 입력해야 함.
CHECK	범위나 조건 등 지정된 값만 허용

 

 

# 트랜잭션 : 데이터 처리의 기본 작업 단위

• 데이터베이스의 DML, 즉 삽입, 갱신, 삭제와 관련된 논리적인 작업
• 트랜잭션은 DML 실행과 동시성 제어를 위한 중요한 개념

이미지 참고 - https://thebook.io/006977/0271/

 

 

** 트랜잭션 특징(ACID)

원자성(Atomicity)	트랜잭션의 처리가 완전히 끝나지 않았을 경우에는 전혀 이루어지지 않은 것과 같아야 한다(all or nothing).
일관성(Consistency)	트랜잭션의 실행이 성공적으로 완료되면 데이터베이스는 모순 없이 일관성이 보존된 상태여야 한다.
고립성(Isolation)	어떤 트랜잭션도 다른 트랜잭션의 부분적 실행 결과를 볼 수 없다
지속성(Durability)	트랜잭션이 성공하면 트랜잭션의 결과를 영구적으로 보장해야 한다.

 

 

** 트랙잭션 동시성 제어 기법

락킹(locking)	트랜잭션이 데이터에 잠금(lock)을 설정하면 다른 트랜잭션은 해당 데이터에 대해 잠금이 해제(unlock)될 때까지 접근/수정/삭제가 불가하다.
타임스탬프(timestamp)	시스템에서 생성하는 고유 번호인 타임스탬프를 트랜잭션에 부여함으로써 트랜잭션 간의 접근 순서를 미리 정한다.
적합성(validation) 검증	먼저 트랜잭션을 수행하고 트랜잭션을 종료할 때 적합성을 검증하여 데이터베이스에 최종 반영한다.

 

---

 

# 파이썬에서 오라클 데이터베이스 불러오기

 

파이썬에서 오라클 모듈 불러오기전에 오라클 확장자 설치

통합터미널에서 아래 문장 입력후 엔터 > 설치 완

pip install oracledb

 

# 오라클 데이터 베이스 연결
import oracledb

# 데이터 베이스 연결
oracledb.init_oracle_client()   # 파이썬 버전오류 해결 구문
username = 'HR'  #오라클 사용자 이름
password = '1234' #오라클 비밀번호
dsn = 'localhost:1521/xe' #데이터베이스 연결문자열 (예:호스트이룸:포트/서비스이름')

conn = oracledb.connect (user = username, password = password, dsn = dsn)


#커서 생성
cur = conn.cursor()

#SQL 쿼리실행
cur.execute('SELECT MEM_ID,MEM_NAME,ADDR,DEBUT_DATE FROM MEMBERS')

#결과 출력
print('멤버ID   멤버이름    주소    데뷔년도')
print('-'*100)

#while문 사용방법
while True:
   row = cur.fetchone()     #쿼리 결과에서 한 행을 가져옴
   if row is None: #더 이상 가져올 행이 없으면 루프 종료
     break
   # 자리수 맞춰주기
   print(f'{row[0]:<5} {row[1]:<15} {row[2]:<20} {row[3].strftime('%Y-%m-%d'):<10}')
#    print(f'{row[0]:<5} {row[1]:<15} {row[2]:<20} {row[3]:<10}')   #데이터 타입이 맞지 않아 오류 발생

cur.close()
conn.close()

 

# 오라클 데이터 베이스 연결
import oracledb

# 데이터 베이스 연결
oracledb.init_oracle_client()   # 파이썬 버전오류 해결 구문
username = 'HR'  #오라클 사용자 이름
password = '1234' #오라클 비밀번호
dsn = 'localhost:1521/xe' #데이터베이스 연결문자열 (예:호스트이룸:포트/서비스이름')

conn = oracledb.connect (user = username, password = password, dsn = dsn)


#커서 생성
cur = conn.cursor()

#SQL 쿼리실행
cur.execute('SELECT MEM_ID,MEM_NAME,ADDR,DEBUT_DATE FROM MEMBERS')

#결과 출력
print('멤버ID   멤버이름    주소    데뷔년도')
print('-'*100)

#for문 사용방법
for row in cur:
   # 자리수 맞춰주기
   print(f'{row[0]:<5} {row[1]:<15} {row[2]:<20} {row[3].strftime('%Y-%m-%d'):<10}')

cur.close()
conn.close()

 

# 오라클 데이터 베이스 연결
import oracledb

# 데이터 베이스 연결
oracledb.init_oracle_client()   # 파이썬 버전오류 해결 구문
username = 'HR'  #오라클 사용자 이름
password = '1234' #오라클 비밀번호
dsn = 'localhost:1521/xe' #데이터베이스 연결문자열 (예:호스트이룸:포트/서비스이름')

conn = oracledb.connect (user = username, password = password, dsn = dsn)


#커서 생성
cur = conn.cursor()

#SQL 쿼리실행
cur.execute('SELECT MEM_ID,MEM_NAME,ADDR,DEBUT_DATE FROM MEMBERS')

#결과 출력
print('멤버ID   멤버이름    주소    데뷔년도')
print('-'*100)

#while문 사용방법 다른 표현문법
while True:
   row = cur.fetchone()     #쿼리 결과에서 한 행을 가져옴
   if row is None: #더 이상 가져올 행이 없으면 루프 종료
     break
   data1 = row[0]
   data2 = row[1]
   data3 = row[2]
   data4 = row[3].strftime('%Y-%m-%d')
   #자리수 맞춰주기
   print('%5s  %15s  %20s  %s' % (data1, data2, data3, data4))

cur.close()
conn.close()

 

# 오라클 데이터 베이스 연결
import oracledb

# 데이터 베이스 연결
oracledb.init_oracle_client()   # 파이썬 버전오류 해결 구문
username = 'HR'  #오라클 사용자 이름
password = '1234' #오라클 비밀번호
dsn = 'localhost:1521/xe' #데이터베이스 연결문자열 (예:호스트이룸:포트/서비스이름')

conn = oracledb.connect (user = username, password = password, dsn = dsn)


#커서 생성
cur = conn.cursor()

#SQL 쿼리실행
#긴 쿼리 구문 한줄에 적는 방법
query = """SELECT S.이름 AS 학생이름, C.과목명 AS 수강과목, E.성적, P.이름 AS 담당_교수명, P.이메일
            FROM 학생 S
            JOIN 수강 E ON S.학번 = E.학번
            JOIN 과목 C ON C.과목코드 = E.과목코드
            JOIN 교수 P ON C.교수ID = P.교수ID"""
cur.execute(query)

#결과 출력
print('학생이름   수강과목    성적    담당교수명    이메일')
print('-'*100)

while True:
   row = cur.fetchone()     #쿼리 결과에서 한 행을 가져옴
   if row is None: #더 이상 가져올 행이 없으면 루프 종료
     break
   # 자리수 맞춰주기
   print(f'{row[0]:<5} {row[1]:<10} {row[2]:<5} {row[3]:<5} {row[4]:<20}')

cur.close()
conn.close()