《数据库应用程序设计实践》

课程编号：B080109004数据库应用程序设计实践报告姓名田红策学号20124702班级软件1202指导教师张莉开设学期2014-2015第二学期开设时间第5周——第7周报告日期2015年5月4日评定成绩评定人评定日期东北大学软件学院11．关系数据库第一部分1)指出你所设计表的各种键值，在选择时不要考虑性能问题。A.指出每张表是否存在主码，若存在，请指出具体的主码，并说明原因。每张表都存在主码，因为每本书都有唯一的一个ISBN号，所以ISBN号是表的主码，同时每个publisher都有唯一的一个publisherId,所以publisherId是表的主码。B.指出每张表是否存在备用码（除了主码之外的所有候选码），若存在，请指出所有的备用码，并说明原因。Book表没有备选码，publisher表可以有一个address备选码，因为publisher有基本上有一个唯一的address.C.指出各表中存在的外码和完整性约束，并说明原因。Book表中publisherId是外键，它参照publisher表的publisherId主码。2)．列出各表所有列和各列的域（数据类型和格式），并说明理由。第二部分：写出如下SQL语句：1)用DDL语言中的CREATETABLE语句创建以上两张表，并确定指定了表的主码和备用码；BookDDL:表名属性名称类型备注BookPrimary_authorvarchar(50)Titlevarchar(40)ISBNChar(8)Publisher_idinteditionChar(8)pricefloatdescriptionVarchar(100)Publication_datedatePublisherPublisher_idIntNameVarchar(50)AddressVarchar(150)2PublisherDDL:2)利用INSERT语句向每张表中以上四条记录；A.向book表中插入数据:B.向publisher表中插入数据:3)写SQL语句检索两个出版人之一所出版书籍的标题和价格，列出你所用到的所有选择、投影和连接操作，并说明各个操作在查询中的作用；SQL：该SQL中用到了自然连接就是将book表中publisher_Id与publisher表中publisher_id相等的行连接起来。4)写SQL语句，检索特定书名（比如说是”FundamentalsofDatabaseSystems”）的出版人。3SQL：该SQL中用到了自然连接就是将book表中publisherId与publisher表中publisherid相等的行连接起来。第三部分：1)已知关系表r和s如下：表rABCabcdafcbd表sDEFbgadaf给出差运算r-s和s-r的结果；接着创建表S,同时填充数据:4然后执行以下差集运算：r-s运行结果截图:s-r运行结果图：2)描述下面查询的结果，如果将UNION用EXCEPT替代，又会有什么样的查询结果？(SELECTMemNoFROMMEMBER,BOOKWHEREMemNo=BorrowerMemNoANDCallNumber='QA76.9.D26C66')UNION(SELECTMemNoFROMMEMBER,BOOKWHEREMemNo=BorrowerMemNoANDCallNumber='QA76.9.D7E53');5Uion连接代表两个集合的并集，except表示集合的差集.2．SQL运行给定的SQL语句生成一个图书馆数据库。注意Book表中的每一条记录代表一本具体的书，因此，如果图书馆中有三本名为DBMS的书，Book表中将有三条相关记录，每条代表一本书。请写出针对以下问题的SQL语句（每一问必需用一条SQL语句实现，但该SQL语句可以包含子查询）。1)列出作者为Churchill的所有书籍的名称和出版年份；2)检索firstname为John或Susan的借阅者所借阅所有图书的标题；3)列出同时借阅Iliad和Odyssey这两本书的所有借阅者的names和IDs；64)列出借阅了Collins所写的所有图书的读者的names和IDs，假设一位读者可以同时借阅一本书的多个复本；5)查找借阅了lastname为Tanenbaum的作者所著作的任一本书籍的所有读者的电话号码；76)查找借阅了三本书以上的读者，并列出他们的names、IDs以及所借阅图书的数量，查询结果按所借阅图书的数量降序排列；7)列出没有借阅图书的所有读者；8)按字母顺序列出是Pittsburgh居民（电话号码以412开头）并没有借阅图书PittRoads的所有读者的firstnames。3．AdvancedSQL1)运行CREATETABLE和INSERT语句；82)编写并运行SQL语句，删除所有表中的记录；3)编写并运行SQL语句，按照以下方式来修改数据库：A.创建上面列出的还未创建的数据库表，当设定表中各列的类型和长度时，需要考虑给定的用于填充数据的INSERT语句；CREATETABLE`purchase_order4`(`PoNum`int(10)NOTNULL,`Qty`int(3)NOTNULL,`OrderDate`datetimeDEFAULTNULL,`ReceiveDate`datetimeDEFAULTNULL,PRIMARYKEY(`PoNum`));CREATETABLE`read_by4`(`callNumber`char(8)NOTNULL,`libId`char(6)NOTNULL,`timesRead`varchar(100)DEFAULTNULL,PRIMARYKEY(`callNumber`,`libId`),KEY`libId_ref`(`libId`),CONSTRAINT`callNumber_ref`FOREIGNKEY(`callNumber`)REFERENCES`title4`(`callnumber`),CONSTRAINT`libId_ref`FOREIGNKEY(`libId`)REFERENCES`member4`(`libid`));CREATETABLE`supplier4`(`supplier_id`int(10)NOTNULL,`name`varchar(100)NOTNULL,9`address`varchar(150)NOTNULL,PRIMARYKEY(`supplier_id`));CREATETABLE`ordered4`(`callNumber`int(10)NOTNULL,`PoNum`int(10)NOTNULL,`supplier_id`int(10)NOTNULL,PRIMARYKEY(`callNumber`,`PoNum`,`supplier_id`));B.将表TITLE4中的ISBN和Name字段设置成备用码，而且它们不能取空值；C.在所有表中添加指定的外码约束；D.在表MEMBER4中添加Join_date和Gender两列；E.指定表READ_BY4中TimesRead列不能取负值。4)根据给定的INSERT语句填充数据库；5)编写SQL语句实现如下功能：A.显示ID以字母A%B或A&B开头，并且加入图书馆不晚于Nov.30,1997的所有读者的ID,firstname和joiningdate；selectlibId,fname,join_datefrommember4wherelibIdlike'A\%B%'orlibIdlike'A&B%'andjoin_date='1997-11-30';10B.显示满足如下条件所以书籍的name,ISBN和callnumber，这些书籍由两个以上的供应商提供，并且订购的数量大于10，显示时按书籍的总订购数量非升序排列；selectcallnumber,name,isbnfromtitle4naturaljoinordered4naturaljoinpurchase_order4wherepurchase_order4.qty10;C.显示所有借阅图书的数量少于图书馆总藏书5％的读者的IDs和firstnames，结果按借阅图书数量非降序进行排列；selectmember4.libid,member4.fnamefrombook4joinmember4onbook4.borrowerid=member4.libidgroupbymember4.libidhavingcount(*)=(selectsum(qty)*0.05frompurchase_order4)orderbycount(*)asc;D.按字母顺序列出所有至少读一本书两次的女读者的firstnames和IDs；selectdistinctmember4.libid,member4.fnamefrommember4joinread_by4onmember4.libid=read_by4.libidwhereread_by4.timesread=2andmember4.gender='F'orderbymember4.fname;11E.列出满足如下条件的供应商的names和他们各自的订单平均订购书籍数量，要求他们的订单平均订购书籍数量大于系统中所有订单的平均订购书籍数量；selectpoNum,sum(qty)fromordered4naturaljoinpurchase_order4natrualjoinsupplier4groupbypoNum;F.按字母顺序列出满足如下要求的所有图书馆中男读者的firstnames和IDs，要求他们在Oct.10,1995之前加入图书馆，并且仅读了五本或更少的不同的图书，对于同一本图书，他们曾没有读过多遍。selectfname,libIdfrommember4wheregender='M'andjoin_date'1995-10-10'andlibIdin(selectlibIdfromread_by4wheretimesRead=1)andlibIdin(selectlibIdfromread_by4groupbylibIdhavingcount(callNumber)5);4.ERModels1.ER图表122)实体的属性与类型表名属性名称单值或多值简单属性或复合属性Bookauthor多值简单属性Title单值简单属性BookbookOrderorder0.10.11.*1.*customercustomerId1111orderNumbershipment111.*1.*publisher11publisher0.*0.*categoridcategorysuperId111111bookpromotionpromotion111.*1.*13ISBN（主键）单值简单属性Publisher_id单值简单属性edition单值简单属性price单值简单属性description单值简单属性Publication_date单值简单属性Category_id单值简单属性PublisherPublisher_id（主键）单值简单属性Name多值简单属性Address多值简单属性CategoryCategory_id（主键）单值简单属性Name单值简单属性Super_id单值简单属性CustomerCustomer_id（主键）单值简单属性name单值复合属性email单值简单属性address多值复合属性Phone_number单值简单属性password单值简单属性shipAddressshipAddrId（主键）单值简单属性recipient单值简单属性shipMethod单值简单属性creditCardname单值简单属性Card_number（主键）单值简单属性type单值简单属性Expiration_date单值简单属性14orderOrder_number（主键）单值简单属性mailAddress