Data modeling and Database design

http://blog.csdn.net/suwu150/article/details/52724937

Data modeling and Database design
I. Entity-Relationship Diagram
Entities-Diagrams (Entity Relationship Diagram), also known as e-r graphs, provide methods for representing entities, attributes, and relationships to describe the conceptual model of the real world.
The basic elements that make up a e-r diagram are entities, attributes, and relationships
Entities: Entities are used to represent things that have the same characteristics and properties (Java-like classes), and entities are represented by entity names and entity attributes.
Property (Attribute): An attribute that an entity has, an entity can have several properties
Relationships (Relationship): The way entities connect to each other is called relationships. Can be divided into the following 3 types:
One-on-one relationship (1:1)
One-to-many relationship (1:N)
Many-to-many relationships (M:N)
May-be and must-be
In the relationship between entity and entity, there are two kinds of may-be and must-be, for example:
The system has the customer and the order two entities (1:n relationship), a customer corresponds to multiple orders, an order corresponds to a customer, and a customer can (May is) no orders and he corresponds, an order must (must be) will have the customer and it corresponds.
Second, the representation of the symbol in ER diagram
1) #: Unique, may later be indicated as the primary key
2) *: Non-empty
3) O: Optional
4) Dashed line: Can be customer this side dashed line, customer may have no order
5) Solid line: Must be order this side solid line, the order must belong to a customer.
6) Vertical bar (|): Represents to be forced on (|) A party establishes a federated primary key and takes the ID of the partner to do the Federated primary key.
7) The umbrella icon represents a multi-party, not an umbrella-like icon represents one party
Third, the database design
After the data modeling is complete, you can convert the ER diagram into a table in the data
1. The name of the entity is converted to the name of the table
2. Attribute conversion of an entity to a column in a table
3. Property with unique characteristics set to primary key in table
4. Set as a foreign key column (Primary foreign Key Association) for a column in a table based on the relationship between entities
Note: The fourth step is mainly: Entity relationship---> table relationship
When designing the relational database, we can design a reasonable relational database by complying with different specification requirements, which are called different paradigms, various paradigm presentation sub-norm, higher paradigm databaseLess Redundancy。
There are currently six paradigms for relational databases:
First Paradigm (1NF)
Second Paradigm (2NF)
Third Paradigm (3NF)
Bath-cod paradigm (BCNF)
Paradigm Four (4NF)
Model Five (5NF, also known as perfect paradigm)
Note: The paradigm that meets the minimum requirements is the first paradigm (1NF). The second paradigm (2NF) is further satisfied on the basis of the first paradigm, and the rest of the paradigms are referred to by analogy. In general, the database only needs to meet the third normal form (3NF) on the line.
First paradigm:
In a table, the values inside each column are no longer divisible.
For example: We design a table that has a column: hobby
The value of this column may be this: Football basketball table Tennis
But this value can be re-divided: Football, basketball, table tennis
So this design is not satisfying the first paradigm
Second paradigm:
The second paradigm is based on satisfying the first paradigm
The non-primary key columns in the table must depend on the primary key column
For example:
Order Form: The order number is the primary key
Order number order name production place of the product in order date order
In these non-primary key columns, product production is not dependent on the order number, so this design is not satisfied with the second paradigm
The third paradigm:
The third paradigm is based on satisfying the second paradigm
The non-primary key columns in the table must be directly dependent on the primary key column, not indirectly.
(Cannot produce dependent delivery)
For example:
Order Form: The order number is the primary key
Order number Order name customer number Customer Name

The customer number depends on the order number, the customer name depends on the customer number, so that the customer name indirectly depends on the order number, then there is a dependency transfer, so this design is not satisfied with the third paradigm

Iv. Understanding primary and foreign keys
Primary key:
1. The column that can be the master key must satisfy the non-null unique characteristic
2. Any column that satisfies a non-null can be the master key
3. You can make a meaningful column in the table key, such as the number, it is both the student number and the table as the primary key, because this column satisfies the non-null unique conditions
4. You can also find a meaningless column master key, which is used to uniquely identify a row of records

5. We can make multiple columns join together to make the primary key in the table, then it is the Federated primary key, which requires the values of these columns to be joined together in a non-null unique

As shown, creating a federated primary key database automatically sets its column to NOT NULL:

[SQL]View PlainCopy

1. Create table T_CSDN (ID number,name VARCHAR2,primary key (ID,name))

FOREIGN key:
1. One of the columns in the table is declared as a foreign key column, and the value of this foreign key column is generally referenced to the value of the primary key column of the other table (columns with unique constraints are OK, not necessarily referencing primary key columns)
2. The values that appear in the primary key column of the other table can be used in the foreign key column, and no value is used
3. Foreign key column values can also be empty, provided that the foreign key column in the table is not the key, because we can also use the table as the foreign key column as a primary key to be used (only to meet the non-null unique requirements)
4. If the value of the Union primary key in table B is referenced to the foreign key in table A, because the two columns do the joint primary key in table B, then a table refers to the values of both columns to refer to, then they will appear as a Union foreign key in table A.

As shown, refer to the Federated PRIMARY KEY constraint shown above:

[SQL]View PlainCopy

2. Create table T_CSDN2 (
4. ID number,name VARCHAR2,
6. CSDN_ID number,
8. Csdn_name VARCHAR2 (15),
10. primary key (ID,name),
12. Foreign Key (csdn_id,csdn_name) references t_csdn (ID,name));

Data modeling and Database design