数据库设计题.doc

完整word版)数据库设计题
数据库设计题（3小题，每题10分，共30分）
题目要求：根据材料，要求画出对应的E-R图，并将E-R图转换为对应的关系模式，最后利用SQL语言，根据关系模式建表。（注意：实体、属性、联系）

1. 设某商场销售数据库中的信息有：员工号、员工名、工资、销售组名、销售负责人、商品号、商品名、单价、销售日期、销售量、供应者号、供应者名、供应者地址。假定：一个员工仅在一个销售组；一个销售组可以销售多种商品，一种商品只能由一个组销售；一种商品每天有一个销售量；一个供应者可以供应多种商品，一种商品可以多渠道供货。
（1） E-R图：附表
（2）关系模式：员工（员工号、员工名、工资、销售组名）
销售（销售组名、负责人）
商品（商品号、商品名、单价、销售组）
供应商（供应者号、供应者名、地址）
商品供应（商品号、供应者号）
商品销售（商品号、销售日期、销售量、销售组名）
（3） SQL语言建表：
Create table 员工
( 员工号 int primary key ,
员工名 Char(10) ,
工资 int ,
销售组名 Char(20)
Foreign key 销售组名 references 销售组 (销售组名)
)
Create table 销售组
( 销售组名 Char(20) primary key ,
负责人 Char(10)
)
Create table 商品
( 商品号 int primary key ,
商品名 Char(20) ,
单价 int ,
销售组名 Char(20) ,
Foreign key 销售组名 references 销售组 (销售组名)
)
Create table 供应商
( 供应商号 int primary key ,
供应者名 Char(20) ,
地址 Char(40)
)
Create table 商品供应
( 商品号 int ,
供应者号 int ,
Primary key ( 商品号 , 供应者号 ) ,
Foreign key 商品号 references 商品 (商品号) ,
Foreign key 供应者号 references 供应商 (供应者号)
)

2. 某学校有若干系，每个系有若干学生、若干课程，每名学生选修若干课程，每门课程有若干学生选修，某一门课程可以为不同系开设，现要建立该校学生选修课程的数据库。
（1） E-R图：附表
（2）关系模式：学生（学号、姓名、性别、系号、年龄）
系（系号、系名、系主任、电话）
课程（课号、课名）
选课（学号、课号、成绩）
开课（系号、课号）
（3） SQL语言建表：
Create table 学生
( 学号 int primary key ,
姓名 Char(20) ,
性别 Char(10) ,
系号 int ,
年龄 int ,
Foreign key 系号 references 系 (系号)
)
Create table 系
( 系号 int primary key ,
系名 Char(20) ,
系主任 Char(20) ,
电话 Char(20)
)
Create table 课程
( 课号 int primary key ,
课名 Char(20)
)
Create table 选课
( 学号 int ,
课号 int ,
成绩 int ,
Primary key ( 学号 , 课号) ,
Foreign key 学号 references 学生 (学号) ,
Foreign key 课程 references 课程 (课号)
)
Create table 开课
( 系号 int ,
课号 int ,
Primary key ( 系号 , 课号) ,
Foreign key 系号 references 系 (系号) ,
Foreign key 课号 references 课程 (课号)
)

3. 某工厂生产若干产品，每种产品由不同的零件组成，有的零件可用在不同的产品上。这些零件由不同的原材料制成，不同零件所用的材料可以相同。这些零件按所属的不同产品分别放在仓库中，原材料按照类别放在若干仓库中。
（1） E-R图：附表
（2） 关系模式：产品（产品号、产品名）
零件（零件号、零件名）
原材料（原材料号、原材料名、类别、仓库号）
仓库（仓库号、仓库名）
产品组成（产品号、零件号）
零件制造（零件号、原材料号）
零件存储（零件号、仓库号）
（3） SQL语言建表：
Create table 产品
( 产品号 int primary key ,
产品名 Char(20)
)
Create table 零件
( 零件号 int primary key ,
零件名 Char(20)
)
Create table 原材料
( 原材料号 int primary key ,
原材料名 Char(20) ,
类别 Char(20) ,
仓库号 int ,
Foreign key 仓库号 references 仓库 (仓库号)
)
Create table 仓库
( 仓库号 int primary key ,
仓库名 Char(20) ,
)
Create table 产品组成
( 产品号 int ,
零件号 int ,
Primary key ( 产品号 , 零件号 ) ,
Foreign key 产品号 references 产品 (产品号) ,
Foreign key 零件号 references 零件 (零件号)
)
Create table 零件制造
( 零件号 int ,
原材料号 int ,
Primary key ( 零件号 , 原材料号 ) ,
Foreign key 零件号 references 零件 (零件号) ,
Foreign key 原材料号 references 原材料 (原材料号)
)
Create table 零件存储
( 零件号 int ,
仓库号 int ,
Primary key ( 零件号 , 仓库号 ) ,
Foreign key 零件号 references 零件 (零件号) ,
Foreign key 仓库号 references 仓库 (仓库号)
)
4. 假设一个部门的数据库包括以下的信息：
职工的信息：职工号、姓名、地址和所在部门。
部门的信息：部门所有职工、经理和销售的产品。
产品的信息：产品名、制造商、价格、型号及产品内部编号。
制造商的信息：制造商名称、地址、生产的产品及价格。
其中，部门与职工是一对多的联系；部门与产品、制造商与产品均是多对多的联系。
（1） E-R图：附表
（2） 关系模式：职工（职工号、姓名、地址、部门名）
部门（部门名、经理）
制造商（制造商名、地址）
产品销售（部门名、内部编号）
产品（内部编号、产品名、制造商、价格、型号）
产品生产（制造编号、内部编号）
（3） SQL语言建表：
Create table 职工
( 职工号 int primary key ,
姓名 Char(20) ,
住址 Char(20) ,
部门名 Char(20) ,
Foreign key 部门名 references 部门 (部门名)
)
Create table 部门
( 部门名 Char(20) primary key ,
经理 Char(20)
)
Create table 制造商
( 制造商名 Char(20) primary key ,
地址 Char(20)
)
Create table 产品
( 内部编号 int primary key ,
产品名 Char(20) ,
制造商 Char(20) ,
价格 int ,
型号 Char(20)
)
Create table 产品销售
( 部门名 Char(20) ,
内部编号 int ,
Primary key ( 部门名 , 内部编号 ) ,
Foreign key 部门名 references 部门 (部门名) ,
Foreign key 内部编号 references 产品 (内部编号)
)
Create table 产品生产
( 制造商名 Char(20) ,
内部编号 int ,
Primary key ( 制造商名 , 内部编号 ) ,
Foreign key 制造商名 references 制造商 (制造商名) ,
Foreign key 内部编号 references 产品 (内部编号)
)

5. 设有如下实体：
学生：学号、单位、姓名、性别、年龄、选修课程名。
课程：编号、课程名、开课单位、任课教师。
教师：教师号、姓名、性别、职称、讲授课程编号。
单位：单位名称、电话、教师号、教师名。
上述实体中存在如下联系：一个学生可选修多门课，一门课程可以被多个学生选修，一个教师可讲授多门课程，一门课程可以被多个教师讲授，一个单位可以有多个教师，一个教师只能属于一个单位。
（1） E-R图：附表
（2） 关系模式：学生（学号、姓名、性别、年龄、单位名称）
课程（编号、课程名）
教师（教师号、姓名、性别、职称、单位名称）
单位（单位名称、电话）
学生选课（学号、课程编号）
教师授课（教师号、课程编号）
（3） SQL语言建表：
Create table 学生
( 学号 int primary key ,
姓名 Char(20) ,
性别 Char(10) ,
年龄 int ,
单位名称 Char(20) ,
Foreign key 单位名称 references 单位 (单位名称)
)
Create table 课程
( 编号 int primary key ,
课程名 Char(20) ,
)
Create table 教师
( 教师号 int primary key ,
姓名 Char(20) ,
性别 Char(10) ,
职称 Char(20) ,
单位名称 Char(20) ,
Foreign key 单位名称 references 单位 (单位名称)
)
Create table 单位
( 单位名称 Char(20) primary key ,
电话 Char(20)
)
Create table 学生选课
( 学号 int ,
课程编号 int ,
Primary key ( 学号 , 课程编号) ,
Foreign key 学号 references 学生 (学号) ,
Foreign key 课程编号 references 课程 (编号)
)
Create table 教师授课
( 教师号 int ,
课程编号 int ,
Primary key ( 教师号 , 课程编号 ) ,
Foreign key 教师号 references 教师 (教师号) ,
Foreign key 课程编号 references 课程 (编号)
)

6. 请设计一个图书馆数据库，此数据库中对每个借阅者保存读者记录，包括：读者号、姓名、地址、性别、年龄、单位。对每本书存有：书号、书名、作者、出版社。对每本被借出的书存有读者号、借出日期和应还日期。
（1） E-R图：附表
（2） 关系模式：读者（读者号、姓名、地址、性别、年龄、单位）
书（书号、书名、作者、出版社）
借书（书号、读者号、借出日期、应还日期）
（3） SQL语言建表：
Create table 读者
( 读者号 int primary key ,
姓名 Char(20) ,
地址 Char(20) ,
性别 Char(10) ,
年龄 int ,
单位 Char(20)
)
Create table 书
( 书号 int primary key ,
书名 Char(20) ,
作者 Char(20) ,
出版社 Char(20)
)
Create table 借书
( 书号 int ,
读者号 int ,
借出日期 time ,
应还日期 time ,
Primary key ( 书号, 读者号 ) ,
Foreign key 书号 references 书 (书号) ,
Foreign key 读者号 references 读者 (读者号)
)

7. 经需求分析可知，某医院病房计算机管理系统中需要管理以下信息：
科室：科室名、科室地址、科室电话、医生姓名
病房：病房号、床位号、所属科室
医生：工作证号、姓名、性别、出生日期、联系电话、职称、所属科室
病人：病历号、姓名、性别、出生日期、诊断记录、主管医生、病历号
其中，一个科室有多个病房，多个医生，一个病房只属于一个科室，一个医生只属于一个科室，但可负责多个病人的诊治，一个病人的主管医生只能由一个。
（1） E-R图：附表
（2） 关系模式：科室（科室名、科室地址、电话）
医生（工作证号、姓名、性别、出生日期、联系电话、职称、科室名）
病人（病历号、姓名、出生日期、性别、主管医生、病房号）
病房（病房号、床位号、科室名）
（3） SQL语言建表：
Create table 科室
( 科室名 Char(20) primary key ,

科室地址 Char(20) ,
电话 Char(20)
)
Create table 医生
( 工作证号 int primary key ,
姓名 Char(20) ,
性别 Char(10) ,
出生日期 Char(20) ,
联系电话 Char(20) ,
职称 Char(20) ,
科室名 Char(20) ,
Foreign key 科室名 references 科室 (科室名)
)
Create table 病人
( 病历号 int primary key ,
姓名 Char(20) ,
出生日期 Char(20) ,
性别 Char(10) ,
主管医生 int ,
病房号 int ,
Foreign key 主管医生 references 医生 (工作证号)
Foreign key 病房号 references 病房 (病房号)

)
Create table 病房
( 病房号 int primary key ,
床位号 int ,
科室名 Char(20) ,
Foreign key 科室名 references 科室 (科室名)
)

8. 职工科研管理系统情况如下：
部门：包括部门号、部门名、办公室，每个部有多名职工。
职工：包括职工号、姓名、性别、年龄、职称、专业。
项目：包括项目编号、项目名称、项目经费、项目来源、负责人。
每名职工可参加多个项目，每个项目可多名员工参加。
（1） E-R图：附表
（2） 关系模式：部门（部门号、部门名、办公室）
职工（职工号、姓名、性别、年龄、专业、职称、部门号）
项目（项目编号、项目名称、项目经费、项目来源、负责人）
参加（职工号、项目编号）
（3） SQL语言建表：
Create table 部门
( 部门号 int primary key ,
部门名 Char(20) ,
办公室 Char(20)
)
Create table 职工
( 职工号 int primary key ,
姓名 Char(20) ,
性别 Char(10) ,
年龄 int ,
专业 Char(20) ,
职称 Char(20) ,
部门号 int ,
Foreign key 部门号 references 部门 (部门号)
)
Create table 项目
( 项目编号 int primary key ,
项目名称 Char(20) ,
项目经费 int ,
项目来源 Char(20) ,
负责人 Char(20)
)
Create table 参加
( 职工号 int ,
项目编号 int ,
Primary key ( 职工号 , 项目编号 ) ,
Foreign key 职工号 references 职工 (职工号) ,
Foreign key 项目编号 references 项目 (项目编号)
)

9. 设有如下实体：
学生：学号、专业、姓名、性别、年龄、选修课程名。
课程：编号、课程名、开课单位、任课教师。
教师：教师号、姓名、性别、职称、讲授课程编号。
专业：专业名称、电话、教师号、教师名。
以上实体中存在如下联系：（1）一个学生可选修多门课程，一门课程可为多个学生选修；（2）一个教师可以讲授多门课程，一门课程可为多个教师讲授；（3）一个专业可以有多个教师，一个教师只能属于一个专业。
（此题答案与第5题类似）

10. 工厂（包括厂名和厂长）需建立一个管理数据库存储以下信息：
一个厂内有多个车间，每个车间有车间号、车间主任、地址和电话；
一个车间有多个工人，每个工人有职工号、姓名、年龄、性别和工种；
一个车间可以生产多种产品，产品由产品号和价格；
一个车间生产多种零件，一个零件也可能是多个车间制造，零件有零件号、重量和价格；
一个产品由多个零件组成，一种零件也可装配出多种产品。
（1） E-R图：附表
（2） 关系模式：车间（车间号、车间主任、地址、电话）
工人（职工号、姓名、性别、年龄、工种、车间号）
产品（产品号、价格、车间号）
零件（零件号、重量、价格）
车间制造零件（车间号、零件号）
零件装配商品（产品号、零件号）
（3） SQL语言建表：
Create table 车间
( 车间号 int primary key ,
车间主任 Char(20) ,
地址 Char(20) ,
电话 Char(20)
)
Create table 工人
( 职工号 int primary key ,
姓名 Char(20) ,
性别 Char(10) ,
年龄 int ,
工种 Char(20) ,
车间号 int ,
Foreign key 车间号 references 车间 (车间号)
)
Create table 产品
( 产品号 int primary key ,
价格 int ,
车间号 int ,
Foreign key 车间号 references 车间 (车间号)
)
Create table 零件
( 零件号 int primary key ,
重量 int ,
价格 int
)
Create table 车间制造零件
( 车间号 int ,
零件号 Int ,
Primary key ( 车间号 , 零件号 ) ,
Foreign key 车间号 references 车间 (车间号) ,
Foreign key 零件号 references 零件 (零件号)
)

Create table 零件装配商品
( 产品号 int ,
零件号 Int ,
Primary key ( 产品号 , 零件号 ) ,
Foreign key 产品号 references 产品 (产品号) ,
Foreign key 零件号 references 零件 (零件号)
)
附表（E-R图）：
第一题：

第二题：

第三题：

第四题：

第五题：

第六题：

第七题：

第八题：

第十题：