db lab 15 var1

use master;
go
if DB_ID (N'lab13_1') is not null
    drop database lab13_1;
go
create database lab13_1
go


use master;
go
if DB_ID (N'lab13_2') is not null
    drop database lab13_2;
go
create database lab13_2
go


--================================================
--------------------DATABASE-1--------------------
--================================================
use lab13_1;
go
if (OBJECT_ID(N'FK_books_wid', N'F') is not null)
    alter table dbo.books
        drop CONSTRAINT FK_books_wid
go
if OBJECT_ID(N'dbo.writers', N'U') is not null
    drop table dbo.writers
go
create table dbo.writers (
    id              int not null,
    name            varchar(35),

    PRIMARY KEY (id),
    CONSTRAINT CHK_writers_id
        CHECK (id < 4)
    );
go

if OBJECT_ID(N'dbo.books', N'U') is not null
    drop table dbo.books;
go
create table dbo.books (
    bid             int not null,
    title           varchar(254),
    wid             int,
    rating          int,

    PRIMARY KEY (title),
    CONSTRAINT CHK_books_id
        CHECK (bid < 4),
    CONSTRAINT FK_books_wid
        FOREIGN KEY (wid)
        REFERENCES dbo.writers(id)
    );
go


--================================================
--------------------DATABASE-2--------------------
--================================================
use lab13_2;
go
DECLARE @comparator int = 5;
go
if (OBJECT_ID(N'FK_books_wid', N'F') is not null)
    alter table dbo.books
        drop CONSTRAINT FK_books_wid
go
if OBJECT_ID(N'dbo.writers', N'U') is not null
    drop table dbo.writers;
go
create table dbo.writers (
    id              int,
    writer          varchar(35),

    PRIMARY KEY (id),
    CONSTRAINT CHK_writers_id
        CHECK (id >= 4)
    );
go

if OBJECT_ID(N'dbo.books', N'U') is not null
    drop table dbo.books;
go
create table dbo.books (
    bid             int not null,
    title           varchar(254),
    wid             int,
    rating          int,

    PRIMARY KEY (title),
    CONSTRAINT CHK_books_id
        CHECK (bid >= 4),
    CONSTRAINT FK_books_wid
        FOREIGN KEY (wid)
        REFERENCES dbo.writers(id)
    );
go



--================================================
-----------------DISTRIBUTED-VIEV-----------------
--================================================
use lab13_1;
go
if OBJECT_ID(N'dist_v', N'V') is not null
    drop view dist_v;
go
create view dist_v as
    select * from lab13_1.dbo.books b1
        inner join lab13_1.dbo.writers w1
            on b1.wid = w1.id
    UNION ALL
    select * from lab13_2.dbo.books b2
        inner join lab13_2.dbo.writers w2
            on b2.wid = w2.id
go

select * from dist_v