A data dictionary or system directory: A system database that describes the data information in a database and is a data or metadata data.
Tuple-> row or record property-> the number of column or field tuples-> the number of cardinality attributes-> degrees
A collection key-> a unique identifier for a-> table field-> A valid value or an atomic value
The value of each attribute in each tuple is atomic, meaning that each value is taken from the field of the property and that there are no multiple or duplicate values allowed. A key: A collection of attributes or properties that uniquely identifies a tuple-related key in a relationship: a collection of one or more relationship attributes, most of which limit the number of attributes with minimal relationship keys, and do not contain any unnecessary attributes
Uniqueness does not have redundancy: when you remove a property from the property collection, the remaining property is no longer unique. Validity: No attribute value in the key is NULL candidate key: When there are multiple attributes or attribute groups that can be uniquely identified Fu: is a key that is selected from the relationship to uniquely identify the tuple
Foreign key: A candidate key in a relationship that matches some other relationship (or possibly the same relationship) for one or more attribute collections.
Relational Algebra
Operation of Set theory
name |
symbols |
keyboard Form |
And |
U |
UNION |
Make |
N |
INTERSECT |
Poor |
- |
Minus |
Product |
X |
Times |
-Specialized relational operations
name |
symbols |
keyboard Form |
Projection |
R[] |
R[] |
Choose |
R Where Condition |
R Where Condition |
Connection |
|
Join |
Except |
÷ |
Divide by |
Cartesian set Operation
is also a cross product operation that connects each tuple (row) from the first named table (relationship) with each tuple in the second table. The Cartesian set multiplies two relationships to define a third relationship, and the third relationship contains all possible tuple pairs in both relationships. Therefore, if a relationship has K tuples and M attributes, and the other relationship has L tuples and N attributes, the Cartesian product produces a relationship that has (n+m) attributes and (k*l) tuples.
SQL data Type
Data Type |
Description |
CHAR (n) or character (n) |
Fixed-length strings |
VARCHAR (n) |
Variable-length string with a maximum length of n |
Integer or int |
Integer |
Float (p) |
Floating-point numbers with a precision equal to or greater than P |
|
Variable-length string with a maximum length of n |
Real |
Single-precision floating-point numbers |
Date |
Date, with YYYY-MM-DD |
Time |
Time, with HH:MM:SS |
SQL operator
operator |
Description |
Arithmetic operators |
|
+,- |
Unary operator, representing positive, negative |
* |
Multiplication |
/ |
|
+ |
|
- |
|
Comparison operators |
$1600 |
= |
Equals |
。 =,<> |
Not equal to |
< |
Less than |
> |
Greater than |
= |
Greater than or equal to |
<= |
Less than or equal to |
In |
equals any one member |
Not in |
Not equal to any one member |
Is NULL |
To detect null values |
is not NULL |
To detect Non-null values |
Like |
When the first expression matches the pattern of the second expression, it returns True |
All |
Compare to all values in the list |
Any,some |
Compare to each in the list |
EXISTS |
If the subquery returns at least one row is true |
BETWEEN x and Y |
=x and <=y |
logical operators |
|
and |
|
OR |
|
Not |
|
Collection operators |
|
UNION |
Returns the row after removing duplicate rows from two queries |
UNION All |
Return from two queries so line |
INTERSECT |
Returns all rows selected by two queries |
Minus |
Returns rows that belong to the first query and not the second query |
Aggregation operator |
|
Avg |
Average |
Min |
Minimum value |
Max |
Maximum Value |
Sum |
Total |
Count |
Count |
SQL Database Definition language (DDL) CREATE TABLE CREATE VIEW CREATE index ALTER TABLE drop TABLE drop View DROP Index
SQL data Query Language (DQL)
SELECT [all| DISTINCT] Column-name
From Table-name
[WHERE Condition expression
GROUP by Column-name
Having condition expression
ORDER BY Column-name]
SQL Data Manipulation Language (DML)
Insert command
INSERT into table-name[(Attribute-value)]
VALUES (lists of values for row 1,
Lists of values for row 2,
.
.
.
Lists of values for row N);
Delete command
DELETE from Table-name
WHERE condition Expression
Delete from table1 just clears all data
Drop table1 Deletes the table, including the structure and data of the table
UPDATE command
UPDATE Table-name
SET target-value-list
WHERE ondition Expression
SQL Data Control Language (DCL)
GRANT
The DBA uses this command to authorize.
GRANT Privileges
On Table-name/view-name
To User-id
Authorized users can authorize this permission to other users.
REVOKE
DBA uses this command to reclaim user permissions
REVOKE Privileges
On Table-name/view-name
From User-id
Affairs
A logical unit of work for database processing, including one or more database access operations.
Granularity of Locks : Database-level, table-level, page-level, row-level, attribute-level
The simplest lock mode is two types of locks: s shared lock (or read lock) and X exclusive lock (write lock).
If:
The transaction t has an S lock on the granularity G and will allow the request of the S lock issued by the transaction W. In other words, read-read is an exchangeable transaction T has an S lock on the granularity G and will reject the request of the X lock issued by transaction W. In other words, read-write is an irreducible transaction T has an X lock on the granularity G and will reject any requests made by transaction W. In other words, writing is not interchangeable.
Classification of Locks two- yuan lock
There are two main states of a lock: a lock (or ' 1 ' state), unlocked (or ' 0 ' state). If a database table, page, tuple (row), or property (field) object is locked by a transaction, no other transaction can be accessed. The problem is: If two transactions are read to the table, also can not access, can not concurrency, inefficient. Shared/Exclusive (or read/write) locks
Read lock is also known as a shared lock because other transactions are allowed to read this data item. Write-plus locks are also known as exclusive locks because only one transaction is allowed to hold locks on data items.
Autonomous access Control
Account-level permission assignment: At the account level, the DBA specifies specific permissions for each account that are independent of the database relationship (or table). Create SCHEMA permission: Creating a Schema Creation Table permission: Creating a Table CREATE VIEW permission: Creating views Alter permissions: Adding or removing properties of a Relationship drop permission: Delete a relationship or view Modify permissions: Delete, insert, or update a tuple SELECT Permissions: Retrieving information from a database using a SELECT query statement
Relationship (or table)-level permission assignment: DBA Controls access to relationships or views in the database. Select permission on r: query or read Modify permission on tuple R in R: modifying (Update,insert and delete) tuples in R
References permission on
R: Referential relationship R
Eg:
GRANT {all|privilege-list}
on {table-name[(column-comma-list)]|view-name[( column-comma-list)]}
to {public|user-list}
[with GRANT OPTION]
All: Specifies that all the action permissions for the specified object are assigned to the specified user
Privilege-li ST: Permission list
on: Indicates to which object permissions, which can be tables or views
Column-comma-list: Constraints The permissions of the specified column. If not specified, the permissions will overwrite the entire table and view
to: Used to specify the authorized user
Public: Grants permissions to all users with valid user IDs and passwords on the system
User-list: Authorized Users list
with GRANT OPTION: Describes an authorized user who can grant specified permissions to another user
The example of a retraction:
Eg:
REVOKE {all|privilege-list}
on {table-name[( column-comma-list)]|view-name[(column-comma-list)]}
from {public|user-list}
Cascade Authorization, and the retract permission is also cascading retract.