つくる 要件の分解
第1章の成果物を仕上げる
新しいことは何も出てきません。ここまでに決めたやり方で、残り3つのエンティティに属性を詰めて、第1章の成果物を完成させます。
すでに stores と products は出来上がっています。残っているのは customers、orders、inventories の3つです。
顧客
要件文は「顧客は会員登録してから注文します」だけです。ここから拾えるのは会員登録日くらいですが、名前と連絡先が無いと顧客管理になりません。補います。
| 属性 | 型 |
|---|---|
| id | integer |
| name | text |
| text | |
| joined_on | date |
注文
注文は少し様子が違います。「注文は必ずどこかの店舗で行われます」「顧客は注文します」とあるので、注文は店舗と顧客を指し示すことになります。
この「他のエンティティを指す列」が store_id と customer_id です。指す先を持つのが注文というエンティティの本質なので、これは属性というより関係の表れです。関係そのものは第2章で正面から扱います。ここでは列として置くだけにします。
| 属性 | 型 |
|---|---|
| id | integer |
| store_id | integer |
| customer_id | integer |
| ordered_on | date |
在庫
在庫は「どの店舗にどの商品が何個あるか」でした。店舗と商品の組み合わせごとに1件なので、id を別に立てる必要がありません。store_id と product_id の2つ合わせて1件を決めます。
こういう複数列でできた主キーを複合主キーと呼びます。書き方は次のとおりです。
別題材の図書館で同じ書き方を試すと、次のようになります。
SQL クエリ
CREATE TABLE branch_books (
branch_id integer NOT NULL,
book_id integer NOT NULL,
quantity integer NOT NULL,
PRIMARY KEY (branch_id, book_id)
);列の後ろに PRIMARY KEY を書くのではなく、最後に独立した行として書くのがポイントです。これで「この2列の組み合わせは重複しない」という約束になり、同じ店舗の同じ商品の在庫が2行できてしまう事故を防げます。
いま持っているもの
これで、5つのエンティティと、それぞれの属性が揃います。ER図もキーの制約もまだですが、要件文という日本語から動くテーブル定義までは辿り着きました。第2章では、これらの間に線を引いていきます。
作り直しと列数の確認
空の仮テーブルは DROP TABLE で消してから、改めて CREATE TABLE で定義できます。
SQL クエリ
DROP TABLE reading_rooms;
CREATE TABLE reading_rooms (id integer PRIMARY KEY, name text NOT NULL);表ごとの列数も、最初に見た GROUP BY と count(*) で確認できます。
SQL クエリ
SELECT table_name, count(*) AS column_count
FROM information_schema.columns
WHERE table_schema = 'public'
GROUP BY table_name
ORDER BY table_name;演習の作り直しと確認は用意済みです。受講者は在庫を識別する属性と複合主キーを補います。
手を動かす
残り3つを完成させて、5テーブルの姿を一覧で確かめます。
customers、orders、inventories はまだ id しかない、または存在しません。いったん DROP TABLE してから、次の定義で作り直してください。
customers—id integer PRIMARY KEY、name text NOT NULL、email text NOT NULL、joined_on date NOT NULLorders—id integer PRIMARY KEY、store_id integer NOT NULL、customer_id integer NOT NULL、ordered_on date NOT NULLinventories—store_id integer NOT NULL、product_id integer NOT NULL、quantity integer NOT NULL、複合主キーは(store_id, product_id)
作り終えたら、information_schema から table_schema が 'public' のテーブルについて table_name と列数を column_count という名前で、table_name の順に取り出してください。
削除処理と顧客・注文の定義は用意済みです。在庫の3属性と、組み合わせで識別する主キーを定義します。
ひな形の「ここに学ぶ部分を追記する」を SQL に置き換えます。模範解答では 4 行を補います。確認用の SQL は用意してあります。
テーブル構造
CREATE TABLE stores (
id integer PRIMARY KEY,
name text NOT NULL,
pref text NOT NULL,
opened_on date
);
CREATE TABLE products (
id integer PRIMARY KEY,
name text NOT NULL,
price integer NOT NULL,
category text NOT NULL,
is_active boolean NOT NULL
);
CREATE TABLE customers (id integer PRIMARY KEY);
CREATE TABLE orders (id integer PRIMARY KEY);
CREATE TABLE inventories (id integer PRIMARY KEY);
期待される出力
| table_name | column_count |
|---|---|
| customers | 4 |
| inventories | 3 |
| orders | 4 |
| products | 5 |
| stores | 4 |
ヒント
まだ開いていません
付箋は 2 枚。下の段ほど答えに近づきます
ヒント 2答えに近い