Basic concepts and architecture of Mysql Databases

Basic concepts and architecture of Mysql Databases

Database

1. Key: The primary key is the flag column in the table. A key may consist of several columns. You can use keys as references between tables.

 

CustomerID is the primary key of the MERs table. It is called a foreign key when it appears in another table, such as the Orders table.

2. Mode

The complete design of the entire database table is called the database mode.

In one mode, the table and table columns, primary keys of each table, and foreign keys are displayed.

A mode does not contain any data, but we may want to use the sample data in the mode to parse the meaning of the data.

For example: MERs (CustomerID, Name, Address, City)

Orders (OrderID,CustomerID, Amount, Date)

The underline element indicates that the element is the primary key of the link, and the Italic element is the foreign key of the link.

3. Relationship

The foreign key represents the relationship between two table data. Based on the number of objects contained in the relationship, these relationships can be divided into one-to-one, one-to-many, and many-to-many.

Design Database

1. Consider the actual object of Modeling

Every real-world object to be modeled must have its own table.

2. Avoid storing redundant data

Avoid irregular updates in three situations: irregular changes, inserts, and deletions.

3. Use atomic column values

Store only one data for each attribute of each row. Books Ordered does not match

When two objects have many-to-many relationships, such as the relationship between orders and books, you must create a new table named Order_Items.

4. Select a meaningful key

Make sure that the selected key is unique.

5. Avoid designing multiple null attributes

It is a bad thing to have many null values in the database. It is a waste of space and may cause errors when calculating the total number of columns or applying computing functions to other numeric columns.

6. Database Table types

Simple Table: A simple table that describes objects in the real world. These tables may also contain keys of other simple objects. They have one-to-one or one-to-many relationships.

Join table: Describes the many-to-many relationship between two real-world objects.

 

Web database architecture

A typical Web database transaction involves the following steps:

(1) The user's Web browser sends an HTTP request to request a specific Web page. For example, the user may search for all books written by Thomson in the bookstore in the form of HTML forms. The search result webpage is called result. php.

(2) The Web server receives a result. php request to obtain the file and upload it to the php engine for processing.

(3) The php engine starts parsing scripts. The script contains a command to connect to the database, and a command to execute a query (execute a search for a book. Php opens the connection to the Mysql database and sends the appropriate query.

(4) the Mysql server receives and processes database queries. Return the result (a list of books) to the php engine.

(5) php engines run scripts. Generally, this includes formatting query results into HTML format. Then, the output HTML is returned to the Web server.

(6) The Web server sends HTML to the browser. In this way, the user can see the books he is searching.