课程编号：b080109004
数据库应用程序设计实践报告。
东北大学软件学院。
1．关系数据库。
设计以下三个表：
客户信息表：
客户号 (主码) number(4)
客户姓名 varchar2(20)
客户类型varchar2(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主码：clientno
device主码：deviceno
monthlyneddpay主码：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 client
addconstraint pk_client_clientno primarykey(clientno)
usingindex
计费设备表：
createtable device
deviceno number(4)notnull,clientno number(4),typechar(1)
altertable device
addconstraint 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 monthlyneedpay
addconstraint pk_monthlyneedpay_id primarykey(id)
usingindex
2． 利用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) from monthlyneedpay where clientno=1001;
第三部分：（理解关系运算）
1． 已知关系表r和s如下：
给出差运算r-s和s-r的结果；
r-ss-r
2．描述下面查询的结果，如果将union用except替代，又会有什么样的查询结果？
select a
from r, s
where =
unionselect a
from r, s
where =
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 where to_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’)=2016 and
to_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 and to_char(payday,’yyyy’)=2015 order by clientno asc;
2. 查询当前年份欠费记录超过5条以上的用户。
select*from(selectsum(casepaystatuswhen'0'then1else0end)asquantityfrommonthlyneedpaywhereto_char(logdate,'yyyy')=2016'groupbyclientno)wherequantity>=5;