[Database] Final review summary

Chapter One

concept

  • Database definition
  • It is a collection of files that organize, store, and manage data according to a specific data model .
  • The difference between database and ordinary file
  • Support different applications to share access to data;
  • Complex data management;
  • Can be independent of the application;
  • Management is realized by DBMS.
  • Data model definition
  • Describe the composition of the data structure of the object , the semantic connection of the data , the abstract structure and description of the data constraint .
  • Main data model
  • Hierarchical data model
  • Mesh data model
  • Relational data model
  • Pros and cons of relational data model
  • advantage
  • Simple and flexible structure;
  • Support relation and set operations;
  • Support SQL;
  • Wide range of applications.
  • Disadvantage
  • Only support structured data storage;
  • The data type is simple;
  • Unable to support unstructured data.
  • DBMSbasic skills
  • create…
  • Adding, deleting, checking...
  • safely control
  • Backup and restore
  • Four management stages
  • Manual management stage
  • File system management stage
  • Database system management stage
  • System life cycle
  • demand analysis
  • System analysis
  • System implementation
  • System test
  • System operation and maintenance

Chapter two

concept

  • Entity definition
  • The abstract name in the conceptual model world of things that contain data features .
  • Relationship definition
  • A two-dimensional table with relational characteristics for storing entity data .
  • Key definition
  • The column that uniquely identifies the tuple .
  • Compound key definition
  • Multiple columns used to uniquely identify tuples in the relationship .
  • Candidate key definition
  • There may be multiple columns as keys in the relationship, and they are all candidate keys.
  • Primary key definition
  • The most representative candidate key in the relationship.
  • Primary key role
  • Uniquely identifies the tuple;
  • Establish a connection with the foreign key of the associated table;
  • Organize data storage;
  • Establish indexes for quick retrieval.
  • Surrogate key definition
  • DBMSThe automatically generated sequence of numbers is used as the primary key of the relational table .
  • Surrogate key role
  • Can replace the composite primary key to obtain higher performance data access operations.
  • Entity integrity rules
  • All primary attributes cannot take null values.

third chapter

operating

type of data

ObjectTypes of
characterCHAR, VARCHAR, TEXT
IntegerSMALLINT, INTEGER
Floating pointNUMBER(n,d), FLOAT(n,d)
dateDATE, DATETIME
currencyMONEY

Operation library

  • template
CREATE DATABASE <数据库名>;
ALTER DATABASE <数据库名> <修改内容>;
DROP DATABASE <数据库名>;

Operation table

CREATE TABLE <表名>
(
	...
	<属性名> <数据类型> [完整性约束],
	...
	CONSTRAINT <主键名> PRIMARY KEY(<属性名>),
	CONSTRAINT <外键名> FOREIGN KEY(<属性名>)
		REFERENCES <关联表名>(<属性名>)
);
ALTER TABLE <表名> <修改方式>;
DROP TABLE <表名>;
  • Integrity constraints
Key wordseffect
PRIMARY KEYPrimary key
NOT NULLNon-empty value
NULLNull value
UNIQUEUnique value
CHECKValidity check
DEFAULTDefault value

Operation index

CREATE INDEX <索引名> ON <表名>(<属性名>);
ALTER INDEX <索引名> <修改项>;
DROP INDEX <索引名>;

Insert operation

INSERT INTO <表名> VALUES (属性值);

Update operation

UPDATE <表名> SET <属性名>=<表达式>
	WHERE <条件表达式>;
DELETE FROM <表名>
	WHERE <条件表达式>;

Check for phrases

SELECT <属性名> INTO <新表名> FROM <表名>
WHERE <条件表达式>
	BETWEEN <属性值> AND <属性值>
WHERE <条件表达式>
	LIKE <属性值>
GROUP BY <属性名>
ORDER BY <属性名> [DESC|ASC]
  • Built-in function
Aggregate functionFeatures
AVG()average value
COUNT()Rows
MIN()Minimum
MAX()Max
SUM()sum
  • Classification statistics
SELECT Major AS 专业 COUNT StudentID AS 学生人数
FROM Student
WHERE StudentGender=‘男‘
GROUP BY Major
HAVING COUNT(*)> 2;
  • Subqueries
SELECT TeacherID,TeacherName,TeacherTitle
FROM Teacher
WHERE CollegeID IN
(
	SELECT CollegeID
	FROM College
	WHERE CollegeName='计算机学院'
);
  • Associated multi-table query
SELECT B.CollegeName AS 学院名称,A.TeacherID AS 编号,A.TeacherName AS 姓名,A.TeacherGender AS 性别,A.TeacherTitle AS 职称
FROM Teacher AS A,College AS B
WHERE A.CollegeID=B.CollegeID
ORDER BY B.CollegeName,A=TeacherID;
  • Connect query
SELECT B.CollegeName AS 学院名称,A.TeacherID AS 编号,A.TeacherName AS 姓名,A.TeacherGender AS 性别,A.TeacherTitle AS 职称
FROM TEACHER AS A JOIN COLLEGE AS B
ON A.CollegeID=B.CollegeID
ORDER BY B.CollegeName,A.TeacherID;
  • External connection
SELECT C.CourseName AS 课程名称, T.TeacherName AS 教师,
	COUNT(R.CoursePlanID) AS 选课人数
FROM COURSE AS C JOIN PLAN AS P ON C.CourseID=P.CourseID
	JOIN TEACHER AS T ON P.TeacherID=T.TeacherID
	JOIN REGISTER AS R ON P.CoursePlanID=R.CoursePlanID
GROUP BY C.CourseName, T.TeacherName;

Data control

GRANT <权限列表> ON <数据库对象> TO <用户|角色>;
REVOKE <权限列表> ON <数据库对象> FROM <用户|角色>;
DENY <权限列表> ON <数据库对象> TO <用户|角色>;

view

CREATE VIEW <视图名> AS
<SELECT查询>;

SELECT * FROM <视图名>;

DROP VIEW <视图名>;
  • example
CREATE VIEW BasicCourseView AS
SELECT CourseName,CourseCredit,CoursePeriod,TestMethod
FROM COURSE
WHERE CourseType='基础课';
SELECT *
FROM BasicCourseView
ORDER BY CourseName;
DROP VIEW BasicCourseView;

Chapter Four

concept

development process

  • Data needs analysis
  • Database design phase
  • Database realization stage
  • Database testing phase

Data model

  • Conceptual data modelCDM
  • From a user perspective
  • Logical data modelLDM
  • From a system analyst perspective
  • Physical data modelPDM
  • From the system designer's perspective

E-Rmodel

  • Entity-connection model
  • An effective method to describe the conceptual data model and logical data model of the real world.

Reasons for standardized database design

  • Reduce data redundancy
  • Data integrity and consistency
  • Efficient access to data by the system

Functional dependency

  • One attribute is determined, and the other attribute is also determined.

Fully functional dependence

  • Indicates the smallest attribute set among the determinants of functional dependence .
  • Otherwise, it is a partial functional dependency.

Attribute transitive dependency

  • If the attributes are not in a one-to-one correspondence, they can be transferred according to the dependencies between the attributes.

Multi-valued dependency

  • One-to -many relationship between attributes .

Normalization paradigm

  • The relationship table conforms to the pattern of the degree of normalization.
modedegree
First normal formAttributes cannot be subdivided
Second normal formEliminate some functional dependencies
Third normal formCut off transfer function dependency
BCNFDeterminants are all candidate keys
Fourth Normal FormEliminate multi-value dependencies

chapter Five

concept

ACIDcharacteristic

  • Atomicity
  • consistency
  • Isolation
  • Persistent

The transaction can not be achieved

  • Operations on the database
  • Log operation
  • Authority given

Reasons for concurrency control

  • Concurrency may bring problems such as data inconsistency and transaction deadlock .

Concurrency control problems to be solved

  • Lost update data
  • Non-repeatable reading/phantom reading
  • Dirty data read

Serializable scheduling

  • When the result of the data operation sequence in the transaction is the same as the result of the serial execution of the transaction, concurrent transaction scheduling can ensure the correctness and consistency of the operation.

Locked access

  • Exclusive lock
  • No other transactions are allowed to lock
  • Shared lock
  • Only allow other transactions to add read locks

Lock compatibility

Exclusive lockShared lockno lock
Exclusive locknonoYes
Shared locknoYesYes
no lockYesYesYes
Lock protocolExclusive lockShared lockUpdate lostDirty data readNon-repeatable
Level 1 Locking ProtocolFull plusdo not addXX
Level 2 Locking ProtocolFull plusRelease after readingX
Three-level locking protocolFull plusFull plus
The two-level and three-level locking protocols are based on the first level .
  • Two-phase locking agreement
  • The locking and unlocking of each transaction is divided into two stages .
  • Transaction isolation level
  • The higher the level, the smaller the possible data inconsistency and the lower the system throughput.
  • Security model
  • Authentication
  • Access control
  • System protection
  • Encrypted storage

operating

Affairs

START TRANSACTION;
<SQL>;
...
COMMIT;
START TRANSACTION;
<SQL>;
...
ROLLBACK;
START TRANSACTION;
<SQL>;
...
SAVEPOINT <保存点名>;
...
ROLLBACK <保存点名>;

user

CREATE USER "userA" WITH
	LOGIN
	NOSUPERUSER
	NOCREATEDB
	NOCREATEROLE
	INHERIT
	NOREPLICATION
	CONNECTION LIMIT -1
	RASSWORD '123456';

ALTER USER "userA"
	CONNECTION LIMIT 10;

DROP USER userA;

GRANT SELECT ON COURSE TO userA;

Character

CREATE ROLE "Role_Manager" WITH
	LOGIN
	CONNECTION LIMIT -1;

GRANT SELECT ON COURSE TO "Role_Manager";

Backup

BACKUP DATABASE CourseDB TO DISK="D:\CourseDB.bak";

RESTORE DATABASE CourseDB FROM DISK="D:\CourseDB.bak";

Chapter Six

concept

  • JDBCSteps to access the database
  • Application start
  • Import java.sqlpackage
  • Load and register the driver
  • Create an Connectionobject
  • Create an Statementobject
  • carried outSQL
  • Use the ResultSetobject to return the result
  • Close ResultSetobject
  • Close Statementobject
  • Close Connectionobject
  • the end
  • trigger
  • Can achieve complex data integrity .
  • It is a special business unit in itself .
  • By a INSERT, UPDATE, DELETEtriggered by passive execution.
  • Must be defined on the table or view .
  • Trigger special variables
  • NEW: The value is the row after the change.
  • OLD: The value is the row before the change.
  • TG_OP: Value is the operation that caused the trigger.

operating

JDBC

  • Load the driver
Class.forName("org.postgresql.Driver");
  • establish connection
String url = "jdbc:postgresql://localhost:5432/testdb";
String usrName = "admin";
String pasWord = "password";
connection conn = DriverManager.getConnection(url,usrName,pasWord);
  • createStatement
Statement stat = conn.createStatement();
  • Execute SQLstatement
String sql = "INSERT INTO STUDENT"
			+ " VALUES('201909','DD','男');";
stat.executeUpdate(sql);

String sql = "SELECT id,name,gender FROM company;"
ResultSet rs = stat.executeQuery(sql);
  • Print result
while(rs.next()){
	System.out.println(rs.getString("id"));
}
  • Close the connection
rs.close();
stat.close();
conn.close();

Stored procedure

  • create
CREATE OR REPLACE FUNCTION countRecords()
RETURNS INTEGER 
AS $COUNT$
DECLARE 
	count integer;
BEGIN
	SELECT COUNT(*) INTO count FROM STUDENT;
	RETURN count;
END;
$COUNT$	LANGUAGE plpgsql;
  • carried out
SELECT * FROM countRecords();
  • delete
DROP FUNCTION IF EXISTS countRecords();

PL/SQL syntax

  • condition
IF count>0 THEN
	count = count - 1;
ELSIF count<0 THEN
	count = count + 1;
ELSE
	EXIT;
END IF;
  • cycle
LOOP
	count=count+1;
	EXIT WHEN count>100;
END LOOP;

WHILE count>0 LOOP
	count=count-1;
END LOOP;

FOR i IN 1...10 LOOP
	RAISE NOTICE 'i=%',i;
END LOOP;

FOR rec IN SELECT id,name FROM STUDENT LOOP
	RAISE NOTICE "ID:%,NAME:%",rec.id,rec.name;
END LOOP;

trigger

  • Construct trigger function
CREATE OR REPLACE FUNCTION score_audit()
RETURNS TRIGGER
AS $SCORE$
	BEGIN
		IF (TG_OP = "DELETE")THEN
			INSERT INTO AUIT SELECT OLD.ID,OLD.SCORE;
			RETURN OLD;
		END IF;
		RETURN NULL;
	END;
$SCORE$ LANGUAGE plpgsql;
  • Operation trigger
CREATE TRIGGER AUDIT_TRIGGER
AFTER DELETE OR INSERT 
ON STUDENT
FOR EACH ROW EXECUTE PROCEDURE score_audit();

ALTER TRIGGER AUDIT_TRIGGER ON STUDENT RENAME TO AT;

DROP TRIGGER IF EXISTS AUDIT_TRIGGER ON STUDENT CASCADE;

cursor

  • definition
CURSOR_NAME REFCURSOR FOR SELECT * FROM STUDENT;

CURSOR_NAME REFCURSOR(KEY INTEGER) IS SELECT * FROM STUDENT WHERE SID=KEY;
  • turn on
OPEN CURSOR_NAME FOR QUERY;

OPEN CURSOR_NAME EXECUTE QUERY_STRING;

OPEN CURSOR_NAME;
The first two apply to unbound ones, and the latter one applies to bound ones.
  • Use cursor
FETCH CURSOR_NAME INTO ID,NAME;
IF FOUND THEN
	...
END IF;
  • Close cursor
CLOSE CURSOR_NAME;