映像租賃系統：
在美國，由于知識產權得到了很好的保護，所以一般不會出現盜版的問題，美國人除去在電影院看電影之外就是在影像店租碟回家去看，在這種情況下，影相出租的店鋪就比較多，在資訊化的背景下，設計一套系統管理影像出租業務成了當務之急，于是通過和店鋪經理溝通，獲得如下業務資訊：
1.凡是想要到該影像店租賃影碟的被稱為會員（Member）,只有成為該店的會員才能夠租借該店的影像資料(Title)，
2.影像店中不定期會購買一些影像資料(Title)，并且獲得了復制該影像資料的權力，每一份影像制品可以有多個拷貝，每一份拷貝稱為一個復制條目(TitleCopy)
3.對客戶而言，原始的影像資料（Title）只能被預定并且可以同時接受多份預定(Reservation)，客戶可以租賃的是影像拷貝（TitleCopy）,
4.任何一個客戶的租賃資訊（Rental）都必須存檔以便將來店鋪對客戶的租賃進行評估，
過和租賃店經理詳細面談，了解更詳細的業務的資訊：
1.每一個會員必須被記錄的資訊包括會員編號id,而且每個會員的編號是唯一的，姓名name, 成為會員的日期join_date,以及其他需要被記錄的資訊包括（住宅地址address, 所在城市city, 住宅電話phone.
2.影像制品(Title)必須被記錄的資訊包括編號id，而且所有影像制品的編號都是唯一的，標題title，影片說明description，價格price，和其他如下資訊（分
級rating，種類category, 發行日期release_date）.
3.每一份影像拷貝（TitleCopy）都有各自的編號id，同一個影像的若干拷貝編號不同，但是不同的影像拷貝的編號可能會重復，必須記錄下是否已經出租，以便于店員隨時能夠查詢拷貝資料的當前狀態(status)，
4.系統中允許會員預定某項影像資料，但必須提前預約租賃時間（res_date），
5.每一項出租事項需要記錄下來（租借日期book_date，實際歸還日期act_ret_date,期待歸還日期/過期日期(exp_ret_date)，
創表陳述句
create table title(id number(7) ,
title varchar2(15) constraint title_title_nn not null,
description varchar2(15) constraint title_description_nn not null,
rating char(1),
category varchar2(15),
release_date date,
price number(7,2) constraint title_price_nn not null,
constraint title_id_pk primary key(id)
);
create table member(
id number(7),
last_name varchar2(15) constraint member_last_name_nn not null,
first_name varchar2(15),
address varchar2(15),
city varchar2(10),
phone varchar2(11),
join_date date constraint member_join_date_nn not null,
constraint member_id_pk primary key(id)
);
create table title_copy(
id number(7),
status varchar2(8) constraint title_copy_status_nn not null,
title_id number(7) constraint title_copy_title_id_nn not null constraint title_copy_title_id_fk references title(id),
constraint title_copy_id_pk2 primary key(id,title_id)
);
----------預約表-----------
create table reservation(
res_date date,
title_id number(7) constraint res_title_id_nn not null
constraint res_title_id_fk references title(id),
member_id number(7) constraint res_member_id_nn not null
constraint res_member_id_fk references member(id),
constraint res_res_date_pk3 primary key(res_date,title_id,member_id)
);
-------租賃表----------
create table rental(
book_date date,
act_ret_date date,
exp_ret_date date,
title_copy_id number(7),
member_id number(7) constraint rental_member_id_nn not null
constraint rental_member_id_fk references member(id),
title_copy_title_id number(7),
constraint rental_book_date_pk3 primary key(book_date,title_copy_id,title_copy_title_id),
constraint rental_title_copy_id_fk2 FOREIGN KEY(title_copy_id,title_copy_title_id) references title_copy(id,title_id)
); 插入資料 -------------title-----------------
insert into title values(1,'西虹市首富','反轉人生','A','comedy','01-1月-18',36.5);
insert into title values(2,'摩天大樓','警察破案','A','suspense','01-9月-20',15);
insert into title values(3,'魁拔','走向巔峰','A','cartoon','01-8月-16',80);
insert into title values(4,'舌尖中國','中國美食','B','documentary','05-6月-18',20);
insert into title values(5,'中國好聲音','選拔精英','B','documentary','06-9月-20',30);
insert into title values(6,'釜山行2','喪尸橫行','B','disaster','03-9月-20',50.2);
insert into title values(7,'流浪地球','保護地球','C','science','01-1月-19',60);
insert into title values(8,'奧特曼','保護日本','C','cartoon','01-8月-06',30.5);
insert into title values(9,'瘋狂動物','動物的本性','C','cartoon','01-1月-18',30);
insert into title values(10,'死亡迷宮','危險的迷宮','A','suspense','01-10月-19',36.5);
------------member---------------
insert into member(id,last_name,first_name,address,city,join_date)
values(1,'jack',null,'學院路','南京','05-3月-19');
insert into member(id,last_name,first_name,address,city,join_date)
values(2,'mary','lisa','解放路','蘇州','29-2月-08');
insert into member(id,last_name,first_name,address,city,join_date)
values(3,'lili','luck','學府路','太原','09-8月-13');
insert into member(id,last_name,first_name,address,city,join_date)
values(4,'shi','zt','牧馬路','忻州','06-9月-20');
insert into member(id,last_name,first_name,address,city,join_date)
values(5,'rui','sj','花園小區','南京','03-3月-19');
insert into member(id,last_name,first_name,address,city,join_date)
values(6,'liu','ll','榴蓮小區','忻州','06-9月-20');
insert into member(id,last_name,first_name,address,city,join_date)
values(7,'ren','zhe','星星小區','太原','01-10月-19');
insert into member(id,last_name,first_name,address,city,join_date)
values(8,'ren','nan','學府街','太原','15-4月-20');
insert into member(id,last_name,first_name,address,city,join_date)
values(9,'zhang','san','學院路','蘇州','01-9月-17');
insert into member(id,last_name,first_name,address,city,join_date)
values(10,'tom','len','學院路','昆山','04-5月-16');
-------------title_copy------------
insert into title_copy values(1,'T',1);
insert into title_copy values(2,'F',1);
insert into title_copy values(1,'T',3);
insert into title_copy values(2,'T',3);
insert into title_copy values(3,'T',3);
insert into title_copy values(1,'F',7);
insert into title_copy values(1,'F',5);
insert into title_copy values(2,'T',7);
insert into title_copy values(1,'F',9);
insert into title_copy values(1,'T',8);
insert into title_copy values(1,'T',6);
insert into title_copy values(1,'F',4);
insert into title_copy values(1,'T',2);
--------------reservation預約表----------
insert into reservation values('10-9月-20',1,3);
insert into reservation values('11-9月-20',3,3);
insert into reservation values('11-9月-20',3,5);
insert into reservation values('13-9月-20',4,6);
insert into reservation values('13-9月-20',6,3);
insert into reservation values('14-9月-20',3,5);
insert into reservation values('16-9月-20',9,5);
insert into reservation values('16-9月-20',6,5);
insert into reservation values('16-9月-20',2,7);
insert into reservation values('16-9月-20',8,5);
insert into reservation values('20-9月-20',7,6);
insert into reservation values('21-9月-20',7,2);
insert into reservation values('22-9月-20',7,1);
------------------rental租賃表----------
insert into rental values('20-9月-18','20-10月-18','20-12月-18',1,2,1);
insert into rental values('05-3月-19','05-7月-19','05-6月-19',1,5,7);
insert into rental values('05-8月-20',null,'05-11月-20',1,5,3);
insert into rental values('03-5月-20','01-7月-20','03-8月-20',2,4,3);
insert into rental values('05-9月-20','09-12月-20','05-12月-20',3,1,3);
insert into rental values('07-3月-20','01-4月-20','07-6月-20',1,6,2);
insert into rental values('07-9月-20','07-10月-20','07-12月-20',1,7,9);
insert into rental values('05-3月-19',null,'05-6月-19',1,8,6);
insert into rental values('07-6月-19','07-9月-19','20-12月-18',2,8,7);
insert into rental values('01-10月-18',null,'01-1月-19',1,1,4);
1.以及用戶所借閱的影像資料名字和借閱的日期
select m.last_name||m.first_name,t.title,ren.book_date
from rental ren,title t,member m
where ren.member_id=m.id and t.id=ren.title_copy_title_id;
2.查詢出最近一周訂閱影像資料的用戶和相應的影像資料名字及借閱日期
select last_name,title,book_date
from member m,title t,reservation res,rental ren
where m.id=res.member_id and t.id=res.title_id and ren.member_id=m.id and ren.title_copy_title_id=t.id
and (months_between(sysdate,res_date))*30<=7
and (months_between(res_date,sysdate))*30<=7;
3.查詢出本周日應該歸還的影像資料和借閱者的姓名，地址
select title_copy_id,title,last_name||first_name name,address
from rental ren,title_copy tc,member m,title t
where ren.title_copy_id=tc.id and ren.title_copy_title_id=tc.title_id
and ren.member_id=m.id and t.id=tc.title_id
and next_day(sysdate,'星期日')>=exp_ret_date;
4.查詢出已經超期還未歸還的影像資料和借閱者的姓名，地址
select title_copy_id,title,last_name||first_name name,address
from rental ren,title_copy tc,member m,title t
where ren.title_copy_id=tc.id and ren.title_copy_title_id=tc.title_id and ren.member_id=m.id and t.id=tc.title_id
and sysdate>=exp_ret_date;
5.查詢出最近一月借閱次數最多的影像資料
select title
from title
where id in (select title_copy_title_id id
from rental
where months_between(sysdate,book_date)<=1
group by title_copy_title_id
having count(title_copy_title_id)=(select max(count(title_copy_title_id))
from rental
where months_between(sysdate,book_date)<=1
group by title_copy_title_id));
6.查詢出已經登記但是還沒有拷貝的影像資料
select title
from title
where id in (select title_id
from title_copy
group by title_id
having count(title_id)=1);
7.查詢出本周預定最多的影像資料
select title
from title
where id in (select max(count(title_id))
from reservation
where res_date between (next_day(sysdate,'星期一'))-7 and (next_day(sysdate,'星期一'))
group by title_id);
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/1153.html
標籤：Oracle
上一篇：Oracle 回滾段undo
下一篇：Oracle資料庫備份和恢復