Create-db-query.sql

-- Run once
-- Create database My2Cents

CREATE TABLE User_Login(
	UserID INT NOT NULL IDENTITY PRIMARY KEY,
	UserName NVARCHAR(255) NOT NULL UNIQUE,
	Email NVARCHAR(255) NOT NULL,
	Password NVARCHAR(255) NOT NULL,
	Email_Verified NVARCHAR(10) NOT NULL
)



CREATE TABLE User_Profile(
	UserID INT NOT NULL PRIMARY KEY,
	FirstName NVARCHAR(30) NOT NULL,
	LastName NVARCHAR(30) NOT NULL,
	SecondaryEmail NVARCHAR(250),
	MailingAddress NVARCHAR(250) NOT NULL,
	Phone NVARCHAR(36) NOT NULL
	City NVARCHAR(40)  NOT NULL,
	State NVARCHAR(40)  NOT NULL,
	Employer NVARCHAR(150)  NOT NULL,
	WorkAddress NVARCHAR(250)  NOT NULL,
	WorkPhone NVARCHAR(36) NOT NULL
);


ALTER TABLE User_Profile ADD CONSTRAINT FK_UserID 
    FOREIGN KEY (UserID) REFERENCES User_Login(UserID)
	ON DELETE CASCADE



CREATE TABLE Account(
	AccountID INT NOT NULL IDENTITY PRIMARY KEY,
	UserID INT NOT NULL,
	TotalBalance DECIMAL  NOT NULL,
	AccountTypeID INT NOT NULL,
	Interest DECIMAL  NOT NULL,
);

CREATE TABLE AccountType(
	AccountTypeID INT NOT NULL IDENTITY PRIMARY KEY,
	AccountType NVARCHAR(40) NOT NULL 
)


-- alter table Account drop CONSTRAINT ACC_FK_UserID
-- alter table Account drop CONSTRAINT ACC_FK_AccTypeID

ALTER TABLE Account ADD CONSTRAINT ACC_FK_UserID 
    FOREIGN KEY (UserID) REFERENCES User_Profile(UserID) ON DELETE CASCADE;

ALTER TABLE Account ADD CONSTRAINT ACC_FK_AccTypeID 
    FOREIGN KEY (AccountTypeID) REFERENCES AccountType(AccountTypeID) ON DELETE CASCADE;


CREATE TABLE Transactions(
	TransactionID INT NOT NULL IDENTITY PRIMARY KEY,
	AccountID INT NOT NULL,
	Amount DECIMAL NOT NULL,
	TransactionName NVARCHAR(100) NOT NULL,
	TransactionDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	Authorized NVARCHAR(20) NOT NULL,
	LineAmount Decimal NOT NULL 
);

-- alter table Transactions drop CONSTRAINT Ts_FK_AccID

ALTER TABLE Transactions ADD CONSTRAINT Ts_FK_AccID 
    FOREIGN KEY (AccountID) REFERENCES Account(AccountID) ON DELETE CASCADE;

-- DATABASE CREATION STOP FROM HERE ---------------



SELECT * FROM User_Login
SELECT * FROM User_Profile
SELECT * FROM Account
SELECT * FROM AccountType
SELECT * FROM Transactions