课程编号：B080109004数据库应用程序设计实践报告东北大学软件学院1．关系数据库设计以下三个表：客户信息表:∙客户号(主码) NUMBER(4)∙客户姓名V ARCHAR2(20)∙客户类型V ARCHAR2(20)∙地址VARCHAR2(20)∙余额NUMBER(7,2)计费设备表∙计费设备号(主码) NUMBER(4)∙客户号(外码)NUMBER(4)∙设备类别（01，02）Characters(1)应收费用表∙年月Date∙计费设备号(外码)NUMBER(4)∙基本费用NUMBER(7,2)∙附加费用1 NUMBER(7,2)∙附加费用2 NUMBER(7,2)∙应收违约金NUMBER(7,2)∙实收违约金NUMBER(7,2)∙减免违约金NUMBER(7,2)∙收费标志（0未交费，1已交费）Characters(1)第一部分：1．指出你所设计表的各种键值，在选择时不要考虑性能问题。

1)指出每张表是否存在主码，若存在，请指出具体的主码，并说明原因。

CLIENT主码:CLIENTNODEVICE主码:DEVICENOMONTHLYNEDDPAY主码:ID这些能够作为主码的字段的数据都是唯一的，因此能够进行唯一性标识，能够作为主码来使用。

2)指出每张表是否存在备用码（除了主码之外的所有候选码），若存在，请指出所有的备用码，并说明原因。

Client和Device表不存在候选码，Montthlyneedpay 中的DeviceNo和logdate 可以作为一个候选码。

3)指出各表中存在的外码和完整性约束，并说明原因。

Client表中没有外码，但是clientno不能为空，因为它是主码。

DEVICE:外码是CLIENTNO，Deviceno不能为null，因为它是主码MONTHLYNEDDPAY:外码有DEVICENO和CLIENTNO。

ID不能为空2．列出各表所有列和各列的域（数据类型和格式），并说明理由。

CLIENT:CLIENTNO是主码DEVICE: DEVICENO是主码MONTHLYNEDDPAY: ID是主码第二部分：写出如下SQL语句：1．用DDL语言中的CREATE TABLE语句创建以上三张表，并确定指定了表的主码和备用码；客户信息表:createtable CLIENT(CLIENTNO NUMBER(4)notnull,CLIENTNAME VARCHAR2(20),ADDRESS VARCHAR2(20),BALANCE VARCHAR2(20))altertable CLIENTaddconstraint PK_CLIENT_CLIENTNO primarykey(CLIENTNO)usingindex计费设备表:createtable DEVICE(DEVICENO NUMBER(4)notnull,CLIENTNO NUMBER(4),TYPECHAR(1))altertable DEVICEaddconstraint PK_DEVICE_DEVICENO primarykey(DEVICENO)usingindex应收费用表:createtable MONTHLYNEEDPAY(IDNUMBER(10)notnull,DEVICENO NUMBER(4),CLIENTNO NUMBER(4),LOGDATE DATE,SFROM NUMBER(10),STO NUMBER(10),BASICFEE NUMBER(7,2),ADDFEE1 NUMBER(7,2),ADDFEE2 NUMBER(7,2),LATEFEE NUMBER(7,2),NEEDPAY NUMBER(7,2),ACTUALPAY NUMBER(7,2),PAYDAY DATE,PAYSTATUS CHAR(1))altertable MONTHLYNEEDPAYaddconstraint PK_MONTHLYNEEDPAY_ID primarykey(ID)usingindex2．利用INSERT语句向客户信息表中插入1条客户记录；insertinto client(clientno,clientname,address,balance)values(1,'张三','沈阳市和平区东北大学','123.09');3．利用INSERT语句向计费设备表中为该客户插入2条设备记录。

Insert into device(deviceno,clientno,type) values(1,1001,‟1‟);Inser into device(deviceno,clientno,type) values(2,1002,‟1‟);4．利用INSERT语句向应收费用表中为该客户插入2个月份的应收费信息。

Insert into monthlyneedpay(id,deviceno,clientno,logdate,sfrom,sto,basicfee,addfee1,addfee2,latefee,needpay,actualpay,payday,paystatus)values(1,1,1001,to_date(…2015-1-31‟,‟YYYY-MM-DD‟),120,150,10,1,2,0,14.7,0,to_date(…2015-2-5‟,‟yyyy-mm-dd‟,),0)Insert into monthlyneedpay(id,deviceno,clientno,logdate,sfrom,sto,basicfee,addfee1,addfee2,latefee,needpay,actualpay,payday,paystatus)values(2,2,1001,to_date(…2015-2-28‟,‟YYYY-MM-DD‟)150,180,10,1,2,0,14.7,0,to_date(…2015-3-5‟,‟yyyy-mm-dd‟,),0)5．在不考虑附加费和违约金的情况下，给定一个客户号，查询该客户号下所有设备累计应收基本费用；Select sum(needpay)+sum(addfee1)+sum(addfee2)+sum(latefee) frommonthlyneedpay where clientno=1001;第三部分：（理解关系运算）1．已知关系表r和s如下：给出差运算r-s和s-r的结果；R-SS-R2．描述下面查询的结果，如果将UNION用EXCEPT替代，又会有什么样的查询结果？( SELECT AFROM r, sWHERE r.a = s.d)UNION( SELECT AFROM r, sWHERE r.c = s.d);UNION 这条语句是查询数据库中r表的a字段的值和s表中的d字段的值，r表中c字段的值和s表中d字段的值相等的部分，取出相等的部分后，相同的结果只保留一个。

将union 换位EXPECT后得到的结果是只存在表a中和d相等的部分，并且去掉重复行。

2．SQL请写出针对以下问题的SQL语句（每一问必需用一条SQL语句实现，但该SQL语句可以包含子查询）。

1.查询姓张的所有客户信息Select * from clinet where clientname like …张%‟;2.查询客户号1001的客户拥有的计费设备个数。

Select count(*) from device where clientno=1001;3.计算客户号1001在2016年1月产生的附加费用1和附加费用2；Select addfee1,addfee2 from monthlyneedpay whereto_char(logdate,‟yyyy‟)=2016 and to_char(logdate,‟mm‟)=1;4.查询客户号1001在2016年的历史缴费记录；Select * from paylog where clientno=1001 and to_char(payday,‟yyyyy‟)=2016;5.更新客户号1001在2016年1月份的收费标识为1；Update monthlyneedpay set paystatus=‟1‟ where to_char(payday,‟yyyy‟)=2016andto_char(payday,‟mm‟)=1;6.查询应收费用表，先按照客户号升序排序，再按照年份排序降序排序。

Select * from monthlyneedpay order by clientno asc,payday desc;3．Advanced SQL在该练习中，我们根据银行代收费系统的需求，完成以下高级SQL语句的编写：1.查询前一年所有客户的欠费记录，按照客户编号升序排列。

Select clientno,needpay,payday from monthlyneedpay where paystatus=0 andto_char(payday,‟yyyy‟)=2015 order by clientno asc;2.查询当前年份欠费记录超过5条以上的用户。

select * from (select sum(case PAYSTATUS when '0' then 1 else 0 end) as QUANTITY from monthlyneedpay where to_char(LOGDATE,'yyyy')='2016' groupby CLIENTNO)where QUANTITY>=5;3计算客户号1001的客户，其名下所有设备的应收基本费用之和，附加费用1之和，附加费用2之和。

Select count(needpay),count(addfee1),count(addfee2) from monthlyneedpaywhere clientno=1001;4.计算客户号1001在2016年1月份，计费设备号100的应收违约金。

Select latefee from monthlyneedpay where clientno=1001 and deviceno=100and to_char(payday,‟yyyy‟)=2016 and to_char(payday,‟mm‟)=1;5.计算银行代号为19的银行在20160130产生的缴费总次数和总金额，冲账的记录不记录总次数和总金额中。

select banktotalcount,banktotalmoney from checktotal where bank_id=19 and to_cha r(checkdate,,‟yyyy‟)=2016 and to_char(checkdate,‟mm‟)=1 and to_char(checkdate,‟dd‟)=30;4．Programming with Transactions and Procedure Process编写存储过程完成以下问题(如果不熟悉存储过程的编写，也可以顺序执行多条SQL语句来实现功能需求)：第一部分【查询】：交易描述：判断客户号是否存在，然后根据客户号取得客户姓名，地址，应收费用。