南邮数据库基础教程实验报告

1、实 验 报 告（1 5 / 1 6 学年 第 二 学期）课程名称数据库系统 与 设 计实验名称数据库系统程序设计与分析实验时间1 6年6月3/17/22/24日指导单位指导教师学生姓名班级学号学院(系)计算机学院专 业实 验 报 告实验名称数据库系统程序设计与分析指导教师沈苏彬、潘子辉、肖鹏实验类型上机实验学时16实验时间2016/06/3/17/22/24一、 实验目的和要求（1）掌握数据库系统创建和数据添加的程序设计方法（2）掌握采用SQL编程语言实现关系数据库查询的程序设计方法（3）掌握对于已有数据库进行有兴趣的数据挖掘设计方法（4）掌握对于复杂数据库查询的优化程序设计方法（5）掌握SQ

2、L编程性能监测和分析方法要求独立完成实验方案的设计、数据库的构建、数据录入、SQL程序的编制、调试和运行；要求独立完成实验报告的编写。 二、实验环境(实验设备)硬件：微机软件：SQL Server 2008三、实验原理及内容实验1、利用教材习题 2.3.1给出的关系模式和习题2.4.1给出的数据，建立包括Product、PC、Laptop、Printer四个关系模式的关系数据库，并且录入给出的数据。实验2、按照教材习题6.2.2要求，编制查询语句，并且利用实验1已经建立的关系数据库，执行查询语句，得出查询结果。在编制查询语句时，建议参照习题2.4.1编制的相关查询的关系代数表达式。实验3、按照

3、教材习题 6.3.1要求，参照习题2.4.1编制的相关查询的关系代数表达式，利用核心数据库查询语言（即核心SQL，不包括GROUP BY、SUM、AVG、MIN、MAX、和COUNT语句）编制查询语句，对在实验1中建立的关系数据库进行查询，并且记录查询结果。实验4、按照教材习题 6.4.6要求，利用完全数据库查询语言（完全SQL）编制查询语句，对在实验1中建立的关系数据库进行查询，并且记录查询结果。实验5、基于在实验1中建立的关系数据库和录入的数据进行数据挖掘，查找“具有最流行配置的PC型号和制造商”，查找“具有较高性价比的Laptop型号和制造商”，以及至少针对自己设立的一项有兴趣的题目，进

4、行数据挖掘，得出挖掘结果，并且监测数据挖掘所花费的时间。实验6、对实验5的数据挖掘的SQL程序进行优化，重新执行以上的数据挖掘操作，监测优化后SQL程序所花费的时间，比较优化前后的数据挖掘的性能差异，分析其中的优化原理。参考教材数据库系统基础教程（第三版）中译本，Jeffery D. Ullman, Jennifer Widon著，岳丽华，金培权，万寿红等译. 北京：机械工业出版社，2011.实 验 报 告实验一创建表create table Product(makerCHAR(30),modelINTPRIMARY KEY,typeCHAR(30),);CREATE TABLE PC(mod

5、elINTPRIMARY KEY,speedFLOAT,ramINT,hdINT,priceINT,);CREATE TABLE Laptop(modelINT PRIMARY KEY,speedFLOAT,ramINT,hdINT,screenFLOAT,priceINT,);CREATE TABLE Printer(modelINTPRIMARY KEY,colorCHAR(30),typeCHAR(30),priceINT,);录入数据insert into PC values(1001,2.66,1024,250,2114);insert into PC values(1002,2.1

6、0,512,250,995);insert into PC values(1003,1.42,512,80,478);insert into PC values(1004,2.80,1024,250,649);insert into PC values(1005,3.20,512,250,630);insert into PC values(1006,3.20,1024,320,1049);insert into PC values(1007,2.20,1024,200,510);insert into PC values(1008,2.20,2048,250,770);insert into

7、 PC values(1009,2.00,1024,250,650);insert into PC values(1010,2.80,2048,300,770);insert into PC values(1011,1.86,2048,160,959);insert into PC values(1012,2.80,1024,160,649);insert into PC values(1013,3.06,512,80,529);insert into Laptop values(2001,2.00,2048,240,20.1,3673);insert into Laptop values(2

8、002,1.73,1024,80,17.0,949);insert into Laptop values(2003,1.80,512,60,15.4,549);insert into Laptop values(2004,2.00,512,60,13.3,1150);insert into Laptop values(2005,2.16,1024,120,17.0,2500);insert into Laptop values(2006,2.00,2048,80,15.4,1700);insert into Laptop values(2007,1.83,1024,120,13.3,1429)

9、;insert into Laptop values(2008,1.60,1024,100,15.4,900);insert into Laptop values(2009,1.60,512,80,14.1,680);insert into Laptop values(2010,2.00,2048,160,15.4,2300);insert into Printer values(3001,true,ink_jet,99);insert into Printer values(3002,false,laster,239);insert into Printer values(3003,true

10、,laster,899);insert into Printer values(3004,true,ink_jet,120);insert into Printer values(3005,false,laster,120);insert into Printer values(3006,true,ink_jet,100);insert into Printer values(3007,true,laster,200);insert into Product values(A,1001,pc);insert into Product values(A,1002,pc);insert into

11、Product values(A,1003,pc);insert into Product values(A,2004,laptop);insert into Product values(A,2005,laptop);insert into Product values(A,2006,laptop);insert into Product values(B,1004,pc);insert into Product values(B,1005,pc);insert into Product values(B,1006,pc);insert into Product values(B,2007,

12、laptop);insert into Product values(C,1007,pc);insert into Product values(D,1008,pc);insert into Product values(D,1009,pc);insert into Product values(D,1010,pc)insert into Product values(D,3004,printer);insert into Product values(D,3005,printer);insert into Product values(E,1011,pc);insert into Produ

13、ct values(E,1012,pc);insert into Product values(E,1013,pc);insert into Product values(E,2001,laptop);insert into Product values(E,2002,laptop);insert into Product values(E,2003,laptop);insert into Product values(E,3001,printer);insert into Product values(E,3002,printer);insert into Product values(E,

14、3003,printer);insert into Product values(F,2008,laptop);insert into Product values(F,2009,laptop);insert into Product values(G,2010,laptop)insert into Product values(H,3006,printer);insert into Product values(H,3007,printer);实验二A）SELECT maker, speedFrom Product, Laptopwhere Laptop.hd =30 AND Laptop.

15、model = Product.modelB）SELECT Product.model, priceFrom Product, PCWhere Product.maker = B AND Product.model = PC.model UNIONSELECT Product.model, priceFrom Product, LaptopWhere Product.maker = B AND Product.model = Laptop.modelUNIONSELECT Product.model, priceFROM Product, PrinterWHERE Product.maker

16、= B AND Product.model = Printer.modelC）SELECT makerFrom Product PWhere P.type = laptopEXCEPTSELECT makerFrom Product PWhere P.type = pcD）SELECT DISTINCT p.hd FROM PC p, PC qWHERE q.hd = p.hd AND p.model q.modelE）SELECT p.model AS MODEL1, q.model AS MODEL2FROM PC p, PC qWHERE p.speed = q.speed AND p.

17、ram = q.ram AND p.model q.modelF）SELECT p.makerFROM (SELECT E.maker, F.modelFROM Product E, PC FWHERE F.speed 3.0 AND E.model = F.modelUNIONSELECT G.maker, H.modelFROM Product G, Laptop HWHERE H.speed 3.0 AND G.model = H.model ) pGROUP BY p.makerHAVING COUNT(p.model) = 2实验三A）SELECT makerFROM Product

18、,(SELECT modelFROM PCWHERE PC.speed 3.0) pWHERE Product.model = p.modelSELECT makerFROM Product JOIN PC ON Product.model = PC.modelWHERE speed 3.0B）SELECT p.priceFROM Printer pWHERE p.price = ALL (SELECT price FROM Printer )SELECT MAX(price) AS priceFROM PrinterC）SELECT p.modelFROM Laptop pWHERE p.s

19、peed = (SELECT MIN(speed)FROM PC)SELECT p.modelFROM Laptop pWHERE p.speed = ALL(SELECT priceFROM PCUNIONSELECT priceFROM LaptopUNION SELECT priceFROM Printer)E）SELECT makerFROM ProductWHERE model = (SELECT modelFROM PrinterWHERE price = (SELECT MIN(price) AS priceFROM Printer)SELECT p.makerFROM Prod

20、uct p, Printer qWHERE p.model = q.model AND q.price = ALL(SELECT priceFROM Printer)F）SELECT makerFROM Product p, PC qWHERE p.model = q.model AND q.ram = ALL(SELECT speedFROM PC)实验四A）SELECT AVG(speed) AS AVGSPEEDFROM PCB）SELECT AVG(speed) AS AVGSPEEDFROM PCWHERE price 1000C）SELECT AVG(price) AS AVGPRICEFROM PCWHERE model IN (SELECT modelFROM ProductWHERE maker = A)D）SELECT AVG(p.price) AS AVGPRICEFROM (SELECT model, priceFROM PCUNIONSELECT model, priceFROM Laptop) pWHERE p.model IN