数据库基础1-n

最新推荐文章于 2025-02-05 20:52:48 发布

Wood_Du

最新推荐文章于 2025-02-05 20:52:48 发布

阅读量2.5k

点赞数 7

分类专栏： School_konwledge

本文链接：https://blog.youkuaiyun.com/Wood_Du/article/details/92420831

版权

School_konwledge 专栏收录该内容

33 篇文章

订阅专栏

实验一：E-R图和数据库

E-R图：

在这里插入图片描述

数据库表：

Drop table if EXISTS `school`;
CREATE table `school`(
	`id` int(11) not null auto_increment,
	`name` varchar(255) not null,
	`location` varchar(255) not null,
	PRIMARY key(`id`)
)
Drop table if EXISTS `department`;
CREATE table `department`(
	`id` int(11) not null auto_increment,
	`name` varchar(255) not null,
	`description` varchar(255) not null,
	`schoolid` int(11) not null,
	PRIMARY key(`id`)
)
Drop table if EXISTS `class`;
CREATE table `class`(
	`id` int(11) not null auto_increment,
	`name` varchar(255) not null,
	`monitor` varchar(255) not null,
	`departmentid` int(11) not null,
	PRIMARY key(`id`)
)
Drop table if EXISTS `student`;
CREATE table `student`(
	`id` int(11) not null auto_increment,
	`name` varchar(255) not null,
	`birthplace` varchar(255) not null,
	`classid` int(11) not null,
	PRIMARY key(`id`)
)
Drop table if EXISTS `course`;
CREATE table `course`(
	`id` int(11) not null auto_increment,
	`name` varchar(255) not null,
	`course_teacher` varchar(255) not null,
	`studentid` int(11) not null,
	PRIMARY key(`id`)
)
Drop table if EXISTS `lab`;
CREATE table `lab`(
	`id` int(11) not null auto_increment,
	`name` varchar(255) not null,
	`departmentid` int(11) not null,
	PRIMARY key(`id`)
)
Drop table if EXISTS `teacher`;
CREATE table `teacher`(
	`id` int(11) not null auto_increment,
	`name` varchar(255) not null,
	`jobtitle` VARCHAR(255) not null, 
	`labid` int(11) not null,
	PRIMARY key(`id`)
)
Drop table if EXISTS `graduate_student`;
CREATE table `graduate_student`(
	`id` int(11) not null auto_increment,
	`name` varchar(255) not null,
	`birthplace` varchar(255) not null,
	`teacherid` int(11) not null,
	PRIMARY key(`id`)
)
Drop table if EXISTS `score`;
CREATE table `score`(
	`course_id` int(11) not null,
	`student_id` int(11) not null,
    `num` int(11) not null
)

实验二：创建数据库表&增删改查&可视化操作

增删该查：

部分可视化操作；

部分SQL语句；

#插入语句
insert into `Student` VALUES('201215123','王敏','女','18','MA');
insert into `Student` VALUES('201210125','张立','男','19','IS');

insert into `Course` VALUES('1','数据库','5','4');
insert into `Course` VALUES('2','数学',null,'2');
insert into `Course` VALUES('3','信息系统','1','4');
insert into `Course` VALUES('4','操作系统','6','3');
insert into `Course` VALUES('5','数据结构','7','4');
insert into `Course` VALUES('6','数据处理',null,'42');
insert into `Course` VALUES('7','PASCAL语言','6','4');

INSERT into `SC` VALUES('201215121','1','92');
INSERT into `SC` VALUES('201215121','2','85');
INSERT into `SC` VALUES('201215121','3','88');
INSERT into `SC` VALUES('201215122','2','90');
INSERT into `SC` VALUES('201215122','3','80');

#增
insert into `Course` VALUES('8','C++程序设计','2','3');

#更新
UPDATE `Course` set Cname='Java程序设计' where Cno='8';

#删除
DELETE from Course where Cname='Java程序设计';

#查询
SELECT * from Student;
SELECT * from course;
SELECT * from SC;
#精确查询 asc 升序 desc 降序
SELECT * from student where student.Ssex='女' ORDER BY student.Sage asc;
#其他条件查询
SELECT Sname,Ssex from student where Sno='201215121';

SELECT * from student,sc where student.sno=sc.Sno;

基本sql语句

#test
alter table student add S_entrance DATE;
SELECT * from Student;

#书上例子
#数据查询
select Sno,Sname from Student;

SELECT Sname,Sno,Sdept from student;

SELECT * from student;

SELECT Sname,2019-Sage from student;

SELECT Sname,'Year of birth: ',2019-Sage,LOWER(Sdept) FROM student;

SELECT DISTINCT Sno from sc;

select Sname,Sage from student where Sage<20;


SELECT DISTINCT Sno from sc WHERE Grade<60;

SELECT Sname,Sdept,Sage from student where Sage BETWEEN 20 and 30;

SELECT Sname,Sdept,Sage from student where Sage not BETWEEN 20 and 30;

SELECT Sname,Ssex from student where Sdept in('CS','MA','IS');

SELECT * from student WHERE Sno like '201215121';

SELECT Sname,Sno,Sage from student where Sname like '刘%';



insert into `Course` VALUES('8','DB_Design','7','4');
SELECT * from course;
##该语句有错误
#SELECT Cno,Ccredit from course where Cname LIKE `DB\_Design` ESCAPE `\`;
##SELECT * from course where Cname like 'DB\_%i__' ESCAPE `\`;


SELECT Sno,Cno from sc where Grade is not null;

#ORDER BY

SELECT Sno,Grade from sc where Cno ='3' ORDER BY Grade DESC;

#聚集函数 只能用于select 子句和Group By 的Having 子句
SELECT COUNT(*) from student;

SELECT AVG(Grade) from sc where Cno='1';

SELECT MAX(Grade) from sc where Cno='1';

SELECT sum(Ccredit) from course,sc where Sno='201215012' and sc.Cno=course.Cno;


#GROUP BY  分组 细化聚集函数的作用对象
SELECT Cno,COUNT(Sno) from sc GROUP BY Cno;
#先用 GROUP BY 按 Sno 进行分组， 再用聚集函数count 对每一组计数，
SELECT Sno from sc GROUP BY Sno Having count(*)>1;
#错误语句 where 中不能有聚集函数
#SELECT Sno,AVG(Grade) from sc where AVG(Grade)>=90 GROUP BY Sno;
SELECT Sno,AVG(Grade) from sc Group by Sno HAVING AVG(Grade)>=90;

#连接查询
SELECT student.*,sc.* from student,sc where student.Sno = sc.Sno;
SELECT student.Sno, Sname,Ssex,Sage,Sdept,Cno,Grade from student,sc where student.Sno=sc.Sno;
#去掉 student 报错
#SELECT Sno, Sname,Ssex,Sage,Sdept,Cno,Grade from student,sc where student.Sno=sc.Sno;

#先修课的先修课 
SELECT FIRST.Cno,SECOND.Cpno from Course FIRST,Course SECOND where FIRST.Cpno=SECOND.Cno;

#左外连接
SELECT student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from student left OUTER join sc on(student.Sno = sc.Sno);

SELECT student.Sno,Sname,Cname,Grade from student,sc,course where student.Sno=sc.Sno and sc.Cno = course.Cno;
#各院系选修数据库的人数
SELECT Sdept,COUNT(student.Sno) from sc,student,course where sc.Cno = course.Cno and student.Sno=sc.Sno and course.Cname='信息系统' GROUP BY Sdept;

实验三

建立学生选课数据库，并对每张表录入实验数据

--创建表
Drop TABLE if EXISTS `Student`;
CREATE table `Student`(
	Sno varchar(255) not null,
	Sname varchar(255) not null,
	Ssex VARCHAR(255) not null,
	Sage int(11) not null,
	Sdept varchar(255) not null,
	PRIMARY key(Sno)
)

Drop table if exists Course;
create TABLE Course(
Cno varchar(255) PRIMARY key,
Cname varchar(255) not null,
Cpno varchar(255),
Ccredit varchar(255) not null
)

Drop TABLE if EXISTS SC;
CREATE table SC(
Sno varchar(255) not null,
Cno varchar(255) not null,
Grade int(11) not NULL,
PRIMARY KEY(Sno,Cno),
FOREIGN Key(Sno) REFERENCES Student(Sno),
FOREIGN KEY(Cno) REFERENCES Course(Cno)
)
--插入数据
insert into Student VALUES('201215121','李勇','男',20,'CS');
SELECT * from Student;
insert into Student VALUES('201215122','刘晨','女',19,'CS');
insert into Student VALUES('201215123','王敏','女',18,'MA');
insert into Student VALUES('201215125','张力','男',19,'IS');

INSERT into Course values('1','数据库','5','4');
SELECT * from Course;
insert into `Course` VALUES('2','数学',null,'2');
insert into `Course` VALUES('3','信息系统','1','4');
insert into `Course` VALUES('4','操作系统','6','3');
insert into `Course` VALUES('5','数据结构','7','4');
insert into `Course` VALUES('6','数据处理',null,'42');
update Course set Ccredit = '2' where Cno = '6';
insert into `Course` VALUES('7','PASCAL语言','6','4');

insert into SC VALUES('201215121','1','92');
SELECT * from SC;
INSERT into `SC` VALUES('201215121','2','85');
INSERT into `SC` VALUES('201215121','3','88');
INSERT into `SC` VALUES('201215122','2','90');
INSERT into `SC` VALUES('201215122','3','80');

联系各类查询语句，增加、删除、修改等语句（选取一些将SQL语句与查询结果截图附）

--查询语句
SELECT Sno,Sname from Student;

SELECT Sname,Sno,Sdept from student;

SELECT * from student;

SELECT Sname,2019-Sage from student;

SELECT Sname,'Year of birth: ',2019-Sage,LOWER(Sdept) FROM student;

SELECT DISTINCT Sno from sc;

select Sname,Sage from student where Sage<20;


SELECT DISTINCT Sno from sc WHERE Grade<60;

SELECT Sname,Sdept,Sage from student where Sage BETWEEN 20 and 30;

SELECT Sname,Sdept,Sage from student where Sage not BETWEEN 20 and 30;

SELECT Sname,Ssex from student where Sdept in('CS','MA','IS');

SELECT * from student WHERE Sno like '201215121';

SELECT Sname,Sno,Sage from student where Sname like '刘%';

insert into `Course` VALUES('8','DB_Design',null,'4');

--语法有错误 
--SELECT Cno,Ccredit from course where Cname like 'DB\_Design' ESCAPE "\";

SELECT Sno,Cno from sc where Grade is not null;

--ORDER BY

SELECT Sno,Grade from sc where Cno ='3' ORDER BY Grade DESC;

--聚集函数 只能用于select 子句和Group By 的Having 子句
SELECT COUNT(*) from student;

SELECT AVG(Grade) from sc where Cno='1';

SELECT MAX(Grade) from sc where Cno='1';

SELECT sum(Ccredit) from course,sc where Sno='201215122' and sc.Cno=course.Cno;

--GROUP BY  分组 细化聚集函数的作用对象
SELECT Cno,COUNT(Sno) from sc GROUP BY Cno;
--先用 GROUP BY 按 Sno 进行分组， 再用聚集函数count 对每一组计数，
SELECT Sno from sc GROUP BY Sno Having count(*)>1;
--错误语句 where 中不能有聚集函数
--SELECT Sno,AVG(Grade) from sc where AVG(Grade)>=90 GROUP BY Sno;
SELECT Sno,AVG(Grade) from sc Group by Sno HAVING AVG(Grade)>=90;

--连接查询
SELECT student.*,sc.* from student,sc where student.Sno = sc.Sno;
SELECT student.Sno, Sname,Ssex,Sage,Sdept,Cno,Grade from student,sc where student.Sno=sc.Sno;
--去掉 student 报错
--SELECT Sno, Sname,Ssex,Sage,Sdept,Cno,Grade from student,sc where student.Sno=sc.Sno;

SELECT Student.Sno,Sname from Student,SC where Student.Sno = SC.Sno AND SC.Cno = '2' AND SC.Grade>90;

--先修课的先修课 
SELECT FIRST.Cno,SECOND.Cpno from Course FIRST,Course SECOND where FIRST.Cpno=SECOND.Cno;

--左外连接
SELECT student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from student left OUTER join sc on(student.Sno = sc.Sno);

SELECT student.Sno,Sname,Cname,Grade from student,sc,course where student.Sno=sc.Sno and sc.Cno = course.Cno;
--各院系选修数据库的人数
SELECT Sdept,COUNT(student.Sno) from sc,student,course where sc.Cno = course.Cno and student.Sno=sc.Sno and course.Cname='信息系统' GROUP BY Sdept;

--嵌套查询  不相关子查询
select Sno,Sname,Sdept From Student where Sdept IN (SELECT Sdept from student where Sname = '刘晨');

SELECT Sno,Sname from Student where Sno in (
SELECT Sno from SC where Cno in (
SELECT Cno from Course where Cname = '信息系统'
)
);

--相关自查询
SELECT Sno,Cno from SC x where Grade>=(
SELECT AVG(Grade) from SC y where y.Sno = x.Sno);

--ANY ALL
SELECT Sname,Sage from Student where Sage<ANY (
SELECT Sage From student where Sdept = 'CS') AND Sdept != 'CS';

SELECT Sname,Sage from Student where Sage<ALL (
SELECT Sage From student where Sdept = 'CS') AND Sdept <> 'CS';

--EXISTS
SELECT Sname from student WHERE exists (
SELECT * from SC where Sno = student.Sno and Cno = '1');

SELECT Sname from student WHERE not exists (
SELECT * from SC where Sno = student.Sno and Cno = '1');

--查询选修了全部课程的学生姓名  #没有一门课程是他不选修的
SELECT Sname from Student where not exists (
SELECT * from Course where not exists (
SELECT * from SC where Sno = Student.Sno And Cno = Course.Cno)); 


--查询至少选修了学生201215122选修的全部课程的学生号码
--不存在这样的课程y,学生201215122选修了y,而学生x没选
SELECT DISTINCT Sno from SC SCX where NOT EXISTS(
SELECT * from SC SCY where SCY.Sno = '201215122' And not EXISTS (
SELECT * from SC SCZ WHERE SCZ.Sno = SCX.Sno AND SCZ.Cno = SCY.Cno));

--集合查询 系统会自动去掉重复元组
--UNION 并
SELECT Sno from SC where Cno = '1' UNION SELECT Sno from SC where Cno = '2';

--intersect 交操作 不行 mysql不支持
--SELECT * from student where Sdept = 'CS' INTERSECT SELECT * from student where Sage<=19;
SELECT * from student where Sdept = 'CS' and Sage <=19;

--except 差 mysql 不支持 返回第一个表一有，表二没有的
--select * from Student where Sdept = 'CS' EXCEPT SELECT * from Student WHERE Sdept = 'CS' and Sage>=19;
SELECT * from Student where Sdept = 'CS' AND Sage >19;

--派生表的查询  未成功
--SELECT Sno,Cno from SC, (SELECT Sno,AVG(Grade) from SC GROUP BY Sno) 
--AS Avg_sc(avg_sno , avg_grade)
--WHERE SC.Sno = Avg_sc.avg_sno and SC.Grade >= Avg_sc.avg_grade;

--数据更新
insert into Student VALUES ('201215126','张成民','男',18,'CS');
SELECT * from student;
update Student set Sage = 22 where Sno = '201215121';

delete from Student where Sno = '201215126';

实验四（第三章课后习题4-9）

建表&插入数据

--建表
--供应商表
DROP table if exists `S`;
CREATE table `S`(
	SNO varchar(255) not null,
	SNAME varchar(255) not null,
	STATU int(11) not null, #STATUS 为关键字
	CITY varchar(255) not null,
	PRIMARY key(SNO)
)
--零件表
drop table if EXISTS `P`;
CREATE table `P`(
	PNO varchar(255) not null,
	PNAME varchar(255) not null,
	COLOR varchar(255) not null,
	WEIGHT int(11) not null,
	PRIMARY key(PNO)
)
--工程项目表
drop table if EXISTS `J`;
CREATE table `J`(
	JNO varchar(255) not null,
	JNAME varchar(255) not null,
	CITY  varchar(255) not null,
	PRIMARY key(JNO)
)
--供应情况表
#restrict 如果从S,P,J中删除一条数据，SPJ中有 ，则会报错不允许删除
#cascade 对应数据一起删除
drop TABLE if exists `SPJ`;
CREATE table `SPJ`(
	SNO VARCHAR(255) not null,
	PNO varchar(255) not null,
	JNO varchar(255) not null,
	QTY int(11) not null,
	PRIMARY key(SNO,PNO,JNO),
	foreign key(SNO) references S(SNO),
	foreign key(PNO) references P(PNO),
	foreign key(JNO) references J(JNO)
)

select * from S;
insert into S VALUES('S1','精益',20,'天津');
insert into S VALUES('S2','盛锡',10,'北京');
insert into S VALUES('S3','东方红',30,'北京');
insert into S VALUES('S4','丰泰盛',20,'天津');
insert into S VALUES('S5','为民',30,'上海');

SELECT * from P;
insert into P values('P1','螺母','红',12);
insert into P values('P2','螺栓','绿',17);
insert into P values('P3','螺丝刀','蓝',14);
insert into P values('P4','螺丝刀','红',14);
insert into P values('P5','凸轮','蓝',40);
insert into P values('P6','齿轮','红',30);

SELECT * from J;
insert into J VALUES('J1','三建','北京');
insert into J VALUES('J2','一汽','长春');
insert into J VALUES('J3','弹簧厂','天津');
insert into J VALUES('J4','造船厂','天津');
insert into J VALUES('J5','机车厂','唐山');
insert into J VALUES('J6','无线电厂','常州');
insert into J VALUES('J7','半导体厂','南京');

SELECT * from SPJ;
insert into SPJ VALUES('S1','P1','J1',200);
insert into SPJ VALUES('S1','P1','J3',100);
insert into SPJ VALUES('S1','P1','J4',700);
insert into SPJ VALUES('S1','P2','J2',100);
insert into SPJ VALUES('S2','P3','J1',400);
insert into SPJ VALUES('S2','P3','J2',200);
insert into SPJ VALUES('S2','P3','J4',500);
insert into SPJ VALUES('S2','P3','J5',400);
insert into SPJ VALUES('S2','P5','J1',400);
insert into SPJ VALUES('S2','P5','J2',100);
insert into SPJ VALUES('S3','P1','J1',200);
insert into SPJ VALUES('S3','P3','J1',200);
insert into SPJ VALUES('S4','P5','J1',100);
insert into SPJ VALUES('S4','P6','J3',300);
insert into SPJ VALUES('S4','P6','J4',200);
insert into SPJ VALUES('S5','P2','J4',100);
insert into SPJ VALUES('S5','P3','J1',200);
insert into SPJ VALUES('S5','P6','J2',200);
insert into SPJ VALUES('S5','P6','J4',500);

查询

--查询
--第四题
SELECT DISTINCT SNO from SPJ where JNO='J1';

SELECT DISTINCT SNO from SPJ where JNO='J1' and PNO='P1';

SELECT SNO from SPJ where JNO='J1' and PNO in(SELECT PNO from P where COLOR='红');

SELECT JNO from J where not exists (
	SELECT * from SPJ where SNO in(
		SELECT SNO from S where CITY = '天津')
	and PNO in(
		SELECT PNO from P where COLOR='红')
	and SPJ.JNO = J.JNO
)
--等价于
SELECT JNO from J where not EXISTS (
	SELECT * from SPJ,S,P where SPJ.PNO=P.PNO and SPJ.SNO=S.SNO and S.CITY = '天津' and P.COLOR='红' and JNO=J.JNO);

SELECT DISTINCT PNO from SPJ where SNO='S1';
SELECT JNO from SPJ where PNO='P1' and SNO='S1' and JNO in (SELECT JNO from SPJ where PNO='P2' and SNO='S1') ;
--等价于
SELECT JNO from J where not EXISTS(
	SELECT * from SPJ x where x.SNO='S1' and not EXISTS(
		SELECT * from SPJ y where y.PNO=X.PNO and y.JNO=J.JNO and y.SNO='S1')
)

--第五题
SELECT SNAME,CITY from S;

SELECT PNAME,COLOR,WEIGHT from P;

SELECT JNO from SPJ where SNO = 'S1';

SELECT PNAME,QTY from SPJ,P where JNO = 'J2' and SPJ.PNO=P.PNO;

SELECT DISTINCT PNO from S,SPJ where CITY='上海' and SPJ.SNO = S.SNO;

SELECT DISTINCT JNAME from S,J,SPJ where S.CITY = '上海' and S.SNO = SPJ.SNO and J.JNO=SPJ.JNO;

SELECT DISTINCT JNO from J where JNO not in(
	SELECT JNO from S,SPJ where CITY = '天津' and S.SNO =SPJ.SNO)

SELECT * from P;
UPDATE P set COLOR = '蓝' where COLOR = '红';

SELECT * from SPJ;
update SPJ set SNO='S3' where SNO='S5' and JNO='J4' and PNO='P6';

SELECT * from S;
DELETE from S where SNO='S2';

INSERT into SPJ VALUES('S2','P4','J6',200);

视图

基本表是本身独立存在的表，在SQL一个关系对应一个表。视图是从一个或几个基本表中导出的表。视图本身不独立存储在数据库中，是一个虚表。即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍然存放在导出视图的基本表中。视图在概念上与基本表等同，用户可以如同基本表那样使用视图，可以在视图上再定义视图。

视图优点

1、视图可以简化用户的操作；

使用户将注意力集中在所关心的数据上

2、视图使用户能以多种角度看待同一数据

3、视图对重构数据库提供了一定程度的逻辑独立性

数据的物理独立性：用户的应用程序不依赖于数据库的物理结构

数据的逻辑独立性：当数据库重构造时，如增加新的关系或对原有关系增加新的字段等，用户的应用程序不会受影响。

4、视图能够对机密数据提供安全保护

在设计数据库应用系统时对不同的用户定义不同的视图，使机密数据不出现在不应看到这些数据的用户视图上。

视图更新

行列子集视图：一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，则称这类视图为行列子集视图。

哪类视图可以更新：一般的，行列子集视图是可以更新的。

规定不可更新：

1、若视图是优两个以上基本表导出的，则此视图不允许更新；

2、若视图的字段来自字段表达式或常数，则不允许对此视图执行insert和update操作，但允许执行delete操作；

3、若视图的字段来自聚集函数，对此视图不允许更新；

4、若视图定义中含有GROUP BY 子句，则不允许更新；

5、若视图中含有distinct短语，则不允许更新；

6、若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新；

CODE

--视图
CREATE VIEW VSP 
AS SELECT SNO,SPJ.PNO,QTY from SPJ,J
where SPJ.JNO=J.JNO and J.JNAME='三建';

SELECT * from VSP;

SELECT  PNO,sum(QTY) from vsp GROUP BY PNO;

SELECT  * from VSP where SNO='S3';

实验五

实验目的：

通过实验体会关系数据理论，函数依赖以及范式

实验内容：

1.设计一个关系，使之满足1NF而不满足2NF。

2.设计一个关系，使之满足2NF而不满足3NF。

3.设计一个关系，使之满足3NF而不满足BCNF。

4.详细描述各关系的语义，分析各关系中存在的函数依赖。

5.使用商用数据库SQL Server或MySQL设计实现，录入数据。体会数据冗余、插入异常、删除异常、修改复杂。

实验步骤：

1、设计一个关系，使之满足1NF而不满足2NF。

关系模式Student(Sno,Cno,Sname,Sage,Sdept,Mname,Grade),其中Mname为系主任名字。

Student

学号(Sno)	课程号（Cno)	姓名（Sname)	年龄（Sage)	所在系(Sdept)	系主任(Mname)	成绩（Grade)

码为（Sno,Cno)

函数依赖
${（Sno,Cno)\overrightarrow{F}Grade} \\ {Sno\rightarrow Sname} , {（Sno,Cno)\overrightarrow{P}Sname}\\ {Sno\rightarrow Sage} , {（Sno,Cno)\overrightarrow{P}Sage}\\ {Sno\rightarrow Sdept} , {（Sno,Cno)\overrightarrow{P}Sage}\\ {Sno\rightarrow Mname}, {（Sno,Cno)\overrightarrow{P}Mname}\\ {Sdept\rightarrow Mname}$

由以上函数依赖可以看出有部分函数依赖。所以不满足第二范式。

2、设计一个关系，使之满足2NF而不满足3NF。

消除上面Student的部分函数依赖。分解成两个关系模式：

SC(Sno,Cno,Grade);

S(Sno,Sname,Sage,Sdept,Mname);

S表满足2NF而不满足3NF。

码是Sno

S表的函数依赖
$KaTeX parse error: No such environment: split at position 8: \begin{̲s̲p̲l̲i̲t̲}̲ &{Sno\rightarr…$
由函数依赖得知：

每一个非主属性完全函数依赖于码。满足2NF。

但是存在传递依赖：
${Sno\overrightarrow{传递} Mname} \\$
所以S表不满足3NF。

3.设计一个关系，使之满足3NF而不满足BCNF。

对于BCNF：满足3NF，每函数依赖的x必是候选码。

关系模式STJ（S,T,J）

关系模式STJ（S,T,J）中，S表示学生，T表示教师，J表示课程。每一个教师只教一门课。每门课有若干个教师，某一学生选定某门课，就对应一个固定的教师。

函数依赖
$KaTeX parse error: No such environment: split at position 8: \begin{̲s̲p̲l̲i̲t̲}̲ &{(S,J)\righta…$
(S,J), (S,T) 都是候选码。

因为没有任何非主属性对码传递依赖或部分依赖。STJ是3NF。

但是T是决定因素，T不包含码。所以STJ不满足BCNF。

5.使用商用数据库SQL Server或MySQL设计实现，录入数据。体会数据冗余、插入异常、删除异常、修改复杂。

对于上面关系模式Student设计实现：

Code:

drop table if exists `Student`;
CREATE table `Student`(
	Sno varchar(255) not null,
	Cno varchar(255) not null,
	Sname varchar(255) not null,
	Sage int(11) ,
	Sdept varchar(255) ,
	Mname varchar(255) ,
	Grade int(11) ,
	PRIMARY key(Sno,Cno)
)

//
insert into Student VALUES('S1','C1','李二',23,'CS','张明',99);
insert into Student VALUES('S2','C2','阿三',22,'CS','张明',98);
SELECT * from Student;

(1)插入异常


//插入异常
INSERT into Student(Sno,Sname,Sdept,Mname) VALUES('S7','刘明','保密','张杰');

报错：不能插入数据。

由于该生还未选课，即这个学生无Cno，这样的元组不能插入Student中。因为插入元组时必须给定码值，而这时的码值一部分为空，因而学生的固有信息无法插入。

(2)删除异常


//删除异常
delete from Student where Cno = 'C2';

如上表，学生S2只选了一门课C2, 现在C2这门课他不选了，那么C2这个数据项就要删除。

删除C2，因为C2为主属性，所以整个元组一起被删除，使得S2的其他信息也被删除了，从而造成删除异常。

(3)修改复杂

学生S2从计算机(CS)转到数学系(MA) ,本来只需要修改学生元组的Sdept分量即可，但因为关系模式Student中还含有系的系主任Mname属性，学生转系将同时改变系主任，因此还必须修改元组中的 Mname 分量。另外，如果这个学生选修了k门课，Sdept，Mname重复了k次，不仅存储冗余度打，而且必须无遗漏的修改k个元组中全部的Sdept,Mname分量，造成修改的复杂化。

(4)数据冗余

每一个系的系主任姓名重复出项。重复次数与该系所有学生的课程成绩出现次数相同，这将浪费大量的存储空间。

实验六

实验目的：

通过实验体会关系数据理论，函数依赖以及范式

实验内容：

1.关系模式R（员工编号，日期，零件数，部门名称，部门经理），表示某个工厂里每个员工的日生产零件数以及员工所在的部门和经理信息。假设：每个员工每天只有一个日生产零件数，每个员工只在一个部门工作，每个部门只有一个经理：
(1)写出模式R的基本函数依赖和候选码。
(2)R是否是2NF，如果不是，把R分解成2NF。
(3)进一步将R分解为3NF。
2.《数据库系统》课本P202，习题2

在这里插入图片描述