数据库上机实验内容及要求(第二部分)
1.建立工厂管理数据库
工厂(包括厂名和厂长名)需要建立一个管理数据库存储以下信息:
(1)一个厂内有多个车间,每个车间有车间号、车间主任姓名、地址和联系电话;
(2)一个车间有多个工人,每个工人有职工号、姓名、年龄、性别和工种;
(3)一个车间生产多种产品,产品有产品号和价格;每种产品只能由一个车间生产;
(4)一个车间制造多种零件,一种零件也可能为多个车间制造。
零件有零件号、重量和价格;
(5)一种产品可由多种零件组成,一种零件也可以装配出多种产品;
(6)产品和零件均存入仓库;
(7)厂内有多个仓库,仓库有仓库号、仓库主任姓名和电话。
根据以上需求分析结果,按照下述要求,设计并建立工厂管理数据库。
◆分析实体及联系,设计E-R图。
◆将E-R图转换成关系模式,并规范化到3NF。
◆在Microsoft SQL Server2000中基于“企业管理器”建立数据库及相关对象(主
码,外码,索引,约束等)。
◆测试数据入库
2.基于“查询分析器”,完成并保存下述题目的SQL脚本
(1)建立“工种”是“钳工”的所有职工详细信息的视图;
create view View_工人_钳工
as
select*
from职工表
where工种='钳工'
with check option
(2)建立“车间号”是“CJ01”的钳工详细信息的视图;
create view View_钳工_CJ01
as
select*
from View_工人_钳工
where车间号='CJ01'
(3)建立使用了“零件号”是“LJ0002”的所有产品详细信息的视图;
create view View_产品_零件LJ0002
as
select产品表.产品号,价格,车间号,仓库号
from装配表,产品表
where装配表.产品号=产品表.产品号and零件号='LJ0002'(4)对零件表按照“零件号”建立唯一索引;
create unique index Index_U_零件号on零件表(零件号)
(5)对职工表按照“性别”建立聚簇索引;
create clustered index Index_C_性别on职工表(性别)
(6)查询使用了“零件号”是“LJ0002”的产品的生产车间号;
select车间号
from装配表,产品表
where装配表.产品号=产品表.产品号and零件号='LJ0002'
(7)查询“职工号”是“ZG0001”的职工所在车间的“车间主任姓名”和“联系电话”;
select车间主任姓名,联系电话
from职工表,车间表
where职工表.车间号=车间表.车间号and职工号='ZG0001'
(8)查询使用了“零件号”是“LJ0002”的所有产品的生产车间的“车间主任姓名”
和“联系电话”;(连接查询、嵌套查询实现)
select车间主任姓名,联系电话
from产品表,装配表,车间表
where产品表.产品号=装配表.产品号and
产品表.车间号=车间表.车间号and
装配表.零件号='LJ0002'
select车间主任姓名,联系电话
from车间表
where车间号in
(select车间号
from产品表
where产品号in
(select产品号
from装配表
where零件号='LJ0002')
)
(9)查询使用了“零件号”是“LJ0002”的所有产品的“产品号”和“零件数量”,且查询结果按照“零件数量”降序排列;
select产品号,零件数量
from装配表
where零件号='LJ0002'
order by零件数量desc
(10)查询使用了“零件号”是“LJ0002”的产品数;
select count(*)as产品数
from装配表
where装配表.零件号='LJ0002'
(11)查询“LJ0002”号零件装配产品的使用总量;
select sum(零件数量)as使用总数
from装配表
where零件号='LJ0002'
(12)查询使用了3种以上零件的产品号;
select产品号
from装配表
group by产品号
having count(*)>=3
【注意】:下机时保存数据库文件(.mdf和.ldf)及SQL脚本文件到U盘。