-- 1-> ******Help informations ******
--------------------------------------------------------------------------------------------
-- sp_helpdb    ...................................List all databases  in your computer 
-- sp_helpdb  BooksManSys .........................List database's informations
-- sp_renamedb 'BooksManSys' , 'newname' ..........Rename BooksManSys 
-- drop database BooksManSys ......................delete BooksMansSys
-- drop table tableNames ..........................delete tableNames
-- sp_help  tVip..............................List  table tvip'information
--------------------------------------------------------------------------------------------

-- 2-> ****** Create database ******
--------------------------------------------------------------------------------------------
-- Modify
-- sp_help drop database Library
use master
go

if exists (select * from dbo.sysdatabases where name = 'Library') 
drop database Library
create database Library
	on 
	(name='Librarydat',
	 filename='d:\Librarions ******
--------------------------------------------------------------------------------------------
-- sp_helpdb    ...................................List all databases  in your computer 
-- sp_helpdb  BooksManSys .........................List database's informations
-- sp_renamedb 'BooksManSys' , 'newname' ..........Rename BooksManSys 
-- drop database BooksManSys ......................delete BooksMansSys
-- drop table tableNames ..........................delete tableNrators 
-- select * from tOperators
use Library
go
create table tOperators 
	(cOperatorID char(5), --PRIMARY KEY CHECK (cOperatorID like '[A-Z][1-9][0-9][0-9][0-9]')
			     --DEFAULT 'Y0OO1',
	 cOperatorName varchar(10),
	 cOperatorPassword varchar(6),
	 cOperatorGrade varchar(10),
	 cOperatorAddTime varchar(10) 
 	 )

--insert values 
insert  tOperators  values( 'M0001','Admin','1','管理員', '2000-01-01 ' )
insert  tOperators  values( 'O0002','Ha Ha','2','普通', '2000-02-01 ' )
insert  tOperators  values( 'O0003','大辮子','3','普通', '2000-03-01 ' )
insert  tOperators  values( 'O0004','叮當','4','普通', '2000-04-01 ' )
insert  tOperators  values( 'O0005','蟈蟈','5','普通', '2000-05-01 ' )
insert  tOperators  values( 'O0006','小燕子','6','普通', '2000-06-01 ' )
--Table 2:   tVip
-------------------------------------------
-- Modify
-- drop table tVip
-- select * from tVip
use Library
go
create table tVip
	(cVipID char(6) PRIMARY KEY ,
	 cVipName varchar (10) NOT NULL,
	 cVipSex char (2) ,
	 cVipID_card char (15) ,						
	 cVipPhone varchar(15),				
	 cVipAddress varchar (25),
	 cVipAddTime varchar (10) ,
	 cVipEndTime varchar (10)	 
	 )
insert tVip values('0001','劉儷','女','36029421685','029-88219498','西安市電子城210所','2004-01-01','2005-01-01')
insert tVip values('0002','阿廈麗','女','360296236555','0394-82677666','河南省周口市','2003-03-13','2004-03-13')
insert tVip values('0003','金正武','男','560298456755','0370-2314897','河南省洛陽市','2005-01-01','2006-01-01')
insert tVip values('0004','尚西戶','男','860298623755','029-82381397','西安市交大商場','2002-05-01','2003-05-01')
insert tVip values('0005','趙平','女','8606292356755','0577-2314897','浙江省寧波市','2001-07-01','2002-07-01')
insert tvip values('0006','趙亞兄','女','860292356725','0577-2314897','湖南省長沙市','2001-07-01','2002-07-01')


--Table 3:   tBorrow
----------------------------------------------
-- Modify
-- drop table tBorrow
-- select * from  tBorrow
use Library
go
create table tBorrow
	(cBorrowID int PRIMARY KEY  IDENTITY(0001,1) ,
	 cVipID char(6)  ,--FOREIGN KEY  REFERENCES tVip(cVipId) NOT NULL  ,	
	 cBooksID char (7) ,-- FOREIGN KEY REFERENCES tBooks(cBooksID) NOT NULL ,	
	 cBorrowTime varchar(10) default getdate(),
	 cReturnTime varchar(10) 
	 )
insert  tBorrow values('0001','0001','2003-01-01',NULL)
insert  tBorrow values('0002','0002' ,'2003-02-01',NULL)
insert  tBorrow values('0003','0003' ,'2003-03-01',NULL)
insert  tBorrow values('0004','0004' ,'2003-04-01',NULL)
insert  tBorrow values('0005','0005' ,'2003-05-01',NULL)
insert  tBorrow values('0006','0006' ,'2003-06-01',NULL)

--Table 4:   tBooks
-----------------------------------------------
-- Modify
-- drop table tBooks
-- select * from  tBooks 
use Library
go
create table tBooks 
	(cBooksID char(7) PRIMARY KEY,
	 cBooksName varchar (30) NOT NULL,	 
	 cBooksISBN char (15),	
   	 cBooksAuthor char (10),
	 cBooksPublisher varchar (20),
	 cBooksType varchar (16),
	 smBooksPrice smallmoney ,
   	 iBooksStoreQuantity int default 1,
	 iBooksLeftQuantity int default 1,	
 	 )
insert tBooks values('0001','計算機基礎','ISN001','路琳','機械工業出版社','計算機',23.50,1,1)
insert tBooks values('0002','高等數學','ISN001','張阿麗','西安理工大學出版社','數學',10.50,1,1)
insert tBooks values('0003','微機原理','ISN001','郭永達','西安交通大學出版社','計算機',16.00,1,1)
insert tBooks values('0004','數據庫原理','ISN001','松礪','清華大學出版社','計算機',32.00,1,1)
insert tBooks values('0005','古代文學史','ISN001','萇蒙','高等教育出版社','計算機',18.6,1,1)
insert tBooks values('0006','一米陽光','ISN001','武輝','陜西人民出版社','文史',15.3,1,1)
select * from tvip
select * from tBooks
select * from tBorrow
select tBorrow.cBorrowID,tBorrow.cBorrowTime,tBorrow.cBooksStatus, tvip.cVipID,tvip.cVipName,tBooks.cBooksID,tBooks.cBooksName,tBooks.cBooksAuthor,tBooks.smBooksPrice,tBooks.cBooksPublisher from tVip ,tBooks,tBorrow where tvip.cVipId=tBorrow.cVipId AND tBorrow.cBooksId =tBooks.cBooksId