use master
go

if exists (select * 
	   from   master..sysdatabases 
	   where  name = N'BankEF')
	drop database BankEF
go

create database BankEF
go

use BankEF
go

create table Contacts
(
	ContactID int identity(40000,1) not null primary key,
	City nvarchar(20) not null,
	[Name] nvarchar(50) not null,
	Email nvarchar(50)
)

create table Customers
(
	CustomerID int not null primary key,
	JoinDate datetime not null
)

create table BusinessCustomers
(
	CustomerID int not null primary key,
	Fax nvarchar(10)
)
go

create table Accounts
(
	AccountID int identity(270000,1) primary key,
	CustomerID int not null,
	Type nvarchar(10) null,
	CreationDate datetime not null default getdate()
)
go

create table Transactions
(
	TransactionID bigint identity(1,1) primary key,
	AccountID int not null,
	Amount float not null default 0.0,
	[Date] datetime not null default getdate()
) 
go

-- Relations
alter table Customers 
add constraint FK_Customers_Contacts 
foreign key (CustomerID)
references Contacts (ContactID)
go

alter table BusinessCustomers 
add constraint FK_BusinessCustomers_Customers
foreign key (CustomerID)
references Customers (CustomerID) 
go

alter table Accounts 
add constraint FK_Accounts_Customers 
foreign key (CustomerID)
references Customers (CustomerID)
go

alter table Transactions 
add constraint FK_Transactions_Accounts
foreign key (AccountID)
references Accounts (AccountID)
go 

-- Generate Contacts
insert into BankEF.dbo.Contacts ([Name], City, Email)
select [Name], City, Email from Bank.dbo.Customers
select * from Contacts

-- Generate Customers
insert into Customers (CustomerID, JoinDate) 
select ContactID, getdate() from Contacts
select * from Customers

-- Generate Business Customers
insert into BusinessCustomers (CustomerID, Fax) 
select CustomerID, '03-' + cast(CustomerID as nvarchar(6))
from Bank.dbo.Customers
where IsBusiness = 1
select * from BusinessCustomers

-- Generate 
insert into Accounts (CustomerID, Type) 
select CustomerID, [Type] from Bank.dbo.Accounts
select * from Accounts