db1

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)