BD.Lab6

USE master
GO

IF DB_ID (N'Shop') IS NOT NULL
    DROP DATABASE Shop;
GO

CREATE DATABASE Shop
    ON (
        name = Shop_dat,
        filename = 'E:\Sorry\DBProjects\Lab6\Shop_dat.mdf',
        size = 10,
        maxsize = unlimited,
        filegrowth = 5%
        )
    LOG ON (
        name = Shop_log,
        filename = 'E:\Sorry\DBProjects\Lab6\Shop_log.ldf',
        size = 5,
        maxsize = 25,
        filegrowth = 5
        ) ;
GO

USE Shop
GO

IF SCHEMA_ID(N'ShopSchema') IS NOT NULL
    DROP SCHEMA ShopSchema;
    GO

CREATE SCHEMA ShopSchema;
 GO

CREATE TABLE Shop.ShopSchema.[shop]
(
    shopCode INT PRIMARY KEY NOT NULL IDENTITY(0, 1) ,
    shopName VARCHAR(25) ,
    isOutlet BIT DEFAULT 0 NOT NULL ,
    address  VARCHAR(25)   NOT NULL
)
 GO

CREATE TABLE Shop.ShopSchema.shopMan
(
    shopmanCode UNIQUEIDENTIFIER PRIMARY KEY ROWGUIDCOL DEFAULT NEWID() NOT NULL,
    firstName VARCHAR(10) NOT NULL,
    lastName VARCHAR(10) NOT NULL,
    middleName VARCHAR(10),
    dateOfBirth DATE NOT NULL,
    phone CHAR(11) NOT NULL,
    position VARCHAR(15),
    isFired BIT DEFAULT 0,

    shopCode INT NOT NULL,
    FOREIGN KEY (shopCode) REFERENCES shop.ShopSchema.[shop](shopCode)
)
  GO

CREATE SEQUENCE [CountBy1]
  START WITH 1
  INCREMENT BY 1;
 GO

CREATE TABLE Shop.ShopSchema.[check]
(
    checkID INT PRIMARY KEY NOT NULL,
    date DATE NOT NULL,
    totalCost MONEY NOT NULL,
    typeOfPay BIT NOT NULL,
    discount SMALLINT,
    shopmanCode UNIQUEIDENTIFIER NOT NULL,
    FOREIGN KEY (shopmanCode) REFERENCES shopMan (shopmanCode)
)
  GO

DECLARE @i INT
SET @i = 0

WHILE @i < 3
  BEGIN
DECLARE @lastIdent INT

INSERT Shop.ShopSchema.[shop] (shopName, isOutlet, address)
  VALUES ('MAVI Аутлет Белая Дача', 1, 'Новорязанское ш., 8')

SET @lastIdent = @@IDENTITY

DECLARE @shopmanCode UNIQUEIDENTIFIER
SET @shopmanCode = newid()

INSERT Shop.ShopSchema.[shopMan] (shopmanCode, firstName, lastName, middleName, dateOfBirth, phone, position, shopCode)
  VALUES (@shopmanCode, 'Иван', 'Иванович', NULL, '1997-01-01', '89161111111', 'Кассир', @lastIdent)

INSERT Shop.ShopSchema.[check] (checkID, date, totalCost, typeOfPay, discount, shopmanCode)
  VALUES (NEXT VALUE FOR CountBy1, '2017-11-02', 1000, 0, 0, @shopmanCode)

  SET @i = @i + 1
  END
GO

-------------

ALTER TABLE Shop.ShopSchema.[check]
    DROP FK__check__shopmanCo__35BCFE0A

ALTER TABLE Shop.ShopSchema.[check]
    ADD CONSTRAINT FK__check__shopmanCo__35BCFE0A FOREIGN KEY (shopmanCode) REFERENCES Shop.ShopSchema.[shopMan](shopmanCode) ON DELETE NO ACTION

DELETE FROM Shop.ShopSchema.[check] WHERE checkID < 4
GO

--------------

ALTER TABLE Shop.ShopSchema.[check]
    DROP FK__check__shopmanCo__35BCFE0A

ALTER TABLE Shop.ShopSchema.[check]
    ADD CONSTRAINT FK__check__shopmanCo__35BCFE0A FOREIGN KEY (shopmanCode) REFERENCES Shop.ShopSchema.[shopMan](shopmanCode) ON DELETE CASCADE

DELETE FROM Shop.ShopSchema.[shopMan] WHERE shopCode = 5
GO

--------------

ALTER TABLE Shop.ShopSchema.[check]
    DROP FK__check__shopmanCo__35BCFE0A

ALTER TABLE Shop.ShopSchema.[check]
    ADD CONSTRAINT FK__check__shopmanCo__35BCFE0A FOREIGN KEY (shopmanCode) REFERENCES Shop.ShopSchema.[shopMan](shopmanCode) ON DELETE SET NULL

DELETE FROM Shop.ShopSchema.[shopMan] WHERE shopCode = 6
GO

---------------

ALTER TABLE Shop.ShopSchema.[check]
    DROP FK__check__shopmanCo__35BCFE0A

ALTER TABLE Shop.ShopSchema.[check]
    ADD CONSTRAINT FK__check__shopmanCo__35BCFE0A FOREIGN KEY (shopmanCode) REFERENCES Shop.ShopSchema.[shopMan](shopmanCode) ON DELETE SET DEFAULT

DELETE FROM Shop.ShopSchema.[shopMan] WHERE shopCode = 7
GO