建立LIBRARY数据库代码(简化)和关系图.doc

3建立Library数据库和表代码 3--CREATE DATABASE libraryCREATE DATABASE 图书馆ON PRIMARY( NAME = 'library', FILENAME = 'H:\Library\library.mdf' , SIZE = 4 , MAXSIZE = UNLIMITED, FILEGROWTH = 1 ) LOG ON ( NAME = 'library_log', FILENAME = 'H:\Library\library_log.ldf' , SIZE = 1 , MAXSIZE = 2048GB , FILEGROWTH = 10%)GO-- 1. CREATE BOOK CREATE TABLE dbo.BOOK(BOOK_ID nvarchar(8) NOT NULL PRIMARY KEY, BOOK_NAME nvarchar(50) NULL, AUTHOR nvarchar(30) NULL, PRESS nvarchar(50) NULL, PRESS_DATE datetime NULL, FLAG_BORROW nvarchar(1) NULL, PRICE money NULL, book_type varchar(10) NULL, ) --2. CREATE CLERKCREATE TABLE dbo.CLERK(CLERK_ID nvarchar(8) NOT NULL PRIMARY KEY,NAME nvarchar(8) NULL,ID_CARD nvarchar(18) NULL,PASSWORD nvarchar(30) NULL,OFFICIER nvarchar(30) NULL DEFAULT '管理员')--3. CREATE READERCREATE TABLE dbo.READER( READER_ID nvarchar(8) NOT NULL PRIMARY KEY, NAME nvarchar(8) NOT NULL, IDCARD nvarchar(18) NULL, MAXNUM_CAN_BORROW smallint NULL, FLAG_BORROW nvarchar(2) NULL, sex bit NULL,) -- 4. CREATE BORROWCREATE TABLE dbo.BORROW( READER_ID nvarchar(8) NULL, BOOK_ID nvarchar(8) NULL, BORROW_DATE datetime NULL, B_CLERK_ID nvarchar(8) NULL,ID int IDENTITY(1,1) NOT NULL PRIMARY KEY, R_CLERK_ID nvarchar(8) NULL, RETURN_DATE datetime NULL, FLAG_RETURN nchar(2) NULL CONSTRAINT DF_BORROW_FLAG_RETURN DEFAULT ('N'),CONSTRAINT FK_BORROW_BOOK FOREIGN KEY(BOOK_ID) REFERENCES dbo.BOOK(BOOK_ID), CONSTRAINT FK_BORROW_CLERK FOREIGN KEY(B_CLERK_ID) REFERENCES dbo.CLERK (CLERK_ID),CONSTRAINT FK_BORROW_R_CLERK FOREIGN KEY(R_CLERK_ID) REFERENCES dbo.CLERK (CLERK_ID),CONSTRAINT FK_BORROW_READER FOREIGN KEY(READER_ID) REFERENCES dbo.READER (READER_ID) )-- 5. CREATE CHUN CREATE TABLE dbo.chun( ID int NOT NULL, ISBN nvarchar(8) NULL, 书名 nvarchar(50) NULL, 作者 nvarchar(30) NULL, 版别 nvarchar(50) NULL, 出版日期 datetime NULL )) --6. CREATE FINECREATE TABLE dbo.FINE( READER_ID nvarchar(8) NULL, AMOUNT int NULL, DAYS smallint NULL, FINE_DATE datetime NULL, CLERK_ID nvarchar(8) NULL, ID int IDENTITY(1,1) NOT NULL, OK_DATE datetime NULL, FLAG_FINE nchar(2) NULL )--7. CREATE FINE_HISTORYCREATE TABLE dbo.FINE_HISTORY( READER_ID nvarchar(30) NULL, AMOUNT int NULL, DAYS smallint NULL, FINE_DATE datetime NULL, OK_DATE datetime NULL, ID int IDENTITY(1,1) NOT NULL) --8. CREATE HISTORYCREATE TABLE dbo.HISTORY( READER_ID nvarchar(30) NULL, BOOK_ID nvarchar(30) NULL, BORROW_DATE datetime NULL, RETURN_DATE datetime NULL, B_CLERK_ID nvarchar(30) NULL, R_CLERK_ID nvarchar(30) NULL, ID int IDENTITY(1,1) NOT NULL) --9. CREATE new_tableCREATE TABLE dbo.new_table( book_name nvarchar(50) NULL, author nvarchar(30) NULL, press_date datetime NULL, price money NULL) --10. CREATE stuCREATE TABLE dbo.stu( ID int NOT NULL, 学号 nvarchar(10) NULL, 姓名 nvarchar(30) NULL, 身份证号 nvarchar(30) NULL) --11. CREATE xml_bookCREATE TABLE dbo.xml_book( id int NOT NULL PRIMARY KEY,content xml NULL) --12. CREATE xmlbookCREATE TABLE dbo.xmlbook( id int NOT NULL PRIMARY KEY, xmlbook xml NULL ) LIBRARY数据库关系图。