Oracle NOTE 2-database design

Design of the database

The process of software development:
Project--Requirements analysis--design, detailed design----implementation---maintenance
[including database design]

Through demand analysis, we can extract the "entity classes" in the key business, these entity classes are
To encapsulate the data, the data is ultimately stored in the database.

Basic process of database design:
Find the appropriate entity classes based on your business needs, and then map these entity classes into databases
Object (table), taking into account the attributes of the entity, which is the column of the table, and finally
Relationships, which are the relationships between tables.

Common Tools for Database design:
1.sybase Company, PowerDesigner, powerful, charge
2.erwin-studio, more powerful, charge
3.JUDE, developed by Japan Open Source, free

E-r diagram (entity-relationship), Entity Relationship Mapping

The core of It is mapping (mapping):
1. Entity name, map to table name
2. Attribute, map to column name
3. Object identifiers, mapped to primary KEY constraints
4. Entity relationships, mapped to relationships between tables (foreign KEY constraints)

Mapping Examples:
Entity class table
Name User table name: Tbl_user
Property ID PRIMARY key: ID
Property Name Column Name: Name
Attribute password column name: password

Relationships between entity classes:
One-to-one mapping into foreign keys
One-to-many mappings into foreign keys
Many-to-many split into 2 one-to-many, then mapped into foreign keys
Self-correlating mappings into foreign keys

Attention:
1. Foreign keys generally appear on the side of the table, and the mappings have directionality.
2. There is no foreign key concept in an entity class, and the concept of a foreign key exists in a database table.
-----------------------------------------------------------

The three main paradigms of database, namely the principle of database design:

Role: is used to guide the database designers a design idea, to ensure the design of the database
Is the scalability, no data redundancy, the structure is clear and simple and straightforward.

1NF: atomicity, which means that any column in the table is unique and can no longer be split
Such as:
R (Id,name,age), the name in this relationship can be divided into first_name and last_name,
Therefore, this design does not conform to 1NF and should be redesigned to:
R (Id,first_name,last_name,age)

2NF: On the basis of 1NF, there is no non-critical column part dependent on the key column, that is to say
All non-critical columns must be fully dependent on the key columns.
Such as:
R (Sno,sname,cname,score)
1 Zhang Three Mathematics 90
Because none of the above columns is a critical column, there are other non-critical column dependencies
In the critical column, so this design does not conform to 2NF and should be redesigned to:
R1 (sno,sname), a table for student information
R2 (cno,cname), a table for course information
R3 (Sno,cno,score), intermediate table for student and course exam results

3NF: On the basis of 2NF, there is no non-critical column transfer function dependent on the key column, i.e.
That all non-critical columns must be directly dependent on the key column
Such as:
If a relies on b,b to rely on C, we say that a transfer function relies on C

Information sheet for students to enter a university:
R (sno,sname,uno,uname,phone,address)
If you use SNO as the key column, Sanme,uno is directly dependent on Sno, but Uname,phone,
Address these three columns are directly dependent on UNO, indirectly dependent on SNO, so this design does not conform to the
3NF, should be redesigned to:
R1 (Sno,sname,uno), a table for student information
R2 (uno,uname,phone,address), a table for university information
------------------------------------------------------------

DDL (Create,drop,alter,rename to)

There are 2 steps to creating a table:
1. Defining columns and data types
2. Adding constraints

Constraint (constraint)
Effect: Used to further qualify the data in the table relative to the data type, making it possible to add to
The data in the table is legally valid, meets the business requirements of the data, and does not appear invalid data.

5 Kinds of constraint types in Oracle:
1.primary KEYPKP meaning: PRIMARY KEY constraint, non-null unique (only one primary KEY constraint in the table)
2.not NULLNNC Meaning: non-empty
3.uniqueUKU Meaning: Unique
4.checkCKC Meaning: Custom constraints
5.foreign keyfkr meaning: FOREIGN key constraint (indicates that the value of this column is a primary key referenced from another table)

Note: A column that can be referenced by a foreign key must itself be a PRIMARY key constraint or a uniqueness constraint.

The first way to create a table:
Grammar
CREATE TABLE table_name (
Column name data type [default defaults] [constraint],
Column name data type [default defaults] [constraint],
...
Column name data type [default defaults] [constraint]
);

Name of the constraint:
When you create a table, if you do not define the name of the constraint, the database provides a default name for the constraint.
and store this content in a data dictionary

--Query all constraint names under the current user
Select Constraint_name from User_constraints;

To add a constraint name syntax:
Constraint constraint name constraint type;

Naming rules for constraint names:
Table_name_ Column Name _ constraint type abbreviation

For example:
--Create a user table
CREATE TABLE Tbl_user (
ID number (5) Constraint TBL_USER_ID_PK primary key,
Username VARCHAR2 (+) constraint Tbl_user_uname_uk unique NOT NULL,
Password VARCHAR2 (+) Default ' 000000 '
);

2 Ways to add constraints:
1. Column-level syntax add--Edge definition column, EDGE add constraint

--Create Account table
CREATE TABLE Tbl_account (
ID number (5) Constraint TBL_ACC_ID_PK primary key,
Accountno varchar2 (+) constraint Tbl_acc_accno_uk unique NOT NULL,
Realname VARCHAR2 (25),
Password char (6) Default ' 000000 ',
Balance number (10,2) constraint tbl_acc_bal_ck check (balance>=0)
);

2. Table-level syntax additions--Define columns first, then add constraints
CREATE TABLE Tbl_account (
IDNumber (5),
ACCOUNTNOVARCHAR2 (+) constraint tbl_acc_ano_nn NOT NULL,
REALNAMEVARCHAR2 (25),
PasswordChar (6) Default ' 000000 ',
Balancenumber (10,2),
Constraint TBL_ACC_ID_PK primary key (ID),
Constraint Tbl_acc_ano_uk Unique (ACCOUNTNO),
Constraint tbl_acc_bal_ck Check (balance>=0)
);

Note: The NOT NULL constraint does not support table-level syntax and can only be added with column-level syntax!!!
Of course, you can also use the following methods to work around the implementation
CREATE TABLE Tbl_account (
IDNumber (5),
ACCOUNTNOVARCHAR2 (25),
REALNAMEVARCHAR2 (25),
PasswordChar (6) Default ' 000000 ',
Balancenumber (10,2),
Constraint TBL_ACC_ID_PK primary key (ID),
Constraint tbl_acc_ano_nn Check (Accountno is not null),
Constraint Tbl_acc_ano_uk Unique (ACCOUNTNO),
Constraint tbl_acc_bal_ck Check (balance>=0)
);

--Create an employee table (self-correlating)
CREATE TABLE Tbl_emp (
ID Number (5),
Name VARCHAR2 (+) constraint tbl_emp_name_nn NOT NULL,
Title VARCHAR2 (25),
Salary number (10,2),
MANAGER_ID Number (5),
Constraint TBL_EMP_ID_PK primary key (ID),
Constraint tbl_emp_title_ck Check (title in (' manager ', ' Business ', ' Sales ', ' finance ')),
Constraint tbl_emp_sal_ck Check (salary>1850),
Constraint TBL_EMP_MGR_FK foreign key (manager_id)
References Tbl_emp (ID)
);

Add:
The second way to create a table (create a new table from a table that already exists):

--Copy the specified column of a table, build a new table (copy the data)
CREATE table new table name as select column, column,... from original table;

Example: Create a new table with three columns of data in the S_emp table Id,first_name,salary
CREATE TABLE New_emp as select Id,first_name,salary from S_emp;

--Copy the specified column of a table, build a new table (do not copy the data, take the table structure)
CREATE table new table name as select column, column,... from original table
where constant false conditions;

Example: Create a new table with three columns in the S_emp table, no data required
CREATE TABLE New_emp as select Id,first_name,salary from S_emp
where 1=2;

Note: Here 1=2 means constant false, then the data will not be copied, only a blank table can be obtained

Delete table (note constraint control):

DROP TABLE table_name [cascade constraints];

NOTE: Cascade constraints represents a joint constraint to delete, if not added,
If there is a foreign key reference, the deletion is unsuccessful.

To modify a table:

--Column-related
1. Adding columns
ALTER TABLE name add column name data type default value constraint;

--Add a column to the Tbl_user table (age)
ALTER TABLE Tbl_user Add age number (3) default is not null;

2. Delete Columns
ALTER TABLE name drop column name;

--Delete The Age column from the Tbl_user table
ALTER TABLE tbl_user drop column age;

3. Modifying columns (modifying the data type and constraints of a column)
ALTER TABLE name modify original column name new data type new default value new constraint;

--Modify the password column in the Tbl_user table as char (6) Default value ' 00000 ' non-empty
ALTER TABLE Tbl_user Modify password char (6) Default ' 000000 ' NOT null;

4. Modify column names
ALTER TABLE table name rename column name to new column name;

--Modify the password column in the Tbl_user table to be named pwd
ALTER TABLE tbl_user rename column password to pwd;

--Constraint related
1. Adding constraints
ALTER TABLE name ADD CONSTRAINT constraint name constraint type (column name);

Note: If you add a non-null constraint, then:
ALTER TABLE name add constraint table name _ Column name _nn check (x is not null);

**********************************************************************
--Delete Table
drop table tbl_emp;

--Create a table
CREATE TABLE Tbl_emp (
ID Number (5),
Name VARCHAR2 (+) constraint tbl_emp_name_nn NOT NULL,
Title VARCHAR2 (25),
Salary number (10,2),
MANAGER_ID Number (5)
);

--Adding constraints
ALTER TABLE TBL_EMP ADD constraint TBL_EMP_ID_PK primary key (ID);
ALTER TABLE TBL_EMP add constraint tbl_emp_title_ck check (title in (' manager ', ' Office ', ' sales ', ' finance '));
ALTER TABLE TBL_EMP add constraint tbl_emp_sal_ck check (salary>1850);
ALTER TABLE TBL_EMP add constraint TBL_EMP_MGR_FK foreign key (manager_id)
References tbl_emp (ID);

--Initialize data
INSERT into tbl_emp values (...);
INSERT into tbl_emp values (...);
INSERT into tbl_emp values (...);

--Submit data
Commit

**********************************************************************

2. Delete a constraint
ALTER TABLE name DROP CONSTRAINT constraint name;

3. Make the constraint effective
ALTER TABLE name enable constraint constraint name;

4. Invalidating the constraint
ALTER TABLE name disable constraint constraint name;

------------------------------------------------------------

DML (Insert,update,delete)

1. Inserting data
Insert into table name (column, column, column,...) values (value, value, value ...);

When inserting data that corresponds to table column one by one, the column can be omitted
Insert into table name values (value, value, value ...);

For example:
--Add a record to the Tbl_user table
Insert into Tbl_user (Id,username,password) VALUES (1, ' Jack ', ' 123456 ');
INSERT into Tbl_user values (2, ' Tom ', ' 456789 ');

2. Updating data
Update table name Set column = new value where condition;

For example:
--Modify the user name in the second row of records in the Tbl_user table
Update tbl_user set username= ' Ben ' where id=2;

--Increase all status in the current order table to be unpaid order price 10%
Update Tbl_order set price=price*1.1 where status= ' unpayment ';

Note: You must pay attention to the condition!!!

3. Delete data
Delete table name where condition;

For example:
--Delete the third row of data from the user table
Delete Tbl_user where id=3;

NOTE: Constraints cannot be violated here

---------------------------------------------------------

The DML statement differs from the DDL statement:

The 1.DML statement is not automatically committed, meaning that when the DML statement is run out, the database
The real data hasn't changed yet, and the only memory you see in your current business is
, so at this point, another transaction is unable to see the result of the modification.

If you want to synchronize the modified results to the database, you must manually use the following directives:

--Commit the data and synchronize the in-memory data into the database
Commit

--rollback operation, undo action not yet committed
Rollback [to rollback point name];

--Set the rollback point
SavePoint Roll back the name of the point;

That is, one transaction cannot read data that has not yet been committed by another transaction!!!

NOTE: DML statements are also automatically committed by default in Navicat

2.DDL statements are auto-submitted

---------------------------------------------------------

Data dictionary

Definition: A data dictionary is an object used to describe a user object.

Oracle is a database system that uses tables (system tables) to maintain tables (user tables), where
The system table is what we call the data dictionary.

The data dictionary defined by the Oracle database follows a certain naming convention, and it has built-in
Thousands table.

Naming rules:
Prefix _ Database object +s/es

Common prefixes:
User
All
Dba
$

Common database objects:
Table,user,constraint,sequence,index,view ...

Common Data dictionaries:
User_users Storing information for users
User_tables information for storing user tables
User_constraints information about the constraints in the storage table
User_sequences information for storing sequences
User_indexes storing the index information
User_views Information for storing views
User_cons_columns information for storing constraints and column correspondence

--See what constraints are under the current user
SELECT * from User_constraints;

--See what tables are available for the current user
Select table_name from User_tables;

--View all constraint names and constraint types in the Tbl_emp table
Select Constraint_name,constraint_type from User_constraints
where table_name= ' tbl_emp ';

--View the name of the constraint for which all States are unavailable under the current user (jsd1510), and the name of the table to which the constraint functions
Select Constraint_name,table_name,status from User_constraints
where status= ' DISABLED ' and owner= ' JSD1510 ';

Note: The table name must be capitalized here!
-------------------------------------------------------------

DTL, data Transaction language

Definition of a transaction:
Refers to a set of related SQL operations, all of our operations are in the transaction.

Note: In a database, the basic unit of execution business is a transaction, not a certain SQL.
The database is open by default, which means that it is always
In a transaction, the end of a transaction represents the opening of the next transaction.
When a commit or rollback instruction is executed, the current transaction is ended.

Function: Used to ensure the smoothness and predictability of data.

Example: Bank transfer business

A account transfer to B account 10000
SQL1:
Update Tbl_account set balance=balance-10000 where Accountno=a account;

SQL2:
Update Tbl_account set balance=balance+10000 where accountno=b account;

SQL1 and SQL2 must be in the same transaction, guaranteeing both success or failure at the same time.

Four characteristics of a transaction (ACID):
Atomic, atomicity, transactions are no longer divisible, either succeed at the same time, or fail at the same time
Consistency, consistency, once the transaction is over, the in-memory data is consistent with the data in the database
Isolation, isolation, non-interference between transactions, the end of a transaction means the opening of the next transaction
Duration, persistence, once a transaction is committed, the data is persisted to the database and persisted

In Oracle, the command to manipulate the transaction:
1.commit, COMMIT Transaction
Persist all data in a transaction to disk

2.rollback [to rollback Point], ROLLBACK TRANSACTION
Cancel all operations in the transaction and return to the initial state

3.savepoint rollback POINT, set rollback point
When a transaction is rolled back, the starting point

Summarize:
1. The current mainstream database is supported by transactions, and where Oracle supports the best,
2. One transaction cannot read data that has not yet been committed by another transaction.
The 3.DDL statement will automatically commit the transaction
4.DML statements do not automatically commit transactions and require manual commit
---------------------------------------------------------

Multi-transaction concurrency processing mechanism:

Cause: Multiple transactions manipulate the same row of data in one table at the same time if these operations are
If you modify the operation, the concurrency problem will occur, and if you do not process it, it will cause several
According to inconsistent circumstances.

The concurrency problems that a database can produce include:
1. Dirty Reading
Refers to a transaction that is accessing the data and making modifications to the data, and this modification
is not yet committed to the database, and another transaction accesses the data and uses the
The data.

WORKAROUND: When a transaction modifies data, the data cannot be accessed by other transactions

2. Non-repeatable reading
Refers to a transaction that reads the same record multiple times if another transaction is also accessed at this time and
When the data is modified, there will be multiple reads with inconsistent data, the original
Data becomes non-repeatable read data

Workaround: The data can be read only after the modified transaction is fully committed

3. Phantom Reading
Refers to a transaction that modifies multiple rows of records in a table, but at this point another transaction makes the table
The operation of inserting data, the first transaction will find rows that are not modified in the table.
It's like a hallucination.

Workaround: Other transactions cannot add data until a transaction submits data

A ' lock ' is used in Oracle for concurrent processing:
1. Table-level row lock (X) Exclusive mode
2. Table-level shared lock (S) share mode
3. Table row-level exclusive lock (SRX) share row Exclusive
Note: These three kinds of locks are applied through special commands.

Grammar:
Lock table table_name in mode;

For example:
--To share the lock table
Lock table Tbl_emp in share mode;

--to lock the table in rows
Lock table Tbl_emp in exlusive mode;

4. Row-level shared lock (RS) Row share
5. Row level exclusive lock (RX) Row Exclusive
Note: These two locks do not need to be requested by a special command, but are applied via DQL and DML

Attention:
1. All DQL statements will automatically apply for RS lock by default
2. All DML statements automatically request a RX lock by default, and each row records a unique RX lock
3. In the project, in order to meet the business requirements, the general SELECT statement needs to apply for the RX lock

The SELECT statement requests the RX lock with the FOR update:
SELECT * from S_emp for update;
SELECT * FROM s_emp for update wait 5;//5 seconds
SELECT * from s_emp for update nowait;
-----------------------------------------------------------

Other database objects:
Sequence (SEQUENCE)
Indexing (Index)
Views (view)

1. Sequence (SEQUENCE)
Corresponding Data dictionary: user_sequences

Role: A database special object used to produce a unique value

To create a sequence's syntax:
Create sequence sequence name
[Start with n]--means starting from a few, the default value is 1
[Increment by n]--number of increments per count, default is 1
[MAXVALUE n]--sequence Peak value n
[MINVALUE n]--sequence lowest peak n
[Cache n]--provides n pre-allocated sequences that are stored in memory
[Cycle | nocycle]--whether to cycle
[Order | noorder]--ordered or unordered sequence

For example:
--Create a sequence for the employee table
Create sequence tbl_emp_id start with 4;

How do I use sequences?
Nextval: Take the next value of the sequence (Tbl_emp_id.nextval)
Currval: Takes the current value of the sequence (Tbl_emp_id.currval)

When inserting data, use:
INSERT into tbl_emp values (Tbl_emp_id.nextval,.........);

To delete a sequence:
Drop sequence sequence name;

Added: MySQL
CREATE TABLE Tbl_user (
Idint Auto Increment,
Usernamevarchar (25),
Passwordvarchar (25)
);
---------------------------------------------------------

2. Indexing (Index)
Corresponding Data dictionary: user_indexes

It is a more important database object, which can effectively improve the database
Query efficiency (Database performance optimization).

There are 2 ways to create an index:
1. Automatically create
When a primary KEY constraint or uniqueness constraint is added to a column in a table, the system automatically
Creates a uniqueness index, which is the name of the constraint.

2. Manually Create
Grammar:
Create index index name on table name (column name ...);

Attention:
1. The index has its own separate storage space and namespace
2. Creating an index will also sacrifice some database performance

How the Index works:
1. By default, the index is a data structure using btree (binary tree)
2. Pseudo-column (ROWID), the actual "physical address" that holds the data row record.

--Query a row of records based on physical address
Get the rowid of a row record first
Select rowID from S_emp where id=1;//aaadxqaabaaai16aaa

Find the corresponding record based on the row record rowID
SELECT * from s_emp where rowid= ' aaadxqaabaaai16aaa ';

==> Sub-query
SELECT * FROM S_emp where rowid=
(select rowID from s_emp where first_name= ' Carmen ');

3. Principle of Index Establishment:
A key value pair is synthesized from the column value that creates the index with ROWID, which is the index,
They are then stored in the specified data structure (binary tree, bitmap) and are
The index space for the stand.

4. How the index query works:
When the column of the Where condition in our query statement is indexed, the query is divided into the following 2 steps:
A. First check the index, the value in the sentence column directly find rowID
B. rowID directly to the corresponding line record end query based on the first step

5. Indexing strategy:
A. Primary key columns and uniqueness columns are suitable for
B. Columns that do not change frequently are suitable for
C. Meet the above 2 conditions, often as the query criteria for the column suitable for
D. Columns with too many duplicate values are inappropriate
A column with too many e.null values is inappropriate

6. Deleting an index
Drop index name;

-----------------------------------------------------------

Views (view)
Corresponding Data dictionary: user_views

It is a database object, a "window" of its table, used to hold query statements
Object, the view is attached to the table, and he shares the storage space with the table.

Definition: The essence is a valid query statement

Role:
1. With permission, according to the business to do hierarchical management
2. Reduce complexity and enhance the security of your data

Syntax for creating a view:
create [or Replace] view name as clause;
[With read only]--This view is read-only
[WITH CHECK option [constraint constraint name]]

For example:
CREATE VIEW view_name as
Select Id,salary from s_emp with Read only;

Attention:
The operation view must have certain permissions!!!

To delete a view:
Drop view name;

Categories of views:
Relationship view, inline view, object view, materialized view

----------------------------------------------------------

Add:
--Query the main actions that the current user can perform
SELECT * from Session_privs;

--Query all actions that a permission can perform
SELECT * from Dba_sys_privs where grantee= ' DBA ';(requires DBA)

--Querying the system roles assigned to the current user
SELECT * from Session_roles order by role;

Two ways to grant permissions:
1.grant create any view to user name;
2.grant connect,resource,dba to user name;

Practice:
--Create a simple view of all employees in the 41 departments (must have permission to operate the view!)
CREATE VIEW view_emp_41 as SELECT * from S_emp where dept_id=41;

Oracle NOTE 2-database design