Principles of SQL database

Database principles
The first chapter relationship model and relational algebra
1. Integrity constraints
(1) Entity integrity Rules (main code is not NULL)
(2) Referential integrity, used to describe the relationship between entities
(3) User-defined integrity

2. Relational algebra
(1) Traditional set operation
and (t belongs to R or s) T = R U S

Difference (t belongs to R does not belong to s) t = R–s

(t belongs to R and belongs to s) t=r N s

Cartesian product (number of T is r*s) T=rxs

3. Expressions

Connection
Same as the Cartesian product.

In addition (set of S in R)

Chapter II SQL language
1.sql language
(1) Data query Select
(2) Data definition create drop alter
(3) Data manipulation Insert Update Delete
(4) Data Control Grant revoke

Select data query:
Sentence (select from where group by have order by)
Eliminate duplicate elements distinct
The words in the Where
(comparison operations) >, =, <, <=, >=, <> (! =)
(logical +is NULL) and/or/not/is NULL
(set range +in) between and/in
(match) Like

Aggregate queries
function has sum count avg is used only within having
Where is to filter tuples, having to filter groups

Set operation
Union except intersect (SQL Server does not support)

Existence quantifier
Exists

Create drop ALTER data definition:
Create, Alter, DROP (database, table, view, index) Note: index no modification
Creation of a database
Create database xx
On
(name=xx
Filename= ' Path +xx.mdf '
Size=
Maxsize
filegroup=
)
Log on
(name=
Filename=
Size=
Maxsize=
Filegroup
)

Modifications to the database
Alter Database <databasename>
Modify file (name=
Size=
)

Deletion of the database
Drop Database <databasename>

Creation of tables
Create table XX
(x1 type NOT NULL,
X2 type NOT NULL,
Constraint primary Key Name primary KEY (x1)
Foreign key (x2) referenges table name (x3)
or constraint foreign key name foreign key (x2) referenges table name (x3)
)

Changes to the table
Alter table XX
ADD X3 Type
Drop X3
Alter X2 Type

Deletion of tables
Drop table XX

Establishment of indexes
Create index name on table name (x1,x2 desc)--first by X1 Ascending, in descending order by x2

Deletion of indexes
Drop Index Index name

Creation of views
Create View View Name
As
Select query section

Updates to Views
Update View Name
Set
Where

Drop VIEW name Cascade--associated will be deleted

SQL Data Update
Insert into table name (X1,X2,X3) values
(X1,X2,X3)

Delete from table name
Where

Update Table Name
Set
Where

Chapter III Theory and model refinement of relational data
1. Dependency of functions

To find the main code candidate code:

Judging whether it is a lossless decomposition

Paradigm Theory

The first paradigm of judgment
(1) Find the candidate code
(2)) judged by paradigm theory

BCFN:

Pattern Refinement

BCNF decomposition is not unique

Principles of SQL database