117 lines
3.3 KiB
Transact-SQL
Executable File
117 lines
3.3 KiB
Transact-SQL
Executable File
-- Baseline Database Initialization Script
|
|
-- This creates a sample database structure for testing
|
|
|
|
USE master;
|
|
GO
|
|
|
|
-- Create test database
|
|
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'TestDB_Baseline')
|
|
BEGIN
|
|
CREATE DATABASE TestDB_Baseline;
|
|
END
|
|
GO
|
|
|
|
USE TestDB_Baseline;
|
|
GO
|
|
|
|
-- Create sample tables
|
|
|
|
-- Dimension: Customers
|
|
CREATE TABLE dbo.DimCustomer (
|
|
CustomerID INT PRIMARY KEY IDENTITY(1,1),
|
|
CustomerName NVARCHAR(100) NOT NULL,
|
|
Email NVARCHAR(100),
|
|
City NVARCHAR(50),
|
|
Country NVARCHAR(50),
|
|
CreatedDate DATETIME DEFAULT GETDATE()
|
|
);
|
|
|
|
-- Dimension: Products
|
|
CREATE TABLE dbo.DimProduct (
|
|
ProductID INT PRIMARY KEY IDENTITY(1,1),
|
|
ProductName NVARCHAR(100) NOT NULL,
|
|
Category NVARCHAR(50),
|
|
UnitPrice DECIMAL(10,2),
|
|
IsActive BIT DEFAULT 1
|
|
);
|
|
|
|
-- Fact: Sales
|
|
CREATE TABLE dbo.FactSales (
|
|
SaleID INT PRIMARY KEY IDENTITY(1,1),
|
|
CustomerID INT,
|
|
ProductID INT,
|
|
SaleDate DATE,
|
|
Quantity INT,
|
|
UnitPrice DECIMAL(10,2),
|
|
TotalAmount DECIMAL(10,2),
|
|
TaxAmount DECIMAL(10,2),
|
|
FOREIGN KEY (CustomerID) REFERENCES dbo.DimCustomer(CustomerID),
|
|
FOREIGN KEY (ProductID) REFERENCES dbo.DimProduct(ProductID)
|
|
);
|
|
|
|
-- Insert sample data (TEST DATA ONLY - NOT REAL CUSTOMERS)
|
|
|
|
-- Customers
|
|
INSERT INTO dbo.DimCustomer (CustomerName, Email, City, Country) VALUES
|
|
('TestCustomer1', 'test1@test.local', 'City1', 'Country1'),
|
|
('TestCustomer2', 'test2@test.local', 'City2', 'Country2'),
|
|
('TestCustomer3', 'test3@test.local', 'City3', 'Country3'),
|
|
('TestCustomer4', 'test4@test.local', 'City4', 'Country4'),
|
|
('TestCustomer5', 'test5@test.local', 'City5', 'Country5');
|
|
|
|
-- Products
|
|
INSERT INTO dbo.DimProduct (ProductName, Category, UnitPrice, IsActive) VALUES
|
|
('Laptop', 'Electronics', 999.99, 1),
|
|
('Mouse', 'Electronics', 29.99, 1),
|
|
('Keyboard', 'Electronics', 79.99, 1),
|
|
('Monitor', 'Electronics', 299.99, 1),
|
|
('Desk Chair', 'Furniture', 199.99, 1),
|
|
('Desk', 'Furniture', 399.99, 1),
|
|
('Notebook', 'Stationery', 4.99, 1),
|
|
('Pen Set', 'Stationery', 12.99, 1);
|
|
|
|
-- Sales (100 records)
|
|
DECLARE @i INT = 1;
|
|
WHILE @i <= 100
|
|
BEGIN
|
|
INSERT INTO dbo.FactSales (CustomerID, ProductID, SaleDate, Quantity, UnitPrice, TotalAmount, TaxAmount)
|
|
VALUES (
|
|
(ABS(CHECKSUM(NEWID())) % 5) + 1, -- Random CustomerID 1-5
|
|
(ABS(CHECKSUM(NEWID())) % 8) + 1, -- Random ProductID 1-8
|
|
DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 365, GETDATE()), -- Random date in last year
|
|
(ABS(CHECKSUM(NEWID())) % 10) + 1, -- Random Quantity 1-10
|
|
(ABS(CHECKSUM(NEWID())) % 900) + 100.00, -- Random price 100-1000
|
|
0, -- Will be calculated
|
|
0 -- Will be calculated
|
|
);
|
|
|
|
-- Calculate amounts
|
|
UPDATE dbo.FactSales
|
|
SET TotalAmount = Quantity * UnitPrice,
|
|
TaxAmount = Quantity * UnitPrice * 0.1
|
|
WHERE SaleID = @i;
|
|
|
|
SET @i = @i + 1;
|
|
END
|
|
GO
|
|
|
|
-- Create some views for testing
|
|
CREATE VIEW dbo.vw_SalesSummary AS
|
|
SELECT
|
|
c.CustomerName,
|
|
p.ProductName,
|
|
s.SaleDate,
|
|
s.Quantity,
|
|
s.TotalAmount
|
|
FROM dbo.FactSales s
|
|
JOIN dbo.DimCustomer c ON s.CustomerID = c.CustomerID
|
|
JOIN dbo.DimProduct p ON s.ProductID = p.ProductID;
|
|
GO
|
|
|
|
-- Create statistics
|
|
CREATE STATISTICS stat_sales_date ON dbo.FactSales(SaleDate);
|
|
CREATE STATISTICS stat_customer_country ON dbo.DimCustomer(Country);
|
|
GO
|
|
|
|
PRINT 'Baseline database initialized successfully';
|
|
GO |