T-FLY团队 映像租赁系统.ppt

制作组：T-FLY湘南学院08级计科一班2011年05月15日T-FLY成员分工实体关系模型&表实例图：彭涛戴姝黄慧建表语句：罗柳珍彭涛林东升样例数据入库（insert语句）:田小艳罗柳珍黄龙兵业务逻辑实现（select语句）：陈明艳徐江华唐振PPT制作:林东升徐江华Columnnameidtitledescriptionratingcolumncategoryrelease_datepricekeytypepknn/uknn,uknnnnForeign-tableforeignkeydatetypenumcharcharnumchardatenummaxlength1020200022010,2TitleTableColumnnameidtitleaddresscityphonejoin_datekeytypepknn/uknn,uknnnnForeigntableforeignkeydatetypenumcharcharnumchardatemaxlength1025502020MemberTableColumnnameRes_idTitle_idMember_idkeytypepkFk,pkFk,pknn/uknn,ukNn,ukNn,ukForeigntableT_FLY_titleT_FLY_memberforeignkeyididdatetypedatenumbernumberMaxlength1010ReservationTableColumnnameidstatusTitle_idkeytypepkPk,fknn/uknn,uknnnn,ukForeigntableT_FLY_titleforeignkeyiddatetypenumcharnumMaxlength102010Title_CopyTableColumnnamebook_dateact_ret_dateexp_ret_datetitle_idtitle_copy_idmember_idkeytypepkfk,pkfk,pkfknn/uknn,uknn,uknn,uknnForeigntableT-FLT_title_copyT-FLY_memberforeignkeytitle_idtitle_copy_ididdatetypedatedatedatenumnumnumMaxlength101010RentalTable建表语句1createtableT_FLY_title(idnumber(10)constraintT_FLY_title_id_pkprimarykey,titlevarchar2(20)constraintT_FLY_title_title_nnnotnull,descriptionvarchar2(2000)constraintT_FLY_title_descrption_nnnotnull,ratingnumber(2),categoryvarchar2(20),release_datedate,pricenumber(10,2)constraintT_FLY_title_price_nnnotnull);createtableT_FLY_member(idnumber(10)constraintT_FLY_member_id_pkprimarykey,namevarchar2(25)constraintT_FLY_member_name_nnnotnull,addressvarchar2(50),cityvarchar2(20),phonevarchar2(20),join_datedateconstraintT_FLY_member_join_date_nnnotnull);建表语句2createtableT_FLY_reservation(res_datedate,member_idnumber(10)constraintT_FLY_reservation_member_id_fkreferencesT_FLY_member(id),title_idnumber(10)constraintT_FLY_reservation_title_id_fkreferencesT_FLY_title(id),constraintT_FLY_reservation_res_date_idprimarykey(res_date,member_id,title_id));建表语句3createtableT_FLY_title_copy(idnumber(10),statusvarchar2(20)constraintT_FLY_title_copy_status_nnnotnull,title_idnumber(10)constraintT_FLY_title_title_id_fkreferencesT_FLY_title(id),constraintT_FLY_title_copy_id_title_id_pkprimarykey(id,title_id));建表语句4createtableT_FLY_rental(book_datedate,act_ret_datedate,exp_ret_datedate,title_idnumber(10),title_copy_idnumber(10),member_idnumber(10)constraintT_FLY_rental_member_id_nnnotnullconstraintT_FLY_rental_member_id_fkreferencesT_FLY_member(id),constraintT_FLY_rental_fkforeignkey(title_id,title_copy_id)referencesT_FLY_title_copy(title_id,id),constraintT_FLY_rental_pkprimarykey(book_date,title_id,title_copy_id));建表语句5往member表中插入数据insertintoT_FLY_membervalues(1,'胡歌','湘南学院','郴州','13298591888',sysdate-365);insertintoT_FLY_membervalues(2,'恬静','湘南学院','郴州','13298591887',sysdate-362);insertintoT_FLY_membervalues(3,'李军','湘南学院','郴州','13298591886',sysdate-300);insertintoT_FLY_membervalues(4,'古天乐','湘南学院','郴州','13298591881',sysdate-370);insertintoT_FLY_membervalues(5,'占山','湘南学院','郴州','13298591882',sysdate-400);样例数据入库（insert语句）往title表中插入数据insertintoT_FLY_title(id,title,description,price)values(1,'天意','Good',5);insertintoT_FLY_title(id,title,description,price)values(2,'意外','Better',10);insertintoT_FLY_title(id,title,description,price)values(3,'非诚勿扰','Best',5);insertintoT_FLY_title(id,title,description,price)values(4,'海洋天堂','Best',5);insertintoT_FLY_title(id,title,description,price)values(5,'神话','Best',5);样例数据入库（insert语句）往reservation表中插入数据insertintoT_FLY_reservation(title_id,member_id,res_date)values(1,1,sysdate-32);insertintoT_FLY_reservation(title_id,member_id,res_date)values(1,2,sysdate-25);insertintoT_FLY_reservation(title_id,member_id,res_date)values(1,3,sysdate-20);insertintoT_FLY_reservation(title_id,member_id,res_date)values(2,1,sysdate-30);insertintoT_FLY_reservation(title_id,member_id,res_date)values(2,2,sysdate-20);insertintoT_FLY_reservation(title_id,member_id,res_date)values(2,3,sysdate-42);insertintoT_FLY_reservation(title_id,member_id,res_date)values(3,1,sysdate-10);)样例数据入库（insert语句）insertintoT_FLY_reservation(title_id,member_id,res_date)values(3,2,sysdate-50);insertintoT_FLY_reservation(title_id,member_id,res_date)values(3,3,sysdate-40);insertintoT_FLY_reservation(title_id,member_id,res_date)values(4,3,sysdate-2);insertintoT_FLY_reservation(title_id,member_id,res_date)values(4,4,sysdate-1);insertintoT_FLY_reservation(title_id,member_id,res_date)values(4,2,sysdate-1);insertintoT_FLY_reservation(title_id,member_id,res_date)values(4,3,sysdate-1);样例数据入库（insert语句）往title_copy表中插入数据insertintoT_FLY_title_copy(id,status,title_id)values(1,'rent',1);insertintoT_FLY_title_copy(id,status,title_id)values(2,'rent',1);insertintoT_FLY_title_copy(id,status,title_id)values(3,'free',1);insertintoT_FLY_title_copy(id,status,title_id)values(4,'free',1);insertintoT_FLY_title_copy(id,status,title_id)values(1,'rent',2);insertintoT_FLY_title_copy(id,status,title_id)values(2,'rent',2);insertintoT_FLY_title_copy(id,status,title_id)values(1,'rent',3);insertintoT_FLY_title_copy(id,status,title_id)values(2,'rent',3);insertintoT_FLY_title_copy(id,status,ti