chatGPTとプログラミング　データベース

概要

・Webシステムの構築のための１つにデータベース構築がある。

・別記事にて記載させていただいた通り、横着な人な私にとって難所であったデータベースの構築がchatGPTなどなどの生成系AIを利用することで楽に構築できてしまう

・特に、ミスって進まないとか、構築用のツールを別途また調べたりするコストをかけなくていいというのが精神衛生上いい。ダメならchatGPTにしつこく聞けばいいだけだ。

・まずはそのとっかかりとして、サンプルデータを用意するところを目指す。

参考文献

・IPA様の令和3年午後試験Ⅱ 不動産の商談システムより拝借致しました。

・やはり、プロ集団が集まってあーでもないこーでもないと磨かれた資料であるテスト問題は勉強に役つ。

ソースコード

以下、ソースコード　DDL文

-- 大エリアテーブル
CREATE TABLE LargeArea (
    LargeAreaCode INT PRIMARY KEY, -- 大エリアコード
    LargeAreaName VARCHAR(255) NOT NULL -- 大エリア名
);

-- 小エリアテーブル
CREATE TABLE SmallArea (
    SmallAreaCode INT PRIMARY KEY, -- 小エリアコード
    SmallAreaName VARCHAR(255) NOT NULL -- 小エリア名
);

-- 住所テーブル
CREATE TABLE Address (
    AddressCode INT PRIMARY KEY, -- 住所コード
    PrefectureName VARCHAR(255) NOT NULL, -- 都道府県名
    CityName VARCHAR(255) NOT NULL, -- 市区町村名
    Block VARCHAR(255), -- 丁目
    SmallAreaCode INT, -- 小エリアコード
    FOREIGN KEY (SmallAreaCode) REFERENCES SmallArea(SmallAreaCode)
);

-- 鉄道事業者テーブル
CREATE TABLE RailwayOperator (
    RailwayOperatorCode INT PRIMARY KEY, -- 鉄道事業者コード
    RailwayOperatorName VARCHAR(255) NOT NULL -- 鉄道事業者名
);

-- 沿線テーブル
CREATE TABLE RailwayLine (
    RailwayCode INT PRIMARY KEY, -- 沿線コード
    RailwayName VARCHAR(255) NOT NULL, -- 沿線名
    RailwayOperatorCode INT, -- 鉄道事業者コード
    FOREIGN KEY (RailwayOperatorCode) REFERENCES RailwayOperator(RailwayOperatorCode)
);

-- 駅テーブル
CREATE TABLE Station (
    RailwayCode INT, -- 沿線コード
    StationCode INT PRIMARY KEY, -- 駅コード
    StationName VARCHAR(255) NOT NULL, -- 駅名
    AddressCode INT, -- 住所コード
    FOREIGN KEY (RailwayCode) REFERENCES RailwayLine(RailwayCode),
    FOREIGN KEY (AddressCode) REFERENCES Address(AddressCode)
);

-- 担当者テーブル
CREATE TABLE PersonInCharge (
    PersonInChargeCode INT PRIMARY KEY, -- 担当者コード
    SalesCenterCode INT, -- 販売センタコード
    Name VARCHAR(255) NOT NULL, -- 氏名
    FOREIGN KEY (SalesCenterCode) REFERENCES SalesCenter(SalesCenterCode)
);

-- 販売センタテーブル
CREATE TABLE SalesCenter (
    SalesCenterCode INT PRIMARY KEY, -- 販売センタコード
    SalesCenterName VARCHAR(255) NOT NULL, -- 販売センタ名
    LocationAddressCode INT, -- 所在地住所コード
    OtherAddress VARCHAR(255), -- 番地その他
    FOREIGN KEY (LocationAddressCode) REFERENCES Address(AddressCode)
);

-- 物件テーブル
CREATE TABLE Property (
    PropertyCode INT PRIMARY KEY, -- 物件コード
    PropertyName VARCHAR(255) NOT NULL, -- 物件名
    SalesCenterCode INT, -- 販売センタコード
    LocationAddressCode INT, -- 所在地住所コード
    OtherAddress VARCHAR(255), -- 番地その他
    FOREIGN KEY (SalesCenterCode) REFERENCES SalesCenter(SalesCenterCode),
    FOREIGN KEY (LocationAddressCode) REFERENCES Address(AddressCode)
);

-- 最寄り駅テーブル
CREATE TABLE NearestStation (
    PropertyCode INT, -- 物件コード
    RailwayCode INT, -- 沿線コード
    StationCode INT, -- 駅コード
    WalkingMinutes INT, -- 徒歩分数
    PRIMARY KEY (PropertyCode, RailwayCode, StationCode),
    FOREIGN KEY (PropertyCode) REFERENCES Property(PropertyCode),
    FOREIGN KEY (RailwayCode, StationCode) REFERENCES Station(RailwayCode, StationCode)
);

-- 販売期テーブル
CREATE TABLE SalesPeriod (
    PropertyCode INT, -- 物件コード
    SalesPeriodCode INT PRIMARY KEY, -- 販売期コード
    SalesPeriodName VARCHAR(255) NOT NULL, -- 販売期名
    SalesStartDate DATE NOT NULL, -- 販売開始日
    SalesEndDate DATE NOT NULL, -- 販売終了日
    FOREIGN KEY (PropertyCode) REFERENCES Property(PropertyCode)
);

-- 棟テーブル
CREATE TABLE Building (
    PropertyCode INT, -- 物件コード
    BuildingCode INT PRIMARY KEY, -- 棟コード
    BuildingName VARCHAR(255) NOT NULL, -- 棟名称
    FOREIGN KEY (PropertyCode) REFERENCES Property(PropertyCode)
);

-- 住戸テーブル
CREATE TABLE Dwelling (
    PropertyCode INT, -- 物件コード
    BuildingCode INT, -- 棟コード
    DwellingCode INT PRIMARY KEY, -- 住戸コード
    SalePrice DECIMAL(10, 2) NOT NULL, -- 販売価格
    SalesPeriodCode INT, -- 販売期コード
    Layout VARCHAR(255), -- 間取り
    FloorArea DECIMAL(8, 2) NOT NULL, -- 専有面積
    FOREIGN KEY (PropertyCode, BuildingCode) REFERENCES Building(PropertyCode, BuildingCode),
    FOREIGN KEY (SalesPeriodCode) REFERENCES SalesPeriod(SalesPeriodCode)
);

-- 顧客テーブル
CREATE TABLE Customer (
    CustomerCode INT PRIMARY KEY, -- 顧客コード
    Name VARCHAR(255) NOT NULL, -- 氏名
    AddressCode INT, -- 住所コード
    Email VARCHAR(255), -- メールアドレス
    PhoneNumber VARCHAR(20), -- 電話番号
    Birthdate DATE, -- 生年月日
    MatchingCustomerCode INT -- 名寄せ先顧客コード
);

-- 商談テーブル
CREATE TABLE Negotiation (
    CustomerCode INT, -- 顧客コード
    PropertyCode INT, -- 物件コード
    NegotiationStatus VARCHAR(50) NOT NULL, -- 商談ステータス
    LatestContactDateTime DATETIME NOT NULL, -- 最新接触日時
    OrderProbability DECIMAL(5, 2), -- 受注確度
    DesiredArea DECIMAL(8, 2), -- 希望面積
    PurchaseBudget DECIMAL(10, 2), -- 購入予算
    PRIMARY KEY (CustomerCode, PropertyCode),
    FOREIGN KEY (CustomerCode) REFERENCES Customer(CustomerCode),
    FOREIGN KEY (PropertyCode) REFERENCES Property(PropertyCode)
);

-- 追客テーブル
CREATE TABLE FollowUp (
    CustomerCode INT, -- 顧客コード
    PropertyCode INT, -- 物件コード
    Sequence INT, -- 連番
    FollowUpType VARCHAR(255), -- 追客区分
    ContactDateTime DATETIME, -- 接触日時
    FollowUpCategory VARCHAR(255), -- 追客種別
    PRIMARY KEY (CustomerCode, PropertyCode, Sequence),
    FOREIGN KEY (CustomerCode) REFERENCES Customer(CustomerCode),
    FOREIGN KEY (PropertyCode) REFERENCES Property(PropertyCode)
);

-- 受付テーブル
CREATE TABLE Reception (
    CustomerCode INT, -- 顧客コード
    PropertyCode INT, -- 物件コード
    Sequence INT, -- 連番
    ScheduledStartDate DATE, -- 開始予定日
    ScheduledEndDate DATE, -- 終了予定日
    PRIMARY KEY (CustomerCode, PropertyCode, Sequence),
    FOREIGN KEY (CustomerCode) REFERENCES Customer(CustomerCode),
    FOREIGN KEY (PropertyCode) REFERENCES Property(PropertyCode)
);

-- 実施テーブル
CREATE TABLE Implementation (
    CustomerCode INT, -- 顧客コード
    PropertyCode INT, -- 物件コード
    Sequence INT, -- 連番
    ResultMemo TEXT, -- 実施結果メモ
    PRIMARY KEY (CustomerCode, PropertyCode, Sequence),
    FOREIGN KEY (CustomerCode) REFERENCES Customer(CustomerCode),
    FOREIGN KEY (PropertyCode) REFERENCES Property(PropertyCode)
);

-- 応対テーブル
CREATE TABLE Correspondence (
    CustomerCode INT, -- 顧客コード
    PropertyCode INT, -- 物件コード
    Sequence INT, -- 連番
    CorrespondenceContent TEXT, -- 応対内容
    PRIMARY KEY (CustomerCode, PropertyCode, Sequence),
    FOREIGN KEY (CustomerCode) REFERENCES Customer(CustomerCode),
    FOREIGN KEY (PropertyCode) REFERENCES Property(PropertyCode)
);

-- 契約テーブル
CREATE TABLE Contract (
    CustomerCode INT, -- 顧客コード
    PropertyCode INT, -- 物件コード
    BuildingCode INT, -- 棟コード
    DwellingCode INT, -- 住戸コード
    ContractStatus VARCHAR(50), -- 契約ステータス
    DealPrice DECIMAL(10, 2), -- 成約価格
    PRIMARY KEY (CustomerCode, PropertyCode, BuildingCode, DwellingCode),
    FOREIGN KEY (CustomerCode) REFERENCES Customer(CustomerCode),
    FOREIGN KEY (PropertyCode, BuildingCode, DwellingCode) REFERENCES Dwelling(PropertyCode, BuildingCode, DwellingCode)
);

以下、サンプルデータ

-- 大エリアテーブルのサンプルデータ
INSERT INTO LargeArea (LargeAreaCode, LargeAreaName) VALUES
(1, '東京都'), 
(2, '大阪府'), 
(3, '福岡県'), 
(4, '北海道'), 
(5, '京都府');

-- 小エリアテーブルのサンプルデータ
INSERT INTO SmallArea (SmallAreaCode, SmallAreaName) VALUES
(101, '渋谷区'), 
(102, '中央区'), 
(103, '博多区'), 
(104, '札幌市'), 
(105, '伏見区');

-- 住所テーブルのサンプルデータ
INSERT INTO Address (AddressCode, PrefectureName, CityName, Block, SmallAreaCode) VALUES
(1001, '東京都', '渋谷区', '1-1-1', 101),
(1002, '大阪府', '中央区', '2-2-2', 102),
(1003, '福岡県', '博多区', '3-3-3', 103),
(1004, '北海道', '札幌市', '4-4-4', 104),
(1005, '京都府', '伏見区', '5-5-5', 105);

-- 鉄道事業者テーブルのサンプルデータ
INSERT INTO RailwayOperator (RailwayOperatorCode, RailwayOperatorName) VALUES
(201, 'JR東日本'), 
(202, 'JR西日本'), 
(203, '西鉄'), 
(204, 'JR北海道'), 
(205, '阪急電鉄');

-- 沿線テーブルのサンプルデータ
INSERT INTO RailwayLine (RailwayCode, RailwayName, RailwayOperatorCode) VALUES
(301, '山手線', 201),
(302, '大阪環状線', 202),
(303, '天神大牟田線', 203),
(304, '函館本線', 204),
(305, '阪急宝塚線', 205);

-- 駅テーブルのサンプルデータ
INSERT INTO Station (RailwayCode, StationCode, StationName, AddressCode) VALUES
(301, 401, '渋谷駅', 1001),
(302, 402, '大阪駅', 1002),
(303, 403, '博多駅', 1003),
(304, 404, '札幌駅', 1004),
(305, 405, '伏見駅', 1005);

-- 担当者テーブルのサンプルデータ
INSERT INTO Staff (StaffCode, SalesCenterCode, Name) VALUES
(501, 601, '田中太郎'),
(502, 602, '山田花子'),
(503, 603, '佐藤一郎'),
(504, 604, '鈴木美香'),
(505, '605', '中村健太');

-- 販売センタテーブルのサンプルデータ
INSERT INTO SalesCenter (SalesCenterCode, SalesCenterName, AddressCode, OtherAddress) VALUES
(601, '渋谷センター', 1001, 'センタービル1F'),
(602, '大阪センター', 1002, '中央ビル2F'),
(603, '博多センター', 1003, '博多ビル3F'),
(604, '札幌センター', 1004, '札幌ビル4F'),
(605, '京都センター', 1005, '京都ビル5F');

-- 物件テーブルのサンプルデータ
INSERT INTO Property (PropertyCode, PropertyName, SalesCenterCode, AddressCode, OtherAddress) VALUES
(701, '渋谷物件A', 601, 1001, 'A棟101号室'),
(702, '大阪物件B', 602, 1002, 'B棟202号室'),
(703, '博多物件C', 603, 1003, 'C棟303号室'),
(704, '札幌物件D', 604, 1004, 'D棟404号室'),
(705, '京都物件E', 605, 1005, 'E棟505号室');

-- 最寄り駅テーブルのサンプルデータ
INSERT INTO NearestStation (PropertyCode, RailwayCode, StationCode, WalkingMinutes) VALUES
(701, 301, 401, 5),
(702, 302, 402, 10),
(703, 303, 403, 3),
(704, 304, 404, 8),
(705, 305, 405, 15);

-- 販売期テーブルのサンプルデータ
INSERT INTO SalesPeriod (PropertyCode, SalesPeriodCode, SalesPeriodName, SalesStartDate, SalesEndDate) VALUES
(701, 801, '春の販売', '2024-03-01', '2024-05-31'),
(702, 802, '夏の販売', '2024-06-01', '2024-08-31'),
(703, 803, '秋の販売', '2024-09-01', '2024-11-30'),
(704, 804, '冬の販売', '2024-12-01', '2025-02-28'),
(705, 805, '年末年始セール', '2025-01-01', '2025-01-10');

-- 棟テーブルのサンプルデータ
INSERT INTO Building (PropertyCode, BuildingCode, BuildingName) VALUES
(701, 901, 'A棟'),
(702, 902, 'B棟'),
(703, 903, 'C棟'),
(704, 904, 'D棟'),
(705, 905, 'E棟');

-- 住戸テーブルのサンプルデータ
INSERT INTO Unit (PropertyCode, BuildingCode, UnitCode, SalePrice, SalesPeriodCode, Layout, FloorArea) VALUES
(701, 901, 1001, 10000000.00, 801, '2LDK', 80.50),
(702, 902, 1002, 15000000.00, 802, '3LDK', 110.75),
(703, 903, 1003, 8000000.00, 803, '1K', 45.20),
(704, 904, 1004, 12000000.00, 804, '2DK', 70.80),
(705, 905, 1005, 18000000.00, 805, '4LDK', 150.25);

-- 顧客テーブルのサンプルデータ
INSERT INTO Customer (CustomerCode, Name, AddressCode, Email, PhoneNumber, BirthDate, MergedCustomerCode) VALUES
(1001, '田中太郎', 1001, 'tanaka@example.com', '090-1234-5678', '1990-05-15', NULL),
(1002, '山田花子', 1002, 'yamada@example.com', '080-9876-5432', '1985-08-20', NULL),
(1003, '佐藤一郎', 1003, 'sato@example.com', '070-6543-2109', '1992-02-10', NULL),
(1004, '鈴木美香', 1004, 'suzuki@example.com', '080-1111-2222', '1980-11-28', NULL),
(1005, '中村健太', 1005, 'nakamura@example.com', '090-9999-8888', '1988-07-05', NULL);

-- 商談テーブルのサンプルデータ
INSERT INTO Negotiation (CustomerCode, PropertyCode, NegotiationStatus, LatestContactDateTime, OrderProbability, DesiredArea, PurchaseBudget) VALUES
(1001, 701, '興味あり', '2024-03-10 15:30:00', 0.8, 90.00, 12000000.00),
(1002, 702, '資料請求中', '2024-06-05 10:45:00', 0.6, 110.50, 15000000.00),
(1003, 703, '来場予定', '2024-09-20 13:15:00', 0.4, 50.00, 8000000.00),
(1004, 704, '検討中', '2024-12-15 11:00:00', 0.7, 75.80, 12000000.00),
(1005, 705, '即決', '2025-01-05 14:20:00', 0.9, 140.25, 18000000.00);

-- 追客テーブルのサンプルデータ
INSERT INTO FollowUp (CustomerCode, PropertyCode, SequenceNumber, FollowUpCategory, ContactDateTime, FollowUpType) VALUES
(1001, 701, 1, 'フォローアップメール', '2024-03-15 09:30:00', 'メール'),
(1002, 702, 2, '来場案内', '2024-06-10 12:20:00', '訪問'),
(1003, 703, 3, '資料送付', '2024-09-25 16:45:00', '郵送'),
(1004, 704, 4, '電話フォロー', '2025-01-10 08:10:00', '電話'),
(1005, 705, 5, '新年のご挨拶', '2025-01-15 11:30:00', '訪問');

-- 受付テーブルのサンプルデータ
INSERT INTO Reception (CustomerCode, PropertyCode, SequenceNumber, ScheduledStartDate, ScheduledEndDate) VALUES
(1001, 701, 1, '2024-03-20 10:00:00', '2024-03-20 12:00:00'),
(1002, 702, 2, '2024-06-15 14:30:00', '2024-06-15 16:30:00'),
(1003, 703, 3, '2024-09-30 11:00:00', '2024-09-30 13:00:00'),
(1004, 704, 4, '2025-01-15 09:45:00', '2025-01-15 11:45:00'),
(1005, 705, 5, '2025-01-20 13:30:00', '2025-01-20 15:30:00');

-- 実施テーブルのサンプルデータ
INSERT INTO Implementation (CustomerCode, PropertyCode, SequenceNumber, ImplementationResultNote) VALUES
(1001, 701, 1, '成功'),
(1002, 702, 2, '成功'),
(1003, 703, 3, '失敗'),
(1004, 704, 4, '成功'),
(1005, 705, 5, '成功');

-- 応対テーブルのサンプルデータ
INSERT INTO Correspondence (CustomerCode, PropertyCode, SequenceNumber, CorrespondenceContent) VALUES
(1001, 701, 1, '電話対応'),
(1002, 702, 2, '訪問対応'),
(1003, 703, 3, 'メール対応'),
(1004, 704, 4, '電話対応'),
(1005, 705, 5, '訪問対応');

-- 契約テーブルのサンプルデータ
INSERT INTO Contract (CustomerCode, PropertyCode, BuildingCode, UnitCode, ContractStatus, DealPrice) VALUES
(1001, 701, 901, 1001, '成約', 10000000.00),
(1002, 702, 902, 1002,

やはり、超絶優秀な方々が作成する試験問題は読みやすく全てにおいて勉強になる。

まとめ

・一旦、データベースの構築まで行うための素材データを用意できた。

・次に実施すべき課題はこれらのデータを利用して、バックエンドとフロントエンドの構築を行うこと。

・最初はDockerなどを利用してローカルで構成を構築したい。

・当該記事のブラッシュアップの課題もある。Dockerまでの手順などを明示したい。

コメント

タイトルとURLをコピーしました