00_CreateDatabase_Complete.sql

-- ============================================================================
-- SECUREBANK DATA WAREHOUSE - COMPLETE AUTOMATED SETUP
-- ============================================================================
-- This script does EVERYTHING:
-- 1. Creates database
-- 2. Creates all tables
-- 3. Loads all data from CSV files
-- 
-- BEFORE RUNNING: Update the @DataPath variable below to YOUR location!
-- ============================================================================

USE master;
GO

-- ****************************************************************************
-- STEP 1: SET YOUR DATA PATH HERE
-- ****************************************************************************
-- CHANGE THIS TO WHERE YOUR CSV FILES ARE LOCATED!
DECLARE @DataPath NVARCHAR(500) = 'C:\Users\parth\OneDrive - University of Illinois Chicago\Desktop\IDS 521 - Project\SecureBank_Data\';
-- Make sure path ends with backslash (\)
-- ****************************************************************************

PRINT '============================================================================';
PRINT 'SecureBank Data Warehouse - Automated Setup';
PRINT '============================================================================';
PRINT 'Data Path: ' + @DataPath;
PRINT '';

-- Drop existing database if it exists
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'SecureBankDW')
BEGIN
    PRINT 'Dropping existing SecureBankDW database...';
    ALTER DATABASE SecureBankDW SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE SecureBankDW;
    PRINT '✓ Existing database dropped';
END
GO

-- Create new database
PRINT 'Creating SecureBankDW database...';
CREATE DATABASE SecureBankDW;
GO
PRINT '✓ Database created';
GO

USE SecureBankDW;
GO

PRINT '';
PRINT '============================================================================';
PRINT 'Creating Tables...';
PRINT '============================================================================';

-- ============================================================================
-- DIMENSION TABLES
-- ============================================================================

-- DimDate
CREATE TABLE DimDate (
    DateKey INT PRIMARY KEY,
    Date DATE NOT NULL,
    Year INT NOT NULL,
    Quarter INT NOT NULL,
    Month INT NOT NULL,
    MonthName VARCHAR(20) NOT NULL,
    Week INT NOT NULL,
    DayOfMonth INT NOT NULL,
    DayOfWeek INT NOT NULL,
    DayName VARCHAR(20) NOT NULL,
    IsWeekend BIT NOT NULL,
    IsHoliday BIT NOT NULL,
    FiscalYear INT NOT NULL,
    FiscalQuarter INT NOT NULL
);
PRINT '✓ DimDate created';

-- DimBranch
CREATE TABLE DimBranch (
    BranchID INT PRIMARY KEY,
    BranchName VARCHAR(100) NOT NULL,
    BranchCode VARCHAR(10) NOT NULL,
    City VARCHAR(50) NOT NULL,
    State VARCHAR(50) NOT NULL,
    StateAbbrev CHAR(2) NOT NULL,
    Region VARCHAR(50) NOT NULL,
    BranchSize VARCHAR(20) NOT NULL,
    StaffCount INT NOT NULL,
    AreaType VARCHAR(20) NOT NULL,
    HasATM BIT NOT NULL,
    OpeningYear INT NOT NULL
);
PRINT '✓ DimBranch created';

-- DimChannel
CREATE TABLE DimChannel (
    ChannelID INT PRIMARY KEY,
    ChannelName VARCHAR(50) NOT NULL,
    ChannelCategory VARCHAR(20) NOT NULL,
    CostPerTransaction DECIMAL(10,2) NOT NULL,
    IsDigital BIT NOT NULL,
    IsPhysical BIT NOT NULL
);
PRINT '✓ DimChannel created';

-- DimTransactionType
CREATE TABLE DimTransactionType (
    TransactionTypeID INT PRIMARY KEY,
    TransactionName VARCHAR(100) NOT NULL,
    Category VARCHAR(20) NOT NULL,
    RequiresApproval BIT NOT NULL,
    TypicalAmount DECIMAL(10,2) NOT NULL,
    FeeAmount DECIMAL(10,2) NOT NULL
);
PRINT '✓ DimTransactionType created';

-- DimLoanType
CREATE TABLE DimLoanType (
    LoanTypeID INT PRIMARY KEY,
    LoanTypeName VARCHAR(100) NOT NULL,
    Category VARCHAR(50) NOT NULL,
    TypicalTerm INT NOT NULL,
    AverageInterestRate DECIMAL(5,2) NOT NULL,
    RequiresCollateral BIT NOT NULL,
    RiskLevel VARCHAR(20) NOT NULL,
    TypicalAmount DECIMAL(12,2) NOT NULL
);
PRINT '✓ DimLoanType created';

-- DimTime
CREATE TABLE DimTime (
    TimeKey INT PRIMARY KEY,
    Hour INT NOT NULL,
    HourFormatted VARCHAR(10) NOT NULL,
    TimeOfDay VARCHAR(20) NOT NULL,
    BusinessHours BIT NOT NULL
);
PRINT '✓ DimTime created';

-- DimCustomer
CREATE TABLE DimCustomer (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL,
    DateOfBirth DATE NOT NULL,
    Gender CHAR(1) NOT NULL,
    AgeGroup VARCHAR(20) NOT NULL,
    City VARCHAR(50) NOT NULL,
    State CHAR(2) NOT NULL,
    ZipCode VARCHAR(10) NOT NULL,
    IncomeBracket VARCHAR(20) NOT NULL,
    CreditScore INT NOT NULL,
    CreditScoreRange VARCHAR(20) NOT NULL,
    Occupation VARCHAR(100) NOT NULL,
    CustomerSegment VARCHAR(50) NOT NULL,
    CustomerSince DATE NOT NULL,
    RiskProfile VARCHAR(20) NOT NULL
);
PRINT '✓ DimCustomer created';

-- DimEmployee
CREATE TABLE DimEmployee (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100) NOT NULL,
    Title VARCHAR(100) NOT NULL,
    ManagerID INT NULL,
    Department VARCHAR(50) NOT NULL,
    BranchID INT NULL,
    HireDate DATE NOT NULL,
    Salary DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (BranchID) REFERENCES DimBranch(BranchID)
);
PRINT '✓ DimEmployee created';

-- DimATM
CREATE TABLE DimATM (
    ATMID INT PRIMARY KEY,
    ATMCode VARCHAR(20) NOT NULL,
    LocationType VARCHAR(50) NOT NULL,
    BranchID INT NULL,
    City VARCHAR(50) NOT NULL,
    State CHAR(2) NOT NULL,
    AreaType VARCHAR(20) NOT NULL,
    InstallationDate DATE NOT NULL,
    ATMType VARCHAR(50) NOT NULL,
    CashCapacity DECIMAL(12,2) NOT NULL,
    Manufacturer VARCHAR(50) NOT NULL,
    FOREIGN KEY (BranchID) REFERENCES DimBranch(BranchID)
);
PRINT '✓ DimATM created';

-- DimAccount
CREATE TABLE DimAccount (
    AccountID INT PRIMARY KEY,
    AccountNumber VARCHAR(20) NOT NULL,
    CustomerID INT NOT NULL,
    AccountType VARCHAR(50) NOT NULL,
    AccountStatus VARCHAR(20) NOT NULL,
    OpenDate DATE NOT NULL,
    CurrentBalance DECIMAL(12,2) NOT NULL,
    InterestRate DECIMAL(5,2) NOT NULL,
    FOREIGN KEY (CustomerID) REFERENCES DimCustomer(CustomerID)
);
PRINT '✓ DimAccount created';

-- ============================================================================
-- FACT TABLES
-- ============================================================================

-- FactTransaction
CREATE TABLE FactTransaction (
    TransactionID INT PRIMARY KEY,
    AccountID INT NOT NULL,
    CustomerID INT NOT NULL,
    BranchID INT NOT NULL,
    ChannelID INT NOT NULL,
    TransactionTypeID INT NOT NULL,
    DateKey INT NOT NULL,
    TimeKey INT NOT NULL,
    TransactionAmount DECIMAL(12,2) NOT NULL,
    TransactionFee DECIMAL(10,2) NOT NULL,
    AccountBalanceAfter DECIMAL(12,2) NOT NULL,
    IsFraud BIT NOT NULL,
    FOREIGN KEY (AccountID) REFERENCES DimAccount(AccountID),
    FOREIGN KEY (CustomerID) REFERENCES DimCustomer(CustomerID),
    FOREIGN KEY (BranchID) REFERENCES DimBranch(BranchID),
    FOREIGN KEY (ChannelID) REFERENCES DimChannel(ChannelID),
    FOREIGN KEY (TransactionTypeID) REFERENCES DimTransactionType(TransactionTypeID),
    FOREIGN KEY (DateKey) REFERENCES DimDate(DateKey),
    FOREIGN KEY (TimeKey) REFERENCES DimTime(TimeKey)
);
PRINT '✓ FactTransaction created';

-- FactLoan
CREATE TABLE FactLoan (
    LoanID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    BranchID INT NOT NULL,
    LoanTypeID INT NOT NULL,
    OriginationDateKey INT NOT NULL,
    LoanAmount DECIMAL(12,2) NOT NULL,
    InterestRate DECIMAL(5,2) NOT NULL,
    Term INT NOT NULL,
    MonthlyPayment DECIMAL(10,2) NOT NULL,
    OutstandingBalance DECIMAL(12,2) NOT NULL,
    Status VARCHAR(20) NOT NULL,
    DaysDelinquent INT NOT NULL,
    CreditScoreAtOrigination INT NOT NULL,
    FOREIGN KEY (CustomerID) REFERENCES DimCustomer(CustomerID),
    FOREIGN KEY (BranchID) REFERENCES DimBranch(BranchID),
    FOREIGN KEY (LoanTypeID) REFERENCES DimLoanType(LoanTypeID),
    FOREIGN KEY (OriginationDateKey) REFERENCES DimDate(DateKey)
);
PRINT '✓ FactLoan created';

-- FactATMUsage
CREATE TABLE FactATMUsage (
    ATMTransactionID INT PRIMARY KEY,
    ATMID INT NOT NULL,
    CustomerID INT NOT NULL,
    DateKey INT NOT NULL,
    TimeKey INT NOT NULL,
    WithdrawalAmount DECIMAL(10,2) NOT NULL,
    DepositAmount DECIMAL(10,2) NOT NULL,
    TransactionCount INT NOT NULL,
    FOREIGN KEY (ATMID) REFERENCES DimATM(ATMID),
    FOREIGN KEY (CustomerID) REFERENCES DimCustomer(CustomerID),
    FOREIGN KEY (DateKey) REFERENCES DimDate(DateKey),
    FOREIGN KEY (TimeKey) REFERENCES DimTime(TimeKey)
);
PRINT '✓ FactATMUsage created';

-- ============================================================================
-- INDEXES
-- ============================================================================
PRINT '';
PRINT 'Creating indexes...';

CREATE INDEX IX_FactTransaction_DateKey ON FactTransaction(DateKey);
CREATE INDEX IX_FactTransaction_CustomerID ON FactTransaction(CustomerID);
CREATE INDEX IX_FactTransaction_BranchID ON FactTransaction(BranchID);
CREATE INDEX IX_FactTransaction_ChannelID ON FactTransaction(ChannelID);
CREATE INDEX IX_FactLoan_CustomerID ON FactLoan(CustomerID);
CREATE INDEX IX_FactLoan_DateKey ON FactLoan(OriginationDateKey);
CREATE INDEX IX_FactATM_ATMID ON FactATMUsage(ATMID);
CREATE INDEX IX_FactATM_DateKey ON FactATMUsage(DateKey);

PRINT '✓ Indexes created';
PRINT '';

PRINT '============================================================================';
PRINT 'Tables created successfully!';
PRINT '============================================================================';
PRINT '';
PRINT 'Database is ready. You can now run the data load script.';
PRINT '';
GO