Oracle 11g Series Base Review

Well, I also Finon, because in the company's year-end goal of writing to learn Oracle, today began Oracle, to give you a new understanding of it! Not going to hit me! ~ Quack.

Actually, to be honest, I don't want to write theory in this respect, but for the integrity of a series, I still have to bite the bullet to write it. I will try to use the colloquial language for those more blunt terminology, of course there are some not rigorous place, but also please reader Haihan.

Well, at first it's not really about Oracle, it's all about the basics of the database, and it starts with part four! ~~~

1. Relational Database model

1.1 Terminology in relational databases

Well, in fact, when I first learned the database, I was particularly annoyed by these terms, which would have been very easy to understand the words, by their whole.

Two-dimensional table: is a table, such as we open Excel, here can be roughly as a table structure it. So, the logical structure of a relationship is a two-dimensional table.

Relationship (relation): a table corresponding to the one we normally see.

Tuple (Tuple): A row in a two-dimensional table

Attribute: A column in a table

Domain: An attribute's range of values, such as an attribute age, in which the range of values ranges from 0 to 130, which is a domain.

Keyword or code (key): That is, we often say that the primary key, it can uniquely determine a tuple, that is, can only determine a row. such as the student number, then it can determine the student name and so on.

Relational schema (relation schema): A description of the relationship, such as the name of the relationship (attribute 1, Property 2, Property 3 ...). ）

1.2 Relationship Operations

Relational operations commonly used in relational data models: Queries (query), inserts (inset), deletes (delete), and updates (update)

The most important relational query operations in a relational operation are: SELECT, drop Shadow (project), join (join), except (Centeride), and (union), difference (except), intersection (intersection), and Cartesian product, and so on.

1.3 Completeness of the relationship

Entity integrity: The primary key in the database table cannot be null; When a property is composed of more than one property group, the property in the property group cannot be null.

Referential integrity: Relationships between entities and entities in a relational model are described by relationships. Referential integrity is the constraint between the two tables associated with the specifically, the value of the foreign key from each record in the table must be present in the primary table, so if an association relationship is established between the two tables, the action on a relationship affects the records in the other table.

FOREIGN key: If a property keyword is a primary key in a table, then this property keyword is called the foreign key of another table. The foreign key represents the connection between the two relationships.

If a property is referential integrity, its value can only be:

1. Null value, indicating that no value was assigned to it

2. is not a null value, then must be in the range of values in a primary table

(Explain this really tired, hehe ~)

Custom Integrity: Entity integrity and the integrity of the reference frame are the completeness that each relational database must support. That customization is not necessary, literally can be seen, is the meaning of their own definition. For example, you have a gender attribute, so you can only define "female" only. This number is understood.

2. function dependencies in the database

function dependency refers to the value of one or a set of properties in a relationship that determines the values of other properties. function dependencies are just like a function y = f (x), and the value of Y is uniquely defined after the value of X is given.

2.1 Types of function dependencies

2.1.1 Full function dependencies and partial function dependencies

Full function dependencies: In a relationship, if a non-primary attribute data item relies on all the keywords called full function dependencies.

For example: There is a table with the following attributes: Student ID, student name, course ID, course name, Grade

Full Reliance: (Student ID, course ID) → Results

Grades can be either dependent on the student ID alone or on the course ID, so the complete function depends on the keyword.

(Student ID, course ID) → Student name

Partial dependence: Student id→ student Name

Student names can depend on one main attribute of the keyword-the student ID, so the student name part function depends on (student ID, course ID).

2.1.2 Trivial function dependencies and nontrivial function dependencies

Trivial function dependencies: When the attribute set Y in a relationship is a subset of the attribute set X, there is a function dependency x→y, a set of property functions that determines all subsets of it, a function dependency called trivial function dependencies.

For example: In relation to the student schedule SC (Student number SNO, course number Cno, Grade Grade),

(Sno, Cno) →sno, (Sno, Cno) →cno

Nontrivial function dependencies: When the attribute set Y in a relationship is not a subset of the attribute set X, there is a function dependency x→y, which is called a non-trivial function dependency.

For example: (Sno, Cno) →grade

2.1.3 Transfer function dependencies

Transitive function dependencies: Set X,Y,Z is a separate set of attributes in relationship R, exists x→y (Y!→x), Y→z, and the Z-pass function is called X.

Example: Relationship S1 (School number, department name, department head)

School Number → Department name, department name → Department director, and the department name-\→, so the school number → Department director for transfer function dependence

3. Paradigm of the database

Benefits:

① Reduce data redundancy

② improve the overall organization of the database

③ enhances data consistency

④ increase the flexibility of database design