-- =============================================
-- Ñîçäàíèå áàçû äàííûõ BookShopDB
-- =============================================
USE master
GO

-- Óíè÷òîæåíèå áàçû äàííûõ, åñëè îíà óæå ñóùåñòâóåò
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'BookShopDB')
	DROP DATABASE BookShopDB
GO

CREATE DATABASE BookShopDB
GO

USE BookShopDB
GO
CREATE TABLE Authors
(	
	AuthorID smallint IDENTITY(101, 1) NOT NULL,
	FirstName varchar(30) NOT NULL DEFAULT 'unknown', 
	LastName varchar(30) NOT NULL,
	YearBorn char(4) NOT NULL DEFAULT 'N/A', 
	YearDied char(4) NOT NULL DEFAULT 'N/A',
	Description varchar(200) NOT NULL DEFAULT 'N/A'   
)
GO
CREATE TABLE Books 
(
	TitleID char(8) NOT NULL, 
	Title varchar(70) NOT NULL, 
	Publisher varchar(50) NOT NULL DEFAULT 'N/A', 
	PubDate char(4) NOT NULL DEFAULT 'N/A', 
	Edition char(4) NOT NULL DEFAULT 'N/A', 
	Cost money NOT NULL, 
	SRP money NOT NULL, 
	ConditionID tinyint NOT NULL,
	Sold bit NOT NULL DEFAULT '0', 
) 
GO
CREATE TABLE BookAuthors
(
	AuthorID smallint NOT NULL,
	TitleID char(8) NOT NULL
)
GO
CREATE TABLE BookCondition
(
	ConditionID	tinyint NOT NULL,
	ConditionName Char(10) NOT NULL,
	Description varchar(50) NOT NULL DEFAULT 'N/A'
)
GO
CREATE TABLE Positions 
( 
	PositionID tinyint NOT NULL, 
	Title varchar(30) NOT NULL,
	JobDescrip varchar(80) NOT NULL DEFAULT 'N/A' 
) 
GO
CREATE TABLE Employees 
( 
	EmployeeID smallint IDENTITY NOT NULL, 
	FirstName varchar(30) NOT NULL, 
	LastName varchar(30) NOT NULL, 
	Addrees1 varchar(60) NOT NULL,
	Address2 varchar(60) NOT NULL DEFAULT 'N/A', 
	City varchar(15) NOT NULL, 
	State char(2) NOT NULL, 
	Zip varchar(12) NOT NULL, 
	Phone varchar(24) NOT NULL, 
	DOB datetime NOT NULL, 
	HireDate datetime NOT NULL, 
	PositionID tinyint NOT NULL 
) 
GO
CREATE TABLE Customers 
( 
	CustomerID smallint IDENTITY(10, 1) NOT NULL, 
	FirstName varchar(30) NOT NULL DEFAULT 'unknown', 
	LastName varchar(30) NOT NULL DEFAULT 'unknown', 
	Phone varchar(24) NOT NULL DEFAULT 'unknown', 
	Address1 varchar( 60) NOT NULL DEFAULT 'unknown', 
	Address2 varchar(60) NOT NULL DEFAULT 'unknown', 
	City varchar(15) NOT NULL DEFAULT 'unknown', 
	State varchar(7) NOT NULL DEFAULT 'unknown', 
	Zip varchar(12) NOT NULL DEFAULT 'unknown' 
) 
GO
CREATE TABLE OrderStatus 
( 
	StatusID tinyint NOT NULL, 
	StatusDescrip varchar(25) NOT NULL 
) 
GO
CREATE TABLE FormOfPayment 
( 
	PaymentID tinyint NOT NULL, 
	PaymentDescrip varchar(12) NOT NULL 
)
GO
CREATE TABLE Orders 
( 
	OrderID smallint IDENTITY NOT NULL, 
	CustomerID smallint NOT NULL, 
	EmployeeID smallint NOT NULL, 
	Amount money NOT NULL DEFAULT 0, 
	OrderDate datetime NOT NULL, 
	DeliveryDate datetime NULL, 
	PaymentID tinyint NOT NULL, 
	StatusID tinyint NOT NULL 
)
GO
CREATE TABLE BookOrders 
( 
	OrderID smallint NOT NULL, 
	TitleID char(8) NOT NULL
)  
GO

USE BookShopDB
GO
ALTER TABLE Authors 
	ADD CONSTRAINT authors_pk PRIMARY KEY (AuthorID)
	GO 
ALTER TABLE Books 
	ADD CONSTRAINT books_pk PRIMARY KEY (TitleID) 
GO
ALTER TABLE BookAuthor 
	ADD CONSTRAINT bookauthors_pk PRIMARY KEY (AuthorID, TitleID) 
GO
ALTER TABLE BookCondition 
	ADD CONSTRAINT bookcondition_pk PRIMARY KEY (ConditionID)
GO
ALTER TABLE BookOrders 
	ADD CONSTRAINT bookorders_pk PRIMARY KEY (OrderID, TitleID) 
GO
ALTER TABLE Orders 
	ADD CONSTRAINT orders_pk PRIMARY KEY (OrderID) 
GO
ALTER TABLE Customers 
	ADD CONSTRAINT customers_pk PRIMARY KEY (CustomerID) 
GO
ALTER TABLE Employees 
	ADD CONSTRAINT employees_pk PRIMARY KEY (EmployeeID) 
GO
ALTER TABLE Positions 
	ADD CONSTRAINT positions_pk PRIMARY KEY (PositionID) 
GO
ALTER TABLE OrderStatus 
	ADD CONSTRAINT orderstatus_pk PRIMARY KEY (StatusID) 
GO
ALTER TABLE FormOfPayment 
	ADD CONSTRAINT formofpayment_pk PRIMARY KEY (PaymentID) USE BookShopDB
GO

USE BookShopDB
GO
ALTER TABLE Books 
	ADD CONSTRAINT conditionid_fk FOREIGN KEY (ConditionID) 
	REFERENCES BookCondition (ConditionID) 
GO
ALTER TABLE BookAuthors 
	ADD CONSTRAINT authorid_fk FOREIGN KEY (AuthorID) 
	REFERENCES Authors (AuthorID) 
GO
ALTER TABLE BookAuthors 
	ADD CONSTRAINT titleid_fk FOREIGN KEY (TitleID) 
	REFERENCES Books (TitleID)
GO
ALTER TABLE BookOrders 
	ADD CONSTRAINT orderid_fk FOREIGN KEY (OrderID) 
	REFERENCES Orders (OrderID) 
GO
ALTER TABLE BookOrders 
	ADD CONSTRAINT titleid2_fk FOREIGN KEY (TitleID) 
	REFERENCES Books (TitleID)
GO
ALTER TABLE Orders 
	ADD CONSTRAINT customerid_fk FOREIGN KEY (CustomerID) 
	REFERENCES Customers (CustomerID)
GO
ALTER TABLE Orders 
	ADD CONSTRAINT employeeid_fk FOREIGN KEY (EmployeeID) 
	REFERENCES Employees (EmployeeID) 
GO
ALTER TABLE Orders 
	ADD CONSTRAINT paymentid_fk FOREIGN KEY (PaymentID) 
	REFERENCES FormOfPayment (PaymentID) 
GO
ALTER TABLE Orders 
	ADD CONSTRAINT statusid_fk FOREIGN KEY (StatusID) 
	REFERENCES OrderStatus (StatusID) 
GO
ALTER TABLE Employees 
	ADD CONSTRAINT positionid_fk FOREIGN KEY (PositionID) 
	REFERENCES Positions (PositionID)
GO

USE BookShopDB
GO
ALTER TABLE Customers
	ADD CONSTRAINT checknames_ck 
	CHECK (FirstName NOT LIKE 'unknown' OR LastName NOT LIKE 'unknown')
GO
ALTER TABLE Authors
	ADD CONSTRAINT authors1_ck 
	CHECK (YearBorn LIKE ('[1-2][0,6-9][0-9][0-9]') OR (YearBorn = 'N/A'))
GO
ALTER TABLE Authors
	ADD CONSTRAINT authors2_ck 
	CHECK (YearBorn NOT LIKE '[1][0][0-9][0-9]')
GO
ALTER TABLE Authors
	ADD CONSTRAINT authors3_ck 
	CHECK (YearBorn NOT LIKE '[2][6-9][0-9][0-9]')
GO
ALTER TABLE Authors
	ADD CONSTRAINT authors4_ck 
	CHECK (YearDied LIKE ('[1-2][0,6-9][0-9][0-9]') OR (YearBorn = 'N/A'))
GO
ALTER TABLE Authors
	ADD CONSTRAINT authors5_ck 
	CHECK (YearDied NOT LIKE '[1][0][0-9][0-9]')
GO
ALTER TABLE Authors
	ADD CONSTRAINT authors6_ck 
	CHECK (YearDied NOT LIKE '[2][6-9][0-9][0-9]')
GO
ALTER TABLE Books
	ADD CONSTRAINT books1_ck 
	CHECK (PubDate LIKE ('[1-2][0,6-9][0-9][0-9]') OR (PubDate = 'N/A'))
GO
ALTER TABLE Books
	ADD CONSTRAINT books2_ck 
	CHECK (PubDate NOT LIKE '[1][0][0-9][0-9]')
GO
ALTER TABLE Books
	ADD CONSTRAINT books3_ck 
	CHECK (PubDate NOT LIKE '[2][6-9][0-9][0-9]')
GO

USE BookShopDB 
GO
BULK INSERT FormOFPayment 
FROM 'D:\BookShopDB\DATA\FormOfPayment.txt' 
WITH (DATAFILEÒYPE = 'CHAR');
GO
BULK INSERT OrderStatus 
FROM 'D:\BookShopDB\DATA\orderstatus.txt' 
WITH (DATAFILEÒYPE = 'CHAR') 
GO
BULK INSERT Positions 
FROM 'D:\BookShopDB\DATA\Positions.txt' 
WITH (DATAFILEÒYPE = 'CHAR') 
GO
BULK INSERT Authors 
FROM 'D:\BookShopDB\DATA\Authors.txt' 
WITH (DATAFILETYPE = 'CHAR') 
GO
BULK INSERT Books 
FROM 'D:\BookShopDB\DATA\Books.txt' 
WITH (DATAFILETYPE = 'CHAR') 
GO
BULK INSERT Customers 
FROM 'D:\BookShopDB\DATA\Customers.txt' 
WITH (DATAFILEÒYPE = 'CHAR') 
GO
BULK INSERT Employees 
FROM 'D:\BookShopDB\DATA\Employees.txt' 
WITH (DATAFILETYPE = 'CHAR') 
GO
BULK INSERT BookCondition 
FROM 'D:\BookShopDB\DATA\BookCondition.txt' 
WITH (DATAFILETYPE = 'CHAR') 
GO
BULK INSERT BookAuthors 
FROM 'D:\BookShopDB\DATA\BookAuthors.txt' 
WITH (DATAFILETYPE = 'CHAR') 
GO


