Planning the Database Schema

Next, you must devise a schema plan. To do that, you decompose the E-R diagram into the following database tables:

• Customers

• Orders

• LineItems

• StockItems

For example, you assign Customer attributes to columns in the table Customers.

Figure 5-3 depicts the relationships between tables. The E-R diagram showed that a line item has a relationship with a purchase order and with a stock item. In the schema plan, you establish these relationships using primary and foreign keys.

A primary key is a column (or combination of columns) whose values uniquely identify each row in a table. A foreign key is a column (or combination of columns) whose values match the primary key in some other table. For example, column PONo in table LineItems is a foreign key matching the primary key in table Orders. Every purchase order number in column LineItems.PONo must also appear in column Orders.PONo.

Figure 5-3 Schema Plan for Purchase Order Application

---