oracle出租影碟管理系统数据库

oracle/sqlserver数据库的建立,各个表的信息、约束、主外键的设置。

CREATE TABLE member
(member_id NUMBER(10) CONSTRAINT member_member_id_pk PRIMARY KEY,
last_name VARCHAR2(25) CONSTRAINT member_last_name_nn NOT NULL,
first_name VARCHAR2(25),
address VARCHAR2(100),
city VARCHAR2(30),
phone VARCHAR2(15),
join_date DATE DEFAULT SYSDATE CONSTRAINT member_join_date_nn NOT NULL);


CREATE TABLE title
(title_id NUMBER(10) CONSTRAINT title_title_id_pk PRIMARY KEY,
title VARCHAR2(60) CONSTRAINT title_title_nn NOT NULL,
description VARCHAR2(400) CONSTRAINT title_description_nn NOT NULL,
rating VARCHAR2(4) CONSTRAINT title_rating_ck CHECK
(rating IN ('G', 'PG', 'R', 'NC17', 'NR')),
category VARCHAR2(20) CONSTRAINT title_category_ck CHECK
(category IN ('DRAMA', 'COMEDY', 'ACTION','CHILD', 'SCIFI', 'DOCUMENTARY')),
release_date DATE);

CREATE TABLE title_copy
(copy_id NUMBER(10),
title_id NUMBER(10) CONSTRAINT title_copy_title_if_fk REFERENCES title(title_id),
status VARCHAR2(15) CONSTRAINT title_copy_status_nn NOT NULL
CONSTRAINT title_copy_status_ck CHECK
(status IN ('AVAILABLE', 'DESTROYED','RENTED', 'RESERVED')),
CONSTRAINT title_copy_copy_id_title_id_pk PRIMARY KEY (copy_id, title_id));

CREATE TABLE rental
(book_date DATE DEFAULT SYSDATE,
member_id NUMBER(10) CONSTRAINT rental_member_id_fk REFERENCES member(member_id),
copy_id NUMBER(10),
act_ret_date DATE,
exp_ret_date DATE DEFAULT SYSDATE + 2,
title_id NUMBER(10),
CONSTRAINT rental_book_date_copy_title_pk
PRIMARY KEY (book_date, member_id,copy_id,title_id),
CONSTRAINT rental_copy_id_title_id_fk FOREIGN KEY
(copy_id, title_id) REFERENCES title_copy(copy_id,title_id));

CREATE TABLE reservation
(res_date DATE,
member_id NUMBER(10)
CONSTRAINT reservation_member_id REFERENCES member(member_id),
title_id NUMBER(10) CONSTRAINT reservation_title_id REFERENCES
title(title_id),
CONSTRAINT reservation_resdate_mem_tit_pk
PRIMARY KEY (res_date, member_id, title_id));

SELECT table_name FROM user_tables
WHERE table_name IN ('MEMBER', 'TITLE', 'TITLE_COPY','RENTAL', 'RESERVATION');
SELECT constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name IN ('MEMBER', 'TITLE', 'TITLE_COPY','RENTAL', 'RESERVATION');

CREATE SEQUENCE member_id_seq
START WITH 101
NOCACHE;

CREATE SEQUENCE title_id_seq
START WITH 92
NO
CACHE;

SELECT sequence_name, increment_by, last_number
FROM user_sequences
WHERE sequence_name IN ('MEMBER_ID_SEQ', 'TITLE_ID_SEQ ');