2022.09.15 [THU]
- Day 31-
수업 주요 목차
- Database Modeling
- ERD
- 정규화
- Join
ERD ( Entity Relationship Diagram ) : 개체 관계 모델링 즉 데이터 모델링을 위한 다이어그램
| 논리 데이터 모델링(logical) | 물리 데이터 모델링(physical) |
| ✑ 논리적인 데이터 관리 및 관계를 정의한 모델 ✑ 전체 업무 범위와 업무 구성요소를 정의하고 확인할 수 있음 |
✑ 논리 데이터 모델을 DBMS 특성에 맞게 구체화시킨 모델 |
정규화 (Normalization) : 데이터베이스 설계시 데이터 중복을 최소화하고 이상현상을 방지하여 무결성을 보장하기 위해 데이터를 구조화하는(분해하는) 작업을 말한다. -> 이후 공부 예정
부모 테이블 : 참조 대상이 되는 테이블
자식 테이블 : 참조하는 테이블
| 식별관계 (identified relationship) | 비식별관계(non-identified relationship) |
| 부모 테이블의 기본키 혹은 복합키(이후 공부예정)가 자식 테이블의 기본키 혹은 복합키의 구성원으로 전이되는 관계 ( ex - 사원과 신체정보 ) |
부모 테이블의 기본키 혹은 복합키가 자식 테이블의 일반속성으로 전이되는 관계 (ex - 부서와 사원정보 ) |
** logical에서는 entity라고 부르고 physical에서는 table이라고 부름
- Foreign Key 제약조건 (Constraints)
- 목적: 다른 테이블의 정보를 참조해야 할 때 지정하는 제약조건으로 참조 무결성을 보장하게 한다.
- => 참조할 때 결점이 없는 데이터가 저장되게 한다.
예) 부모 테이블 - 부서 테이블 / 자식 테이블 - 사원 테이블
1. 사원 테이블 생성시 부서번호 컬럼에 Foreign Key 제약조건을 지정
2. 사원이 사원테이블에 저장하려고 할 때 사원의 부서정보는 반드시 참조하는 부모인 부서테이블의 저장되어 있는 부서번호로만 저장
3. 아니면 참조 무결성 제약조건 위배로 ERROR가 나서 사원이 등록되지 않는다.
=> 부서 테이블에 10 개발, 20 전략 부서 정보가 저장되어 있는 상태에서 사원 테이블에 아이유 사원이 등록되기 위해서는 아이유 사원의 부서번호는 10 또는 20만 가능하다.(아니면 ERROR)
예)
사원정보 관리
1. 실제 데이터를 기록해본다.
| 사원번호 | 사원명 | 직종 | 월급 | 부서번호 | 부서명 | 근무지 | 부서전화 |
| 1 | 아이유 | 개발 | 500 | 10 | 연구개발 | 판교 | 031 |
| 2 | 박보검 | 개발 | 700 | 10 | 연구개발 | 판교 | 031 |
2. 위와 같이 테이블을 생성해서 정보를 저장하면 부서 관련 데이터가 중복된다. => 시스템에 안좋은 영향을 줌
또한 부서 관련 정보들이 수정되면 다수의 사원 데이터 또한 함께 변경되어야 한다.
3. 정규화 과정(3 정규화 : 이행 함수적 종속을 제거)을 거쳐 테이블 분해되면 부서 테이블과 사원 테이블로 나뉘게 된다.
- 사원 테이블
| 사원번호 | 사원명 | 직종 | 월급 | 부서번호(FK) |
| 1 | 아이유 | 개발 | 500 | 10 |
| 2 | 박보검 | 개발 | 700 | 10 |
- 부서 테이블
| 부서번호 | 부서명 | 근무지 | 부서전화 |
| 10 | 연구개발 | 판교 | 031 |
부서 테이블 |----------0|<- 사원 테이블
▲
비식별관계 : 부모 테이블 -> 부서 테이블 / 자식 테이블 -> 사원 테이블
4. 하나의 부서는 0 or 1 or 다수의 사원을 가진다.
5. 한명의 사원은 반드시 하나의 부서를 가진다.
6. 이 때 참조무결성 보장을 위해 자식 테이블인 사원의 부서번호 컬럼에 FK(Foreign Key) 제약조건을 준다.
🔎 SQL 실습 내용
--참조 대상이 되는 부모테이블
CREATE TABLE k_department(
deptno NUMBER PRIMARY KEY,
dname VARCHAR2(100) NOT NULL,
loc VARCHAR2(100) NOT NULL,
tel VARCHAR2(100) NOT NULL
)
DROP TABLE k_department;
DROP TABLE k_employee;
--부서 테이블을 참조하는 자식 테이블
CREATE TABLE k_employee(
empno NUMBER PRIMARY KEY,
ename VARCHAR2(100) NOT NULL,
sal NUMBER NOT NULL,
job VARCHAR2(100) NOT NULL,
deptno NUMBER NOT NULL,
CONSTRAINT fk_k_emp FOREIGN KEY (deptno) REFERENCES k_department(deptno)
)
-- ERROR: table or view does not exist -> 부모 테이블 생성 전에 자식 테이블을 먼저 만들 수 없다.
--FOREIGN KEY test
INSERT INTO k_employee(empno,ename,sal,job,deptno) VALUES(1,'아이유',500,'개발',10);
-- ERROR: integrity constraint (SCOTT.FK_K_EMP) violated - parent key not found -> 참조하는 부모 테이블의 10번 부서정보가 존재하지 않다.
INSERT INTO k_employee(empno,ename,sal,job,deptno) VALUES(2,'김훈진',700,'개발',10);
INSERT INTO k_employee(empno,ename,sal,job,deptno) VALUES(3,'김동훈',900,'기획',20);
INSERT INTO k_employee(empno,ename,sal,job,deptno) VALUES(4,'문준호',800,'개발',10);
INSERT INTO k_employee(empno,ename,sal,job,deptno) VALUES(5,'채은서',1200,'개발',40);
--integrity constraint (SCOTT.FK_K_EMP) violated - parent key not found -> parent table에 없는 deptno다.
INSERT INTO k_department(deptno,dname,loc,tel) VALUES(10,'연구개발','판교',031);
INSERT INTO k_department(deptno,dname,loc,tel) VALUES(20,'공공사업','종로',02);
INSERT INTO k_department(deptno,dname,loc,tel) VALUES(30,'전략기획','오리',031);
COMMIT
SELECT*FROM k_department;
SELECT*FROM k_employee;
ERD를 UML에서 설계하면 아래와 같음
-UML
JOIN SQL : 여러 테이블의 정보를 결합해 조회하기 위한 SQL
방법 1 : Oracle Join
SELECT 컬럼명,컬럼명
FROM 테이블명 별칭, 테이블명 별칭
WHERE 별칭.컬럼명=별칭.컬럼명
방법 2 : ANSI SQL Join
SELECT 컬럼명,컬럼명
FROM 테이블명 별칭
INNER JOIN 테이블명 별칭
ON 별칭.컬럼명=별칭.컬럼명
예)
사원 번호가 1인 사원의 사원명(ename), 부서명(dname)을 조회하고자 한다.
사원번호의 사원명 -> 사원 테이블 / 부서명->부서 테이블
이 두 테이블을 조건에 맞게 결합해서 조회 -> JOIN SQL을 이용
--Oracle inner join
SELECT e.ename, d.dname
FROM k_department d, k_employee e
WHERE e.empno=1
AND d.deptno=e.deptno;
-- ANSI SQL Join
SELECT e.ename, d.dname
FROM k_employee e
INNER JOIN k_department d
ON (e.deptno=d.deptno)
WHERE e.empno=1;🔎 Eclipse 실습 내용
1. Join
사원번호(empno)로 사원 정보를 검색, 사원 정보는 부서정보까지 가지고 있는 정보을 조회 => 사원과 부서 테이블을 Join 해서 조회
- DepartmentVO
package model;
public class DepartmentVO {
private int deptNo;
private String dname;
private String loc;
private String tel;
public DepartmentVO() {
super();
}
public DepartmentVO(int deptNo, String dname, String loc, String tel) {
super();
this.deptNo = deptNo;
this.dname = dname;
this.loc = loc;
this.tel = tel;
}
public int getDeptNo() {
return deptNo;
}
public void setDeptNo(int deptNo) {
this.deptNo = deptNo;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
@Override
public String toString() {
return "DepartmentVO [deptNo=" + deptNo + ", dname=" + dname + ", loc=" + loc + ", tel=" + tel + "]";
}
}
-EmployeeVO
package model;
public class EmployeeVO {
private int empNo;
private String ename;
private long sal;
private String job;
private DepartmentVO departmentVO; // has a relationship
public EmployeeVO() {
super();
}
public EmployeeVO(int empNo, String ename, long sal, String job) {
super();
this.empNo = empNo;
this.ename = ename;
this.sal = sal;
this.job = job;
}
public EmployeeVO(int empNo, String ename, long sal, String job, DepartmentVO departmentVO) {
super();
this.empNo = empNo;
this.ename = ename;
this.sal = sal;
this.job = job;
this.departmentVO = departmentVO;
}
public int getEmpNo() {
return empNo;
}
public void setEmpNo(int empNo) {
this.empNo = empNo;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public long getSal() {
return sal;
}
public void setSal(long sal) {
this.sal = sal;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public DepartmentVO getDepartmentVO() {
return departmentVO;
}
public void setDepartmentVO(DepartmentVO departmentVO) {
this.departmentVO = departmentVO;
}
@Override
public String toString() {
return "EmployeeVO [empNo=" + empNo + ", ename=" + ename + ", sal=" + sal + ", job=" + job + ", departmentVO="
+ departmentVO + "]";
}
}
-EmployeeDAO
package model;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import common.DbConfig;
// Data Access Object
public class EmployeeDAO {
public void closeAll(ResultSet rs, PreparedStatement pstmt, Connection con) throws SQLException {
if(rs!=null)
rs.close();
if(pstmt!=null)
pstmt.close();
if(con!=null)
con.close();
}
public EmployeeVO findEmployeeByNo(int empNo) throws SQLException {
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
//EmployeeVO vo2=new EmployeeVO();
EmployeeVO vo2=null;
try {
con=DriverManager.getConnection(DbConfig.URL,DbConfig.USER,DbConfig.PASS);
//String sql="SELECT e.empno, e.ename, e.sal, e.job, d.deptno, d.dname, d.loc, d.tel FROM k_department d INNER JOIN k_employee e ON e.deptno=d.deptno WHERE e.empno=?";
StringBuilder sql=new StringBuilder("SELECT e.empno, e.ename, e.sal, e.job, d.deptno, d.dname, d.loc, d.tel ");
sql.append("FROM k_department d ");
sql.append("INNER JOIN k_employee e ");
sql.append("ON e.deptno=d.deptno ");
sql.append("WHERE e.empno=? ");
pstmt=con.prepareStatement(sql.toString());
pstmt.setInt(1, empNo);
rs=pstmt.executeQuery();
if(rs.next()) {
DepartmentVO vo=new DepartmentVO(rs.getInt(5),rs.getString(6),rs.getString(7),rs.getString(8));
vo2=new EmployeeVO(rs.getInt(1),rs.getString(2),rs.getLong(3),rs.getString(4),vo);
//위의 두 라인을 아래와 같이 사용 가능
DepartmentVO vo3=new DepartmentVO();
vo.setDeptNo(rs.getInt("deptno"));
vo3.setDname(rs.getString("dname"));
vo3.setLoc(rs.getString("loc"));
vo3.setTel(rs.getString("tel"));
EmployeeVO vo4=new EmployeeVO();
vo4.setEmpNo(rs.getInt("empno"));
vo4.setEname(rs.getString("ename"));
vo4.setJob(rs.getString("job"));
vo4.setSal(rs.getLong("sal"));
vo4.setDepartmentVO(vo3);
}
} finally {
closeAll(rs,pstmt,con);
}
return vo2;
}
}
-TestCase
package test.step1;
import java.util.Scanner;
import model.EmployeeDAO;
import model.EmployeeVO;
public class TestCaseFindEmployeeByNo {
public static void main(String[] args) {
Scanner scanner=new Scanner(System.in);
System.out.print("검색할 사원번호를 입력하세요: ");
int empNo=scanner.nextInt();
EmployeeDAO dao=new EmployeeDAO();
try {
EmployeeVO empVO=dao.findEmployeeByNo(empNo);
if(empVO==null) {
System.out.println("사원이 존재하지 않습니다.");
} else{
System.out.println("사원번호 "+empNo+" 사원에 대한 검색결과");
//System.out.println(empVO);
System.out.println(empVO.getEmpNo());
System.out.println(empVO.getEname());
System.out.println(empVO.getJob());
System.out.println(empVO.getSal());
System.out.println(empVO.getDepartmentVO().getDeptNo());
System.out.println(empVO.getDepartmentVO().getDname());
System.out.println(empVO.getDepartmentVO().getTel());
System.out.println(empVO.getDepartmentVO().getLoc());
}
} catch(Exception e) {
e.printStackTrace();
}
scanner.close();
}
}
↓
검색할 사원번호를 입력하세요: 1
사원번호 1 사원에 대한 검색결과
1
아이유
개발
500
10
연구개발
031
판교
🤮error🤮
1. SQL 구문이 길어서 String대신 StringBuilder를 써보겠다고 append로 잘 써놓고 PreparedStatement에서 실행시킬 때 toString()을 추가를 안해줘서 계속 에러가 떴었다..더 빨리 끝낼 수 있었는데 여기서 시간 다 잡아먹힘...^^ 아무튼 그래도 혼자서 어찌저찌 성공!
👾시행착오👾
1. SQL SELECT 뒤 줄줄줄줄 쓰는게 시간이 아까워서 SELECT e.*, d.*로 했다가 deptno가 중복이 되어서 생각처럼 간단해지지가 않았다. 근데 가능은 해서(이 후에 rs.get~~할 때 숫자/컬럼명 하나 띄어서 해주면 됨)
2. EmployeeVO vo2를 인스턴스 변수로 선언할 때 아예 생성자를 만들어 줬었는데(안만드니까 return vo2에서 "vo2 cannot be resolved to a variable(범위 내에서 변수의 초기화를 감지할 수 없음)"이 떴음) 그냥 나중에 강사님 풀이보고 null로 준다음 이 후 할당되었어도 가능했다는 것을 깨달았다.(안그래도 코드 짜면서 좀 찝찝했었는데 역시나...)
3. 매애애앤 처음에는 EmployeeVO 생성자 안에다가 무식하게 다 때려넣다가 너무 하드코딩이라 '아, 이거 아닌데...'하고 잠깐 멈춰서 Employee생성자 매개변수들 살펴보니 DepartmentVO를 만들어 뒀던 걸 발견하고 술술 풀렸다.
//오늘의 숙제
복습+정규화 예습!!
