7

USE master;
GO

IF db_id (N'Bank_lab7') IS NOT NULL
DROP DATABASE Bank_lab7;
go

CREATE DATABASE Bank_lab7 
	ON (NAME = Bank_dat_lab7, FILENAME = 'C:\data\Bankdatlab7.mdf',
	SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
	LOG ON (NAME = Bank_log_lab7, FILENAME = 'C:\data\Bankloglab7.ldf',
	SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB);
GO

USE Bank_lab7;
GO

CREATE TABLE Human (idofperson int IDENTITY(0, 1) PRIMARY KEY,
					firstname char(50),
					secondname char(50),
					location char(150),
					phone int);
GO

ALTER TABLE Human ADD age int CHECK (age > 0);
GO

ALTER TABLE Human ADD email char(25) DEFAULT ('н/д');
GO

ALTER TABLE Human ADD dateofbirth datetime DEFAULT (getdate());
GO

CREATE TABLE Employee (uniqid uniqueidentifier PRIMARY KEY ROWGUIDCOL DEFAULT (newid()),
						post char(50),
						passnumber int,
						experience int);
GO

CREATE TABLE Client (clientid int PRIMARY KEY,
					accountnumber int);
GO

CREATE SEQUENCE ForClient START WITH 0 INCREMENT BY 1;
GO

INSERT Client (clientid, accountnumber)
VALUES (NEXT VALUE FOR ForClient, 1);
INSERT Client (clientid, accountnumber)
VALUES (NEXT VALUE FOR ForClient, 2);
GO

/*
SELECT * FROM Client;
GO
*/

CREATE TABLE Operation (idoper int PRIMARY KEY,
						dt datetime);
GO

CREATE TABLE Credit (ido int FOREIGN KEY REFERENCES Operation(idoper) 
						ON DELETE SET NULL
						ON UPDATE CASCADE,
					sumofmoney money,
					procent decimal);
GO


INSERT Operation (idoper, dt) VALUES (1, '20131201');
INSERT Operation (idoper, dt) VALUES (2, '20131222');
INSERT Operation (idoper, dt) VALUES (3, '20140111');
INSERT Operation (idoper, dt) VALUES (4, '20140112');
INSERT Credit(ido, sumofmoney, procent) VALUES (1, $100000, 10.5);
INSERT Credit(ido, sumofmoney, procent) VALUES (1, $25000, 5.0);
INSERT Credit(ido, sumofmoney, procent) VALUES (2, $1000000, 15.0);
INSERT Credit(ido, sumofmoney, procent) VALUES (3, $1000, 5.0);
GO

-- view 1
CREATE VIEW CCO AS 
	SELECT ido, sumofmoney FROM Credit WHERE ido = 1;
GO

SELECT * FROM CCO;
GO

-- view 2
CREATE VIEW OC WITH SCHEMABINDING AS
	SELECT a.idoper, a.dt, b.sumofmoney FROM
	dbo.Operation a INNER JOIN dbo.Credit b ON a.idoper = 1
	WITH CHECK OPTION;
GO

SELECT * FROM OC;
GO

-- index 1
CREATE UNIQUE CLUSTERED INDEX idOC ON OC (sumofmoney);
GO

-- index 2
CREATE INDEX idOC1 ON Credit (ido) INCLUDE (sumofmoney);
GO