2023年7月27日发(作者：)

数据库设计题

数据库设计题（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.设有如下实体：

学生：学号、专业、姓名、性别、年龄、选修课程名。课程：编号、课程名、开课单位、任课教师。

教师：教师号、姓名、性别、职称、讲授课程编号。

专业：专业名称、电话、教师号、教师名。