• 바드에게 질문했습니다. → PostgreSQL 하지만, 제 DB에 Mysql이 깔려있어 Mysql로 진행하겠습니다.

Create Table 은 번거로워 바드에게 맏겼습니다.

• 테이블을 모두 생성 뒤 alter table로 FK 를 거는 방법과
• 테이블에 FK를 걸고 제약조건이 적은 테이블 부터 차례차례 순서대로 지정하는 방법

두가지가 있지만, 관계 생성이 번거로워 테이블 생성을 맏긴 뒤 생성 순서만 적절하게 지정해 주었습니다.

EER.pdf

트랜잭션을 사용할 예정임으로 MySQL의 default engine(InnoDB)를 러프하게 지정해줬습니다.

create schema study;
use study;

CREATE TABLE member (
  member_id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  rule VARCHAR(255),
  email VARCHAR(255) NOT NULL,
  contact_number VARCHAR(255),

  PRIMARY KEY (member_id)
);

CREATE TABLE subject (
  subject_id INT NOT NULL AUTO_INCREMENT,
  subject_name VARCHAR(255) NOT NULL,
  description VARCHAR(255),

  PRIMARY KEY (subject_id)
);

CREATE TABLE study_group (
  group_id INT NOT NULL AUTO_INCREMENT,
  subject_id INT NOT NULL,
  group_name VARCHAR(255) NOT NULL,
  description VARCHAR(255),
  foundation_date DATE,

  PRIMARY KEY (group_id),
  FOREIGN KEY (subject_id) REFERENCES subject (subject_id)
);

CREATE TABLE location (
  location_id INT NOT NULL AUTO_INCREMENT,
  location_name VARCHAR(255) NOT NULL,
  address VARCHAR(255),
  operating_hours VARCHAR(255),

  PRIMARY KEY (location_id)
);

CREATE TABLE project (
  project_id INT NOT NULL AUTO_INCREMENT,
  project_name VARCHAR(255) NOT NULL,
  description VARCHAR(255),
  start_date DATE NOT NULL,
  end_date DATE,
	project_type ENUM('TOY', 'SIDE') NOT NULL,

  PRIMARY KEY (project_id)
);

CREATE TABLE toy_project (
  project_id INT NOT NULL,
  FOREIGN KEY (project_id) REFERENCES project (project_id)
);

CREATE TABLE side_project (
  project_id INT NOT NULL,
  FOREIGN KEY (project_id) REFERENCES project (project_id)
);

CREATE TABLE project_group (
  project_group_id INT NOT NULL AUTO_INCREMENT,
  project_id INT NOT NULL,
  member_id INT NOT NULL,
  role varchar(255) NOT NULL,
  
  PRIMARY KEY (project_group_id),
  FOREIGN KEY (member_id) REFERENCES member (member_id),
  FOREIGN KEY (project_id) REFERENCES project (project_id)
);

CREATE TABLE rule (
  rule_id INT NOT NULL AUTO_INCREMENT,
  group_id INT NOT NULL,
  rule_name VARCHAR(255) NOT NULL,
  description VARCHAR(255),

  PRIMARY KEY (rule_id),
  FOREIGN KEY (group_id) REFERENCES study_group (group_id)
);

CREATE TABLE presentation (
  presentation_id INT NOT NULL AUTO_INCREMENT,
  member_id INT NOT NULL,
  group_id INT NOT NULL,
  date_time DATE NOT NULL,
  turn INT NOT NULL,
  presentation_url VARCHAR(255),

  PRIMARY KEY (presentation_id),
  FOREIGN KEY (member_id) REFERENCES member (member_id),
  FOREIGN KEY (group_id) REFERENCES study_group (group_id)
);

CREATE TABLE presentation_topic (
  topic_id INT NOT NULL AUTO_INCREMENT,
  presentation_id INT NOT NULL,
  subject_id INT NOT NULL,
  topic_name VARCHAR(255) NOT NULL,
  description VARCHAR(255),

  PRIMARY KEY (topic_id),
  FOREIGN KEY (presentation_id) REFERENCES presentation (presentation_id),
  FOREIGN KEY (subject_id) REFERENCES subject (subject_id)
);

CREATE TABLE attend (
  attend_id INT NOT NULL AUTO_INCREMENT,
  group_id INT NOT NULL,
  presentation_id INT NOT NULL,
  member_id INT NOT NULL,
  location_id INT NOT NULL,

  PRIMARY KEY (attend_id),
  FOREIGN KEY (group_id) REFERENCES study_group (group_id),
  FOREIGN KEY (presentation_id) REFERENCES presentation (presentation_id),
  FOREIGN KEY (member_id) REFERENCES member (member_id),
  FOREIGN KEY (location_id) REFERENCES location (location_id)
);

CREATE TABLE penalty (
  member_id INT NOT NULL AUTO_INCREMENT,
  sequence INT NOT NULL,
  rule_id INT NOT NULL,
  penalty_content VARCHAR(255),
  imposition_date DATE,

  PRIMARY KEY (member_id),
  FOREIGN KEY (rule_id) REFERENCES rule (rule_id)
);

CREATE TABLE membership_fee (
  fee_id INT NOT NULL AUTO_INCREMENT,
  rule_id INT NOT NULL,
  member_id INT NOT NULL,
  fee_name VARCHAR(255) NOT NULL,
  amount DECIMAL(10,2),
  payment_date DATE, 

  PRIMARY KEY (fee_id),
  FOREIGN KEY (rule_id) REFERENCES rule (rule_id),
  FOREIGN KEY (member_id) REFERENCES member (member_id)
);