Database System Overview

By the way, this is the database system overview published by the Higher Education Press. Chapter 1 knowledge point database is a collection of large amounts of data that can be shared and organized in a computer that has been stored for a long time. 1. database data features P4 permanent storage, organized, and shared. 2. Data independence and how to ensure P10 and P34 logical independence: Use

By the way, this is the database system overview published by the Higher Education Press. Chapter 1 knowledge point database is a collection of large amounts of data that can be shared and organized in a computer that has been stored for a long time. 1. database data features P4 permanent storage, organized, and shared. 2. Data independence and how to ensure P10 and P34 logical independence: Use

By the way, this is the database system overview published by the Higher Education Press.


Chapter 1 knowledge points
A database is a collection of large amounts of data that can be shared and organized in a computer that has been stored for a long time.
1. database data features P4 Permanent storage, organized, and shared.
2. Data independence and how to ensure P10 and P34

Logical independence: The logical structure of your applications and databases is independent of each other. ( Internal ModeGuaranteed) Physical independence: User applications interact with data stored in the database on the disk ( External ModeGuaranteed)

3. Elements of the data model P13 Data structure, data operations, and integrity constraints.
4. Use ergraphs to represent the conceptual model P17
Entities, contacts, and attributes. The contact itself is also a kind of solid shape, and can also have attributes.


Chapter 2
1, relationship related concepts (such as relationship, candidate code, Master attribute, non-master attribute) P42-P44 Single Data Structure ---- Link. Real-world entities and various relationships between entities are expressed by relationships. DomainIs a set of values of the same data type.

If the value of an attribute group in a link uniquely identifies a tuples, the attribute group is Candidate codeAll attribute groups in the link mode are candidate codes of the link mode. Full codeIf a link has multiple candidate codes, select one of them Master codeThe attributes of candidate codes are called Primary attributeThe attribute that is not included in any candidate code is called Non-Primary attribute

2. Relational algebra operator P52

In the generalized Cartesian Product R × S, a natural link selects the cognominal attribute that meets the equality condition, and then performs projection to remove duplicate attributes of the same name to form a new relationship. Given the relationship r (R) and s (S), S? R, then r & pide; s is the largest relation t (R-S) Meet t x s? R 3, relational Algebra Expression


Chapter 3

Operational elephant	Operation Method
	Create	Delete	Repair and Modification
Mode	CREATE SCHEMA	DROP SCHEMA
Table	CREATE TABLE	DROP TABLE	ALTER TABLE
View	CREATE VIEW	DROP VIEW
Suo Yin	CREATE INDEX	DROP INDEX

1, SQL features P79-P80

1. integrated and unified 2. Highly procedural 3. Set-oriented operations 4. Multiple usage modes with the same syntax structure 5. simple and easy to learn

2. Define, delete, and modify P84-P87 for the basic table

PRIMARY KEY PRIMARY KEY(Sno, Cno) UNIQUE FOREIGN KEY(Cpno) REFERENCESCourse (Cno)
ALTER TABLE <表名> [ ADD <新列名> <数据类型> [Integrity constraints] [ DROP <完整性约束名> ] [ ALTER COLUMN <列名> <数据类型> ];
DROP TABLE <表名> [ RESTRICT | CASCADE];

3. index creation and deletion P89-P90 CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> ( <列名> [ <次序> ] [, <列名> [ <次序> ]…); Unique Index UNIQUE, Non-unique index or clustered Index CLUSTER DROP INDEX <索引名> ;
4, Data Query P91-P114 unique DISTINCT

Confirm range Between and, NOT BETWEEN ANDDetermine set IN, NOT INCharacter matching LIKE, NOT LIKENull Value Is null, IS NOT NULLMultiple conditions (logical operations) AND, OR, NOT

ORDERAscending clause: ASC; Descending order: DESC; The default value is ascending.
Aggregate functions:

Count

COUNT

([DISTINCT | ALL] *)

COUNT

([DISTINCT | ALL] <列名> )

Sum

SUM

([DISTINCT | ALL] <列名> )

Calculate the average value

AVG

([DISTINCT | ALL] <列名> )

Maximum and minimum values

MAX

([DISTINCT | ALL] <列名> )

MIN

([DISTINCT | ALL] <列名> )

Group by clause grouping & HAVING phrases-specific usage in the instructor courseware chapter 3rd standard relational database language SQL (2)
5. Data Update P115-P118

INSERT INTO <表名> [( <属性列1> [, <属性列2> …)] VALUES( <常量1> [, <常量2> ]… ) // Or subquery
UPDATE <表名> SET <列名> = <表达式> [, <列名> = <表达式> ]… [ WHERE <条件> ];
DELETE FROM <表名> [ WHERE <条件> ];

6, view P118-126 CREATE VIEW <视图名> [( <列名> [, <列名> ]…)] AS <子查询> -- Subqueries cannot contain the order by clause and DISTINCT phrase [ WITH CHECK OPTION];
DROP VIEW <视图名> ;


Chapter 4 and Chapter 51, authorization and recycling P137-P140

GRANTGeneral statement format: GRANT <权限> [, <权限> ]... -- All priviliges [ON <对象类型> <对象名> ] <用户> [, <用户> ]... -- PUBLIC [with grant option]; -- allow another user to GRANT this permission. REVOKEStatement format: REVOKE <权限> [, <权限> ]... [ON <对象类型> <对象名> ] FROM <用户> [, <用户> ]...; -- CASCADE

2. The database role P142-P143 role is a set of Permissions

I, Role creationCREATE ROLE <角色名>
II, Authorize a roleGRANT <权限> [, <权限> ]… ON <对象类型>Object Name <角色> [, <角色> ]…
III, Assign a role to another role or userGRANT <角色1> [, <角色2> ]… TO <角色3> [, <用户1> ]… [With admin option]
IV, Revoke role PermissionsREVOKE <权限> [, <权限> ]… ON <对象类型> <对象名> FROM <角色> [, <角色> ]…

3. Three types of database integrity and Its Implementation P152-P158
Entity integrityUse primary key in CREATE TABLE
Integrity of referenceIn create table, use the foreign key phrase to define which columns are external codes. Use the REFERENCES phrase to specify which tables the external codes refer.
User-Defined integrityWhen creating TABLE, the column value is defined as non-NULL (not null). The UNIQUE column value (UNIQUE) checks whether the column value meets a Boolean expression (CHECK)
CONSTRAINT Constraints <完整性约束条件名> [Primary key phrase | foreign key phrase | CHECK Phrase]
You can use the alter table statement to modify the integrity constraints of a TABLE. You can first Delete the original constraints, and then add the new constraint alter table Student drop constraint C1; alter table Student add constraint C1 CHECK (Sno BETWEEN 900000 AND 999999)

Chapter 6The relational mode is a quintuple: R (U, D, DOM, F)
1, function dependency and Code definition P172-P174

Data Dependency: the constraint between an internal attribute and an attribute. The most important data dependency: function dependency and multi-value dependency Function dependency: The value of an attribute or a group of attributes can determine the value of other attributes.
Function dependency: Dependencies between ordinary functions and non-ordinary functionsIn the relational mode R (U), for the subset X and Y of U, If X → Y, but Y does not belong to X, X → Y is a non-trivial function dependency. If X → Y, but Y belongs to X, X → Y is an ordinary function dependency.

In R (U), if X → Y has x' Y for any real subset of X, then Y is called X Full function dependency, As XY.
If X → Y, but Y is not fully dependent on X, Y is called X Some function dependencies, As XY.
In R (U), if X → Y, (YX), YX, Y → Z Pass function dependency. Add the condition Y → X, because if Y → X, then X then → Y, actually, yes Direct function dependencyInstead of passing function dependencies.

2, 1NF, 2NF, 3NF P175-P176 if all attributes of a relational mode R are Not scoreIn 1NFThe first paradigm is the minimum requirement for the relational model. Non-primary attributes fully function depends on code, R 2NF.Each non-master attribute does not depend on the code or Do not pass code-dependent, R 3NF

3. function-dependent systems (Armstrong justice system P183, closure P184, minimum coverage of P186)

Armstrong justice system A1. Self-inverse Law(Reflexivity): If Y belongs to XU, X → Y is contained in F. Belongs to A2. Augmented Law(Augmentation): If X → Y is contained in F and Z belongs to U, XZ → YZ is contained in F. A3. Transfer Law(Transitivity): If X → Y and Y → Z are contained in F, X → Z is contained in F. (Export rules): Merge rules: from X → Y, X → Z, with X → YZ. (A2, A3) pseudo transfer rules: from X → Y, WY → Z, with XW → Z. (A2, A3) decomposition rules: X → Y and Z belong to Y, with X → Z. (A1, A3)
In relational mode R All the functional dependencies of the logic contained in F are called F's Closure, As F +. Set F to A set of function dependencies on the property set U. X belongs to U. XF ++ = {A | X → A can be exported by F according to the Armstrong principle }, XF + is called the closure of attribute set X on function dependency set F.
F + = G + is a sufficient condition that F belongs to G + and G belongs to F + Minimum dependency set(1) F contains only one attribute on the right of any function dependency. (2) F does not depend on X → A, which makes F equivalent to F-{X →. (3) F does not depend on X → A. X has A real subset Z, which makes F-{X → A} else {Z → A} equivalent to F.

4. Pattern decomposition (pattern decomposition criterion and definition P189-P190, pattern decomposition method P191) pattern decomposition should be maintained Lossless connectivity and Function Dependence. For the algorithm, see the teaching material...


Chapter 71. Basic Steps for database design P201

Requirement Analysis Conceptual Structure Design Logical Structure Design Physical Structure Design Database implementation Database Operation and Maintenance

2, conceptual structure design methods and steps P209-P211

Four Methods for Conceptual Structure Design Top-downFirst, define the framework of the global conceptual structure, and then gradually refine Bottom-upFirst, define the conceptual structures of each local application, and then integrate them to obtain the global conceptual structure. Gradual expansionFirst, define the most important core concept structure, then expand outward, and gradually generate other Conceptual Structures in snowball mode until the overall conceptual structure Hybrid PolicyCombines top-down and bottom-up to design a global conceptual structure framework with a top-down policy, use it as the skeleton to integrate the local conceptual structures designed by the bottom-up policy.

3, E-R graph to the relational model of the conversion of P224-P225 properties can no longer have the need to describe the nature. Attributes cannot be associated with other entities. This Conversion generally follows the following principles::

Transform a real shape to a relational model. Convert a m: n contact to a relational mode. A 1: n link can be converted into an independent link mode, or merged with the link mode corresponding to n. A ⒋ 1:1 can be converted to an independent link mode, or merged with the link mode corresponding to any end. A Multivariate relationship between three or more entities is converted into a relational model. The association between entities in the same entity set, that is, the self-Association, can also be handled in the above, 1: n and m: n conditions. Links with the same code can be merged.

4. Physical Design (P228-P230 for the choice of relational mode access method, database access structure P230-P231)

1, Index MethodB + tree index method query condition, clustering function, Join Operation 2, Cluster MethodConnection operation, equal comparison, high repetition rate 3, HASH MethodPredictable size. DBMS provides dynamic access

Determine the physical structure of the database

⒈ Determine the data storage structure; design the data access path; Determine the data storage location( Storage space usage Maintenance Cost) Begin Confirm System Configuration

Chapter 2A Transaction is a sequence of user-defined database operations. These operations are either performed in full or not, which is an inseparable unit of work. transactions are the basic unit of recovery and concurrency control;
1. Features of transactions P279 ACIDFeatures: Atomicity (Consistency) Isolation (Isolation) Continuity)
2. problems caused by improper concurrency control P294 1) Loss of Modification(Lost Update) 2) Read "dirty data"(Dirty read) 3) Non-repeated read(Non-repeatable Read)
3, blockout P295-296

Blocking has three links: the first one is Apply for LockThe second step is Get lockThe third step is Release lock. There are two basic blocking types: Exclusive lock(Exclusive Locks, short X lock) And Shared lock(Share Locks, short S lock).
Solve the live lock problemFirst-Come service is adopted. Solution to deadlock: (1) prevent the occurrence of deadlocks; (2) allow deadlocks, and then use certain means to regularly diagnose whether there are deadlocks in the system. If there are deadlocks, release them. Deadlock Prevention Methods: one lock method, sequential lock method deadlock diagnosis: timeout method, transaction wait Method

4. serializable P299-P230 for concurrent Scheduling Definition: The concurrent execution of multiple transactions is correct, and the results are the same only when the results are serially executed in a certain order.
Conflict operationIt refers to the read/write operations of different transactions on the same data. Conflicting operations of different transactions cannot be exchanged. Two operations of the same transaction cannot be exchanged.Conflict serializable scheduling is a sufficient and not necessary condition for serializable scheduling.
5, two-segment lock protocol P301-P302 two-segment lock protocol: refers to all transactions must Two PhasesLock and unlock data items. The first stage is Get blocked, Also known as scalability. The second stage is Release Block, Also known as the contraction stage.
Transaction compliance with the two-segment lock protocol is a sufficient condition for serializable scheduling, rather than a necessary condition.