1、課程編號：B080109004數據庫應用程序設計實踐報告姓名杜宏壯 學號20124821班級軟件1206指導教師劉益先開設學期2014-2015第二學期開設時間第5周第8周報告日期2015年4月22日評定成績評定人評定日期東北大學軟件學院1關系數據庫第一部分：1指出你所設計表的各種鍵值，在選擇時不要考慮性能問題。1) 指出每張表是否存在主碼，若存在，請指出具體的主碼，并說明原因。Every book has a unique ISBN, so ISBN is the primary key of table BOOK;Every publisher has a unique publisher

2、 id, so PublisherID is the primary key of table PUBLISHER.2) 指出每張表是否存在備用碼（除了主碼之外的所有候選碼），若存在，請指出所有的備用碼，并說明原因。The alternative key of PUBLISHER is Address, as every publisher has a unique address.3) 指出各表中存在的外碼和完整性約束，并說明原因。The foreign of table BOOK is PublisherID, and it is associated with table PUBLISH

3、ER through the attribute PublisherID.2 列出各表所有列和各列的域（數據類型和格式），并說明理由。TABLEATTRIBUTETYPEBOOKPrimary Author charTitle charISBN charPublisherID charEdition charDate of Publication datePrice doubleBook Description textPUBLISHERPublisherID charName charAddress char第二部分：寫出如下SQL語句：1 用DDL語言中的CREATE TABLE語句創建以

4、上兩張表，并確定指定了表的主碼和備用碼；- -PUBLISHER-create table publisher(publisher_id char(10) not null, name char(20), address char(20), primary key (publisher_id),unique (address);- -BOOK-create table book(isbn char(18),primary_author char(50), title char(50), publisher_id char(10) not null, edition char(10), date

5、_of_publication date, price double,book_description text, primary key (isbn), foreign key (publisher_id) references publisher(publisher_id);2 利用INSERT語句向每張表中以上四條記錄；- -PUBLISHER_DATA-insert into publisher values('01', 'Addison Wesley', 'Unknown');insert into publisher values(&

6、#39;02', 'McGraw Hill', 'Unknown2');insert into publisher values('03', '高等教育出版社', '北京');insert into publisher values('04', '華中科技大學出版社', '湖北');insert into publisher values('05', '科學出版社', '北京2');insert into pub

7、lisher values('06', 'Great Shaw', 'NTU');- -BOOK_DATA-insert into book values('9780136086208', 'Ramez Elmasri,ShamkantNavathe', 'Fundamentals of Database Systems', '01', '6', '2010-4-9', '1540.00', 'Clear explanation

8、s of theory and design,broad coverage of models and real systems, and an up-to-dateintroduction to modern database technologies result in a leadingintroduction to database systems. Intended for computer sciencemajors, Fundamentals of Database Systems, 6/e emphasizesmath models, design issues, relati

9、onal algebra, and relationalcalculus.');insert into book values('9787040195835', '王珊，薩師煊等', '數據庫系統概論', '03', '第四版', '2006-5-1', '30.40', '本書可以作為高等學校計算機專業、信息管理與信息系統等相關專業數據庫課程的教材。也可供從事數據庫系統研究、開發和應用的研究人員和工程技術人員參考。');insert into book va

10、lues('9787560907093', '劉云生', '數據庫系統概論', '04', '第四版', '1997-9-1', '8.80', '本書可作為高等學校計算機各專業或非計算機專業數據庫課程的教材，能適合多種層次（本科或專科）、多種培養目標的人員（如數據庫的一般用戶或程序設計員、數據庫與信息系統的設計開發人員、數據庫管理員等）的學習需要，亦可供計算機應用研究工作者、工程技術人員和其他有關人員參考。');insert into book values(

11、9;9787030321770', '程昌秀', '空間數據庫管理系統概論', '05', '1', '2012-1-1', '38.40', '空間數據庫管理系統概論重點討論了空間數據庫管理系統(SDBMS)領域已經實現的一些關鍵技術(如：空間數據模型、空間索引等)，同時進一步介紹了空間查詢優化、空間并發控制等方面的研究進展，還討論了需要進一步解決的關鍵科學問題和技術問題。');3 寫SQL語句檢索兩個出版人之一（比如說是Addison Wesley和McGraw Hill

12、）所出版書籍的標題和價格，列出你所用到的所有選擇、投影和連接操作，并說明各個操作在查詢中的作用；- 檢索兩個出版人之一（比如說是Addison Wesley和McGraw Hill）所出版書籍的標題和價格：select title,price from book where publisher_id in ( select publisher_id from publisher where name='Addison Wesley' or name='McGraw Hill');- 或者select title,price from book join publ

13、isher on book.publisher_id = publisher.publisher_id where name='Addison Wesley' or name='McGraw Hill'- 或者select title,pricefrom book,publisherwhere book.publisher_id = publisher.publisher_idand name='Addison Wesley' or name='McGraw Hill'4 寫SQL語句，檢索特定書名（比如說是”Fundamenta

14、ls of Database Systems”）的出版人。- 檢索特定書名（比如說是”Fundamentals of Database Systems”）的出版人select name from publisher where publisher_id in ( select publisher_id from book where title='Fundamentals of Database Systems');- 或者select name from book join publisher on book.publisher_id = publisher.publishe

15、r_id where title='Fundamentals of Database Systems'第三部分：1.表的差運算- - PART 3- -create table r(A varchar(255), B varchar(255), C varchar(255);insert into r values('a', 'b', 'c');insert into r values('d', 'a', 'f');insert into r values('c',

16、'b', 'd');create table s(D varchar(255), E varchar(255), F varchar(255);insert into s values('b', 'g', 'a');insert into s values('d', 'a', 'f');給出差運算r-s和s-r的結果；- r-sselect A,B,Cfrom rwhere (A,B,C)not in ( select D,E,F from s);- 或者select

17、 A,B,Cfrom r rwhere not exists ( select * from s s where s.D=r.A and s.E=r.B and s.F=r.C);- s-rselect D,E,Ffrom swhere (D,E,F)not in ( select A,B,C from r);- 或者select D,E,Ffrom s swhere not exists ( select * from r r where r.A=s.D and r.B=s.E and r.C=s.F);2如果將UNION換成EXCEPT，得到的結果將是兩條語句的得出集合的差集合。2SQL1

18、. 列出作者為"Churchill"的所有書籍的名稱和出版年份；select Name, Year from title where CallNumber in (select CallNumber from author where Lname='Churchill');2. 檢索first name為"John" 或"Susan" 的借閱者所借閱所有圖書的標題；select distinct Name from (Title inner join Book on Title.CallNumber=Book.Call

19、Number) where BorrowerMemNo in ( select MemNo from member where Fname='John' or Fname='Susan');3. 列出同時借閱"Iliad" 和"Odyssey"這兩本書的所有借閱者的names和IDs；select MemNo, Fname, MI, Lname from member where MemNo in (select distinct b1.BorrowerMemNo from Book b1 join Book b2 whe

20、re (b1.CallNumber in (select CallNumber from Title where Name='Iliad') and (b2.CallNumber in (select CallNumber from Title where Name='Odyssey') and (b1.BorrowerMemNo=b2.BorrowerMemNo);4. 列出借閱了Collins所寫的所有圖書的讀者的names和IDs，假設一位讀者可以同時借閱一本書的多個復本；select member.MemNo, member.Fname, member.

21、MI, member.Lnamefrom member, book, author a2where book.CallNumber = a2.CallNumberand book.BorrowerMemNo = member.MemNoand a2.Lname = 'Collins'group by member.MemNohaving count(distinct book.CallNumber) = (select count(*) from author a1 where a1.Lname='Collins');5. 查找借閱了last name為&quo

22、t;Tanenbaum"的作者所著作的任一本書籍的所有讀者的電話號碼；select distinct member.PhoneNumber from member join book on book.BorrowerMemNo = member.MemNo where book.CallNumber in (select temp_table.CallNumber from (select title.CallNumber from title join author on title.CallNumber = author.CallNumber where author.Lname

23、='Tanenbaum' order by rand() limit 1 ) as temp_table );6. 查找借閱了三本書以上的讀者，并列出他們的names、IDs以及所借閱圖書的數量，查詢結果按所借閱圖書的數量降序排列；select MemNo,Fname,MI,Lname,count(*) from member join book on member.MemNo = book.BorrowerMemNo group by member.MemNohaving count(*)>3 order by count(*) desc;7. 列出沒有借閱圖書的所有讀

24、者；select MemNo,Fname,MI,Lname from member where member.MemNo not in (select distinct book.BorrowerMemNo from book where BorrowerMemNo != 'NULL');8. 按字母順序列出是Pittsburgh居民（電話號碼以412開頭）并沒有借閱圖書"Pitt Roads"的所有讀者的first names。select m1.FName from member m1 where m1.PhoneNumber like '412

25、%' and m1.MemNo not in (select m2.MemNo from member m2 join book b2 on m2.MemNo = b2.BorrowerMemNo where b2.CallNumber in (select distinct b1.CallNumber from title join book b1 on title.CallNumber = b1.CallNumber where title.Name='Pitt Roads') );3Advanced SQL1運行CREATE TABLE和INSERT語句；2編寫并

26、運行SQL語句，刪除所有表中的記錄；3編寫并運行SQL語句，按照以下方式來修改數據庫：1) 創建上面列出的還未創建的數據庫表，當設定表中各列的類型和長度時，需要考慮給定的用于填充數據的INSERT語句；DROP TABLE IF EXISTS PURCHASE_ORDER4;DROP TABLE IF EXISTS SUPPLIER4;DROP TABLE IF EXISTS READ_BY4;DROP TABLE IF EXISTS ORDERED4;CREATE TABLE PURCHASE_ORDER4 (ponum char(8) NOT NULL, qty int, orderdat

27、e date, duedate date, receiveddate date, PRIMARY KEY (ponum);CREATE TABLE SUPPLIER4(supplier_id char(8) NOT NULL, name char(14), address char(20), PRIMARY KEY (supplier_id);CREATE TABLE READ_BY4(callnumber char(8) NOT NULL, libid char(6) NOT NULL, timesread int, PRIMARY KEY(callnumber, libid),FOREIG

28、N KEY (callnumber) REFERENCES TITLE4(callnumber),FOREIGN KEY (libid) references MEMBER4(libid);CREATE TABLE ORDERED4(callnumber char(8) NOT NULL, ponum char(8) NOT NULL, supplier_id char(8) NOT NULL, PRIMARY KEY (callnumber,ponum,supplier_id), FOREIGN KEY (callnumber) REFERENCES TITLE4(callnumber),

29、FOREIGN KEY (ponum) REFERENCES PURCHASE_ORDER4(ponum), FOREIGN KEY (supplier_id) REFERENCES SUPPLIER4(supplier_id);2) 將表TITLE4中的ISBN和Name字段設置成備用碼，而且它們不能取空值；ALTER TABLE TITLE4 MODIFY COLUMN isbn CHAR(8) NOT NULL;ALTER TABLE TITLE4 ADD UNIQUE (isbn);ALTER TABLE TITLE4 MODIFY COLUMN name CHAR(14) NOT N

30、ULL;ALTER TABLE TITLE4 ADD UNIQUE (name);3) 在所有表中添加指定的外碼約束；ALTER TABLE BOOK4 ADD FOREIGN KEY (borrowerid) REFERENCES MEMBER4(libid);ALTER TABLE BOOK4 ADD FOREIGN KEY (callnumber) REFERENCES TITLE4(callnumber);4) 在表MEMBER4中添加"Join_date" 和 "Gender"兩列；ALTER TABLE MEMBER4 ADD join_da

31、te DATE;ALTER TABLE MEMBER4 ADD gender CHAR(1);5) 指定表READ_BY4中"TimesRead"列不能取負值。ALTER TABLE READ_BY4 MODIFY COLUMN timesread INT UNSIGNED;4根據給定的INSERT語句填充數據庫；5編寫SQL語句實現如下功能：1) 顯示ID以字母"A%B" 或 "A&B"開頭，并且加入圖書館不晚于Nov. 30, 1997的所有讀者的ID, first name和joining date；select lib

32、id,fname,join_date from member4 where (libid like 'A%B%' or libid like 'A&B%') and (DATEDIFF(join_date,'1997-11-30')>0);2) 顯示滿足如下條件所以書籍的name, ISBN和call number，這些書籍由兩個以上的供應商提供，并且訂購的數量大于10，顯示時按書籍的總訂購數量非升序排列；select ,title4.isbn,title4.callnumber from title4wher

33、e title4.callnumber in(select ordered4.callnumberfrom ordered4,purchase_order4where ordered4.ponum = purchase_order4.ponumgroup by ordered4.callnumberhaving count(*) > 2and sum(purchase_order4.qty)>10);3) 顯示所有借閱圖書的數量少于圖書館總藏書5的讀者的IDs和first names，結果按借閱圖書數量非降序進行排列；select member4.libid,member4.fna

34、me from book4 join member4 on book4.borrowerid = member4.libid group by member4.libid having count(*)<=(select sum(qty)*0.05 from purchase_order4 ) order by count(*) asc;4) 按字母順序列出所有至少讀一本書兩次的女讀者的first names和IDs；select distinct member4.libid,member4.fname from member4 join read_by4 on member4.libi

35、d = read_by4.libid where read_by4.timesread>=2 and member4.gender='F' order by member4.fname;5) 列出滿足如下條件的供應商的names和他們各自的訂單平均訂購書籍數量，要求他們的訂單平均訂購書籍數量大于系統中所有訂單的平均訂購書籍數量；select ,sum(po1.qty)/count(*) as average from supplier4 s1,purchase_order4 po1,ordered4 o1 where s1.supplier_id = o1.

36、supplier_id and o1.ponum = po1.ponum group by s1.supplier_id having sum(po1.qty)/count(*) > (select sum(po2.qty)/count(*) from purchase_order4 po2);6) 按字母順序列出滿足如下要求的所有圖書館中男讀者的first names和IDs，要求他們在Oct. 10, 1995之前加入圖書館，并且僅讀了五本或更少的不同的圖書，對于同一本圖書，他們曾沒有讀過多遍。select m1.libid,m1.fname from member4 m1 wher

37、e (datediff(m1.join_date,'1995-10-10')<0) and (m1.gender='M') and (m1.libid in (select distinct rb2.libid from read_by4 rb2 where rb2.libid in (select rb1.libid from read_by4 rb1 group by rb1.libid having count(*)<5) and rb2.timesread = 1) ) order by m1.fname;4ER Models1. 指明實體類

38、型（指定是否為弱實體）；EntityEntity TypePublisherStrong EntityBookTitleStrong EntityPromotionStrong EntityCustomerStrong EntityOrderStrong EntityFulfilledOrderWeak EntityCategoryStrong Entity2. 指明每個實體的屬性及類型（指定是單值或多值、簡單或復合屬性等），將屬性列在表格中，不要直接畫在圖上；EntityAttributesTypesingle-valued/multi-valuedsimple/compositeBookT

39、itleAuthorcharmulti-valuedcompositeTitlecharsingle-valuedsimpleISBNcharsingle-valuedsimplePublisherIdcharsingle-valuedsimpleEditioncharmulti-valuedsimpleDateOfPublicationdatemulti-valuedsimplePrice doublesingle-valuedsimpleBookDescription textsingle-valuedsimpleCategoryIdcharmulti-valuedsimpleOrderO

40、rderNumbercharsingle-valuedsimpleCustomerIdcharsingle-valuedsimpleMailingAddress charsingle-valuedsimpleCreditCard charsingle-valuedsimpleMethodOfShipment charsingle-valuedsimpleShippingDate datesingle-valuedsimpleDateAndTimeOfOrder datetimesingle-valuedsimplePriceOfTheOrderdoublesingle-valuedcompos

41、iteFulfilledOrderTitleShippedQtyintsingle-valuedsimpleTrackingNumbercharsingle-valuedsimpleTitleShippedcharmulti-valuedsimpleDateOfExpectedDeliverydatesingle-valuedsimpleDateOfShipmentdatesingle-valuedsimpleOrderNumbercharsingle-valuedsimplePublisherAddresscharsingle-valuedcompositePublisherNamechar

42、single-valuedsimplePublisherIdcharsingle-valuedsimpleCustomerCustomerIdcharsingle-valuedsimpleEmailAddresscharsingle-valuedsimplePhoneNumbercharmulti-valuedsimpleMailingAddresscharmulti-valuedsimpleCreditCardExpirationDatedatesingle-valuedsimpleCreditCardNumbercharsingle-valuedsimplePromotionPromoti

43、onIdcharsingle-valuedsimpleBooksBeingPromotedCharsingle-valuedcompositePercentageDiscountPointsdoublesingle-valuedsimpleDurationOfPromotionDatesingle-valuedsimpleCategoryCategoryIdcharsingle-valuedsimpleSubCategoryIdcharmulti-valuedsimpleCategoryNamecharsingle-valuedsimple3. 指明聯系的類型（指定聯系的基數以及聯系是強制還是

44、可選聯系）。A publisher is associated with several (including 0) BookTitles via publishing books; (可選聯系)A BookTitle is associated with one Customer via publishing books; (強制聯系)A Promotion is associated with at least one BookTitles via promoting books; (強制聯系)A BookTitle is associated with several (includin

45、g 0) Promotions via promoting books; (可選聯系)A BookTitle is associated with several (including 0) Orders via ordering books; (可選聯系)A Order is associated with at least one BookTitles via ordering books; (強制聯系)A BookTitle is associated with at least one Categorys via belonging to; (強制聯系)A Category is as

46、sociated with several (including 0) BookTitles via belonging to; (可選聯系)A FulfilledOrder is associated with one Order via finishing order; (強制聯系)A Order is associated with one FulfilledOrder via finishing order; (可選聯系)An Order is associated with one Customer via placing an order; (強制聯系)A customer is

47、associated with several Orders via placing an order; (可選聯系)A Category is associated with several (including 0) Categorys via (are parent categories of); (可選聯系)A Category is associated with one Category via (is a sub category of); (可選聯系)5. Normalization1. 給出主碼；PK: TrackingNum2. 寫出所有的函數依賴；EmpIdEmpName

48、TrackingNumShipToAddrTrackingNumShippedDateTrackingNumEmpIdTrackingNumOrderNo3. 寫出所有的修改異常，并舉例說明；只修改EmpName而沒有修改EmpId時，即會違背EmpIdEmpName函數依賴關系4. 解釋該關系屬于第幾范式？屬于第一范式，因為所有字段值都是不可分解的原子值.5. 利用關系模式規范化的方法，將該關系規范化為3NF。即，如果該關系是非規范化關系，則將其規范化為1NF，然后將其在1NF的基礎上轉換為2NF，在在2NF的基礎上轉換為3NF。3NF如下：SHIPMENTEmpIDOrderNoShipT

49、oAddrShippedDateTrackingNum12342234615 Forbes Ave, Pittsburgh, PA 1514712/21/991243567821342244615 Forbes Ave, Pittsburgh, PA 1514712/25/9921345678SHIPMENT=(EmpId, OrderNo, ShipToAddr, ShippedDate, TrackingNum)函數依賴：TrackingNumShipToAddrTrackingNumShippedDateTrackingNumEmpIdTrackingNumOrderNoEMPEmpID

50、EmpName1234Joe2134JonesEMP=(EmpId, EmpName)函數依賴EmpIdEmpName符合第三范式的原因：每一列數據都和主鍵直接相關，而不能間接相關，所以是3NF。6. ER Mapping利用映射算法，將1.4節中你所設計的E-R模型轉換為關系數據模型PUBLISHER7 (Address, PublisherName, PublisherId),PK (PublisherId),BOOKTITLE7 (ISBN, PublisherId, CategoryId, Edition, DateOfPublication, BookDescription, Tit

51、le, Price, Fname, Lname),PK (ISBN),FK (PublisherId) -> PUBLISHER7 (PublisherId),FK (CategoryId) -> CATEGORY7 (CategoryId),CATEGORY7 (CategoryId, CategoryName, SubCategoryId),PK (CategoryId),FK (SubCategoryId) -> CATEGORY7 (CategoryId),PROMOTION7 (PromotionId, BooksBeingPromoted, PercentageD

52、iscountPoints, DurationOfPromotion),PK (PromotionId),FK (BooksBeingPromoted) -> BOOKTITLE7 (ISBN),FULFILLEDORDER7 (OrderNumber, DateOfShipment, DateOfExpectedDelivery, TitleShipped, TrackingNumber, TitleShippedQty),PK (OrderNumber),AK (TrackingNumber),FK (OrderNumber) -> ORDER7 (OrderNumber),C

53、USTOMER7 (CustomerId, EmailAddress, PhoneNumber, CreditCardExpirationDate, MailingAddress, CreditCardNumber),PK (CustomerId),AK (EmailAddress),ORDER7 (OrderNumber, CustomerId, MailingAddress, CreditCard, MethodOfShipment, ShippingDate, DateAndTimeOfOrder, ISBN, Tax, QtyPurchase, ShippingCost),PK (Or

54、derNumber),FK (CustomerId) -> CUSTOMER7 (CustomerId),FK (ISBN) -> BOOKTITLE7 (ISBN),針對你最終得到的關系模式，給出相應的CREATE TABLE語句DROP DATABASE IF EXISTS Library7;CREATE DATABASE Library7;USE Library7;SET FOREIGN_KEY_CHECKS=0;DROP TABLE IF EXISTS PUBLISHER7;DROP TABLE IF EXISTS BOOKTITLE7;DROP TABLE IF EXIS

55、TS PROMOTION7;DROP TABLE IF EXISTS FULFILLEDORDER7;DROP TABLE IF EXISTS CUSTOMER7;DROP TABLE IF EXISTS ORDER7;- PUBLISHER7CREATE TABLE PUBLISHER7(Address char(20), PublisherName char(10), PublisherId char(10) NOT NULL, PRIMARY KEY(PublisherId);- BOOKTITLE7CREATE TABLE BOOKTITLE7(ISBN char(20) NOT NULL, PublisherId char(10) NOT NULL, CategoryId char(10), Edition char(10), DateOfPublication date, BookDescription text, Title char(20), Price double, Fname char(10), Lname char(10), PRIMARY KEY(ISBN), FOREIGN KEY(PublisherId) REFERENCES PUBLISHER7(PublisherId), FOR