createearnestbankobjects.sql

学生用银行数据库及部分银行数据库中的部分数据

USE EarnestBank
go
/* -- Creating Objects for Earnest Bank*/

raiserror('Now at the create table section ....',0,1)
Go


raiserror('Creating  EarnestBank_Registration....',0,1)
create table Registration
(
cRegistration_id int IDENTITY(100,1) not null primary key,
cFirst_name char(50) not null,
cLast_name char(50) not null,
cAddress char(50) not null,
cAccount_type char(30) not null,
mAnnual_income money null,
cPhone_no char(10) not null
)
go



raiserror('Creating EarnestBank_Account_Holder....',0,1)
create table Account_Holder
(
cAccount_id char(10) not null primary key,
cRegistration_id int not null references Registration(cRegistration_id),
mBalance money not null
)
go

raiserror('Creating  EarnestBank_Transaction....',0,1)
create table Account_Holder_Transaction
(
cAccount_id char(10) not null references Account_Holder(cAccount_id),
dDate_of_transaction datetime not null,
vcParticulars varchar(50) not null,
cCheck_no char(10) null,
mAmount money not null 
)
GO



raiserror('Creating  EarnestBank_Counter....',0,1)
create table Counter
(
cCounter_id char(10) not null primary key,
cAddress char(50) not null,
mCashBalance money not null,
mMinBalance money not null
)
GO

raiserror('Creating  EarnestBank_Counter_Transaction....',0,1)
create table Counter_Transaction
(
cTransaction_id char(10) not null primary key,
cCounter_id char(10) not null references Counter(cCounter_id),
cAccount_id char(10) not null references Account_Holder(cAccount_id),
dDate_of_transaction datetime not null,
mDebit_amount money  null,
mCredit_amount money null
)
go

raiserror('Creating  EarnestBank_Loan....',0,1)
create table Loan
(
cLoan_id char(10) not null primary key,
cLoan_type char(15) not null,
intRate int not null,
intLoan_period int not null,
intInstalment int not null
)
go

raiserror('Creating  EarnestBank_Loan_Registration....',0,1)
create table Loan_Registration
(
cLoan_Registration_id char(10) not null primary key,
cFirst_name char(10) not null,
cLast_name char(10) not null,
cAddress char(60) not null,
cLoan_type char(10) not null,
cPhone char(10) not null,
mAnnual_income money not null,
mAmount_applied money not null,
)
go

raiserror('Creating  EarnestBank_Loan_Details....',0,1)
create table Loan_Details
(
cLoan_id char(10) not null references Loan(cLoan_id),
cLoan_Registration_id char(10) not null references Loan_Registration(cLoan_Registration_id),
mLoan_amount money not null,
mLoan_amount_repaid money not null,
mBalance money not null,
dDate_of_sanction datetime not null,
intBal_no_installments int not null
)
go


raiserror('Creating  EarnestBank_Login....',0,1)
create table Login
(
cAccount_id char(10) not null references Account_Holder(cAccount_id),
cPin_no char(10) not null
)
go


raiserror('Inserting  EarnestBank_Registration....',0,1)
insert into Registration values('Diana','Helbert','Bay Road, Singapore 5892', 'Savings', 340000, '02163467')
insert into Registration values('Jim','Hopkins','Stockholm street, SW 678902', 'Current', 600000, '032234')
insert into Registration values('Diana','Hayden','10 Hays Street,NY 30570', 'Savings', 240000, '045678')
insert into Registration values('Bob','Meakins','23 SunLey House, CA 45678', 'Fixed Deposit', 400000, '02145')
insert into Registration values('Laurry','Helbert','Rock St.Seattle 234657', 'Fixed Deposit', 450000, '0416348')
insert into Registration values('Shane' , 'Cornway', '25,Mackin St. CA 31435', 'Savings', 200000, '041656')
go





raiserror('Inserting  EarnestBank_Account_Holder....',0,1)
insert into Account_Holder values('AH0001',100,500000)
insert into Account_Holder values('AH0002',101,100000)
insert into Account_Holder values('AH0003',102,100000)
insert into Account_Holder values('AH0004',103,150000)
insert into Account_Holder values('AH0005',104,145000)
go

raiserror('Inserting  EarnestBank_Account_Holder_Transaction....',0,1)
insert into Account_Holder_Transaction values('AH0001',07/24/01,'Cheque deposit',234123,2250)
insert into Account_Holder_Transaction values('AH0002',07/14/01,'Cheque deposit',234455,5000)
insert into Account_Holder_Transaction values('AH0003',07/04/01, 'Withdrawal',0,36000)
insert into Account_Holder_Transaction values('AH0004',07/24/01, 'Withdrawal',0,3000)
insert into Account_Holder_Transaction values('AH0005',07/09/01, 'Cheque deposit',289756,165000)
go

raiserror('Inserting  EarnestBank_Counter....',0,1)
insert into Counter values('CT0001','Hummingway Street NY', 8000, 50000)
insert into Counter values('CT0002','Timothy Square NY', 9000, 50000)
insert into Counter values('CT0003','Sunley Estate CA', 15000, 70000)
insert into Counter values('CT0004','Bayer House LS', 50000, 50000)
insert into Counter values('CT0005','Mackinnon Street CA', 45000, 80000)
go

raiserror('Inserting  EarnestBank_Counter_Transaction....',0,1)
insert into Counter_Transaction values('TD0001','CT0002','AH0002','05/2/01',0,11000)
insert into Counter_Transaction values('TD0002','CT0001','AH0005','07/24/01',0,20000)
insert into Counter_Transaction values('TD0003','CT0003','AH0001','07/12/01',25000,0)
insert into Counter_Transaction values('TD0004','CT0004','AH0004','08/24/01',0,50000)
insert into Counter_Transaction values('TD0005','CT0005','AH0003','02/24/01',70000,0)
go

raiserror('Inserting  EarnestBank_Loan....',0,1)
insert into Loan values('L0001','PL',19,3,36)
insert into Loan values('L0002','CL',20,5,60)
insert into Loan values('L0003','HL',15,10,120)
insert into Loan values('L0004','SL',10,7,94)
insert into Loan values('L0005','BL',21,5,60)
go

raiserror('Inserting  EarnestBank_Loan_Registration....',0,1)
insert into Loan_Registration values('LD001','Jonas','Smith','15, Mackinnon Street CA 31435','PL','91434556',500000,100000)
insert into Loan_Registration values('LD002','Jullianne','Andrews','27A, Bayer House LS 22534','HL','91535445',700000,400000)
insert into Loan_Registration values('LD003','Bob','Anderson','143/C, Hummingway Street NY 259CA','BL','97645676',1400000,400000)
insert into Loan_Registration values('LD004','Othello','Darwin','23, Sunley Estate CA 13246','CL','45634523',500000,200000)
insert into Loan_Registration values('LD005','Jesse','Barkins','45QS, Timothy Square NY 147CG','SL','23456734',100000,75000)
go

raiserror('Inserting  EarnestBank_Loan_Details....',0,1)
insert into Loan_Details values('L0001','LD002',300000,50000,250000,'04/2/01',30)
insert into Loan_Details values('L0002','LD003',400000,400000,0,'05/15/01',0)
insert into Loan_Details values('L0003','LD005',1200000,400000,800000,'06/12/01',34)
insert into Loan_Details values('L0004','LD004',300000,275000,25000,'06/08/01',1)
insert into Loan_Details values('L0003','LD001',350000,300000,50000,'06/06/01',2)
go



raiserror('Inserting  EarnestBank_Login....',0,1)
insert into Login values('AH0001','1001')
insert into Login values('AH0002','1005')
insert into Login values('AH0003','1251')
insert into Login values('AH0004','6129')
insert into Login values('AH0005','6613')
go





raiserror('Database is now ready for use...',0,1)
go