2015.09.06 Database Notes

Chapter I.

Data Model three elements: data structure, manipulation, integrity constraints
Main data models: Hierarchical models, mesh models, relational models
Integrity constraints: Entity integrity, referential integrity, user-defined integrity

Database management system will have a certain structure of data to form a set, it has the following characteristics:
1. Data in a structured database is not disorganized and irrelevant, they have a certain organizational structure, and the data belonging to the same set has similar characteristics.
2. The sharing of data there is a large amount of duplication of information between departments in a unit. The purpose of using a database is to manage this information uniformly, reduce redundancy, and make each

Share the same data with the department.
3. The independence of data independence refers to the independence of data records and data management software. The data and its structure should be independent and should not change the application.
4. Completeness of data integrity means that the data in the database is guaranteed to be correct. There are many reasons why data may be incorrect, and the database management system checks the nature of the data

and manage them.
5. Flexibility of data database management system is not the simple accumulation of data, it has a lot of management functions on the basis of recording data information, such as input, output, query, edit and modify

such as
6. Data security According to the user's responsibility, different levels of people have different permissions to the database, the database management system should ensure the security of the data.

Basic classification of SQL commands:
1. Data Definition Language DDL
2. Data manipulation language DML
3. Data Query Language DQL
4. Data Control Language DCL
5. Data Management Commands
6. Transaction control commands

1.
CREATE Table/index/view
ALTER Table/index
DROP Table/index/view

2.
INSERT
Updata
DELETE

3.
SELECT

4.
ALTER PASSWORD
Grant (granted)
REVOKE
CREATE synonym (synonymous word)

5.
Start/stop AUDIT (Audit)

6.
COMMIT to save the database transaction
ROLLBACK fallback
SavePoint setting marker points for fallback
Set TRANSACTION Setting the transaction name

Field:
such as "ID" "Last Name" "Name"

Recording:
Row of data

Column:
All information for a specific field

Primary key:
For example, "ID"



chapter II Data definition

Decimal (P,s)//Decimal
such as decimal (4,2), the shape is 99.99

1.DATE
2.TIME
3.DATETIME
4.TIMESTAMP

CREATE TYPE Person as OBJECT
(NAME VARCHAR (30),
SSN VARCHAR (9));

CREATE TABLE Emp_pay
(EMPLOYEE person,
Slkary DECIMAL (10,2),
Hire_data DATE);


Domain integrity can be achieved by default constraints and check constraints as well as data types
Here is the details ~

Completeness includes
1. Entity integrity
A) data rows cannot be duplicated
2. Domain Integrity
A) a limit on the values entered into a particular column is implemented
3. Referential integrity
A) requires that the related item in the child table must exist in the primary table
b) If the relationship between the primary table and the child table is established, then:
I. The data of the related item in the child table must exist in the main table;
II. Data for the related item in the primary table changes, the corresponding data item of the child table should also be changed;
III. The primary table cannot be deleted until the child table is deleted;
4. Custom Integrity
In fact, SQL Server's process of creating tables is the process of stipulating the properties of data columns, as well as the assurance of enforcing data integrity, including entity integrity, referential integrity, and domain integrity. Creating a table includes selecting the field name, data type, whether the definition is empty, setting default values, primary key and foreign key relationships, checking constraints, and so on. There is no appropriate column in the table as the primary key, and you can create an identity column.

There are five kinds of constraints in SQL Server:
• The purpose of the constraint: to ensure that the data in the table is full-
• Common Types of constraints:
– PRIMARY KEY constraint (Primary key Constraint): Requires primary key column data to be unique and is not allowed to be empty
– Unique Constraint: Requires the column to be unique, allowed to be empty, but only one null value can occur.
– Check Constraint: A column value range limit, formatting restrictions, etc., such as age-related constraints
– Default Constraint: The default value of a column, such as our male students more, the gender default is "male"
– FOREIGN KEY constraint (Foreign key Constraint): For establishing a relationship between two tables, you need to specify the column that references the main table

CREATE DOMAIN Money_d as number (8.2);

ALTER DOMAIN Money_d
ADD constrant Money_con1
CHECK (VALUE > 5);

The scale of a numeric type is the number of bits in the decimal part, and the precision (precision) is the number of all data bits

Chapter III

Database objects:
tables, views, clusters, sequences, indexes, different names

Planning:
A collection of database objects associated with a user name, such as a database object created by that user

USER1. Employee_tbl

ID SERIAL//The ID is automatically incremented

INSERT into Test_increment (tesr_name)
VALUES (' FRED '), (' JOE '), (' MIKE '), (' TED ');

CREATE TABLE as SELECT//mysql
SELECT into//msqls

CREATE TABLE Products2 as SELECT * from Products1;
SELECT * into Products2 from products1;

drop table products1;

PRIMARY KEY constraint:

CREATE TABLE EMPLOYEE
(ID CHAR (9) Not NULL PRIMARY KEY,
NAME VARCHAR (+) not NULL unique,//Uniqueness Constraint
...
PAGER Interger (Ten) NULL);

PRIMARY KEY (ID));
ALTER TABLE EMPLOYEE ADD CONSTRAINT PK PRIMARY KEY (ID, NAME);


FOREIGN KEY constraints:

CREATE TABLE PAYMENT
(PID CHAR (9) is not NULL,
...
Constrant FK FOREIGN KEY (PID) REFERENCES EMPLOYEE (ID));
Parent table child table relationships, referential integrity
ALTER TABLE PAYMENT ADD constrant FK FOREIGN KEY (PID) REFERENCES EMPLOYEE (ID);

CHECK constraints:

...
CONSTRAINT CHK CHECK (ZIP = ' 10000 '));

ALTER TABLE EMPLOYEE DROP CONSTRAINT PK;


ALTER TABLE EMPLOYEE MODIFY middle_name VARCHAR (), not NULL

ALTER TABLE EMPLOYEE ADD COLUMN EMPID INT auto_increment;


Fourth Chapter

Normalized:
The process of decomposing raw brush data into tables to eliminate redundant data


First specification form:
Decomposition of raw data into a table

Second Specification form:
Extracts only partially dependent data from the primary key to another table

Third specification form:
Delete data from the table that does not depend on the primary key (for example, job description)


The fifth chapter data Operation

INSERT into Products VALUES (' 1123 ', ' leather ', 24,99);



chapter Sixth managing database Transactions

2015.09.06 Database Notes