Learning database system concepts, design and application experience

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.