-- создание файловых групп
use [master]
go

ALTER DATABASE [db_storage] ADD FILEGROUP FastGrowingGroup;
alter database [db_storage] add file
(NAME = N'FastGrowingGroup', FILENAME = N'D:\SQLData\oleg_db\FastGrowingGroup.ndf', SIZE = 358400KB, FILEGROWTH = 51200KB)
to filegroup FastGrowingGroup
go

ALTER DATABASE [db_storage] ADD FILEGROUP DefaultGroup;
alter database [db_storage] add file
(NAME = N'DefaultGroup', FILENAME = N'D:\SQLData\oleg_db\DefaultGroup.ndf', SIZE = 358400KB, FILEGROWTH = 51200KB)
to filegroup DefaultGroup
go

ALTER DATABASE [db_storage] ADD FILEGROUP GroupOtherTable;
alter database [db_storage] add file
(NAME = N'GroupOtherTable', FILENAME = N'D:\SQLData\oleg_db\GroupOtherTable.ndf', SIZE = 358400KB, FILEGROWTH = 51200KB)
to filegroup GroupOtherTable
go

ALTER DATABASE [db_storage] ADD FILEGROUP indexGroup;
alter database [db_storage] add file
(NAME = N'IndexGroup', FILENAME = N'D:\SQLData\oleg_db\IndexGroup.ndf', SIZE = 358400KB, FILEGROWTH = 51200KB)
to filegroup indexGroup
go

--функция и схема разбиения для секционирования таблицы фактов
use[db_storage]
go
create partition function functionPartition (bigint)
as range right for values (20230101, 20240101, 20250101);
go

CREATE PARTITION SCHEME scheme_1
AS PARTITION functionPartition TO
(FastGrowingGroup,DefaultGroup, DefaultGroup, DefaultGroup);

-- Product table
CREATE TABLE Product (
    id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Product_id PRIMARY KEY,
    category VARCHAR(255),
    product_name VARCHAR(255),
    [description] NVARCHAR(max),
    stock_quantity INT,
	price INT,
    discount INT,
    promotion VARCHAR(255),
) on GroupOtherTable;


-- Supplier table
CREATE TABLE Supplier (
    id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Supplier_id PRIMARY KEY,
    company_name VARCHAR(255),
    supplier_name VARCHAR(255),
    surname VARCHAR(255),
    supplier_address NVARCHAR(MAX),
    phone VARCHAR(255),
    email NVARCHAR(255)
) on GroupOtherTable;

-- Client table with SCD Type 2 columns for versioning
CREATE TABLE Client (
    id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Client_id PRIMARY KEY,
    f_name VARCHAR(255),
    l_name VARCHAR(255),
    patronymic VARCHAR(255),
    sex VARCHAR(255),
    client_address NVARCHAR(MAX),
    flag BIT
) on GroupOtherTable;

-- Client_fast table
CREATE TABLE Client_fast (
    id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Client_fast_id PRIMARY KEY,
    age INT,
    phone VARCHAR(255),
    email NVARCHAR(255),
) on FastGrowingGroup;

-- Seller table with SCD Type 2 columns for versioning
CREATE TABLE Seller (
    id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Seller_id PRIMARY KEY,
    seller_name VARCHAR(255),
    surname VARCHAR(255),
    patronymic VARCHAR(255),
    age INT,
    seller_address NVARCHAR(max),
    phone VARCHAR(255),
    email NVARCHAR(255),
    salary INT,
    experience INT,
    position VARCHAR(255),
    flag BIT
) on GroupOtherTable;

-- Filial table
CREATE TABLE Filial (
    id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Filial_id PRIMARY KEY,
    [name] VARCHAR(255),
    country VARCHAR(255),
    city VARCHAR(255),
    filial_address NVARCHAR(max),
    phone VARCHAR(255),
    email NVARCHAR(255)
) on GroupOtherTable;

-- Delivery table
CREATE TABLE Delivery (
    id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Delivery_id PRIMARY KEY,
    [type] VARCHAR(255),
    method VARCHAR(255),
    [status] VARCHAR(255),
    Tracing BIT,
    estimated_delivery_date DATE,
    actual_delivery_date DATE
) on GroupOtherTable;

-- Date table
CREATE TABLE Dates
  ( id bigint NOT NULL CONSTRAINT PK_Dates_key PRIMARY KEY,
	Date date,
	Year int,
	Quarter int,
	Month int,
	Day int,
	Month_Name nvarchar(50),
	Day_Name nvarchar(50))
ON GroupOtherTable;


-- Sales_facts table
CREATE TABLE Sales_facts (
    id INT IDENTITY(1,1) NOT NULL,
    product_id INT,
    client_id INT,
    client_fast_id INT,
    seller_id INT,
    filial_id INT,
    delivery_id INT,
    supplier_id INT,
    date_sale BIGINT,
    quantity INT,
    total_cost DECIMAL,
	foreign key (supplier_id) references Supplier(id),
    FOREIGN KEY (product_id) REFERENCES Product(id),
    FOREIGN KEY (client_id) REFERENCES Client(id),
    FOREIGN KEY (seller_id) REFERENCES Seller(id),
    FOREIGN KEY (filial_id) REFERENCES Filial(id),
    FOREIGN KEY (delivery_id) REFERENCES Delivery(id),
    FOREIGN KEY (date_sale) REFERENCES Dates(id),
    FOREIGN KEY (client_fast_id) REFERENCES Client_fast(id)
) on scheme_1(date_sale);

select * from Sales_facts;

here is the code for the electronics store database. I need you to redo the tables so that this database looks like a travel agency database. In fact, I want you to replace the names without particularly redoing the logic and connections.