Phase0 – DBA 2024/25 restaurant-management

Context Navigation

← Previous Version
View Latest Version
Next Version →

Version 1 (modified by 213280, 3 weeks ago) ( diff )
Creation of phase0

Креирање на табели

CREATE TABLE [dbo].[Business]
(
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (100) NOT NULL,
    [Address] NVARCHAR (200) NOT NULL,
    [Rating] REAL NOT NULL,
    [PhoneNumber] NVARCHAR(15) NOT NULL,
    [BackGroundImage] VARBINARY(MAX) NULL,
    [Logo] VARBINARY(MAX) NULL,
    [IsOpen] BIT NOT NULL,
    [IsAvailable] BIT NOT NULL,
    [Type] INT NOT NULL,
    [OwnerId] INT NOT NULL,
    [CreatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    [UpdatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    [DeletedAt] DATETIME2 NULL,
    [IsDeleted] BIT NOT NULL DEFAULT 0,
    CONSTRAINT [PK_Business] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Business_User_OwnerId] FOREIGN KEY (OwnerId) REFERENCES [User]([Id]) ON DELETE NO ACTION,
);

CREATE TABLE [dbo].[FoodCategory]
(
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (100) NOT NULL,
    [IsAvailable] BIT NOT NULL,
    [BusinessId] INT NOT NULL,
    [CreatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    [UpdatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    [DeletedAt] DATETIME2 NULL,
    [IsDeleted] BIT NOT NULL DEFAULT 0,
    CONSTRAINT [PK_FoodCategory] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_FoodCategory_Business_BusinessId] FOREIGN KEY (BusinessId) REFERENCES [Business]([Id]) ON DELETE CASCADE,
);

CREATE TABLE [dbo].[FoodItem]
(
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (100) NOT NULL,
    [Price] INT NOT NULL,
    [Description] NVARCHAR (300) NULL,
    [IsAvailable] BIT NOT NULL,
    [FoodItemImage] VARBINARY(MAX),
    [FoodCategoryId] INT NOT NULL,
    [CreatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    [UpdatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    [DeletedAt] DATETIME2 NULL,
    [IsDeleted] BIT NOT NULL DEFAULT 0,
    CONSTRAINT [PK_FoodItem] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_FoodItem_FoodCategory_FoodCategoryId] FOREIGN KEY (FoodCategoryId) REFERENCES [FoodCategory]([Id]) ON DELETE CASCADE,
);

CREATE TABLE [dbo].[ItemInOrder]
(
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [Quantity] INT NOT NULL,
    [FoodItemId] INT NOT NULL,
    [OrderId] INT NOT NULL,
    CONSTRAINT [PK_ItemInOrder] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_ItemInOrder_FoodItem_FoodItemId] FOREIGN KEY (FoodItemId) REFERENCES [FoodItem]([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FK_ItemInOrder_Order_OrderId] FOREIGN KEY (OrderId) REFERENCES [Order]([Id]) ON DELETE CASCADE,
);

CREATE TABLE [dbo].[ItemInShoppingCart]
(
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [Quantity] INT NOT NULL,
    [FoodItemId] INT NOT NULL,
    [ShoppingCartId] INT NOT NULL,
    [CreatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    [UpdatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    [DeletedAt] DATETIME2 NULL,
    [IsDeleted] BIT NOT NULL DEFAULT 0,
    CONSTRAINT [PK_ItemInShoppingCart] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_ItemInShoppingCart_FoodItem_FoodItemId] FOREIGN KEY (FoodItemId) REFERENCES [FoodItem]([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FK_ItemInShoppingCart_ShoppingCard_ShoppingCartId] FOREIGN KEY (ShoppingCartId) REFERENCES [ShoppingCart]([Id]) ON DELETE CASCADE,
);

CREATE TABLE [dbo].[Order]
(
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [OrderGlobalStatus] INT NOT NULL,
    [TotalPrice] DECIMAL NOT NULL,
    [DeliveryFee] DECIMAL NOT NULL,
    [CustomerId] INT NOT NULL,
    [DeliveryDriverId] INT NULL,
    [BusinessId] INT NOT NULL,
    [CreatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    [UpdatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    [DeletedAt] DATETIME2 NULL,
    [IsDeleted] BIT NOT NULL DEFAULT 0,
    CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Order_User_CustomerId] FOREIGN KEY (CustomerId) REFERENCES [User]([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FK_Order_User_DeliveryDriverId] FOREIGN KEY (DeliveryDriverId) REFERENCES [User]([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FK_Order_Business_BusinessId] FOREIGN KEY (BusinessId) REFERENCES [Business]([Id]) ON DELETE NO ACTION,
);

CREATE TABLE [dbo].[OrderHistory]
(
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [TimeStamp] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    [OrderGlobalStatus] INT NOT NULL,
    [OrderId] INT NOT NULL,
    [UpdaterUserId] INT NOT NULL,
    [CreatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    [UpdatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    [DeletedAt] DATETIME2 NULL,
    [IsDeleted] BIT NOT NULL DEFAULT 0,
    CONSTRAINT [PK_OrderHistory] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_OrderHistory_Order_OrderId] FOREIGN KEY (OrderId) REFERENCES [Order]([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_OrderHistory_User_UpdaterUserId] FOREIGN KEY (UpdaterUserId) REFERENCES [User]([Id]) ON DELETE NO ACTION,
);

CREATE TABLE [dbo].[Role]
(
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (50) NOT NULL,
    CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED ([Id] ASC),
);

CREATE TABLE [dbo].[ShoppingCart]
(
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [OwnerId] INT NOT NULL,
    CONSTRAINT [PK_ShoppingCart] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_ShoppingCart_User_OwnerId] FOREIGN KEY (OwnerId) REFERENCES [User]([Id]) ON DELETE CASCADE,
);

CREATE TABLE [dbo].[Token]
(
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [AccessToken] NVARCHAR (MAX) NOT NULL,
    [RefreshToken] NVARCHAR (MAX) NOT NULL,
    [AccessTokenExpirationDate] DATETIME2 NOT NULL, 
    [RefreshTokenExpirationDate] DATETIME2 NOT NULL,
    [UserId] INT NOT NULL,
    CONSTRAINT [PK_Token] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Token_User_UserId] FOREIGN KEY (UserId) REFERENCES [User]([Id]) ON DELETE CASCADE,
);

CREATE TABLE [dbo].[User]
(
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [Email] NVARCHAR (50) NOT NULL UNIQUE,
    [PasswordHash] NVARCHAR (200) NOT NULL,
    [FirstName] NVARCHAR (100) NOT NULL,
    [LastName] NVARCHAR (100) NOT NULL,
    [RoleId] INT NOT NULL,
    [CreatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    [UpdatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    [DeletedAt] DATETIME2 NULL,
    [IsDeleted] BIT NOT NULL DEFAULT 0,
    CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_User_Role_RoleId] FOREIGN KEY (RoleId) REFERENCES [Role]([Id]) ON DELETE CASCADE,
);

Креирање на тест податоци

DECLARE @RoleId INT = 1;
DECLARE @NumberOfUsers INT = 1000; 
DECLARE @InsertedUsers INT = 0;
DECLARE @BatchSize INT = 1000;

WHILE @InsertedUsers < @NumberOfUsers
BEGIN
    INSERT INTO [dbo].[User] 
        (Email, PasswordHash, FirstName, LastName, RoleId, CreatedAt, UpdatedAt, DeletedAt, IsDeleted)
    SELECT TOP (@BatchSize)
        CONCAT('user', ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + @InsertedUsers, '@example.com'),
        'AQAAAAIAAYagAAAAEDiHqhqrUlhjMSqG+Wm9CP10wIioySTDOI1PEgvzV5uIoRhJAXdrXLLqQ6mSsw3lug==',
        CONCAT('FirstName', ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + @InsertedUsers),
        CONCAT('LastName', ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + @InsertedUsers),
        @RoleId,
        DATEADD(SECOND, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + @InsertedUsers, GETUTCDATE()),
        DATEADD(SECOND, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + @InsertedUsers, GETUTCDATE()),
        NULL,
        0
    FROM sys.objects a CROSS JOIN sys.objects b;

    SET @InsertedUsers = @InsertedUsers + @BatchSize;
END;

DECLARE @NumberOfUsers INT = 10000;
DECLARE @NumberOfOrders INT = 20000000;
DECLARE @InsertedOrders INT = 0;
DECLARE @BatchSize INT = 20000;
DECLARE @StartDate DATETIME2 = '2021-01-01';
DECLARE @EndDate DATETIME2 = '2024-12-31';

WHILE @InsertedOrders < @NumberOfOrders
BEGIN
    INSERT INTO [dbo].[Order]
        (OrderGlobalStatus, TotalPrice, DeliveryFee, CustomerId, DeliveryDriverId, BusinessId, CreatedAt, UpdatedAt, DeletedAt, IsDeleted)
    SELECT TOP (@BatchSize)
        CASE WHEN (ABS(CHECKSUM(NEWID())) % 10) = 0 THEN 1 ELSE (ABS(CHECKSUM(NEWID())) % 5) + 2 END,
        CAST((ABS(CHECKSUM(NEWID())) % 9000 + 1000) / 100.0 AS DECIMAL(10,2)),
        CAST((ABS(CHECKSUM(NEWID())) % 800 + 200) / 100.0 AS DECIMAL(10,2)),
        (ABS(CHECKSUM(NEWID())) % @NumberOfUsers) + 1,
        CASE WHEN (ABS(CHECKSUM(NEWID())) % 10) = 0 THEN NULL ELSE (ABS(CHECKSUM(NEWID())) % @NumberOfUsers) + 1 END,
        (ABS(CHECKSUM(NEWID())) % 3) + 1,
        DATEADD(SECOND, ABS(CHECKSUM(NEWID())) % DATEDIFF(SECOND, @StartDate, @EndDate), @StartDate),
        DATEADD(MINUTE, ABS(CHECKSUM(NEWID())) % 121, DATEADD(SECOND, ABS(CHECKSUM(NEWID())) % DATEDIFF(SECOND, @StartDate, @EndDate), @StartDate)),
        NULL,
        0
    FROM sys.objects a CROSS JOIN sys.objects b;

    SET @InsertedOrders = @InsertedOrders + @BatchSize;
        PRINT CONCAT('Orders inserted: ', @InsertedOrders);
END;

-- ========================================
-- Insert ItemInOrder (batch)
-- ========================================
DECLARE @InsertedItems INT = 0;
DECLARE @NumberOfItemsInOrder INT = 40000000
DECLARE @NumberOfOrders INT = 32980003
DECLARE @BatchSize INT = 20000

WHILE @InsertedItems < @NumberOfItemsInOrder
BEGIN
    INSERT INTO [dbo].[ItemInOrder]
        (Quantity, FoodItemId, OrderId)
    SELECT TOP (@BatchSize)

                -- Quantity between 1 and 5
                (ABS(CHECKSUM(NEWID())) % 5) + 1 AS Quantity,

                -- FoodItemId only between 1 and 17
        (ABS(CHECKSUM(NEWID())) % 17) + 1 as FoodItemId,

                -- Attach to random existing order
        (ABS(CHECKSUM(NEWID())) % @NumberOfOrders) + 1 AS OrderId
    FROM sys.objects a CROSS JOIN sys.objects b;

    SET @InsertedItems = @InsertedItems + @BatchSize;
    PRINT CONCAT('ItemInOrder inserted: ', @InsertedItems);
END

-- ========================================
-- Parameters
-- ========================================

DECLARE @NumberOfOrderHistory INT = 15000000; -- change as needed
DECLARE @NumberOfUsers INT = 1000; 
DECLARE @NumberOfOrders INT = 10000000
DECLARE @InsertedHistory INT = 0;
DECLARE @BatchSize INT = 20000


-- ========================================
-- Insert OrderHistory (batch)
-- ========================================


WHILE @InsertedHistory < @NumberOfOrderHistory
BEGIN
    INSERT INTO [dbo].[OrderHistory]
        (TimeStamp, OrderGlobalStatus, OrderId, UpdaterUserId, CreatedAt, UpdatedAt, DeletedAt, IsDeleted)
    SELECT TOP (@BatchSize)
        DATEADD(MINUTE, -ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), GETUTCDATE()),

                -- Random status
                (ABS(CHECKSUM(NEWID())) % 6) + 1 AS OrderGlobalStatus,
                
                -- Random order id (must exist from Orders table)
                (ABS(CHECKSUM(NEWID())) % @NumberOfOrders) + 1 AS OrderId,
                
                -- Random updater user
                (ABS(CHECKSUM(NEWID())) % @NumberOfUsers) + 1 AS UpdaterUserId,  

        GETUTCDATE(),
        GETUTCDATE(),
        NULL,
        0
    FROM sys.objects a CROSS JOIN sys.objects b;

    SET @InsertedHistory = @InsertedHistory + @@ROWCOUNT;
    PRINT CONCAT('OrderHistory inserted: ', @InsertedHistory);
END

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text