DB 구성

Notice

Recent Posts

Recent Comments

Link

« 2024/12 »
일	월	화	수	목	금	토
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31

Tags more

Archives

Today

Total

관리 메뉴

kjh00n의 기록저장소

DB 구성 본문

세미프로젝트

DB 구성

kjh00n 2024. 12. 3. 20:06

물류팀 DB (warehouse_db)

물류팀 DB (warehouse_db)
● TABLE = [재고 목록, 실제 판매 목록, 상품 입고, 구매 상태]
● TABLE = [inventory, sales, recipts, purchase]
● 부장 = 물류DB의 모든 권한
● 과장 = 물류DB의 모든 권한
● 대리 = 재고목록, 상품입고, 구매상태 Table 생성, 수정, 삭제, 조회만 가능 실제 판매 목록 테이블 조회, 수정
● 신입 = 상품 입고, 구매 상태 Table만 조회 가능

-- 부장 --
CREATE USER supervisor@'180.180.180.2' IDENTIFIED BY '12345';
CREATE USER supervisor@'181.181.181.2' IDENTIFIED BY '12345';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, GRANT OPTION, REVOKE ON warehouse_db.* TO supervisor@'180.180.180.2';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, GRANT OPTION, REVOKE ON warehouse_db.* TO supervisor@'181.181.181.2';

-- 대리 --
CREATE USER officer@'180.180.180.2' IDENTIFIED BY '12345';
CREATE USER officer@'181.181.181.2' IDENTIFIED BY '12345';

GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON warehouse_db.inventory TO officer@'180.180.180.2';
GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON warehouse_db.inventory TO officer@'181.181.181.2';
GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON warehouse_db.recipts TO officer@'180.180.180.2';
GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON warehouse_db.recipts TO officer@'181.181.181.2';
GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON warehouse_db.purchase TO officer@'180.180.180.2';
GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON warehouse_db.purchase TO officer@'181.181.181.2';
GRANT SELECT, INSERT ON warehouse_db.InvenSales TO officer@'180.180.180.2';
GRANT SELECT, INSERT ON warehouse_db.InvenSales TO officer@'181.181.181.2';

-- 신입 --
CREATE USER newcomer@'180.180.180.2' IDENTIFIED BY '12345';
CREATE USER newcomer@'181.181.181.2' IDENTIFIED BY '12345';

GRANT SELECT ON warehouse_db.recipts TO newcomer@'180.180.180.2';
GRANT SELECT ON warehouse_db.recipts TO newcomer@'181.181.181.2';
GRANT SELECT ON warehouse_db.purchase TO newcomer@'180.180.180.2';
GRANT SELECT ON warehouse_db.purchase TO newcomer@'181.181.181.2';

-- 권한 변경 적용
FLUSH PRIVILEGES;

※계정 설정할 때 마다 FLUSH PRIVILEGES; 할 것!

[재고목록]
CREATE TABLE inventory (
    ProductID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ProductName VARCHAR(255) NOT NULL UNIQUE,
    Quantity INT DEFAULT 0 NOT NULL,
    Price VARCHAR(20) NOT NULL,
    InvenStat ENUM('판매중', '품절', '단종') CHARACTER SET utf8 DEFAULT '판매중',
    TimeInven DATETIME DEFAULT NOW())
    CHARACTER SET utf8;

[실제 판매 목록]
CREATE TABLE InvenSales (
    SaleID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL UNIQUE,
    Price VARCHAR(20) NOT NULL,
    Quantity INT NOT NULL,
    SaleStatus ENUM('판매중', '품절', '단종') CHARACTER SET utf8 DEFAULT '판매중')
    CHARACTER SET utf8;
   
[상품 입고]
CREATE TABLE recipts (
    StockInID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL UNIQUE,
    Quantity INT NOT NULL,
    StockInDate DATETIME DEFAULT NOW())
    CHARACTER SET utf8;

[구매 상태]
CREATE TABLE purchase (
    PurchaseID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL UNIQUE,
    CustomerName VARCHAR(100) NOT NULL,
    PurchaseStatus ENUM('구매완료', '취소', '반품') CHARACTER SET utf8 DEFAULT '구매완료',
    PurchaseDate DATETIME DEFAULT NOW())
    CHARACTER SET utf8;
    
구매상태를 3개월이 지나면 자동으로 삭제되는 EVENT 구성
[EVENT]
CREATE EVENT Purchase_Delete
ON SCHEDULE EVERY 7 DAY
DO
DELETE FROM purchase
WHERE PurchaseDate < CURDATE() - INTERVAL 3 MONTH;

[재고목록]
INSERT INTO inventory (ProductName, Quantity, Price, InvenStat)
VALUES 
('아이폰 14', 50, '500,000', '판매중'),
('삼성 갤럭시북', 30, '1,200,000', '판매중'),
('아이패드 프로', 20, '800,000', '판매중'),
('갤럭시 워치 5', 100, '300,000', '판매중'),
('에어팟 프로', 200, '50,000', '판매중'),
('로지텍 키보드 K', 150, '70,000', '판매중'),
('로지텍 마우스 MX', 180, '30,000', '판매중'),
('HP 레이저 프린터', 0, '250,000', '단종'),
('LG 32인치 UHD 모니터', 0, '400,000', '품절'),
('캐논 EOS 90D', 15, '900,000', '판매중'),
('커세어 기계식 키보드', 50, '150,000', '판매중'),
('소니 플레이스테이션 5', 0, '300,000', '품절'),
('델 서버 R740', 0, '2,000,000', '단종');


[실제 판매 목록]
INSERT INTO InvenSales (ProductName, Price, Quantity, SaleStatus)
VALUES 
('아이폰 14', '500,000', 50, '판매중'),
('삼성 갤럭시북', '1,200,000', 30, '판매중'),
('아이패드 프로', '800,000', 20, '판매중'),
('갤럭시 워치 5', '300,000', 100, '판매중'),
('에어팟 프로', '50,000', 200, '판매중'),
('로지텍 키보드 K', '70,000', 150, '판매중'),
('로지텍 마우스 MX', '30,000', 180, '판매중'),
('HP 레이저 프린터', '250,000', 0, '단종'),
('LG 32인치 UHD 모니터', '400,000', 0, '품절'),
('니콘 D7500', '1,000,000', 25, '판매중'),
('비츠 스튜디오3 헤드폰', '350,000', 40, '판매중'),
('MSI 게이밍 노트북', '2,500,000', 12, '판매중'),
('소니 플레이스테이션 5', '300,000', 0, '품절');


[상품 입고]
INSERT INTO recipts (ProductName, Quantity, StockInDate)
VALUES 
('아이폰 14', 30, NOW()),
('삼성 갤럭시북', 20, NOW()),
('아이패드 프로', 40, NOW()),
('갤럭시 워치 5', 50, NOW()),
('에어팟 프로', 150, NOW()),
('로지텍 키보드 K', 100, NOW()),
('마이크로소프트 서피스 프로', 10, NOW()),
('소니 WH-1000XM4', 25, NOW()),
('삼성 T7 외장 SSD', 60, NOW()),
('디지털 카메라 후지필름 X-T4', 15, NOW());


[구매상태]
INSERT INTO purchase (ProductName, CustomerName, PurchaseStatus, PurchaseDate)
VALUES 
('아이폰 14', '김지민', '구매완료', NOW()),
('삼성 갤럭시북', '박상현', '구매완료', NOW()),
('아이패드 프로', '이수연', '구매완료', NOW()),
('갤럭시 워치 5', '정민우', '구매완료', NOW()),
('에어팟 프로', '조민석', '구매완료', NOW()),
('로지텍 키보드 K', '최정호', '구매완료', NOW());

보안팀 DB (security_db)

보안팀 DB (물류DB 조회 불가능) (security_db)
● TABLE = [회원 정보, 블랙 리스트]
● TABLE = [members, blacklist]
● 부장 = 보안 DB의 모든 권한
● 과장 = 보안 DB의 모든 권한
● 대리 = 보안 DB의 members Table 조회,생성,삭제,수정 가능 / 블랙리스트 table 조회 가능
● 신입 = 보안 DB의 members Table 조회,생성만 가능 / 블랙리스트 table all deny

-- 부장 --
CREATE USER manager@'180.180.180.2' IDENTIFIED BY '12345';
CREATE USER manager@'181.181.181.2' IDENTIFIED BY '12345';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, GRANT OPTION, REVOKE ON security_db.* TO manager@'180.180.180.2' IDENTIFIED BY '12345';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, GRANT OPTION, REVOKE ON security_db.* TO manager@'181.181.181.2' IDENTIFIED BY '12345';

-- 대리 --
CREATE USER assistant@'180.180.180.2' IDENTIFIED BY '12345';
CREATE USER assistant@'181.181.181.2' IDENTIFIED BY '12345';

GRANT SELECT, INSERT, DROP, DELETE, DROP ON security_db.members TO assistant@'180.180.180.2' IDENTIFIED BY '12345';
GRANT SELECT, INSERT, DROP, DELETE, DROP ON security_db.members TO assistant@'181.181.181.2' IDENTIFIED BY '12345';
GRANT SELECT ON security_db.blacklist TO assistant@'180.180.180.2' IDENTIFIED BY '12345';
GRANT SELECT ON security_db.blacklist TO assistant@'181.181.181.2' IDENTIFIED BY '12345';

-- 신입 --
CREATE USER junior@'180.180.180.2' IDENTIFIED BY '12345';
CREATE USER junior@'181.181.181.2' IDENTIFIED BY '12345';

GRANT SELECT, INSERT ON security_db.members TO junior@'180.180.180.2' IDENTIFIED BY '12345';
GRANT SELECT, INSERT ON security_db.members TO junior@'181.181.181.2' IDENTIFIED BY '12345';

-- 권한 변경 후 FLUSH PRIVILEGES 실행
FLUSH PRIVILEGES;

[회원 정보]
CREATE TABLE members (
    MemberID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    UserID VARCHAR(50) NOT NULL UNIQUE,
    Email VARCHAR(100) NOT NULL UNIQUE,
    Password VARCHAR(255) NOT NULL,
    Name VARCHAR(100) NOT NULL,
    PhoneNumber VARCHAR(20) NOT NULL,
    SignupDate DATETIME DEFAULT NOW(),
    Role INT DEFAULT 0)
    CHARACTER SET utf8;
    
    0 = '직원' 1= '회원'
    (role은 안넣고 만듬. role은 내가 나중에 로그인 페이지까지 적용되고 난 이후에 구성해서 만들어볼거임)
    
 [블랙 리스트]
 CREATE TABLE blacklist (
    BlacklistID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    UserID VARCHAR(50) NOT NULL UNIQUE,
    Reason VARCHAR(255),
    BlacklistDate DATETIME DEFAULT NOW())
    CHARACTER SET utf8;

[회원 정보]
INSERT INTO members (UserID, Email, Password, Name, PhoneNumber, Role) VALUES
('manage001', 'manage001@example.com', 'password123', '홍길동', '010-1234-5678', '1'),
('manage002', 'manage002@example.com', 'password456', '김철수', '010-2345-6789', '1'),
('manage003', 'manage003@example.com', 'password789', '이수현', '010-3456-7890', '1'),
('manage004', 'manage004@example.com', 'password101', '정민지', '010-4567-8901', '1'),
('manage005', 'manage005@example.com', 'password202', '강민석', '010-5678-9012', '1'),
('manage006', 'manage006@example.com', 'password303', '김유정', '010-6789-0123', '1');
('manage007', 'manage007@example.com', 'password257', '강민', '010-5678-9012', '0'

[블랙 리스트]
INSERT INTO blacklist (UserID, Reason, BlacklistDate) VALUES
('user001', '스팸 메시지 발송', '2024-12-01 14:30:00'),
('user002', '욕설 사용', '2024-12-01 15:00:00'),
('user003', '사기 활동', '2024-12-01 16:10:00'),
('user004', '모욕적인 언어 사용', '2024-12-01 16:50:00'),
('user005', '스팸 메시지 발송', '2024-12-02 09:20:00'),
('user006', '괴롭힘', '2024-12-02 10:15:00'),
('user007', '사칭', '2024-12-02 11:00:00'),
('user008', '허위 계정 생성', '2024-12-02 12:05:00'),
('user009', '혐오 발언', '2024-12-02 13:45:00'),
('user010', '사기 활동', '2024-12-02 14:30:00'),
('user011', '스팸 메시지 발송', '2024-12-02 16:00:00'),
('user012', '사기 시도', '2024-12-02 17:10:00'),
('user013', '위협적인 행동', '2024-12-02 18:20:00'),
('user014', '부적절한 콘텐츠 업로드', '2024-12-02 19:00:00');

행정팀 DB (employee_db)

TABLE = [사원 정보, 휴가자, 입사 예정자]
● TABLE = [staff, vacation, new_hires]
● 부장 = 행정 DB의 모든 권한
● 과장 = 사원 정보(월급 빼고)=staff2 VIEW, 휴가자, 입사 예정자 Table의 모든 권한
● 대리 = 사원 정보(민감한 정보 (주소,월급,전화번호)빼고)=staff3 VIEW 모든 권한 가능 / 휴가자 모든 권한
● 신임 = 휴가자Table의 생성,삭제,수정,조회 가능 / 사원 정보(민감한 정보 빼고) 조회만 가능

-- 부장 --
CREATE USER coworker@'180.180.180.2' IDENTIFIED BY '12345';
CREATE USER coworker@'181.181.181.2' IDENTIFIED BY '12345';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, GRANT OPTION, REVOKE ON employee_db.* TO coworker@'180.180.180.2' IDENTIFIED BY '12345';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, GRANT OPTION, REVOKE ON employee_db.* TO coworker@'181.181.181.2' IDENTIFIED BY '12345';

-- 과장 --
CREATE USER head@'180.180.180.2' IDENTIFIED BY '12345';
CREATE USER head@'181.181.181.2' IDENTIFIED BY '12345';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, GRANT OPTION, REVOKE ON employee_db.vacation TO head@'180.180.180.2' IDENTIFIED BY '12345';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, GRANT OPTION, REVOKE ON employee_db.vacation TO head@'181.181.181.2' IDENTIFIED BY '12345';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, GRANT OPTION, REVOKE ON employee_db.new_hires TO head@'180.180.180.2' IDENTIFIED BY '12345';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, GRANT OPTION, REVOKE ON employee_db.new_hires TO head@'181.181.181.2' IDENTIFIED BY '12345';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, GRANT OPTION, REVOKE ON employee_db.staff2 TO head@'180.180.180.2' IDENTIFIED BY '12345';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, GRANT OPTION, REVOKE ON employee_db.staff2 TO head@'181.181.181.2' IDENTIFIED BY '12345';

-- 대리 --
CREATE USER associate@'180.180.180.2' IDENTIFIED BY '12345';
CREATE USER associate@'181.181.181.2' IDENTIFIED BY '12345';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, GRANT OPTION, REVOKE ON employee_db.staff3 TO associate@'180.180.180.2' IDENTIFIED BY '12345';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, GRANT OPTION, REVOKE ON employee_db.staff3 TO associate@'181.181.181.2' IDENTIFIED BY '12345';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, GRANT OPTION, REVOKE ON employee_db.vacation TO associate@'180.180.180.2' IDENTIFIED BY '12345';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, GRANT OPTION, REVOKE ON employee_db.vacation TO associate@'181.181.181.2' IDENTIFIED BY '12345';

-- 신임 --
CREATE USER newhire@'180.180.180.2' IDENTIFIED BY '12345';
CREATE USER newhire@'181.181.181.2' IDENTIFIED BY '12345';

GRANT SELECT ON employee_db.staff3 TO newhire@'180.180.180.2' IDENTIFIED BY '12345';
GRANT SELECT ON employee_db.staff3 TO newhire@'181.181.181.2' IDENTIFIED BY '12345';
GRANT SELECT, INSERT, DROP, DELETE, UPDATE ON employee_db.vacation TO newhire@'180.180.180.2' IDENTIFIED BY '12345';
GRANT SELECT, INSERT, DROP, DELETE, UPDATE ON employee_db.vacation TO newhire@'181.181.181.2' IDENTIFIED BY '12345';

-- 권한 변경 후 FLUSH PRIVILEGES 실행
FLUSH PRIVILEGES;

[사원 정보]
CREATE TABLE staff (
    StaffID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Address VARCHAR(100) NOT NULL,
    Email VARCHAR(100) NOT NULL,
    Department VARCHAR(100),
    PhoneNumber VARCHAR(20) UNIQUE,
    Salary VARCHAR(20),
    HireDate DATE,
    Position VARCHAR(50))
    CHARACTER SET utf8;

[휴가자]
CREATE TABLE vacation (
    StaffID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Department VARCHAR(50) NOT NULL,
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL,
    Reason VARCHAR(255),
    V_status ENUM('휴가 중', '휴가 예정', '취소', '대기') CHARACTER SET utf8 DEFAULT '대기',
    RequestDate DATE)
    CHARACTER SET utf8;

[입사 예정자]
CREATE TABLE new_hires (
    StaffID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) NOT NULL,
    StartDate DATE NOT NULL,
    Department VARCHAR(100),
    Position VARCHAR(50),
    Status ENUM('대기', '확정') CHARACTER SET utf8 DEFAULT '대기')
    CHARACTER SET utf8;

[특정 컬럼 없는 VIEW 만듦]
CREATE VIEW staff2 AS SELECT StaffID, Name, Address ,Email, Department, PhoneNumber, HireDate, Position FROM staff;
		
CREATE VIEW staff3 AS SELECT StaffID, Name, Email, Department ,HireDate, Position FROM staff;

[사원 정보]
INSERT INTO staff (Name, Address, Email, Department, PhoneNumber, Salary, HireDate, Position) VALUES
('홍길동', '서울시 강남구 역삼동', 'hong@example.com', '행정팀', '010-1234-5678', '3500000', '2020-03-01', '팀장'),
('김철수', '서울시 송파구 잠실동', 'kim@example.com', '영업팀', '010-2345-6789', '3000000', '2019-06-15', '과장'),
('이영희', '서울시 마포구 상수동', 'lee@example.com', '전산팀', '010-3456-7890', '2800000', '2021-02-20', '대리'),
('박지민', '서울시 서초구 방배동', 'park@example.com', '물류팀', '010-4567-8901', '3200000', '2018-11-11', '사원'),
('최은지', '서울시 강동구 천호동', 'choi@example.com', '보안팀', '010-5678-9012', '2800000', '2022-08-10', '사원'),
('장수빈', '서울시 관악구 신림동', 'jang@example.com', '행정팀', '010-6789-0123', '3500000', '2017-05-21', '팀장'),
('정민지', '서울시 동작구 노량진동', 'jung@example.com', '영업팀', '010-7890-1234', '5000000', '2015-04-05', '부장'),
('김재훈', '서울시 종로구 종로1가', 'kimjh@example.com', '전산팀', '010-8901-2345', '4200000', '2016-09-30', '과장'),
('윤아영', '서울시 용산구 한남동', 'yuna@example.com', '물류팀', '010-9012-3456', '2800000', '2021-03-25', '대리'),
('조현수', '서울시 구로구 고척동', 'jo@example.com', '보안팀', '010-0123-4567', '3500000', '2019-12-12', '팀장'),
('강민석', '서울시 성동구 성수동', 'kang@example.com', '영업팀', '010-1234-5679', '4000000', '2018-06-17', '부장'),
('문지은', '서울시 양천구 목동', 'moon@example.com', '행정팀', '010-2345-6780', '3100000', '2020-07-07', '사원'),
('김민지', '서울시 중구 을지로', 'minji@example.com', '물류팀', '010-3456-7891', '3500000', '2022-01-10', '대리'),
('임지수', '서울시 강서구 화곡동', 'im@example.com', '전산팀', '010-4567-8902', '3200000', '2021-11-15', '사원');

[휴가자]
INSERT INTO vacation (Name, Department, StartDate, EndDate, Reason, V_status, RequestDate) VALUES
('김철수', '영업팀', '2024-12-10', '2024-12-12', '휴식', '휴가 예정', '2024-11-25'),
('이영희', '전산팀', '2024-12-15', '2024-12-18', '가족 모임', '휴가 중', '2024-12-01'),
('최은지', '보안팀', '2024-12-05', '2024-12-08', '건강검진', '휴가 예정', '2024-11-22'),
('정민지', '영업팀', '2024-12-08', '2024-12-12', '연차 휴가', '휴가 예정', '2024-11-25'),
('김재훈', '전산팀', '2024-12-10', '2024-12-14', '여행', '휴가 예정', '2024-11-29'),
('조현수', '보안팀', '2024-12-15', '2024-12-17', '집수리', '휴가 예정', '2024-12-03'),
('김민지', '물류팀', '2024-12-01', '2024-12-03', '병원 치료', '휴가 중', '2024-11-25'),
('임지수', '전산팀', '2024-12-18', '2024-12-20', '여행', '휴가 예정', '2024-12-02');

[입사 예정자]
INSERT INTO new_hires (Name, Email, StartDate, Department, Position, Status) VALUES
('김도훈', 'kimdh@example.com', '2024-12-01', '행정팀', '팀장', '확정'),
('이수현', 'leesuhyun@example.com', '2024-12-03', '영업팀', '과장', '확정'),
('박서연', 'parkseoyeon@example.com', '2024-12-05', '전산팀', '대리', '대기'),
('정우진', 'jungwoojin@example.com', '2024-12-07', '물류팀', '사원', '대기'),
('한지우', 'hanjiwoo@example.com', '2024-12-10', '보안팀', '사원', '확정'),
('최진혁', 'choojinhyuk@example.com', '2024-12-12', '행정팀', '팀장', '확정'),
('김유정', 'kimyujung@example.com', '2024-12-15', '영업팀', '대리', '확정'),
('오민호', 'ohminho@example.com', '2024-12-17', '전산팀', '과장', '대기'),
('배수지', 'baesooji@example.com', '2024-12-20', '물류팀', '사원', '확정'),
('최현준', 'choihyunjoon@example.com', '2024-12-22', '보안팀', '대리', '확정'),
('구상민', 'koosangmin@example.com', '2024-12-25', '영업팀', '부장', '대기'),
('유다정', 'yudajung@example.com', '2024-12-28', '행정팀', '사원', '확정'),
('박지연', 'parkjiyeon@example.com', '2025-01-02', '물류팀', '대리', '확정'),
('이태성', 'leetaeseong@example.com', '2025-01-05', '전산팀', '사원', '대기');

영업팀 DB (sales_db)

TABLE = [판매 실적, 경쟁사 분석]
● TABLE = [sales, competitor]
● 부장 = 영업DB의 모든 권한
● 과장 = 영업DB의 모든 권한
● 대리 = 경쟁사 분석 Table의 조회만 가능, 판매 실적 Table의 모든 권한
● 신임 = 판매실적 Table의 생성과 조회만 가능 / 경쟁사 분석 table all deny

-- 부장 --
CREATE USER director@'180.180.180.2' IDENTIFIED BY '12345';
CREATE USER director@'181.181.181.2' IDENTIFIED BY '12345';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, GRANT OPTION, REVOKE ON sales_db.* TO director@'180.180.180.2' IDENTIFIED BY '12345';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, GRANT OPTION, REVOKE ON sales_db.* TO director@'181.181.181.2' IDENTIFIED BY '12345';

-- 대리 --
CREATE USER deputy@'180.180.180.2' IDENTIFIED BY '12345';
CREATE USER deputy@'181.181.181.2' IDENTIFIED BY '12345';

GRANT SELECT ON sales_db.competitor TO deputy@'180.180.180.2' IDENTIFIED BY '12345';
GRANT SELECT ON sales_db.competitor TO deputy@'181.181.181.2' IDENTIFIED BY '12345';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, GRANT OPTION, REVOKE ON sales_db.sales TO deputy@'180.180.180.2' IDENTIFIED BY '12345';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, GRANT OPTION, REVOKE ON sales_db.sales TO deputy@'181.181.181.2' IDENTIFIED BY '12345';

-- 신임 --
CREATE USER beginner@'180.180.180.2' IDENTIFIED BY '12345';
CREATE USER beginner@'181.181.181.2' IDENTIFIED BY '12345';

GRANT SELECT, INSERT ON sales_db.sales TO beginner@'180.180.180.2' IDENTIFIED BY '12345';
GRANT SELECT, INSERT ON sales_db.sales TO beginner@'181.181.181.2' IDENTIFIED BY '12345';

-- 권한 변경 후 FLUSH PRIVILEGES 실행
FLUSH PRIVILEGES;

[판매 실적]
CREATE TABLE sales (
    SaleID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    SalesPerson VARCHAR(100) NOT NULL,
    ProductName VARCHAR(100) NOT NULL,
    SaleDate DATE,
    Quantity INT NOT NULL,
    SaleAmount VARCHAR(20) NOT NULL)
    CHARACTER SET utf8;

[경쟁사 분석]
CREATE TABLE competitor (
    CompetitorID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    CompetitorName VARCHAR(100) NOT NULL UNIQUE,
    ProductName VARCHAR(100) NOT NULL,
    Price VARCHAR(20) NOT NULL,
    MarketShare DECIMAL(4, 2),
    Reputation VARCHAR(255),
    AnalysisDate DATE)
    CHARACTER SET utf8;

# DECIMAL(5, 2)는 숫자 5자리 중에 소수점 자리는 2자리를 의미함
# MarketShare은 시장 점유율임
# Reputation은 평판임

[판매실적]
INSERT INTO sales (SalesPerson, ProductName, SaleDate, Quantity, SaleAmount) VALUES
('김철수', '아이폰 14', '2024-12-01', 24, '12000000'),
('정민지', '삼성 갤럭시북', '2024-12-02', 8, '9600000'),
('강민석', '아이패드 프로', '2024-12-03', 11, '8800000'),
('이수현', '갤럭시 워치 5', '2024-12-04', 48, '14400000'),
('김유정', '에어팟 프로', '2024-12-05', 130, '6500000'),
('김철수', '로지텍 키보드 K', '2024-12-06', 30, '2100000');


판매실적일을 기준으로 1개월이 지나면 자동으로 삭제된다.
[EVENT]
CREATE EVENT Sales_Delete
ON SCHEDULE EVERY 7 DAY
DO
DELETE FROM sales
WHERE SaleDate < CURDATE() - INTERVAL 1 MONTH;

[경쟁사 분석]
INSERT INTO competitor (CompetitorName, ProductName, Price, MarketShare, Reputation, AnalysisDate) VALUES
('온라인몰 A', '아이폰 14', '550,000', 25.50, '빠른 배송', '2024-12-01'),
('쇼핑몰 B', '삼성 갤럭시북', '1,150,000', 18.30, '저렴한 가격', '2024-12-02'),
('디지털샵 C', '아이패드 프로', '850,000', 12.40, '빠른 배송', '2024-12-03'),
('웹스토어 D', '갤럭시 워치 5', '320,000', 10.50, '빠른 배송, 고객센터 우수', '2024-12-04'),
('이커머스몰 E', '에어팟 프로', '60,000', 20.00, '고객센터 우수', '2024-12-05'),
('쇼핑플랫폼 F', '로지텍 키보드 K', '80,000', 8.90, '빠른 배송, 고객센터 우수', '2024-12-06');

'세미프로젝트' 카테고리의 다른 글

DB EVENT (0)	2024.12.03
방화벽 구성 (0)	2024.12.03
DB 이중화 옵션 (SLAVE에서 설정) (0)	2024.11.28
RAID에 DB구성하기 (0)	2024.11.28
MariaDB 이중화 (Master - Slave) (0)	2024.11.28

'세미프로젝트' Related Articles

kjh00n의 기록저장소

DB 구성 본문

DB 구성

물류팀 DB (warehouse_db)

보안팀 DB (security_db)

행정팀 DB (employee_db)

영업팀 DB (sales_db)

'세미프로젝트' 카테고리의 다른 글

티스토리툴바