1.图书借阅系统包含以下表:
会员表(member)
图书表(title)
库存图书备份表(title_copy)
图书借阅表(borrow)
预定表(reservation)
2.表信息
1)会员表包含以下字段:
会员ID(序列号)、会员名、地址、城市、电话、加入时间(默认为系统时间)、会员ID是主键
2)图书表包含以下字段:
图书编号(序列号)、书名、书的描述信息、出版时间、作者、图书的类别(只能是'java'、'c'、'c++'、'db'、'web')图书编号为主键
3.库存备份表包含以下信息:
图书编号、库存数量(最低数量为0)、缺少数量(默认为0),图书编号为主键
4.图书借阅表包含以下字段(每个会员可以借阅多本书):
序列号、图书编号、会员ID、借阅日期(默认为系统当前日期)、还书日期、到期日期(默认为系统单签日期后的一个月),序列号为主键
5.预定表包含以下字段(每个会员可以预定多本书):
会员ID、图书编号、预定日期,会员ID、图书编号、预定日期为联合主键
3.操作步骤
1)创建用户neu,分配用户create session、unlimited tablespace、create table、createsequence、create view权限
SQL>CREATE USER neusoft IDENTIFIED BY neu;
SQL> GRANT create session,unlimited tablespace,create table,
create sequence,create view
TO neu;
2)创建各个表,选择适当的数据类型并添加完整性约束
CREATE TABLE member
(member_id NUMBER(10) CONSTRAINT member_id_pk PRIMARY KEY,
m_name VARCHAR2(25) NOT NULL,
address VARCHAR2(100),
city VARCHAR2(30),
phone VARCHAR2(15),
join_date DATE DEFAULT SYSDATE NOT NULL); CREATE TABLE title
(title_id NUMBER(10) CONSTRAINT title_id_pk PRIMARY KEY,
title VARCHAR2(60) NOT NULL,
description VARCHAR2(400) NOT NULL,
pub_date DATE,
author VARCHAR2(40),
label VARCHAR2(20) CONSTRAINT label_ck
CHECK(label IN('java','c','c++','db','web'))); CREATE TABLE title_copy
(title_id NUMBER(10) CONSTRAINT title_bak_pk PRIMARY KEY,
bak_num NUMBER(10) CONSTRAINT bak_num_c CHECK(bak_num>=0),
lack_num NUMBER(10) DEFAULT 0,
CONSTRAINT title_id_bak_fk FOREIGN KEY(title_id) REFERENCES title(title_id)); CREATE TABLE reservation
(member_id NUMBER(10) CONSTRAINT r_member_id_fk REFERENCES member(member_id),
title_id NUMBER(10) CONSTRAINT r_title_id_fk REFERENCES title(title_id),
res_date DATE,
CONSTRAINT res_s_pk PRIMARY KEY(member_id,title_id, res_date)); CREATE TABLE borrow
(borrow_seq NUMBER(10) CONSTRAINT borrow_seq_pk PRIMARY KEY,
title_id NUMBER(10) CONSTRAINT b_title_id_fk REFERENCES title(title_id),
borrow_date DATE DEFAULT SYSDATE,
return_date DATE,
exp_date DATE DEFAULT ADD_MONTHS(SYSDATE,1));
3)检查数据字典,验证表和约束已被完全创建
SELECT * FROM user_tables;
SELECT * FROM user_constraints;
或:
SELECT table_name
FROM user_tables
WHERE table_name IN ('MEMBER', 'TITLE', 'TITLE_COPY', 'BORROW', 'RESERVATION'); SELECT constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name IN ('MEMBER', 'TITLE', 'TITLE_COPY', 'BORROW', 'RESERVATION');
4)创建序列来唯一地标识在会员表、图书表、图书借阅表中的每一行
CREATE SEQUENCE member_seq
INCREMENT BY 1
START WITH 1
NOCACHE; CREATE SEQUENCE title_seq
INCREMENT BY 1
START WITH 1
NOCACHE; CREATE SEQUENCE borrow_seq
INCREMENT BY 1
START WITH 1
NOCACHE;
5)验证在数据字典中已经存在的序列
SELECT * FROM user_sequences;
或:
SELECT sequence_name, increment_by, last_number
FROM user_sequences
WHERE sequence_name IN ('MEMBER_SEQ','TITLE_ID_SEQ','BORROW_SEQ');
6)添加数据到表中,注意:数据的添加要使用序列
INSERT INTO title
VALUES (title_seq.NEXTVAL, '世界是平的','介绍世界为什么是平的','14-8月-07','xiyou','java');
INSERT INTO member
VALUES (member_seq.NEXTVAL, 'yang','陕西韩城','韩城',83660002,sysdate); INSERT INTO title_copy
VALUES (title_seq.CURRVAL,10,0); INSERT INTO borrow(borrow_seq, title_id , member_id , borrow_date, exp_date )
VALUES (borrow_seq.NEXTVAL, title_seq.CURRVAL,member_seq.currval, sysdate, ADD_MONTHS(SYSDATE,1)); INSERT INTO reservation
VALUES (member_seq.currval,title_seq.CURRVAL, sysdate);
7)创建一个名为title_avail的视图,显示书的编号、书名、出版时间和书的库存数量,从视图中查询所有出版日期大于2007-01-01的数据,用书的编号排序
CREATE OR REPLACE VIEW title_avail
AS
SELECT t.title_id ,t.title, c.bak_num , t.pub_date
FROM title t, title_copy c
WHERE t.title_id = c.title_id; SELECT *
FROM title_avail
WHERE pub_date>TO_DATE('2007-01-01','yyyy-mm-dd')
ORDER BY title_id;
8)在图书借阅表上以会员ID创建非惟一索引
CREATE INDEX bow_mem_idx
ON borrow(member_id );
9)更新库存图书备份表,将其中一本书名为XXX的库存量在原有基础上增加10本
UPDATE title_copy
SET bak_num=bak_num+10
WHERE title_id=
(SELECT title_id
FROM title
WHERE title='世界是平的')
10)修改表结构,添加一个price列到title表,记录书的购买价格。该列应该有8位数字长度和2位小数,验证你的修改。
ALTER TABLE title
ADD (price NUMBER(8,2)); DESC title
11)为每本书添加价格
UPDATE title SET price=50 WHERE title_id=1;
12)更改title表中价格字段为非空,验证该约束
ALTER TABLE title MODIFY (price NOT NULL);
SELECT * FROM user_constraints;
或:
SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'TITLE';
13)查询客户租书信息,包含客户编号、客户名、书名、书的价格、借阅日期、还书日期、到期日期
SELECT m.member_id,m.m_name,t.title,t.price,b.borrow_date,b.return_date,b.exp_date
FROM member m,title t,borrow b
WHERE m.member_id=b.member_id
AND t.title_id=b.title_id;
14)删除各个表中的数据
DELETE FROM borrow;
DELETE FROM reservation;
DELETE FROM title_copy;
DELETE FROM title;
DELETE FROM member;
15)删除member表上的主键约束,并且任何与其相依赖的约束也被一起删除
ALTER TABLE member
DROP PRIMARY KEY CASCADE;