《数据库的多表操作》由会员分享，可在线阅读，更多相关《数据库的多表操作（7页珍藏版）》请在金锄头文库上搜索。
1、实验二数据库定义课程名称：数据库原理实验类型：综合型实验名称数据库定义学时2 学时实验目的：（1）使学生掌握E_R 图的设计方法，并能将E_R 图转换成关系模式；（2）熟悉 SQL DDL 和 DML 语言，能够完成基本的表结构的定义、修改；（3）掌握数据库完整性约束定义实验原理：1.将 E_R图转换为关系的方法：一个实体转换为一个关系模式，实体的属性就是关系的属性，实体的码就是关系的码一个 1:1 联系可以转换为一个独立的关系模式，也可以与任意对应的关系模式合并一个 1:n 联系可以转换为一个独立的关系模式，也可以与n 端对应的关系模式合并一个 m:n 联系转换为一个关系模式三个或三个以上实
2、体间的一个多元联系可以转换为一个关实验内容：假设某公司的业务规则如下：（1）公司下设几个部门，如技术部、财务部、市场部等。（2）每个部门承担多个工程项目，每个工程项目属于一个部门。（3）每个部门有多名职工，每一名职工只能属于一个部门。（4）一个部门可能参与多个工程项目，且每个工程项目有多名职工参与施工，一个职工可以参与多个项目。根据职工在工程项目中完成的情况发放酬金。（5）工程项目有工程号、工程名两个属性；部门有部门号、部门名称、办公电话和办公地点等属性；职工有职工号、姓名、性别属性，出生日期；问题：1根据上述规则设计E-R 模型;公司部门项目包括包括部门号部门名称工程号工程名办公地点办公电话
3、员工职工号姓名性别出生年龄承担含有工资名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 7 页 -2将 E-R 模型转换成关系数据模型；部门（部门号，部门名称，办公地点，办公电话）项目（工程号，工程名，部门号）员工（职工号，姓名，性别，出生年龄，部门号）承担（项目号，职工号，工资）3利用 SQL 语句定义上述关系，要求：（1）为每个关系定义主码和外码；（2）职工姓名、部门名称不能取空值，并且部门名称是唯一的；（3）职工的性别只能取“男”和“女”，默认值为“男”；（4）部门号具有类似“B01”的形式，即每个部门号的首字符为“B”，其余两个字符为数字。(5)酬金不低于2000 元。4修
4、改部门号的约束，改为“B001”的形式，每个部门号的首字符为“B”，其余三个字符为数字。create database company;create table a(ano char(6)check(ano betweenB001andB999)primary key,aname varchar(8)not null,age smallint,aphone char(6),aaddress char(10)create table b(bno char(6)primary key,bname varchar(20),ano char(6),foreign key(ano)references
5、a on delete cascade)create table employees(employeeID char(6)primary key,employeename char(6)not null,sex char(2)check(sex=男 or sex=女)default(男),brithday char(10),ano char(6),foreign key(ano)references a on delete cascade)create table cheng(employeeID char(6),bno char(6),salary int check(salary=2000
6、),primary key(bno,employeeID),foreign key(bno)名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 7 页 -references b on delete cascade,foreign key(employeeID)references employees on delete no action)5在原有关系的基础上，增加一个新列，用于记录每名职工参加项目的起始时间。alter table cheng add start datetime 实验总结通过本次试验使我掌握E_R 图的设计方法，并能将E_R 图转换成关系模式；熟悉SQL DDL
7、 和 DML 语言，能够完成基本的表结构的定义、修改；掌握数据库完整性约束定义实验三数据更新课程名称：数据库原理实验类型：综合型实验名称数据更新学时2 学时一实验目的和要求：掌握用 SQL语句实现数据的插入、修改和删除。二实验内容：建立一个商店的数据库store，记录顾客及其购物情况，由下面三个表组成：商品（商品号，商品名，单价，商品类别，供应商）；顾客（顾客号，姓名，住址）；购买（顾客号，商品号，购买数量）；三试用 SQL 语言完成下列功能：1建表，在定义中要求声明：（1）每个表的主外码；（2）顾客的姓名和商品名不能为空值；（3）单价必须大于 0；（4）购买数量必须在0 到 20 之间；2往
8、表中插入数据：部门（M01，佳洁士，8.00，牙膏，宝洁；M02，高露洁，6.50，牙膏，高露洁；M03，洁诺，5.00，牙膏，联合利华；M04，舒肤佳，3.00，香皂，宝洁；M05，夏士莲，5.00，香皂，联合利华；M06，雕牌，2.50，洗衣粉，纳爱斯 M07，中华，3.50，牙膏，联合利华；M08，汰渍，3.00，洗衣粉，宝洁；M09，碧浪，4.00，洗衣粉，宝洁；）名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 7 页 -顾客（C01，Dennis，海淀；C02，John，朝阳；C03，Tom，东城；C04，Jenny，东城；C05，Rick，西城；）购买(C01，M01
9、，3；C01，M05，2；C01，M08，2；C02，M02，5；C02，M06，4；C03，M01，1；C03，M05，1；C03，M06，3；C03，M08，1；C04，M03，7；C04，M04，3；C05，M06，2；C05，M07，8；)注：商品表数据（9）,顾客表数据（5）,购买表数据（5）create database store-建库create table goods-建商品表(goodno char(10)primary key,goodname varchar(10)not null,price varchar(10),classify varchar(30),suppl
10、y varchar(20)create table customers-建顾客表(customerID varchar(6)primary key,customername varchar(10)not null,address varchar(20)create table buys-建购买表(customerID varchar(6),goodno char(10),number char(20)foreign key(customerID)references customers on delete cascade,foreign key(goodno)references goods
11、on delete no action);名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 7 页 -alter table goods alter column price varchar(10);insert into goods values(m01,佳洁士,8.00,牙膏,宝洁);insert into goods values(m02,高露洁,6.50,牙膏,高露洁);insert into goods values(m03,洁诺,5.00,牙膏,联合利华);insert into goods values(m04,舒肤佳,3.00,香皂,宝洁);insert into
12、goods values(m05,夏士莲,5.00,香皂,联合利华);insert into goods values(m06,雕牌,2.50,洗衣粉,纳爱斯);insert into goods values(m07,中华,3.50,牙膏,联合利华);insert into goods values(m08,汰渍,3.00,洗衣粉,宝洁);insert into goods values(m09,碧浪,4.00,洗衣粉,宝洁);insert into customers values(c01,Dennis,海淀);insert into customers values(c02,John,朝
13、阳);insert into customers values(c03,Tom,东城);insert into customers values(c04,Jenny,东城);insert into customers values(c05,Rick,西城);insert into buys values(c01,m01,3);insert into buys values(c01,m08,2);insert into buys values(c02,m06,4);insert into buys values(c03,m05,1);insert into buys values(c03,m01
14、,3);insert into buys values(c01,m05,2);insert into buys values(c02,m02,5);insert into buys values(c03,m08,1);insert into buys values(c03,m06,3);insert into buys values(c04,m03,7);insert into buys values(c04,m04,3);insert into buys values(c05,m07,8);insert into buys values(c05,m06,2);2.完成下列查询：(1)求购买了
15、供应商 宝洁产品的所有顾客；select customerID from customers,goods where supply=宝洁 group by customerID 名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 7 页 -(2).求购买的商品包括了顾客Dennis所购买商品的顾客（姓名）；select customername from customers where customerID in(select customerID from buys where buys.goodno in(select buys.goodno from customers,buy
16、s where customername=Dennis and customers.customerID=buys.customerID)(3).求牙膏卖出数量最多的供应商。select top 1 supply 供应商,sum(number)牙膏数量from buys,goods where buys.goodno in(select goods.goodno from goods where classify=牙膏)and buys.goodno=goods.goodno group by supply order by sum(number)desc 3.将所有的牙膏商品单价增加10%。update goods set price=price*1.1 where classify=牙膏 名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页，共 7 页 -4.删除从未被购买的商品记录。delete from goods where goodno not in(select goodno from buys)实验总结通过本次试验掌握用SQL语句实现数据的插入、修改和删除名师资料总
