数据库综合实验任a务书.doc

《数据库技术》实 验 报 告数据库 图 “人员-销售”E-R图 试根据E-R所示的关系图，在SQLServer数据库系统中建立与之相应的数据表。 为建立的数据表中添加必要的测试数据。 创建工人表 worker create table worker (wno char(9) primary key, wname nchar(20) unique, wsex NCHAR(2), wbirth SMALLINT, wgrade CHAR(20), ) insert into worker values(001,周楚,男,1994,3600) insert into worker values(002,洪运志,男,1995,3950) insert into worker values(003,李珍,女,1994,4200) insert into worker values(004,杜姗,女,1994,4000) insert into worker values(005,范江,男,1994,5000) insert into worker values(006,王明,男,1993,3000) 创建商店表Shop create table shop( sno char(9) primary key, sname Nchar(20) , splace nchar(20), ) insert into shop values(1,晨光,武汉) insert into shop values(2,淘宝,上海) insert into shop values(3,京东,北京) 创建聘用表 employ create table employ (wno char(9)primary key, sno char(9), wtime smallint, wage smallint, foreign key(sno)references shop(sno) ) insert into employ values(001,1,2016,2000) insert into employ values(002,2,2017,3000) insert into employ values(003,3,2017,3500) insert into employ values(004,2,2018,3000) insert into employ values(005,3,2017,4000) insert into employ values(006,1,2016,1800) 创建商品表 Goods create table goods (gno char(9)primary key, gname nchar(20) unique, gprice smallint, scale int) insert into goods values(1001,钢笔,5,360) insert into goods values(1002,圆珠笔,1,3500) insert into goods values(1003,签字笔,2,9000) insert into goods values(1004,毛笔,5,90) 创建商品销售表 Gsale create table gsale (gno char(9), sno char(9), gsale float, primary key(gno,sno), foreign key(gno)references goods(gno), foreign key (sno)references shop(sno), ) insert into gsale values(1001,1,500) insert into gsale values(1002,1,1000) insert into gsale values(1003,1,5000) insert into gsale values(1004,1,100) insert into gsale values(1001,2,600) insert into gsale values(1002,2,1200) insert into gsale values(1003,2,6000) insert into gsale values(1004,2,150) insert into gsale values(1001,3,700) insert into gsale values(1002,3,1300) insert into gsale values(1003,3,7000) insert into gsale values(1004,3,200) 以数