Database System Overview

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 P4Permanent 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 2Single 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.

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

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<Table Name> [ ADD<New column Name> <data type> [integrity constraints] [ DROP<Integrity Constraint Name>] [ ALTER COLUMN<Column Name> <data type>];
DROP TABLE<Table Name> [ RESTRICT | CASCADE];

3. index creation and deletion P89-P90 CREATE [UNIQUE] [CLUSTER] INDEX<Index Name> ON<Table Name> (<column Name> [<sequence>] [, <column Name> [<sequence>]…); Unique Index UNIQUE, Non-unique index or clustered Index CLUSTER DROP INDEX<Index Name>;
4, Data Query P91-P114 unique DISTINCT

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

([DISTINCT | ALL] *)

COUNT

([DISTINCT | ALL] <column Name>)

SUM

([DISTINCT | ALL] <column Name>)

AVG

([DISTINCT | ALL] <column Name>)

MAX

([DISTINCT | ALL] <column Name>)

MIN

([DISTINCT | ALL] <column Name>)

Group by clause grouping & HAVING phrase
LEFT OUT JOIN XXX ON
5. Data Update P115-P118

INSERT INTO<Table Name> [(<attribute column 1> [, <attribute Column 2>…)] VALUES(<Constant 1> [, <constant 2>]… ) // Or subquery
UPDATE<Table Name> SET<Column name >=< expression> [, <column name >=< expression>]… [ WHERE<Condition>];
  DELETE  FROM<Table Name> [ WHERE<Condition>];

6, view P118-126 CREATE VIEW<View Name> [(<column Name> [, <column Name>]…)] AS<Subquery> -- subquery cannot contain the order by clause and DISTINCT phrase [ WITH CHECK OPTION];
DROP VIEW<View Name>; Chapter 4 and Chapter 51, authorization and recycling P137-P140

GRANTStatement Syntax: GRANT <permission> [, <permission>]... -- all priviliges [ON <Object type><Object Name>] TO <user> [, <user>]... -- PUBLIC [with grant option]; -- allow another user to grant this permission REVOKEStatement format: REVOKE <permission> [, <permission>]... [ON <Object type><Object Name>] FROM <user> [, <user>]...; -- CASCADE

A role is a set of permissions.

Role creationCreate role <ROLE name>
II, Authorize a roleGRANT <permission> [, <permission>]… ON <Object type>Object Name TO <role> [, <role>]…
III, Assign a role to another role or userGRANT <role 1> [, <role 2>]… TO <role 3> [, <user 1>]… [With admin option]
IV, Revoke role PermissionsREVOKE <permission> [, <permission>]… ON <object type> <Object Name> FROM <role> [, <role>]…

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 <Integrity CONSTRAINT Condition Name> [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 6
1, function dependency and Code definition P172-P174

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.

, As follows: X
Some function dependencies, Which is written as X-passing function dependency.Direct function dependencyInstead of passing function dependencies.

If all the 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)

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 the relational mode R <U, F>, all function dependencies in the F logic 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 more information about algorithms, see Chapter 7.1. 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

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::

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

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 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

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.