SQL is required. Take notes. Chapter 2 describes advanced SQL features. SQL is required.

SQL is required. Take notes. Chapter 2 describes advanced SQL features. SQL is required.
22.1 Constraints

To correctly design a relational database, you need to ensure that only valid data is inserted in the table. For example, if the Orders table stores order information and the OrderItems table stores order details, ensure that any order IDs referenced in Orderitems exist in Orders. Similarly, any user referenced in the Orders table must exist in the MERs table.
Although the check can be performed when a new row is inserted, it is best not to do so for the following reasons:
(1) If database integrity rules are implemented at the client level, each client is forced to implement these rules, but it is likely that some clients will not implement these rules.
(2) These rules must also be implemented during UPDATE and DELETE operations.
(3) It is very time-consuming to execute client-side checks, and DBMS will execute these checks relatively efficiently.
Constraints (constraint) Manage the rules for how to insert or process database data.
DBMS applies constraints on database tables to implement reference integrity.Most constraints are defined in Table definitions.

22.1.1 primary key

A primary key is a special constraint that ensures that values in a column (or a group of columns) are unique and never changed. In other words, the values of one or more columns in a table uniquely identify rows in the table.
Any column in the table can be used as a primary key if the following conditions are met:
(1) The primary key values of any two rows are different.
(2) Each row has a primary key value (that is, the column does not allow NULL values ).
(3) columns containing primary key values are not modified or updated.
(4) primary key values cannot be reused. If a row is deleted from the table, its primary key value is not assigned to the new row.
One way to define a primary key is to create it, as shown below:

CREATE TABLE Vendors{     vend_id        CHAR(10)        NOT NULL PRIMARY KEY,     vend_name      CHAR(50)        NOT NULL,     vend_address   CHAR(50)        NULL,     vend_city      CHAR(50)        NULL,     vend_state     CHAR(5)         NULL,     vend_zip       CHAR(10)        NULL,     vend_country   CHAR(50)        NULL};

Change to primary key

ALTER TABLE VendorsADD CONSTRAINT PRIMARY KEY(vend_id);

22.1.2 foreign key

A foreign key is a column in a table and its values must be listed in the primary key of another table.Foreign keys are an important component to ensure the integrity of the reference.
For example, the Orders table contains one row for each order entered into the system. Customer information is stored in the MERs table. Orders in the Orders table are associated with specific rows in the MERs table through the customer ID. The customer ID is the primary key of the MERs table; each customer has a unique ID. The order number is the primary key of the Orders table. Each order has a unique order number.
A method for defining foreign keys

CREATE TABLE Orders{     order_num      INTEGER        NOT NULL PRIMARY KEY,     order_date     DATETIME       NOT NULL,     cust_id        CHAR(10)       NOT NULL REFERENCES Customers(cust_id)};

You can also use the CONSTRAINT syntax in the alter table statement.

ALTER TABLE CustomersADD CONSTRAINTFOREIGN KEY (cust_id) REFERENCES Customers (cust_id);

Foreign keys can help prevent accidental deletion: in addition to helping ensure the integrity of the reference, foreign keys also play an important role. After a foreign key is defined, DBMS cannot delete rows with associated rows in another table.

22.1.3 unique constraint

The unique constraint is used to ensure that the data in a column is unique. They are similar to primary keys, but there are several important differences:
(1) A table can contain multiple unique constraints, but each table only allows one primary key.
(2) The unique constraint column can contain NULL values.
(3) The unique constraint column can be modified or updated.
(4) The value of the unique constraint column can be reused.
(5) Unlike primary keys, the unique constraint cannot be used to define foreign keys.
The syntax of the unique constraint is similar to that of other constraints.The unique constraint can be defined either by using the UNIQUE keyword in the table definition or by using a separate CONSTRAINT.

22.1.4 check Constraints

Check constraints are used to ensure that the data in a column meets the specified conditions. The purpose of the plugin for checking constraints is:
(1) Check the minimum or maximum values.
(2) specify the range.
(3) only specific values are allowed.
Constraints are given during definition:

CREATE TABLE OrderItems{     order_num     INTEGER     NOT NULL,     order_item    INTEGER     NOT NULL,     prod_id       CHAR(10)    NOT NULL,     quantity      INTEGER     NOT NULL CHECK(quantity > 0),     item_price    MONEY       NOT NULL};

To check that columns named gender only contain M or F, you can write the following altet table statement:

ADD CONSTRAINT CHECK (gender LIKE '[MF]');

22.2 Index

Indexes are used to sort data to speed up search and sorting.The best way to understand the index is to imagine the index after a book.
You can define an index on one or more columns so that the DBMS can save a sorted list of its content. After an index is defined, DBMS uses it in a similar way as using a book index. DBMS searches for sorted indexes, finds matching locations, and then retrieves these rows.
Before creating an index, remember the preceding content:
(1) indexes improve the search performance, but reduce the performance of data insertion, modification, and deletion. When performing these operations, the DBMS must dynamically update the index.
(2) index data may occupy a large amount of storage space.
(3) Not all data is suitable for indexing. Data with poor uniqueness has more benefits from indexing than data with more value options.
(4) indexes are used for data filtering and data sorting. If you sort data in a specific order, the data may be an alternative to the index.
(5) You can define multiple columns in the index. This index is only useful when sorting in the order of States and cities. This index is useless if you want to sort by city.
CREATE an INDEX using the create index statement. The following statement creates a simple index in the product name column of the Products table:

CREATE INDEX prod_name_indON PRODUCTS(prod_name);

The index must be unique.The INDEX name is defined after the keyword create index. ON is used to specify the table to be indexed, and the columns in the index are given in parentheses after the table name.
Check indexes: the efficiency of indexes varies with the increase or change of table data. Many database administrators find that an ideal index created in the past may not be ideal after several months of data processing. It is best to check the index and adjust the index as needed.

22.3 trigger

A trigger is a special stored procedure that is automatically executed when a specific database activity occurs.Triggers can be associated with INSERT, UPDATE, and DELETE operations on specific tables.
Unlike stored procedures, triggers are associated with a single table. The trigger associated with the INSERT operation on the Orders table is only executed when the row is inserted in the Orders table. Similarly, the triggers for INSERT and UPDATE operations on the MERs table are only executed when these operations appear on the table.
The code in the trigger has the following data access permissions:
(1) All new data in the INSERT operation;
(2) All new and old data in the UPDATE operation;
(3) data deleted in the DELETE operation.
Some common functions of triggers:
(1) ensure data consistency.
(2) execute activities on other tables based on changes to a table.
(3) perform additional verification and roll back the data as needed.
(4) Calculate the calculated column value or update timestamp.
Create a trigger that converts the cust_state column in the MERs table to uppercase for all INSERT and UPDATE operations.

CREATE TRIGGER customer_stateAFTER INSERT OR UPDATEFOR EACH ROWBEGINUPDATE CustomersSET cust_state = Upper(cust_state)WHERE Customers.cust_id = :OLD.cust_idEND;

Constraints are faster than triggers:In general, constraints are processed faster than triggers, so when possible, use constraints as much as possible.

22.4 database security

Most DBMS provides administrators with a management mechanism that grants or restricts access to data.
The foundation of any security system is user authorization and Identity confirmation. This is a kind of processing. Through this kind of processing, the user is confirmed to ensure that the user is authorized to execute the operations he is trying to perform. Some DBMS use the operating system security measures for this purpose, while some maintain their own user and password lists, and some use external directory service servers in combination.
Actions to be protected:
(1) access to the database management function.
(2) access to a specific database or table.
(3) Access type.
(4) access tables only through views or stored procedures.
(5) create multi-level security measures to allow multiple login-based access and control measures.
(6) restrict the ability to manage user accounts.
Security is managed by SQL GRANT and REVOKE statements. However, most DBMS provide interactive management applications that use GRANT and REVOKE statements internally.