B17041622-洪宗濤-數(shù)據(jù)庫實(shí)驗(yàn)報(bào)告

1、精選優(yōu)質(zhì)文檔-傾情為你奉上精選優(yōu)質(zhì)文檔-傾情為你奉上專心-專注-專業(yè)專心-專注-專業(yè)精選優(yōu)質(zhì)文檔-傾情為你奉上專心-專注-專業(yè)實(shí) 驗(yàn) 報(bào) 告（1 8 / 1 9 學(xué)年 第 二 學(xué)期）課程名稱數(shù)據(jù)庫系統(tǒng) 與 設(shè) 計(jì)實(shí)驗(yàn)名稱數(shù)據(jù)庫系統(tǒng)程序設(shè)計(jì)與分析實(shí)驗(yàn)時(shí)間1 9年5，6月24/31,14/21日指導(dǎo)單位計(jì)算機(jī)學(xué)院/軟件學(xué)院指導(dǎo)教師沈蘇彬?qū)W生姓名洪宗濤班級(jí)學(xué)號(hào)B學(xué)院(系)計(jì)算機(jī)學(xué)院專 業(yè)軟件工程（NIIT）實(shí) 驗(yàn) 報(bào) 告實(shí)驗(yàn)名稱數(shù)據(jù)庫系統(tǒng)程序設(shè)計(jì)與分析指導(dǎo)教師沈蘇彬?qū)嶒?yàn)類型上機(jī)實(shí)驗(yàn)學(xué)時(shí)16實(shí)驗(yàn)時(shí)間2019-5-24/31, 6-14/21實(shí)驗(yàn)?zāi)康暮鸵螅?）掌握數(shù)據(jù)庫系統(tǒng)創(chuàng)建和數(shù)據(jù)添加的程序設(shè)計(jì)方法

2、（2）掌握采用SQL編程語言實(shí)現(xiàn)關(guān)系數(shù)據(jù)庫查詢的程序設(shè)計(jì)方法（3）掌握對(duì)于已有數(shù)據(jù)庫進(jìn)行有興趣的數(shù)據(jù)查詢?cè)O(shè)計(jì)方法（4）掌握對(duì)于復(fù)雜數(shù)據(jù)庫查詢的優(yōu)化程序設(shè)計(jì)方法（5）掌握SQL編程性能監(jiān)測和分析方法要求獨(dú)立完成實(shí)驗(yàn)方案的設(shè)計(jì)、數(shù)據(jù)庫的構(gòu)建、數(shù)據(jù)錄入、SQL程序的編制、調(diào)試和運(yùn)行；要求獨(dú)立完成實(shí)驗(yàn)報(bào)告的編寫。 二、實(shí)驗(yàn)環(huán)境(實(shí)驗(yàn)設(shè)備)硬件：微機(jī)軟件：SQL Server 2008三、實(shí)驗(yàn)原理及內(nèi)容實(shí)驗(yàn)1、利用教材習(xí)題 2.3.1給出的關(guān)系模式和習(xí)題2.4.1給出的數(shù)據(jù)，建立包括Product、PC、Laptop、Printer四個(gè)關(guān)系模式的關(guān)系數(shù)據(jù)庫，并且錄入給出的數(shù)據(jù)。實(shí)驗(yàn)2、按照教材習(xí)題6.2

3、.2要求，編制查詢語句，并且利用實(shí)驗(yàn)1已經(jīng)建立的關(guān)系數(shù)據(jù)庫，執(zhí)行查詢語句，得出查詢結(jié)果。在編制查詢語句時(shí)，建議參照習(xí)題2.4.1編制的相關(guān)查詢的關(guān)系代數(shù)表達(dá)式。實(shí)驗(yàn)3、按照教材習(xí)題 6.3.1要求，參照習(xí)題2.4.1編制的相關(guān)查詢的關(guān)系代數(shù)表達(dá)式，利用核心數(shù)據(jù)庫查詢語言（即核心SQL，不包括GROUP BY、SUM、AVG、MIN、MAX、和COUNT語句）編制查詢語句，對(duì)在實(shí)驗(yàn)1中建立的關(guān)系數(shù)據(jù)庫進(jìn)行查詢，并且記錄查詢結(jié)果。實(shí)驗(yàn)4、按照教材習(xí)題 6.4.6要求，利用完全數(shù)據(jù)庫查詢語言（完全SQL）編制查詢語句，對(duì)在實(shí)驗(yàn)1中建立的關(guān)系數(shù)據(jù)庫進(jìn)行查詢，并且記錄查詢結(jié)果。實(shí)驗(yàn)5、基于在實(shí)驗(yàn)1中建立

4、的關(guān)系數(shù)據(jù)庫和錄入的數(shù)據(jù)進(jìn)行數(shù)據(jù)查詢，查找“具有最流行配置的PC型號(hào)和制造商”，查找“具有較高性價(jià)比的Laptop型號(hào)和制造商”，以及至少針對(duì)自己設(shè)立的一項(xiàng)有興趣的題目，進(jìn)行數(shù)據(jù)查詢，得出查詢結(jié)果，并且監(jiān)測數(shù)據(jù)查詢所花費(fèi)的時(shí)間。實(shí)驗(yàn)6、對(duì)實(shí)驗(yàn)5的數(shù)據(jù)查詢的SQL程序進(jìn)行優(yōu)化，重新執(zhí)行以上的數(shù)據(jù)查詢操作，監(jiān)測優(yōu)化后SQL程序所花費(fèi)的時(shí)間，比較優(yōu)化前后的數(shù)據(jù)查詢的性能差異，分析其中的優(yōu)化原理。參考教材數(shù)據(jù)庫系統(tǒng)基礎(chǔ)教程（第三版）中譯本，Jeffery D. Ullman, Jennifer Widon著，岳麗華，金培權(quán)，萬壽紅等譯. 北京：機(jī)械工業(yè)出版社，2011.實(shí)驗(yàn)11.Product、PC、

5、Laptop、Printer四個(gè)關(guān)系模式的關(guān)系數(shù)據(jù)庫的建立CREATE TABLE Product(maker CHAR(10), -制造商model INT PRIMARY KEY, -型號(hào)type CHAR(10) -產(chǎn)品類型);CREATE TABLE PC(model INT PRIMARY KEY, -型號(hào)speed FLOAT, -速度ram INT, -內(nèi)存hd INT, -硬盤容量price INT -價(jià)格);CREATE TABLE Laptop(model INT PRIMARY KEY, -型號(hào)speed FLOAT, -速度ram INT, -內(nèi)存hd INT, -硬盤

6、容量screen FLOAT, -屏幕尺寸price INT -價(jià)格);CREATE TABLE Printer(model INT PRIMARY KEY, -型號(hào)color CHAR(10), -色彩type CHAR(10), -處理類型price INT -價(jià)格);2.數(shù)據(jù)的錄入及錄入數(shù)據(jù)的查看Product表INSERT INTOProduct(maker,model,type)VALUES (A,1001,pc),(A,1002,pc),(A,1003,pc),(A,2004,laptop),(A,2005,laptop),(A,2006,laptop),(B,1004,pc),(

7、B,1005,pc),(B,1006,pc),(B,2007,laptop),(C,1007,pc),(D,1008,pc),(D,1009,pc),(D,1010,pc),(D,3004,printer),(D,3005,printer),(E,1011,pc),(E,1012,pc),(E,1013,pc), (E,2001,laptop), (E,2002,laptop), (E,2003,laptop),(E,3001,printer),(E,3002,printer),(E,3003,printer),(F,2008,laptop),(F,2009,laptop),(G,2010,l

8、aptop),(H,3006,printer),(H,3007,printer);查看Product表SELECT *FROM Product;PC表INSERT INTOPC(model,speed,ram,hd,price)VALUES (1001,2.66,1024,250,2114),(1002,2.10,512,250,995),(1003,1.42,512,80,478),(1004,2.80,1024,250,649),(1005,3.20,512,250,630),(1006,3.20,1024,320,1049),(1007,2.20,1024,200,510),(1008,

9、2.20,2048,250,770),(1009,2.00,1024,250,650),(1010,2.80,2048,300,770),(1011,1.86,2048,160,959),(1012,2.80,1024,160,649),(1013,3.06,512,80,529);查看PC表SELECT *FROM PC;Laptop表INSERT INTOLaptop(model,speed,ram,hd,screen,price)VALUES (2001,2.00,2048,240,20.1,3673),(2002,1.73,1024,80,17.0,949),(2003,1.80,51

10、2,60,15.4,549),(2004,2.00,512,60,13.3,1150),(2005,2.16,1024,120,17.0,2500),(2006,2.00,2048,80,15.4,1700),(2007,1.83,1024,120,13.3,1429),(2008,1.60,1024,100,15.4,900),(2009,1.60,512,80,14.1,680),(2010,2.00,2048,160,15.4,2300);查看Laptop表SELECT *FROM Laptop;Printer表INSERT INTOPrinter(model,color,type,pr

11、ice)VALUES (3001,true,ink-jet,99),(3002,false,laser,239),(3003,true,laser,899),(3004,true,ink-jet,120),(3005,false,laser,120),(3006,true,ink-jet,100),(3007,true,laser,200);查看Printer表SELECT *FROM Printer;實(shí)驗(yàn)2a.查詢硬盤容量至少30G的筆記本電腦制造商及該電腦的速度SELECT maker,speedFROM Product,LaptopWHERE Product.model = Laptop

12、.model AND hd = 30;b.查詢制造商B生產(chǎn)的任意類型的所有產(chǎn)品的型號(hào)和價(jià)格(SELECT Product.model AS model,priceFROM Product,PCWHERE Product.model = PC.model AND maker = B)UNION(SELECT Product.model AS model,priceFROM Product,LaptopWHERE Product.model = Laptop.model AND maker = B)UNION(SELECT Product.model AS model,priceFROM Prod

13、uct,PrinterWHERE Product.model = Printer.model AND maker = B)c.查詢只賣筆記本電腦不賣PC的廠商(SELECT maker FROM ProductWHERE type = Laptop)EXCEPT(SELECT makerFROM ProductWHRE type = PC);d.查詢出現(xiàn)在兩種或兩種以上PC中的硬盤的大小SELECT DISTINCT PC1.ram AS ramFROM PC PC1,PC PC2WHERE PC1.model PC2.model AND PC1.ram = PC2.ram;e. 查詢每對(duì)具有

14、相同速度和RAM的PC的型號(hào)。每對(duì)只能列出一次；例如，若（i，j）已被列出，則（j，i）就不能再被列出SELECT PC1.model AS i,PC2.model AS jFROM PC PC1,PC PC2WHERE PC1.speed = PC2.speed AND PC1.ram = PC2.ram AND PC1.model PC2.model;f.查詢生產(chǎn)至少兩種速度至少為3.0的電腦（PC或筆記本電腦）的廠商SELECT makerFROM (SELECT maker,Product.model AS model FROM Product,PCWHERE Product.mode

15、l = PC.model AND speed = 3.0)UNION(SELECT maker,Product.model AS model FROM Product,LaptopWHERE Product.model = Laptop.model AND speed = 3.0)TempGROUP BY makerHAVING COUNT(model) = 2;實(shí)驗(yàn)3a.找出速度在3.0以上的PC制造商方法1SELECT DISTINCT maker FROM ProductWHERE model IN(SELECT model FROM PC WHERE speed = 3.0);方法2S

16、ELECT DISTINCT maker FROM ProductWHERE EXISTS(SELECT modelFROM PCWHERE PC.speed = 3.0 AND model =Product.model);b.找出價(jià)格最高的打印機(jī)方法1SELECT modelFROM PrinterWHERE price = ALL(SELECT price FROM Printer);方法2SELECT modelFROM PrinterWHERE NOT( price ANY(SELECT price FROM Printer);c.找出速度比任何一臺(tái)PC都慢的筆記本電腦方法1SELEC

17、T model FROM LaptopWHERE speed ALL(SELECT speed FROM PC);方法2SELECT modelFROM LaptopWHERE NOT (speed = ALL(SELECT priceFROM (SELECT priceFROM PC)UNION(SELECT priceFROM Laptop)UNION(SELECT priceFROM Printer)Temp2);方法2SELECT model FROM(SELECT model,priceFROM PC)UNION(SELECT model,priceFROM Laptop)UNION

18、(SELECT model,priceFROM Printer)Temp1WHERE NOT(price ANY(SELECT priceFROM (SELECT priceFROM PC)UNION(SELECT priceFROM Laptop)UNION(SELECT priceFROM Printer)Temp2);e.找出價(jià)格最低的彩色打印機(jī)的制造商方法1SELECT makerFROM ProductWHERE model = (SELECT modelFROM PrinterWHERE color = true AND price ANY(SELECT priceFROM Pri

19、nter);f.找出RAM容量最小而PC中速度最快的制造商方法1SELECT DISTINCT makerFROM ProductWHERE model IN(SELECT modelFROM PCWHERE speed =ALL(SELECT speed FROM PC WHERE ram = ALL(SELECT ramFROM PC);方法2SELECT DISTINCT makerFROM ProductWHERE model IN(SELECT modelFROM PCWHERE NOT(speed ANY(SELECT ramFROM PC);實(shí)驗(yàn)4a.查詢PC的平均速度SELEC

20、T AVG(speed) AS agvSpeedFROM PC;b.查詢價(jià)格高于$1000的筆記本電腦的平均速度SELECT AVG(speed) AS agvSpeedFROM LaptopWHERE price 1000;c.查詢廠商A生產(chǎn)的PC的平均價(jià)格SELECT AVG(price) AS avgPriceFROM Product,PCWHERE Product.model = PC.model AND maker = A;d.查詢廠商D生產(chǎn)的PC和筆記本電腦的平均價(jià)格SELECT AVG(price) AS avgPriceFROM (SELECT priceFROM Produc

21、t,PCWHERE Product.model = PC.model AND maker = D)UNION ALL(SELECT priceFROM Product,LaptopWHERE Product.model = Laptop.model AND maker = D)Tempe.查詢每種不同速度的PC的平均價(jià)格SELECT speed,AVG(price) AS avgPriceFROM PCGROUP BY speed;f.查詢每家廠商生產(chǎn)的筆記本電腦的屏幕尺寸的平均值SELECT maker,AVG(screen) AS avgScreenFROM Product,LaptopW

22、HERE Product.model = Laptop.modelGROUP BY maker;g.查詢至少生產(chǎn)三種不同型號(hào)PC的制造商SELECT makerFROM ProductWHERE type = pcGROUP BY makerHAVING count(model) = 3;h.查詢每個(gè)銷售PC的廠商的PC的最高價(jià)格SELECT maker,max(price) AS maxPriceFROM Product,PCWHERE Product.model = PC.model GROUP BY maker;i.查詢每種高于2.0速度的PC的平均價(jià)格SELECT speed,AVG(

23、price) AS avgPriceFROM PCWHERE speed 2.0GROUP BY speed;j.查詢所有生產(chǎn)打印機(jī)的廠商生產(chǎn)的PC的硬盤容量的平均大小SELECT maker,AVG(hd) AS avgHdFROM Product,PCWHERE Product.model = PC.model AND maker IN (SELECT maker FROM Product WHERE type = printer )GROUP BY maker;實(shí)驗(yàn)5查找“具有最流行配置的PC型號(hào)和制造商”對(duì)最流行配置的PC的個(gè)人定義：最流行配置不考慮價(jià)格，在廠家生產(chǎn)的PC中選出所有型號(hào)

24、中speed值出現(xiàn)的最多的型號(hào)，ram值出現(xiàn)最多的型號(hào)，hd值出現(xiàn)最多的型號(hào)，在這三者的基礎(chǔ)上求交集，當(dāng)然交集的結(jié)果可能為空集，但當(dāng)考慮到實(shí)際以及對(duì)于數(shù)據(jù)量非常的大庫而言，應(yīng)該是不會(huì)出現(xiàn)空集的。SELECT maker,PC.model AS modelFROM Product,PCWHERE Product.model = PC.modelAND speed IN (SELECT speed FROM PC GROUP BY speedHAVING COUNT(model) = ALL(SELECT COUNT(model)FROM PCGROUP BY speed)AND ram IN (

25、SELECT ram FROM PC GROUP BY ramHAVING COUNT(model) = ALL(SELECT COUNT(model)FROM PCGROUP BY ram)AND hd IN (SELECT hd FROM PC GROUP BY hdHAVING COUNT(model) = ALL(SELECT COUNT(model)FROM PCGROUP BY hd);Datalog 描述：基本關(guān)系代數(shù)描述的每一個(gè)表達(dá)式都可以用Datalog表達(dá)，而擴(kuò)展的關(guān)系代數(shù)中的操作，如分組和聚集，則不能用Datalog表達(dá)，在該SQL代碼中涉及擴(kuò)展關(guān)系代數(shù)中的操作，故無法用

26、Datalog表示。查找“具有較高性價(jià)比的Laptop型號(hào)和制造商”對(duì)較高性價(jià)比的Laptop的個(gè)人定義：ram = 512 AND hd 150 AND speed = 2.20 AND price 200 AND speed = 2.80 AND price = 250 AND speed = 3.00 AND price 150 AND speed = 2.20 AND price 200 AND speed = 2.80 AND price = 250 AND speed = 3.00 AND price = 3;Datalog 描述：基本關(guān)系代數(shù)描述的每一個(gè)表達(dá)式都可以用Datalo

27、g表達(dá)，而擴(kuò)展的關(guān)系代數(shù)中的操作，如分組和聚集，則不能用Datalog表達(dá)，在該SQL代碼中涉及擴(kuò)展關(guān)系代數(shù)中的操作，故無法用Datalog表示。實(shí)驗(yàn)6對(duì)實(shí)驗(yàn)5中（1）的優(yōu)化比較優(yōu)化前后的數(shù)據(jù)查詢的性能差異，分析其中的優(yōu)化原理：將FROM子句中的一個(gè)兩個(gè)表改為一個(gè)表結(jié)合子查詢實(shí)現(xiàn)，F(xiàn)ROM之后給出多個(gè)表相當(dāng)于多個(gè)表之間進(jìn)行笛卡兒積操作，非常耗費(fèi)時(shí)間，而且會(huì)產(chǎn)生大量的冗余數(shù)據(jù)，特別是對(duì)于數(shù)據(jù)非常龐大的數(shù)據(jù)表。SELECT maker,modelFROM ProductWHERE model IN(SELECT model FROM PCWHERE speed IN (SELECT speed FROM PC GROUP BY speedHAVING COUNT(model) = ALL(SELECT COUNT(model)FROM PCGROUP BY speed)INTERSECT(SELECT model FROM PCWHERE ram IN(SELECT ram FROM PC GROUP BY ramHAVING COUNT(model) = ALL(SELECT COUN