《数据库技术及应用》课程大作业-2014 - 图文

外码:学号(stu_id或者书号(book_id 表级完整性约束:

实体完整性约束:PRIMARY KEY(stu_id,book_id 参照完整性约束:

FOREIGN KEY (stu_id REFERENCES Student(stu_id; FOREIGN KEY (book_id REFERENCES Book (book_id; 索引数据项为:学号(stu_id

4.数据定义语言

4.1基本表及其完整性定义 (1学生表(Student CREATE TABLE Student(

stu_id bigint (8 NOT NULL UNIQUE, name char(10 NOT NULL, sex char(4

CHECK(sex IN ('男','女', major varchar(20 NOT NULL, PRIMARY KEY (stu_id;

(2管理员表(Administrator CREATE TABLE Administrator (

admini_id char (10 NOT NULL UNIQUE, name char(10 NOT NULL, sex char(4

CHECK(sex IN ('男','女', admini_pass char(10 NOT NULL, contact char(11,

PRIMARY KEY (admini_id; (3 图书表(Book CREATE TABLE Book (

book_id char (10 NOT NULL UNIQUE, title varchar(20 NOT NULL, category char(10, press varchar(20, author char(10, price char(5,2, total int(4 NOT NULL, stock int(4 NOT NULL,

PRIMARY KEY (book_id,

FOREIGN KEY (admini_id REFERENCES Administrator (admini_id; (4借书记录表(Borrow CREATE TABLE Borrow (

stu_id bigint(8 NOT NULL UNIQUE, book_id char(10 NOT NULL UNIQUE, borrow_date date NOT NULL, return_date date NOT NULL, PRIMARY KEY (stu_id, PRIMARY KEY (book_id,

FOREIGN KEY (stu_id REFERENCES Student(stu_id, FOREIGN KEY (book_id REFERENCES Book(book_id;

4.2 视图定义

1、创建视图显示借书者的学号、书号、书名、借期、还期。 CREATE VIEW IS_Borrow(stu_id,book_id,title, borrow_date,return_date

AS

SELECT stu_id,book_id,title,borrow_date,return_date FROM Book,Student,Borrow

WHERE Student.stu_id=Borrow.stu_id AND Book.book_id=Borrow.book_id;

2、创建视图显示可借图书的书号、书名、作者、出版社、图书价格。CREATE VIEW IS_Book(book_id,title,author,press,price

AS

SELECT book_id,title,author,press,price FROM Book; 4.3 索引定义

(1为图书表添加索引,索引数据项为:图书名(title CREATE UNIQUE INDEX PK_ Book ON Book(title; (2为借书记录表添加索引,索引数据项为:学号(stu_id CREATE UNIQUE INDEX PK_ Borrow ON Borrow(stu_id;

5. 数据库操作