データベースで自分の成績の管理 (PostgreSQL)

はじめに

SQLの練習として自分の成績を管理するデータベースを作成してみました．

実行環境

• macOS
• psql (PostgreSQL) 13.3

作成するテーブル

今回は次の4つのテーブルを作成したいと思います．

• 科目テーブル :「科目名」，「単位数」を管理
• 区分テーブル :科目の区分（「教養科目」，「学科基礎科目」，「実験科目」など）の情報を管理
• 大区分テーブル :大区分（「全学部共通科目」，「学科専門科目」など）の情報を管理
• 成績評価テーブル : 「成績評価」，「履修年度」，「履修学期（前期・後期）」の情報を管理

ER図は次のようになります．

テーブルの作成

SQLでテーブルの作成を行います．

CREATE TABLE big_categories( --大区分
  id SERIAL PRIMARY KEY,
  name VARCHAR(32) NOT NULL -- 大区分名
);

CREATE TABLE categories( --区分
  id SERIAL PRIMARY KEY,
  name VARCHAR(32) NOT NULL, -- 区分名
  big_category_id INTEGER,
  
  FOREIGN KEY(big_category_id) REFERENCES big_categories(id)
);

CREATE TABLE subjects( --科目
  id SERIAL PRIMARY KEY,
  name VARCHAR(64) NOT NULL, -- 科目名
  credit_num INTEGER NOT NULL, -- 単位数
  category_id INTEGER NOT NULL,
  
  FOREIGN KEY(category_id) REFERENCES categories(id)
);

CREATE TABLE evaluations( --評価
  id SERIAL PRIMARY KEY,
  subject_id INTEGER NOT NULL,
  master_year INTEGER, -- 履修年度
  master_semester VARCHAR(8), -- 学期（前期・後期）
  evaluation VARCHAR(4), -- 成績評価
  
  FOREIGN KEY(subject_id) REFERENCES subjects(id) 
);

成績の管理

データの挿入

INSERT文でデータを挿入します．
データ挿入の例は次の通りです．
idはserialとしているため，idに値を挿入しなくても自動で．採番されますが今回はidにも値を挿入します．

INSERT INTO big_categories VALUES(1,'全学部共通科目');
INSERT INTO big_categories VALUES(2,'学科専門科目');

INSERT INTO categories VALUES(1,'教養科目',1);
INSERT INTO categories VALUES(2,'理系科目',1);
INSERT INTO categories VALUES(3,'学科基礎科目',2);
INSERT INTO categories VALUES(4,'実験科目',2);

INSERT INTO subjects VALUES(1,'微分積分',2,2);
INSERT INTO subjects VALUES(2,'線形代数',2,2);
INSERT INTO subjects VALUES(3,'経営学',2,1);
INSERT INTO subjects VALUES(4,'プログラミング基礎',2,3);
INSERT INTO subjects VALUES(5,'コンピュータネットワーク',2,3);
INSERT INTO subjects VALUES(6,'情報通信工学実験',4,4);

INSERT INTO evaluations VALUES(1,1,2019,'前期','秀');
INSERT INTO evaluations VALUES(2,2,2019,'前期','優');
INSERT INTO evaluations VALUES(3,3,2019,'後期','可');
INSERT INTO evaluations VALUES(4,4,2020,'前期','良');
INSERT INTO evaluations VALUES(5,5,2020,'後期','不可');
INSERT INTO evaluations VALUES(6,6,2019,'前期','優');

データの取得

成績一覧

科目名，成績評価，科目の区分を取得するには次のようなSQLで取得できます．

SELECT subjects.name, evaluation, categories.name FROM evaluations
INNER JOIN subjects ON subjects.id=evaluations.subject_id
INNER JOIN categories ON categories.id=subjects.category_id;

INNER JOINを用いてテーブルの内部結合を行い，科目名，成績評価，区分を取得しています．

           name           | evaluation |     name     
--------------------------+------------+--------------
 微分積分                 | 秀         | 理系科目
 線形代数                 | 優         | 理系科目
 経営学                   | 可         | 教養科目
 プログラミング基礎       | 良         | 学科基礎科目
 コンピュータネットワーク | 不可       | 学科基礎科目
 情報通信工学実験         | 優         | 実験科目
(6 rows)

区分ごとの取得単位数

区分ごとの取得単位数のデータを取得するには次のようなSQLを実行します．

SELECT categories.name, sum(subjects.credit_num) FROM evaluations
INNER JOIN subjects ON evaluations.subject_id=subjects.id
INNER JOIN categories ON subjects.category_id=categories.id
WHERE evaluation IS NOT NULL AND evaluation<>'不可'
GROUP BY categories.name;

評価がまだ確定していなくてNULLとなっている科目と評価が「不可」で単位を取得できていないものを除外して，単位数の合計を計算しています．

     name     | sum 
--------------+-----
 理系科目     |   4
 実験科目     |   4
 学科基礎科目 |   2
 教養科目     |   2
(4 rows)

GPAの計算

GPAの計算をSQLで行います．
GP (Grade Point) は次のように対応します．

成績	GP
秀	4
優	3
良	2
可	1
不可	0

科目のGrade PointをGP，その科目の単位数をn，履修登録した単位数の合計をNとすると，GPAは次のように計算されます．

$$
GPA=\frac{\sum(GP\times n)}{N}
$$

SQLでGPAの計算は次のように行います．

SELECT (sum((CASE WHEN evaluation='秀' THEN 4.0
  WHEN evaluation='優' THEN 3.0
  WHEN evaluation='良' THEN 2.0
  WHEN evaluation='可' THEN 1.0
  WHEN evaluation='不可' THEN 0.0
  END)*subjects.credit_num)) / (SELECT sum(subjects.credit_num) FROM evaluations
  INNER JOIN subjects
  ON subjects.id=evaluations.subject_id WHERE(evaluation<>'認' AND evaluation IS NOT NULL))
  AS gpa
  FROM evaluations 
  INNER JOIN subjects 
  ON subjects.id=evaluations.subject_id
  WHERE(evaluation<>'認' AND evaluation IS NOT NULL);

CASEで成績評価とGPを対応させて，評価が確定していなくてNULLとなっている科目や，単位認定でGPAに含まれない科目は除外して計算しています．

        gpa         
--------------------
 2.2857142857142857
(1 row)