2024 电子科技大学数据库期末考试复习笔记

lulu_melon

已于 2024-07-22 15:23:45 修改

阅读量892

点赞数 8

文章标签：数据库笔记 sql 学习 postgresql

于 2024-07-22 15:21:36 首次发布

本文链接：https://blog.csdn.net/weixin_73727639/article/details/140609490

版权

2024 电子科技大学数据库期末考试复习笔记

全部融汇贯通轻松90+

考试回忆录

postgresql的数据结构
关系代数编写
范式
数据存储
权限
加锁协议
游标
JDBC
sql语句编程题
触发器
存储结构+游标
er图
cdm转pdm

聚合函数

在这里插入图片描述

完整性约束

在这里插入图片描述

三级加锁协议

三段锁协议：
共享锁（S锁）：共享 (S) 用于不更改或不更新数据的操作（只读操作），如 SELECT 语句。
如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。

排他锁（X)：用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时同一资源进行多重更新。
如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

用三段锁协议来解决数据不一致的三种情况
1.一级封锁协议解决丢失修改
当事务在更新数据的时候给数据加上排他锁
原理：加上排他锁之后，其他事务不能对该数据加上任意锁，在当前事务没有释放锁时其他事务不能进行对该数据的读写操作，只有当前事务释放排他锁之后，才能对该数据进行操作

在这里插入图片描述

只加排他锁

2.二级封锁协议解决读脏数据
在一级封锁协议的基础上，当事务在读取数据的时候加共享锁,读取完成后释放锁
原理：加入共享锁之后，不能对该数据加排他锁，即其他事务不能进行修改数据。所以此时读取的数据一定是与数据库一致的

在这里插入图片描述

先加排他锁，再加共享锁

3.三级封锁线协议不可重复读
在一级封锁协议的基础上，当事务在读取数据的时候加共享锁，事务结束后释放
原理：加入共享锁之后，不能对该数据加排他锁，即其他事务不能进行修改数据。如果读取完成后就释放，那么其他事务此时可以修改该数据。当延迟到事务结束后释放，其他事务就无法修改该数据了

在这里插入图片描述

先加共享锁，再加排他锁

非标识符依赖or标识符依赖

在这里插入图片描述

弱实体的pk是自己的，而被依赖实体的主键在弱实体中充当外键

在这里插入图片描述

弱实体的pk是（被依赖实体的pk+自己的pk）组成的复合主键，同样，被依赖实体的pk充当弱实体的外键。

4NF

第一范式（1NF）

数据库中：表的每一列都是不可分割的基本数据项，同一列中不能有多个值（不能是集合、数组等）
实体中：某个属性不能有多个值，或者不能有重复的属性，即每个属性都是不可分割的原子项。

第二范式（2NF）

在1NF基础上，一个关系（表）必须有一个主键（主码），且非主属性（非主码属性）必须完全依赖于主键（主码）。

第三范式（3NF）

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

BCNF范式

巴斯-科德范式即在满足3NF基础上，任何主属性不能对候选码子集有依赖（即在3NF基础上，消除主属性对候选码的部分函数依赖和传递函数依赖)

若一个关系达到了第三范式，并且它只有一个候选码，或者它的侯选码都是单属性，则该关系自然达到BCNF。

第四范式

消除表中的多值依赖，要求把同一表内的多对多关系删除；
分解表格，以解决信息冗余，达到“一事一地”也就是一对一的关系；

多值依赖与函数依赖的区别
1）若函数依赖X→Y在R（U）上成立，则对于任何Y’ 属于Y均有X→Y’ 成立(一对一)。
2）多值依赖X→→Y若在R(U)上成立，不能断言对于任何Y’ 属于Y有X→→Y’ 成立，因为多值依赖中，其实就是一对一组，一个老师可能教多门课，不同老师可能有教相同的课，所以不能推出X→→Y’ 成立。
3）把一组改为一个，实际上就是函数依赖，所以说函数依赖是多值依赖的特例，多值依赖不一定是函数依赖，但函数依赖一定是多值依赖。

总结

1NF：使属性具有原子性；
2NF：消除非主属性对主键（主码）的部分依赖；就是说每个非主属性必须依赖于候选码集合（在数据库具体来说就是复合主键）
3NF：消除非主属性对主建（主码）的传递依赖；
BCNF：，任何主属性不能对候选码子集有依赖
4NF：消除属性间的多值依赖；

判定方法：
对于任意关系中，如果存在两个元组（就是行），记为A,B，如果他们的某一属性X的值相等，那么我们交换它们另外的属性Y的值后，得到的新的两个元组，在表中是可以在原来的表中找到与它们相匹配的元组的。

在这里插入图片描述

扩充知识：

1、码=超键：能够唯一标识一条记录的属性或属性集。
标识性：一个数据表的所有记录都具有不同的超键
非空性：不能为空
有些时候也把码称作“键”

2、候选键=候选码：能够唯一标识一条记录的最小属性集
标识性：一个数据表的所有记录都具有不同的候选键
最小性：任一候选键的任何真子集都不能唯一标识一个记录（比如在成绩表中（学号,课程号）是一个候选键，单独的学号，课程号都不能决定一条记录）
非空性：不能为空
候选键是没有多余属性的超键
举例：学生ID是候选码，那么含有候选码的都是码。
少部分地方也有叫超级码的，但是见得不多

3、主键=主码：某个能够唯一标识一条记录的最小属性集（是从候选码里人为挑选的一条）
唯一性：一个数据表只能有一个主键
标识性：一个数据表的所有记录都具有不同的主键取值
非空性：不能为空
人为的选取某个候选码为主码

4、主属性包含在任一候选码中的属性称主属性。简单来说，主属性是候选码所有属性的并集
非主属性不包含在候选码中的属性称为非主属性。非主属性是相对于主属性来定义的。

5、外键（foreign key）：子数据表中出现的父数据表的主键，称为子数据表的外键。

练习一（需熟练掌握并融汇贯通）

Course（courseid，coursename，credit）

Grade（gradeid，studentid，courseid，grade）

Student（studentid，studentname，sex，age）

1、如果grade大于等于60分视为该学生取得相应学分，编写sql代码统计每个学生取得的总学分，包含学号、姓名、总学分；

2、使用plgqsl语言编写存储过程函数完成上述功能；

3、使用游标完成输出显示；

1、
SELECT A.studentid AS 学号, A.studentname AS 学生姓名, SUM(C.credit) AS 总学分
FROM Student AS A
JOIN Grade AS B ON A.studentid = B.studentid
JOIN Course AS C ON B.courseid = C.courseid
WHERE B.grade >= 60
GROUP BY A.studentid, A.studentname;



2、使用储存函数
create or replace function getcredit()
returns table(vsid char,vsname VARCHAR,vscredit BIGINT)
AS $$
BEGIN
	RETURN QUERY
	SELECT A.studentid AS 学号, A.studentname AS 学生姓名, SUM(C.credit) AS 总学分
	FROM Student AS A
	JOIN Grade AS B ON A.studentid = B.studentid
	JOIN Course AS C ON B.courseid = C.courseid
	WHERE B.grade >= 60
	GROUP BY A.studentid, A.studentname;

END;
$$ 
LANGUAGE plpgsql;

select * from getcredit();

2、使用储存过程
//因为储存过程不能使用返回语句，故先前创建一个表
create table  tb1(vsid char,vsname VARCHAR,vscredit BIGINT);

create or replace Procedure getcredit()
as $$
BEGIN 
	INSERT into tb1(vsid char,vsname VARCHAR,vscredit BIGINT)
	SELECT A.studentid AS vsid, A.studentname AS vsname, SUM(C.credit) AS vscredit 
	FROM Student AS A
	JOIN Grade AS B ON A.studentid = B.studentid
	JOIN Course AS C ON B.courseid = C.courseid
	WHERE B.grade >= 60
	GROUP BY A.studentid, A.studentname;
END;
$$ LANGUAGE plpgsql;

CALL getcredit();//调用储存过程

3、使用游标编程
create or replace function getcredit1()
returns boolean as $$
DECLARE
	refcursor REFCURSOR;
	vsid VARCHAR;
	vsname VARCHAR;
	vscredit BIGINT;
BEGIN 
	open refcursor FOR 
	SELECT A.studentid AS 学号, A.studentname AS 学生姓名, SUM(C.credit) AS 总学分 
	FROM Student AS A
	JOIN Grade AS B ON A.studentid = B.studentid
	JOIN Course AS C ON B.courseid = C.courseid
	WHERE B.grade >= 60
	GROUP BY A.studentid, A.studentname;
	
	LOOP
		FETCH refcursor into vsid,vsname,vscredit;
		EXIT when NOT FOUND;
		RAISE NOTICE '% % %',vsid,vsname,vscredit;
	END LOOP;
	
	CLOSE refcursor;
	RAISE NOTICE '取数据结束';
	RETURN TURE;
	
	EXCEPTION 
      WHEN OTHERS THEN RAISE EXCEPTION 'error(%): %', SQLSTATE, SQLERRM;
END;

$$ LANGUAGE plpgsql;

SELECT getcredit1();

练习二（需熟练掌握并融汇贯通）

建立一个成绩视图stuscoreview，假设某用户希望在成绩视图上插入一行数据，为了保证数据完整性，需要检查插入成绩的学生和课程是否分别在Student表和Course中存在，如果不存在，则给出错误提示，如果存在，则将成绩插入成绩表Grade中。请用PL/pgsql给出实现上述功能的触发器。

--创建视图
create or replace VIEW stuscoreview as
SELECT A.studentid AS 学号, A.studentname AS 学生姓名, SUM(C.credit) AS 总学分 
	FROM Student AS A
	JOIN Grade AS B ON A.studentid = B.studentid
	JOIN Course AS C ON B.courseid = C.courseid
	GROUP BY A.studentid, A.studentname;


--创建触发器函数 check_insert
create or replace FUNCTION check_insert()
returns trigger as $$

DECLARE 
	colname BIGINT;//假设Grade表中的gradeid是serial结构递增
BEGIN
	select count(*) into colname
	from grade;
	
	colname:=colname+1;
	
	SELECT studentid 
	FROM student 
	WHERE studentid = NEW.studentid;
	IF FOUND THEN 
		SELECT courseid 
		FROM course 
		WHERE courseid = NEW.courseid;
	    IF FOUND THEN
 			INSERT INTO grade VALUES (colname, NEW.studentid, NEW.courseid,NEW.grade);
     		RETURN NEW;
     	ELSE
     	raise notice '课程号%不存在',NEW.courseid;
     	RETURN NULL;
     	END IF;
	ELSE
	raise notice'学号%不存在',new.studentid;
	return null;
	END IF;
END;
$$ LANGUAGE plpgsql;

--删除存在的触发器
drop trigger if exists insteadofinsert on stuscoreview;

--创建触发器
create trigger insteadfinsert
	INSTEAD OF INSERT ON stuscoreview;//instead of 触发器原理如下
	for each row
	execute function check_insert();
	
--插入数据
INSERT INTO stuscoreview VALUES ('s01', '王琳', '003', '高等数学', 90);
	
--查看数据
select * from stuscoreview；

在这里插入图片描述

1、触发器编程：

在如下雇员表薪水表empSalary中，每插入或更新一行数据时，触发函数程序将当前时间戳和用户名记录在该数据行中,并且检查该雇员的姓名以及薪水数据是否为空，若为空，则分别输出警示信息。

--创建触发器函数
create or replace function update_emp_salary()
return trigger as $$
begin
	NEW.timestamp:=NOW();
	NEW.username=current_user;
	
	IF NEW.employee_name=null 
	then
	raise warning'雇员姓名为空';
	END IF;
	
	IF NEW.employee_salary=null then
	raise WARNING '薪水数据为空';
	END IF;
	
	RETURN NEW;
END;
$$ LANGUAGE plpgsql;

creat tigger emp_salary_trigger
before insert or update on empSalary
FOR EACH ROW
execute function update_emp_salary();

2、存储过程or函数编程：

针对一个成绩管理系统，其数据库包括学生表Student(StudentId,SName,Major,CompletedCredits)，成绩表Grade(StudentId, CourseId,grade)，课程表Course(CourseId,Cname,credit)。采用PL/pgSQL语言，编写存储过程StudentCreditePro(StudentID）实现给定学号的学生完成学分统计，其值保存在Student表中，并通过查询Student表输出该生已获得学分。

CREATE OR REPLACE PROCEDURE StudentCreditePro(p_StudentID INT)
AS $$
DECLARE
    v_CompletedCredits INT;
BEGIN
    -- 初始化已完成学分为0
    v_CompletedCredits := 0;
    
    -- 计算学生已完成的学分
    SELECT SUM(c.credit)
    INTO v_CompletedCredits
    FROM Grade g
    JOIN Course as c ON g.CourseId = c.CourseId
    where g.StudentId = p_StudentID;
    
    -- 更新学生表中的已完成学分字段
    UPDATE Student
    SET CompletedCredits = v_CompletedCredits
    WHERE StudentId = p_StudentID;
    
    -- 输出信息
    RAISE NOTICE '学生 %s 已完成的学分为 %s', p_StudentID, v_CompletedCredits;
END;
$$ LANGUAGE plpgsql;

存储函数表达：

CREATE OR REPLACE FUNCTION StudentCreditePro(studentID INT)
RETURNS VOID AS $$
DECLARE
    totalCredits INT;
BEGIN
    -- 计算给定学生已完成的学分
    SELECT SUM(C.credit)
    INTO totalCredits
    FROM Grade G
    JOIN Course C ON G.CourseId = C.CourseId
    JOIN Student AS S ON G.StudentId = S.studentID;

    -- 更新 Student 表中的 CompletedCredits 字段
    UPDATE Student
    SET CompletedCredits = totalCredits
    WHERE StudentId = studentID;

    -- 提示完成
    RAISE NOTICE '学生 % 的完成学分已更新为 %', studentID, totalCredits;
END;
$$ LANGUAGE plpgsql;

在这里插入图片描述

3、如下工资表在进行数据访问时会出现哪些异常问题？如何逐步规范化设计该表，使其满足4NF范式？

在这里插入图片描述

一、该满足第一范式，即各个属性具有原子性。（如果说联系方式需要改动，因为联系方式可能对应邮箱，电话等各种形式）

二、由图可知，年月和工号为候选键的并集，即为主属性，而并未标注主键，且姓名、职称、月薪、所在部门、办公电话、工作地点非主属性对“年月”属性没有依赖，故不符合第二范式。

进行分解：

在这里插入图片描述

加粗样式

三、由图可知，并不满足第三范式，因为在基本信息表中，存在非主属性的传递依赖关系，工号->办公地点，办公地点->所在部门，办公地点->办公电话，故进行分解。

在这里插入图片描述

四、BCNF式，满足BCNF式，即即在3NF基础上，消除主属性对候选码的部分函数依赖和传递函数依赖，（A->B,B->A）这不算传递依赖

五、4nf式，并不存在多值依赖

CDM-LDM-PDM转换

CDM:下面引用Jerome’s BI BLOG里面的一句，这一句话很重要，也是理解概念模型与逻辑模型之间的区别的关键。

概念数据模型的内容包括重要的实体及实体之间的关系。在概念数据模型中不包括实体的属性，也不用定义实体的主键。这是概念数据模型和逻辑数据模型的主要区别。——Jerome’s BI BLOG

LDM:逻辑数据模型的内容包括所有的实体和关系，确定每个实体的属性，定义每个实体的主键，指定实体的外键，需要进行范式化处理。

在这里插入图片描述

PDM:物理数据模型是在逻辑数据模型的基础上，考虑各种具体的技术实现因素，进行数据库体系结构设计，真正实现数据在数据库中的存储。(即编写sql语句)

例题：针对以下图书销售系统CDM，如何进一步设计出合理的逻辑数据模型LDM、物理数据模型PDM？为提高查询性能，如何设计表索引？为满足业务规则，如何设计参照完整性约束规则？

在这里插入图片描述

时间关系，自行写吧

数据库编程

在这里插入图片描述

1.加载驱动
CLASS.forName("驱动名字");
//比如是postgresql驱动
Class.forName("org.postgresql.Driver");

2.建立连接
Sring URL="jdbc:postgresql://localhost:5432/testdb";
String username="myuser";
String passWord="sa";
//调用DriverManager.getConnection(String URL,String username,String passWord);
connection conn=DriverManger.getConnection(URL,username,passWord);

3.创建statement对象
Statement stmt=conn.creatStatement();

4.执行sql语句
//分为两种
1.executeQuery();-->sql中select
2.executeUpdate();-->sql中的update,delete,insert;

String sql="INSERT INTO public.student(sid,sname,gender,birthday,major,phone)"+"VALUES("2017001","李浩","男","20040601","计算机","180");
stmt.executeUpdate(sql);//执行该sql语句

4.获取result集，即显示输出
rs=stmt.executeUpdate(sql);
while(re.next()){
	String gender=rs.getString("name");//获取name列的内容
	Int age=rs.getInt("age");//获取age列的内容
}

5.关闭结果集合，关闭对象，关闭连接
rs.close();
stmt.close();
conn.close();

lulu_melon

关注

8
点赞
踩
18

收藏

觉得还不错? 一键收藏
打赏
0
评论
2024 电子科技大学数据库期末考试复习笔记

1NF：使属性具有原子性；2NF：消除非主属性对主键（主码）的部分依赖；就是说每个非主属性必须依赖于候选码集合（在数据库具体来说就是复合主键）3NF：消除非主属性对主建（主码）的传递依赖；BCNF：，任何主属性不能对候选码子集有依赖4NF：消除属性间的多值依赖；判定方法：对于任意关系中，如果存在两个元组（就是行），记为A,B，如果他们的某一属性X的值相等，那么我们交换它们另外的属性Y的值后，得到的新的两个元组，在表中是可以在原来的表中找到与它们相匹配的元组的。
复制链接

扫一扫