学生宿舍管理系统数据库部分设计.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.2NOTNULL工资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 tabledrom_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,女,13103764444，to_date（20110101000000，YYYYMMDDHH24MISS）；insert into worker values(101001,小李，宿管，1000，男，13103765555，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），信息工程学院，计算机应用技术，一班,张莉,15936421111，2，101）；insert into student values(141401102，程明恒，男,河南，to_date(19950101000000,YYYYMMDDHH24MISS），to_date(20140901000000，YYYYMMDDHH24MISS）,信息工程学院,计算机应用技术,一班，张莉，15936421111，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 count（*) into n_count from worker where wor_type=type;dbms_output.put_line(工作类型为|type的员工人数为：|n_count）；exceptionwhen no_data_fount thendbms_output。put_line（工作类型不存在）;end；根据输入的工作类型统计该类型的员工人数9、设计函数函数名对象定义SQL语句说明getsalcreate or replace function getsal（sno number)return numberisvsal number；beginselect salary into vsal from worker where wor_id=sno;return vsal； exception when too_many_rows thendbms_output。put_line（too many rows）; when others thendbms_output.put_line(error)； end;创建一个函数，以员工编号查询对应员工工资10、设计程序包程序包名对象定义SQL语句说明11、设计触发器触发器名对象定义SQL语句说明12、设计数据库用户用户名定义用户SQL语句权限分配SQL语句说明13、备份与恢复操作类型对应操作的SQL语句说明备份恢复三、体会：设计者:上交时间：