Database design for multiple tables __ Database

Source: Internet
Author: User
Tags one table

Mode Schema is a description of the data in the database (columns and tables), as well as any related objects and various connection modes. 1 FOREIGN Key

A foreign key is a column in a table that references the primary key of another table
About foreign keys:
(1) The foreign key may be different from the name of the primary key it references
(2) The primary key used by the foreign key is also called the parent key, and the table that contains the primary key is called the parent table
(3) A foreign key can be used to confirm that a row in one table corresponds to a row in another table
(4) The value of the foreign key can be value NULL even if the primary key value is not NULL
(5) Foreign key values do not require a unique, in fact, foreign keys are usually not unique
A foreign key is NULL, indicating that there is no corresponding primary key in the parent table, that the foreign key contains meaning, is stored in the parent table, and can be implemented by constraints .
The value of inserting a foreign key column must already exist in the parent table's source table, which is referential integrity .
How to create a foreign key :
Method One:

CREATE TABLE emp (
emp_id char (8) Primary key, 
Emp_name char 
);
CREATE TABLE salary (
ID char (8), 
salary float, 
foreign key (ID) references emp (emp_id) 
);

Method Two:

ALTER TABLE table_name
ADD constraint fk_table_name 
foreign key (AA) references table_name2 (AA);
2 The relationship between tables and tables

Relationships between Tables : One-to-one, Many-to-many, Many-to-many
Connection Table (junction table): When you encounter a many-to-many relationship, you can create a new table in the middle to meet the paradigm's requirements and reduce duplicate data.
A primary key that consists of more than one column in a key combination . 3 function Dependencies

When a column's data must change with the data in another column, it means that the first column function depends on the second column.
T.x→t.y T.x\rightarrow{t.y} indicates that the Y Y column function relies on x x columns in the relational table T t
partial function Dependencies A column that is not a primary key depends on a part of the combined primary key (but not entirely dependent on the combined primary key)
transfer function Dependency refers to the change of any non-key column that may result in other columns 4 paradigm

First Paradigm (1NF)
Rule 1: Each data row must contain an atomic value
Rule 2: Each data row must have a unique identifier--primary key
second Normal form (2NF)
Rule 1: Meet 1NF first
Rule 2: No partial function dependencies
As long as all columns are part of a primary key or table with a unique primary key column conforming to the 1NF table also conforms to 2NF
Third paradigm (3NF)
Rule 1:2NF compliant
Rule 2: No transfer function dependencies

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.