--準備：創建數據庫
use master

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'LibrarySys')
	DROP DATABASE [LibrarySys]
GO

CREATE DATABASE LibrarySys
ON (NAME = N'LibrarySys_Data', FILENAME = N'E:\database\LibrarySys_Data.MDF',SIZE = 5, FILEGROWTH = 10%) 
LOG ON (NAME = N'LibrarySys_Log', FILENAME = N'E:\database\LibrarySys_Log.LDF',SIZE = 1, FILEGROWTH = 10%)
GO

use LibrarySys
go



---------------
create table Librarian
(
       LibrarianID  char(10) primary key,
       LibrarianPassword varchar(20) not null,
       LibrarianName varchar(20) not null,
       Address  varchar(20) not null,
       Telephone  varchar(15) ,
)
create table  Library
(    
       LibName  char(20)  primary key,
       LibID    smallint not null,
       Lib_CurID char(10)references Librarian(LibrarianID),
)
Create  table  Book
(
       BookID varchar(12)  primary key,
       BookName  varchar(20) not null,
       ISBN    varchar(15) not null,
       PubHouse  varchar(20) not null,
       PubDate   Datetime,
       Version   char(1),
       Price   char(4) not null,
       InDate    DateTime,
       OwnedDate smallint,
       Location  char(20) ,
       AllStoreNum  smallint  not null,
       NowStoreNum  smallint not null,
)
       
create table Reader
(     
      ReaderID char(10) primary key,
      ReaderPassword  varchar(20) not null ,
      ReaderName varchar(20) not null,      
      Telephone char(15),
      NowBorrowNum char(2) default(0),
      AuthoredNum  char(2) ,
      PenaltyAccumlate  float default(0),
)
create table  Teacher
(
   ReaderID char(10) primary key references Reader(ReaderID),
   TeacherID char(10) not null,
      OfSchool   varchar(20) ,
 )
create  table  Student
(
   ReaderID char(10) primary key references Reader(ReaderID),
   StudentID char(10) not null,
   Major   varchar(20) not null,
)
create table PenaltyForm
( 
    ReaderID char(10)  references  Reader(ReaderID),
    BookID   varchar(12)   references Book(BookID),
    DueDays  smallint not null ,
    Penalty  float not null,
    Reason   varchar(20),
   primary key(ReaderID,BookID ),
)

create table  WorkIn
(
   LibrarianID char(10), 
  LibName  char(20),
  Salary float,
  foreign key (LibrarianID ) references Librarian(LibrarianID ),
  foreign key (LibName)   references  Library(LibName),
)
create table  StoredIn
(
  BookID varchar(12) references Book(BookID),
  LibName char(20) references  Library(LibName),
)
create table Borrow
(
  ReaderID char (10) references Reader(ReaderID),
  BookID  varchar(12) references Book(BookID),
  BorrowDate DateTime not null,
  DueDate  dateTime,
)