Python中級

中級 Pythonで学ぶデータベース設計｜練習問題編

 

導入

データベース設計は、アプリケーションのパフォーマンスや拡張性に直接影響を与える重要な要素です。本記事では、中級者向けに、特定のビジネスシナリオに基づいたデータベース設計の実践的なアプローチを紹介します。具体的には、オンラインストアの顧客管理システムを題材に、効率的なデータ構造とクエリの設計について考察します。

教科書レベルの解説（データベース設計）

重要な概念の整理

顧客管理システムでは、顧客情報、注文履歴、商品情報が密接に関連しています。このような場合、正規化を行うことでデータの冗長性を減らし、データ整合性を保つことが求められます。また、パフォーマンスを考慮したインデックスの設計も重要です。特に、頻繁に検索されるカラムにはインデックスを付与することで、クエリの応答速度を向上させることができます。

コード例（Python）


import sqlite3

# データベース接続
conn = sqlite3.connect('online_store.db')
cursor = conn.cursor()

# テーブルの作成
cursor.execute('''
CREATE TABLE IF NOT EXISTS customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date TEXT,
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
)
''')

# データの挿入
cursor.execute('''
INSERT INTO customers (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com')
''')

cursor.execute('''
INSERT INTO orders (customer_id, order_date) VALUES
(1, '2023-10-01'),
(2, '2023-10-02')
''')

# データの取得
cursor.execute('''
SELECT c.name, o.order_date FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
''')

for row in cursor.fetchall():
    print(row)

# 接続のクローズ
conn.commit()
conn.close()

コードの行ごとの解説

  1. 最初にSQLiteデータベースに接続し、カーソルを作成します。
  2. 顧客情報を格納するテーブルを作成し、主キーを設定します。
  3. 注文情報を格納するテーブルを作成し、顧客IDを外部キーとして設定します。
  4. 顧客情報と注文情報をそれぞれ挿入します。
  5. 顧客名と注文日を結合して取得し、結果を表示します。
  6. 最後に、データベース接続をクローズします。

練習問題編

以下の問題に取り組んで、データベース設計の理解を深めましょう。

  1. 顧客テーブルに電話番号を追加する場合、どのようにテーブルを変更しますか？
    2. 
ALTER TABLE customers ADD COLUMN phone TEXT;
  2. 同じ顧客が複数回注文をする場合、どのように注文テーブルを設計しますか？

    3. 顧客IDを外部キーとして保持し、各注文にユニークなIDを付与することで、同じ顧客からの複数の注文を管理できます。

  3. インデックスを追加する理由と、その効果について説明してください。

    4. インデックスは検索を高速化します。特に、顧客名やメールアドレスのように頻繁に検索されるカラムにインデックスを追加することで、クエリのパフォーマンスが向上します。

  4. データの整合性を保つために、どのような制約を設けるべきですか？

    5. 顧客テーブルのメールアドレスにユニーク制約を設けることで、同一のメールアドレスを持つ顧客が存在しないようにします。

  5. 顧客が注文をキャンセルした場合、どのようにデータを更新しますか？

    6. 注文テーブルにキャンセルフラグを追加し、キャンセルされた注文にはそのフラグを設定することで、履歴を保持したまま管理できます。

まとめ

  • データベース設計において、正規化とインデックス設計はパフォーマンスに大きな影響を与えます。
  • 実際の業務シナリオに基づいた設計が、効率的なシステムを構築する鍵となります。
RELATED POST