Notes on Database Overview

Preface

1. the relational model requires that the relationship must be normalized. the most basic of these normative conditions is that each component (tuples) of the relationship must be an attribute of an unidentifiable data item.

2. The mode reflects the data structure and connection, while the instance reflects the status of the database at a certain time point.

3. The three-level mode of the database refers to the external mode, mode, and internal mode of the database system. The database management system provides two layers of images between these three modes: external mode/mode image and mode/internal mode image. These two layers of images ensure that the data in the database system has high logical and physical independence.

4. The mode is actually the logical view of database data. A database has only one mode. Dbms provides a mode Description Language (mode ddl) to strictly define the mode.

Three-level structure of the text Database

A database can have multiple external modes. The same mode can be used by multiple application systems of a user, but one application can only use one external mode.

 

1. A mode can have any external mode. For each external mode, the database system has an external mode/mode image.

2. When the mode changes, the database administrator changes the external mode/mode image so that the external mode does not need to be changed.

The application is written based on the external data mode, so that the application does not need to be modified. This ensures the logical independence between the data and the program. Unless the application requirements change, the application generally does not need to be modified.

 

Relational Database

A link is a finite subset of cartesian products.

1. There are three types of links:

Basic table: the actually existing table, which is the logical representation of the actually stored data.

Query table: the query result is displayed in white.

Visual chart: A table exported from a basic table or other visual charts. It is a virtual table and does not correspond to the actually stored data.

2. The link mode is a type, and the link is a value.

The description of a link is called a relational model. It can be formally represented as R (U, D, DOM, F ).

Here, R is the link name, U is the property set of the link, D is the property in the property group U from the obtained domain, DOM is the image set of the property to the domain; F is the dependency set of data between attributes.

3. Link Operations

4. link integrity 

1. entity integrity and link integrity are integrity constraints that must be met by the link model.

Entity integrity rules: If attribute A is the primary attribute (Candidate Code) of the basic relationship R, A cannot be null.

Reference integrity rules: If attribute F is an external code of relational basic relation R, it corresponds to the primary key of relational s, the value of each tuples in R on F must be:

Or the value is null or equal to the primary code value of a tuples in s.

The R and S can be in the same relationship, for example, the shift leader is an external code.

5. Relational algebra

The traditional set operation involves two-object operations, namely, sum, difference, intersection, and Cartesian product. The relationship is treated as a set of tuples, the operation is performed from the horizontal direction of the link, that is, the row. The special relational operation includes selection, projection, join, and Division operations, which not only involve rows but also columns. Comparison operators>, <, =, <> and logical operators are used to assist in specialized relational operations.

1. equijoin: it selects the tuples whose values of A and B are equal from the generalized Descartes of RYuS.

2. A natural connection is a special equivalent connection. It requires that the components for comparison in the two relations must be the same attribute group and duplicate attribute columns should be removed from the results. Generally, the join operation is performed from the perspective of rows, but the duplicate columns need to be removed from the natural connection. Therefore, the natural connection is performed from the perspective of rows and columns.

3. Outer Join: Save the discarded tuples In the result link, and fill in the blank value on other attributes. If the left link R is retained, the left link is called; if the right link is retained, the right outer link is called.

4. division operation

Given the relationship R (x, y) and s (y, z), where x, y, z is the attribute group, Y in R and Y in S can have different attribute names, but it must come from the same domain set. The division operation between R and S generates a new relationship p (x). P is the projection of the tuples in R that meet the following relationship on the x attribute column: X indicates the image set Yx of x, which is the value of X on x. It contains the set of S projection on Y.

Example"

1. query the names of students who have selected all courses.

Select sname from student where not exists (select * from course where no exists (select * from SC where sno = student. sno and cno = course. cno); -- SQL does not contain full-name quantifiers. However, we can always convert predicates with full-name quantifiers into equivalent predicates with existing quantifiers.

2. query the number of all courses selected for at least 10094030011 of students. Logical operations can be used.

Select distinct sno from SC scx where not exists (select * from SC scy where scy. sno = '200' and not exists (select * from SC scz where scz. sno = scx. sno and scz. cno = scy. cno ));

Delete Mode

Drop schema <mode Name> <cascade | restrict> -- either cascade or restrict is required.

Define View

Rdbms only saves the view definition to the data dictionary and does not execute the select statement. Only when querying a view can data be found from the basic table according to the view definition. The query of a non-row-column subset view cannot be converted. In the where clause, clustering functions cannot be used as conditional expressions.

Grant and revoke

The grate and revoke statements grant or revoke data operation permissions to users. Database mode authorization is implemented by dba when creating a user.

Database role

A database role is a group of named permissions related to database operations. A role is a set of permissions. Using a role to manage database permissions can simplify the authorization process.

Mac Mandatory Access Control

Only when the subject's license level is greater than or equal to the object's confidentiality level can the subject read the corresponding object; only when the subject's license level is equal to the object's confidentiality level, only the subject can write the corresponding object.

Note:

1. the subject is the activity entity in the system, that is, the actual user that is not unfamiliar with the management, and the page contains various processes that represent the user. The object is the passive entity in the system and is operated by the subject, including files, basic tables, indexes, and views.

2. mac indicates the confidentiality of the database. No matter how the data is copied, the mark and data are an inseparable whole. Only users who meet the confidentiality requirements can operate the data.

3. the security protection provided by the higher security level should include all the lower-level protection. Therefore, dac must be implemented first when mac is implemented, and the system checks the dac first, the system automatically performs mac checks on the database objects that can be accessed through the dac check. Only database objects that pass the mac check can be accessed.

 

Audit

The audit statement is used to set the audit function. The noaudit statement cancels the audit function.

Audit alter, update on table name;

Audit settings and audit content are generally stored in the data dictionary. You must turn on the audit switch. That is, you can set the system parameter audit_rail to true to view the audit information in the system table sys_audittrail.

Note:

Security measures should make those who attempt to undermine security spend far more than what they get.

Trigger

Trigger is a special event-driven process defined on a relational table.

1. Create a trigger

Create trigger <trigger Name>

{Before | after} <trigger event> on <Table Name>

Foreach {row | statement}

[When <trigger event>]

<Trigger action body>

2. delete a trigger

Drop trigger <trigger Name> on <Table Name>

Note:

1. foreach statement is a statement-level trigger that only occurs once. If the table trigger is Row-level, the trigger action will apply to all rows.

If a row-Level Trigger is used, you can use new and old to reference the new values after the update and insert events and the old values before the update and delete events. If it is a statement-Level Trigger, new and old cannot be referenced.

2. The trigger action body can be an anonymous PL/SQL block or a call to the created stored procedure.

3. The following execution sequence is followed when multiple triggers of the same table are activated.

1) execute the before trigger on the table.

2) activate the SQL statement on the trigger

3) execute the after trigger on the table.

 

Normalization

Set R (U) to the relational mode on the property set U. X and Y are the subsets of U. If any possible relationship between R (U) and R is R, the attribute values of two tuples on X cannot exist in R, but those on Y cannot be the same, the x function is called to define y, or the y function depends on x.

1. Full function dependency

In R (U), if the y function depends on y and any real subset of x is X', y function does not depend on x '. y is called the full function dependency on x.

2. Paradigm

1 ). The Relationship Model of a lower-level paradigm can be converted into the activation of the Relationship patterns of several higher-level paradigms through mode decomposition. This process is called standardization.

2 ). As a two‑dimensional table, each component must be an inseparable data item. The relational model that satisfies this condition belongs to the first paradigm.

3. If the relational R belongs to the first paradigm and every non-primary attribute fully depends on the function, that is, some function dependencies are eliminated to reach the second paradigm.

4 ). If such Y does not exist in the relational mode R <U, F>, the attribute group Y and the non-count Primary attribute Z (Z does not belong to Y) Make the y function dependent on X, the Z function depends on Y, and X does not depend on Y .. The relational model belongs to the third paradigm. If the third paradigm is used, none of its non-key attributes depend on any candidate key.

Process

The concept of a process is divided into two parts. A task is the smallest unit for applying for resources, and a thread is the smallest unit for scheduling and running. A task can have multiple threads, which share all the resources of the task and complete a task together.

1. Comparison between clue mechanism and process mechanism

1) clues consume less resources than processes.

2. Flexible clue scheduling ..

3) the overhead of thread switching is small: the thread shares the same address space, while the address space of the process is private.

4) convenient clue Communication

Cursor

The system provides a data buffer pool for users to store SQL statement execution results. Each cursor zone has a name.

PL/SQL (procedural language/SQL)

Pl/SQL is a process language used to compile database stored procedures. It is an extension of SQL and adds procedural statements.

1. pl/SQL Overview

The basic structure of pl/SQL programs is blocks, each of which completes a logical operation.

 

Note:

1. pl/SQL blocks can be named blocks or anonymous blocks. The anonymous block must be compiled every time it is executed. It cannot be stored in the database or referenced in other pl/SQL blocks.

2. stored procedures and functions are named quickly. They are compiled and stored in the database. They can be called repeatedly and run quickly.

Pl/SQL Creation

1. creste procedure process name ([parameter 1, parameter 2 ,...])

As

<Pl/SQL block>

2. alter procedure process name 1 rename to process name 2;

Run pl/SQL

Call/perform procedure process name ([parameter list ]);

Delete pl/SQL

Drop procedure Link name ();

Note:

 

1. l during optimization, when there are both select operations and link operations, select operations first, so that the tuples participating in the connection can be greatly reduced. Algebra Optimization

2. Select full table scan and index scan for the operation algorithm. You should select index scan.

3. The index join cost page for table join is small. This is physical optimization.

 

Summary

1. Constraints on link integrity include entity integrity, referential integrity, and user-defined integrity.

2. Differences between stored procedures and functions

Stored procedures are a collection of user-defined SQL statements. You can call stored procedures to design tasks for specific tables or other objects;

A function is usually a database-defined method. It accepts parameters and returns some type of values without involving specific user tables.