1. 데이터 베이스 설계 단계
(추상적)
요구조건 분석 : 요구조건 명세서 작성
개념적 설계 : 개념스키마, 트랜잭션 모델링, ER모델
논리적 설계 : 논리 스키마 설계, 트랜잭션 인터페이스 설계
물리적 설계 : 물리적 구조의 데이터로 변환
데이터베이스 구현 : DDL로 데이터 베이스 생성, 트랜잭션 생성
(구체적)
2. ER-Model(Entity - Ralationship Model)
: ‘개념적 데이터 모델’ 중 대표적인 모델,
1976년 피터 챈(Peter Chen) 의 의해 제안,
데이터의 개체(Entity), 속성(Attribute), 관계(Relationship)에 대해 용이하게
표현할 수 있는 ERD(Entity - Ralationship Diagram) 정의
3. 데이터 모델링의 3가지 요소
1) 업무가 관여하는 어떤 것(Things, 개체 : Entity)
2) 어떤 것이 가지는 성격(속성, Attributes)
3) 업무가 관여하는 어떤 것 간의 관계(Relationships)
4. 하나의 개체를 개체 어커런스(Entity Occurence), 개체 인스턴스(Entity Instance)라고 한다.
5. '관계'에 참여하는 개체타입의 '개수'에 대한 대응 카디널리티(Cardinality)를 갖는다.
대응 카디널리티에 따른 관계의 종류는 1:1관계, 1:N관계 N:M관계가 있다.
6. 요구사항을 바탕으로 ERD 작성을 위해 개체, 속성, 대응 카디널리티 파악
(개체 : 빨간 네모, 속성 : 파란 동그라미, 기본키 : 파란 밑줄, 관계 : 검정색 마름모)
7. ERD 설계 툴(aquerytool)
: https://aquerytool.com/
8. SQL 문제 연습하기 좋은 홈페이지
: https://programmers.co.kr/
[실습코드]
1. [폴더] ERD
1) erd1.erm, DDL로 내보냄 > erd1.spl 생성, campus 폴더를 만들고 HTML 내보내기
** 이클립스를 이용 ERMaster 파일 만들기, erd1.erm
** DDL로 내보냄 > erd1.spl 생성
/* Drop Tables */
DROP TABLE register CASCADE CONSTRAINTS;
DROP TABLE student CASCADE CONSTRAINTS;
DROP TABLE subject CASCADE CONSTRAINTS;
DROP TABLE professor CASCADE CONSTRAINTS;
/* Create Tables */
CREATE TABLE professor
(
profno number NOT NULL,
name varchar2(10) NOT NULL,
deptno number,
PRIMARY KEY (profno)
);
CREATE TABLE register
(
studno number NOT NULL,
subjno number NOT NULL
);
CREATE TABLE student
(
studno number NOT NULL,
name varchar2(10) NOT NULL,
deptno number,
profno number NOT NULL,
PRIMARY KEY (studno)
);
CREATE TABLE subject
(
subjno number NOT NULL,
name varchar2(10) NOT NULL,
profno number NOT NULL,
PRIMARY KEY (subjno)
);
/* Create Foreign Keys */
ALTER TABLE student
ADD FOREIGN KEY (profno)
REFERENCES professor (profno)
;
ALTER TABLE subject
ADD FOREIGN KEY (profno)
REFERENCES professor (profno)
;
ALTER TABLE register
ADD FOREIGN KEY (studno)
REFERENCES student (studno)
;
ALTER TABLE register
ADD FOREIGN KEY (subjno)
REFERENCES subject (subjno)
;
** campus 폴더를 만들고 HTML 내보내기
2) erd2.erm, DDL로 내보냄 > erd2.sql 생성
** 요구사항 확인
** E-R Diagram
** 이클립스를 이용 ERMaster 파일 만들기, erd2.erm
** DDL로 내보냄 > erd2.sql 생성
/* Drop Tables */
DROP TABLE takeAclass CASCADE CONSTRAINTS;
DROP TABLE lecture CASCADE CONSTRAINTS;
DROP TABLE professor CASCADE CONSTRAINTS;
DROP TABLE student CASCADE CONSTRAINTS;
DROP TABLE department CASCADE CONSTRAINTS;
/* Create Tables */
CREATE TABLE department
(
deptno number NOT NULL,
phonenum varchar2(10) NOT NULL,
office varchar2(10) NOT NULL,
name varchar2(10) NOT NULL,
PRIMARY KEY (deptno)
);
CREATE TABLE lecture
(
lectno number NOT NULL,
name varchar2(10) NOT NULL,
creditnum number NOT NULL,
num number NOT NULL,
class varchar2(10) NOT NULL,
years number NOT NULL,
profno number NOT NULL,
PRIMARY KEY (lectno)
);
CREATE TABLE professor
(
profno number NOT NULL,
jumin varchar2(13) NOT NULL,
address varchar2(20) NOT NULL,
name varchar2(10) NOT NULL,
position varchar2(10) NOT NULL,
phonenum varchar2(15) NOT NULL,
years number NOT NULL,
deptno number NOT NULL,
PRIMARY KEY (profno)
);
CREATE TABLE student
(
studno number NOT NULL,
jumin varchar2(13) NOT NULL,
name varchar2(10) NOT NULL,
grade number NOT NULL,
pnonenum varchar2(15) NOT NULL,
address varchar2(20) NOT NULL,
deptno number NOT NULL,
PRIMARY KEY (studno)
);
CREATE TABLE takeAclass
(
studno number NOT NULL,
lectno number NOT NULL
);
/* Create Foreign Keys */
ALTER TABLE professor
ADD FOREIGN KEY (deptno)
REFERENCES department (deptno)
;
ALTER TABLE student
ADD FOREIGN KEY (deptno)
REFERENCES department (deptno)
;
ALTER TABLE takeAclass
ADD FOREIGN KEY (lectno)
REFERENCES lecture (lectno)
;
ALTER TABLE lecture
ADD FOREIGN KEY (profno)
REFERENCES professor (profno)
;
ALTER TABLE takeAclass
ADD FOREIGN KEY (studno)
REFERENCES student (studno)
;
2. Lec28_JDBC
1) com.lec.java.db패키지, Query 인터페이스 / com.lec.java.db04패키지, Query를 인터페이스 받은 DB04Main클래스
** com.lec.java.db04패키지, Query를 인터페이스 받은 DB04Main클래스
package com.lec.java.db04;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
import com.lec.java.db.Query;
/* auto-generated keys 값
* SEQUENCE (ORACLE), AUTO_INCREMENET (mysql), IDENTITY (mssql) 등으로
* 새로운 레코드 INSERT 시 자동 생성된 key 값 알아내기
*/
public class DB04Main implements Query {
public static void main(String[] args) {
System.out.println("DB 4 - generated id값");
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
// 자동생성되는 컬럼의 이름(들) 이 담긴 배열 준비 (auto-generated keys 배열)
String[] generatedCols = {COL_LABEL_NO}; // mb_no
try {
// OracleDriver 클래스를 메모리에 로딩
Class.forName(DRIVER);
System.out.println("드라이버 클래스 로딩 성공");
// DB Connection
conn = DriverManager.getConnection(URL, USER, PASSWD);
System.out.println("DB Connection 성공");
System.out.println();
System.out.println("INSERT");
// Statement 나 PreparedStatement 생성시 두번째 매개변수로 auto-generated keys 배열 넘겨줌
pstmt = conn.prepareStatement(SQL_INSERT_SEQ, generatedCols);
pstmt.setString(1, "그루트");
int cnt = pstmt.executeUpdate();
System.out.println(cnt + " 개 행 INSERT 성공");
// auto-generated keys 값 뽑아오기
rs = pstmt.getGeneratedKeys();
if(rs.next()) {
long genKey = rs.getLong(1);
System.out.println("자동 생성된 key 값 = " + genKey);
}
rs.close();
pstmt.close();
// SELECT -> executeQuery()
System.out.println();
System.out.println("SELECT");
pstmt = conn.prepareStatement(SQL_SELECT_ALL + " ORDER BY "
+ COL_LABEL_NO + " DESC");
// SQL 전송/실행
rs = pstmt.executeQuery();
while (rs.next()) {
String no = rs.getString(COL_LABEL_NO);
String name = rs.getString(COL_LABEL_NAME);
String birthdate = rs.getString(COL_LABEL_BIRTHDATE);
System.out.println(no + " | " + name + " | " + birthdate);
} // end while
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
// SQL 에러 메세지는 SQLException 에서 확인가능
System.out.println("SQL 에러: " + e.getMessage());
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
} // end try-catch
} // end main()
} // end class DB03Main
** com.lec.java.db패키지, Query 인터페이스
package com.lec.java.db;
public interface Query {
// 1. Oracle 연동을 위한 정보들(상수들)을 정의(세팅)
// JDBC 드라이버 클래스 정보
public static final String DRIVER =
"oracle.jdbc.driver.OracleDriver";
// DB 서버 정보
public static final String URL =
"jdbc:oracle:thin:@localhost:1521:XE";
// DB 사용자 계정 정보
public static final String USER = "scott0316";
public static final String PASSWD = "tiger0316";
public static final String TBL_NAME = "test_member";
public static final String COL_LABEL_NO = "mb_no";
public static final String COL_LABEL_NAME = "mb_name";
public static final String COL_LABEL_BIRTHDATE = "mb_birthdate";
public static final String SEQ_NAME = "test_member_seq";
// PreparedStatement에서 사용될 SQL 문장
public static final String SQL_SELECT_ALL =
"SELECT * FROM " + TBL_NAME;
public static final String SQL_INSERT =
"INSERT INTO " + TBL_NAME + " VALUES(?, ?, ?)";
public static final String SQL_INSERT_SEQ =
"INSERT INTO " + TBL_NAME + " VALUES(" + SEQ_NAME + ".nextval, ?, SYSDATE)";
public static final String SQL_UPDATE_BIRTHDATE =
"UPDATE " + TBL_NAME
+ " SET " + COL_LABEL_BIRTHDATE + " = ?"
+ " WHERE " + COL_LABEL_NO + " = ?";
public static final String SQL_DELETE_BY_NO =
"DELETE FROM " + TBL_NAME
+ " WHERE " + COL_LABEL_NO + " = ?";
} // end Query(I)
2) com.lec.java.db패키지, Query 인터페이스 / com.lec.java.db05패키지, Query를 인터페이스 받은 DB05Main클래스
** com.lec.java.db05패키지, Query를 인터페이스 받은 DB05Main클래스
package com.lec.java.db05;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.lec.java.db.Query;
/*
* 트랜잭션 (Transaction)
* 데이터베이스의 '작업'의 단위,
* '하나의 트랜잭션'은 '일련의 쿼리연산(들)' 로 구성
*
* 가령 : A계좌 → B계좌 로의 '송금' 이라는 트랜잭션은 아래 쿼리연산(들)로 구성 된 경우
* ① A계좌에서 인출 쿼리실행
* ② B계좌로 입금 쿼리실행
* 두 쿼리 연산은 '모두 반영' 되든지 아니면 '전혀 반영되지 않아야 한다'
*
* 트랜잭션 중간에 실패하면 직전까지 성공한 쿼리 결과도 다시 원상 복귀 해야 한다 ( rollback 이라 한다)
*
* 트랜잭션의 4 특징 (줄여서 ACID 라고 한다) > 에이씨드..!
* 1. 원자성 (Atomicity) : 트랜잭션 연산은 '모두 반영' 되든지 아니면 '전혀 반영되지 않든지' 해야 한다.
* 2. 일관성 (Consistency) : 트랜잭션이 성공적으로 완료되면 언제나 일관성 있는 데이터베이스 상태로 변환되어야 한다
* 3. 독립성 (Isolation) : 한 트랜잭션이 완료되기 전까지 트랜잭션이 다루는 데이터를 다른 트랜잭션이 데이터를 접근하지 못하도록 해야 한다
* 4. 영속성 (Durability) : 트랜잭션 완료후에 변경된 내용은 계속 영구적으로 보존되어야 한다.
*
* 트랜잭션의 상태
*
* 시작
* ↓
* (활동)
* ↙ ↘
* (부분완료) → (실패)
* ↓ ↓
*(완료:commit) (철회:rollback)
*
*
* 기본적으로 JDBC 의 모든 쿼리실행은 auto commit 이다.
*
* JDBC 에서 트랜잭션 구현하기
* 1. Connection 의 auto commit 을 해제 setAutoCommit(false);
* 2. 쿼리연산(들) 수행
* 3. 정상적으로 완료되면 commit() 실행
* 4. 예외 발생등으로 실패 하면 rollback() 실행
*/
public class DB05Main implements Query {
public static void main(String[] args) {
System.out.println("DB 5 - Transaction");
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// OracleDriver 클래스를 메모리에 로딩
Class.forName(DRIVER);
System.out.println("드라이버 클래스 로딩 성공");
// DB Connection
conn = DriverManager.getConnection(URL, USER, PASSWD);
System.out.println("DB Connection 성공");
// Statement 작성
stmt = conn.createStatement();
// 1. Connection 의 auto commit 을 해제
conn.setAutoCommit(false);
// 2. 쿼리연산(들) 수행
stmt.executeUpdate("INSERT INTO test_member
VALUES (test_member_seq.nextval, '1번째', SYSDATE)");
stmt.executeUpdate("INSERT INTO test_member
VALUES (test_member_seq.nextval, '2번째', SYSDATE)");
stmt.executeUpdate("INSERT INTO test_member
VALUES (test_member_seq.nextval, '', SYSDATE)");
stmt.executeUpdate("INSERT INTO test_member
VALUES (test_member_seq.nextval, '4번째', SYSDATE)");
// 3. 정상적으로 완료되면 commit() 실행
System.out.println("커밋 실행");
conn.commit();
// 결과는 직접 콘솔에서 확인해보자
rs = stmt.executeQuery(SQL_SELECT_ALL + " ORDER BY " + COL_LABEL_NO + " DESC");
while (rs.next()) {
String no = rs.getString(COL_LABEL_NO);
String name = rs.getString(COL_LABEL_NAME);
String birthdate = rs.getString(COL_LABEL_BIRTHDATE);
System.out.println(no + " | " + name + " | " + birthdate);
} // end while
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
System.out.println("SQL 에러: " + e.getMessage());
e.printStackTrace();
// 4. 예외 발생등으로 실패 rollback() 실행
if(conn != null) {
try {
System.out.println("롤백 합니다..!!");
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
} // end try-catch
} // end main()
} // end class
[추가] 트랜잭션을 모두 성공하면 commit
[추가] 트랜잭션을 수행하다가 중간에 실패하면 rollback
** com.lec.java.db패키지, Query 인터페이스
package com.lec.java.db;
public interface Query {
// 1. Oracle 연동을 위한 정보들(상수들)을 정의(세팅)
// JDBC 드라이버 클래스 정보
public static final String DRIVER =
"oracle.jdbc.driver.OracleDriver";
// DB 서버 정보
public static final String URL =
"jdbc:oracle:thin:@localhost:1521:XE";
// DB 사용자 계정 정보
public static final String USER = "scott0316";
public static final String PASSWD = "tiger0316";
public static final String TBL_NAME = "test_member";
public static final String COL_LABEL_NO = "mb_no";
public static final String COL_LABEL_NAME = "mb_name";
public static final String COL_LABEL_BIRTHDATE = "mb_birthdate";
public static final String SEQ_NAME = "test_member_seq";
// PreparedStatement에서 사용될 SQL 문장
public static final String SQL_SELECT_ALL =
"SELECT * FROM " + TBL_NAME;
public static final String SQL_INSERT =
"INSERT INTO " + TBL_NAME + " VALUES(?, ?, ?)";
public static final String SQL_INSERT_SEQ =
"INSERT INTO " + TBL_NAME + " VALUES(" + SEQ_NAME + ".nextval, ?, SYSDATE)";
public static final String SQL_UPDATE_BIRTHDATE =
"UPDATE " + TBL_NAME
+ " SET " + COL_LABEL_BIRTHDATE + " = ?"
+ " WHERE " + COL_LABEL_NO + " = ?";
public static final String SQL_DELETE_BY_NO =
"DELETE FROM " + TBL_NAME
+ " WHERE " + COL_LABEL_NO + " = ?";
} // end Query(I)
3. [폴더] 11_Hierarchical
1) Hierarchy.sql
-- 계층형 쿼리(Hierarchical Query)
SELECT * FROM t_dept2; -- 서로의 계층관계 주목!
SELECT LPAD(dname, 10, '*') 부서명 FROM t_dept2;
-- LEVEL
SELECT dname, LEVEL
FROM t_dept2
CONNECT BY PRIOR dcode = pdept
START WITH dcode = 0001;
/*
* 해설
* LEVEL 은 오라클에서 계속 사용할 수 있는 것으로
* 해당 데이터가 몇번째 단계 이냐를 의미하는 것.
*
* CONNECT BY PRIOR : 각 row 들이 어떻게 연결되어야 하는지 조건 지정
* PRIOR를 어느쪽에 주느냐가 중요!
*/
-- PRIOR를 다른데 주면??
SELECT dname, LEVEL
FROM t_dept2
CONNECT BY dcode = PRIOR pdept
START WITH dcode = 0001;
SELECT dcode, dname, pdept, LEVEL
FROM t_dept2
CONNECT BY dcode = PRIOR pdept
--START WITH dcode = 1005 -- s/w지원(레벨1) > 기술부(레벨2) > 사장실(레벨3)
START WITH dcode = 1011; -- 영업4팀 > 영업기획팀 > 영업부 > 사장실
SELECT LPAD(dname, LEVEL * 6, '*') 부서명
FROM t_dept2
CONNECT BY PRIOR dcode = pdept
START WITH dcode = 0001;
--------------------------------------------------
SELECT * FROM t_emp2;
SELECT LPAD(e.name || ' ' || d.dname || ' ' || NVL(e.post, '사원'), LEVEL * 22, '-') "이름과 직급"
FROM t_emp2 e, (SELECT dname, dcode, pdept FROM t_dept2) d
WHERE e.deptno = d.dcode
CONNECT BY PRIOR e.empno = e.pempno
START WITH e.empno = 20000101;
SELECT dname, dcode, pdept FROM t_dept2;
SELECT LEVEL - 1 HR
FROM dual
CONNECT BY LEVEL <= 24
4. 입양 시각 구하기(2) : 프로그래머스 홈페이지 문제
: https://programmers.co.kr/learn/courses/30/lessons/59413?language=oracle
SELECT h.hr "HOUR", COUNT(datetime) "COUNT"
FROM
(SELECT LEVEL - 1 HR FROM dual CONNECT BY LEVEL <= 24) h
LEFT OUTER JOIN ANIMAL_OUTS a
ON h.hr = to_number(to_char(datetime, 'HH24'))
GROUP BY h.hr
ORDER BY 1;'웹_프론트_백엔드 > JAVA프레임윅기반_풀스택' 카테고리의 다른 글
| 2020.04.14 (0) | 2020.04.14 |
|---|---|
| 2020.04.13 (0) | 2020.04.13 |
| 2020.04.09 (0) | 2020.04.09 |
| 2020.04.08 (0) | 2020.04.08 |
| 2020.04.07 (0) | 2020.04.07 |
