學生宿舍管理系統(tǒng)數(shù)據(jù)庫部分設(shè)計

1、項目名稱一、功能模塊分解 參見教材第334-335頁二、數(shù)據(jù)庫設(shè)計1、設(shè)計表（1）宿舍樓基本信息表（drom_table）列名數(shù)據(jù)類型長度約束說明drom_idVarchar26PRIMARY KEY宿舍樓編號drom_sexVarchar22CHECK男女宿舍drom_mobileVarchar215NOT NULL宿管處電話drom_administVarchar210NOT NULL宿舍管理人員wor_idVarchar215FOREIGN KEY(wor_id)工作人員編號（2）工人基本信息表（worker）列名數(shù)據(jù)類型長度約束說明wor_idVarchar215PRIMARY KEY

2、工作人員編號wor_nameVarchar210NOT NULL工作人員姓名wor_typeVarchar210NOY NULL工作類型salaryNUMBER10.2NOT NULL工資wor_sexVarchar22CHECK性別wor_mobileVarchar215NOT NULL聯(lián)系方式wor_datedateNOT NULL雇傭日期（3）學生基本信息表（student）列名數(shù)據(jù)類型長度約束說明stu_idVarchar220PRIMARY KEY學號stu_nameVarchar210NOT NULL學生姓名stu_sexVarchar22CHECK學生性別stu_fromVarc

3、har220NOT NULL學生生源地stu_datedateNOT NULL學生出生日期stu_hiredatedateNOT NULL學生入學時間stu_collegeVarchar220NOT NULL學生所在學院stu_majorVarchar220NOT NULL學生所學專業(yè)stu_classVarchar220NOT NULL學生班級stu_instructorVarchar220NOT NULL學生輔導員stu_contactVarchar215NOT NULL輔導員聯(lián)系方式drom_idVarchar26FOREIGN KEY(drom_id)學生所在宿舍樓room_idVar

4、char26FOREIGN KEY(room_id)學生所在宿舍（4）宿舍信息表（room）列名數(shù)據(jù)類型長度約束說明room_idVarchar26PRIMARY KEY宿舍編號drom_idVarchar26FOREIGN KEY(drom_id)宿舍樓編號room_sumVarchar26NOT NULL入住人數(shù)room_fullVarchar22CHECK是否住滿romm_clearVarchar22CHECK離校清理（5）宿舍物品信息表（items）列名數(shù)據(jù)類型長度約束說明items_idVarchar26PRIMARY KEY物品編號items_nameVarchar220NOT N

5、ULL物品名稱items_repairsVarchar22CHECK報修情況（是，否）（6）宿舍物品損壞信息報修表（repairs）列名數(shù)據(jù)類型長度約束說明repairs_idVarchar26PRIMARY KEY報修編號repairs_planVarchar210NOT NULL報修進度repairs_solveVarchar22CHECK是否解決2、創(chuàng)建表表名對象定義SQL語句說明drom_tablecreate table drom_table(drom_id Varchar2(6) not null,drom_sex Varchar2(2) not null,drom_mobile

6、Varchar2(15) not null,drom_administ Varchar2(10) not null,wor_id Varchar2(15) not null,primary key(drom_id),constraint p_fk foreign key (wor_id) references worker(wor_id);用于存儲宿舍樓基本信息，便于查刪改；workercreate table worker(wor_id Varchar2(15) not null,wor_name Varchar2(10) not null,wor_type Varchar2(10) not

7、 null,salary number(10,2) not null,wor_sex Varchar2(2) not null,wor_mobile Varchar2(15) not null,wor_date date not null,primary key(wor_id);用于存儲宿舍工作人員基本信息，便于宿舍管理，與信息的查刪改；studentcreate table student(stu_idVarchar2(20)not null,stu_name Varchar2(10)not null,stu_sexVarchar2(2)not null,stu_from Varchar2(

8、20)not null,stu_date datestu_hiredate datestu_college Varchar2(20)not null,stu_major Varchar2(20)not null,stu_class Varchar2(20)not null,stu_instructor Varchar2(20)not null,stu_contact Varchar2(15)not null,drom_idVarchar2(6)not null,room_idVarchar2(6)not null,primary key(stu_id)constraint p_fk forei

9、gn key (drom_id) references drom_table(drom_id)constraint p_fk foreign key (room_id) references room(room_id);用于存儲學生的基本信息，便于宿舍管理，與信息的查刪改；roomcreate table room(room_idVarchar2(6)not null,drom_idVarchar2(6)not null,room_sum Varchar2(6)not null,room_full Varchar2(2)not null,romm_clear Varchar2(2)not nu

10、ll,primary key(room_id)constraint p_fk foreign key (drom_id) references drom_table(drom_id);用于存儲宿舍基本信息，便于宿舍管理，與信息的查刪改；itemscreate table items(items_id Varchar2(6),items_name Varchar2(20),items_repairs Varchar2(2),primary key(items_id);用于存儲宿舍物品基本信息，便于宿舍管理，與信息的查刪改；repairscreate table repairs(repairs_i

11、d Varchar2(6),repairs_plan Varchar2(10),repairs_solve Varchar2(2),primary key(repairs_id);用于存儲宿舍物品損壞報修的基本信息，便于宿舍管理，與信息的查刪改；3、操作表中的數(shù)據(jù)操作類型數(shù)據(jù)操作SQL語句說明插入數(shù)據(jù)insert into drom_table values(1,'男',03760000001,'小李',101001);insert into drom_table values(2,'女',03760000002,'小紅',101

12、002);insert into worker values(101002,'小紅','宿管',1000,'女'to_date(20110101000000,'YYYYMMDDHH24MISS');insert into worker values(101001,'小李','宿管',1000,'男'to_date(20110101000000,'YYYYMMDDHH24MISS');insert into worke

13、r values(101003,'王郭斌','保潔',1200,'男',1310376666,to_date(20120101000000,'YYYYMMDDHH24MISS');insert into student values(141401101,'楊貝','女','河南',to_date(19970101000000,'YYYYMMDDHH24MISS'),to_date(20140901000000,'YYYYMMDDHH24MISS'),&

14、#39;信息工程學院','計算機應(yīng)用技術(shù)','一班','張莉','#39;,'2','101');insert into student values(141401102,'程明恒','男','河南',to_date(19950101000000,'YYYYMMDDHH24MISS'),to_date(20140901000000,'YYYYMMDDHH24MISS'),'信息工程學院&#

15、39;,'計算機應(yīng)用技術(shù)','一班','張莉','#39;,'1','101');insert into room values(101,1,4,'否','否');insert into room values(101,2,4,'否','否');insert into items values(1,'風扇','否');insert into items values(2,'電燈&#

16、39;,'否');insert into repairs values (1,'已上報','是');修改數(shù)據(jù)update worker set salary=1500 where wor_id=101001;刪除數(shù)據(jù)delete from worker;truncate table worker;查詢數(shù)據(jù)select *from worker;4、設(shè)計視圖視圖名對象定義SQL語句說明sex_stucreate view sex_stu as select stu_id,stu_name,stu_sex from student where stu

17、_sex='女' with check option;創(chuàng)建一個帶檢查約束的視圖。顯示性別為女的學生信息drom_selectcreate view drom_select as select * from drom_table where drom_id=1 with read only;創(chuàng)建一個只讀視圖。顯示宿舍樓編號為1的全部信息。刪除視圖drop view + 視圖名5、設(shè)計索引索引名對象定義SQL語句說明wor_idcreate index wor_id on worker(wor_id);工人信息表列為員工編號的位圖索引wor_namecreate unique in

18、dex wor_ name on worker(wor_name);工人信息表列為員工名稱的唯一索引wor_salaryquery rewrite create index wor_salary on worker(round(salary);工人信息表列為工資的函數(shù)索引data_worcreate index data_wor on worker(wor_date);工人信息表列為雇傭日期的B樹索引6、設(shè)計同義詞同義詞名對象定義SQL語句說明worker_syncreate public synonym worker_syn for worker;工人信息表公有同義詞student_sync

19、reate public synonym student_syn for student;學生信息表公有同義詞drom_syncreate private synonym drom_syn for drom_table;宿舍樓信息表私有同義詞room_syncreate private synonym room_syn for room;宿舍信息表私有同義詞7、設(shè)計序列序列名對象定義SQL語句說明worker_seqcreate sequence worker_seq increment by1start with 1maxvalue 100nominvaluenocyclenocache工人

20、信息表中wor_id的唯一序列字段student_seqcreate sequence student_seq increment by1start with 1maxvalue 100nominvaluenocyclenocache學生信息表中stu_id的唯一序列字段room_seqcreate sequence room_seq increment by1start with 1maxvalue 100nominvaluenocyclenocache宿舍信息表中room025_id的唯一序列字段8、設(shè)計存儲過程存儲過程名對象定義SQL語句說明pro_count_typecreate or replace procedure pro_count