Changes between Initial Version and Version 1 of ddlScriptVer1.sql

Timestamp:

11/20/24 17:34:15 (3 days ago)

Author:

211101

Comment:

--

ddlScriptVer1.sql

@@ +1 @@
+
+{{{#!sql
+-- Create USER table
+CREATE TABLE USER (
+    user_id SERIAL PRIMARY KEY,
+    user_name VARCHAR(50) NOT NULL,
+    email VARCHAR(100) NOT NULL,
+    password VARCHAR(100) NOT NULL
+);
+
+-- Create TRANSACTION_ACCOUNT table
+CREATE TABLE TRANSACTION_ACCOUNT (
+    transaction_account_id SERIAL PRIMARY KEY,
+    account_name VARCHAR(50),
+    balance DECIMAL(10, 2),
+    user_id INT REFERENCES USER(user_id)
+);
+
+-- Create TRANSACTION table
+CREATE TABLE TRANSACTION (
+    transaction_id SERIAL PRIMARY KEY,
+    transaction_name VARCHAR(100),
+    amount DECIMAL(10, 2) NOT NULL,
+    net_amount DECIMAL(10, 2),
+    day_id INT REFERENCES DAY(day_id),
+    tag_id INT REFERENCES TAG(tag_id)
+);
+
+-- Create TRANSACTION_BREAKDOWN table
+CREATE TABLE TRANSACTION_BREAKDOWN (
+    transaction_breakdown_id SERIAL PRIMARY KEY,
+    transaction_id INT REFERENCES TRANSACTION(transaction_id),
+    transaction_account_id INT REFERENCES TRANSACTION_ACCOUNT(transaction_account_id),
+    spent_amount DECIMAL(10, 2),
+    earned_amount DECIMAL(10, 2)
+);
+
+-- Create YEAR table
+CREATE TABLE YEAR (
+    year_id SERIAL PRIMARY KEY,
+    year_name INT NOT NULL
+);
+
+-- Create MONTH table
+CREATE TABLE MONTH (
+    month_id SERIAL PRIMARY KEY,
+    year_id INT REFERENCES YEAR(year_id),
+    month_name VARCHAR(20) NOT NULL
+);
+
+-- Create DAY table
+CREATE TABLE DAY (
+    day_id SERIAL PRIMARY KEY,
+    month_id INT REFERENCES MONTH(month_id),
+    day_number INT,
+    day_name VARCHAR(20)
+);
+
+-- Create TAG table
+CREATE TABLE TAG (
+    tag_id SERIAL PRIMARY KEY,
+    tag_name VARCHAR(50) NOT NULL
+);
+
+-- Delete tables if they exist
+DROP TABLE IF EXISTS TRANSACTION_BREAKDOWN CASCADE;
+DROP TABLE IF EXISTS TRANSACTION CASCADE;
+DROP TABLE IF EXISTS TRANSACTION_ACCOUNT CASCADE;
+DROP TABLE IF EXISTS DAY CASCADE;
+DROP TABLE IF EXISTS MONTH CASCADE;
+DROP TABLE IF EXISTS YEAR CASCADE;
+DROP TABLE IF EXISTS TAG CASCADE;
+DROP TABLE IF EXISTS USER CASCADE;
+}}}