--[建立以上資料庫的參考語法]
CREATE DATABASE [圖書借閱資料庫7-1]
ON PRIMARY
   (name=P1,filename='C:\Databases\D1\P1.MDF') ,
FILEGROUP G1
   (name=G11,filename='C:\Databases\D1\G11.NDF') ,
   (name=G12,filename='C:\Databases\D2\G12.NDF')
LOG ON
   (name=LogFile,filename='C:\Databases\D2\LogFile.LDF')



USE [圖書借閱資料庫7-1]

--1.建立『科系資料』
--由於此資料表的主要鍵(Primary Key)為『科系代號』，所以在該資料行後面多一個資料行限制為PRIMARY KEY，只要被設為PRIMARY KEY就一定不可為空值，所以不用特別再限制。而『科系名稱』不可為空值，所以資料行後面的限制多一個NOT NULL。

CREATE TABLE 科系資料
(
  科系代號 CHAR(3)       PRIMARY KEY ,
  科系名稱 VARCHAR(20)  NOT NULL ,
  位置     VARCHAR(10)
) ;

--2.建立『學生資料』
--此資料表與前一資料表的差異，在於『科系代號』的屬性參考『科系資料』資料表的主要索引鍵『科系代號』，並且限制此關聯為ON DELETE NO ACTION & ON UPDATE  CASCADE，所以在資料行限制處，多一個 『REFERENCES 科系資料(科系代號) ON DELETE NO ACTION ON UPDATE CASCADE』

CREATE TABLE 學生資料
(
   學號     CHAR(8)       PRIMARY KEY ,
   科系代號 CHAR(3)       REFERENCES 科系資料(科系代號)
                           ON DELETE  NO ACTION
                           ON UPDATE  CASCADE ,
   姓名     VARCHAR(20)  NOT NULL ,
   年級     INT           NOT NULL
) ;

--3.建立『出版公司』

CREATE TABLE 出版公司
(
  出版公司代號 INT            PRIMARY KEY ,
  出版公司名稱 VARCHAR(20)   NOT NULL ,
  聯絡人       VARCHAR(10)
) ;

--4.建立『書籍資料』

CREATE TABLE 書籍資料
(
  書籍代號     VARCHAR(6)   PRIMARY KEY ,
  出版公司代號 INT           REFERENCES 出版公司(出版公司代號) ,
  書籍名稱     VARCHAR(30)  NOT NULL ,
  出版日期     DATETIME      NOT NULL ,
  庫存量       INT
) ;

--5.建立『作者資料』

CREATE TABLE 作者資料
(
  作者代號 INT            PRIMARY KEY ,
  作者姓名 VARCHAR(20)   NOT NULL ,
  聯絡電話 VARCHAR(20) ,
  email     VARCHAR(30)
) ;

--6.建立『作者著作』
--此資料表較為特殊之處在於主要鍵是由兩個屬性『書籍代號』+『作者代號』所組成，所以此限制不可定義於『屬性定義』的區塊內，必須定義於『資料表限制』區塊內。

CREATE TABLE 作者著作
(
  書籍代號 VARCHAR(6)  REFERENCES 書籍資料(書籍代號) ,
  作者代號 INT           REFERENCES 作者資料(作者代號) ,
  PRIMARY KEY (書籍代號, 作者代號)
) ;

--7.建立『借閱紀錄』
--這個資料表是在此實體資料模型中較為複雜的一個，除了『學號』與『書籍代號』為外部索引鍵(Foreign Key)之外，『借閱日期』與『預計歸還日期』皆有預設值。

--『借閱日期』的預設日期為當日，可利用MS SQL Server的內建日期函數GETDATE( )來取得當日日期，而『預計歸還日期』則利用GETDATE( )+7來表示預計七日後歸還。

--最後，此資料表所歸屬的檔案群組為G1，所以在最後的括弧外的『指定檔案群組』區塊內加上 ON G1。

CREATE TABLE 借閱紀錄
(
  學號         CHAR(8)      REFERENCES 學生資料(學號) ,
  書籍代號     VARCHAR(6)  REFERENCES 書籍資料(書籍代號)
ON UPDATE CASCADE
ON DELETE CASCADE ,
  借閱日期     DATETIME    DEFAULT GETDATE( )  NOT NULL,
  預計歸還日期 DATETIME     DEFAULT GETDATE( )+7  NOT NULL,
  實際歸還日期 DATETIME ,
  PRIMARY KEY (學號, 書籍代號, 借閱日期)
)
ON G1 ;

--依序刪除所有前面所建立之資料表

DROP TABLE [圖書借閱資料庫7-1].dbo.借閱紀錄 ;
DROP TABLE [圖書借閱資料庫7-1].dbo.作者著作 ;
DROP TABLE [圖書借閱資料庫7-1].dbo.作者資料 ;

DROP TABLE  dbo.書籍資料 ;
DROP TABLE  dbo.出版公司 ;

DROP TABLE 學生資料 ;
DROP TABLE 科系資料 ;