Danh mục

Exploring the Northwind Database

Số trang: 11      Loại file: pdf      Dung lượng: 131.90 KB      Lượt xem: 16      Lượt tải: 0    
Hoai.2512

Xem trước 2 trang đầu tiên của tài liệu này:

Thông tin tài liệu:

Exploring the Northwind Database A database may have many tables, some of which are related to each other. For example, the North-wind database contains many tables
Nội dung trích xuất từ tài liệu:
Exploring the Northwind DatabaseExploring the Northwind DatabaseA database may have many tables, some of which are related to each other. For example,the North-wind database contains many tables, four of which are named Customers,Orders, Order Details, and Products. Figure 2.11 is a repeat of the diagram shown earlierthat illustrates how these tables are related.Figure 2.11: Relationships between the Customers, Orders, Order Details, and ProductstablesThe columns for each table are shown within each box. For example, the Customers tablecontains 11 columns: • CustomerID • CompanyName • ContactName • ContactTitle • Address • City • Region • PostalCode • Country • Phone • FaxIn the next few sections, youll learn some database theory, and then youll learn howeach of the previous columns is defined in the Customers table. Youll also explore theOrders, Order Details, and Products tables.Primary KeysTypically, each table in a database has one or more columns that uniquely identify eachrow in the table. This column is known as the primary key for the table. A primary keycan be composed of more than one column. In such cases, it is known as a composite key.Note The value for the primary key in each row of a table must be unique.In the case of the Customers table, the primary key is the CustomerID column. The keyicon shown to the left of the CustomerID column in Figure 2.11 indicates that thiscolumn is the primary key for the Customers table. Similarly, the primary key for theOrders table is OrderID. The primary key for the Order Details table is composed of twocolumns: OrderID and ProductID. The primary key for the Products table is ProductID.Table Relationships and Foreign KeysThe lines that connect the tables in Figure 2.11, shown earlier, display the relationshipsbetween the tables. The infinity sign (∞) at the end of each line indicates a one-to-manyrelationship between two tables, meaning that a row in one table can be related to one ormore rows in the other table.For example, the Customers table has a one-to-many relationship with the Orders table.Each customer can place many orders. Similarly, the one-to-many relationship betweenthe Orders and Order Details table means that each order can be made up of many orderdetails (you can think of an order detail as a line in a purchase order list, with each linereferring to a specific product that is ordered). Finally, the one-to-many relationshipbetween the Products and Order Details table means that each product can appear inmany order details.One-to-many relationships are modeled using foreign keys. For example, the Orders tablehas a column named CustomerID. This column is related to the CustomerID column inthe Customers table through a foreign key. This means that every row in the Orders tablemust have a corresponding row in the Customers table with a matching value for theCustomerID column. For example, if a row in the Orders table has a CustomerID ofALFKI, then there must also be a row in the Customers table with a CustomerID ofALFKI. Since the relationship between the Customers and Orders table is one-to-many,this means that there can be many rows in the Orders table with the same CustomerIDcolumn. Conceptually, you can think of the foreign key as a pointer from the Orders tableto the Customers table.Often, the table containing the foreign key is known as the child table, and the table withthe column referenced by the foreign key is known as the parent table. For example, theOrders table is the child table, and the Customers table is the parent table. Foreign keyrelationships are often known as parent-child relationships.Note The relational term from relational database comes from the fact that tables can be related to each other through foreign keys.You can manage the relationships for a table from Enterprise Manager by selecting thetable from the Tables node, clicking the right mouse button, and selecting Design Table.You then click the Manage Relationships button on the toolbar of the table designer. Forexample, Figure 2.12 shows the relationship between the Customers and Orders tables.Figure 2.12: Relationship between the Customers and Orders tableThe Customers and Orders tables are related through the CustomerID column. TheCustomerID column in the Orders table is the foreign key. The relationship between thetwo tables is named FK_Orders_Customers.Null ValuesDatabases must also provide the ability to handle values that are not set, or are otherwiseunknown. Unknown values are called null values, and a column is defined as allowing ordisallowing null values. When a column allows null values, that column is defined asnull; otherwise it is defined as not-null. A not-null column in a row must always havevalue stored in it. If you tried to add a row but didnt supply a value t ...

Tài liệu được xem nhiều: