数据库建模案例

SQL

一、（难度：1）某待办事宜管理应用，需要用数据库存储数据。数据包含：事项名称、截止日期、办理状态、完成日期，应用对数据处理的要求可描述如下：

1. 可根据关键词、办理状态、完成日期等查找待办事项；
2. 可统计已完成的事项数目；
3. 可统计未完成的事项数目；
4. 可统计已过截止日期，但仍未办理的事项数目；
5. 可列出已过截止日期，但仍未办理的事项（含过期天数），按过期天数进行降序排序；
6. 可列出未过截止日期，未办理的事项数目；
7. 可列出未过截止日期，未办理的事项（含距截止日期的天数），按距截止日期的天数进行升序排序。

请画出ER图，建立数据字典，并完成定义数据库和表的SQL脚本；建立表后插入样例数据，对以上数据处理要求写出对应的SQL语句。

二、（难度：2）某公司需要用数据库存储员工数据，涉及的数据包含：部门名称、部门电话、部门地址、员工姓名、身份证号、性别、出生日期、年龄、学历、雇佣日期、解雇日期、职位等。在公司内部，每个员工只能在一个部门。对数据处理的要求：
1. 可按部门统计当前员工数目；
2. 可按姓名查找员工，并列出其所有信息以及所在部门的名称；
3. 可按年龄统计当前员工数目；
4. 可按性别统计当前员工数据；
5. 可按学历统计当前员工数目；
6. 可列出某一部门的全体当前员工；

请画出ER图，建立数据字典，并完成定义数据库和表的SQL脚本；建立表后插入样例数据，对以上数据处理要求写出对应的SQL语句。

三、（难度：2）某公司需要记录员工在项目上的业绩（工时数衡量），数据包括：员工姓名、性别、职称、项目名称、工时数、单位工时薪酬、薪酬等。其中，『单位工时薪酬』由职称确定，目前为：助理工程师 - 300元，工程师 - 500元，高级工程师 - 800元。对以上数据的处理要求为：
1. 按项目统计应发的薪酬总额，按薪酬总额降序排序；
2. 按员工统计获得的薪酬总额，按薪酬总额降序排序；
3. 按姓名查找员工并列出其所参与的所有项目及薪酬；
4. 按名称查找项目并列出其所有参与者的信息及薪酬；
5. 列出薪酬总额超出10000元的工程项目，并按总额降序排序。

请画出ER图，建立数据字典，并完成定义数据库和表的SQL脚本；建立表后插入样例数据，对以上数据处理要求写出对应的SQL语句。

四、（难度：3）某商店需要管理商品，并将商品进行了分类（分类没有级别限制）。数据包括：类别名称、商品名称、型号规格、数量、单价、金额小计等。对数据的处理要求为：
1. 能够列出所有顶级分类；
2. 能够列出所有底级分类；
3. 能够列出某一分类下的下一级分类；
4. 能够列出所有分类，包含名称和路径（诸如：『电器类>家用电器>厨房电器>...』）;
5. 能够列出某个分类下在全部子分类，包含名称和路径（诸如：『家用电器>厨房电器>...』）；
6. 可从某一子类开始，列出其上一级分类，直至顶级分类；
7. 可列出各个分类的下一级子分类的数目；
8. 列出某一分类及其所有下级子分类包含的所有商品。

五、（难度：4）某商店需要管理商品和销售，对商品和分类的管理如上题，商品增加进货价。现商店需要管理销售，涉及的数据包括客户、开票人、销售日期、商品明细等。对数据的处理增加如下：
1. 可按开票员统计历史销售总金额；
2. 可按客户统计历史购买额；
3. 可按商品统计历史销售额；
4. 可查询一定历史时期内某项商品的各年度（或各季度或各月）的销售情况；
5. 可按年度（季度或月份）统计各种商品的毛利润。

六、（难度：5）某学院含多个管理部门和教学系部，教学系部下设多个专业，各专业有不同入学年份的各个班级，每个班级都有一名教职员工担任班主任（一名员工可同时承担多个班级的班主任）。每个部门下有教职员工，每个教职员工只隶属于一个部门。各专业有特定计划课程，一名老师（含外聘）可承担多门课程的教学任务。学生的各科成绩要得到记录。学生在校期间的操行情况也要得到记录。预先考虑到的数据处理包括：
1. 可查询某部门下的所有员工；
2. 可统计学院各类职称的人员分布；
3. 可查看某专业的课程;
4. 可统计各班主任所负责的班级数；
5. 可查看每个学生的操行情况；
6. 可列出每个学生的各科课程成绩；
7. 可列出某专业的全部班级；
8. 可列出某位教师所担任的全部课程；
9. 可列出某部门负责的全部专业。