学生宿舍管理系统数据库部分设计.doc

项目名称一、功能模块分解 参见教材第334-335页二、数据库设计1、设计表（1）宿舍楼基本信息表（drom_table）列名数据类型长度约束说明drom_idVarchar26PRIMARY KEY宿舍楼编号drom_sexVarchar22CHECK男女宿舍drom_mobileVarchar215NOT NULL宿管处电话drom_administVarchar210NOT NULL宿舍管理人员wor_idVarchar215FOREIGN KEY(wor_id)工作人员编号（2）工人基本信息表（worker）列名数据类型长度约束说明wor_idVarchar215PRIMARY KEY工作人员编号wor_nameVarchar210NOT NULL工作人员姓名wor_typeVarchar210NOY NULL工作类型salaryNUMBER10.2NOT NULL工资wor_sexVarchar22CHECK性别wor_mobileVarchar215NOT NULL联系方式wor_datedateNOT NULL雇佣日期（3）学生基本信息表（student）列名数据类型长度约束说明stu_idVarchar220PRIMARY KEY学号stu_nameVarchar210NOT NULL学生姓名stu_sexVarchar22CHECK学生性别stu_fromVarchar220NOT NULL学生生源地stu_datedateNOT NULL学生出生日期stu_hiredatedateNOT NULL学生入学时间stu_collegeVarchar220NOT NULL学生所在学院stu_majorVarchar220NOT NULL学生所学专业stu_classVarchar220NOT NULL学生班级stu_instructorVarchar220NOT NULL学生辅导员stu_contactVarchar215NOT NULL辅导员联系方式drom_idVarchar26FOREIGN KEY(drom_id)学生所在宿舍楼room_idVarchar26FOREIGN KEY(room_id)学生所在宿舍（4）宿舍信息表（room）列名数据类型长度约束说明room_idVarchar26PRIMARY KEY宿舍编号drom_idVarchar26FOREIGN KEY(drom_id)宿舍楼编号room_sumVarchar26NOT NULL入住人数room_fullVarchar22CHECK是否住满romm_clearVarchar22CHECK离校清理（5）宿舍物品信息表（items）列名数据类型长度约束说明items_idVarchar26PRIMARY KEY物品编号items_nameVarchar220NOT NULL物品名称items_repairsVarchar22CHECK报修情况（是，否）（6）宿舍物品损坏信息报修表（repairs）列名数据类型长度约束说明repairs_idVarchar26PRIMARY KEY报修编号repairs_planVarchar210NOT NULL报修进度repairs_solveVarchar22CHECK是否解决2、创建表表名对象定义SQL语句说明drom_tablecreate table drom_table(drom_id Varchar2(6) not null,drom_sex Varchar2(2) not null,drom_mobile 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 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(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 foreign 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 null,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_id Varchar2(6),repairs_plan Varchar2(10),repairs_solve Varchar2(2),primary key(repairs_id);用于存储宿舍物品损坏报修的基本信息，便于宿舍管理，与信息的查删改；3、操作表中的数据操作类型数据操作SQL语句说明插入数据insert into drom_table values(1,男,03760000001,小李,101001);insert into drom_table values(2,女,03760000002,小红,101002);insert into worker values(101002,小红,宿管,1000,女to_date(20110101000000,YYYYMMDDHH24MISS);insert into worker values(101001,小李,宿管,1000,男to_date(20110101000000,YYYYMMDDHH24MISS);insert into worker values(101003,王郭斌,保洁,1200,男,1310376666,to_date(20120101000000,YYYYMMDDHH24MISS);insert into student values(141401101,杨贝,女,河南,to_date(19970101000000,YYYYMMDDHH24MISS),to_date(20140901000000,YYYYMMDDHH24MISS),信息工程学院,计算机应用技术,一班,张莉2,101);insert into student values(141401102,程明恒,男,河南,to_date(19950101000000,YYYYMMDDHH24MISS),to_date(20140901000000,YYYYMMDDHH24MISS),信息工程学院,计算机应用技术,一班,张莉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,电灯,否);insert into repairs values (1,已上报,是);修改数据update worker set salary=1500 where wor_id=101001;删除数据delete from worker;truncate table worker;查询数据select *from worker;4、设计视图视图名对象定义SQL语句说明sex_stucreate view sex_stu as select stu_id,stu_name,stu_sex from student where stu_sex=女 with check option;创建一个带检查约束的视图。显示性别为女的学生信息drom_selectcreate view drom_select as select * from drom_table where drom_id=1 with read only;创建一个只读视图。显示宿舍楼编号为1的全部信息。删除视图drop view + 视图名5、设计索引索引名对象定义SQL语句说明wor_idcreate index wor_id on worker(wor_id);工人信息表列为员工编号的位图索引wor_namecreate unique index wor_ name on worker(wor_name);工人信息表列为员工名称的唯一索引wor_salaryquery rewrite create index wor_salary on worker(round(salary);工人信息表列为工资的函数索引data_worcreate index data_wor on worker(wor_date);工人信息表列为雇佣日期的B树索引6、设计同义词同义词名对象定义SQL语句说明worker_syncreate public synonym worker_syn for worker;工人信息表公有同义词student_syncreate public synonym student_syn for student;学生信息表公有同义词drom_syncreate private synonym drom_syn for drom_table;宿舍楼信息表私有同义词room_syncreate private synonym room_syn for room;宿舍信息表私有同义词7、设计序列序列名对象定义SQL语句说明worker_seqcreate sequence worker_seq increment by1start with 1maxvalue 100nominvaluenocyclenocache工人信息表中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、设计存储过程存储过程名对象定义SQL语句说明pro_count_typecreate or replace procedure pro_count_type(type varchar2)as n_count number;beginselect c