---
# System prepended metadata

title: スケジュール管理システムDB
tags: [スケジュール管理システムTeam]

---

スケジュール管理システムDB 
==

# DDL
## sms データベース削除
DROP DATABASE sms;

## sms データベース作成
CREATE DATABASE sms;

## sms データベースに接続
\c sms

## 社員マスター
CREATE TABLE m_employee (
 emp_id SERIAL NOT NULL PRIMARY KEY,
 registered_date TIMESTAMP,
 update_date TIMESTAMP,
 password CHARACTER VARYING(60),
 emp_name CHARACTER VARYING(15),
 department CHARACTER VARYING(10),
 contact_extension CHARACTER VARYING(4),
 contact_mobile CHARACTER VARYING(11)
);

## グループマスター
CREATE TABLE m_group (
 group_id SERIAL NOT NULL PRIMARY KEY,
 administrator_id INT NOT NULL,
 registered_date TIMESTAMP,
 update_date TIMESTAMP,
 group_name CHARACTER VARYING(10),
 FOREIGN KEY (administrator_id) REFERENCES m_employee (emp_id)
);

## グループ割当情報
CREATE TABLE t_group_allocation  (
 group_id INT NOT NULL,
 emp_id INT NOT NULL,
 registered_date TIMESTAMP,
 update_date TIMESTAMP,
 delete_flag BOOLEAN,
 PRIMARY KEY (group_id,emp_id),
 FOREIGN KEY (group_id) REFERENCES m_group (group_id),
 FOREIGN KEY (emp_id) REFERENCES m_employee (emp_id)
);

## 予定情報
CREATE TABLE t_schedule (
 schedule_id SERIAL NOT NULL PRIMARY KEY,
 emp_id INT NOT NULL,
 registered_date TIMESTAMP,
 update_date TIMESTAMP,
 start_date TIMESTAMP,
 end_date TIMESTAMP,
 schedule_contents CHARACTER VARYING(30),
 open_range INT NOT NULL,
 FOREIGN KEY (emp_id) REFERENCES m_employee (emp_id)
);

## 出退勤情報
CREATE TABLE t_attendance (
 attendance_id SERIAL NOT NULL PRIMARY KEY,
 emp_id INT NOT NULL,
 registered_date TIMESTAMP,
 attendance_status INT,
 FOREIGN KEY (emp_id) REFERENCES m_employee (emp_id)
);

## 予定公開割当範囲情報
CREATE TABLE t_schedule_range_allocation  (
 schedule_id INT NOT NULL,
 allocation_group_id INT NOT NULL,
 PRIMARY KEY (schedule_id, allocation_group_id),
 FOREIGN KEY (allocation_group_id) REFERENCES m_group (group_id),
 FOREIGN KEY (schedule_id) REFERENCES t_schedule (schedule_id)
);

## 暗号モジュールを有効にする
CREATE EXTENSION pgcrypto;

# DML

## 社員マスター
INSERT INTO m_employee(registered_date, update_date, password, emp_name, department, contact_extension, contact_mobile)
 VALUES (current_timestamp, current_timestamp, crypt('1', gen_salt('bf')), '総務一郎', '総務部', '1000', '09011112222')
 , (current_timestamp, current_timestamp, crypt('2', gen_salt('bf')), '総務二子', '総務部', '1000', '09012341234')
 , (current_timestamp, current_timestamp, crypt('3', gen_salt('bf')), '開発三郎', '開発部', '1001', '09055556666')
 , (current_timestamp, current_timestamp, crypt('4', gen_salt('bf')), '開発四朗', '開発部', '1001', '07012341234')
 , (current_timestamp, current_timestamp, crypt('5', gen_salt('bf')), '営業五郎', '営業部', '1002', '07011111111');

## パスワードの確認は下記のようなSELECT文
SELECT * FROM m_employee WHERE emp_id = 1 AND password = crypt('1', password);


## グループマスター
INSERT INTO m_group(registered_date, update_date, administrator_id, group_name)
 VALUES (current_timestamp, current_timestamp, 2, '新人研修チーム')
 , (current_timestamp, current_timestamp, 4, '新商品開発チーム');

## グループ割当情報
INSERT INTO t_group_allocation(group_id, emp_id, registered_date, update_date, delete_flag)
 VALUES (1, 2, current_timestamp, current_timestamp, false)
 , (1, 1, current_timestamp, current_timestamp, false)
 , (2, 4, current_timestamp, current_timestamp, false)
 , (2, 2, current_timestamp, current_timestamp, true)
 , (2, 3, current_timestamp, current_timestamp, false);

## 予定情報
INSERT INTO t_schedule(emp_id, registered_date, update_date, start_date, end_date, schedule_contents, open_range)
 VALUES (1, current_timestamp, current_timestamp, '2020-06-01 12:00:00', '2020-06-01 13:00:00', 'ランチミーティング', 0)
 , (1, current_timestamp, current_timestamp, '2020-06-03 9:00:00', '2020-06-01 18:00:00', '健康診断', 2)
 , (1, current_timestamp, current_timestamp, '2020-06-05 11:00:00', '2020-06-05 12:00:00', 'リーダーミーティング', 1)
 , (3, current_timestamp, current_timestamp, '2020-06-30 15:00:00', '2020-06-30 17:00:00', '新商品開発定例会', 1)
 , (5, current_timestamp, current_timestamp, '2020-07-01 09:00:00', '2020-07-01 10:00:00', '営業部ミーティング', 0);

## 予定公開割当範囲情報
INSERT INTO t_schedule_range_allocation(schedule_id, allocation_group_id)
 VALUES (3, 1)
  ,(3, 2)
  ,(4, 2);

## 出退勤情報
    本日の日付に変更してから利用してください。
INSERT INTO t_attendance(emp_id, registered_date, attendance_status)
 VALUES (1, '2020-06-01 8:45:00', 1)
 , (1, '2020-06-01 18:05:30', 2)
 , (2, '2020-06-01 8:47:25', 1)
 , (2, '2020-06-01 19:30:20', 2)
 , (3, '2020-06-01 0:00:00', 7)
 , (1, '2020-06-02 8:45:00', 1)
 , (2, '2020-06-02 8:47:25', 1)
 , (3, '2020-06-02 8:50:10', 1)
 , (3, '2020-06-02 11:00:00', 4);


***
###### tags: `スケジュール管理システムTeam`
