发布时间 : 星期日 文章《数据库技术及应用》课程大作业-2014 - 图文更新完毕开始阅读2f37944ffd4ffe4733687e21af45b307e871f9fd
外码:学号(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. 数据库操作