SQL 15일차[물리 모델링][반정규화]
https://livesql.oracle.com/apex/f?p=590:1000
view scripts and tutorials 들어가면 테이블 샘플 다 만들어둠 , 참고하면 좋다
파티셔닝은 여기서 진행한다. 우리가 다운받은 dB는 express 버전으로 파티션기능을 사용할 수 없기때문에
슈퍼타입과 서브타입을 반정규화하는방법
2. 수직분할과 수평분할
수평분할
수평분할은 자주 참조되는 튜플에 대해서 파티션을 나눠서 관리한다. 이렇게 되면 필요한 튜플을 빠르게 조회할 수있다.
테이블 분할관점
데이터 모델링 수행을 수행하고 DB용량에 따른 대량의 데이터처리 패턴을 분석하여 트랜잭션이 컬럼단위로 집중조회하면 수직분할이고 로우 단위로 집중하면 수평분할한다.
테이블 수평 분할
테이블이 대량의 데이터를 가질 것으로 파악되는 경우 partitioning한다.
1.범위로 분할 : 숫자 , 날짜, 문자
--숫자로 나누기
create table sales(
sales_no number,
sales_year number,
customer_name varchar2(20),
price number
)
PARTITION BY range (sales_no)
(
PARTITION SALES_P1 VALUES LESS THAN (3),
PARTITION SALES_P2 VALUES LESS THAN (5),
PARTITION SALES_P3 VALUES LESS THAN (MAXVALUE)
);3이하 애들은 여기, 3-5애들은 여기 , 최대치 아래들은 여기 이렇게 나눔
select * from sales PARTITION (SALES_P1);
여기 파티션애들 나와! -> 전체조회보다는 성능 개좋음 --> 대용량 데이터일때
수평분할 은 파티셔닝을 하는 거라고 생각하면 된다.
2. 값으로 분할 (서울, 대구 , 부산 등) -> list
3. 해쉬함수 분할(Hash)
반정규화는 정규화를 한걸 다시 테이블에 합치는걸 이야기 하는 거지 정규화 하지않고 반정규화라고 우기는 건 아니다
새로운 프로젝트를 생성
1) oracle - 계정을 하나 더만듬
2) ms, mysql은 dB를 하나 더만듬
--반정규화
--수평분할
--테이블 분할 관점
--1. 데이터 모델링 수행 -> 데이터베이스 용량 산정 -> 대량 데이터 처리(트랜잭션) 패턴 분석
-- -> 트랜잭션이 컬럼 단위 집중 (수직 분할) ..... 로우 단위 집중 (수평분할)
--테이블 수평 분할
--데이블이 대량의 데이터를 가질 것 파악(예상) 경우 : partitioning
--1. 범위로 분할 (고객 1~10000 , 10001~20000 , 20001~) >> Range
--범위 (range) : [숫자] , [날짜] , [문자]
--파티션 나누는 키가 숫자 타입
create table sales(
sales_no number,
sales_year number,
customer_name varchar2(20),
price number
)
PARTITION BY range (sales_no)
(
PARTITION SALES_P1 VALUES LESS THAN (3),
PARTITION SALES_P2 VALUES LESS THAN (5),
PARTITION SALES_P3 VALUES LESS THAN (MAXVALUE)
);
insert into sales values(1, 2005 , '길동', 5000);
insert into sales values(2, 2005 , '길동', 5000);
insert into sales values(3, 2005 , '길동', 5000);
insert into sales values(4, 2005 , '길동', 5000);
insert into sales values(5, 2005 , '길동', 5000);
select * from sales; --전체 조회
select * from sales PARTITION (SALES_P1);
1, 2005 , '길동', 5000
2, 2005 , '길동', 5000
--2. 값으로 분할 (서울 , 대구 , 부산 등) >> List
--3. 해쉬함수 분할 (Hash)
--실습은 https://livesql.oracle.com/테이블 반정규화
이력테이블추가하기
컬럼의 값들 중 수정전의 이전 데이터들이 중요한 애들이 있다. 그런 애들은 컬럼을 따로 빼서 이력테이블로 관리한다.
이력데이터를 관리하는 테이블을 만들게되면 원래의 테이블에서는 가장 최근의 데이터를 조회해야 할 때가 늘어나는데 그럴때마다 join하고 order by 해서 테이블의 가장 나중에 저장된 데이터를 불러와야한다. order by와 join은 조회 성능을 떨어뜨리는 연산이다.
이럴때 이력테이블에서 추출한 최신데이터를 원래테이블의 컬럼으로 관리한다. 이렇게 되면 한 테이블안에 다 있으므로 조회가 빨라진다. 단점은 관리가 힘들다는 점이다. 이력테이블에 새로운 튜플이 추가되면 최신데이터 또한 변경해야하기 때문이다. 그래서 업무관리를 위해 필요할 경우에만 컬럼으로 관리하는 편이 좋다.
부분테이블 추가
차량의 사진정보를 많이 검색한다면 사진정보 컬럼만 따로 빼서 관리하는게 낫다.
관계 반정규화
아래와 같이 쇼핑몰 홈페이지를 운영하기 위한 테이블을 만들었는데 배송테이블에 고객이름이 필요한 상황이 왔다. 그럴때 문제점은 고객이름을 얻기위해서 배송, 주문상세,주문, 고객 4개의 테이블을 조인해야한다. 너무 많은 조인은 성능을 낮추기 때문에 반정규화가 필요하다.
조인의 횟수를 줄이기 위해서는 반정규화를 사용해서 고객테이블의 고객번호를 배송 테이블에서 참조할수 있도록 관계를 설정해주면 조인을 1번만하고도 주문한 고객의 이름을 얻어 올수있다. 이는 보통 상식적인 선에서 해결할 수있는 반정규화로 반정규화인지 모르고 쓰는 경우가 많다.
USER의 할당량을 설정하면 테이블 스페이스에 할당된 DB용량을 전부 사용한다.
exerd 홈페이지 가면 메뉴얼 다운 가능한데 그거 해보
물리모델링
물리모델링은 이제 드디어 논리 모델링에서 만들어봤던 테이블 그림들을 데이터베이스에 직접 넣을 차례이다. 데이터 베이스에 넣을 때는 eXERD 툴을 이용해서 create table 과 관계조건을 삽입하는 alter문을 자동을 생성하게 한다.
물리모드 설정
동시편집
테이블 상세 설정
트리거는 우리가 배운 문법으로 하드코딩 하자 ui가 더 어려움
DB로 옮기기
코드를 그림으로
그림을 코드로
스키마 시퀀스, 펑션 프로시저 트리거 뷰는 다 스크립트가 더 빠름
인덱스 자동으로 만들기는 하자 그리고 테이블 생성
테이블을 수정하고 싶어서 eXERD에서 컬럼을 추가했다면 컬럼에 대한 정보를 데이터베이스에 직접 업데이트해주지는 않는다.그래서 컬럼을 추가하면 기존테이블을 drop하고 다시 만든다. 이를 방지하기 위해서 drop table 에 대한 sql문을 추가하지 않기 위해서 테이블 삭제 버튼을 해제해준다.
내가 생성할 테이블의 물리명(dB에서 select from절에 들어갈 테이블명), 논리명(테이블의 설명을 위해서 논리 모델링에 직접 적어준 테이블명)이다.
실행될 코드들 명세 단계
alter를 나중에 함 - > 이 sql문을 공유하려면 ddl저장하면됨
자바설정
ojdbc6.jar가 존재하는 폴더에서 선택해준다.
호스트에는 ip or localhost
sid - > db 이름 xe -> orc는 유료
만약 db에 만들고자 하는 테이블이 생성되어 있다면 삭제하고 다시만들도록하기 위해서 테이블 삭제를 체크한다. 그 삭제 체크박스는 이 경우에만 사용한다.
시퀀스, 함수, 뷰, 트리거, 프로시져는 developer에서 하드코딩해주는 게 더편리하다.
desc emp;
desc dept;
select * from user_constraints where table_name='EMP';
select * from user_constrains where table_name='DEPT';
/*
1. 시퀀스
2. 함수
3. 뷰
4. 트리거
5. 프로시져 생성
*/
테이블정의서 자동생성
테이블을 생성하면 이에 대한 문서작업을 해야하는데 eXERD에서는 자동으로 버튼만 누르면 해준다.
색깔같은거 설정
생성완료
web MVC
root : 주소
directory : 웹컨텐츠 위치
JDBC
create table memo(
id varchar2(20) not null,
email varchar2(20) not null,
content varchar2(100)
);
desc memo;
select * from memo;
프론트 컨트롤러
요청과 처리 :servlet
데이터는 DTO,DAO가 관리함
나누면 일 분담 개쉬움
serbvlet은 서비스만 알면되지 dAO는 알필요 없다 서로의 관심사를 알필요 없다.
패키지명은 소문자로 한다.
1. Model2 기반의 MVC 패턴 적용 웹 프로젝트
Model1 >> 요청을 JSP 받아서 처리
Model2 >> 요청을 servlet 받아서 처리
+ MVC
Model : JAVA 클래스 > DTO , DAO(JDBC)
View : html or jsp(EL & JSTL)
Controller : servlet(웹용 자바파일)
---------------------------------------------------
JSP 없던 시절 >> Servlet 모든 것 >> UI 담당
DB 작업
create table memo(
id varchar2(20) not null,
email varchar2(20) not null,
content varchar2(100)
);
desc memo;
1. 드라이버 :ojdbc6.jar
--------------------------------------------------------------
1.DB생성(테이블생성)
2. 프로젝트 생성(구조 정의 -> 폴더)
2.1 package 구조 정의 (MVC)
kr.or.kosa.dto
kr.or.kosa.dao
kr.or.kosa.service
kr.or.kosa.utils
kr.or.kosa.controller
-----------------------------------------------------------------
View 단
보안을 위해서 webapp에 views 폴더를만든다
>>webapp>WEB-INF>views 폴더 > 업무용 > admin > admin.jsp
> board > board.jsp , boardwrite.jsp
>>WEB-INF 보안폴더 >> 사용자가 주소를 알고 이곳에 접근해도 404에러가 발생한다.
-----------------------------------------------------------------
현업 DB작업
connection pool (히카리)
지금은 연습이니까 pool안 씀
------------------------------------------------------------------
서블릿 (servlet)
게시판 관련 servlet >> FrontBoardServlet
관리자에 관련된 servlet >> FrontAdminServlet
현재 실습
요청당 servlet 1개 씩만 생성하자
lib 폴더에 ojdbc6.jar파일추가해주기
memoInsert.java -->insert할때 필요한 요청을처리할 servlet파일
특징: doprocess라는 함수를 doget과 dopost모두에서 불러서 get방식과 post방식일 떄에 처리해야할 코드들을 똑같이 넣어준다. 여기에 접근하려면 주소가 프로젝트명/memoInsert여야한다.
package kr.or.controller;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import kr.or.kosa.dao.MemoDao;
import kr.or.kosa.dto.Memo;
@WebServlet("/memoInsert")
public class memoInsert extends HttpServlet {
private static final long serialVersionUID = 1L;
public memoInsert() {
super();
// TODO Auto-generated constructor stub
}
private void doProcess(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
/*
1. 한글처리
2. 데이터받기
3. DB작업
4. insert
5. 응답구성
* */
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=UTF-8");//내가 클라이언트에 보내는 파일은 html파일이고 인코딩은 utf-8이다.
PrintWriter out = response.getWriter();
String id=request.getParameter("id");
String email=request.getParameter("email");
String content=request.getParameter("content");
try {//보통 insert하다가 터지기 때문에
MemoDao dao = new MemoDao();
int row = dao.insertMemo(new Memo(id, email, content));
if(row>0) {
out.print("<script>");
out.print("alert('등록성공');");
out.print("location.href='MemoList'");//memoList로 요청함
out.print("</script>");
}
//else로 잡지 않고 catch로 잡는다.
}catch(Exception e) {
out.print("<script>");
out.print("alert('등록실패');");
out.print("location.href='memo.html';");//다시 재입력받도록함
out.print("</script>");
}
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doProcess(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doProcess(request, response);
}
}
memberList.java
DB의 저장된 멤버를 전체 출력할 수있는 servlet이다. 이 역시 do process를 사용해 주었고 RequestDispatcher를 통해서 DAO에서 받아온 memberList 정보를 출력할 jsp파일로 forward함수로 연결시켜준다.
package kr.or.controller;
import java.io.IOException;
import java.util.List;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import kr.or.kosa.dao.MemoDao;
import kr.or.kosa.dto.Memo;
/**
* Servlet implementation class MemoList
*/
@WebServlet("/MemoList")
public class MemoList extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public MemoList() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
private void doProcess(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//gET, POST 함수 로직 처리
//http://localhost:8090/WebServlet_Mvc_Memo/MemoList 요청이 들어오면 이게 실행
//함수 실행
//목록보기
MemoDao dao = new MemoDao();
try {
List<Memo> memoList = dao.getMemoList();
//데이터 저장
request.setAttribute("memoList", memoList);
//view 지정하기
RequestDispatcher dis = request.getRequestDispatcher("/WEB-INF/views/memolist.jsp");
//view 전달(request,response 객체)
dis.forward(request, response);
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doProcess(request,response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doProcess(request,response);
}
}memberDAO.java
DB를 조작하는 DML에 관련된 sQL문들을 jdbc로 연결된 데이터베이스에서 실행시켜서 결과를 가져오는 함수들을 모아 놓은 자바 파일이다. 여기 있는 함수들을 servlet에서 호출해서 클라이언트의 요청을 처리할 수있도록한다.
package kr.or.kosa.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import kr.or.kosa.dto.Memo;
import kr.or.kosa.utils.ConnectionHelper;
import kr.or.kosa.utils.SingletonConnectionHelper;
public class MemoDao {
Connection conn = null;
public MemoDao() {
try {
conn = SingletonConnectionHelper.getConnection("oracle");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//전체조회(select id,email,content from memo)
public List<Memo> getMemoList(){
List<Memo> memoList = new ArrayList<>();
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "select id, email,content from memo";
try {
pstmt=conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()) {
memoList.add(new Memo(rs.getString("id"),rs.getString("email"),rs.getString("content")));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
ConnectionHelper.close(pstmt);
ConnectionHelper.close(rs);
}
return memoList;
}
//조건조회(select id, email,content from memo where id=?)
public Memo getMemoListById(String id) {
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "select id, email,content from memo where id=?";
Memo memo = null;
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, id);
rs = pstmt.executeQuery();
if(rs.next()) {
memo = new Memo(rs.getString("id"),rs.getString("email"),rs.getString("content"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
ConnectionHelper.close(pstmt);
ConnectionHelper.close(rs);
}
return memo;
}
//삽입(insert into memo(id,email,content) values(?,?,?))
public int insertMemo(Memo memo) {
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "insert into memo(id,email,content) values(?,?,?)";
int result = 0;
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,memo.getId());
pstmt.setString(2,memo.getEmail());
pstmt.setString(3,memo.getContent());
result = pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
ConnectionHelper.close(pstmt);
ConnectionHelper.close(rs);
}
return result;
}
//삭제(delete from memo where id=?)
public int deleteMemo(String id) {
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "delete from memo where id=?";
int result = 0;
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, id);
result = pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
ConnectionHelper.close(pstmt);
ConnectionHelper.close(rs);
}
return result;
}
//수정(update memo set email=? , content=? where id=?)
public int updateMemo(Memo memo) {
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "update memo set email=? , content=? where id=?";
int result = 0;
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, memo.getEmail());
pstmt.setString(2, memo.getContent());
pstmt.setString(3, memo.getId());
result = pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
ConnectionHelper.close(pstmt);
ConnectionHelper.close(rs);
}
return result;
}
}memo.java
데이터베이스에 생성되어있는 테이블의 컬럼들을 받아올 변수들을 선언해주고 DAO에서 한튜플을 구별할 자바파일이다.
package kr.or.kosa.dto;
/*
create table memo(
id varchar2(20) not null,
email varchar2(20) not null,
content varchar2(100)
);
조인쿼리 테이블 2게 1:1 매핑 >> DTO생성
조인한거도 DTO에 생성하세요 이내용을 다담을수 있는애
* */
public class Memo {
private String id;
private String email;
private String content;
public Memo() {}
public Memo(String id, String email, String content) {
super();
this.id = id;
this.email = email;
this.content = content;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
@Override
public String toString() {
return "Memo [id=" + id + ", email=" + email + ", content=" + content + "]";
}
}memo.html
사용자의 가입정보를 받아 serlvet으로 전달해주는 html 폼 파일
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<div style="margin-top:70px;"></div>
<!-- html 파일 action="MemoServlet"
JSP 경로 /Logindo *****************************
<form action="${pageContext.request.contextPath}/Login.do"
-->
<form name="f" action="memoInsert" method="get">
<div align="center">
<table width="600" border="0" cellpadding="7" >
<tr align="center" bgcolor="gold" height="50">
<td colspan="2">
<font size="4" color="#0033CC" face="굴림체">
<b>
Servlet Memo
</b>
</font>
</td>
</tr>
<tr>
<td width="25%" align="center" >
<b>ID</b>
</td>
<td>
<input type="text" size="40" name="id" maxlength="10" >
</td>
</tr>
<tr>
<td width="25%" align="center">
<b>EMAIL</b>
</td>
<td>
<input type="text" size="40" name="email" maxlength="60" >
</td>
</tr>
<tr>
<td width="25%" align="center">
<b>MEMO</b>
</td>
<td>
<textarea name="content" cols="50" class="box2"></textarea>
</td>
</tr>
<tr bgcolor="gold">
<td colspan="2" align="center" class="c2">
<input type="submit" value="전송" >
<input type="reset" value="취소">
</td>
</tr>
</table>
</div>
</form>
<hr>
<a href="MemoList">목록보기</a>
</body>
</html>memolist.jsp
회원전체를 볼수 있는 jsp파일 servlet에서 받아온 회원전체 목록을 출력해준다.
EL& JSTL을 사용했는데 이를사용하려면 jar파일을 lib에 추가해주어야한다.
<%@page import="kr.or.kosa.dto.Memo"%>
<%@page import="java.util.List"%>
<%@page import="java.util.ArrayList"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style>
table {
font-family: arial, sans-serif;
border-collapse: collapse; /* 붕괴하다 , 무너지다 */
width: 100%;
}
th {
border: 1px solid #dddddd;
text-align: center;
padding: 8px;
}
td{
border: 1px solid #dddddd;
text-align: left;
padding: 8px;
}
tr:nth-child(even) { /* even 짝수 odd 홀수 */
background-color: #dddddd;
}
</style>
</head>
<body>
<div align=center>
<hr color=green width=400>
<h2> Line Memo List </h2>
<hr color=green width=400>
<table>
<tr>
<th>Writer</th>
<th>MemoContent</th>
<th>Email</th>
</tr>
<!-- EL & JSTL 데이터 출력하세요 -->
<%=request.getAttribute("memoList") %>
<%
/* List<Memo> memoList = request.getAttribute("memoList");
for(Memo memo : memoList){
}*/
%>
<!-- for(Memo memo :ArrayList) -->
<c:forEach var="memo" items="${requestScope.memoList}">
<tr>
<td>${memo.id }</td>
<td>${memo.email }</td>
<td>${memo.content }</td>
</tr>
</c:forEach>
</table>
</div>
<a href='memo.html'>글쓰기</a>
</body>
</html>