Notes on the introduction of databases

Objective

1. Relationship models require relationships to be normalized, the most basic of which is that each component of a relationship (a tuple) must be an attribute in a data item that cannot be divided.

2. The pattern responds to the structure of the data and its linkage, and the instance responds to the state of the database at a certain point in time.

3. The database's level three model refers to the database system is composed of external mode, mode and internal mode. The database management system provides a two-layer image between these three-level modes: External mode/mode image and Mode/internal mode image. It is these two layers of images that ensure that the data in the database system can have high logical independence and physical independence.

4. The pattern is actually a logical view of the database data, with only one schema for the database. The DBMS provides schema description language (schema DDL) to strictly define patterns.

Three-level schema structure for the body database

A database can have multiple out-of-the-way schemas that can be used by multiple application systems of a single user, but only one external mode can be used by an application.

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

2. When the mode changes, there is a database administrator external mode/mode image to make corresponding changes to achieve the external mode without changing the purpose.

The application is written according to the external schema of the data so that the application does not have to be modified, thus ensuring the logical independence of the data and the program. Applications generally do not need to be modified unless the application requirements themselves change.

relational database

Relationship is a finite subset of Cartesian product

1. There can be three types of relationships:

Base table: A table that actually exists, which is a logical representation of the actual stored data.

Query table: Query results corresponding to the white OH

View chart: A table derived from a base table or other view chart, which is a virtual table that does not correspond to the actual stored data

2. Relationship pattern is type, relationship is value

The description of the relationship is called the relational pattern, and he can formally represent R (u,d,dom,f).

where R is the relationship name, U is the collection of attributes that make up the relationship, D is the domain from which the attribute in the attribute group U is derived, the DOM is a collection of attributes to the domain, and F is a collection of dependencies between the attributes.

3. Relational operations

4. Completeness of the relationship

1. Entity integrity and relationship integrity are the integrity constraints that a relational model must meet.

Entity integrity rules: If attribute A is the primary attribute (candidate code) of the basic relationship R, a cannot take a null value.

Referential integrity rules: If the attribute f is the outer code of the relationship's basic relationship R, it corresponds to the main code K of the basic relationship s, then the value on F for each tuple in R must be:

Or the value is empty, or equal to the primary code value of a tuple in s

where R and S can be the same relationship, such as the monitor is outside the code.

5. Relational algebra

In the traditional set operation, the two-mesh operation, namely, the difference, the intersection and the Cartesian product four kinds, regards the relation as a set of tuples, and its operation is from the angle of the horizontal direction of the relationship, and the special relational operation includes the selection, projection, connection and division, not only the row but also the column involved. Comparison operators >,<,=,<> and logical operators are used to aid specialized relational operations.

1. Equivalent connection: It is the tuple that chooses the value of a and B attributes from the generalized Cartesian product of the relational Ryus.

2. The natural connection is a special equivalence connection, and he requires that the components of the comparison in the two relationships be the same attribute group, and that the duplicate attribute columns are removed from the result. The general join operation is from the point of view of the row, but natural joins also need to cancel repeating columns, so natural joins are operations from the point of view of rows and columns.

3. Outer joins: The discarded tuple pages are saved in the result relationship, and the values are blank in other attributes. If the left relationship R to discard the tuple is called LEFT JOIN, if the right side of the tuple reservation is called the right outer join.

4. Except operations

Given the relationship R (x, y) and S (y,z), in which x, Y, Z are attribute groups, Y in R and Y in S can have different property names, but must originate from the same set of domains. With the addition of R and S, a new relationship p (x) is obtained, and P is the projection on the X attribute column of the tuple that satisfies the relationship in R: the yx of tuple x on X contains the set of S projected on Y.

Example

1. Check the name of the student who has enrolled in all courses.

Select sname from student where isn't exists (SELECT * from course where no exists (SELECT * from SC where sno=student.sno an D cno=course.cno)); There is no universal quantifier in--sql, but we can always convert predicates with full-name quantifiers to equivalent predicates with existential quantifiers.

2. The enquiry has enrolled at least 10094030011 of the students ' number of all courses. You can use implicit logic operations.

SELECT DISTINCT Sno from SC Scx where isn't exists (SELECT * from SC scy where scy.sno= ' 100940311 ' and NOT EXISTS (SELECT * FR Om SC scz where Scz.sno=scx.sno and Scz.cno=scy.cno));

Delete mode

Drop Schema < schema name > <cascade|restrict>--cascade cascade and restrict both must be selected.

Defining views

The RDBMS execution CREATE VIEW statement simply stores the definition of the view in a data dictionary and does not execute the SELECT statement in it. Only when the view is queried, the data is isolated from the base table as defined by the views. For queries that are non-column subset views, it is not necessarily possible to convert. Because the aggregation function cannot be used as a conditional expression in the WHERE clause.

Grant and REVOKE

The GRATE and REVOKE statements grant or reclaim permissions on the operation of the data to the user. Authorization of the database schema is implemented by the DBA when the user is created.

Database roles

A database role is a named set of permissions that are related to database operations, a collection of permissions, and the ability to use roles to manage database permissions to simplify the process of authorization.

Mac forced access control

Only if the subject's license level is greater than or equal to the object's classification, the subject can read the corresponding object, and the subject can write the corresponding object only when the license level of the subject equals the object's classification.

Note:

1. The subject is the active entity in the system, that is, the actual user who is managed, including the individual processes on behalf of the user; The object is the passive entity in the system and is subject to the action, including files, basic tables, indexes, and views.

2.mac is a classification of the database itself, no matter how the data is copied, the tag and data is an integral whole, only the users who meet the requirements of the classification can manipulate the data.

3. Security security at a higher level provides all protection at a lower level, so the DAC is implemented first when implementing the Mac, the system first checks the DAC, and the system automatically checks for the MAC for the database objects that are checked by the DAC. Only database objects that are checked by Mac can be accessed.

Audit Audit

The audit statement is used to set the auditing function, and 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, the audit switch must be opened. The system parameter Audit_rail is set to true before the audit information can be viewed in the system table Sys_audittrail.

Note:

Security measures should cost people who are trying to undermine security far more than they would have benefited.

Trigger Trigger

Trigger is a class of event-driven special procedures that user-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, triggering action occurs only once, if the trigger of the table is a row-level, the trigger action will be used for all rows.

In the case of row-level triggers, the user can then use new and old in the process to refer to update, the new value after the Insert event, and the previous value before the update, delete event. If you are a statement-level trigger, you cannot use new and old for reference.

2. The triggering action body can be an anonymous PL/SQL block, or it can be a call to a stored procedure that has been created.

3. When multiple triggers of the same table are activated, the following order of execution is followed.

1) Execute the Before trigger on the table

2) activating the SQL statement on the trigger

3) Execute the AFTER trigger on the table.

Standardization

Set R (U) is the relationship pattern on the attribute set U. X, Y is a subset of U. If an attribute value of two tuples on X cannot exist in any one possible relationship with R (U), and the value of the property on Y is unequal, then the X function is called R,r y, or the Y function is dependent on X.

1. Full function dependencies

In R (U), if Y is dependent on y, and for any true subset X ' of x, there is a Y function that does not depend on X '. It is called Y-to-X complete function dependency.

2. Paradigm

1). A low-level paradigm-based relationship model can be converted to the activation of a number of high-first-class paradigm-by-model relationships. This process is called normalization.

2). Relationship, as a two-dimensional table, has a minimum requirement for him: Each component must be an irreducible data item. The relationship model satisfying this condition belongs to the first paradigm.

3. If the relationship R belongs to the first paradigm, and each non-principal attribute full function relies on, that is, to eliminate part of the function dependency, to achieve the second paradigm.

4). If this is not true in the relational schema r<u,f>, the attribute group Y and the non-number main attribute Z (Z does not belong to Y), so that the Y function depends on x, and the Z function relies on y, and X does not depend on Y. It is said that the relationship pattern belongs to the third paradigm. In the case of a third paradigm, any of its non-key properties are not passed dependent on any candidate key.

Process

The concept of a process is in Split, the task is the smallest unit of request resources, and thread is the smallest unit of dispatch and operation. A task can have multiple thread, which share all the resources of a task and work together to accomplish a task.

1. The comparison between the clue mechanism and the process mechanism

1) Threads consume less resources than processes.

2 lead scheduling is more flexible.

3) The thread switching overhead is small: the thread shares the same address space, and the process's address space is private.

4) Easy lead communication

Summarize

1. The integrity constraints of a relationship include three main categories: entity integrity, referential integrity, and user-defined integrity.

2. The difference between a stored procedure and a function

A stored procedure is a collection of user-defined sets of SQL statements that design a task for a particular table or other object that a user can call a stored procedure;

A function is usually a method defined by a database that takes parameters and returns a value of some type and does not involve a particular user table.