140911 Relational Database Foundation

Basic concepts of relational databases

Relationship: two-dimensional table

Rows: Tuples

Columns: Properties

Domain: Property value Range

Keywords: uniquely identify a tuple (master code)

General display representation: The relationship name (attribute 1, property 2, ...). Attribute N)

such as: Student (school number, name, gender, age, faculty number)

Data integrity

Refers to the correctness and uniqueness of the data in the database.

Three types of integrity rules:

1. Entity integrity rules

2. Referential integrity rules

3. User-defined integrity rules

Relationship actions: Selection, projection, connection

1, choice, also known as the limit. I select a tuple in a relationship that satisfies a given condition

The selection operation is actually a tuple that chooses the logical expression to be true from the relationship

To perform an operation on the line angle of a relationship

The logical expression operator can be:>, <, >=, <=,! =, =

2, projection.

Select several attribute columns on a relationship to compose a new relationship

Projection is an operation at the angle of a column

Some tuples may be canceled after the projection operation, because once a particular column is selected, duplicate rows may be generated, and the duplicate rows must be eliminated

3, connection.

A connection that is a connection condition by one of the attributes in one relationship that is equal to a property of another relationship.

Logical Database Design

Transforming entities and relationships into relational patterns

Function-dependent

Lossless segmentation

Normalization guidelines

Contact

The connection of things can be divided into two categories: one is the relationship within the entity set, the other is between the attributes, and the other is the relation between the entity sets, which can be decomposed into the relations between the entities.

Two types of relationships between entities:

1:1 each entity in entity set A is associated with at most one entity in entity set B. Vice versa.

1:n entity set A is associated with more than one entity in entity set B, and each entity in B is associated with at most one entity in a.

M:n each entity in entity set A is associated with more than one entity in entity set B, whereas each entity in B is also associated with multiple entities in a.

A pair of one or one-to-many, many-to-many connections between entities is also present in more than two solid types.

Entity-contact data model, represented by a e-r graph.

Entity (type): A rectangle box with the name of the entity in the box.

Properties: Rectangle box to connect the lines to the entities. A separate list of entities and their properties is also available when there are many properties.

Contact between entities: The Diamond box, the name of the contact within the box. Connect the Diamond box to the entity by line, and label the contact type on the line.

Relational Model evaluation

High degree of redundancy. That is, repeated occurrences.

Difficult to modify. When modifying data, it is easy to influence other data.

Insert the problem. When inserting data, it is easy to influence other data.

Remove the issue. When you delete data, you can easily affect other data.

function dependencies

According to the definition of the function, if x takes the determined value, then Y must have a unique value corresponding to the value, then the y function depends on x, which is recorded as:x→y. You can understand this: x determines y

Example: Student ( school Number , name, age, class, place of origin), existence "name" function depends on "school number" record: study number → name

Full function Dependency : In the relational mode R (A1, A2, ..., an), if x→y, and the any true subset of x x′, there is no x′→y, then the Y-complete functionis called X-dependent. If x→y, but the Y partial function is dependent on X, then the y functionis called X-dependent. (full function dependencies are primarily for composite primary keys)

transfer function Dependency : In the relational mode R (A1, A2, ..., an), if x→y,y→z, and Y does not belong to X,z, is not y,y→x, then the Z transfer functionis called dependent on X. (transfer function dependencies are primarily for foreign keys)

Paradigm

Relationships in relational databases need to meet certain requirements, called paradigms.

1, the first normal form (1NF). The minimum requirement becomes the first paradigm.

The values of each column cannot be split into simpler data items.

If a relationship satisfies: Each attribute value must be an element that can no longer be divided, then the canonical relationship, called the first normal, précis-writers to 1NF.

Example: Relationship "Time table" (number, name, commute)--the third field can be broken down into work hours and off hours.

2, the second normal form (2NF). The main solution is partial function dependencies.

satisfies the first paradigm.

Non-primary key columns are full functions that depend on the primary key.

The "Employee Number" and "Project number" in the table below are the combined primary keys, however, the employee department number and the project number have no function correlation and do not conform to 2NF; The Employee department number cannot be determined by the project number alone.

Cases:

3, the third normal form (3NF). Each non-primary property is not partially dependent on the keyword, nor does it pass a keyword-dependent relationship. Resolves the transfer function dependency.

satisfies the second paradigm.

Each non-key column in the table does not pass dependent on the primary key.

The following table has a functional dependency between the non-primary key "department number" and "department name", because once the department number is specified, there will be a department name corresponding to it.

Non-destructive segmentation: The partitioning of a table cannot destroy the dependency of a function, and satisfies this condition called lossless segmentation.

140911 Relational Database Foundation