各位老师麻烦打个分给个评价

Question

前几天在群里看到有人问这个问题，由于毕业后就几乎没怎么碰过数据库，所以手痒想试一试，虽然我也是计算机专业毕业，但是感觉差距好大，当年我上学的时候根本没有老师出过这么复杂的题目。
假设系统数据库设计占50分的话，各位老师看完后麻烦看着给个分，呵呵。
题目：
学校有若干公寓，每栋5层，每层18个房间，每个房间4个学生，需要一个公寓管理系统实现管理。系统主要功能如下： （1）寝室分配：根据系别、年级、班级分配寝室。查询寝室状态和入住信息。 （2）学生管理：实现入住学生信息的维护和查询功能。 （3）信息查询：按公寓楼号、学生姓名等查询住宿信息。 （4）财产管理：实现对公寓财产的管理功能。 （5）出入登记：实现对学生搬出公寓的货物进行登记和对外来人员进行登记等功能。 （6）系统管理：参数设置（如公寓楼号、寝室房号、系别、年级、班级的设置） 
--数据库表设计
/*
 *  公寓信息表
 * HotelID   公寓编号
 * HotelName  公寓名称
*/
CREATE TABLE Hotel
(
HotelID varchar(32) NOT NULL,
HotelName varchar(32)
);
/*
 *  信息表
 * UnitID   信息编号
 * UnitMsg  信息内容
*/
CREATE TABLE Unit
(
UnitID varchar(32) NOT NULL,
UnitMsg varchar(128)
);
/*
 *  财产表
 * ResourceID   财产编号
 * ResourceName  财产名称
 * UnitID  财产单位
*/
CREATE TABLE Assets
(
AssetsID varchar(32) NOT NULL,
AssetsName varchar(64),
UnitID varchar(32) NOT NULL
);
/*
 *  房间表
 * RoomID   房间编号
 * HotelID  所属公寓
 * Floor  楼层信息
 * RoomName  房间号码
 * Res  备    注
*/
CREATE TABLE Room
(
RoomID varchar(32) NOT NULL,
HotelID varchar(32) NOT NULL,
Floor varchar(4),
RoomName varchar(8),
Res varchar(1024)
);
/*
 *  房间财产列表
 * RoomID   房间编号
 * AssetsID  财产编号
 * AssetsCount  财产数量
 * AssetsPrincipal  负责人
 * Res  备    注
*/
CREATE TABLE RoomAssets
(
RoomID varchar(32) NOT NULL,
AssetsID varchar(32) NOT NULL,
AssetsCount int,
AssetsPrincipal varchar(32),
Res varchar(1024)
);
/*
 *  房间财产出入登记表
 * RoomID   房间编号
 * AssetsID  财产编号
 * AssetsCount  财产数量
 * AssetsPrincipal  负责人
 * IOFlag  出入标志
 * Status  入：已分配，已领取；出：新借出，已归还
 * DataTime  发生日期
 * BackTime  出：归还日期；入：领取日期
 * Res  备    注
*/
CREATE TABLE RAInOut
(
RoomID varchar(32) NOT NULL,
AssetsID varchar(32) NOT NULL,
AssetsCount int,
AssetsPrincipal varchar(32) NOT NULL,
IOFlag varchar(32),--借出，拿入
DataTime Date,--发生的时间
BackTime Date,--如果是借出，归还日期
Res varchar(1024)
);
/*
 *  学校表
 * UniversityID   学校编号
 * UniversityName  学校名称
*/
CREATE TABLE University
(
UniversityID varchar(32) NOT NULL,
UniversityName varchar(64)
);
/*
 *  学院表
 * SeriesID  学院编号
 * UniversityID   学校编号
 * SeriesName  学院名称
*/
CREATE TABLE Series
(
SeriesID varchar(32) NOT NULL,
UniversityID varchar(32),
SeriesName varchar(64)
);
/*
 *  年级表
 * GradeID  年级编号
 * SeriesID   学院编号
 * GradeName  年级名称
*/
CREATE TABLE Grade
(
GradeID varchar(32) NOT NULL,
SeriesID varchar(32) NOT NULL,
GradeName varchar(32)
);
/*
 *  年级表
 * GradeID  年级编号
 * SeriesID   学院编号
 * GradeName  年级名称
*/
CREATE TABLE Class
(
ClassID varchar(32) NOT NULL,
GradeID varchar(32) NOT NULL,
ClassName varchar(32)
);
/*
 *  学生表
 * StudentID  学生编号
 * StudentName   学生姓名
 * Sex   学生性别
 * Birthday   出生日期
 * ClassID  学生所在班级
*/
CREATE TABLE Student
(
StudentID varchar(32) NOT NULL,
StudentName varchar(32),
Sex varchar(32),
Birthday Date,
ClassID varchar(32) NOT NULL
);
/*
 *  宿舍入住学生表
 * RoomID  房间号
 * StudentID   学生编号
 * BedNumber   床号
 * InFlag   已经入住，尚未入住
 * Res  备注
*/
CREATE TABLE RoomStudent
(
RoomID varchar(32) NOT NULL,
StudentID varchar(32),
BedNumber varchar(4),
InFlag varchar(32),
Res varchar(1024)
);
/*
 *  宿舍外来人员探访记录表
 * StudentID  学生编号
 * GuestID   来访者
 * DataTime   发生时间
 * LeaveTime   离开时间
 * Res  备注
*/
CREATE TABLE SGInOut
(
StudentID varchar(32),
GuestID varchar(32),
DataTime Date,
LeaveTime Date,
Res varchar(1024)
);
/*
 *  外来人员记录表
 * GuestID  来访者编号
 * GuestName   来访者姓名
 * GuestOrganise   来访者单位
 * Res  备注
*/
CREATE TABLE Guest
(
GuestID varchar(32),
GuestName varchar(32),
GuestOrganise varchar(32),
Res varchar(1024)
);
--可能用到的sql
--寝室分配:查询全部学生信息
SELECT s.studentid,s.studentname,s.sex,to_char(s.birthday,'yyyy-mm-dd')AS Bitthday,c.classname,g.gradeid,se.seriesname,u.unitmsg
FROM student s
LEFT JOIN CLASS c ON s.classid = c.classid
LEFT JOIN Grade g ON c.gradeid = g.gradeid
LEFT JOIN Series se ON g.seriesid = se.seriesid
LEFT JOIN University un ON se.universityid = un.universityid
LEFT JOIN Roomstudent rs ON s.studentid = rs.studentid
LEFT JOIN Unit u ON u.unitid = NVL(rs.Inflag,'11001')          
ORDER BY s.studentid;
--寝室分配:寝室状态及入住信息（学生）
SELECT r.roomid,r.roomname,h.hotelname,rsc.scount,nvl(s.studentname,'-'),nvl(u.unitmsg,'-'),rs.res
FROM Room r
LEFT JOIN Hotel h ON r.hotelid = h.hotelid
LEFT JOIN Roomstudent rs ON r.roomid = rs.roomid  
LEFT JOIN Student s ON rs.studentid = s.studentid
LEFT JOIN Unit u ON s.sex = u.unitid
INNER JOIN (
          SELECT r1.roomid roomid,COUNT(rs1.roomid) AS scount
          FROM Room r1
          LEFT JOIN Hotel h1 ON r1.hotelid = h1.hotelid
          LEFT JOIN Roomstudent rs1 ON r1.roomid = rs1.roomid  
          GROUP BY r1.roomid,r1.roomname) rsc
          ON r.roomid = rsc.roomid
ORDER BY h.hotelid,r.roomid;
--寝室分配:寝室状态及入住信息（寝室）
SELECT r.roomid,r.roomname,nvl(u.unitmsg,'-'),
       nvl(MAX(decode(rs.bednumber,'1',s.studentname)),'空') Bed_1,
       nvl(MAX(decode(rs.bednumber,'2',s.studentname)),'空') Bed_2,
       nvl(MAX(decode(rs.bednumber,'3',s.studentname)),'空') Bed_3,
       nvl(MAX(decode(rs.bednumber,'4',s.studentname)),'空') Bed_4,
       rs.res
FROM Room r
LEFT JOIN Hotel h ON r.hotelid = h.hotelid
LEFT JOIN Roomstudent rs ON r.roomid = rs.roomid  
LEFT JOIN Student s ON rs.studentid = s.studentid
LEFT JOIN Unit u ON s.sex = u.unitid
GROUP BY r.roomid,r.roomname,rs.res,u.unitmsg
ORDER BY r.roomid;
--信息查询:按公寓楼号、学生姓名等查询住宿信息
SELECT r.roomid,r.roomname,h.hotelname,s.studentname,u.unitmsg,rs.res
FROM Room r
LEFT JOIN Hotel h ON r.hotelid = h.hotelid
LEFT JOIN Roomstudent rs ON r.roomid = rs.roomid  
LEFT JOIN Student s ON rs.studentid = s.studentid
LEFT JOIN Unit u ON s.sex = u.unitid;
--财产管理的触发器
CREATE OR REPLACE TRIGGER T_RA
  AFTER INSERT OR UPDATE OR DELETE on RAInOut  
  for each row
DECLARE
       v_falg INT:=0;
       in_falg VARCHAR(32):='50001';
       out_falg VARCHAR(32):='50002';
       old_aCount INT;
       old_aPrincipal VARCHAR(32);
BEGIN
     CASE
         WHEN inserting THEN
              SELECT COUNT(*) INTO v_falg 
              FROM Roomassets ra 
              WHERE ra.roomid = :NEW.RoomID 
                    AND ra.assetsid = :NEW.AssetsID;
              
              --如果已经有此资产，返回资产数量，责任人
              IF v_falg<>0 THEN
                SELECT ra.assetscount,ra.assetsprincipal 
                       INTO old_aCount,old_aPrincipal
                FROM Roomassets ra
                WHERE ra.roomid = :NEW.RoomID AND ra.assetsid = :NEW.AssetsID;
              END IF;              IF :NEW.assetscount>0 THEN--如果请求操作资产数量为 正数              
                IF :NEW.IOFlag=in_falg THEN--如果是：入
                   IF old_aCount>0 THEN--如果已存在，则修改原记录
                         UPDATE Roomassets SET Assetscount = Assetscount + :NEW.Assetscount,assetsprincipal = :NEW.assetsprincipal
                                WHERE roomid = :NEW.RoomID AND assetsid = :NEW.AssetsID;
                   ELSE--如果不存在，则增加记录
                       INSERT INTO Roomassets VALUES(:NEW.RoomID,:NEW.AssetsID,:NEW.Assetscount,:NEW.AssetsPrincipal,'');
                   END IF;
                ELSIF :NEW.IOFlag=out_falg THEN--如果是：出
                      IF old_aCount>:NEW.Assetscount THEN
                         UPDATE Roomassets SET Assetscount = Assetscount -:NEW.Assetscount,assetsprincipal = :NEW.assetsprincipal
                                WHERE roomid = :NEW.RoomID AND assetsid = :NEW.AssetsID;
                      ELSIF old_aCount = :NEW.Assetscount THEN
                            DELETE Roomassets WHERE roomid = :NEW.RoomID AND assetsid = :NEW.AssetsID;
                      ELSE 
                           raise_application_error(-20010,'Don''t hava enough assets.');
                      END IF;
                END IF;
              ELSE
                  raise_application_error(-20011,'The asset number must be more than zero.');
              END IF;
              
         WHEN updating THEN
              dbms_output.put_line('update');
              IF :NEW.BackTime<:Old.DataTime THEN
                 raise_application_error(-20012,'The BackTime must be later than happened time.');
              END IF;
         WHEN deleting THEN
              raise_application_error(-20013,'Do not hava enough permission to delete.');
         END CASE;       
END T_RA;

Answer 1

另附测试用的sql如下：INSERT INTO Unit VALUES('30001','张');
INSERT INTO Unit VALUES('30002','把');
INSERT INTO Unit VALUES('30003','台');
INSERT INTO Unit VALUES('50001','入');
INSERT INTO Unit VALUES('50002','出');
INSERT INTO Unit VALUES('10001','男');
INSERT INTO Unit VALUES('10002','女');
INSERT INTO Unit VALUES('11001','未入住');
INSERT INTO Unit VALUES('11002','已入住');INSERT INTO Hotel VALUES('20001','一号公寓');
INSERT INTO Hotel VALUES('20002','二号公寓');
INSERT INTO Hotel VALUES('20003','三号公寓');INSERT INTO Assets VALUES('30001','桌子','30001');
INSERT INTO Assets VALUES('30002','椅子','30002');
INSERT INTO Assets VALUES('30003','饮水机','30003');INSERT INTO Room VALUES('4000001','20001','1','101','备注');
INSERT INTO Room VALUES('4000002','20001','2','202','备注');
INSERT INTO Room VALUES('4000003','20002','3','303','备注');
INSERT INTO Room VALUES('4000004','20002','4','404','备注');/*INSERT INTO RoomAssets VALUES('4000001','30001',1,'2010199004036724','备注');
INSERT INTO RoomAssets VALUES('4000001','30002',4,'2010199004036724','备注');
INSERT INTO RoomAssets VALUES('4000001','30003',1,'2010199004036724','备注');
INSERT INTO RoomAssets VALUES('4000002','30001',1,'2010199001016721','备注');
INSERT INTO RoomAssets VALUES('4000002','30002',4,'2010199001016721','备注');
INSERT INTO RoomAssets VALUES('4000002','30003',1,'2010199001016721','备注');*/INSERT INTO RAInOut VALUES('4000001','30001',1,'2010199004036724','50001',to_date('2010-12-8','yyyy-MM-dd'),'','备注');
INSERT INTO RAInOut VALUES('4000001','30002',4,'2010199004036724','50001',to_date('2010-12-7','yyyy-MM-dd'),'','备注');
INSERT INTO RAInOut VALUES('4000001','30003',1,'2010199004036724','50001',to_date('2010-12-6','yyyy-MM-dd'),'','备注');
INSERT INTO RAInOut VALUES('4000002','30001',1,'2010199001016721','50001',to_date('2010-12-8','yyyy-MM-dd'),'','备注');
INSERT INTO RAInOut VALUES('4000002','30002',4,'2010199001016721','50001',to_date('2010-12-7','yyyy-MM-dd'),'','备注');
INSERT INTO RAInOut VALUES('4000002','30003',1,'2010199001016721','50001',to_date('2010-12-6','yyyy-MM-dd'),'','备注');INSERT INTO University VALUES('6000001','北京艺术学院');
INSERT INTO University VALUES('6000002','中国话剧院');INSERT INTO Series VALUES('7000001','6000001','计算机系');
INSERT INTO Series VALUES('7000002','6000001','数学系');
INSERT INTO Series VALUES('7000003','6000001','化学系');
INSERT INTO Series VALUES('7000004','6000001','物理系');INSERT INTO Grade VALUES('8000001','7000001','一年级');
INSERT INTO Grade VALUES('8000002','7000001','二年级');
INSERT INTO Grade VALUES('8000003','7000001','三年级');
INSERT INTO Grade VALUES('8000004','7000001','四年级');INSERT INTO Class VALUES('9000001','8000001','一班');
INSERT INTO Class VALUES('9000002','8000001','二班');
INSERT INTO Class VALUES('9000003','8000001','三班');
INSERT INTO Class VALUES('9000004','8000001','四班');INSERT INTO Student VALUES('2010199001016721','米莱','10002',to_date('1990-01-01','yyyy-MM-dd'),'9000001');
INSERT INTO Student VALUES('2010199001026722','夏林','10002',to_date('1990-01-02','yyyy-MM-dd'),'9000001');
INSERT INTO Student VALUES('2010199001036723','杨晓芸','10002',to_date('1990-01-03','yyyy-MM-dd'),'9000001');
INSERT INTO Student VALUES('2010199004036724','陆涛','10002',to_date('1990-04-03','yyyy-MM-dd'),'9000001');INSERT INTO RoomStudent VALUES('4000002','2010199001016721','2','11001','备注');
INSERT INTO RoomStudent VALUES('4000002','2010199001026722','3','11002','备注');
INSERT INTO RoomStudent VALUES('4000002','2010199001036723','4','11001','备注');
INSERT INTO RoomStudent VALUES('4000001','2010199004036724','2','11001','备注');INSERT INTO Guest VALUES('1300001','华子','无业游民','备注');
INSERT INTO Guest VALUES('1300002','陆 露','无业游民','备注');
INSERT INTO Guest VALUES('1300003','方灵珊','无业游民','备注');INSERT INTO SGInOut VALUES('2010199001026722','1300001',to_date('1990-01-02','yyyy-MM-dd'),to_date('1990-01-02','yyyy-MM-dd'),'备注');看过别忘记打分哦！

Answer 2

DROP TABLE Hotel;
DROP TABLE Unit;
DROP TABLE Assets;
DROP TABLE Room;
DROP TABLE RoomAssets;
DROP TABLE RAInOut;
DROP TABLE University;
DROP TABLE Series;
DROP TABLE Grade;
DROP TABLE Class;
DROP TABLE Student;
DROP TABLE RoomStudent;
DROP TABLE SGInOut;
DROP TABLE Guest;

Answer 3

这个东西本来还真是C++，SQL Server做的，哈哈，你真是算来对地方咯。

Answer 4

最好画个ER图或者大概的流程图什么的好理解点
光看SQL没太明白你系统的关联性

Answer 5

在表RAInOut有，IOFlag字段，用来标示资产的进出状态，目前我只设立了：入，出两种状态，对于笔记本被盗类的问题，只需要在unit表中增加一个被盗状态就可以了，因为IOFlag字段关联到Unit表的UnitID。

Answer 6

我毕业后就没接触过数据库，Oracle也是刚刚安装的，稍等我今天晚上回去看看能不能找个工具画个图出来。

Answer 7

不错，做开发足够了，
没有接触过数据库？那你一直做的什么工作？有什么东西不用数据库的？
难道不存储数据？难不成用TXT文件？

Answer 8

--你这个太长了，眼都花了～
--俺就看下你的需求得了！
（1）寝室分配：根据系别、年级、班级分配寝室。查询寝室状态和入住信息。 
    --难道男女混住？哇塞～太爽了！（2）学生管理：实现入住学生信息的维护和查询功能。 （3）信息查询：按公寓楼号、学生姓名等查询住宿信息。 
    --学校中同名的同学真的很多，而且有可能住一个公寓。（4）财产管理：实现对公寓财产的管理功能。 
    --财产这个，你用int，确定没问题？你的单位是什么呢？元？角？分？（5）出入登记：实现对学生搬出公寓的货物进行登记和对外来人员进行登记等功能。 
    --搬出公寓的货物？这个可不好概述！
    --外来人员，如果是访问，那么是不是该加上要访问谁？以及访问人的联系方式呢？万一是小偷呢！（6）系统管理：参数设置（如公寓楼号、寝室房号、系别、年级、班级的设置）  

Answer 9

计算机行当有一个超级垃圾的方向叫外包，外包中有一项叫对日外包。如果有涉及到sql的，几乎都被写好了。最悲剧的是我连这我也没碰到，打印机上面貌似目前没有装数据库的是吧，呵呵，XML足够了。

Answer 10

1.学生表中有性别列
2.资产我设计了3个表，Room，Assets，RoomAssets,你说的int不知道是什么意思，Assets对应Unit表的主键UnitID，所以单位可以随时添加。
3.访问目标已经存在了，有3个表Student，Guest，SGInOut。外来人员表Guest有来访人员的单位信息（公司等），但是没有联系方式，我感觉记录来访者的联系方式没必要，反正能找到他要访问的人。

Answer 11

16楼的意思是：财产不一定是一个整数吧，比如是20.5元呢？这时候用int是不行的，所以最好改成float好点