Creating the Database Tables From ER Model

Drawing the Entity-Relationship Diagram

The objective is to develop a simple system for managing customer purchase orders. First, you must identify the business entities involved and their relationships. To do that, you draw an entity-relationship (E-R) diagram by following the rules and examples given in Figure 1-1.

Figure 1-1 Rules for Drawing an E-R Diagram

As Figure 1-2 illustrates, the basic entities in this example are customers, purchase orders, line items, and stock items.

Figure 1-2 E-R Diagram for Purchase Order Application

A Customer has a one-to-many relationship with a Purchase Order because a customer can place many orders, but a given purchase order can be placed by only one customer. The relationship is optional because zero customers might place a given order (it might be placed by someone not previously defined as a customer).

A Purchase Order has a many-to-many relationship with a Stock Item because a purchase order can refer to many stock items, and a stock item can be referred to by many purchase orders. However, you do not know which purchase orders refer to which stock items.

Therefore, you introduce the notion of a Line Item. A Purchase Order has a one-to-many relationship with a Line Item because a purchase order can list many line items, but a given line item can be listed by only one purchase order.

A LineItem has a many-to-one relationship with a StockItem because a line item can refer to only one stock item, but a given stock item can be referred to by many line items. The relationship is optional because zero line items might refer to a given stock item.

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 1-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 1-3 Schema Plan for Purchase Order Application

Creating the Database Tables

Next, you create the database tables required by the schema plan. You begin by defining the table Customers, as follows:

CREATE TABLE Customers (

  CustNo   NUMBER(3) NOT NULL,

  CustName VARCHAR2(30) NOT NULL,

  Street   VARCHAR2(20) NOT NULL,

  City     VARCHAR2(20) NOT NULL,

  State    CHAR(2) NOT NULL,

  Zip      VARCHAR2(10) NOT NULL,

  Phone    VARCHAR2(12),

  PRIMARY KEY (CustNo)

);

The table Customers stores all the information about customers. Essential information is defined as NOT NULL. For example, every customer must have a shipping address. However, the table Customers does not manage the relationship between a customer and his or her purchase order. So, that relationship must be managed by the table Orders, which you define as:

CREATE TABLE Orders (

  PONo      NUMBER(5),

  Custno    NUMBER(3) REFERENCES Customers,

  OrderDate DATE,

  ShipDate  DATE,

  ToStreet  VARCHAR2(20),

  ToCity    VARCHAR2(20),

  ToState   CHAR(2),

  ToZip     VARCHAR2(10),

  PRIMARY KEY (PONo)

);

The E-R diagram in Figure 1-2 showed that line items have a relationship with purchase orders and stock items. The table LineItems manages these relationships using foreign keys. For example, the foreign key (FK) columnStockNo in the table LineItems references the primary key (PK) column StockNo in the table StockItems, which you define as:

CREATE TABLE StockItems (

  StockNo     NUMBER(4) PRIMARY KEY,

  Description VARCHAR2(20),

  Price       NUMBER(6,2))

);

The table Orders manages the relationship between a customer and purchase order using the FK column CustNo, which references the PK column CustNo in the table Customers. However, the table Orders does not manage the relationship between a purchase order and its line items. So, that relationship must be managed by the table LineItems, which you define as:

CREATE TABLE LineItems (

  LineNo    NUMBER(2),

  PONo      NUMBER(5) REFERENCES Orders,

  StockNo   NUMBER(4) REFERENCES StockItems,

  Quantity  NUMBER(2),

  Discount  NUMBER(4,2),

  PRIMARY KEY (LineNo, PONo)

);