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

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

# 実行環境
- macOS
- psql (PostgreSQL) 13.3

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

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

ER図は次のようになります．
![ER-grade.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/668961/e387685c-814d-f3fe-832b-754053b9b0da.png)

# テーブルの作成
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)
```