# 3.8 중첩하위질의 (nested subquery)
select-from-where 에서 모두 중첩된 하위 질의 가능하다
# in접속사
집합 멤버십을 테스트. 반대로는 not in 집합 멤버십의 부재
바깥 sql이 안쪽 sql에 들어있는 것들만 출력
- 2009년 가을 학기와 2010 봄 학기에 둘 다 있는 수업을 구하라
in 접속사 이용해 중첩질의
select distinct course_id
from section
where semester='Fall' and year = 2009 and
course_id in (select course_id
from section
where semester = 'Spring' and year = 2010)
- 2009년 가을에는 있지만 2010년 봄에는 없는 수업을 구하라
외부 sql맞고, 내부 sql 아닌 것
select distinct course_id
from section
where semester='Fall' and year = 2009 and
course_id not in (select course_id
from section
where semester = 'Spring' and year = 2010)
# in과 not in연산자들은 열거형 집합에서도 사용할 수 있다.
- Mozart도 Einstein도 아닌 교수를 선택하는 질의
select distinct name
from instructor
where name not in ('Mozart', 'Einstein')- ID 110011의 교수가 가르치는 수업 분반을 수강하는 학생의 수를 구하라
(sql에서는 where 콜롬명 한개만 가능)
select count(distinct ID)
from takes
where course_id in (select course_id
from teaches
where teaches.ID = 10101);
takes의 행은 primary key로 작동
# 3.8.2 집합비교
# >some (>any 도 결과 같음)
하나 이상보다 큰
# =some은 in과 동일
-생물학과의 적어도 한 교수보다도 급여가 많은 모든 교수들의 이름을 구하라
select name
from instructor
where salary > some (select salary
from instructor
where dept_name = 'Biology')=같은코드=
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Biology'
5 not in (0,5) = False // 5가 (0,5)에 들어있으므로
5 != some (0,5) = True // 5가 0(값들중 한 값)과 같지 않기 때문에
5 > some (0,5,6) = true
5 < some (0,5) = false
5 = some (0,5) = true
5 <> some (0,5) = true
(= some)은 in 과 같음
(<> some) 은 not in 과 같지 않음
#all
select *
from instructor
where salary > all (select salary
from instructor
where dept_name = 'Biology')
select *
from instructor
where dept_name = 'Biology'
생물학과의 교수들중 봉급의 최댓값은 77036이므로
모든 교수들중 봉급이 77036이상인 교수들만 출력
5 > all (0,5,6) = false
5 < all (6,10) = true
5 = all (4,5) = false
5 <> all (4,6) = true
(<> all) 은 not in 과 같음
(= all) 은 in 과 같지 않음
# exist
그 사실이 있냐없냐만 중요
- 2009년 가을학기 S 와 2010년 봄학기 T에 모두 진행된 강좌들 (smallDB)
select course_id
from section as S
where semester = 'Fall' and year = 2009 and
exists( select *
from section as T
where semester = 'Spring' and year = 2010
and S.course_id=T.course_id)CS-101
# not exist
하위 질의의 결과물에 투플이 존재하지 않음을 테스트할 수 있다.
- 생물학과에서 제공하는 모든 수업을 수강하는 학생을 구하라
밖의 select는 각각의 학생을 갖고, 학생이 수강하는 모든 수업들의 집합이 생물학과의 모든 수업의 집합을 포함하는지 테스트
select distinct S.id, S.name
from student as S
where not exists ( (select course_id
from course
where dept_name = 'Biology')
except
(select T.course_id
from takes as T
where S.id = T.id) )해당 조건에 맞는 결과들이 있으므로 출력이 안됨
exists로 변경시 출력됨
# unique (sql 지원안됨)
하위 질의가 중복된 투플을 가지지 않으면 true
- 2009년에 많아야 한 번 제공된 모든 수업을 구하라
select T.course_id
from course as T
where unique (select R.course_id
from section as R
where T.course_id = R.course_id
and R.year = 2009)--> where 1 = count(*) 형식으로 unique대체해서 사용 가능
위 코드와 같은 코드 (실행가능한코드)
select T.course_id
from course as T
where 1 = (select count(R.course_id)
from section as R
where T.course_id = R.course_id and
R.year = 2009)- 2009년 적어도 두 번 제공되는 모든 수업을 구하라
select T.course_id
from course as T
where not unique (select count(R.course_id)
from section as R
where T.course_id = R.course_id and
R.year = 2009)select T.course_id
from course as T
where 1< (select count(R.course_id)
from section as R
where T.course_id = R.course_id and
R.year = 2009)
# 3.8.5 From 절의 하위 질의
- 평균 급여가 42000 이상인 학과의 교수들의 평균 급여를 구하라
select dept_name, avg_salary
from (select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name) as dept_avg(dept_name, avg_salary)
where avg_salary>42000;
- 각 학과의 총 급여의 최대값을 구하라
select max(tot_salary) as max_tot_salary
from (select dept_name, sum(salary)
from instructor
group by dept_name) as dept_total(dept_name, tot_salary)
# lateral (sql지원안됨 생략)
# with
--> 가장 많은 예산을 가진 학과
with max_budget (value) as
(select max(budget)
from department)
select dept_name, budget
from max_budget,department
where department.budget = max_budget.value--> 학과의 총 급여가 평균 학과의 총 급여보다 많은 모든 학과
with dept_total(dept_name, value) as
(select dept_name, sum(salary)
from instructor
group by dept_name),
dept_total_avg(value) as
(select avg(value)
from dept_total)
select dept_name
from dept_total, dept_total_avg
where dept_total.value >= dept_total_avg.value
## 데이터베이스의 변경 삽입, 삭제, 변경
# 삭제 delete
transaction
폭격맞아도 DBMS는 버텨야함
ACID
- Atomicity : all or nothing 데이터.
- Consistency
- Isolation
- Durability :
삭제의 단위는 row (행) 별로
drop table 은 모두 삭제 (존재자체가 사라짐)
delete from table은 구조는 남아있고 내부의 데이터(행)만 사라짐
--->instructor 릴레이션에서 모든 투플을 삭제. instructor 릴레이션 자체는 존재하지만, 비어있다
delete from instructordefault : auto commint ON
commit : ALL (한개의 sql문장 단위로)
rollback : NOTHING
begin transaction ~sql 문장~ commit transaction
비깃과 커밋으로 문장들을 묶을 수 있다.
begin transaction ~sql 문장 ~ rollback transaction
begin실행하고 삭제하는 sql을 실행한 뒤 rollback을 실행하면 삭제되었던 데이터들이 다시 복구과 됨 (nothing ,원위치)
--> 재무과의 교수들과 관련된 instructor릴레이션의 모든 투플을 삭제하라
delete from instructor
where dept_name = 'Finance'
--> 급여가 13,000에서 15,000 사이인 모든 교수들을 삭제하라
delete from instructor
where salary between 13000 and 15000
--> Watson 건물에 위치한 학과와 관련된 instructor 릴레이션의 모든 교수를 삭제하라
delete from instructor
where dept_name in (select dept_name
from department
where building = 'Watson')
# 삽입 insert
삽입도 삭제와 마찬가지로 행단위로
삽입이 안되는 경우 primary key 유일성을 갖추지 못할 때
insert into course
values ('CS-437', 'Database Systems', 'Comp. Sci.', 4)value값의 순서와 course의 기본 구조의 형식이 다를때는 모두 써줘야함
insert into course(course_id, title, credits, dept_name)
values ('CS-437', 'Database Systems', 4, 'Comp. Sci.')null 값 넣기, 테이블 작성 파일에서 조건이 not null이 아닐때만 삽입 가능
insert into course
values ('3003', 'Green', 'Finance', null)--> 컴공 교수들의 봉급을 2배로 올려줌
update instructor
set salary = salary *2
where dept_name = 'Comp. Sci.'# 갱신 update
--> 모든 교수들의 급여 5퍼세트 인상
update instructor
set salary = salary *1.05
--> 급여가 70000 미만인 교수들만 급여 인상
update instructor
set salary = salary * 1.05
where salary < 70000;
--> 평균보다 적은 급여를 받는 교수들의 급여를 5% 인상하라
update instructor
set salary = salary *1.05
where salary < (select avg(salary)
from instructor)update문의 순서가 중요하다. 따라서 case구문을 이용하기도
update instructor
set salary = case
when salary <= 100000 then salary * 1.05
else salary * 1.03
endupdate student
set tot_cred = (select sum(course.credits)
from takes, course
where student.id = takes.id and
takes.course_id = course.course_id and
takes.grade!='F' and
takes.grade is not null)매년 학생들의 총 이수학점을 업데이트하는데, 이때 금학기에 이수한 학점 F가 아니여야 하고 null값이 아니여야함
# 조인식 join
자연 조인(natural join) , 두 테이블 간의 연관이 있어야 함.
join .. on : on은 조인된 릴레이션에 대한 조건을 정할 수 있다. where과 같은 기능
select *
from student join takes on student.ID=takes.ID
--> 같은 결과
select *
from student, takes
where student.ID=takes.ID--> 모두 같은 결과
select *
from course join prereq on course.course_id = prereq.course_id
select *
from course inner join prereq on course.course_id = prereq.course_id
select *
from course, prereq
where course.course_id = prereq.course_iddefault 는 inner join
# 외부 조인 outer join
left outer join
right outer join
full outer join
select *
from course join prereq on course.course_id = prereq.course_id
order by course.course_id
select *
from course left join prereq on course.course_id = prereq.course_id
order by course.course_id
그냥 join에서 선수과목이 있는 것들을 제외하고는 NULL로 표시됨
(distinct를 통해 선수과목이 겹치는 과목들이 21개가 있음. 따라서 join하고 결과가 221 행이 나올 것임)
join 완벽정리 블로그 https://pearlluck.tistory.com/46
DB JOIN 정리(INNER/LEFT/RIGHT/OUTER)
join(조인) 둘 이상의 테이블을 연결해서 데이터를 검색하는 방법 연결하려면 테이블들이 적어도 하나의 컬럼을 공유하고 있어야함 이 공유하고 있는 컬럼을 PK 또는 FK값으로 사용 종류 1. INNER
pearlluck.tistory.com
# 뷰 views
논리적 모델의 일부로서가 아니고 가상적인 릴레이션으로서 사용자에게 보이는 릴레이션을 뷰(view)라고 한다.
views 뷰는 선언만 할 뿐 데이터를 가지고 있지 않다 ** 중요 **
create view faculty as --> faculty 뷰 생성
select ID, name, dept_name
from instructorcreate view departments_total_salary(d_name, total_salary) as
select dept_name , sum(salary)
from instructor
group by dept_name
select dept_name , sum(salary)
from instructor
group by dept_name
와 같은 결과가 나오고 칼럼 이름은 d_name, total_salary로 정해지게 됨.
- physics_fall_2006 뷰 생성 : 2006년 가을 학기에 물리학과에서 제공한 모든 수업의 정보와 수업이 이루어진 건물과 강의실 번호의 목록을 가지고 있는 뷰
create view physics_fall_2006 as
select course.course_id, sec_id, building, room_number
from course, section
where course.course_id=section.course_id
and course.dept_name = 'Physics'
and section.semester='Fall'
and section.year='2006'
뷰로 뷰를 생성가능
create view physics_fall_2006_Taylor as
select *
from physics_fall_2006
where building = 'Taylor'
select *
from physics_fall_2006_Taylor
--> 각 학과별 모든 교수의 연봉의 합
create view departments_total_salary(dept_name, total_salary) as
select dept_name, sum(salary)
from instructor
group by dept_namecreate view physics_fall_2009_watson as
select course_id, room_number
from physics_fall_2009
where building= 'Watson';
--> 같은 sql. 점점 확장된다고 보면 됨
create view physics_fall_2009_watson as
(select course_id, room_number
from (select course.course_id, building, room_number
from course, section
where course.course_id = section.course_id
and course.dept_name = 'Physics'
and section.semester = 'Fall'
and section.year = '2009')
where building= 'Watson')# view 생성 시 고려사항
view를 남용하지 말 것!!
# 트랜잭션
commit work
rollback work
## 무결성 제약조건 Integrity constraints
데이터 변경이 데이터 일관성(consistency)에 손실을 초래하지 않음을 보장하는데 이용.
무결성 제약조건은 데이터베이스 손상방지.
ex)
교수 이름은 null 이 될 수 없다.
서로 다른 교수가 같은 ID를 가질 수 없다.
course 릴레이션에서 모든 학과 이름은 department 릴레이션의 학과 이름과 일치해야 한다.
학과의 예산은 $0.00보다 항상 커야 한다.
무결성이 발생할때 : insert, delete, update할 시
meta data : Data about data --> Data Dictionary 사전 (System Catalog)
# 단일 릴레이션에서의 제약조건
- primary key --> not null 개체무결성제약조건
- not null
- unique
- check (<predicate>)
# unique
테이블 변경하기 :
alter table instructor
add unique(name)
unique는 내가 원하는대로 동작하지 않을 수 있음
# check
check(P)절은 릴레이션의 모든 투플에 의해 만족되는 술어 P를 명세한다.
명시된 조건들을 만족하는 것을 보장. 더 강력한 타입 시스템.
학기 조건 : 계절 중 하나여야 한다.
year의 조건 : 1702년 이상 2099년 이하
check(P)절은 릴레이션의 모든 투플에 의해 만족되는 술어 P를 명세한다.
명시된 조건들을
# unique옵션 삭제해보기
alter table course
add constraint title_UNI unique(title, course_id)
alter table course
drop constraint title_UNI
# 개체 무결성 제약조건
존재하는 사실은 유일하게 식별돼야 함.
- primary key
# 참조 무결성 references integrity
존재하지 않는 사실은 참조하면 안됨.
* foreign키가 primary키에 꼭 매칭되지 않아도 된다.
강좌를 지우면 교수도 지워야하고, 수강한 학생들도 삭제된다.
한 사실이 없어짐에 따라서, 줄줄이 연관된 사실이 사라진다. 존재하지 않는 사실은 참조하면 안되므로.
# on delete cascade
# on update cascade
# cascade의 작업 : set null, set default, restrict 제한하다
예를 들어, on delete restrict라고 되어있으면 지우는 것에 제약을 둬서 삭제를 제한하게하는 작업
insert할때도 개체무결성 제약조건에 해당함.
on delete NO ACTION
# sql 날짜와 시간 타입 date, time, timestamp
date: (4개의 숫자를 가지는) 년도, 월, 일로 구성되는 날짜
예 : date ‘2005-7-27’
time: 그날의 시간, 시, 분, 초
예 : time ‘09:00:30’ time ‘09:00:30.75’
timestamp: date 와 time 의 조합
예 : timestamp ‘2005-7-27 09:00:30.75’
# create type
새로운 타입을 만든다.
create type Dollars as numeric (12,2)
create type Dollars as numeric (12,2) not null
# Domains
sql에서는 다루기에는 좀 큰 개념
# 대형 객체 타입 Large-Object Types
create table A (
C1 int,
C2 BLOB // CLOB --> 코드화,이진데이터 // 문자데이터
)
# 인덱스 index (2학기 수업)
Index 왜? --> 검색성능을 향상시키기 위해서
create index studentID on student(ID)
--> 인덱스 제거하기
drop index student.studentID# 접근제어 access control
grant / revoke
grant select on instructor 유저1,유저2,유저3
접근의 종류
select, insert, update, delete, all privileges
# 5.1 프로그래밍언어에서 sql접근하는 방법
1. 동적 SQL :
범용 프로그램은 함수의 집합이나 메소드를 사용해서 데이터베이스 서버에 접속하고 통신할 수 있다.
동적 SQL은 프로그램이 런타임에 문자열로 SQL질의를 만들어 제출하고, 한 번에 하나의 투플씩 프로그램 변수로 결과를 가져온다. SQL의 동적 요소는 프로그램이 실행 시간에 SQL 질의를 구성하고 완료할 수 있도록 한다.
2. 내장 SQL :
동적 SQL처럼 내장 SQL도 프로그램이 데이터베이스 서버와 소통할 수 있는 수단을 제공한다.
그러나, 전처리기를 사용해 컴파일시에 SQL구문을 확인.
전처리기가 SQL전달 -> 적당한 코드와 함수 호출로 변경 -> 프로그램 언어 컴파일러 호출
user <<---> SQL -> (SSMS) SQLserver <<---> Database
Java program <<----> SQL---JDBC----> SQLserver <<---> Database
C, C++, C# program <<----> SQL----> ADO.NET---->ODBC----> SQLserver <<---> Database
# JDBC 와 ODBC (database connectivity)
API란 ? 어플리케이션 프로그램 인터페이스 = 라이브러리 함수
library (set of functions) 함수들의 집합으로 이루어진 큰 덩어리
process <--> process 프로세스와의 커넥션
IPC (inter process communication)
자바와 jdbc사이의 연결될 객체를 sql에게 넘겨주고
넘겨받은 객체로 작업을 처리함.
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://localhost:49679;database=largeDB;integratedSecurity=true";
Connection conn = DriverManager.getConnection(connectionUrl);database = largeDB / smallDB 선택적으로 사용가능
intergratedSecurtiy = ture
localhost : 내 TCP포트 번호 (49679)
- 포트번호 : SQL서버의 프로세스와 다른 프로세스가 통신할때 할당된 포트번호
connection conn : 상태 객체
SSMS시작할때 인증방법 window - sql 서버로 연결
JDBC 실습
12주차
* TCP/IP 포트번호를 매번 확인해서 코드 수정하기
# 데이터베이스 접속
접속은 DriverManager 클레스(java.sql.*)의 getConnection 메서드를 사용해 열 수 있다.
# SQL구문 전달
데이터베이스 접속이 열리면 프로그램은 접속을 사용해, 실행을 위해 SQL구문을 데이터베이스 시스템으로 보낼 수 있다. -> statement 클래스의 인스턴스를 통해 수행된다.
conn 접속에 대해 Statement 접근자를 생성.
구문을 실행하기 위해 SQL 구문이 질의(결과 집합을 반환하는)냐 / update, insert, delete, create table 등과 같은 비질의 구문이냐에 따라 executeQuery 메서드나 executeUpdate 메서드를 호출하게 된다.
# 질의 결과 검색
ResultSet 객체 rset에 그 결과들의 투플의 집합을 구하고, 결과 집합에서 한번에 한 투플씩 받아올 수 있다.
next 메서드는 결과 집합에서 받아오지 않는 투플이 남아있는지 아닌지 검사한다. 만약 있으면 받아온다.
(next 의 반환값은 Boolean값)
받아온 투플의 속성은 get으로 시작하는 메서드들을 사용해 검색된다.
# get ~ 메서드 - 문자열로 명시된 속성 이름 / 속성의 위치
- getString : 자바의 String 객체로 검색가능 rest.getString("dept_name")
- getFloat : 해당 번호의 Column(열) rset.getFloat(2); --> 2번째 열 값
# 종료
stmt.close();
conn.close();
import java.sql.*;
import java.sql.SQLException;
public class test_jdbc {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://localhost:49684;database=largeDB;integratedSecurity=true";
Connection conn = DriverManager.getConnection(connectionUrl); //접속
Statement stmt = conn.createStatement(); // 접근자
System.out.println("MS-SQL 서버 접속에 성공하였습니다.1");
ResultSet rs = stmt.executeQuery("SELECT * FROM instructor");
while( rs.next() ) {
String field1 = rs.getString("name");
String field2 = rs.getString("dept_name");
String field3 = rs.getString(3);
System.out.print(field1 + "\t");
System.out.print(field2 + "\t");
System.out.println(field3 );
}
rs.close();
stmt.close();
conn.close();
} catch (ClassNotFoundException sqle) {
System.out.println("SQLException : " + sqle);
}
}
}String connectionUrl = "jdbc:sqlserver://localhost:49684;database=largeDB;integratedSecurity=true";
- localhost : TCP/IP 포트 번호
- databse : 특정 데이터베이스 선택
- intergratedSecurity : 내부 컴퓨터에 연결할 것인가 (문자열로된 패스워드)
Connection conn = DriverManager.getConnection(connectionUrl);
- DriverManager메소드를 이용해 getConnection해달라고한다 = 해당 url을 연결하라. conn객체 생성
Statement stmt = conn.createStatement();
- conn객체를 이용해 stmt객체 생성하기 --> 접속 성공
ResultSet rs = stmt.executeQuery("SELECT * FROM instructor");
- SQL 문장 실행
- 교수들의 학과,연봉 출력하고 평균 연봉 구하기
// 모든 교수들의 연봉 구하기
import java.sql.*;
import java.sql.SQLException;
public class sample2 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://localhost:49684;database=largeDB;integratedSecurity=true";
Connection conn = DriverManager.getConnection(connectionUrl);
Statement stmt = conn.createStatement();
System.out.println("MS-SQL 서버 접속에 성공하였습니다.1");
ResultSet rs = stmt.executeQuery("SELECT * FROM instructor");
double sum=0;
int i=0;
while( rs.next() ) {
String field1 = rs.getString("name");
String field2 = rs.getString("dept_name");
String field3 = rs.getString(4);
sum =sum+ Double.parseDouble(rs.getString(4));
System.out.print(field1 + "\t");
System.out.print(field2 + "\t");
System.out.println(field3);
i=i+1;
}
System.out.println("교수들의 평균 연봉은 "+sum/i);
rs.close();
stmt.close();
conn.close();
} catch (ClassNotFoundException sqle) {
System.out.println("SQLException : " + sqle);
}
}
}
- 학과 별 교수들의 평균 급여 구하기
import java.sql.*;
import java.sql.SQLException;
public class sample3 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://localhost:49684;database=largeDB;integratedSecurity=true";
Connection conn = DriverManager.getConnection(connectionUrl);
Statement stmt = conn.createStatement();
System.out.println("MS-SQL 서버 접속에 성공하였습니다.");
String d_name;
float sal;
ResultSet rset= stmt.executeQuery("select dept_name, avg(salary) "
+ "from instructor group by dept_name");
while (rset.next()) {
d_name= rset.getString("dept_name");
sal= rset.getFloat(2);
System.out.println(d_name+ " " + sal);
}
rset.close();
stmt.close();
conn.close();
} catch (ClassNotFoundException sqle) {
System.out.println("SQLException : " + sqle);
}
}
}
- instructor 테이블에 NULL값이 있는지 확인하기
import java.sql.*;
import java.sql.SQLException;
public class is_in_null {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://localhost:49684;database=largeDB;integratedSecurity=true";
Connection conn = DriverManager.getConnection(connectionUrl);
Statement stmt = conn.createStatement();
System.out.println("MS-SQL 서버 접속에 성공하였습니다.1");
ResultSet rs= stmt.executeQuery("select * from instructor");
while (rs.next()) {
String field1 = rs.getString("name");
String field2 = rs.getString("dept_name");
Float field3 = rs.getFloat("salary");
if (rs.wasNull()) {
System.out.println("Got null value");
break;
}
System.out.print(field1 + "\t");
System.out.print(field2 + "\t");
System.out.println(field3);
}
rs.close();
stmt.close();
conn.close();
} catch (ClassNotFoundException sqle) {
System.out.println("SQLException : " + sqle);
}
}
}re.wasNull로 null이 있는지 확인하기. 여기 데이터베이스에는 없어서 break없이 쭉 출력
- instructor 테이블에 새로운 교수(투플) 삽입 (# 준비된 구문)
import java.sql.*;
import java.sql.SQLException;
public class sample4_insert {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://localhost:49684;database=largeDB;integratedSecurity=true";
Connection conn = DriverManager.getConnection(connectionUrl);
Statement stmt = conn.createStatement();
System.out.println("MS-SQL 서버 접속에 성공하였습니다.");
//stmt.executeUpdate("insert into instructor values('77987', 'Kim', 'Physics', 98000)"); 하드코딩이므로
//preparedstatement 방식을 해야함
PreparedStatement pStmt = conn.prepareStatement(
"insert into instructor values(?,?,?,?)");
pStmt.setString(1, "88877");
pStmt.setString(2, "Perry");
pStmt.setString(3, "Finance");
pStmt.setInt(4, 125000);
//values(?,?,?,?) 에 순서대로 값을 넣어주고
pStmt.executeUpdate();
//값 업데이트
pStmt.setString(1, "88878");
pStmt.executeUpdate();
stmt.close();
conn.close();
} catch (ClassNotFoundException sqle) {
System.out.println("SQLException : " + sqle);
}
}
}# Prepared statement 준비된 구문
바로 대입하지 않고, 변수에 따로 담아준 다음 변수에 담아주기.
테이블이 생성될 때 넣어주는 자료형과 칼럼 순서를 칼럼별로 넣어준다.
"?"로 먼저 준비시키고 실제 값이 나중에 제공되도록 명시한다.
PreparedStatement pStmt = conn.prepareStatement(
"insert into instructor values(?,?,?,?)");
pStmt.setString(1, "88877");
pStmt.setString(2, "Perry");
pStmt.setString(3, "Finance");
pStmt.setInt(4, 125000);
// values(?,?,?,?) 에 순서대로 값을 넣어주고
pStmt.executeUpdate();
// 값 업데이트
pStmt.setString(1, "88878");
pStmt.executeUpdate();** 하드코딩 : stmt.executeUpdate("insert into instructor values('77987', 'Kim', 'Physics', 98000)"
# Hard coding 하드코딩
변경할 수 없는 식의 코딩
하드코딩을 피하기 위해서는 prepared statement를 사용한다.
삽입직후 ssms에서 테이블 확인해보니 삽입이 잘 이루어짐
한 번 더 실행하면 에러가 발생. 그 이유는 77987이 primary key이므로 중복 삽입 불가능하다.*** 중요 ****
# Metadata Features 메타데이터 특성
-> 열의 개수, 명시된 열의 이름 혹은 명시된 열의 타입과 같은 메타데이터 정보 찾을 수 있음
import java.sql.*;
import java.sql.SQLException;
public class sample5{
public static void main(String[] args) throws ClassNotFoundException, SQLException {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://localhost:49684;database=largeDB;integratedSecurity=true";
Connection conn = DriverManager.getConnection(connectionUrl);
Statement stmt = conn.createStatement();
ResultSet rs= stmt.executeQuery("select * from instructor where id > 70000");
ResultSetMetaData rsmd= rs.getMetaData();
for(int i= 1; i<= rsmd.getColumnCount(); i++) {
// getColumnCount : 릴레이션의 열의 수를 반환한다.
System.out.print(rsmd.getColumnName(i) + "\t"); // 열의 이름
System.out.println(rsmd.getColumnTypeName(i)); // 열의 속성
}
conn.close();
} catch (ClassNotFoundException sqle) {
System.out.println("SQLException : " + sqle);
}
}
}
// ResultSetMetaData 객체를 반환하는 getMetaData 메서드
ResultSetMetaData rsmd= rs.getMetaData();
for(int i= 1; i<= rsmd.getColumnCount(); i++) {
System.out.print(rsmd.getColumnName(i) + "\t");
System.out.println(rsmd.getColumnTypeName(i));
}
rs에게 metadata의 정보를 가져다달라고 부탁한 후 rsmd에 대입한다.
rsmd객체를 반복문을 이용해 테이블의 칼람별 타입을 출력한다.
이를 통해 출력할때 getFloat을 할지 getString으로 할지 알게 되는 것.
- metadata
import java.sql.*;
import java.sql.SQLException;
public class sample6{
public static void main(String[] args) throws ClassNotFoundException, SQLException {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://localhost:49684;database=largeDB;integratedSecurity=true";
Connection conn = DriverManager.getConnection(connectionUrl);
DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs = dbmd.getColumns(null, null, "department", "%");
while(rs.next()) {
System.out.print(rs.getString("COLUMN_NAME") + "\t");
System.out.println(rs.getString("TYPE_NAME"));
}
conn.close();
} catch (ClassNotFoundException sqle) {
System.out.println("SQLException : " + sqle);
}
}
}
12주차 트랜잭션
# JDBC에서 트랜잭션 처리 Transaction : Logical unit of work자동 커밋 (default)
- 자동 커밋 off
conn.setAutoCommit(false);
트랜잭션을 커밋하거나 롤백 해야함
= conn.commit();
= conn.rollback();
- 자동 커밋 on
conn.setAutoCommit(true)
# ACID
Atomicity 원자성 : All or nothing
ALL : Commit nothing : Rollback
Consistency, Isolation, Durability
insert into ~~~~ ---> commit <---- all
# 자동커밋 autocommit
자동커밋을 하는 경우는 보통 그룹을 만들고(insert) 해당 그룹에 가입(update) 시키는 작업이 한번에 일어날때
autocommit을 false값으로 바꿔놓는다.
autocommit이 ture값이면(디폴트) 우리가 작성한 문장이 자동으로 테이블에 반영된다.
이렇게 되면 그룹은 만들어지는데 예외가 발생해 가입작업이 안되는 경우가 발생하기 때문이다.
conn.setAutoCommit(false);
stmt.executeUpdate("~~"); //sql문장 작성
conn.rollback();# 대형객체 불러오기 large object
동영상, 사진, 문자데이터 등등 많은 양의 데이터베이스를 처리 가능하다.
getBlob() 와 getClob() 메소드
blob 와 clob 타입의 객체를 반환
객체 전체를 저장하는게 아니라 위치자(포인터)를 저장함
* 위치자란 실제 대형 객체에 대한 논리적인 포인터
setBlob : 데이터베이스 칼럼을 입력 스트림에 연결할 수 있도록 한다. 데이터가 입력스트림에서 읽혀지고 데이터베이스에 blob으로 저장된다
setClob : 데이터베이스 칼럼을 입력 스트림에 연결할 수 있도록 한다.
Statement sta = con.createStatement();
ResultSet res = sta.executeQuery("SELECT * FROM Image");
int i = 0;
while (res.next() && i<3) {
i++; System.out.println("Record ID: "+res.getInt("ID"));
System.out.println(" Subject = "+res.getString("Subject"));
Blob bodyOut = res.getBlob("Body");
int length = (int) bodyOut.length();
System.out.println(" Body Size = "+length);
byte[ ] body = bodyOut.getBytes(1, length);
String bodyHex = bytesToHex(body, 32);
System.out.println(" Body in HEX = "+bodyHex+"...");
// bodyOut.free(); // new in JDBC 4.0
}
res.close();
sta.close(); setBlob()
String INSERT_PICTURE = "insert into blob1 values (?)";
FileInputStream fis = null;
PreparedStatement ps = null;
try {
conn.setAutoCommit(false); // 자동커밋 해제
File file = new File("1.wma"); // 파일을 불러오고
fis = new FileInputStream(file);
byte b[ ] = new byte[(int)file.length()];
fis.read(b);
System.out.println(b.length);
java.sql.Blob b2 = new SerialBlob(b); // json형태로 변환
ps = conn.prepareStatement(INSERT_PICTURE);
ps.setBlob(1,b2);
ps.executeUpdate();
conn.commit();
System.out.println("Record inserted successfully");
} catch(Exception ex){
ex.printStackTrace();
throw ex;
}
finally {
ps.close();
fis.close();
}
설명하셨는지 확인하기
# ODBC p.152
Open DataBase Connectivity(ODBC) 표준
데이터베이스 서버와 통신하는 응용 프로그램 표준
#API
데이터베이스에 접속을 염
질의와 갱신 전송
결과 얻음
# SQLJ --> 거의 사용하지않음
자바 문법, sql문법에 맞지 않는 언어를 사용. 슈도코드같은 개념
https://docs.microsoft.com/ko-kr/sql/connect/jdbc/using-a-stored-procedure-with-input-parameters?view=sql-server-ver15 --> 강의 장료 참고된 곳 (microsoft 홈페이지)
입력 매개 변수가 있는 저장 프로시저 사용 - JDBC Driver for SQL Server
입력 매개 변수가 있는 저장 프로시저 사용 이 문서의 내용 --> JDBC 드라이버 다운로드 호출할 수 있는 SQL Server 저장 프로시저는 데이터를 저장 프로시저에 전달하는 데 사용할 수 있는 입력 매개
docs.microsoft.com
# 프로시저
procedural extensions and stored procedures
- 데이터베이스 안의 프로시저에 저장하여 정의
- 프로시저는 while 문, repeat 문, if-then-else 문, for 반복문, case문 사용 가능
- call 구문을 통해 호출됨
- in 은 입력 받을 값을 저장하고 있는 매개변수
- out 은 결과 값을 반환하기 위해 프로시저 안에서 값이 지정될 매개변수
- 매개변수의 수가 다르면 같은 이름을 가진 여러 개의 프로시저들을 허용
- 장점) 프로시저에 접근하는 다수의 응요 프로그램을 허용한다.
- 장점2) 응용 프로그램의 변경없이 비즈니스 규칙만 변경 가능하다
- 응용프로그램 코드는 데이터베이스릴레이션의 직접적인 변경대신 저장된 프로시저 호출 가능하다
# 함수
- 학과의 이름을 주어서 그 학과의 교수의 수를 반환하는 함수
- 변수들앞에는 @를 붙인다.
create function dept_count(@dept_name varchar(20))
returns integer
as
begin
declare @d_count integer;
select @d_count = count (*)
from instructor
where instructor.dept_name = @dept_name
return(@d_count);
end- 12명이상의 교수를 가진 모든 학과의 이름과 예산을 구하라
select dept_name, budget
from department
where dept_count (dept_name ) > 12- jdbc로
import java.sql.*;
import java.sql.SQLException;
public class sample7{
public static void main(String[] args) throws ClassNotFoundException, SQLException {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://localhost:49684;database=largeDB;integratedSecurity=true";
Connection conn = DriverManager.getConnection(connectionUrl);
DatabaseMetaData dbmd = conn.getMetaData();
CallableStatement cstmt = null;
try {
cstmt = conn.prepareCall("{? = call dbo.dept_count (?)}");
cstmt.registerOutParameter(1,java.sql.Types.VARCHAR);
cstmt.setString(2, "Accounting");
cstmt.execute(); // Execute the callable statement
String outParam = cstmt.getString(1);
System.out.println("Output: "+outParam);
} catch (Exception e) {
System.out.println("Exception message: "+e.getMessage());
}
conn.close();
} catch (ClassNotFoundException sqle) {
System.out.println("SQLException : " + sqle);
}
}
}
cstmt = conn.prepareCall("{? = call dbo.dept_count (?)}");
cstmt.registerOutParameter(1,java.sql.Types.VARCHAR);
cstmt.setString(2, "Accounting");
?의 위치가 중요하다
registerOutParameter로 대입되는 변수(첫번째 물음표 자리)를 설정한다. (첫번째물음표, sql타입)
setString으로 두번째 물음표 자리에 매개변수 "Accounting"을 넣어준다.
- 함수) 매개변수로 주어진 학과명에 있는 교수의 정보
create function instructors_of(@dept_name char(20))
returns table as
return (select ID as r_id, name as r_name, dept_name as r_dept_name, salary as r_salary
from instructor
where instructor.dept_name = @dept_name)
sqlserver jdbc fuction table return 찾아서 해보기
- 파라미터가 없는 프로시저
CREATE PROCEDURE GetContactFormalNames AS
BEGIN
SELECT TOP 10 Title + ' ' + FirstName + ' ' + LastName AS FormalName
FROM Person.Contact
ENDpublic static void executeSprocNoParams(Connection con) {
try {
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("{call dbo.GetContactFormalNames}");
while (rs.next()) {
System.out.println(rs.getString("FormalName")); }
rs.close();
stmt.close();
}
catch (Exception e) {
e.printStackTrace();
}
}- input, output 둘 다 있는 프로시저
CREATE PROCEDURE GetImmediateManager
@employeeID INT, @managerID INT OUTPUT
AS
BEGIN
SELECT @managerID = ManagerID
FROM HumanResources.Employee
WHERE EmployeeID = @employeeID
ENDpublic static void executeStoredProcedure(Connection con) {
try {
CallableStatement cstmt = con.prepareCall("{call dbo.GetImmediateManager(?, ?)}"); //함수호출
cstmt.setInt(1, 5); // 첫번째 파라미터에 5 대입
cstmt.registerOutParameter(2, java.sql.Types.INTEGER); // 두번째 파라미터는 INTEGER값이라고 설정
cstmt.execute();
System.out.println("MANAGER ID: " + cstmt.getInt(2)); // 두번째 파라미터에 2를 전달하고 출력
} catch (Exception e) {
e.printStackTrace();
}
}* 디폴트는 INPUT, sql 함수작성에서 두번째 값을 OUTPUT으로 작성한 상황
함수 매개변수 두번째값(물음표)이 OUTPUT
callablestatement
preparedstatement 둘의 차이
getupdatecount : 변경,삭제 등 갱신이 이루어진 다음에 갯수 반환해주는 메소드
# 오버로딩 (같은 이름, 다른 매개변수)
함수 - 파라미터 개수 다름
프로시저 - 파라미터 개수와 타입이 다름
## 프로시저와 함수를 위한 언어구조
sql은 범용 프로그래밍 언어같다.
변수들은 dclare을 사용해서 선언되고 할당은 set문을 사용
begin ... end 사이에 다수의 sql구문을 포함할 수 있다.
begin atomic ... end 를 사용하게되면 모든 문장이 한번에 단일 트랜잭션으로 실행 가능
# while 과 repeat 문 :
| declare n integer default 0; while n < 10 do set n = n + 1 end while |
repeat set n = n – 1 until n = 0 end repeat |
# 외부 언어 루틴
- 기본적인 기능조차 데이터베이스 제품들 마다 다른 문법과 의미를 가진다.
- SQL은 자바, C#, C, C++같은 프로그래밍 언어로 함수를 정의 할 수 있음
- 정의된 함수들로 SQL이 못하는 계산 수행 가능
- c#이나 자바에서 작성된 코드라면 샌드박스에서 실행하는 것 가능. 프로세스간 통시능ㄹ 피하는 것은 함수호출에 대한 비용을 많이 줄인다.
# 트리거
데이터베이스에서 일어나는 여러가지 갱신들이 적용되고 난 후에 지켜야 하는 규칙같은 것.
트리거는 데이터베이스의 수정(insert, update, delete)에 대한 부수효과로 시스템이 자동적으로 수행한는 문장이다.
무결성 제약조건을 구현하기 위해 사용될 수 있다. 데이터베이스 외부에 대한 갱신은 수행할 수 없다.
< 트리거 요구사항 >
1. 트리거가 실행될 시점을 명시해야 한다. 트리거가 검사돼야 하는 사건이나 실행을 위한 조건
2. 트리거가 실행될 때 수행해야 할 동작을 명시해야 한다.
# 트리거 after insrert **중요**
create trigger timeslot_check1 after insert on section
referencing new row as nrow
for each row
when (nrow.time_slot_id not in (
select time_slot_id
from time_slot)) /* time_slot_id 는 time_slot 에 존재하면 안됨*/
begin
rollback
end;새로운 row를 nrow라고 정의하자.
norw의 time_slot_id가 when내부 sql문장에 없다면, insert한 것을 롤백
# 트리거 after delete
- 분명 삭제했는데, 다른 테이블에서 삭제한것을 여전히 참조하는 사실이 발견되면 삭제작업을 롤백
create trigger timeslot_check2 after delete on timeslot
referencing old row as orow
for each row
when (orow.time_slot_id not in (
select time_slot_id
from time_slot)
/* time slot id 에 대한 마지막 투플을 time slot 에서 삭제 */
and orow.time_slot_id in (
select time_slot_id
from section)) /* time_slot_id 는 여전히 section 에 참조됨*/
begin
rollback
end;# 트리거 after update
insert - nrow
delete - orow
update - nrow, orow
< 데이터 베이스 스키마 설계 시 두 가지 위험성 >
1. 중복성
2. 불완전성
< 데이터베이스 작업과정 >
ISP(정보전략짜기) / BPR(업무처리재구성) -> ER Modeling (개체관계 모델링)
* 비전공자 교직원들이 ER-diagram(설계도)를 보고 이해해야한다. 따라서 설계도는 쉽게 작성되어야 한다.
## 7.2 개체-관계 모델 ER-Modeling
개체(Entity)
실세계에서 다른 모든 객체와 구별되는 유, 무형의 사물
예 : 각 개인, 수업, 비행기 예약
속성(Attributes)
각 개체 집합의 각 구성원들이 가지는 기술적 특성
예 : instructor 개체 집합의 속성은 ID, name, dept_name, salary
개체 집합(Entity set)
같은 속성을 공유하는 같은 유형의 개체들의 집합
예 : 교수인 모든 사람들의 집합, 대학에 속한 모든 학생들의 집합
관계 (Relationship)
여러 객체들 사이의 연관성
관계 집합 (Relationship set)
같은 유형의 관계들의 집합
관계 집합은 n 2 개의 개체 집합 사이의 수학적 관계
{(e1, e2, … en) | e1 E1, e2 E2, …, en En}
(e1, e2, …, en) 은 관계
예 : (44553,22222) advisor
# 속성
단순 속성 과 복합 속성 simple & composite attribute
예 : Address, name, 김갑동, …
단일값 속성 과 다중값 속성 single-valued & multivalued attributes
예 : phone_numbers, dependent_name
유도된 속성 Derived attributes (파생)
다른 관련된 속성들이나 객체들의 값들로부터 유도됨
예 : age 는 date_of_birth 로부터 유도됨
# 이진 관계 집합(Binary relationship)
두 개체 집합이 관련된 관계 집합
데이터베이스 내의 대부분의 관계 집합은 이진 관계 집합임
예 : 대학에서 수행된 모든 연구 과제를 표현하는 project라는 개체 집합 ,
어느 특정 과제에 있어서 어느 교수가 어느 학생을 지도하고 있는 지에 대한 정보를 저장하는 관계 집합 proj_guide
* 관계 집합에 참가하는 개체 집합의 수를 관계 집합의 차수라고 함
# 대응수 mapping cardinalities
관계 집합을 통하여 다른 개체와 관련될 수 있는 개체의 수
이진 관계 집합에서 대응수 :
일대일
일대다
다대일
다대다
(b)의 경우 B집합의 개체들은 여러개로 뻗지 않고 A의 개체 하나로만 뻗는다. 따라서 일 대 다
# keys
개체들을 유일하게 구별해주는 속성들의 집합
수퍼키
유일성, 1개 이상
후보키
최소한의 수퍼키
유일성, 최소성, 1개 이상
ID 는 instructor 의 후보키
course_id 는 course의 후보키
주키
후보키 중 한 개
# E-R Diagrams
| 사각형 | 개체 집합 첫 번째 부분은 개체 집합의 이름 두 번째 부분은 개체 집합의 모든 속성, 주 키는 밑줄 |
| 다이아몬드 | 관계 집합 |
| 선 | 개체 집합을 관계 집합에 연결 -> 화살표 : "one" — 막대기 : "many" 참가 이중선 : 전체적인 참가 참가 단일선 : 부분적인 참가 |
| 점선 | 어떤 관계 집합의 속성들을 그 관계 집합에 연결 |
| 이중선 | 개체의 관계 집합에의 전체적 참가를 나타냄 |
| 이중 다이아몬드 | 약성 개체 집합으로 연결된 식별 관계 집합을 나타냄 |
# 역할 roles
- 다이아몬드를 사각형에 연결하는 선에 이름을 붙여 역할 표시
- 1항 관계
- course_id 와 prereq_id 역할 표시자
# 일대일
교수는 최대 한 명, 학생은 최대 한 명의 지도교수 가짐
# 일대다
교수는 많은 학생, 학생은 최대 한 명의 지도교수 가짐
# 다대일
교수는 반드시 한 명의 학생, 학생은 많은 지도교수 가짐
# 다대다
교수는 많은 학생, 학생은 많은 지도교수 가짐
# 참가 one to many
모든 강좌는 sec_course에 참여한다.(모든 수업과 연관됨)
교과목중에는 sec_course에 부분적으로 참여한다.
# 삼진 관계 Ternary relationship
터널 릴레이션이 존재할때만 삼진관계를 잡는다.
어떤 학생이 프로젝트에 참여하는 동안 어떤 교수가 프로젝트를 가이딩한다.
어떤 교수가 어떤 프로젝트에 대해서 어떤 학생을 가이딩했다.
어떤 학생이 어떤 교수에게 가이딩 받을때 어떤 프로젝트에 대해서 받았다.
* 주어와 목적어를 개체와 관계만 번갈아가면서 표현가능
# 약성 개체 집합 Weak Entity sets
약성 개체의 구별자는 점선으로 밑줄
약성 개체 집합을 식별 강성 집합과 연결하는 관계 집합은 이중 다이아몬드로 표현
약성 개체 집합 : section
- 주 키를 형성하기 위한 충분한 속성들을 지니고 있지 않는 개체 집합
- 식별, 지배 개체 집합이라 불리는 다른 개체 집합과 관련되어야 함
- 식별 개체 집합에 존재 종속 관계
강성 개체 집합 : course
- 주 키를 가지고 있는 개체 집합
- 식별 개체 집합
식별 관계 : 약성 개체 집합과 식별 개체 집합을 연관 짓는 관계
- 약성 관계 집합으로부터 식별 관계 집합으로의 다대일
- 약성 관계 집합의 참가는 전체적
구별자
- 개체 집합의 부분 키
- 약성 개체 집합 내의 모든 개체들을 서로 구별하게 하는 속성들의 집합
약성 개체 집합의 주 키는 식별 개체 집합의 주 키와 약성 개체 집합의 구별자를 합하여 만듦
# 대학 조직을 위한 E-R Diagram
14주차
# 우리가 배우는 부분을 실무 관점에서
후보테이블 ----> 정규화된 테이블로 변환할때 약 1차정규형(1NF)~3차정규형(3NF)까지 진행함
* 4,5차정규형까지 진행할 필요 X
# Normalization 정규화 **중요**
“하나의 Table에는 한 가지 주제만을 기록한다”
정규화가 덜 되어있다 = 하나의 Table에 두 가지 이상의 주제가 담겨있다.
후보테이블을 정규화시키기 위해서는 후보테이블에서 정규화 과정을 수차례 거침
- 정규화의 장점
| 정규화 | 미흡한 정규화 / 정규화 x | |
| 장점 | Insert, Delete, Update 가 정상적으로 이루어짐 | select -> 성능 향상 |
| 단점 | select -> 성능저하가 발생 | Insert, Delete, Update가 비정상적으로 이루어짐 Anomoly(이상)현상이라고 한다. -> 신입생등록이 안된다. -> 중요한 정보가 지워질 수 있다. DB의 무결성제약조건이 무너진다. |
∴ 정규화를 하지 않았을때 DB의 신뢰도가 떨어지므로 정규화를 해야한다.
- 아래 테이블은 교수의 연봉과 건물별 예산이라는 주제가 같이 작성되어 있음
--> 정규화 진행되지 않음
* 테이블의 row단위는 유일해야한다.
Candidate Key 후보키 : ID = Primary Key
(name은 동명이인이 있을수도 있으므로 후보키가 될 수 없다)
* 주제는 Instructor이고 Department 가 주제에 대해 설명
중복이 발생하면 저장공간 낭비 발생
- Insert 실패
만약, 신설학과를 생성한할때 ID, name, salary값도 채워줘야하는데 primary key인 ID가 NULL값이 됨
- Delete 실패
만약 음악학과에 있는 교수가 한명일때 15151 교수를 delete하면 교수삭제와 함께 (의도와 달리)학과도 삭제됨
- Update 시
해당 학과의 예산을 올리려할때, 중복되는 행의 값들을 똑같이 올려줘야한다 -> 속도가 떨어지고 비효율적이다.
## 테이블 분해 decomposition
# 테이블 분해 시 기준
함수의 종속 나타내기
# 함수의 종속성 functional dependency
그 테이블이 담고자 하는 사실의 모습
테이블의 행은 사실의 모습이다.
** 데이터베이스에 정규화를 진행했지만, 비전공자인 요청자가 볼 수 있게 테이블 join을 해야한다.
# 손실 분해 A Lossy Decomposition
정규화(분해)된 테이블을 다시 조인했을때 데이터가 이상해짐 -> 손실
# 무손실 조인 분해 Lossless-Join Decomposition
정규화 진행 후 다시 조인할때 정규화 이전 상태가 될 수 있을만큼, 정규화를 잘 해야한다. -> 무손실
# 1차정규형 First Normal Form
ˇ 원소들을 나누는 조건 (Atomic원자가는과정):
원소에 접근할 때 규칙이 있다 -> 구조화
- 도메인의 원소들이 나눌 수 없을 때 원자적(Atomic)이라 함
- 원자적이다 = 내부적으로 구조를 가질 수 없다 (indevisable 쪼갤수없는)
- 릴레이션 스키마 R의 모든 원소들의 도메인이 원자적일 때
- R이 제 1 정규형(1NF)에 속한다고 함
⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒
ˇ 원자적이지 않은 도메인:
복합 속성 – 이름들의 집합
다중 속성
직원 번호 CS101 – 부서 번호
만약, 김갑동의 주소가 "서울시 성북구 정릉동 16-1"인데 "서울시 성북구"인 데이터를 찾는다하면 김갑동의 주소는
원자적이지 않다.
원자적(구조화)되려면 "서울시", "성북구", "정릉동", "16-1" 형태로 데이터를 변경
⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒
# 좋은 스키마의 조건
관계는 좋은 형태여야한다.
분해는 무손실 조인 분해 여야한다.
함수종속
다중분해 (4,5차)는 피하기 <- 만약 4,5차 까지 가는 상황이라면 후보테이블이 잘못된 상황이다
# 함수 종속 Functional Dependencies ** 중요 **
X → Y 라는 표기는, X가 각각의 Y 멤버를 함수적으로 결정한다는 뜻이다.
사실의 세부적인 모습
이차함수를 예로 들면
x2 -> y2, x3 -> y2 를 보면 알 수 있듯이 x가 결정자이다 (y2 -> ? 는 불가능)
# 결정자 / 종속자
- 결정자 : 속성 간의 종속성을 규명할 때 기준이 되는 값
- 종속자 : 결정자의 값에 의해 정해지는 값
- 속성 Y가 속성X에 의해 함수적으로 종속된다는 말은 속성 X의 값을 이용해 속성 Y의 값을 유일하게 식별할 수 있다는 의미
- 결정자 X의 값은 반드시 하나의 Y값과 연관됨
- X 값에 의존하는 Y 값은 하나뿐이지 다른 Y 값이 있을 수 없음
- 기호
- X → Y
- Y = F(X)
- X : 결정자(Determinant), Y : 종속자(Dependent)
- 결정자 사례
- 주민등록번호, 이름, 휴대전화번호, 주소로 이루어진 엔터티일 경우 주민등록번호는 결정자
- 주민등록번호를 알 경우 나머지들은 모두 유일하게 식별이 가능함
- 종속자 사례
- 이름으로 주민등록번호가 고유하게 결정되지 않으므로 종속자
inst_dept 테이블
| ID 슈퍼키, 후보키, 주키 |
name | salary | dept_name | building | budget |
ID -> name ~ budget 까지 가능. ID가 결정자이다.
하지만 ? -> ID 안됨.
* 한가지 주제만 있도록 테이블 쪼갠다
# Trivial 당연한 함수종속
b가 a의 부분집합이면 a->b는 당연하다.
ex) ID, name -> ID
# 폐포집합 closure of a set of Functional Dependencies
X의 폐포는 X에 종속됐다고 추론할 수 있는 모든 속성의 집합을 의미함
- X → Y, Z 라면 X의 폐포는 X, Y, Z라고 함
- 기호 : X＋ = X, Y, Z
# 폐포 사례
엔터티 R에 {A, B, C, D, E| 아래와 같은 함수 종속이 있을 경우
- A → C
- B → D
- A, B → E
# 폐포
- A＋ = A, C
- B＋ = B, D
- (A, B)＋ = A, B, C, D, E
- 폐포 (A, B)＋가 엔터티 R의 모든 속성을 가지고 있으므로, R 엔터티의 키는 A, B가 됨
# 종속성 추론 규칙 (암스트롱의 공리) F+ ← F
- Y ⊆ X 이면 X → Y 성립함 (reflexivity 재귀 )
- X → Y 이면 XZ → YZ 성립함 (augmentation 증가 )
- X → Y 이고, Y → Z 이면 X → Z 성립함 (transivity 이행)
- X → YZ 이면, X → Y 이면 X → Z 성립함 (union 연합 )
- X → Y 이고, X → Z 이면 X → YZ 성립함 (decompositon 분해 )
- X → Y 이고, YZ → W 이면 XZ → W 성립함 (pseudotransitivty 가이행 )
* A -> B,C,D,E,A 이고 A가 후보키일때
AB -> A,B,C,D,E 를 만족하면 A는 슈퍼키이자 후보키가 된다. ** 중요 **
엔터티는 정규화에 의해 생성되고, 정규화는 함수 종속에 의해 생성되며, 함수 종속은 결정자가 없이는 존재할 수 없음
결정자 => 함수 종속 => 정규화 => 엔터티
(폐포 closure) F+ --> F --> Fc ( 규준커버 canonical cover )
# Boyce-codd normal form (BCNF)
< 두가지 조건 >중에 하나만 만족해도 BCNF :
1. a->b가 명백한 함수 종속 (b는 a의 부분집합)
2. a가 R의 슈퍼키
그 테이블에 정의되어 있는 함수적 종속성의 canonical cover에 있는 각각의 함수적 종속성의 결정자가 후보키면 된다.
# BCNF로 분해
분해 전 : (ID는 슈퍼키)
ID -> name, salary, dept_name, building, budget
dept_name -> building, budget ※ bcnf 위반
따라서, 결정자인 dept_name은 원자테이블에 남겨놓고 종속자인 building과 budget은 삭제한다.
분해 후 :
R1 = department(dept_name, building, budget)
dept_name -> building, budget
R2 = instructor(ID, name, salary, dept_name)
ID -> name, salary, dept_name
위처럼 BCNF로 분해하게 되면 join 했을때 완벽하게 복구 가능.
# 3차 정규형 Third Normal Form
R (A,B,C,D,E) -> 사람이 정의해놓은 F를 보고 -> Fc를 구한다.
Fc에 해당하는 결정자/종속자들이 나올 것임
이때, < 세가지 조건 >중에 하나만 만족하면 된다.
1. a->b is trivial (a->b가 명백한 함수 종속, b가 a의 부분집합)
2. a가 R의 슈퍼키 (결정자가 canditdate key인지 확인하면 됨)
3. 피결정자가 candidate key 후보키에 포함이 되어있는 경우
3차정규형이라고 판단하면, 2차정규형이자 1차정규형이 된다.
# (추가적) 정규화하는 이유 -> BCNF가 아니라 3차정규형으로 남아있는 테이블의 경우
만약 R이 좋은 형태가 아니다 (BCNF가 아니다). 따라서 R 테이블을 분해해야한다.
만약 BCNF로 변환했는데 dependency preserving(함수적종속성)이 안된다.
그러면 BCNF로 변환하지않고 3NF(3차정규형)으로 남아있는다.
# BCNF 분해 예시
class (course_id, title, dept_name, credits, sec_id, semester, year, building, room_number, capacity, time_slot_id)
라는 테이블이 있다.
아래와 같은 함수적 종속성 만족한다.
- course_id -> title, dept_name, credits
- building, room_number -> capacity
- course_id, sec_id, semester, year -> building, room_number, time_slot_id
결정자는 course_id, {building, room_number}, {course_id, sec_id, semester, year} 이다. = 세가지 주제가 테이블 안에 있다.
이때 후보키는 {course_id, sec_id, semester, year}
BCNF -> {후보키, 결정자, 종속자}
!!BCNF 분해 완료!!
course(course_id, title, dept_name, credits) <- BCNF
class-1 (course_id, sec_id, semester, year, building, room_number, capacity, time_slot_id)
classroom (building, room_number, capacity) <- BCNF
section (course_id, sec_id, semester, year, building, room_number, time_slot_id) <- BCNF
BCNF = good table => 무손실 조인 분해 => 함수적 종속성
ER-Modeling -> 후보테이블 -> 정규화 -> 최종테이블
ER-Modeling -> 최종테이블로 바로 갈 순 없을까?
개체-관계 파악
'전공 과목 이수2👨💻 > 데이터베이스' 카테고리의 다른 글
| [데이터베이스] 08. 관계형데이터베이스 설계 (0) | 2021.06.03 |
|---|---|
| [데이터베이스] 07.데이터베이스 설계 (0) | 2021.05.31 |
| [데이터베이스] 13주차 | 프로시저, 함수 (0) | 2021.05.30 |
| [데이터베이스] 트랜잭션 처리 (0) | 2021.05.30 |
| [데이터베이스] jdbc 입문 (0) | 2021.05.29 |
| [데이터베이스] 4장 말 5장 초 (0) | 2021.05.26 |