数据库原理 试卷

以下是某高校教学管理系统的毕业论文指导ER图，数据信息：一名教师指导多名学生，一名学生只能选择一名教师，试分析完成以下各题，如用SQL命令完成的，在SQL Server2008验证后把答案写在题目的下方。

图1 毕业论文指导ER图

表1 教师实体属性相关信息

实体	属性名	数据类型	描述
教师	TNO	CHAR(4)	教师号
	TN	NVARCHAR(10)	姓名
	SEX	NCHAR(1)	教师性别
	PF	NVARCHAR(10)	教师专业
	PROF	NVARCHAR(10)	教师职称

表2 课程实体属性相关信息

实体	属性名	数据类型	描述
学生	SNO	CHAR(6)	学号
	SN	NVARCHAR(10)	姓名
	SEX	NCHAR(1)	性别
	AGE	INT	年龄
	PF	NVARCHAR(10)	学生专业

根据题意，完成下面各题，SQL命令在SQL Server 2008验证后，把SQL命令写在本文档的题目后。

一、分析实体教师与实体学生的联系方式。（本题共 3分）

答案：教师与学生之间是一对多（1:N）的联系。一名教师可以指导多名学生，而一名学生只能选择一名教师作为指导老师。

二、将概念模型的ER图转化成相应关系模式，指出关系的主码，如有外码，指出码，关系名自拟。（本题共 8分）

答案：

教师（TNO，TN，SEX，PF，PROF）

主码：TNO

无外码

学生（SNO，SN，SEX，AGE，PF，TNO）

主码：SNO

外码：TNO（引用教师表的 TNO）

三、第二题所得的关系模式最高满足第几范式？为什么？（本题共 6分）

答案：

最高满足第三范式（3NF）。

第一范式（1NF）：所有属性都是原子的，不可再分，满足。

第二范式（2NF）：每个非主属性完全依赖于主码。在教师表和学生表中，所有非主属性都完全依赖于各自的主码（TNO 和 SNO），满足。

第三范式（3NF）：每个非主属性既不部分依赖于主码也不传递依赖于主码。教师表中的属性直接依赖于 TNO，学生表中的属性直接依赖于 SNO，且 TNO 对学生表中的非主属性的依赖是通过主码 SNO 传递的，但这是合理的外码约束，不存在非主属性之间的传递依赖，满足。

四、用SQL命令创建教学管理数据库STU，并切换到STU数据库。（本题共 6分）

答案：

CREATE DATABASE STU;

GO

USE STU;

五、用SQL命令按第二题的关系模式创建数据表，表名自拟，并按实际应用情况设计各属性的约束条件，比如各表的主码等。（本题共24分）  

答案：

创建教师表:

CREATE TABLE Teachers (

    TNO CHAR(4) PRIMARY KEY,

    TN NVARCHAR(10) NOT NULL,

    SEX NCHAR(1) CHECK (SEX IN ('男', '女')),

    PF NVARCHAR(10) NOT NULL,

    PROF NVARCHAR(10) NOT NULL

);

创建学生表:

CREATE TABLE Students (

    SNO CHAR(6) PRIMARY KEY,

    SN NVARCHAR(10) NOT NULL,

    SEX NCHAR(1) CHECK (SEX IN ('男', '女')),

    AGE INT CHECK (AGE BETWEEN 15 AND 50),

    PF NVARCHAR(10) NOT NULL,

    TNO CHAR(4) FOREIGN KEY REFERENCES Teachers(TNO)

);

               

六、用SQL命令把以下教师信息、学生信息及指导情况添加到数据表中。（本题共12分）

1.教师信息有：

编号1001教师：王小吉、男、计算机专业、副教授职称；

编号1002教师：李明、女、数学专业、教授职称；

编号1003教师：姜丽、女、市场营销专业、讲师职称。

2.学生信息有：

编号11001学生：李勇、男、数学专业、年龄21；

编号11003学生：刘成、女、数学专业、年龄20；

编号11004学生：王敏、女、电子商务专业、年龄19；

3.毕业论文指导情况有：

李明教师指导李勇和刘成，姜丽教师指导王敏。

答案：

添加教师信息:

INSERT INTO Teachers (TNO, TN, SEX, PF, PROF)

VALUES

('1001', '王小吉', '男', '计算机专业', '副教授'),

('1002', '李明', '女', '数学专业', '教授'),

('1003', '姜丽', '女', '市场营销专业', '讲师');

添加学生信息及指导关系:

INSERT INTO Students (SNO, SN, SEX, AGE, PF, TNO)

VALUES

('11001', '李勇', '男', 21, '数学专业', '1002'),

('11003', '刘成', '女', 20, '数学专业', '1002'),

('11004', '王敏', '女', 19, '电子商务专业', '1003');

七、SQL命令完成以下各题。（本题共7小题，每小题5分，共35分）

1.查询所有教师信息。

答案：

SELECT * FROM Teachers;

2.查询年龄在19至20岁之间的学生姓名、专业、年龄。

答案：

SELECT SN, PF, AGE

FROM Students

WHERE AGE BETWEEN 19 AND 20;

3.查询1002教师指导的学生信息。

答案：

SELECT *

FROM Students

WHERE TNO = '1002';

4.把电子商务女学生的年龄更新为21岁。

答案：

UPDATE Students

SET AGE = 21

WHERE PF = '电子商务专业' AND SEX = '女';

5.为教师姓名添加唯一值索引。

答案：

CREATE UNIQUE INDEX idx_Teachers_TN

ON Teachers (TN);

6.查询不是教授职称的教师姓名、职称、专业。

答案：

SELECT TN, PROF, PF

FROM Teachers

WHERE PROF <> '教授';

7.查询没有指导学生的教师的信息。

答案：

SELECT *

FROM Teachers

WHERE TNO NOT IN (SELECT DISTINCT TNO FROM Students WHERE TNO IS NOT NULL);

八、创建教师指导学生的视图，包含教师姓名、学生学号、学生姓名、学生专业，视图名为Guide（本题6分）

答案：

CREATE VIEW Guide AS

SELECT T.TN AS 教师姓名, S.SNO AS 学生学号, S.SN AS 学生姓名, S.PF AS 学生专业

FROM Teachers T

LEFT JOIN Students S ON T.TNO = S.TNO;