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

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

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

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

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

5、ULL物品名稱(chēng)items_repairsVarchar22CHECK報(bào)修情況（是，否）（6）宿舍物品損壞信息報(bào)修表（repairs）列名數(shù)據(jù)類(lèi)型長(zhǎng)度約束說(shuō)明repairs_idVarchar26PRIMARY KEY報(bào)修編號(hào)repairs_planVarchar210NOT NULL報(bào)修進(jìn)度repairs_solveVarchar22CHECK是否解決2、創(chuàng)建表表名對(duì)象定義SQL語(yǔ)句說(shuō)明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);用于存儲(chǔ)宿舍樓基本信息，便于查刪改；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);用于存儲(chǔ)宿舍工作人員基本信息，便于宿舍管理，與信息的查刪改；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);用于存儲(chǔ)學(xué)生的基本信息，便于宿舍管理，與信息的查刪改；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);用于存儲(chǔ)宿舍基本信息，便于宿舍管理，與信息的查刪改；itemscreate table items(items_id Varchar2(6),items_name Varchar2(20),items_repairs Varchar2(2),primary key(items_id);用于存儲(chǔ)宿舍物品基本信息，便于宿舍管理，與信息的查刪改；repairscreate table repairs(repairs_i

11、d Varchar2(6),repairs_plan Varchar2(10),repairs_solve Varchar2(2),primary key(repairs_id);用于存儲(chǔ)宿舍物品損壞報(bào)修的基本信息，便于宿舍管理，與信息的查刪改；3、操作表中的數(shù)據(jù)操作類(lèi)型數(shù)據(jù)操作SQL語(yǔ)句說(shuō)明插入數(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;信息工程學(xué)院','計(jì)算機(jī)應(yīng)用技術(shù)','一班','張莉','#39;,'2','101');insert into student values(141401102,'程明恒','男','河南',to_date(19950101000000,'YYYYMMDDHH24MISS'),to_date(20140901000000,'YYYYMMDDHH24MISS'),'信息工程學(xué)院&#

15、39;,'計(jì)算機(jī)應(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,'風(fēng)扇','否');insert into items values(2,'電燈&#

16、39;,'否');insert into repairs values (1,'已上報(bào)','是');修改數(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è)計(jì)視圖視圖名對(duì)象定義SQL語(yǔ)句說(shuō)明sex_stucreate view sex_stu as select stu_id,stu_name,stu_sex from student where stu

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

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

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

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