【DB入門】サブテーブルを作成する

2020年6月22日

スポンサーリンク

説明

これからは以降の説明用にテストテーブルを複数作成します。

各テーブルのCreate文をコピーして実行し、テーブルを作成して下さい。

注意ここまではテーブル「m_school」が作成されている状態を想定しています。

作られていない場合、エラーになってしまいますのでリンクを参照し作成して下さい。

テーブル「m_school」の作成

 

CREATE文を実行するのが面倒という方に

この下に書かれているCREATE文8個をまとめたテキストファイルを用意しました。

[DOWNLOAD]よりテキストファイルをダウンロードし、pgAdminより実行してください。

 

m_grade(学年マスタ)

学年の情報(1年生、2年生など)を格納するテーブルです。

CREATE TABLE m_grade
(
  school_code character varying(10) NOT NULL,
  grade_code character varying(10) NOT NULL,
  grade_name character varying(100) NOT NULL,
  CONSTRAINT pk_m_grade PRIMARY KEY (school_code, grade_code),
  CONSTRAINT fk_m_grade_m_school FOREIGN KEY (school_code)
      REFERENCES m_school (school_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE m_grade
  OWNER TO postgres;

 

m_class(クラスマスタ)

クラスの情報(1組、2組など)を格納するテーブルです。

CREATE TABLE m_class
(
  school_code character varying(10) NOT NULL,
  grade_code character varying(10) NOT NULL,
  class_code character varying(10) NOT NULL,
  class_name character varying(100) NOT NULL,
  CONSTRAINT pk_m_class PRIMARY KEY (school_code, grade_code, class_code),
  CONSTRAINT fk_m_class_m_grade FOREIGN KEY (school_code, grade_code)
      REFERENCES m_grade (school_code, grade_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_m_class_m_school FOREIGN KEY (school_code)
      REFERENCES m_school (school_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE m_class
  OWNER TO postgres;

 

m_student(学生マスタ)

学生情報(氏名、カナ、性別等)を格納するテーブルです。

CREATE TABLE m_student
(
  school_code character varying(10) NOT NULL,
  student_code character varying(10) NOT NULL,
  student_name character varying(100) NOT NULL,
  student_kana character varying(100),
  gender numeric,
  birthday date,
  CONSTRAINT pk_m_student PRIMARY KEY (school_code, student_code),
  CONSTRAINT fk_m_student_m_school FOREIGN KEY (school_code)
      REFERENCES m_school (school_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE m_student
  OWNER TO postgres;

 

m_class_student(クラス学生マスタ)※クラスに所属する学生を格納するテーブル

クラスに所属する学生(1組にはA君、B君、C君、2組にはD君、E君等)を格納するテーブルです。

CREATE TABLE m_class_student
(
  school_code character varying(10) NOT NULL,
  grade_code character varying(10) NOT NULL,
  class_code character varying(10) NOT NULL,
  student_code character varying(10) NOT NULL,
  CONSTRAINT pk_m_class_student PRIMARY KEY (school_code, grade_code, class_code, student_code),
  CONSTRAINT fk_m_class_student_m_class FOREIGN KEY (school_code, grade_code, class_code)
      REFERENCES m_class (school_code, grade_code, class_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_m_class_student_m_grade FOREIGN KEY (school_code, grade_code)
      REFERENCES m_grade (school_code, grade_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_m_class_student_m_school FOREIGN KEY (school_code)
      REFERENCES m_school (school_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE m_class_student
  OWNER TO postgres;

 

m_subject(科目マスタ)

科目情報(国語、算数、英語)を格納するテーブルです。

CREATE TABLE m_subject
(
  school_code character varying(10) NOT NULL,
  subject_code character varying(10) NOT NULL,
  subject_name character varying(100),
  CONSTRAINT pk_m_subject PRIMARY KEY (school_code, subject_code),
  CONSTRAINT fk_m_subject_m_school FOREIGN KEY (school_code)
      REFERENCES m_school (school_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE m_subject
  OWNER TO postgres;

 

m_timetable(時間割マスタ)

時間割(月曜の1限目は国語、2限目は体育等)の情報を格納するテーブルです。

CREATE TABLE m_timetable
(
  school_code character varying(10) NOT NULL,
  grade_code character varying(10) NOT NULL,
  week_day numeric NOT NULL,
  time_no numeric NOT NULL,
  subject_code character varying(10) NOT NULL,
  CONSTRAINT pk_m_timetable PRIMARY KEY (school_code, grade_code, week_day, time_no),
  CONSTRAINT fk_m_timetable_m_grade FOREIGN KEY (school_code, grade_code)
      REFERENCES m_grade (school_code, grade_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_m_timetable_m_school FOREIGN KEY (school_code)
      REFERENCES m_school (school_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_m_timetable_m_subject FOREIGN KEY (school_code, subject_code)
      REFERENCES m_subject (school_code, subject_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE m_timetable
  OWNER TO postgres;

 

t_student_score(生徒教科成績)

生徒と科目ごとの成績(A君の国語は90点、算数は80点等)を格納するテーブルです。

CREATE TABLE t_student_score
(
  school_code character varying(10) NOT NULL,
  grade_code character varying(10) NOT NULL,
  class_code character varying(10) NOT NULL,
  student_code character varying(10) NOT NULL,
  subject_code character varying(10) NOT NULL,
  score numeric,
  CONSTRAINT pk_t_student_score PRIMARY KEY (school_code, grade_code, class_code, student_code, subject_code),
  CONSTRAINT fk_t_student_score_m_class FOREIGN KEY (school_code, grade_code, class_code)
      REFERENCES m_class (school_code, grade_code, class_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_t_student_score_m_grade FOREIGN KEY (school_code, grade_code)
      REFERENCES m_grade (school_code, grade_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_t_student_score_m_school FOREIGN KEY (school_code)
      REFERENCES m_school (school_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_t_student_score_m_student FOREIGN KEY (school_code, student_code)
      REFERENCES m_student (school_code, student_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_t_student_score_m_subject FOREIGN KEY (school_code, subject_code)
      REFERENCES m_subject (school_code, subject_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE t_student_score
  OWNER TO postgres;

 

サブテーブルのCREATE文は以上です。

テーブルへのコメント

少し本編と逸れますがテーブルへコメントを追加する方法を紹介します。

【DB入門】テーブルにコメントを追加する

 

次に

次はデータ登録を紹介します。

【DB入門】テーブルにデータの登録をする(データの直接入力)

 

一つ前の説明はこちらです。

【DB入門】テーブルの作成