Database Management Systems:Designing Relational Databases.

Designing Relational Databases

This section examines the steps involved in creating a relational database. Keep in mind that database design is a portion of a much larger systems development process that involves extensive analysis of user needs, which are not covered at this time. That body of material is the subject of Chapters 13 and 14. Thus, our starting point is one that normally follows considerable preliminary work that has identified in detail the key elements of the system under development. With this backdrop, the focus will be on the following six phases of database design, which are collectively known as view modeling:

1. Identify entities.

2. Construct a data model showing entity associations.

3. Add primary keys and attributes to the model.

4. Normalize the data model and add foreign keys.

5. Construct the physical database.

6. Prepare the user views.

IDENTIFY ENTITIES

View modeling begins by identifying the primary entities of the business function in question. Recall that entities are things about which the organization wishes to capture data. To demonstrate entity identification, we will analyze the following key features of a simplified purchasing system:

1. The purchasing agent reviews the inventory status report (Figure 9-13) for items that need to be reordered.

2. The agent selects a supplier and prepares an online purchase order.

3. The agent prints a copy of the purchase order (Figure 9-18a) and sends it to the supplier.

4. The supplier ships inventory to the company. Upon its arrival, the receiving clerk inspects the inven- tory and prepares an online receiving report (Figure 9-18b). The computer system automatically updates the inventory records.

Entities are represented as nouns in a system description. A number of candidate entities can be identified in the previous description: Purchasing Agent, Receiving Clerk, Inventory, Supplier, Inventory Status Report, Purchase Order, and Receiving Report. Not all of these candidates are true entities that need to be modeled in the database. To pass as valid entities, two conditions need to be met:

Condition 1. An entity must consist of two or more occurrences.

Condition 2. An entity must contribute at least one attribute that is not provided through other entities.

We need to test these conditions for each candidate to eliminate any false entities.

PURCHASING AGENT. Assuming that the organization has only one purchasing agent, then the Purchasing Agent candidate fails Condition 1. If, however, more than one agent exists, Condition 1 is met but Condition 2 may be a problem. If we assume that an Employee table already exists as part of a human

resources or payroll system, then basic data about the agent as an employee are captured in that table. We need to determine what data about the agent that is unique to his or her role of order placing need to be captured. Note that we are not referring to data about the order, but data about the agent. Because we have no information on this point in our brief description of the system, we will assume no agent-specific data are captured. Hence, the Purchasing Agent candidate is not an entity to be modeled.

RECEIVING CLERK. The previous argument applies also to the Receiving Clerk entity. We will assume that no clerk-specific data need to be captured that require a dedicated table.

INVENTORY. The Inventory entity meets both conditions. The description suggests that the organization holds many items of inventory; thus this entity would contain multiple occurrences. Also, we can logically assume that the attributes that define the Inventory entity are not provided through other tables. The Inventory entity is, therefore, a true entity that will need to be modeled.

SUPPLIER. The description states that multiple vendors supply inventory; hence the Supplier entity meets the first condition. We can also assume that it meets the second condition since no other entity would logically provide supplier data. The Supplier entity, therefore, will be included in the data model.

INVENTORY STATUS REPORT. The Inventory Status Report is a user view derived from the Inventory and Supplier entities (see Figure 9-15). While it contains multiple occurrences, it is not an entity because it does not satisfy Condition 2. The view is derived entirely from existing entities and provides no additional data that require a separate entity. The view will be carefully analyzed, however, to ensure that all the attributes needed for it are included in the existing entities.

PURCHASE ORDER. The Purchase Order entity meets both conditions. Many purchase orders will be processed in a period; thus the entity will have many occurrences. While some purchase order data can be derived from other entities (Inventory and Supplier) in the model, some attributes unique to the purchase event such as order date and order quantity will require a separate entity that needs to be modeled.

RECEIVING REPORT. The Receiving Report meets both conditions. Many receipt events will take place in the period; thus a Receiving Report entity will have multiple occurrences. A Receiving Report entity will contain attributes such as date received and quantity received that are unique to this entity and thus not provided by other entities in the model.

At this point our search has revealed four entities: Inventory, Supplier, Purchase Order, and Receiving Report. These will be used to construct a data model and, ultimately, the physical database tables.

CONSTRUCT A DATA MODEL SHOWING ENTITY ASSOCIATIONS

The next step in view modeling is to determine the associations between entities and document them with an ER diagram. Recall that associations represent business rules. Sometimes the rules are obvious and are the same for all organizations. For example, the normal association between a Customer entity and a Sales Order entity is 1:M (or 1:0,M). This signifies that one customer may place many orders during a sales period. The association would never be 1:1. This would mean that the organization restricts each customer to a single sale, which is illogical.

Sometimes the association between entities is not apparent because different rules may apply in different organizations. To reiterate an important point made earlier, the organization’s business rules directly impact the structure of the database tables. If the database is to function properly, its designers need to understand the organization’s business rules as well as the specific needs of individual users. Figure 9-19 illustrates the entity associations in our example. The underlying business rules are explained next.

1. There is a 0,M:M association between the Purchase Order and Inventory entities. This means that each inventory item may have been ordered many times or never ordered in the current business

period. Obviously, every inventory item must have been purchased at least once in the past, so why do we show a 0,M cardinality for the Purchase Order entity? We must keep in mind that transaction entities, such as sales and purchases, are associated with a particular time frame. We will assume that the Purchase Order table for this system will contain records for purchases made in the current period only. Closed purchase orders of past periods will have been removed to an archive table, which is not shown in our example.

2. There is an M:M association between the Inventory and Supplier entities. This means that one or more vendors supply each inventory item, and each of them supplies one or more items of inventory.

3. There is a 1:0,M association between the Supplier and the Purchase Order entities. This means that in the current period, each supplier may have received zero or many purchase orders, but each order goes to only one supplier.

4. There is a 1:1 association between the Purchase Order and Receiving Report entities. A single receiving report record reflects the receipt of goods that are specified on a single purchase order record. Multiple purchase orders are not combined on a single receiving report.

5. The association between the Receiving Report and Inventory entities is 0,M:M. This signifies that within the period, each item of inventory may have been received many times or never. Also, each receiving report is associated with at least one and possibly many inventory items.

The many-to-many (M:M and 0,M:M) associations in the data model need to be resolved before the physical databases can be created. We know from previous discussion that these associations signify a missing entity that is needed to link them. We will resolve these problems during the normalization process.

ADD PRIMARY KEYS AND ATTRIBUTES TO THE MODEL

ADD PRIMARY KEYS. The next step in the process is to assign primary keys to the entities in the model. The analyst should select a primary key that logically defines the nonkey attributes and uniquely identifies each occurrence in the entity. Sometimes this can be accomplished using a simple sequential code such as an Invoice Number, Check Number, or Purchase Order number. Sequential codes, however, are not always efficient or effective keys. Through careful design of block codes, group codes, alphabetic codes, and mnemonic codes, primary keys can also impart useful information about the nature of the entity. These techniques are discussed in detail in Chapter 2. Figure 9-20 presents the four entities in the model with primary keys assigned.

ADD ATTRIBUTES. Every attribute in an entity should appear directly or indirectly (a calculated value) in one or more user views. Entity attributes are, therefore, originally derived and modeled from user views. In other words, if stored data are not used in a document, report, or a calculation that is reported in some way, then it serves no purpose and should not be part of the database. The attributes assigned to each entity in Figure 9-21 are derived from the user views of the Purchase Order and Receiving Report illustrated in Figure 9-18 and from the Inventory Status Report that we previously normalized.

NORMALIZE DATA MODEL AND ADD FOREIGN KEYS

Figure 9-22 presents a normalized data model. The normalization issues that needed resolution are out- lined in the following section:

1. Repeating Group Data in Purchase Order. The attributes Part Number, Description, Order Quantity, and Unit Cost are repeating group data. This means that when a particular purchase order contains more than one item (most of the time), then multiple values will need to be captured for these attributes. To resolve this, these repeating group data were removed to a new PO Item Detail entity. The new entity was assigned a primary key that is a composite of Part Number and PO Number. The creation of the new entity also resolved the M:M association between the Purchase Order and Inventory entities by providing a link.

2. Repeating Group Data in Receiving Report. The attributes Part Number, Quantity Received, and Condition Code are repeating groups in the Receiving Report entity and were removed to a new

entity called Rec Report Item Detail. A COMPOSITE KEY composed of PART NUMBER and REC REPT NUMBER was assigned. As in the previous example, creating this new entity also resolved the M:M association between Receiving Report and Inventory.

3. Transitive Dependencies. The Purchase Order and Receiving Report entities contain attributes that are redundant with data in the Inventory and Supplier entities. These redundancies occur because of transitive dependencies (see the appendix of this chapter) in the Purchase Order and Receiving Report entities and are dropped.

CONSTRUCT THE PHYSICAL DATABASE

Figure 9-23 illustrates the 3NF table structures for the database. The primary and foreign keys linking the tables are represented by dotted lines. The following points are worth elaboration.

Each record in the Rec Report Item Detail table represents an individual item on the receiving report. The table has a combined key comprising REC REPT NUMBER and PART NUMBER. This composite key is needed to uniquely identify the Quantity Received and Condition attributes of each item-detail record. The REC REPT NUMBER portion of the key provides the link to the Receiving Report table that contains general data about the receiving event. The PART NUMBER portion of the key is used to access the Inventory table to facilitate updating the Quantity on Hand field from the Quantity Received field of the Item-Detail record.

The PO Item Detail table uses a composite primary key of PO NUMBER and PART NUMBER to uniquely identify the Order Quantity attribute. The PO NUMBER component of the composite key pro- vides a link to the Purchase Order table. The PART NUMBER element of the key is a link to the Inventory table where Description and Unit Cost data reside.

The next step is to create the physical tables and populate them with data. This is an involved step that must be carefully planned and executed and may take many months in a large installation. Programs will need to be written to transfer organization data currently stored in flat files or legacy databases to the new relational tables. Data currently stored on paper documents may need to be entered into the database tables manually. Once this is done, the physical user views can be produced.

PREPARE THE USER VIEWS

The normalized tables should be rich enough to support the views of all users of the system being modeled. For example, the PO in Figure 9-23, which could be the data entry screen for a purchasing clerk, has been constructed from attributes located in several tables. To illustrate the relationship, the fields in the user view are cross-referenced via circled numbers to the attributes in the supporting tables. Keep in mind that these tables may also provide data for many other views not shown here, such as the receiving report, purchase requisition listing, inventory status report, and vendor purchases activity report.

The query function of a relational DBMS allows the system designer to easily create user views from tables. The designer simply tells the DBMS which tables to use, their primary and foreign keys, and the attributes to select from each table. Older DBMSs require the designer to specify view parameters directly in SQL. Newer systems do this visually. The designer simply points and clicks at the tables and the attributes. From this visual representation, the DBMS generates the SQL commands for the query to produce the view.

The Receiving Report, Purchase Order, and Inventory Status Report views would all be created in this way. To illustrate, the SQL commands needed to produce the inventory status report illustrated in Figure 9-13 are given in the following section.

SELECT inventory.part-num, description, quant-on-hand, reorder-point, EOQ, part-supplier.part- num, part-supplier.supplier-number, supplier.supplier-number, name, address, tele-num, FROM in- ventory, part-supplier, supplier

WHERE inventory.part-num = part-supplier.part-num AND part-supplier.supplier-number = supplier.supplier-number AND quant-on hand ::: reorder-point.

• The SELECT command identifies all of the attributes to be contained in the view. When the same at- tribute appears in more than one table (for example, PART-NUM), the source table name must also be specified.

• The FROM command identifies the tables used in creating the view.

• The WHERE command specifies how rows in the Inventory, Part-Supplier, and Supplier tables are to be matched to create the view. In this case, the three tables are algebraically joined on the primary keys PART-NUM and SUPPLIER-NUMBER.

• Multiple expressions may be linked with the AND, OR, and NOT operators. In this example, the last expression uses AND to restrict the records to be selected with the logical expression quant-on- hand ::: reorder-point. Only records whose quantities on hand have fallen to or below their reorder points will be selected for the view. The user will not see the many thousands of other inventory items that have adequate quantities available.

These SQL commands will be saved in a user program called a query. To view the Inventory Status report, the purchasing agent executes the query program. Each time this is done, the query builds a new view with current data from the Inventory and Vendor tables. By providing the user with his or her personal query, rather than permitting access to the underlying base tables, the user is limited to authorized data only.

A report program is used to make the view visually attractive and easy to use. Column headings can be added, fields summed, and averages calculated to produce a hard-copy or computer screen report that resembles the original user report in Figure 9-13. The report program can suppress unnecessary data from the view, such as duplicated fields and the key values in the Inventory/Vendor link table. These keys are necessary to build the view, but are not needed in the actual report.

GLOBAL VIEW INTEGRATION

The view modeling process described previously pertained to only one business function—the purchases system—and the resulting tables and views constitute only a subschema of the overall database schema. A modern company, however, would need hundreds or thousands of views and associated tables. Com- bining the data needs of all users into a single schema or enterprise-wide view is called view integration. This is a daunting undertaking when creating the entire database from scratch. To facilitate this task, modern Enterprise Resource Planning (ERP) systems (discussed in Chapter 11) come equipped with a core schema, normalized tables, and view templates. These best-practices databases are derived from economic models that identify commonalities among the data needs of different organizations. For example, all organizations that sell products to customers will need an Inventory table, a Customer table, a Supplier table, and so forth. Many of the attributes and keys in these tables are also common to all organizations. Working from a core ERP database, the view modeling process thus becomes one of configuring or tailoring predefined views to accommodate specific user needs. ERP vendors cannot, however, anticipate the information needs of all users in advance. Therefore, new tables and new attributes may need to be added to the core schema. Although configuring the core database in this fashion is far more efficient than working from scratch, the objective is the same. The database designer must produce a set of integrated tables that are free of the update, insert, and deletion anomalies and sufficiently rich to serve the needs of all users.

In Chapter 10 we will examine in detail the resources events agents (REA) model that underlies many ERP database designs. Applying REA analysis to accounting information system solutions promotes an understanding of the relevant economic events and exchanges between trading partners. ERP vendors have thus been able to anticipate the information needs of both accounting and nonaccounting users and produce core databases that support multiple views for most organization types.