付録O　最終演習課題 DB スキーマ仕様書(保有資格管理)

配布タイミング:工程 3「仕様確定」

これは、工程 3 で 自分たちの設計と見比べるための、参考の DB スキーマ です。まず工程 2 で 自分たちが設計したスキーマ を作り、それと この仕様を見比べて 違いを スキーマ比較メモ に書き出してください。そのうえで、基本はこの仕様に合わせて実装することをおすすめします (名前が揃い、相談や見直しがしやすいためです)。ただし、どうしても自分たちの設計で進めたいチームは、そのまま実装してもかまいません (工程 2 のレビューを通った設計が条件)。いきなり写すのではなく「自分の設計とどこが違ったか」を確かめるのが学びです。

既存の TrainingDB(社員系・商品系と同じデータベース)に テーブルを 2 つ追加 します。

1. テーブル構成(全体像)

社員(employees)と資格(qualifications)は 多対多 の関係です。間に 中間テーブル employee_qualifications を置いて表します。

employees は既存(第 16 章で作成済み)。今回 追加するのは qualifications と employee_qualifications の 2 つ です。
作成順は qualifications → employee_qualifications(外部キーの参照先を先に作る)。

2. `qualifications`(資格マスタ)

「資格そのもの」の情報です。どの社員が持っていても同じ内容(=マスタ)。

列名	型	制約	説明
`qualification_id`	`INT`	PK / `IDENTITY(1,1)`	資格 ID(自動採番)
`qualification_name`	`NVARCHAR(100)`	`NOT NULL`	資格名
`vendor_name`	`NVARCHAR(100)`	`NULL`	実施団体・ベンダー
`category_name`	`NVARCHAR(50)`	`NULL`	分野
`level_name`	`NVARCHAR(50)`	`NULL`	レベル(無い資格は NULL)
`is_active`	`BIT`	`NOT NULL` / 既定 `1`	追加フォームの選択肢に出すか

CREATE TABLE qualifications (
    qualification_id   INT IDENTITY(1,1) PRIMARY KEY,
    qualification_name NVARCHAR(100) NOT NULL,
    vendor_name        NVARCHAR(100) NULL,
    category_name      NVARCHAR(50)  NULL,
    level_name         NVARCHAR(50)  NULL,
    is_active          BIT NOT NULL DEFAULT 1
);

3. `employee_qualifications`(中間テーブル=社員の保有資格)

「どの社員が・どの資格を・いつ取得し・証書番号は何か」という、保有(社員と資格の関係)ごと の情報です。

列名	型	制約	説明
`employee_qualification_id`	`INT`	PK / `IDENTITY(1,1)`	保有 ID(自動採番)
`employee_id`	`INT`	`NOT NULL` / FK → `employees`	どの社員か
`qualification_id`	`INT`	`NOT NULL` / FK → `qualifications`	どの資格か
`acquired_date`	`DATE`	`NULL`	取得日(R3。未記録は NULL)
`certificate_no`	`NVARCHAR(50)`	`NULL`	証書番号・認定番号(R4。社員×資格ごとに固有。無い場合は NULL)
(制約)	—	`UNIQUE(employee_id, qualification_id)`	同じ社員に同じ資格を二重登録させない(R5)

CREATE TABLE employee_qualifications (
    employee_qualification_id INT IDENTITY(1,1) PRIMARY KEY,
    employee_id      INT  NOT NULL,
    qualification_id INT  NOT NULL,
    acquired_date    DATE NULL,
    certificate_no   NVARCHAR(50) NULL,

    CONSTRAINT fk_eq_employee
        FOREIGN KEY (employee_id)      REFERENCES employees(employee_id),
    CONSTRAINT fk_eq_qualification
        FOREIGN KEY (qualification_id) REFERENCES qualifications(qualification_id),
    CONSTRAINT uq_employee_qualification
        UNIQUE (employee_id, qualification_id)
);

4. 初期データ(サンプル)

資格マスタは事前に登録しておきます。保有資格も動作確認用にいくつか入れておきます。

-- 資格マスタ(qualification_id は IDENTITY で 1〜8)
INSERT INTO qualifications (qualification_name, vendor_name, category_name, level_name) VALUES
    (N'Oracle Master Silver SQL',        N'Oracle',    N'Database',    N'Silver'),
    (N'Oracle Master Bronze DBA',        N'Oracle',    N'Database',    N'Bronze'),
    (N'基本情報技術者試験',               N'IPA',       N'国家資格',     NULL),
    (N'応用情報技術者試験',               N'IPA',       N'国家資格',     NULL),
    (N'Azure Fundamentals',              N'Microsoft', N'Cloud',       N'Fundamentals'),
    (N'AWS Certified Cloud Practitioner', N'AWS',       N'Cloud',       N'Foundational'),
    (N'Java Silver',                     N'Oracle',    N'Programming', N'Silver'),
    (N'CCNA',                            N'Cisco',     N'Network',     NULL);

-- 社員の保有資格(employee_id は 1001〜1010 の範囲、qualification_id は 1〜8)
INSERT INTO employee_qualifications (employee_id, qualification_id, acquired_date, certificate_no) VALUES
    (1001, 1, '2025-05-15', N'ORA-SQL-1001'),
    (1001, 3, '2024-10-20', NULL),               -- 証書番号なしの例(NULL 可)
    (1002, 1, '2025-05-15', N'ORA-SQL-1002'),
    (1002, 5, '2025-06-01', N'MS-AZ900-22087'),
    (1003, 4, '2023-11-10', NULL),
    (1004, 6, '2024-08-01', N'LPI-LV1-55512'),
    (1005, 7, '2025-02-20', N'CISCO-CCNA-90031');

やり直したいときは DELETE FROM employee_qualifications; → DELETE FROM qualifications; の順で消します (子テーブルから先に消す)。件数は環境で変わって構いません。

5. このスキーマ設計のポイント(自分の設計と見比べる観点)

工程 2 で作った自分たちのスキーマと、次の点を見比べてください。

多対多 → 中間テーブル:1 人が複数資格を持ち、1 資格を複数人が持つ。employee_qualifications で 2 本の 1 対多 に分解している。
重複防止(R5)は DB 制約で:UNIQUE(employee_id, qualification_id)。アプリの事前チェックだけに頼らず、DB 側でも二重登録を止める。
中間テーブルの主キーには複数の正解がある:ここでは代理キー(employee_qualification_id)を主キーにし、重複防止は UNIQUE で別に担保している。複合キー(PRIMARY KEY(employee_id, qualification_id))で設計しても正解で、その場合は主キー自身が重複を防ぐので UNIQUE は不要になる。代理キーにしたのは、第 23〜30 章で身につけた「どの行も int の id を 1 本持ち、id 1 つで取得・更新・削除する」CRUD パターンと揃えるため(保有 1 件の削除も employee_qualification_id 1 つで書ける)。どちらも実務でよく使われる設計なので、自分が複合キーで設計していても間違いではない。
取得日・証書番号は中間テーブル側:同じ資格でも「いつ取ったか」「証書番号」は 人ごとに違う=保有の属性。資格マスタには置かない。
NULL を許容する列:取得日・証書番号・レベルなどは「無いことがある」→ NULL 可。実装では空欄を DBNull として渡す(第 30 章の作法)。

6. 列名と C# プロパティの対応(実装の目安)

クラス設計(工程 3)・実装(工程 4)で、列とプロパティの名前・型をそろえる目安です。

qualifications → Qualification クラス

列(SQL)	プロパティ(C#)	型
`qualification_id`	`QualificationId`	`int`
`qualification_name`	`QualificationName`	`string`
`vendor_name`	`VendorName`	`string`
`category_name`	`CategoryName`	`string`
`level_name`	`LevelName`	`string`
`is_active`	`IsActive`	`bool`

employee_qualifications → EmployeeQualification クラス

列(SQL)	プロパティ(C#)	型
`employee_qualification_id`	`EmployeeQualificationId`	`int`
`employee_id`	`EmployeeId`	`int`
`qualification_id`	`QualificationId`	`int`
`acquired_date`	`AcquiredDate`	`DateTime?`
`certificate_no`	`CertificateNo`	`string`

一覧表示では、employee_qualifications に資格名などは入っていないので、qualifications と JOIN して qualification_name などを一緒に取り出します(第 28〜29 章の社員×部署と同じ要領)。

7. 設計例:クラス図とクラス(行き詰まったとき用)

これは「行き詰まったとき」の参考です

全チームの設計が終わったので、設計例を公開します。ここに載せるのは クラスの形(設計) までです。 Repository のメソッドの中身(SQL・ADO.NET)と Controller は載せていません。そこは皆さんが実装する部分です(第 28〜30 章とまったく同じ作法で書けます)。 View(Razor)は、期日が近づいてきたので §7-5 に追加公開 しました。自分たちの設計(自設計のまま進めているチームを含む)と見比べる材料に使ってください。クラス名・プロパティ名は、皆さんの設計に合わせて読み替えて構いません。

7-1. クラス図(全体の俯瞰)

読み方:--> は「持っている(参照する)」、..> は「使う(呼び出す)」関係です。 Employee は第 28〜30 章で作った既存のクラスをそのまま再利用します。

7-2. Model クラス(資格・保有資格)

資格マスタの 1 行を表すクラスです。

1
namespace MvcEmployeeApp.Models;
2

3
// 資格マスタ(qualifications)の 1 行
4
public class Qualification
5
{
6
    public int QualificationId { get; set; }
7
    public string QualificationName { get; set; }
8
    public string VendorName { get; set; }
9
    public string CategoryName { get; set; }
10
    public string LevelName { get; set; }
11
    public bool IsActive { get; set; }
12
}

社員の保有資格(中間テーブルの 1 行)を表すクラスです。一覧表示のため、資格マスタ側の情報も持たせています。

1
namespace MvcEmployeeApp.Models;
2

3
// 中間テーブル(employee_qualifications)の 1 行 + 一覧表示に必要な資格情報(JOIN 結果)
4
public class EmployeeQualification
5
{
6
    public int EmployeeQualificationId { get; set; }
7
    public int EmployeeId { get; set; }
8
    public int QualificationId { get; set; }
9

10
    // JOIN で qualifications 側から持ってくる表示用
11
    public string QualificationName { get; set; }
12
    public string VendorName { get; set; }
13
    public string CategoryName { get; set; }
14
    public string LevelName { get; set; }
15

16
    public DateTime? AcquiredDate { get; set; }
17
    public string CertificateNo { get; set; }
18
}

EmployeeQualification に資格名などの表示用プロパティがあるのは、一覧表示で qualifications と JOIN した結果をまとめて受け取るためです(§6・第 28〜29 章の社員×部署と同じ)。

7-3. ViewModel(社員詳細画面に渡す入れ物)

1 つの社員詳細画面で「社員の基本情報」「保有資格の一覧」「追加フォームの選択肢」「追加フォームの入力」をまとめて扱うため、ViewModel に集約します。

1
namespace MvcEmployeeApp.Models;
2

3
public class EmployeeDetailsViewModel
4
{
5
    // 表示用
6
    public Employee Employee { get; set; }
7
    public List<EmployeeQualification> EmployeeQualifications { get; set; }
8
    public List<Qualification> Qualifications { get; set; }  // 追加フォームの選択肢
9

10
    // 追加フォームの入力(POST で受け取る)
11
    public int EmployeeId { get; set; }
12
    public int QualificationId { get; set; }
13
    public DateTime? AcquiredDate { get; set; }
14
    public string CertificateNo { get; set; }
15
}

7-4. Repository(メソッドの形だけ)

ここは メソッドの名前・引数・戻り値だけ を示します。 中身(SQL と ADO.NET)は皆さんが実装 してください(第 28〜30 章の EmployeeRepository とまったく同じ書き方です)。

QualificationRepository(資格マスタ / DBアクセス)
  - GetAllActive() : List<Qualification>
      … 追加フォームの選択肢にする資格一覧(is_active = 1)を取得

EmployeeQualificationRepository(保有資格 / DBアクセス)★本演習の中心
  - GetByEmployeeId(int employeeId) : List<EmployeeQualification>
      … R1/R6 指定社員の保有資格を qualifications と JOIN して取得
  - Exists(int employeeId, int qualificationId) : bool
      … R5 同じ社員×同じ資格がすでにあるか(追加前のアプリ側チェック)
  - Insert(EmployeeQualification eq) : int
      … R7 追加(取得日・証書番号が空なら DBNull を渡す)
  - Delete(int employeeQualificationId) : int
      … R8 削除(主キー employee_qualification_id で対象を 1 件に限定)

Delete の引数が employee_qualification_id 1 つで済むのは、中間テーブルに代理キーを置いた設計の利点です(§5 参照)。複合キー(PRIMARY KEY(employee_id, qualification_id))で設計したチームは、削除の引数が 社員 ID ＋資格 ID の 2 つ になります。自分たちの設計に合わせて読み替えてください。

7-5. View(社員詳細画面 `Views/Employees/Details.cshtml`)

期日が近づいてきたので、社員詳細画面の View も公開します。これは ViewModel(7-3)を受け取って、基本情報の表示(R6)・保有資格の一覧と削除フォーム(R1・R8)・追加フォーム(R7) を 1 画面に並べたものです。 この View に値を渡す Controller(Details / Add / Delete アクション)と、Repository のメソッドの中身は、引き続き皆さんが実装 します(下の asp-controller / asp-action の名前が、作るべきアクションの目安になります)。

1
@model MvcEmployeeApp.Models.EmployeeDetailsViewModel
2

3
<h1>社員詳細</h1>
4

5
@* 1) 社員の基本情報を表示(R6) *@
6
<dl>
7
    <dt>社員ID</dt><dd>@Model.Employee.EmployeeId</dd>
8
    <dt>氏名</dt><dd>@Model.Employee.FullName</dd>
9
    <dt>部署</dt><dd>@Model.Employee.DepartmentName</dd>
10
</dl>
11

12
<h2>保有資格</h2>
13

14
@* 2) 保有資格の一覧(R1)。各行の末尾に削除フォーム(R8) *@
15
<table class="table">
16
    <thead>
17
        <tr>
18
            <th>資格名</th><th>実施団体</th><th>分野</th><th>レベル</th>
19
            <th>取得日</th><th>証書番号</th><th></th>
20
        </tr>
21
    </thead>
22
    <tbody>
23
        @foreach (var eq in Model.EmployeeQualifications)
24
        {
25
            <tr>
26
                <td>@eq.QualificationName</td>
27
                <td>@eq.VendorName</td>
28
                <td>@eq.CategoryName</td>
29
                <td>@eq.LevelName</td>
30
                <td>@(eq.AcquiredDate.HasValue ? eq.AcquiredDate.Value.ToString("yyyy/MM/dd") : "")</td>
31
                <td>@eq.CertificateNo</td>
32
                <td>
33
                    @* 削除は POST。主キーで 1 件を特定する(第30章の削除と同じ) *@
34
                    <form asp-controller="EmployeeQualifications" asp-action="Delete" method="post">
35
                        <input type="hidden" name="employeeQualificationId" value="@eq.EmployeeQualificationId" />
36
                        <input type="hidden" name="employeeId" value="@Model.Employee.EmployeeId" />
37
                        <button type="submit" onclick="return confirm('削除しますか?');">削除</button>
38
                    </form>
39
                </td>
40
            </tr>
41
        }
42
    </tbody>
43
</table>
44

45
<h2>資格を追加</h2>
46

47
@* 3) 資格を追加(R7)。選択肢は ViewModel の Qualifications から作る *@
48
<form asp-controller="EmployeeQualifications" asp-action="Add" method="post">
49
    @* 追加後にどの社員の詳細へ戻るかを伝えるため、社員IDを hidden で持つ *@
50
    <input type="hidden" asp-for="EmployeeId" value="@Model.Employee.EmployeeId" />
51

52
    <label>資格</label>
53
    <select asp-for="QualificationId"
54
            asp-items="@(new SelectList(Model.Qualifications, "QualificationId", "QualificationName"))">
55
        <option value="">選択してください</option>
56
    </select>
57

58
    <label>取得日</label>
59
    <input asp-for="AcquiredDate" type="date" />
60

61
    <label>証書番号</label>
62
    <input asp-for="CertificateNo" />
63

64
    <button type="submit">追加</button>
65
</form>
66

67
<p><a asp-action="Index">一覧へ戻る</a></p>

社員一覧画面 Views/Employees/Index.cshtml には、各行に 詳細への動線 を 1 つ足します(一覧の @foreach の変数名に合わせて読み替えてください)。

<a asp-action="Details" asp-route-id="@emp.EmployeeId">詳細</a>

ルーティングは既定の {controller}/{action}/{id?} のままで /Employees/Details/1001 が通ります(第 30 章の Edit/{id} と同じ)。 table / btn などの Bootstrap クラスで見た目を整えたい場合は付録 K「最低限の HTML」K-5 を参照(機能が先、デザインは後)。

付録O 最終演習課題 DB スキーマ仕様書(保有資格管理)