Relational truth
For a long time, we are used to calling the table in the relational database as a two-dimensional table. Because it has rows and columns, it is easy for us to link it to the same 2D plane, but in fact, this is not the original intention of the relationship database, and it is not a design of the relationship model. In fact, I have only a very vague concept, I have doubt about the view of the plane watch, but I have been verified. Until one day, turned out of an old book - "Relational Database" (Shi Tree, Zheng Zhenqi, Tsinghua University Press, 1993), this old book is not a popular new, but full of local is mathematics theory. This book is not very attractive, but it is indeed rigorous, clarifying my many unknown things. I also motivate me to find a variety of authoritative materials. Thin book, pricing is only 9.9 yuan, think about it, when I am going to school, I bought it from the old book stall, may have only spend less than five yuan. This is definitely a book I bought the highest price.
Here, I don't want to copy the original words from the book, spelling a text, there is such a work, I am not as good as the net back, I would rather put my experience and use it is not strict, but I am easy as possible. Write under the language. Let friends have a basic concept to the relationship model, making many readers like me, do not understand the truth, not in the work. If you want to truly master the mathematical model of the relational database, read the "Relational Database" read by reading this unfolded. Of course, there are still a lot of new regular textbooks that are very good, such as "Database System Concepts" (Machinery Press) and "SQL-3 Reference House) (Machinery Industry Publishing House). The first two writes are more rigorous, and the latter is translated is not very good. Some of the crucial places read inexplicably, but it is still a rare book.
It is said that we talk about how the relational database is about. Let's take a table first:
X y z
------------------
0 0 0
1 1 1
0 0 1
0 1 1
......
This table stores some points in a three-dimensional space. We can see very clearly that every complete line represents a point. Only one row is locked, it does not express the information structure defined by this table. When we insert or delete data from this table, it still represents a point set in the three-dimensional space. But if we add a column T (TIME)? That's all, it's no longer a three-dimensional space, now, this table stores four-dimensional information (friends who read relative theory should understand that the time and space in the relative theory is a four-dimensional coordinate system). Delete a column? For example, the Z column, now we face the X-Y plane, this is a two-dimensional coordinate system. That is to say, in the table, it is deleted or incremented or modified, and the meaning of this table is not changed. To modify or add, it will change the structure of the table, and the meaning representative of the table is different from before. The rows and columns of the table are not equal! We can't make it easy to understand it as a plane! Instead, I think that the structure of the table is more suitable for an algebraic space, so that each line in the table is a point in this algebra, then the information in the current table forms a point set in this space. Of course, this collection can also have other constraints, below, we start with relationship model.
Among the strict sense models, a relational model, including relationship name, limited property set, attribute value domain, property column-to-value map, integrity constraint, and inter-property dependencies. The structure in the corresponding database, usually one or more tables and views correspond to one or more tables. These tables and views have their respective columns, and list list, column data type, primary key constraints, foreign key constraints, check, assertions, triggers (in SQL Server2000, can be in the view in the SQL Server 2000) Define indexes and triggers, plus the SQL script of the view itself, and the view can be defined as a complete relationship mode). A simple relationship mode can be constructed only by a table, while a plurality of tuples (tables or views) consists of completion of each other by foreign bonds and triggers. In this definition, the columns in the relationship (that is, the pattern of relationships) can have strong dependence. For example, some columns have primary key constraints, others have reference foreign keys. The dependence between the line and the line (that is, between the relationship) is only existing in two cases - foreign key and trigger. The foreign key can be expressed as the mode (that is, the columns) dependence. For example, the following order system, which is made by the customer table, an order table, and Create Table Customers (
Customerid Int Not Null,
Firstname char (20),
Lastname char (20),
City varchar (30),
ConsTRAINT PK_CUSTOMER Primary Key (Customerid)
)
CREATE TABLE GOODS
ID INT NOT NULL,
Name char (20) Not null,
Number Int,
Price Money,
ConsTRAINT PK_GOOD PRIMARY Key (ID))
Create Table ORDERS
OrderId Int Not Null,
Customerid Int Not Null,
ORDERDATE dateTime,
Constraint PK_Order Primary Key (ORDERID),
Constraint fk_customer foreign key (Customerid)
References Customers (Customerid))
CREATE TABLE ITEMS
Itemsid Int Not Null,
OrderId Int Not Null,
Number Int Not NULL
Constraint PK_Item Primary Key (Itemsid),
ConsTRAINT FK_GOOD Foreign Key (itemsid)
References Goods (ID),
Constraint fk_order foreIGN Key (OrderID)
References Orders (OrderID)
)
In the above table, a customer can have multiple orders, and the subscriber of the order relies on the client table. A order can have multiple entries, and the bright entries are dependent on orders and goods. These four tables constitute a relational mode, and the four tables have their primary keys, and other columns in the table depends on the primary key column. The foreign key reference between the tables constitutes the dependencies between the tables. This is connected to the entire mode. Each complete order, including the ordering information in the ORDERS table, and the details in the Items table, form a relationship. And a user and his order can also form a new relationship, the model of these relationships, can be expressed by the SQL statement, here is not detailed, friends can try, very simple join query. We can see that in this typical relationship, the content of the information is complicated, which is far exceeded by "several two-dimensional flat tables". "Several algebraic spatial points associated with strong constraints" may be better. Even, we can also define stronger constraints, such as using a trigger to ensure that the user's order is within a range. When we add orders, inventory or even customers, it is still complete due to strong constraints. The definition of the list itself is part of the relational mode. If we change or add a column, modify the entire mode. At least one algebraic space is changed by us. This is the difference between the rows and columns. Of course, only one table is defined, do not give it any constraints, it is technically allowed, but such a table cannot be referred to as a relationship. It does not even guarantee the most basic relationship integrity. As shown in previous articles, such a table can be easily inserted into duplicate data, and these data that cannot be distinguished from each other cannot give us more information. This unconstrained table should be strictly limited in practical tables, and should not appear in any other occasion. I hope to read the friend of this article, I can no longer commit me to start an error in the early days, establish another table without any constraints, deposit unreliable data. We should directly express the structure and relationship model of the information in the design of the database, this is the meaning of the relational database. This time there is almost no code of executable, and it may be comments. But I really wish you all the best, and work more easily in the world of relational databases. If you have criticism, praise, guidance, I will listen to it here, thank you for your support. I focus on SQL Server and Interbase, I hope to have some other database systems such as Oracle or DB2 to work with me to complete the different versions of this book, share the hardships of labor and success.
