"SQL Story" excerpt five--The truth of the relationship

The truth of the relationship

For a long time, we used to call tables in relational databases a two-dimensional table. Because it has rows and columns, it is easy for us to associate it with a two-dimensional plane, but in fact, this is not the original intention of relational databases, nor is it consistent with the design of relational models. In fact, for a long time, I have only a vague concept of this, the view of the plane table although there is doubt, but has been unable to verify. Until one day, the old book-"relational Database" (Shi Shugang, Zheng, Tsinghua University Press, 1993), the old book is not a popular new gimmick, but full of mathematical theory. This book is not very attractive to read, but it is really rigorous and clarifies many of my unknowns. Also inspired me to find all kinds of authoritative materials to learn from the beginning. A thin book, priced only 9.9 yuan, think this should be I was at school, from the old book shop, probably only spent less than five yuan. This must be the most cost-effective book I've bought.

Here, I do not want to copy a large paragraph from other people's words, spelling out a text, there is less time than the internet to post a more fulfilling, I would rather my experience, with not strict, but as easy to understand the language written down. Let friends first have a basic concept of the relational model, so that many of the trained readers like me to understand the truth, not in the work of inconvenience. If you want to really grasp the mathematical model of a relational database, read the "relational database" of this not-so-popular old book. Of course, there are a lot of new formal teaching materials are also very good, such as now in my hand, "DATABASE SYSTEM Concepts" (Machinery industry Press) and the "SQL-3 reference book" (Machinery Industry Press). The first two write more rigorous, the latter is not very good translation, some key places to read inexplicable, but in the end it is still a rare good book.

Now, let's talk about what the relational database is all about. Let's look at a table first:

X Y Z

------------------

0 0 0

1 1 1

0 0 1

0 1 1

......

This table stores some points within a three-dimensional space. We can see very clearly that every complete line represents a point. By locating only a column of a row, it does not represent the information structure defined by the table. When we insert or delete data into this table, it still represents the set of points in the three-dimensional space. But what if we add a column of T (time)? All that has changed, it is no longer three-dimensional space, now, this table is stored in four-dimensional information (read the theory of relativity of friends should understand that the relativity of space-time is a four-dimensional coordinate system). Delete a column? For example z column, now we are facing is the X-y plane, this is a two-dimensional coordinate system. That is, deleting or adding or modifying several columns in a table does not change the meaning that the table represents. and modify or delete the column, will change the structure of the table, the table represents the meaning is different from the previous. The rows and columns of the table are not equal! We can't simply understand it as a plane! Instead, I think it is more appropriate to understand the structure of a table as an algebraic space, so that each row in the table is a point in the algebraic space, and the information in the current table forms a set of points in that space. Of course, there are other constraints to this set, and we'll start with the relational model.

In a strictly relational model, a relational model, including relationship name, finite attribute set, attribute range, attribute column to domain mapping, integrity constraints and attribute dependencies. The structure in the corresponding database, typically a relational pattern corresponds to one or more tables and views. These tables and views have their own columns, columns have column names, data types of columns, primary key constraints for tables and views, foreign key constraints, checks, assertions, triggers (in SQL Server2000, indexes and triggers can be defined in the view, plus SQL scripts for the view itself. View to be defined as a complete relational pattern. A simple relational schema can consist of only one table, and multiple tuples (tables or views) that are related to each other by foreign keys and triggers. In this definition, each column in the relationship (that is, the pattern of the relationship) can have strong dependencies. For example, some columns have primary KEY constraints, others have reference foreign keys. The dependencies between rows and rows (that is, between relationships) exist only in two cases-foreign keys and triggers. Where the foreign key can be expressed as a dependency on the pattern (that is, between columns). For example, the following order system, which consists of Customer table, order form 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 (not NULL),

Number INT,

Price,

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, one customer can have multiple orders, and the subscriber of the order relies on the Customer table. An order can have multiple entries, and itemized items depend on the existence of orders and commodities. These four tables form a relational schema, each of which has its primary key, and the other columns in the table depend on the primary key columns. A foreign key reference between tables constitutes a dependency between tables. This joins up the entire pattern. Each complete order, including the order information in the Orders table and the details in the Items table, forms a relationship. And a user and his order, can form a new relationship, these relationship patterns, can be expressed by SQL statements, not in detail here, friends can try, very simple join query just. We can see that in this typical relationship, the content of the information is much more complex than the "few two-dimensional plane tables" can define. "Several algebraic space point sets with strong constraint conditions may be better." Even, we can define a stronger constraint on it, such as using a trigger to guarantee that a user's order is within a certain range. When we delete orders, inventory and even customers, because of the strong constraints exist, ensure that each relationship is still intact. The definition of a column is itself part of the relational schema, and if we change or delete the column, the whole pattern is modified. At least one algebraic space, which we have changed. This is the difference between rows and columns. Of course, it is technically permissible to define only one table without any constraints on it, but such a table cannot be called a relationship. It does not even guarantee the most basic relationship integrity. As the previous article shows, such tables can easily insert duplicate data, and these data cannot be differentiated to give us more information. Such an unconstrained table should be strictly limited to temporary tables in practice and should not be present on any other occasion.

Hope that the friend who read this article, can no longer make my original Chang error, build one and another without any constraints of the table, into unreliable data. We should express the structure and relation model of information directly in the design of database, which is the meaning of relational database. There's almost no executable code this time, and perhaps readers will have a problem. But I wish you all the best to understand what I mean by working more easily and freely in the world of relational databases. If you have criticism, praise, guidance, I am here to listen attentively, thank you for your support. I focus on SQL Server and InterBase in the book, I want to have Oracle or DB2 and other database systems master to work with me to complete the different versions of this book, and I share the hardships of labor and the joy of success.