What is a domain restriction in DBMS

Write a custom catalog title here

First SQL language (1)

Portal: SQL Language (1)
Content preview:

  • SQL data definition
  • Basic data type
  • Basic mode definition
  • Basic query structure
  • Nested subquery
  • Database change

Two, SQL language (two)

Portal: SQL language (two)
Content preview:

Three, transaction (transaction)

Consists of a sequence of query and update instructions. The SQL standard dictates that when executing an SQL statementA transaction started implicitly. One of the following SQL statements ends a transaction:

  • Commit work: Apply the current transaction, ie the updates made by the transaction are retained in the database. After the transaction is committed, a new transaction is automatically started.
  • Rollback work: Roll back the current transaction, that is, cancel all SQL statements in the transaction to update the database. This reverts the database to the state it was in before the first statement in the transaction was executed.

Motivation example: Transfer 100 yuan from account A-101 to A-201

  • If one update is successful and the other update fails, this leads to data inconsistencies in the database
  • Therefore, these two updates either all succeed or all fail

Four characteristics of matters

  • Atomic
  • consistency
  • isolation
  • Durability (durability)

Atomic

  • A transaction either freezes its actions after all steps have been completed or rolls back its many actions without having successfully completed all of the actions

By default in many SQL implementationsEach SQL statement is a transaction in itself and is sent as soon as it is executed

  • If a company wants toExecute multiple SQL statements,onThe automatic transmission of individual SQL statements must be deactivatedHow automatic submission is disabled also depends on the specific SQL implementation.

A better choice is to allow multiple SQL statements to be included in the begin atomic ... end keywords as part of the SQL: 1999 standard

Fourth, integrity constraints

Integrity constraints ensure that changes made to the database by authorized users do not destroy data consistency

Examples of integrity constraints are:

  • The teacher's name cannot be null
  • No two teachers can have the same teacher ID
  • Each department name in the course relationship must have a corresponding department name in the department relationship
  • A department's budget must be greater than $ 0.00

Integrity constraints include:

  • Domain Integrity
  • Entity Integrity (Primary Key Constraints)
  • Referential Integrity (Foreign Key Constraints)
  • Custom integrity constraints

The constraint is the databaseexample(Instance) must be followed.
The integrity constraints are managed by the DBMS.

1. Limitations on a single relationship

  • not null
  • unique
  • check ()
  • Example:

2. Domain restrictions

Domain restriction is integrity restrictionThe most basic formCan be used to check the validity of the data inserted into the database

New fields can be created from existing data types

Review clauseCan also be applied to the domain

  • Example: The check clause can ensure that the teacher salary fieldJust allowA value that is greater than a certain value

The Annual Salary field has a restriction to ensure that the annual salary is at least $ 29,000.00

Constraint Salary_value_Test clauseIf optional, it will be usedName the constraint salary_value_test. The system uses this name to indicate which restriction an update violates.

As another example

  • usein clauseCan restrict a domainContain only the specified set of values

3. Referential Integrity

Definition of the referential integrity constraint

  • Let the attribute sets of the relationships r1 and r2 be R1 and R2, and the main codes are K1 and K2, respectively
  • If for a tuple t2 in r2 there is a requirement that there be a tuple t1 in r1 such that t1 [K1] = t2 [α], we call it a subset of R2
  • Refers to the foreign key of K1 in relation r1 (foreign key)
  • Referential integrity constraints are also known as subset dependencies. These can be written as follows:

    Review: Database Principles of the Database System The outer code in (nine, key)

Database change

Changing the database leads to the destruction of the referential integrity. Here is a list of tests that should be performed on different types of database changes in order to maintain the following referential integrity constraints:

  • insert: If you insert a tuple t2 in r2, the system must ensure that there is a tuple t1 in r1 such that t1 [K] = t2 [α]. which is
  • Clear: If the tuple t1 is deleted from r1, the system has to calculate the set of tuples in r2 that relates to t1. which is

    If the record is not empty, either the delete command reports an error and is aborted, or the tuple that refers to t1 must be deleted (can lead to a cascading deletion).
  • To update: Two types of updates need to be considered: update the reference relationship r2 and update the referenced relationship r1
    When the tuple t2 is updated in the relation r2 and the value in the outer code α is updated and modified, a test similar to the insertion case is performed. T2 ’denotes the new value of tuple t2, then the system must ensure

    When the tuple t1 in the relationship r1 is updated and the update changes the value of the main code K, a test similar to the deletion case is carried out. The system must use the old t1 value (the value before the update) for the calculation

    If the collection is not empty, the update will fail or the cascading update will be performed in a manner similar to deletion

Referential Integrity in SQL

Main codeCandidate codeWithOuter codeCan be specified in the SQL statement create create table

  • primary keyThe clause has a number of componentsMain codeAttribute of
  • uniqueThe clause has a number of componentsCandidate codeAttribute of
  • foreign keyThe clause has a number of componentsOuter codeThe attributes and relationship name pointed to by the changed foreign code

By defaultOuter codereferenceReferenced relationshipMain code

You can use the following short definitionOne column as a foreign code

Attributes in the referenced relationshipCan be specified explicitly, but must be declared asMain codeorCandidate code

The name can be different

Let's give a few examples (if you don't understand something, you need to look back at the previous one and then move on when you understand it).

  • Relationship Name: Classroom
    Primary key: building, room number
  • Relationship Name: Department
    Restriction: budget> 0
    Primary key: Dept_name
  • Relationship Name: Course
    Restriction: credits> 0
    Primary key: course_id
    Foreign code: dept_name (see relationship department)
  • Relationship Name: Instructor
    Restriction: salary> 29,000
    Primary key: ID
    Foreign code: dept_name (see relationship department)

Cascading actions in SQL

Because of thewhen deleting the cascade clauseIf the deletion of the tuple in the department leads to the referential integrity constraint being violated, the deletion is not rejected by the system, but a "cascading" deletion of the course relationship.The tuple of the deleted line is deleted

"Cascading" updates are similar

If there is a foreign code dependency chain that spans multiple relationships, the delete or update done at one end of the chain can be carried across the chain

However, if a constraint violation caused by a cascade update or delete cannot be resolved by another cascade operation, the system terminates the transaction

  • That means, all changes and cascading actions of the company will be undone

Referential IntegrityJust check at the end of the transaction

  • Intermediate steps can destroy referential integrity as long as the subsequent steps fix this damage
  • Otherwise it is impossible to set up certain database states, e.g. B. the insertion of two tuples whose foreign keys refer to each other
    - For example, the spouse attribute of the married person relationship
    marriedperson (name, address, spouse)

Further options in addition to cascade operation:

  • on delete set null
  • on delete set default

Zero value for foreign key attributeTo complicate the semantics of SQL referential integrity, it is best not to use null to prevent this from happening

  • When a foreign key attribute is null, the tuple by definition meets the referential constraint

Fifth assertion (assertion)

claim(Assertion) is a predicate (complex test condition) that expresses a condition that the database must always meet

The form of the assertion in SQL is as follows

After creating an assurance, the system checks its legitimacy andEveryoneDetection of database updates that can corrupt the claim

  • This detection creates a lot of overhead, so be careful when using assertions

Since SQL doesn't provide the "for all X, P (X)" structure, we canOn a detourExpression:not exists X such that not P (X)

  • Example 1. For each tuple in the student relationship, the value for the tot_cred attributeMust be the sameThe total of the credits for the successfully completed courses
  • Example 2: Each teacher cannot teach in two different classrooms at the same time in the same semester

Six, trigger (trigger)

Triggers are statements that are automatically executed by the system and are caused by database update operations