1、(1)掌握数据库系统创建和数据添加的程序设计方法(2)掌握采用SQL编程语言实现关系数据库查询的程序设计方法(3)掌握对于已有数据库进行有兴趣的数据挖掘设计方法(4)掌握对于复杂数据库查询的优化程序设计方法(5)掌握SQL编程性能监测和分析方法要求独立完成实验方案的设计、数据库的构建、数据录入、SQL程序的编制、调试和运行;要求独立完成实验报告的编写。 二、实验环境(实验设备)硬件:微机软件:SQL Server 2008三、实验原理及内容实验1、利用教材习题 2.3.1给出的关系模式和习题2.4.1给出的数据,建立包括Product、PC、Laptop、Printer四个关系模式的关系数据库
2、,并且录入给出的数据。实验2、按照教材习题6.2.2要求,编制查询语句,并且利用实验1已经建立的关系数据库,执行查询语句,得出查询结果。在编制查询语句时,建议参照习题2.4.1编制的相关查询的关系代数表达式。实验3、按照教材习题 6.3.1要求,参照习题2.4.1编制的相关查询的关系代数表达式,利用核心数据库查询语言(即核心SQL,不包括GROUP BY、SUM、AVG、MIN、MAX、和COUNT语句)编制查询语句,对在实验1中建立的关系数据库进行查询,并且记录查询结果。实验4、按照教材习题 6.4.6要求,利用完全数据库查询语言(完全SQL)编制查询语句,对在实验1中建立的关系数据库进行查
3、询,并且记录查询结果。实验5、基于在实验1中建立的关系数据库和录入的数据进行数据挖掘,查找“具有最流行配置的PC型号和制造商”,查找“具有较高性价比的Laptop型号和制造商”,以及至少针对自己设立的一项有兴趣的题目,进行数据挖掘,得出挖掘结果,并且监测数据挖掘所花费的时间。实验6、对实验5的数据挖掘的SQL程序进行优化,重新执行以上的数据挖掘操作,监测优化后SQL程序所花费的时间,比较优化前后的数据挖掘的性能差异,分析其中的优化原理。参考教材数据库系统基础教程(第三版)中译本,Jeffery D. Ullman, Jennifer Widon著,岳丽华,金培权,万寿红等译. 北京:机械工业出
4、版社,2011.实验一创建表 create table Product( maker CHAR(30), model INT PRIMARY KEY, type CHAR(30),);CREATE TABLE PC( speed FLOAT, ram INT, hd INT, price INT,CREATE TABLE Laptop( screen FLOAT,CREATE TABLE Printer( color CHAR(30),录入数据 insert into PC values(1001,2.66,1024,250,2114);insert into PC values(1002,2
5、.10,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 in
6、to 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 value
7、s(2002,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,14
8、29);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,falselaster,239);insert into Printer values(3003,89
9、9);insert into Printer values(3004,120);insert into Printer values(3005,insert into Printer values(3006,100);insert into Printer values(3007,200);insert into Product values(A,1001,pc,1002,1003,2004,laptop,2005,2006,B,1004,1005,1006,2007,C,1007,D,1008,1009,1010,),3004,printer,3005,E,1011,1012,1013,20
10、01,2002,2003,3001,3002,3003,F,2008,2009,G,2010,H,3006,3007,实验二A)SELECT maker, speedFrom Product, Laptopwhere Laptop.hd =30 AND Laptop.model = Product.modelB)SELECT Product.model, priceFrom Product, PCWhere Product.maker = AND Product.model = PC.model UNION AND Product.model = Laptop.modelFROM Produc
11、t, PrinterWHERE Product.maker = AND Product.model = Printer.modelC)SELECT makerFrom Product PWhere P.type = EXCEPTD)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 MODEL2WHERE p.speed = q.speed AND p.ram = q.ram AND p.model F)SELECT p.m
12、akerFROM ( SELECT E.maker, F.model FROM Product E, PC F WHERE F.speed 3.0 AND E.model = F.model UNION SELECT G.maker, H.model FROM Product G, Laptop H WHERE H.speed 3.0 AND G.model = H.model ) pGROUP BY p.makerHAVING COUNT(p.model) = 2实验三FROM Product,( SELECT model FROM PC WHERE PC.speed 3.0 ) pWHER
13、E Product.model = p.modelFROM Product JOIN PC ON Product.model = PC.modelWHERE speed SELECT p.priceFROM Printer pWHERE p.price = ALL ( SELECT price FROM Printer )SELECT MAX(price) AS priceFROM PrinterSELECT p.modelFROM Laptop pWHERE p.speed = ( SELECT MIN(speed) FROM PC )= ALL( SELECT speedD) SELECT
14、 model, price FROM PC FROM Laptop UNION FROM Printer SELECT priceFROM ProductWHERE model = ( SELECT model FROM Printer WHERE price = ( SELECT MIN(price) AS price FROM Printer ) )FROM Product p, Printer qWHERE p.model = q.model AND q.price SELECT price FROM Printer )FROM Product p, PC qWHERE p.model
15、= q.model AND q.ram SELECT speed ) 实验四SELECT AVG(speed) AS AVGSPEEDFROM PC FROM PC WHERE price 1000 SELECT AVG(price) AS AVGPRICEFROM PCWHERE model IN ( FROM Product WHERE maker = ) SELECT AVG(p.price) AS AVGPRICE) pWHERE p.model IN (SELECT speed, AVG(price) AS AVGPRICEGROUP BY speedSELECT maker, AV
16、G(screen) AS AVGSCREENFROM Product p JOIN Laptop q ON p.model = q.modelGROUP BY makerG)WHERE type = PCHAVING COUNT(model) = 3H)SELECT maker, MAX(price) AS MAXPRICEFROM Product p JOIN PC q ON p.model = q.modelI)Where speed 2.0J)SELECT AVG(hd) AS AVGHDWHERE maker IN ( SELECT maker Where type = Printer四、实验小结(包括问题和解决方法、心得体会、意见与建议等) 通过四次实验,对T-SQL查询有了更深入的理解,对分组、聚集、子查询、连接等有了更好的运用。五、指导教师评语成 绩批阅人日 期
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1