db1

图书借阅系统

最新推荐文章于 2025-10-21 22:09:11 发布

原创最新推荐文章于 2025-10-21 22:09:11 发布 · 556 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#table #null #date #database

CREATE DATABASE Labery_3109001382
ON PRIMARY(NAME=Labery_3109001382,
FILENAME='D:\DB\Labery_3109001382.mdf',
SIZE=4MB,
MAXSIZE=6MB,
FILEGROWTH=20%
)

use Labery_3109001382

CREATE TABLE Book
(
bno        CHAR(8) NOT NULL,
category   varchar(10) ,
title      varchar(40) NOT NULL,
press      varchar(30) NOT NULL,
book_year Int NOT NULL,
author     varchar(20) ,
price      decimal(7,2) NOT NULL,
book_total Int NOT NULL
)

CREATE TABLE Card
(
cno        CHAR(7) NOT NULL,
name       char(8) NOT NULL,
department varchar(40),
type       char(1) NOT NULL,

)

CREATE TABLE Borrow
(
cno        CHAR(7) NOT NULL,
bno       char(8) NOT NULL,
borrow_date smalldatetime default getdate() NOT NULL,////
return_date smalldatetime,

)
////1
ALTER TABLE Book ADD CONSTRAINT Book_PRIMARY PRIMARY KEY(bno)
ALTER TABLE Card ADD CONSTRAINT Card_PRIMARY PRIMARY KEY(cno)
ALTER TABLE Borrow ADD CONSTRAINT Borrow_PRIMARY PRIMARY KEY(cno,bno)
/////2
ALTER TABLE Borrow   ADDCONSTRAINT BNO_FOREIGN_KEY FOREIGN key(bno)

REFERENCES Book(bno)
ALTER TABLE Borrow ADDCONSTRAINT CNO_FOREIGN_KEY
FOREIGN key(cno) REFERENCES Card(cno)

////3
ALTER TABLE Card ADD CONSTRAINT TYPE_CHECK CHECK(type='T' OR

type='G' OR type='U' OR type='O')

////4
ALTER TABLE Borrow ALTER COLUMN borrow_date default getdate()

///5
CREATE UNIQUE INDEX BOOK_INDEX ON Book(bno)
CREATE UNIQUE INDEX CARD_INDEX ON Card(cno)
CREATE UNIQUE INDEX BORROW_INDEX ON Borrow(borrow_date)