数据库实验报告第一章
实验1.1
(1)
CREATE TABLE CUSTOMER
(CID CHAR(8)UNIQUE,CNAME CHAR(20),CITY CHAR(8),
DISCNT INT,
PRIMARY KEY(CID))
CREATE TABLE AGENTS
(AID CHAR(8) UNIQUE,ANAME CHAR(20),CITY CHAR(8),
PERCENTS FLOAT,PRIMARY KEY(AID))
CREATE TABLE PRODUCTS
(PID CHAR(8) UNIQUE, PNAME CHAR(20),PRIMARY KEY (PID))
(2)
CREATE TABLE ORDERS
(ORDNA CHAR(8) UNIQUE,MONTH INT, CID CHAR(8),AID CHAR (8),
PID CHAR(8),QTY INT , DOLLARS FLOAT,PRIMARY KEY (ORDNA),
FOREIGN KEY(CID)REFERENCES CUSTOMER,FOREIGN KEY(AID)
REFERENCES AGENTS, FOREIGN KEY(PID) REFERENCES PRODUCTS)
(3)
ALTER TABLE PRODUCTS ADD CITY CHAR(8)
ALTER TABLE PRODUCTS ADD QUANTITY INT
ALTER TABLE PRODUCTS ADD PRICE FLOAT
(4)
CREATE INDEX XSNO ON CUSTOMER (CID)
CREATE INDEX XSNO ON AGENTS(AID)
CREATE INDEX XSNO ON PRODUCTS(PID)
CREATE INDEX XSNO ON ORDERS(ORDNA)
(5)
DROP INDEX CUSTOMER.XSNO
DROP INDEX AGENTS.XSNO
DROP INDEX PRODUCTS.XSNO
DROP INDEX ORDERS.XSNO
实验1.2
(1)SELECT * FROM COURSES
(2)SELECT SID FROM CHOICES
(3)SELECT CID FROM COURSES WHERE hour<88
(4)SELECT SID FROM CHOICES GROUP BY SID HAVING SUM(SCORE)>400
(5)SELECT COUNT(CID)FROM COURSES
(6)SELECT CID ,COUNT(CID) FROM CHOICES GROUP BY cid
(7)SELECT SID FROM CHOICES WHERE score >60 GROUP BY sid HAVING COUNT (cid )>2
(8)SELECT SID ,COUNT (CID),AVG (SCORE)FROM CHOICES GROUP BY sid
(9)SELECT STUDENT.sid,sname from student ,choice ,COURSES
where student.sid=choice.sid and choice.cid=COURSES .cid
and COURSES .cname='java'
(10)SELECT CHOICES.sid ,CHOICES.score FROM CHOICES ,STUDENTS
WHERE sname='SSSHT'AND CHOICES.sid=STUDENTS.sid
SELECT CID SCORE FROM CHOICES WHERE sid IN (
SELECT STUDENTS.sid FROM STUDENTS WHERE sname='SSSHT')
(11)SELECT C1.CNAME FROM COURSES AS C1,COURSES AS C2
WHERE C1.hour >C2.hour AND C2.cname ='C++'
(12)SELECT SID , SNAME FROM STUDENTS
WHERE sid IN (
SELECT C1.SID FROM CHOICES AS C1, CHOICES AS C2
WHERE C1.score>C2.score AND C1.cid =C2.cid
AND C2.sid=(SELECT sid FROM STUDENTS WHERE sname='ZNKOO')
AND C1.cid=(SELECT cid FROM COURSES WHERE cname='C++') )
(13)SELECT SNAME FROM STUDENTS WHERE grade IN (
SELECT grade FROM STUDENTS WHERE sid IN ('883794999','850955252'))
(14)SELECT SNAME FROM STUDENTS WHERE sid NOT IN
(SELECT sid FROM CHOICES WHERE cid =(SELECT cid FROM
COURSES WHERE cname='JAVA'))
(15)SELECT * FROM COURSES WHERE hour <=ALL (SELECT hour FROM COURSES )
(16)SELECT CHOICES.tid ,CID FROM CHOICES WHERE NOT EXISTS
(SELECT * FROM TEACHERS WHERE TEACHERS.salary>=
(SELECT salary FROM TEACHERS WHERE TEACHERS.tid =CHOICES.tid ))
(17)SELECT SID FROM CHOICES WHERE score=(
SELECT MAX(score )FROM CHOICES WHERE cid =(
SELECT cid FROM COURSES WHERE cname='ERP'))
(18)SELECT CNAME FROM COURSES WHERE cid NOT IN (
SELECT cid FROM CHOICES )
(19)SELECT CNAME FROM COURSES WHERE cid=SOME (
SELECT cid FROM CHOICES WHERE tid =SOME (
SELECT tid FROM COURSES ,CHOICES WHERE cname='UML'AND
COURSES.cid =CHOICES.cid ))
(20)SELECT SNAME FROM STUDENTS WHERE NOT EXISTS(
SELECT * FROM CHOICES AS C1 WHERE NOT EXISTS(
SELECT * FROM CHOICES AS C2 WHERE C2.sid=STUDENTS.sid
AND C2.cid =C1.cid AND C2.tid='200102901') )
(21)SELECT SID FROM CHOICES,COURSES WHERE COURSES.cid =CHOICES.cid
AND COURSES.cname='DATABASE'
UNION
SELECT SID FROM CHOICES,COURSES WHERE COURSES.cid =CHOICES.cid AND COURSES.cname ='UML'
(22)SELECT X.sid FROM CHOICES AS X,CHOICES AS Y
WHERE (X.cid=(SELECT cid FROM COURSES WHERE cname='DATABASE')
AND Y.cid=(SELECT cid FROM COURSES WHERE cname='UML'))
AND X.sid=Y.sid
(23)SELECT X.SID FROM CHOICES AS X,CHOICES AS Y
WHERE (X.cid=(SELECT cid FROM COURSES WHERE cname='DATABASE'))
AND X.sid=Y.sid
AND NOT (Y.cid =(SELECT cid FROM COURSES WHERE cname='UML'))
实验1.3
(1)INSERT INTO STUDENTS (sid,sname)
VALUES('8000022222','WANGLAN')
(2)INSERT INTO TEACHERS
VALUES('200001000','LXL','S4ZRCK@PEW.NET','3024')
(3)UPDATE TEACHERS
SET salary=4000
WHERE tid='200010493'
(4)UPDATE TEACHERS
SET salary=2500
WHERE salary<2500
(5)UPDATE CHOICES
SET tid=
(SELECT tid FROM TEACHERS WHERE tname='RNUPX')
WHERE tid='200016731'
(6)UPDATE STUDENTS
SET grade=2001
WHERE sid='800071780'
(7)DELETE FROM COURSES
WHERE cid NOT IN (
SELECT cid FROM CHOICES GROUP BY cid )
(8)DELETE FROM STUDENTS
WHERE grade<1998
(9)DELETE FROM STUDENTS WHERE sid NOT IN
(SELECT sid FROM CHOICES GROUP BY sid )
(10)DELETE FROM CHOICES WHERE score <60
实验1.4
(1)CREATE VIEW VIEWC AS
SELECT CHOICES.no ,CHOICES.sid ,CHOICES.tid,COURSES.cname ,CHOICES.score FROM CHOICES ,COURSES
WHERE CHOICES.cid =COURSES.cid
(2)CREATE VIEW VIEWS AS
SELECTCHOICES.no ,STUDENTS.sname ,CHOICES.tid,CHOICES.cid ,CHOICES.score FROM CHOICES ,STUDENTS
WHERE CHOICES.sid=STUDENTS.sid
(3)CREATE VIEW S2(SID,SNAME,GRADE)AS
SELECT STUDENTS.sid ,STUDENTS.sname,STUDENTS.grade
FROM STUDENTS
WHERE grade >1998
(4)SELECT * FROM VIEWS WHERE SNAME='UXJOF'
(5)SELECT SID,SCORE FROM VIEWC WHERE CNAME = 'UML'
(6)INSERT INTO s1 VALUES('60000001','LILY','2001')
(7)CREATE VIEW S1 (SID,SNAME,GREADE)AS
SELECT SID ,SNAME,GRADE FROM STUDENTS
WHERE grade>1998
WITH CHECK OPTION
(8)UPDATE VIEWS SET SCORE = SCORE+5 WHERE SNAME='UXJOF'
(9)DROP VIEW VIEWC
DROP VIEW VIEWS
DROP VIEW S1
实验1.6
(1) SELECT CID,HOUR*18 FROM COURSES
(2)select count(*)
from choices,courses
where choices.cid=courses.cid and cname='C++' and score is null
(3)select sid,score
from choices,courses
where choices.cid=courses.cid and cname='C++'
order by score
(4)SELECT DISTINCT
SCORE FROM CHOICES WHERE cid=(
SELECT cid FROM COURSES WHERE cname ='C++')
ORDER BY score
(5)SELECT DISTINCT GRADE FROM STUDENTS GROUP BY grade
(6)SELECT AVG(SCORE),COUNT (*),MAX(SCORE),MIN(SCORE)
FROM CHOICES GROUP BY cid
(7)SELECT GRADE FROM STUDENTS WHERE GRADE>=ALL (
SELECT grade FROM STUDENTS )
(8)SELECT COUNT (*)FROM STUDENTS ,TEACHERS
WHERE TEACHERS .TID=STUDENTS .SID
第二篇:河北工业大学算法分析实验报告
算法设计与分析
实验报告
一、实验目的与要求:
熟悉C/C++语言的集成开发环境;
通过本实验加深对贪心算法、动态规划和回溯算法的理解。
二、实验内容:
掌握贪心算法、动态规划和回溯算法的概念和基本思想,分析并掌握"0-1"背包问题的三种算法,并分析其优缺点。
三、实验题:
1. "0-1"背包问题的贪心算法
2. "0-1"背包问题的动态规划算法
3. "0-1"背包问题的回溯算法
四、实验步骤:
1. 理解算法思想和问题要求;
2. 编程实现题目要求;
3. 上机输入和调试自己所编的程序;
4. 验证分析实验结果;
5. 整理出实验报告。
五、实验程序:
1、“0-1”背包问题的贪心算法源程序
#include
struct goodinfo
{
float p; //物品效益
float w; //物品重量
float X; //物品该放的数量
int flag; //物品编号
};//物品信息结构体
void Insertionsort(goodinfo goods[],int n)
{
int j,i;
for(j=2;j<=n;j++)
{
goods[0]=goods[j];
i=j-1;
while (goods[0].p>goods[i].p)
{
goods[i+1]=goods[i];
i--;
}
goods[i+1]=goods[0];
}
}//按物品效益,重量比值做升序排列
void bag(goodinfo goods[],float M,int n)
{
float cu;
int i,j;
for(i=1;i<=n;i++)
goods[i].X=0;
cu=M; //背包剩余容量
for(i=1;i { if(goods[i].w>cu)//当该物品重量大与剩余容量跳出 break; goods[i].X=1; cu=cu-goods[i].w;//确定背包新的剩余容量 } if(i<=n) goods[i].X=cu/goods[i].w;//该物品所要放的量 /*按物品编号做降序排列*/ for(j=2;j<=n;j++) { goods[0]=goods[j]; i=j-1; while (goods[0].flag { goods[i+1] = goods[i]; i--; } goods[i+1]=goods[0]; } cout<<"最优解为:"< for(i=1;i<=n;i++) { cout<<"第"<
cout< } } void main() { cout<<"|--------运用贪心法解背包问题---------|"< cout<<"|-------------------------------------|"< int j; int n; float M; goodinfo *goods;//定义一个指针 while(j) { cout<<"请输入物品的总数量:"; cin>>n; goods=new struct goodinfo [n+1];// cout<<"请输入背包的最大容量:"; cin>>M; cout< int i; for(i=1;i<=n;i++) { goods[i].flag=i; cout<<"请输入第"<
cin>>goods[i].w; cout<<"请输入第"<
cin>>goods[i].p; goods[i].p=goods[i].p/goods[i].w;//得出物品的效益,重量比 cout< } Insertionsort(goods,n); bag(goods,M,n); cout<<"press <1> to run agian"< cout<<"press <0> to exit"< cin>>j; } } 2、“0-1”背包问题动态规划算法远程序: #include #define MAX 20 int n,c,w[MAX],v[MAX],m[MAX][MAX]={0}; void knapsack() { int i,j; for (i=1; i<=n; i++) for (j=1; j<=c; j++) { m[i][j]=m[i-1][j]; if ( j>=w[i-1] && m[i-1][j-w[i-1]]+v[i-1]> m[i][j] ) m[i][j]=m[i-1][j-w[i-1]]+v[i-1]; } } //显示所取的物品及其重量(其中一个解) //对数组m的最后一列检查来求解 void disp( ) { int i,j; i=n; while ( m[i][c]==m[i-1][c] ) i--; while (i>0) { j=i-1; while (m[i][c]-m[j][c]!=v[i-1] && j>0) j--; printf("%5d%5d\n",w[i-1],v[i-1]); i=j; } } void main( ) { int i,j; printf("输入物品种数:"); scanf("%d",&n); printf("输入每种物品的重量与价值:\n"); for (i=0; i scanf("%d%d",&w[i],&v[i]); printf("输入背包的总重量:\n"); scanf("%d",&c); knapsack(); disp(); printf("最大价值=%d\n",m[n][c]); for (i=0; i<=n; i++) { for (j=0; j<=c; j++) printf("%3d",m[i][j]); printf("\n"); } } 3、"0-1"背包问题的回溯算法源程序: #include using namespace std; void input(int *number,int * weight,int * price){ int i,n; cout<<"包的容量: "; cin>>weight[0]; cout<<"物品数: "; cin>>*number; cout<<"各物品的重量: "; for(i=1;i<=*number;i++){ cin>>weight[i]; } cout<<"各物品的价值: "; for(i=1;i<=*number;i++){ cin>>price[i]; } } void backtrack(int t,int n,int *weight,int *price,int *maxPrice,int *flag,int *nowWeight,int *nowPrice,int *x){ int i; if(t>n){ if(*nowWeight<=weight[0]&&*nowPrice>*maxPrice){ *maxPrice=*nowPrice; flag[0]=0; for(i=1;i<=n;i++){ if(x[i]){ flag[++flag[0]]=i; } } } return; }else{ for(i=0;i<=1;i++){ x[t]=i; *nowWeight+=weight[t]*i; *nowPrice+=price[t]*i; backtrack(t+1,n,weight,price,maxPrice,flag,nowWeight,nowPrice,x); *nowWeight-=weight[t]*i; *nowPrice-=price[t]*i; } } } void output(int *maxPrice,int *flag){ int i; cout< cout<<"物品的最大价值为:"<<*maxPrice< cout<<"选中的物品为:"; for(i=1;i<=flag[0];i++){ cout< } cout< } int main(){ int temp1=0,temp2=-1,temp3=0,temp4=0;//这一行很重要! int *number=&temp1,weight[100],price[100]; int *maxPrice=&temp2,flag[100],*nowWeight=&temp3,*nowPrice=&temp4,x[100]; input(number,weight,price); backtrack(1,*number,weight,price,maxPrice,flag,nowWeight,nowPrice,x); output(maxPrice,flag); return 0; } 六、实验结果: 1、贪心算法实验结果: 2、动态规划法实验结果: 3、回溯算法实验结果: 七、实验分析: 1、“0-1”背包问题的贪心算法 贪心算法(又称贪婪算法)是指,在对问题求解时,总是做出在当前看来是最好的选择。也就是说,不从整体最优上加以考虑,他所做出的仅是在某种意义上的局部最优解。贪心算法不是对所有问题都能得到整体最优解,但对范围相当广泛的许多问题他能产生整体最优解或者是整体最优解的近似解。在本问题中,通过求出每件物品的单位效益来确定放入哪个物品,从而求出解。 本题目第一件物品的单位效益为6,而第二件物品的单位效益为2,第三件物品的单位效益为4,按照升序排列,顺序为:1 3 2,有因为背包容量为2,所以,重量为1的第一件物品可以放入。由于第三件物品的重量为2,所以只能放入一半,背包的容量就满了,不能再放了。 2、“0-1”背包问题的动态规划算法 动态规划过程是:每次决策依赖于当前状态,又随即引起状态的转移。一个决策序列就是在变化的状态中产生出来的,所以,这种多阶段最优化决策解决问题的过程就称为动态规划。基本思想与分治法类似,也是将待求解的问题分解为若干个子问题(阶段),按顺序求解子阶段,前一子问题的解,为后一子问题的求解提供了有用的信息。在求解任一子问题时,列出各种可能的局部解,通过决策保留那些有可能达到最优的局部解,丢弃其他局部解。依次解决各子问题,最后一个子问题就是初始问题的解。 本题目中m[i][j]表示有i个物品,背包容量为j时的最优解,通过knapsack函数用来寻找最优解的值,自底向上地对每一个物品进行运算,都将把其放入与不放入背包两种情况下背包的总价值作=进行比较,然后选择中值较大者作为当前状态下的最优值,最后求出最优值。在通过disp函数用来输出动态规划法中的二维矩阵。 3、“0-1”背包问题的回溯算法 回溯法(探索与回溯法)是一种选优搜索法,按选优条件向前搜索,以达到目标。但当探索到某一步时,发现原先选择并不优或达不到目标,就退回一步重新选择,这种走不通就退回再走的技术为回溯法,而满足回溯条件的某个状态的点称为“回溯点”。 一般步骤为:(1)针对所给问题,定义问题的解空间;(2)确定易于搜索的解空间结构;(3)以深度优先方式搜索解空间,并在搜索过程中用剪枝函数避免无效搜索。 本题目采用的是定长的解空间,从根结点出发,以深度优先方式搜索整个解空间。Backtrack函数用来进行深度搜索,程序用了指针的方法对左右子树进行搜索,当进入左子树的条件不满足时,就对其进行右子树搜索,如果还是不满足,就进行回溯。每次到达叶子结点时都是更新最优值,故最后求出来的解一定是最优解。