| import sqlite3 |
|
|
| conn = sqlite3.connect('hello_earth_data_2.db') |
| cursor = conn.cursor() |
|
|
| cursor.execute(''' |
| CREATE TABLE IF NOT EXISTS Project ( |
| project_id TEXT PRIMARY KEY, |
| title TEXT, |
| status TEXT, |
| detail_information TEXT, |
| start_date DATETIME, |
| end_date DATETIME |
| ) |
| ''') |
|
|
| cursor.execute(''' |
| CREATE TABLE IF NOT EXISTS Deliverable ( |
| deliverable_id TEXT PRIMARY KEY, |
| project_id TEXT, |
| title TEXT, |
| status TEXT CHECK(status IN ('done', 'ongoing')), |
| risk_level TEXT CHECK(risk_level IN ('green', 'yellow', 'red')), |
| risk_level_rationale TEXT, |
| start_date DATETIME, |
| end_date DATETIME, |
| FOREIGN KEY (project_id) REFERENCES Project(project_id) |
| ) |
| ''') |
|
|
| cursor.execute(''' |
| CREATE TABLE IF NOT EXISTS Expense ( |
| expense_id TEXT PRIMARY KEY, |
| associated_deliverable_id TEXT, |
| seller_name TEXT, |
| seller_address TEXT, |
| seller_phone_number TEXT, |
| buyer_name TEXT, |
| buyer_address TEXT, |
| transaction_date DATETIME, |
| total_payment_amount REAL, |
| expense_description TEXT, |
| status TEXT, |
| FOREIGN KEY (associated_deliverable_id) REFERENCES Deliverable(deliverable_id) |
| ) |
| ''') |
|
|
| cursor.execute(''' |
| CREATE TABLE IF NOT EXISTS Deliverable_Budget ( |
| id TEXT PRIMARY KEY, |
| deliverable_id TEXT, |
| wage REAL, |
| materials REAL, |
| tools_equipment REAL, |
| services REAL, |
| misc REAL, |
| FOREIGN KEY (deliverable_id) REFERENCES Deliverable(deliverable_id) |
| ) |
| ''') |
|
|
| conn.commit() |
| conn.close() |
|
|
| print("SQLite database created with the specified schema.") |
|
|
