Statement operation of Database Creation table and specification of database creation

I. Statement manipulation of tables

1. Create a table
CREATE TABLE Login
(
UserName varchar (primary) key,--the primary key.

Primary KEY (Code,chengwei)--Federated primary Key
Password varchar () NOT NULL,--cannot be a null value.
Name varchar (unique)--a unique key is built.
The SEX bit default 1---------Defaults to constraints (default constraints).
Birthday datetime Check (birthday> ' 1900-1-1 ')--Constructs a check constraint.
)

2. Modify the form
ALTER TABLE Login Add money float--add
ALTER TABLE Login Drop column money--delete
ALTER TABLE Login ALTER COLUMN money Real--Modify

3. Delete a table
drop table Login

The three main paradigms of database design

First paradigm: (1NF): The atomicity of columns, each column can no longer be split down

Second paradigm: (2NF): For federated primary Keys, some of these columns have a relationship with only one primary key column that violates the second paradigm.

Third paradigm: (3NF): The table cannot have columns that are indirectly associated with the primary key, only columns that have a direct relationship

Key words:
Primary key: Primary key
Identity: Self-growth
References table name (column name): FOREIGN key relationship

Iii. Examples of paradigm application

The first paradigm (1NF): All properties of the relational mode R are required to be indivisible basic data items, which means that each column of a database table is an inseparable basic data item and cannot have multiple values in the same column, that is, an attribute in an entity cannot have multiple values or cannot have duplicate properties.

For example, some database systems need to use the "address" attribute, the "address" attribute should be directly designed as a database table field of the row. However, if the system often accesses the "city" part of the "address" attribute, then it is not to be the "address" attribute to be re-split into provinces, cities, detailed address and other parts of storage, so that in the address of a part of the operation will be very convenient. This design satisfies the first paradigm of the database, as shown in the following table.

User Information table

numbering	Name	Sex	Age	Contact phone	Provinces	City	Detailed Address
1	Zhang Hongxin	man	26	0378-23459876	Henan	Kaifeng	Chaoyang District, Xinhualu 23rd
2	Li Shiping	female	32	0751-65432584	Guangzhou	Guangdong
3	h	man	21	0371-87659852	Henan	Zhengzhou	Erqi District University Road 198th
4	Guo Xiaoming	Woman	27	0371-62556789	Henan	Zhengzhou	No. No. 218, Xinzheng bei Jie, Xue Dian

The user information shown in the table above follows the requirements of the first paradigm, which makes it very convenient to classify users using cities, and also improves the performance of the database.

The second paradigm (2NF) is established on the basis of the first paradigm (1NF), i.e. satisfying the second normal form (2NF) must first satisfy the first paradigm (1NF). The second paradigm (2NF) first requires that the primary key must first be in the database table. For the implementation of the distinction, it is common to add a column to the table to store unique identities for each instance. Second, it requires that the entity's attributes depend entirely on the primary keyword. The so-called full dependency is the inability to have a property that depends only on the primary key, and if so, this part of the property and the primary key should be separated to form a new entity, and the new entity is a one-to-many relationship with the original entity. Using projection decomposition method to decompose a 1NF relationship into a number of 2NF relationships, it can alleviate the problems of insertion anomaly, deletion anomaly, data redundancy and complex modification in the original 1NF relationship to some extent.

For example, to design an order information table, because there may be more than one item in the order, the order number and the product number are used as the federated primary key for the database table, as shown in the following table.

Order Information Form

Product Code   TD valign= "Top" >

order number	Product name	number	units	Product price
001	1	excavator	table	1200000￥
002	2	percussion drill		230￥
003	3	Forklift	2	Car	980000￥

This creates a problem: The table is the Union primary key with the order number and the product number. In this table, the product name, unit, commodity price and other information is not related to the table's primary key, but only related to the product number. So this violates the design principle of the second paradigm.

And if the Order Information table is split, the product information is separated into another table, it is very perfect. As shown in the following two.

Order Information Form

Order Number	Product number	Quantity
001	1	1
002	2	8
003	3	2

Product Information Sheet

Product number	Product Name	Unit	Commodity price
1	Excavator	Console	1200000￥
2	Impact Drills	A	230￥
3	Forklift	Car	980000￥

This design, to a large extent, reduces the redundancy of the database. If you want to get the product information for an order, use the item number to inquire in the product information sheet.

The third Paradigm 3NF is a subset of the second paradigm (2NF), that is, satisfying the third paradigm must satisfy the second paradigm. The third paradigm needs to ensure that each column of data in a data table is directly related to the primary key, and not indirectly, that any non-primary attribute must not be passed dependent on the primary attribute. In short, a relationship is required to not contain non-primary key information that has already been included in other relationships. (The projection decomposition method is often used to solve the problem)

For example, when designing an order data table, the customer number can be used as a foreign key and order table to establish the corresponding relationship. Instead of adding fields to the order form about other customer information (such as name, company, etc.). The design shown in the following two tables is a database table that satisfies the third paradigm.

Order Information Form

Order Number	Order Items	principal	salesman	Order Quantity	Customer number
001	excavator	Liu Ming	partner	1	1
002	percussion drill	li gang	Hownie	8	2
003	Forklift	Guo Niu	Amelie	2 Cars	1

Customer Information Form

Customer number	Customer Name	Affiliated Companies	Contact information
1	Li Cong	51 Construction	13253661015
2	Liu Xinmin	Self-employed	13285746958

In this way, when the order information is queried, the customer number can be used to refer to the records in the Customer information table, and the data redundancy is reduced without having to enter the contents of the customer information multiple times in the Order Information table.

Statement operation of Database Creation table and specification of database creation