数据库期中试卷答案.doc

1、南昌航空大学 20112012 学年第二学期期中考试课程名称：数据库系统概论 期中卷 100 分钟题号 一 二 三 四 合计满分 15 18 57 10 100实得分一、设计题（共 15 分）请设计一个物资数据库。物资实体型，属性包括：物资号，物资名，物资类别。供应公司实体型，属性包括：公司号，公司名，公司所在城市，公司电话，联系人。仓库实体型，属性包括：仓库号，仓库名，面积。对供应公司供应物资存有供应日期、供应数量和供应单价，一种物资可由多个供应公司提供，一个供应公司可以提供多种物资。一个仓库存放多项物资，一项物资只存放在一个仓库中。1、 画出 E-R 图。 （10 分）2、将 E-R 图转

2、换为关系模型。 （5 分）2物资（物资号，物资名，物资类型，仓库号）公司（公司号，公司名，所在城市，电话，联系人） 供应物资（物资号，公司编号，供应日期，供应数量，供应单价）仓库（仓库号，仓库名，面积）班级-学号-姓名-重修标记评阅人 得分m仓库号物资 公司供应物资号物资类型物资名供应单价 联系人电话所在城市公司名公司号供应数量供应日期仓库存放nm1面积仓库名一、 二、关系代数程序题（共 18 分。 ）二、设有三个关系：S(S#,SNAME,AGE,SEX)SC(S#,C#,GRADE)C(C#,CNAME,TEACHER)说明：S#学号 SNAME学生姓名 AGE年龄 SEX性别C#课程号

3、CNAME课程名 TEACHER教师 GRADE成绩试用关系代数写出查询语句。(1)查询吴立老师所授课程的课程号、课程名。 （3 分）C#,CNAME(TEACHER=吴立 (C)(2)查询年龄大于 20 岁的女学生的学号与姓名。 （3 分）S#,SNAME(AGE20SEX=女 (S)(3)查询学号为 S1 学生所学课程的课程名与任课教师名。 （4 分）CNAME,TEACHER(S#=S1(SCC)(4)查询至少选修刘晨老师所授课程中一门课程的男学生的姓名。 （4 分）SNAME(SEX=男TEACHER= 刘晨 (SSCC)(5)查询选修课程包含刘晨老师所授课程的学生学号。 （4 分）S

4、#(TEACHER=刘晨 (SSCC) 或 S#(TEACHER=刘晨 (SCC)或 S#,C#(SC)c#(TEACHER=刘晨 (C)或S#,C#(SC)TEACHER=刘晨 (C)或三、 三、SQL 程序题（共 57 分。 ）1、根据以下表要求，建立以下第(1)和第(3)张表（10 分） 。（1）/*员工人事表employee */列名 数据类型 是否为空 主外键约束 列含义emp_no char(5) Not null primary key 员工编号emp_name char(10) Not null 员工姓名评阅人 得分评阅人 得分sex char(1) Not null 性别de

5、pt char(4) Not null 所属部门title char(6) Not null 职称date_hired datetime Not null 到职日birthday datetime Null 生日salary int Not null 薪水addr char(50) null 住址Mod_date datetime Default(getdate() 操作者（2）/*客户表customer */列名 数据类型 是否为空 主外键约束 列含义cust_id char(5) Not null primary key 客户号cust_name char(20) Not null, 客户

6、名称addr char(40) Not null, 客户住址tel_no char(11) Not null, 客户电话zip char(6) null 邮政编码（3）/*销售主表sales */列名 数据类型 是否为空 主外键约束 列含义order_no int Not null primary key 订单编号cust_id char(5) Not null, Foreign key 客户号sale_id char(5) Not null, 业务员编号tot_amt numeric(9,2) Not null, 订单金额order_date datetime Not null, 订货日期s

7、hip_date datetime Not null, 出货日期invoice_no char(10) Not null 发票号码（4）/*销货明细表sale_item */列名 数据类型 是否为空 主外键约束 列含义order_no int Not null, 订单编号prod_id char(5) Not null, primary key 产品编号qty int Not null 销售数量unit_price numeric(7,2) Not null 单价order_date datetime null 订单日期（5）/*产品名称表product */列名 数据类型 是否为空 主外键约

8、束 列含义prod_id char(5) Not null primary key 产品编号prod_name char(20) Not null 产品名称CREATE TABLE employee(emp_no CHAR(5) PRIMARY KEY,emp_name char(10) not null,sex char(1) not null,dept char(4) not null,title char(6) not null,date_hired datetime not null,birthday datetime,salary int not null,addr char(50)

9、,mod_date datetime DEAFAULT(getdate();CREATE TABLE sales (order_no int PRIMARY KEY,Cust_id char(5) not null,Sale_id char(5) not null,Tot_amt numeric(9,2) not null,Order_date datetime not null,Ship_date datetime not null,Invoice_no char(10) not null,FOREIGN KEY(Cust_id) REFERENCES customer(cust_id);2

10、、通过 T-SQL 语句修改表约束，在表 employee 加入 CHECK 约束：输入的员工编号必须以 E 开头的 5 位数编号，性别只能为 M/F。 （4 分）ALTER TABLE employee ADD CONSTRAINT c1 CHECK(emp_no LIKE (E0-90-90-90-9)ALTER TABLE employee ADD CONSTRAINT c2 CHECK(sex IN (M,F)3、创建一个视图 view_customer，该视图只含上海客户信息，即客户号、客户姓名、住址。 （5 分）CREATE VIEW view_customer ASSELECT

11、cust_id,cust_name,addr FROM customer WHERE addr LIKE%上海%;4、删除视图 view_customer （3 分）DROP VIEW view_customer;5、对表 customer 添加一条记录数据（00001， 张三， 北京东路 1 号 ，13612345678, 330001） 。 （3 分） INSERT INTO customer VALUES(（00001， 张三， 北京东路 1 号， 13612345678, 330001);6、修改00001号客户的电话为15812345678 。 （3 分） UPDATE custom

12、er SET tel_no= 15812345678 WHERE cust_id=00001;7、删除表 employee 中所有姓“陈”的员工数据。 （3 分）DELETE FROM employee WHERE emp_name LIKE 陈%;5、使用 SQL 语句实现下述查询操作。1）查询表中所有姓刘的职工的工号，部门，薪水（4 分）Select emp_no ,dept,salary From employee Where emp_name like 刘%2）查询所有定单金额高于 20000 的所有客户编号（4 分）Select cust_id from sales where to

13、t_amt200003）查询每位客户每次订购的详细清单，要求显示出客户号，客户名，产品号，产品名，数量、单价、订单日期。 （5 分）select a.cust_id, cust_name,c.prod_id,prod_name,qty,unit_price, order_datefrom customer a,sales b, sale_item c ,product dwhere a.cust_id=b.cust_id and b.order_no=c.order_no andc.prod_id=d.prod_id4）计算出一共销售了几种产品。 （3 分）selectcount(distin

14、ctprod_id)fromsale_item 5）计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排序。（5 分）selectprod_id,sum(unit_price*qty)fromsale_itemgroupbyprod_idorderbysum(unit_price*qty) desc 6）用存在量词查找没有订货记录的客户名称。 （5 分）select cust_namefrom customer awhere not exists(select *from sales bwhere a.cust_id=b.cust_id)四、 四、触发器（10 分）创建一个触发器

15、，实现级联更新：当更新 employee 表中 emp_no 列的值时，同时更新 sales 表中的 sale_id 列的值，并且一次只能更新一行。CREATE TRIGGER update_deptAFTER UPDATE OF emp_no ON employeeFOR EACH ROWAS BEGINUPDATE SALESSET SALE_ID=NEW.EMP_NOWHERE SALE_ID=OLD.EMP_NO;END ;或CREATE TRIGGER update_dept ON employeeAFTER UPDATE OF emp_no AS BEGINDECLARE eno

16、char(5),enonew char(5)SELECT eno = EMP_NOFROM DELETED ;SELECT enonew = EMP_NOFROM INSERTED ;UPDATE SALESSET SALE_ID= enonewWHERE SALE_ID= eno;END ;评阅人 得分设有一个 SPJ 数据库，包括 S，P，J，SPJ 四个关系模式：（1）求供应工程 J1 零件的供应商号码 SNO： Sno( Jno=J1（SPJ）)（2）求供应工程 J1 零件 P1 的供应商号码 SNO： Sno( Jno=J1Pno=P1 (SPJ)（3）求供应工程 J1 零件为红色的供应商号码 SNO： Sno( Jno=j1 （ COLOR=红 （P ）SPJ）)（4）求没有使用天津供应商生产的红色零件的工程号 JNO： Jno(J)- JNO（ city=天津Color=红 （SSPJ P）（5）求至少用了供应商 S1 所供应的全部零件的工程号 JNO： Jno，Pno (SPJ) Pno（ Sno=S1 （SPJ） ） Jno，Pno ( Sno=S1(SPJ) Pno（ Sno=S1 （SPJ ） ）