'Database' 카테고리의 글 목록

Database

MySQL 새 계정 만들기 (Mac ver) - terminal VS. MySQL 워크벤치 2024.07.25
[MySQL] INNER JOIN / OUTER JOIN / CROSS JOIN / SELF JOIN 2024.02.25 2
[MySQL] JDBC 예제 2024.02.25
[MySQL] JDBC 정의, 로딩, 프로그램 개발 절차 2024.02.25
[MySQL] 데이터베이스 모델링 | 참조 무결성 2024.02.25 1
[MySQL] 데이터베이스 모델링 | 1~3 정규화, 역정규화 2024.02.25 1

PREV 이전 1 2 3 4 NEXT 다음

MySQL 새 계정 만들기 (Mac ver) - terminal VS. MySQL 워크벤치

리버 River 2024. 7. 25. 18:27

2024. 7. 25. 18:27

1. MySQL 접속:

✓ (terminal) root 사용자로 MySQL에 접속

mysql -u root -p

2. 데이터베이스 생성:

mysql> CREATE DATABASE 'DB명';

3. 기존 사용자 확인:

-- root 계정의 데이터베이스 중 'mysql' 이라는 데이터베이스 선택하기
mysql> use mysql;

-- 'user' 이라는 테이블의 정보에서 사용자 계정 확인하기
mysql> select host, user from user;

+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | doraflower       |
| %         | springstudent    |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+

4. 기존 사용자 삭제:

mysql> drop user 'doraflower'@'%';

5. 새 사용자 생성:

mysql> CREATE USER '계정ID'@'localhost' IDENTIFIED BY '비밀번호';

6. 사용자에게 권한 부여:

mysql> grant all privileges on DB명.* to 계정ID@localhost identified by '비밀번호';

7. 권한 확인:

mysql> show grants for '계정ID'@localhost;

8. 새로운 계정으로 접속 후 DB 설정:

// 새로만든 계정으로 접속
mysql -u {계정ID} -p

// root 계정으로 만든 DB 이전
mysql> use {DB명};

9. MySQL 새 커넥션 생성:

✓ MySQL Connections 옆 + 버튼 클릭해서 새로운 connection 생성!

내용 참고 : https://velog.io/@ejayjeon/MYSQL-1.-%EA%B3%84%EC%A0%95-%EC%83%9D%EC%84%B1-%EA%B6%8C%ED%95%9C-%EB%B6%80%EC%97%AC

[MYSQL] 1. 계정 생성 / 권한 부여

<small style="color: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER

velog.io

MySQL Workbench에서 새 계정 만드는 방법

1. root 계정으로 접속 후 좌측 Administration 창으로 이동

2. 하단 Users and Privileges 클릭

3. 오른쪽 창 하단에 add account 클릭

4. Login name, password 설정

5. 필요시 Administration Roles 에서 역할 설정

6. Schema Privileges - Add Entry에서 생성된 DB 추가 가능

워크벤치 사용 추천... 🙏🏻

저작자표시 비영리 변경금지

'Database > MySQL' 카테고리의 다른 글

[MySQL] INNER JOIN / OUTER JOIN / CROSS JOIN / SELF JOIN (2)	2024.02.25
[MySQL] JDBC 예제 (0)	2024.02.25
[MySQL] JDBC 정의, 로딩, 프로그램 개발 절차 (0)	2024.02.25
[MySQL] 데이터베이스 모델링 \| 참조 무결성 (1)	2024.02.25
[MySQL] 데이터베이스 모델링 \| 1~3 정규화, 역정규화 (1)	2024.02.25

[MySQL] INNER JOIN / OUTER JOIN / CROSS JOIN / SELF JOIN

리버 River 2024. 2. 25. 20:09

2024. 2. 25. 20:09

1. 조인 Join

👾 두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어내는 것

⚡️ 데이터베이스 테이블은 중복과 공간 낭비를 피하고 데이터 무결성을 위해 여러 개 테이블로 분리해 저장

⚡️ 분리된 테이블은 서로 관계 Relation 를 맺고 있는데, 가장 많이 사용하는 관계는 '1대 다' 관계

ex. 학생 한 명이 여러 과목의 학점을 받는 것, 한 개의 아이디로 여러 상품을 주문하는 것

1) INNER JOIN (내부 조인)

 SELECT <열 목록>
 FROM <첫 번째 테이블>
      INNER JOIN <두 번째 테이블>
      ON <조인될 조건>
 [WHERE 검색조건]

· 첫 번째 테이블은 구매테이블(buytbl), 두 번째 테이블은 회원테이블(usertbl)

 SELECT * FROM buytbl
      INNER JOIN usertbl
         ON buytbl.userID = usertbl.userID
      WHERE buytbl.userID = 'JYP';

📌 추출 과정

1. 구매 테이블의 userID인 'JYP'를 추출한다.

2. 'JYP'와 동일한 값을 회원 테이블의 userID열에서 검색한 후 찾으면 구매 테이블과 회원 테이블의 두 행을 결합

📌 WHERE 절을 생략하면 buytbl 테이블의 모든 행에 대해서 위와 동일한 방식으로 추출

👾 필요한 열만 추출하는 방법

 SELECT userID, name, prodName, addr, CONCAT(mobile1, mobile2) AS '연락처'
    FROM buytbl
      INNER JOIN usertbl
         ON buytbl.userID = usertbl.userID
      ORDER BY num;

📌 위의 구문을 실행하면 다음과 같은 에러 코드가 뜬다.

Error Code: 1052. Column 'userID' in field list is ambiguous

💡 userID의 경우 두 테이블 모두에 들어 있어 어느 테이블의 userID 추출할 지 명시해줘야 함

 SELECT buytbl.userID, name, prodName, addr, CONCAT(mobile1, mobile2) AS '연락처'
    FROM buytbl
      INNER JOIN usertbl
         ON buytbl.userID = usertbl.userID
      ORDER BY num;

👾 코드가 길어지면 복잡해 보이기 때문에 각 테이블에 '별칭 Alias' 을 부여하는 방법

 SELECT B.userID, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
    FROM buytbl B
      INNER JOIN usertbl U
         ON B.userID = U.userID
      WHERE B.userID = 'JYP';

📌 위와 같이 FROM 절에 나오는 테이블의 이름 뒤에 별칭을 붙여주면 된다.

👾 세 개의 테이블을 조인하는 방법

· 첫 번째 stdTbl(학생테이블) / 두 번째 stdclubTbl / 세 번째 clubTbl

-- 학생 테이블, 동아리 테이블, 학생동아리 테이블을 이용해서 학생을 기준으로 학생 이름/지역/가입한 동아리/동아리방을 출력
SELECT S.stdName, S.addr, C.clubName, C.roomNo
  FROM stdTbl S
     INNER JOIN stdclubTbl SC
        ON S.stdName = SC.stdName
     INNER JOIN clubTbl C
        ON SC.clubName = C.clubName
  ORDER BY S.stdName;

2) OUTER JOIN (외부 조인)

👾 주종관계에 있는 테이블에서 주테이블의 모든 레코드를 보여주고 조건을 만족하는 부테이블의 필드를 같이 출력

 SELECT <열 목록>
 FROM <첫 번째 테이블(LEFT 테이블)>
  <LEFT|RIGHT|FULL> OUTER JOIN <두 번째 테이블 (RIGHT 테이블)>
       ON <조인될 조건>
 [WHERE 검색조건];

📌 전체 회원의 구매기록 구하기 (단, 구매 기록 없는 회원도 출력)

 SELECT U.userID, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
    FROM usertbl U
      LEFT OUTER JOIN buytbl B     -- LEFT OUTER JOIN은 왼쪽 테이블(usertbl)의 것은 모두 출력되어야 한다는 뜻
         ON U.userID = B.userID
    ORDER BY U.userID;

📌 RIGHT OUTER JOIN을 사용해서 동일한 결과 값을 얻으려면 단순히 왼쪽과 오른쪽 테이블 위치만 바꿔주면 됨

💡 FULL OUTER JOIN (전체 조인) : LEFT JOIN + RIGHT JOIN

➡️ 왼쪽 테이블과 오른쪽 테이블의 모든 행을 반환. 조인 조건에 맞는 데이터가 없는 경우, 해당 테이블의 열에는 NULL 값이 채워짐

3) CROSS JOIN (상호 조인)

👾 한쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인시키는 기능

➡️ 결과 개수는 두 테이블의 개수를 곱한 개수

👾 카티션 곱 Cartesian Product이라고도 부른다.

👾 ON 구문 사용 x, 용도는 테스트로 사용할 많은 용량의 데이터를 생성할 때 주로 사용

📌 회원 테이블과 구매 테이블의 CROSS JOIN 구문

 SELECT COUNT(*) AS '데이터 개수' FROM buytbl CROSS JOIN usertbl;

4) SELF JOIN (자체 조인)

👾 자기 자신과 자기 자신이 조인한다는 의미

👾 대표적인 예가 조직도와 관련된 테이블

 -- 우대리 상관의 연락처를 확인
 SELECT A.emp AS '부하직원' , B.emp AS '직속상관', B.empTel AS '직속상관연락처'
   FROM empTbl A
      INNER JOIN empTbl B
         ON A.manager = B.emp
   WHERE A.emp = '우대리';

[ 내용 참고 : 책 '이것이 MySQL'이다 ]

저작자표시 비영리 변경금지

'Database > MySQL' 카테고리의 다른 글

MySQL 새 계정 만들기 (Mac ver) - terminal VS. MySQL 워크벤치 (0)	2024.07.25
[MySQL] JDBC 예제 (0)	2024.02.25
[MySQL] JDBC 정의, 로딩, 프로그램 개발 절차 (0)	2024.02.25
[MySQL] 데이터베이스 모델링 \| 참조 무결성 (1)	2024.02.25
[MySQL] 데이터베이스 모델링 \| 1~3 정규화, 역정규화 (1)	2024.02.25

[MySQL] JDBC 예제

리버 River 2024. 2. 25. 19:41

2024. 2. 25. 19:41

1. DB 연결

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class connection {

 public static void main(String[] args) {
    -- try 블럭 안에 넣으면 finally 구문에서 연결끊기가 실행 안됨.
	Connection conn = null; 
	try {
		-- JDBC Driver를 메모리로 로딩하고, DriverManager에 등록
		Class.forName("com.mysql.cj.jdbc.Driver");
		    
		-- 연결하기
		conn = DriverManager.getConnection(
		    	"jdbc:mysql://localhost:3306/sample",
		    	"(아이디)",
		    	"(비밀번호)"
		       );
		    		
		    System.out.println("연결 성공");
		    	    
	} catch (ClassNotFoundException e) {
			e.printStackTrace(); -- 예외 종류, 발생 이유, 어디서 발생했는지 추적한 내용까지 출력
	} catch (SQLException e) {
			e.printStackTrace();
	} finally {
		 if (conn != null) { -- null 이면 굳이 닫을 필요 없기 때문.
			try {
				/* 연결 끊기 (try 구문 안에 넣으면 예외 발생시 실행이 안되기 때문에 
                finally 구문에서 무조건 실행되도록 함.) */
				conn.close();
				System.out.println("연결 끊기");
			} catch (SQLException e) {}
		}
	}
 }
}

2. INSERT

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class UserInsert {

   public static void main(String[] args) {
	 Connection conn = null;
	 try {
		  Class.forName("com.mysql.cj.jdbc.Driver");
		    
		  conn = DriverManager.getConnection(
		    	 "jdbc:mysql://localhost:3306/sample",
		    	 "(아이디)",
		    	 "(비밀번호)"
		    	 );
		    		
		  System.out.println("연결 성공");
		    
		  -- 매개변수화된 SQL문 작성
		  String sql = "" + "INSERT INTO users (
                       userid, username, userpassword, userage, useremail) " +
		    		   "VALUES (?,?,?,?,?)"; // 매개변수에 들어갈 값을 물음표로 표현해준다.
		    		
		   -- PreparedStatement 얻기 및 값 지정
		   PreparedStatement pstmt = conn.prepareStatement(sql); 
		   -- 연결 객체를 통해 sql문을 실행해서 결과문을 받을 statement를 준비하고,
	       -- 구현객체가 리턴되어 PreparedStatement 인터페이스 변수에 대입된다. 
              (아직 실행된 상태는 아니다.)
		    
		   pstmt.setString(1,  "winter"); 
		   pstmt.setString(2, "한겨울");
		   pstmt.setString(3, "12345");
		   pstmt.setInt(4, 25);
		   pstmt.setString(5, "winter@mycompany.com");
		    
		   -- SQL문 실행
		   int rows = pstmt.executeUpdate(); -- DB에서 insert문을 실행하는 메소드
		   System.out.println("저장된 행 수: " + rows); -- DB에 반영된 행의 수를 뜻함.
		   -- 정상적으로 실행되었다면 1이 나옴.		
		    
		   -- PreparedStatement 닫기
		   pstmt.close();
		   
	} catch (ClassNotFoundException e) {
			e.printStackTrace();
	} catch (SQLException e) {
			e.printStackTrace();
	} finally {
		if (conn != null) { 
			try {
				-- 연결 끊기 
				conn.close();
				System.out.println("연결 끊기");
			} catch (SQLException e) {}
		}
	}

 } 
}

3. UPDATE

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

public class BoardUpdate {

	public static void main(String[] args) {
		Connection conn = null;
		try {
		    Class.forName("com.mysql.cj.jdbc.Driver");
		    
		    conn = DriverManager.getConnection(
		    		"jdbc:mysql://localhost:3306/sample",
		    		"(아이디)",
		    		"(비밀번호)"
		    		);
		    		
		    -- 매개변수화된 SQL문 작성
		    String sql = new StringBuilder() -- 문자열 연결 메서드
                   .append("UPDATE boards SET ")
                   .append("btitle=?, ")
                   .append("bcontent=?, ")
                   .append("bfilename=?, ")
                   .append("bfiledata=? ")
                   .append("WHERE bno=?")
                   .toString();
		    
		    -- PreparedStatement 얻기 및 저장
		    PreparedStatement pstmt = conn.prepareStatement(sql);
		
		    pstmt.setString(1, "눈사람");
		    pstmt.setString(2, "눈으로 만든 사람");
		    pstmt.setString(3, "snowman.jpg");
		    pstmt.setBlob(4, new FileInputStream("src/DBtest/photo2.jpg"));
		    pstmt.setInt(5, 3); // boards 테이블에 있는 게시물 번호(bno) 지정
		    
		    -- SQL문 실행
		    int rows = pstmt.executeUpdate();
		    System.out.println("수정된 행 수: " + rows);
		    
		    -- PreparedStatement 닫기
		    pstmt.close();
		    
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(conn != null) {
				try {
				-- 연결 끊기
				conn.close();
			} catch (SQLException e) {}
		}
	}
	}

}

4. DELETE

public class BoardDelete {

	public static void main(String[] args) {
		Connection conn = null;
		try {
		    Class.forName("com.mysql.cj.jdbc.Driver");
		    
		    conn = DriverManager.getConnection(
		    		"jdbc:mysql://localhost:3306/sample",
		    		"(아이디)",
		    		"(비밀번호)"
		    		);
		    		
		    -- 매개변수화된 SQL문 작성
		    String sql = "DELETE FROM boards WHERE btitle=?";
		    
		    -- PreparedStatement 얻기 및 저장
		    PreparedStatement pstmt = conn.prepareStatement(sql);
		    pstmt.setString(1, "눈사람");
		    
		    
		    -- SQL문 실행
		    int rows = pstmt.executeUpdate();
		    System.out.println("삭제된 행 수: " + rows);
		    
		    -- PreparedStatement 닫기
		    pstmt.close();
		    
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(conn != null) {
				try {
				-- 연결 끊기
				conn.close();
			} catch (SQLException e) {}
		}
	}
	}

}

5. SELECT

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.mysql.cj.jdbc.Blob;

public class BoardSelect {

	public static void main(String[] args) {
        Connection conn = null;
		
		try {
			-- JDBC Driver 등록
		    Class.forName("com.mysql.cj.jdbc.Driver");
		    
		    -- DB 연결
		    conn = DriverManager.getConnection(
                     "jdbc:mysql://localhost:3306/sample", 
                     "(아이디)", 
                     "(비밀번호)");
		    
		    -- 매개변수화된 SQL문 작성
		    String sql = "SELECT bno, btitle,bcontent, bwriter, 
                          bdate, bfilename, bfiledata 
                          from boards where bwriter =?";
		    
		    -- PreparedStatement 얻기 및 값 지정
		    PreparedStatement pstmt = conn.prepareStatement(sql);
		    pstmt.setString(1,"winter");
		    
		    -- SQL문 실행 후, ResultSet을 통해 데이터 읽기.
		    ResultSet rs = pstmt.executeQuery();
		    List<Board> boards = new ArrayList<>();
		    while(rs.next()) {
		    	Board board = new Board(); -- board 테이블의 행을 저장할 객체를 만듦.
		    	board.setBno(rs.getInt("bno"));
		    	board.setBtitle(rs.getString("btitle"));
		    	board.setBcontent(rs.getString("bcontent"));
		    	board.setBwriter(rs.getString("bwriter"));
		    	board.setBdate(rs.getDate("bdate"));
		    	board.setBfilename(rs.getString("bfilename"));
		    	board.setBfileData(rs.getBlob("bfileData"));
		    	
		        System.out.println(board); -- 콘솔에 출력
		    }
		    rs.close();
		    
		    
		    pstmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) { 
				try {
					-- 연결 끊기 
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}
}

[ 내용 참고 : 책 '이것이 자바다' ]

저작자표시 비영리 변경금지

'Database > MySQL' 카테고리의 다른 글

MySQL 새 계정 만들기 (Mac ver) - terminal VS. MySQL 워크벤치 (0)	2024.07.25
[MySQL] INNER JOIN / OUTER JOIN / CROSS JOIN / SELF JOIN (2)	2024.02.25
[MySQL] JDBC 정의, 로딩, 프로그램 개발 절차 (0)	2024.02.25
[MySQL] 데이터베이스 모델링 \| 참조 무결성 (1)	2024.02.25
[MySQL] 데이터베이스 모델링 \| 1~3 정규화, 역정규화 (1)	2024.02.25

[MySQL] JDBC 정의, 로딩, 프로그램 개발 절차

리버 River 2024. 2. 25. 19:37

2024. 2. 25. 19:37

1. JDBC ; Java DataBase Connectivity

👩🏻‍💻 자바 / JSP 프로그램 내에서 데이터베이스와 관련된 작업을 처리할 수 있도록 도와주는 자바 표준 인터페이스

👩🏻‍💻 관계형 데이터베이스 시스템에 접근하여 SQL 문을 실행하기 위한 자바 API 또는 자바 라이브러리

⚡️ JDBC API를 사용하면 DBMS의 종류에 상관없이 데이터베이스 작업을 처리할 수 있다.

➡️ 데이터베이스 관리시스템(DBMS)의 종류 상관없이 동일하게 사용할 수 있는 클래스와 인터페이스로 구성되어 있기 때문

⚡️ JDBC API는 java.sql.* 패키지에 의해 구현되며, 이 패키지는 여러 종류의 데이터베이스에 접근 할 수 있다.

※ java.sql.* 패키지는 단일 API를 제공하는 클래스와 인터페이스의 집합

1) DB 연결

👾 클라이언트 프로그램에서 DB와 연결하려면 해당 DBMS의 JDBC Driver가 필요

📌 연결에 필요한 정보

a. DBMS가 설치된 컴퓨터(서버)의 IP 주소

b. DBMS가 허용하는 포트(Port) 번호

c. 사용자(DB 계정) 및 비밀번호

d. 사용하고자 하는 DB 이름

2) JDBC에 포함되어 있는 클래스와 인터페이스

(1) DriverManager

- JDBC Driver를 관리하며 DB와 연결해서 Connection 구현 객체를 생성

(2) Connection

- Statement, PreparedStatement, CallableStatement 구현 객체를 생성하며 Transaction 처리 및 DB 연결을 끊을 때 사용

(3) Statement

- 주로 변경되지 않는 정적 SQL 문을 실행할 때 사용

(4) PreparedStatement

- 매개변수화된 SQL 문을 사용할 수 있기 때문에 편리성과 보안성이 좋음

- SQL 문을 미리 만들어 두고 변수를 따로 입력하는 방식으로 효율성이나 유지 보수 측면에서 유리

- 기본적으로 Statement 클래스를 상속받기 때문에 Statement 클래스의 모든 메서드를 모두 사용할 수 있음

(5) CallableStatement

- DB에 저장되어 있는 프로시저(procedure)와 함수(function)를 호출할 때 사용

(6) ResultSet

- DB에서 가져온 데이터를 읽을 때 사용

3) JDBC 프로그램 개발 절차

👾 JDBC 뿐만 아니라 오픈소스 라이브러리나 프레임워크 등을 이용해서 프로그램을 작성한다는 것은 해당 라이브러리나 프레임워크의 구조와 성격을 이해하고, 제기하는 규격이나 구조에 맞게 프로그램을 만들어야 함을 의미

📌 1단계 : JDBC 드라이버 로드

· 데이터베이스를 접속하려면 먼저 해당 데이터베이스의 JDBC driver를 로딩

· 이런 과정이 필요한 이유는 JDK에 포함된 JDBC는 실제 구현 내용이 빠진 스펙만 인터페이스로 포함 하기 때문

➡️ 실제로 동작하려면 각 데이터베이스 공급업체에서 구현한 클래스들을 사전에 로딩해야 함

· Class.forName() 메서드를 사용

// MySQL 버전
Class.forName("com.mysql.cj.jdbc.Driver");

⚡️ 이 과정에서 JDBC Driver 클래스의 static 블록이 실행되면서 Driver Manager에 JDBC Driver 객체를 등록하게 된다.

// static 블록
class MysqlDriver {
    static {
       Driver driver = new MysqlDriver();
       DriverManager.registerDriver(driver);
    }
}

⚡️ DriverManager에 JDBC Driver가 등록되면 getConnection() 메소드로 DB와 연결할 수 있다.

📌 2단계 : 데이터베이스 연결

· 데이터베이스를 연결하려면 몇 가지 추가 정보가 필요

① JDBC URL

- JDBC URL은 다양한 데이터베이스 정보를 포함

- 각 데이터베이스별로 JDBC URL이 다르므로, 사용하는 데이터 베이스 매뉴얼을 참고하여 작성

jdbc:<서브 프로토콜>:<데이터 원본 식별자>

// MySql의 형식
jdbc:mysql://DB 서버의 IP/스키마(데이터베이스):PORT
ex) jdbc:mysql://localhost:3306/sample

🔎 IP주소 : MySql 데이터베이스가 설치된 컴퓨터의 IP 주소 또는 도메인 이름

🔎 스키마 : 데이터베이스에서 생성된 스키마(데이터베이스) 이름

🔎 PORT : 기본 설정값인 3306 포트를 사용할 때는 생략 가능

② Connection 클래스 인스턴스 래퍼런스 얻기

- DriverManager의 getConnection 메서드를 사용

Connection conn = DriverManager.getConnection(JDBC URL,"아이디", "비밀번호");

🔎 JDBC URL : 해당 데이터베이스에 맞게 미리 정의한 문자열

🔎 아이디와 비밀번호 : 데이터베이스 자체에서 관리하는 계정

➡️ 옆에 사진에 나와 있는 것처럼 보통 아이디는 " root " 비밀번호는 mysql 설치할때 입력한 비밀번호 뜻함

📌 3단계 : Statement 생성

· Statement는 데이터베이스를 연결하여 SQL문을 수행할 수 있도록 하는 클래스

· 대표적으로 아래 메서드를 사용

① executeQuery()

- SELECT 문을 수행할 때 사용

- 반환값은 ResultSet 클래스의 인스턴스로, 해당 SELECT 문의 결과에 해당하는 데이터에 접근할 수 있는 방법을 제공

- 단독으로 사용하기 보다는 ResultSet과 함께 사용할 때가 많다

String sql = "select * from test";
Statement stmt = conn.createStatement();
stmt.executeQuery(sql);

② executeUpdate()

- UPDATE 문, DELETE 문 등을 수행할 때 사용

- 반환값은 int형 데이터로, 처리된 데이터 수를 반환

Statement stmt = conn.createStatement();

stmt.executeUpdate("insert into test values (' " +
getUserName() + " ', ' " + getEmail() + " ') ");

👾 사용자 입력값을 포함해야 할 때는 SQL 문을 변수와 결합해서 만들어야 하므로 코드가 다소 복잡해짐

➡️ 컬럼 수가 많아지면 코드 작성이나 유지보수가 어려움

👾 복잡한 SQL 문을 사용할 때는 Statement의 향상된 기능을 제공하는 PreparedStatement을 사용

③ PreparedStatement

- PreparedStatement는 SQL 문을 미리 만들어 두고 변수를 따로 입력하는 방식

- 기본적으로 Statement 클래스를 상속받기 때문에 Statement 클래스의 메서드를 모두 사용

PreparedStatement pstmt = conn.preparedStatement("insert
into test values(?, ?) ");
pstmt.setString(1, getUserName());
pstmt.setString(2, getEmail());
pstmt.executeUpdate();

🔎 행은 늘어났지만 Statement를 사용할때 보다는 프로그램이 간결해짐

🔎 SQL 문에서 변수가 와야 할 위치에는 물음표만 적어 두고, 물음표자리에는 setXxx() 메서드로 값을 설정

➡️ Xxx에는 String, Int 와 같이 자료형별로 다르게 들어감

④ Statement의 close()

- 다른 JDBC 리소스와 마찬가지로 Statement 역시 사용하지 않을 때는 닫아 주어여 함

- PreparedStatement도 마찬가지

stmt.close();
pstmt.close();

📌 4단계 : SQL 문 전송

· 데이터를 입력, 수정, 삭제하려고 SQL문을 만들때는 PreparedStatement를 사용하여 변수와 적절히 조합하면 됨

➡️ executeUpdate() 메서드를 사용

int count = pstmt.executeUpdate();

💡 이 때 executeUpdate() 메서드는 처리한 로우의 개수를 반환

➡️ 처리된 항목이 없다면 0을 반환

💡 처리 결과는 반환값을 받아서 확인해야 하고, 그럴 필요가없다면 굳이 반환을 받지 않아도 됨

📌 5단계 : 결과받기

· 데이터베이스에서 데이터 결과를 받으려면 Statement나 PreparedStatement의 executeQuery()를 사용

· 입력, 수정, 삭제와 달리 데이터를 가져올 때는 가져온 결과 데이터를 처리하는 ResultSet 객체가 필요

ResultSet rs = pstmt.executeQuery():

💡 ResultSet은 데이터베이스 내부적으로 수행한 SQL 문의 처리 결과를 JDBC에서 쉽게 관리할 수 있게 함

💡 필요한 데이터를 모두 가져온 후 ResultSet을 close()하고나서 connection을 close() 해야 함

💡 ResultSet은 next() 메서드를 사용하여 다음 로우로 이동

➡️ 커서를 최초 데이터의 위치로 이동시키려면 ResultSet을 사용하기 전에 rs.next() 메서드를 한 번 호출

· 대부분은 다음과 같이 executeQuery() 메서드를 수행한 후 while(rs.next())와 같이 더 이상 로우가 없을 때까지 루프를 돌면서 데이터를 처리하는 방법을 사용. 이 때 로우에서 각 컬럼값을 가져오려면 rs.getXxx() 메서드를 사용

ResultSet rs = psmt.executeQuery();
while(rs.next()) {
  name = rs.getString(1); // or rs.getString("name");
  age = rs.getInt(2); // or rs.getInt("age");
}
rs.close();

🔎 getXxx() 메서드에서 컬럼을 지정하는 방법으로 해당 컬럼의 index 값(1부터 시작) 이나 컬럼 이름을 사용

🔎 가급적 index보다는 컬럼 이름을 사용하는 편이 코드 이해나 유지보수에 더 유리

🔎 ResultSet의 사용이 끝났으면 rs.close() 메서드를 사용하여 ResultSet을 닫아 주어야 함

📌 6단계 : 연결 해제

· 데이터베이스와 연결을 관리하는 Connection 인스턴스는 사용한 후 반납하지 않으면 계속 연결을 유지

· 데이터베이스 연결은 해당 연결을 이용한 작업이 모두 끝나는 시점에서 close() 메서드를 사용하여 해제해야 함

conn.close();

4) 주요 클래스

(1) Statement 주요 메서드

boolean execute(String sql)	👾 주어진 SQL문 sql을 실행 - select 구문을 실행하는 경우에는 true를 리턴, 그렇지 않은 경우에는 false 리턴 - true를 리턴하는 경우에는 getResultSet() 메서드를 이용하여 ResultSet 객체를 생성 - update, insert, delete 구문을 사용하는 경우에는 false를 리턴하고, getUpdateCount() 메서드를 이용하여 영향받은 행의 갯수를 알아낼수 있음
ResultSet executeQuery(String sql)	👾 select 구문을 실행할 때 사용
int executeUpdate(String sql)	👾 select를 제외한 나머지 insert, create, update, delete 구문을 실행할 때 사용 - 이 때 영향을 받은 행의 개수를 리턴
ResultSet getResultSet()	👾 현재 SQL 구문을 실행한 결과를 리턴 - select 구문을 실행했을 경우에만 유효
int getUpdateCount()	👾 현재 SQL 구문의 실행으로 영향을 받은 행의 개수를 리턴 - select를 제외한 나머지 구문에서만 유효

(2) ResultSet

👩🏻‍💻 Statement 객체의 getResultSet(), executeQuery() 메서드가 리턴하는 객체로서 select 구문 실행 결과를 다룰 때 사용

👩🏻‍💻 select 구문을 실행하여 ResultSet 객체가 생성되면 커서 cursor 가 만들어지고 select 구문 실행 결과를 가르킴

boolean next()	👾 커서를 다음 행으로 이동 - ResultSet 객체가 처음 생성된 직후에 next() 메서드를 한 번 호출해야 첫 번째 행을 커서가 가르키게 됨 - 성공적으로 커서가 이동하면 true를 리턴하고 더 이상 없어서 커서를 이동시킬 수 없으면 false를 리턴
boolean previous()	👾 커서를 이전 행으로 이동 - 성공적으로 커서가 이동하면 true를 리턴하고 더 이상 결과가 없어서 커서를 이동시킬 수 없으면 false를 리턴
Statement getStatement()	👾 현재 ResultSet을 생성시킨 Statement 객체를 리턴
<자료형> get<자료형>(String colName), <자료형> get<자료형>(int colIndex)	👾 colName에 지정된 속성명에 해당하는 실제 데이터를 리턴 - 예를 들어 속성의 데이터 자료형이 String 형과 호환되는 속성이라면 getString() 메서드를 사용 - 속성 이름 대신 속성의 위치 정보를 colIndex로 줄 수 있음 맨 앞의 속성은 '1', 두 번째 속성은 '2'와 같이 숫자로 속성의 위치를 지정

(3) PreparedStatement

👩🏻‍💻 Statement 객체의 execute계열 메서드는 모두 SQL 문을 컴파일하고 바로 수행시켜서 결과를 리턴

👩🏻‍💻 PreparedStatement 객체는 SQL 문을 미리 컴파일하여 실행하기 직전의 상태로 만든 후 실제 실행은 나중에 필요에 따라 할 수 있음

👩🏻‍💻 같은 SQL 문을 여러 번 실행시켜야 하는 경우에 는 Statement 객체보다 PreparedStatement 객체 사용이 더 효과적

👩🏻‍💻 PreparedStatement 객체로 생성되는 SQL 문은 마치 함수처럼 매개변수를 설정, 필요에 따라 매개변수의 값을 바꿔 실행가능

➡️ 비슷한 SQL 구문을 반복적으로 실행시켜야 하는 경우에도 유용

➡️ 매개변수를 정의할 때에는 '?' 을 사용하고 매개변수에 값을 설정할때에는 set계열 메서드를 사용

📌 DBMS와 Java의 자료형 변환

· DBMS에서의 컬럼의 자료형과 Java 자료형, 그리고 관련된 JDBC 메소드 간의 변환표

DBMS 자료형	Java 자료형	ResultSet 메서드	PreparedStatement 메서드
CHAR	String	getString	setString
VARCHAR	String	getString	setString
DECIMAL	java.math.BigDecimal	getBigDecimal	setBigDecimal
NUMBER	java.math.BigDecimal	getBigDecimal	setBigDecimal
TINYINT	byte	getByte	setByte
SMALLINT	short	getShort	setShort
INTEGER	int	getInt	setInt
BIGINT	long	getLong	setLong
REAL	float	getFloat	setFloat
FLOAT	double	getDouble	setDouble
DOUBLE	double	getDouble	setDouble
DATE	java.sql.Date	getDate	setDate
TIME	java.sql.Time	getTime	setTime
CLOB	Clob	getClob	setClob
BLOB	Blob	getBlob	setBlob
TIMESTAMP	java.sql.Timestamp	getTimestamp	setTimestamp

[ 내용 참고 : IT 학원 수업 내용 ]

저작자표시 비영리 변경금지

'Database > MySQL' 카테고리의 다른 글

[MySQL] INNER JOIN / OUTER JOIN / CROSS JOIN / SELF JOIN (2)	2024.02.25
[MySQL] JDBC 예제 (0)	2024.02.25
[MySQL] 데이터베이스 모델링 \| 참조 무결성 (1)	2024.02.25
[MySQL] 데이터베이스 모델링 \| 1~3 정규화, 역정규화 (1)	2024.02.25
[MySQL] 데이터베이스 모델링 \| 진행단계, 분류, 엔티티, 관계 (0)	2024.02.25

[MySQL] 데이터베이스 모델링 | 참조 무결성

리버 River 2024. 2. 25. 18:25

2024. 2. 25. 18:25

1. 참조 무결성

1) 참조 관계

👩🏻‍🚀 제 1 정규화에서 예로 들었던 직원과 프로젝트 테이블을 생성하고 관계를 맺는다

➡️ 두 개의 테이블을 만들되 프로젝트에 비용 필드를 추가

📌 먼저 부모 테이블에 해당하는 직원 테이블을 생성하고 샘플 데이터를 입력

CREATE TABLE `tEmployee`
(
`name` CHAR(10) PRIMARY KEY, // 직원의 이름을 기본키로 지정
`salary` INT NOT NULL, // 월급
`addr` VARCHAR(30) NOT NULL // 주소
);
INSERT INTO `tEmployee` VALUES ('김상형', 650, '이천시');
INSERT INTO `tEmployee` VALUES ('문종민', 480, '대구시');
INSERT INTO `tEmployee` VALUES ('권성직', 625, '안동시');

📌 다음은 직원 테이블과 관계를 맺을 프로젝트 테이블을 생성

CREATE TABLE `tProject`
(
`projectID` INT PRIMARY KEY,  --기본키인 projectID는 자동 증가열로 정의하는 것이 간편
`employee` CHAR(10) NOT NULL, --담당자
`project` VARCHAR(30) NOT NULL, --프로젝트이름 
`cost` INT --비용
);
INSERT INTO `tProject` VALUES (1, '김상형', '홍콩 수출건', 800);
INSERT INTO `tProject` VALUES (2, '김상형', 'TV 광고건', 3400);
INSERT INTO `tProject` VALUES (3, '김상형', '매출분석건', 200);
INSERT INTO `tProject` VALUES (4, '문종민', '경영 혁신안 작성', 120);
INSERT INTO `tProject` VALUES (5, '문종민', '대리점 계획', 85);
INSERT INTO `tProject` VALUES (6, '권성직', '노조 협상건', 24);

👾 employee 외래키는 직원 테이블의 name키를 가리키며 이 관계를 통해 프로젝트의 담당자가 누구인지 알 수 있음

💡 두 테이블은 담당한다는 관계 = (참조)를 맺음

* 참고 *

- 테이블 이름은 표현하는 엔티티로 붙이는 것이 좋음

➡️ 도시의 정보를 다루니 tCity로 이름을 붙이고 직원 목록은 tStaff으로 붙임 프로젝트명은 tProject가 어울림.

- 레코드 여러 개를 저장한다고 해서 굳이 복수형으로 작성할 필요는 없음

➡️ tCities, tStaffs, tProjects로 해도 문제는 없지만 테이블에는 어차피 여러 개의 레코드가 들어가니 복수형일 필요 없음

2) 외래키 제약

👩🏻‍💻 직원과 프로젝트의 샘플 데이터는 규칙에 맞게 입력하여 참조 관계가 정확

📌 새로 레코드를 삽입할 때는 아직 문제가 있음

INSERT INTO `tProject` VALUES (7, '홍길동', '원자재 매입', 900);

👾 새로운 프로젝트의 담당자로 지정한 '홍길동'은 직원 테이블에 존재하지 않음

👾 외래키가 존재하지 않는 잘못된 키를 가리키고 있지만 이상 없이 잘 삽입됨

📌 다음 명령도 문제가 있음

DELETE FROM `tEmployee` WHERE name = '김상형';

👾 이 명령은 '김상형' 직원을 삭제

➡️ 이 직원이 맡고 있는 모든 프로젝트는 담당자를 잃어버림.

👾 외래키가 가리키는 대상이 사라져 참조 무결성이 깨져 버림

👾 관계를 기반으로 동작하는 RDB의 참조 무결성이 깨지면 이후의 동작을 보증할 수 없음

👩🏻‍💻 DB 엔진은 시키는대로 할 뿐 실행을 거부할 이유 x ➡️ 이런 이유로 외래키(FK) 제약이 필요

👩🏻‍💻 외래키 제약은 어떤 필드가 외래키인지, 어떤 테이블의 무슨 키를 참조하는지 지정

👩🏻‍💻 외래키 제약이 있으면 참조 무결성이 깨지는 명령의 실행을 거부

📌 직원 테이블은 원상 복구해 두고 tProject 테이블은 다시 정의

CREATE TABLE `tProject` (
`projectID` INT PRIMARY KEY,
`employee` CHAR(10) NOT NULL,
`project` VARCHAR(30) NOT NULL,
`cost` INT,
 CONSTRAINT FK_emp FOREIGN KEY(`employee`) REFERENCES `tEmployee`(name`)
 // 마지막 필드 끝에 콤마를 찍고 제일 아래에 외래키 제약을 추가
);

👾 제약의 이름과 종류를 지정하고 REFERENCES 키워드 다음에 테이블(필드) 형식으로 참조하는 테이블과 키를 지정

👾 오라클과 SQL Server는 외래키 필드 선언문에 컬럼 제약으로 지정할 수도 있음

`employee` ...CHAR(10) NOT NULL REFERENCES
`tEmployee`(name),

👾 외래키 제약은 employee 필드가 tEmployee 테이블의 name 필드와 연결되었음을 선언

➡️ 이후 DBMS는 이 제약을 위반하는 모든 명령의 실행을 거부

INSERT INTO `tProject` VALUES (7, '홍길동', '원자재 매입', 900);
// ORA-02291: 무결성 제약조건(SYSTEM.FK_EMP)이 위배되었습니다- 부모 키가 없습니다

DELETE FROM `tEmployee` WHERE `name` = '김상형';
// ORA-02292: 무결성 제약조건(SYSTEM.FK_EMP)이 위배되었습니다- 자식 레코드가 발견되었습니다

💡 둘 다 에러 메시지를 출력하고 실행을 거부

➡️ 원하는대로 하려면 다음 두 명령을 순서대로 실행

INSERT INTO `tEmployee` VALUES ('홍길동', 330, '장성');
INSERT INTO `tProject` VALUES (7, '홍길동', '원자재 매입', 900);

💡 직원을 먼저 등록해야 이 직원에게 프로젝트를 맡길 수 있음

➡️ 마찬가지로 직원을 삭제하려면 다음 두 명령을 순서대로 실행

DELETE FROM `tProject` WHERE `employee` = '김상형';
DELETE FROM `tEmployee` WHERE `name` = '김상형';

💡 직원이 맡고 있는 프로제트를 모두 제거하거나 아니면 다른 직원에게 넘겨야 이 직원을 컷할 수 있음

📌 FK 제약이 설정되어 있는 상태에서 다음 쿼리문도 에러 처리

DROP TABLE `tEmployee`;

👾 직원 테이블을 삭제하면 프로젝트 테이블 전체가 무효해짐

➡️ 정직원 테이블을 삭제하려면 프로젝트 테이블부터 비워야 함

👩🏻‍💻 DBMS가 외래키 제약에 대한 규칙을 정확하게 알고 있으므로 무결성을 해치는 모든 명령을 원천적으로 차단

👩🏻‍💻 외래키 제약은 참조 무결성을 지키는 강력한 수단

3) 연계 참조 무결성 제약

👩🏻‍🚀 외래키 제약관련 작업을 자동화하여 한 번에 처리하는 연계 참조 무결성 제약

👩🏻‍🚀 관련키의 삭제나 수정을 무조건 금지하는 것이 아니라 추가 동작까지 자동으로 처리하여 무결성을 유지하는 기능

👩🏻‍🚀 부모 테이블을 변경하면 자식 테이블까지 알아서 수정

💡 이 기능을 사용하려면 외래키 제약 뒤에 다음 선언을 추가

ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }

👾 ON DELETE는 참조되는 키가 삭제될 때의 동작을 정의하며 ON UPDATE는 참조되는 키가 변경될 때의 동작을 정의

📌 각각에 대해 4개의 옵션을 지정할 수 있음

* NO ACTION은 아무것도 하지 않고 실패하도록 내버려 두는 것이며 이 옵션이 디폴트

* CASCADE는 참조되는 키와 연결되어 있는 외래키를 자동으로 삭제하거나 변경

* SET NULL이나 SET DEFAULT는 외래키를 NULL이나 기본값으로 변경하는데 외래키가 NULL 허용이거나 기본값이 지정되어 있어야 함

📌 tProject 테이블을 삭제한 후 다음 명령으로 다시 생성

CREATE TABLE `tProject` (
`projectID` INT PRIMARY KEY,
`employee` CHAR(10) NOT NULL,
`project` VARCHAR(30) NOT NULL,
`cost` INT,

CONSTRAINT FK_emp FOREIGN KEY(`employee`) REFERENCES `tEmployee`
ON DELETE CASCADE
);

👾 ON DELETE에 대해 CASCADE 옵션을 지정하여 필요한 추가 처리를 자동으로 하도록 함

👾 이 상태에서 다음 명령으로 직원을 삭제

DELETE FROM `tEmployee` WHERE `name` = '김상형';

💡 외래키 제약만 걸려 있다면 이 명령은 당연히 에러지만 CASCADE 옵션이 지정되어 있어 에러 없이 실행 가능

📌 실행하기 전 후 차이

👾 직원 뿐만 아니라 tProject에 이 직원이 담당한 프로젝트도 같이 삭제

👾 연계 참조란 이런 식으로 하나를 삭제하면 연결된 다른 테이블을 자동으로 수정하는 기능

💡 만약 연계에 의해 삭제되는 레코드에 또 다른 연계 참조 무결성 제약이 걸려 있다면 연쇄적으로 수정

👾 ON UPDATE 선언을 하면 참조키 수정시 관련 외래키를 같이 수정하며 동작 방식은 ON DELETE와 유사

👾 SQL Server와 MariaDB는 이 기능을 지원하지만 오라클은 ON DELETE까지만 지원하며 똑같은 기능을 구현하려면 트리거를 사용해야 함

📌 tProject 테이블을 삭제한 후 다음 명령으로 다시 생성

📌 ON DELETE와 ON UPDATE에 대해 모두 CASCADE 동작을 지정

CREATE TABLE `tProject`(
`projectID` INT PRIMARY KEY,
`employee` CHAR(10) NOT NULL,
`project` VARCHAR(30) NOT NULL,
`cost` INT,
CONSTRAINT FK_emp FOREIGN KEY(`employee`) REFERENCES `tEmployee`
ON DELETE CASCADE ON UPDATE CASCADE
);

👾 직원을 삭제하면 연관 정보도 같이 삭제

📌 이번에는 직원이 이름을 변경한 상황을 가정하여 UPDATE 명령으로 참조키를 수정

 `tEmployee` SET `name` = '문사장' WHERE `name` = '문종민';

👾 이름이 바뀌는 것은 원래의 이름이 없어지는 것과 같아 프로젝트의 담당자가 무효해짐

👾 외래키 제약만 걸려 있다면 담당자의 이름만 바꾸는 것은 불가능

👾 연계 참조 무결성 제약이 걸려 있으면 참조되는 키를 수정할 수 있음

➡️ 직원 이름을 수정하면 tProject의 외래키도 같이 변경

➡️ 담당자의 이름과 프로젝트의 담당자가 동시에 바뀌므로 무결성은 여전히 유지

💡 자동으로 처리한다는 면에서 편리하지만 이 기능은 함부로 사용 x, 실무에서도 잘 사용하지 않음

[ 내용 참조 : IT 학원 강의 ]

저작자표시 비영리 변경금지

'Database > MySQL' 카테고리의 다른 글

[MySQL] JDBC 예제 (0)	2024.02.25
[MySQL] JDBC 정의, 로딩, 프로그램 개발 절차 (0)	2024.02.25
[MySQL] 데이터베이스 모델링 \| 1~3 정규화, 역정규화 (1)	2024.02.25
[MySQL] 데이터베이스 모델링 \| 진행단계, 분류, 엔티티, 관계 (0)	2024.02.25
[MySQL] 제약조건 \| 일련번호, 시퀀스, AUTO_INCREMENT (0)	2024.02.24

[MySQL] 데이터베이스 모델링 | 1~3 정규화, 역정규화

리버 River 2024. 2. 25. 16:46

2024. 2. 25. 16:46

1. 정규화

🌙 정규화는 테이블을 재구성하여 논리적 구조를 개선하는 절차

🌙 불필요하게 중복되는 데이터를 제거하여 저장 공간을 절약하고 전체적인 성능을 향상시킴

🌙 실무에서는 주로 1~ 3 정규화까지 사용

1) 제 1 정규화

👾 제 1 정규화는 필드의 데이터를 원자화(Atomicity)함

👾 필드에는 단일값을 저장하고 필드끼리는 독립적이어야 함

📌 다음 테이블은 직원의 신상과 맡은 프로젝트를 저장

이름	월급	주소	프로젝트
김상형	650	이천시	홍콩 수출건, TV광고건, 매출 분석건
문종민	480	대구시	경영 혁신안 작성, 대리점 계획
권성직	625	안동시	노조 협상건

🔎 월급, 주소 필드에 단일값을 저장, 상호 아무런 연관이 없기 때문에 두필드는 원자성을 확보

🔎 프로젝트는 한 직원이 여러 건을 맡을 수 있어 콤마로 구분하여 나열

➡️ 저장의 목적은 달성했지만 원자성이 없어 문제가 발생

* 프로젝트 개수에 제한이 없어 필드 길이를 충분히 할당 해야 함

신입사원은 당장 맡은 프로젝트가 없지만 앞으로 맡을 프로젝트를 위해 최대 길이만큼 미리 확보해야 하니 저장 공간을 낭비

* 단일값이 아니어서 = 연산자로 비교할 수 없으며 WHERE project LIKE '%홍콩수출건%' 식으로 부분 문자열을 검색해야 함

* 수정도 문제가 됨

🔎 김상형 직원이 담당하는 TV 광고건을 문종민 직원에게 이관하려면 중간에 낀 문자열을 빼 뒤쪽에 추가하는 문자열 조작을 해야 함

➡️ 구분자인 콤마까지 정확히 추출해야 함, UPDATE, DELETE 쿼리로는 이런 조작이 불가능

➡️ 필드값은 더 이상 나눌 수 없는 단일값(Scalar)이어야 관리하기 편함

⬇️

💡 프로젝트 필드를 여러 개 선언하고 각 필드에 프로젝트 하나를 저장하면 어떨까?

이름	월급	주소	프로젝트1	프로젝트2	프로젝트3
김상형	650	이천시	홍콩 수출건	TV광고건	매출 분석건
문종민	480	대구시	경영 혁신안 작성	대리점 계획	NULL
권성직	625	안동시	노조 협상건	NULL	NULL

🔎 이 구조는 원자성은 확보했지만 직원 한명당 프로젝트를 3개까지만 담당해야 한다는 제한이 생김

🔎 핵심은 1) 낭비없이 얼마든지 2) 많은 프로젝트를 저장할 수 있어야 함

➡️ 이런 경우 정석이 바로 제 1 정규화이며 다음과 같이 두 개의 테이블로 분할

① 직원 테이블에는 이름, 월급, 주소 등의 직원 정보만 두고 프로젝트 필드는 제거

② 프로젝트 테이블을 따로 만들어 누가 어떤 프로젝트를 담당하는지 기록 (1:다 관계)

🔎 양쪽 테이블은 각자의 기본키가 있음. 직원 테이블은 이름이 PK이며 프로젝트 테이블은 일련번호를 PK로 정의

🔎 프로젝트는 PK 외에 담당자를 가리키는 별도의 필드가 더있음

💡 이처럼 외부 테이블에 대한 참조를 가지는 키를 외래키(Foreign Key)라고 부르며 양 테이블의 연결고리 역할을함

➡️ 직원과 프로젝트는 담당자 외래키로 연결되며 두 테이블 간에 "담당한다"는 관계가 성립

➡️ 담당자 외래키가 없다면 직원과 프로젝트는 완전히 따로 놀며 누가 어떤 프로젝트를 맡고 있는지 알 방법이 없음

👾 레코드는 얼마든지 자유롭게 늘릴 수 있어 담당 프로젝트 개수에 제한 x

👾 신규 프로젝트는 새 레코드를 삽입(INSERT)하고, 완료되었으면 삭제(DELETE)하고, 수정(UPDATE)하면 됨

📌 김상형 직원이 담당하는 TV 광고건을 문종민 직원에게 이관건은 다음 쿼리 하나로 해결

UPDATE tProject SET 담당자='문종민' WHERE 프로젝트='TV 광고건';

📌 특정 직원이 맡은 프로젝트를 조사하려면 외래키를 직원명으로 검색

SELECT 프로젝트명 FROM tProject WHERE 담당자= '김상형';

🌙 관계를 맺는 테이블은 누가 업무의 중심인가에 따라 부모, 자식 관계를 형성

📌 업무의 주체가 되는 쪽을 부모 또는 마스터(Master)라고 하며 반대쪽을 자식 또는 디테일(Detail)이라고 함.

📌 위의 예시 경우 직원이 프로젝트를 수행하므로 직원 테이블이 마스터이고 프로젝트 테이블이 디테일

🌙 정규화에 의해 테이블을 분할하면 양쪽의 정보를 조합하여 얻기는 조금 번거로워짐

➡️ 하지만 분할된 테이블에서 관련 정보를 검색하는 서브쿼리(SubQuery)나 조인(Join) 등의 방법으로 해결 가능

2) 제 2 정규화

🌙 제 2 정규화는 일반 필드를 모두 기본키에 종속시키는 것

➡️ 종속된다는 것은 기본키를 알면 나머지 정보도 알 수 있다는 뜻

📌 tCity 테이블에서 기본키인 도시명과 나머지 일반 필드는 종속 관계

📌 일반 필드끼리는 종속 관계가 없으며 상호 독립적. 독립적이라는 것은 각자 따로 논다는 뜻.

➡️ 경기도에 있다고 해서 광역시는 아니며 면적을 안다고 해서 인구를 알 수는 없음

📌 tCity는 모든 필드가 기본키에 종속적이므로 제 2 정규화를 만족

💡 만약 프로젝트 테이블을 다음과 같이 만들었다면?

프로젝트 번호	프로젝트	담당자	주소	월급
1	홍콩 수출건	김상형	이천시	650
2	TV 광고건	김상형	이천시	650
3	매출 분석건	김상형	이천시	650
4	경영혁신안	문종민	대구시	480
5	대리점 계획	문종민	대구시	480
6	노조 협상건	권성직	안동시	625

🔎 프로젝트 번호에 따라 프로젝트 이름과 담당자가 결정되므로 이들은 종속 관계

🔎 담당자의 주소나 월급은 프로젝트와는 직접적인 상관이 없어 이 테이블에 있을 필요가 없음

➡️ 프로젝트 테이블에 담당자만 기록해 두면 나머지 정보는 직원 테이블에서 언제든지 조사할 수 있음

📌 단일 기본키에서는 이런 실수가 금방 눈에 띄지만 복합키에서는 헷갈리는 상황 발생

📌 모든 필드는 복합키 전체에 종속되어야 하며 일부에만 종속되서는 안됨

💡 다음 테이블은 주문 처리 내역을 기록하는 주문 테이블

고객명	주문번호	주문 일자	품목	개수	주소	회원등급
보라돌이	1	5월 5일	하드디스크	1	춘천	우수
보라돌이	2	5월 6일	노트북	2	춘천	우수
뚜비	1	5월 7일	팥빙수	1	이천	신입
뚜비	2	5월 8일	카네이션	2	이천	신입
뚜비	3	5월 15일	수박	5	이천	신입
보라돌이	3	5월 28일	분유	1	춘천	우수

📌 고객 한명이 여러 건의 주문을 낼 수 있어 고객명과 주문번호를 복합키로 설정하여 어떤 고객이 몇 번째 낸 주문이라는 식으로 구분

📌 주문일자나 품목, 개수는 주문 내역인 고객명+주문번호에 종속적

➡️ 누가 몇 번째 낸 주문인가에 따라 날짜, 품목, 개수가 결정됨

📌 주소와 회원등급은 복합키가 아닌 고객명에 종속되어 있으며 누가 주문을 냈는가에 따라 결정

➡️ 주문번호가 다르다고 해서 주소나 등급이 바뀌는 것은 아님

👾 이 구조에서 주소, 회원등급 필드는 테이블의 기억 장소를 불필요하게 낭비

👾 기본키 전체에 종속되지 않는 필드는 별도의 테이블로 분리하여 제 2 정규화를 함

[ 주문 테이블 ]

고객명	주문번호	주문 일자	품목	개수
보라돌이	1	5월 5일	하드디스크	1
보라돌이	2	5월 6일	노트북	2
뚜비	1	5월 7일	팥빙수	1
뚜비	2	5월 8일	카네이션	2
뚜비	3	5월 15일	수박	5
보라돌이	3	5월 28일	분유	1

[ 고객 테이블 ]

고객명	주소	회원등급
보라돌이	춘천	우수
보라돌이	춘천	우수
뚜비	이천	신입
뚜비	이천	신입
뚜비	이천	신입
보라돌이	춘천	우수

👾 주문 내역 테이블에는 복합키에 종속적인 필드만 포함했으며 고객 정보는 별도의 테이블을 구성

👾 한 고객이 수천 번의 주문을 낸다 하더라도 주소, 회원등급 정보는 딱 하나씩만 있으면 됨

3) 제 3 정규화

🌙 제 3 정규화는 기본키가 아닌 일반 필드끼리 서로 종속되지 않도록 하는 것

📌 일반 필드끼리는 독립적이어야 한다는 규칙

💡 다음은 직원 테이블에 직원이 가입한 사내 동호회 관련 필드를 추가한 것

직원명	월급	동호회	방번호	회원수
이인재	700	독서	203	32
김영석	680	독서	203	32
김연경	430	게임	306	18
조기봉	690	독서	203	32
차일환	320	게임	306	18

📌 월급과 가입한 동호회 이름은 기본키인 직원명에 종속적

📌 동호회의 방번호와 회원수는 기본키가 아닌 동호회 필드에 종속적

📌 같은 동호회에 가입한 직원끼리는 방번호, 회원수 등의 중복 정보를 가짐

💡 이 구조의 문제점

① 기억 장소가 낭비

· 만약 동호회 회장 이름, 설립년도, 예산 등의 상세 정보가 추가되고 동호회별로 회원수가 수십명이라면 낭비가 더 심해짐

② 이 테이블의 엔티티는 직원이지 동호회가 아니다.

· 미가입 직원의 동호회 관련 정보는 모두 NULL일 수 밖에 없음

· 회원이 없는 신규 동호회는 표현할 방법이 없음

③ 중복 정보를 변경하기 번거로움

· 예를 들어 독서 동호회가 203호에서 505호로 이사갔다면 이 동호회에 속한 모든 회원의 정보를 다 수정해야 함

④ 정보의 불일치가 발생할 수 있음

· 같은 독서 동호회인데도 이인재 직원의 총 회원수는 32로 기록하고 김영석 직원의 총 회원수는 31로 기록할 수도있음

💡 이런 문제가 발생하는 이유는 분석 단계부터 엔티티 추출에 문제가 있었기 때문

➡️ 문제를 해결하려면 정규화를 통해 테이블을 분할.

📌 월급, 동호회는 기본키인 사원명에 종속적

📌 반면 일반 필드인 월급과 동호회는 아무 상관이 없으며 독립적 따라서 이 둘의 관계는 정규화를 위반 x

📌 동호회와 방번호, 회원수의 관계는 종속적. 동호회를 알면 방번호나 회원수도 알 수 있음

➡️ 일반 필드끼리는 독립적이어야 하는데 그렇지 않아 중복이 발생

➡️ 이럴 경우 직원 테이블과 동호회 테이블을 따로 분리하여 중복을 제거

[직원 테이블]

직원명	월급	동호회
이인재	700	독서
김영석	680	독서
김연경	430	게임
조기봉	690	독서
차일환	320	게임

[동호회 테이블]

동호회	방번호	회원수
독서	203	32
독서	203	32
게임	306	18
독서	203	32
게임	306	18

👾 직원 테이블에는 소속 동호회의 이름만 기록하고 각 동호회에 대한 정보는 별도의 테이블로 분리

👾 직원 테이블의 동호회 필드는 동호회 테이블을 참조하는 외래키

👾 직원 테이블의 월급과 동호회는 기본키인 직원명에 종속적

👾 새로 분할한 동호회 테이블에서 방번호와 회원수는 기본키인 동호회에 종속적

💡 둘 다 3 정규화가 완료 ➡️ 중복으로 인한 모든 문제가 해결

💡 동호회 정보가 딱 하나여서 기억 장소의 낭비가 없고 데이터의 불일치가 발생할 여지 x

💡 가입 회원이 없는 신규 동호회도 표현가능

2. 역정규화

👩🏻‍💻 정규화 과정을 거치면 중복이 제거되고 구조가 치밀해짐

👩🏻‍💻 양쪽 정보를 조합하여 읽을 때는 여러 테이블을 읽어야 하므로 속도는 느려짐

👩🏻‍💻 프로그래밍의 대원칙 중 하나는 속도와 크기는 항상 반비례

👩🏻‍💻 SQL은 조인(Join)이라는 효율적인 기능을 제공하여 속도 감소가 심하지 않음

💡 정규화를 너무 과하게 하여 이중 삼중의 조인이 필요하면 성능 저하가 발생하는데, 이럴 때는 의도적으로 데이터를 중복시켜 속도 향상을 꾀하기도 한다. 원칙을 반대로 적용하여 속도 향상을 위해 용량상의 이득을 포기하는 것

➡️ 이를 정규화의 반대 개념인 역정규화라고 하는데 구현 단계의 튜닝 과정에서 주로 수행

ex. 제 2 정규화에서 예를 든 주문 정보 테이블에서 배송 주소를 구하려면 주문자를 먼저 찾고 고객 테이블에서 주소를 조사하는 번거로운 과정을 거쳐야 함. 이럴 때 주소 필드를 주문 내역 테이블에 다시 포함시키면 곧바로 배송지 주소를 구할 수 있음

[ 내용 참고 : IT 학원 강의 ]

저작자표시 비영리 변경금지

'Database > MySQL' 카테고리의 다른 글

[MySQL] JDBC 정의, 로딩, 프로그램 개발 절차 (0)	2024.02.25
[MySQL] 데이터베이스 모델링 \| 참조 무결성 (1)	2024.02.25
[MySQL] 데이터베이스 모델링 \| 진행단계, 분류, 엔티티, 관계 (0)	2024.02.25
[MySQL] 제약조건 \| 일련번호, 시퀀스, AUTO_INCREMENT (0)	2024.02.24
[MySQL] 제약조건 \| 식별자, 기본키, 복합키, 유니크, 체크 (1)	2024.02.24

ID_ allriver

Database

MySQL 새 계정 만들기 (Mac ver) - terminal VS. MySQL 워크벤치

MySQL Workbench에서 새 계정 만드는 방법

'Database > MySQL' 카테고리의 다른 글

[MySQL] INNER JOIN / OUTER JOIN / CROSS JOIN / SELF JOIN

1. 조인 Join

'Database > MySQL' 카테고리의 다른 글

[MySQL] JDBC 예제

1. DB 연결

2. INSERT

3. UPDATE

4. DELETE

5. SELECT

'Database > MySQL' 카테고리의 다른 글

[MySQL] JDBC 정의, 로딩, 프로그램 개발 절차

1. JDBC ; Java DataBase Connectivity

'Database > MySQL' 카테고리의 다른 글

[MySQL] 데이터베이스 모델링 | 참조 무결성

1. 참조 무결성

'Database > MySQL' 카테고리의 다른 글

[MySQL] 데이터베이스 모델링 | 1~3 정규화, 역정규화

1. 정규화

⬇️

2. 역정규화

'Database > MySQL' 카테고리의 다른 글

+ Recent posts

티스토리툴바