The goal of this chapter is to show how you to build a sample database with Personal Oracle7. To illustrate the concepts and techniques described throughout this book, you will be looking at a sample database application for a repair store. Frayed Wires is a friendly neighborhood repair store specializing in the renovation of most consumer electronics. Started in 1982 by Jim Helmholtz, the business of Frayed Wires has grown over the years and is long overdue for some automation. Jim performed a thorough search of existing off-the-shelf applications for his business, but not a single one met his requirements. As a result, Helmholtz has retained you, his database consultant, to develop an Oracle database to help him manage his business.
In this chapter I show you how to use the Navigator (in Personal Oracle7 for Windows 95) or the Object Manager (in Personal Oracle7 for Windows 3.11) to construct the sample database. Later in the book I'll show you how to use SQL*Plus to create these tables.
To understand Personal Oracle7, you need to be comfortable with the terminology used in this book, in Personal Oracle7 tools, and in Oracle documentation. Two terms--;database connection and database user--;merit special attention.
To connect to Oracle means to supply a valid username and password that is accepted by the Oracle database. A user can connect to an Oracle database using any of the Database Administration Tools. A user also can establish an Oracle connection with another tool such as Oracle Forms, Visual Basic, or PowerBuilder. The terms Oracle session, Oracle connection, and database connection all refer to the same thing: the tasks performed by a database user from the time the user successfully connects to an Oracle database until the time the same user disconnects.
Every database connection is established on behalf of a database user. The terms table owner, Oracle user, and Oracle account are used interchangeably. A table owner is the database user that owns a table. A table owner is always an Oracle user, but an Oracle user may or may not own any tables of his or her own.
For example, suppose that the marketing department of a corporation has its own Oracle database. An Oracle account named MKTG owns all the tables. Sally Jensen is the vice president of marketing and has an Oracle account named JENSENS. However, even though Sally Jensen has access to all the tables owned by MKTG, she owns no tables of her own.
Every table that exists in an Oracle database must have an owner. A sensible approach is to create an owner that corresponds to the organization rather than to any individual in the organization. If you think about it, an organization's data--;information that members of the organization create, modify, and use --;doesn't belong to any one person. Also, given the dynamics of organizations, people come and go, but the organization remains.
In the sample application, instead of making Jim Helmholtz the owner of the Oracle tables, you are going to create a new user named FRAYED_WIRES and have that Oracle account own all of the tables in the application. An organization responsible for the data in the tables is sometimes referred to as the data steward.
As you recall, Personal Oracle7 for Windows has the same functionality that exists in its bigger brethren such as the Oracle7 Server for the UNIX operating system. However, unlike its brethren, Personal Oracle7 is a single-user database, as the first word of its name indicates. But even though Personal Oracle7 is not designed to function as a database server, you can still define multiple users, each of which can own a set of tables.
As a first step in setting up the sample database, you are going to use User Manager to create an Oracle user, FRAYED_WIRES, which will own all the database objects in the application database.
First, invoke the Navigator from the Personal Oracle7 for Windows 95 program group. To see the existing users in the Local Database, just double-click the User folder. A list of the existing users appears on the right side of the Main window. To create the FRAYED_WIRES user, select the User folder and right-click New. The Navigator will display a window, prompting you for the username, the password, and the password confirmation. (See Figure 5.1.)
Creating the FRAYED_WIRES user with the Navigator. Click OK. The FRAYED_WIRES user appears in the list of existing users in the Local Database. (See Figure 5.2.)
The FRAYED_WIRES user appears in the list of users in the Local Database. You'll want to assign an existing database role to the FRAYED_WIRES user: RESOURCE. This role enables the FRAYED_WIRES Oracle account to create new tables and other database objects. To assign this role, select the FRAYED_WIRES user and right-click Properties. A window with two tab folders will appear; select the Role/Privilege tab. Select the Roles radio button. Select the role named RESOURCE and click < to assign the role to FRAYED_WIRES. (See Figure 5.3.)
These are the steps for creating a new user with User Manager:
Figure 5.4. Adding the FRAYED_WIRES user.
You've now created the Personal Oracle7 user FRAYED_WIRES, which will own all the database objects used in the sample application.
The tool that you use to create an Oracle table depends on the version of Personal Oracle7 that you are using. For Personal Oracle7 for Windows 95, you can use the Navigator to create or modify a table. For Personal Oracle7 for Windows 3.11, you may use Object Manager to create or modify a table. You can also use SQL*Plus to create and modify a table, as I explain in Chapter 8, "Creating and Modifying Tables."
The Navigator is an intuitive, graphical tool that makes it easy to create or modify an Oracle table. The Navigator's user interface for creating tables is very similar to that of the Object Manager in Personal Oracle7 version 7.1.4. However, the Navigator contains a Table Wizard that makes creating a table even easier for novices.
You will find the Object Manager icon in the Personal Oracle7 Windows group. When invoked, Object Manager will prompt you for a username and password. After the Personal Oracle7 database validates the username and password, Object Manager enables you to choose the type of database object--;tables, indexes, synonyms, views, or database links--;to be viewed, created, or modified.
NoteThe Navigator and Object Manager provide a subset of the capabilities that can be found in SQL*Plus. SQL*Plus is a character-oriented command processor that accepts all valid SQL statements. The graphical interfaces of both the Navigator and Object Manager are more intuitive than the SQL*Plus interface, and they simplify the creation of tables and other database objects.
A logical data model is a representation of both the data elements used by an enterprise and the relationships between those data elements. Like any model, a logical data model is an idealization of a real system. A model is only as useful as it is accurate. Like any real enterprise, a logical data model is dynamic rather than static. It needs to evolve as the enterprise upon which it is based changes.
One of the most common methodologies used for developing a logical data model is entity-relationship modeling. Entities are people, places, objects, or concepts. Each entity is described by a set of attributes. For example, an employer can view an employee as an entity described by a set of attributes, such as employee number, last name, first name, department number, and hire date. An automobile marketing manager is interested in an entirely different set of attributes for the same individual; for instance, the make, model, age, and color of the car that he or she currently owns. As you can see, business requirements drive the data model.
Entities don't exist in a vacuum--;you can define relationships between entities. These relationships help enforce business rules. In a repair store, a customer may bring in several items to be repaired. The information about the customer's repair can be grouped into two sets of facts. The first set consists of facts that are independent of the items to be repaired--;for example, the customer's name and address--;and are stored in the Repair_Header table. The second set is made up of facts that are directly related to the items to be repaired--;for instance, make, model, and condition--;and are stored in the Repair_Item table. Each repair is assigned a unique Repair ID. A relationship is defined between the Repair_Header and Repair_ Item tables so that the Repair_Item table cannot reference a Repair ID that doesn't exist in the Repair_Header table.
Why bother to develop a logical data model--;why not just jump right into database design? By developing a logical data model, you are forced to focus on an organization's data and its internal relationships without initially worrying about implementation details such as a column's datatype. You can think of the logical data model at a higher level of abstraction than you can think of the database design.
No book that deals with relational database systems can be considered complete unless it discusses the basic concepts of relational database theory. In a nutshell:
Every entity has a set of attributes that uniquely define an instance of that entity. This set of attributes is called the primary key. The primary key may be composed of a single attribute--;an employee is uniquely identified by a social security number--;or of several attributes--;a repair item is uniquely identified by the Repair ID and the item number. Sometimes, the attributes that compose the primary key are obvious; other times they are not. To test your understanding of the primary key you must look at existing data and also interview people who understand the way in which the organization operates. Don't rely solely on existing data to validate your understanding of the primary key.
A basic tenet of relational theory is that no part of the primary key can be null. If you think about that idea for a moment, it seems intuitive: The primary key must uniquely identify each row in an entity; therefore, if the primary key (or a part of it) is null, it wouldn't be able to identify anything. For example, a repair item that has an undefined item number cannot be identified or processed in any way.
According to relational theory, every entity has a set of attributes that uniquely identify each row in that entity. Relational theory also states that no duplicate rows can exist in a table, which is really just another way of saying that every table must have a primary key. This concept is referred to as data integrity. For example, the social security number for each current employee will be unique.
Tables are related to one another through foreign keys. A foreign key is one table column for which the set of possible values is found in the primary key of a second table. Referential integrity is achieved when the set of values in a foreign key column is restricted to the primary key that it references or to the null value. Once the database designer declares primary and foreign keys, enforcing data and referential integrity is the responsibility of the DBMS.
A key tenet of relational database theory is that a table has no implied ordering. The only way to know the order in which rows will be retrieved from a table is to specify the order. The concept of no implied order is powerful because it enables you to think abstractly about tables and, for the most part, to ignore the physical implementation of a database's structures.
As with a table's rows, a table's columns have no implied ordering. If you use SQL*Plus to describe a table, SQL*Plus returns the columns in the order in which they were created. You can, however, specify any order for retrieving columns. Also, you can modify a column's definition without affecting any of the other columns. For example, you can increase the width of a column without having to modify any of your existing table definitions or SQL statements. Taking care of the physical details related to the change is the job of Personal Oracle7. A relational database is said to provide logical data independence because column definitions are independent from one another.
Keep these concepts in mind as you examine the sample database that is used throughout the remainder of the book.
You've provided Helmholtz with your strategy: you'll use Personal Oracle7 as a prototyping tool to design a functioning database and "upsize" that database to the Oracle Workgroup Server for the actual implementation of the application.
Because he is quite meticulous, Helmholtz wants to keep track of changes to the database. Specifically, he wants to know who added or changed a record in each table and when the record was added or changed. Therefore, each table in the Frayed Wires database will contain the following information: Created_Date: the date and time when the row was created
Created_By: the Oracle user that created the row
Modified_Date: the date and time when the row was last modified
Modified_By: the Oracle user that last modified the row
What's a business without customers? Helmholtz values his customers and wants to maintain accurate information about them without being intrusive. He wants to track the following items: Customer ID
Last name
First name
Middle initial
Salutation
Street address
City
State
Zip code
Work telephone number
Home telephone number
Fax number
Earliest time to call
Latest time to call
Local time zone
Figure 5.5 demonstrates the use of the Navigator to create the Customer table. Figure
5.6 demonstrates the use of Object Manager to create the Customer table. (Please
refer to Chapter 8 for a more detailed discussion of Object Manager and the Navigator.)
Frayed Wires handles repairs for the major consumer electronics manufacturers (and for some obscure manufacturers, too). Helmholtz wants to be able to track the following information for each manufacturer that his business deals with: Manufacturer ID
Manufacturer name
Street address
City
State
Zip code
Telephone number
Fax number
As shown in Figure 5.7, you can create the Manufacturer table using Object Manager.
Frayed Wires handles repairs many different products, so Helmholtz wants to retrieve some details on these products. The following information is available for some, but not all, products: Product ID
Product name
Manufacturer ID (foreign key to Manufacturer table)
Description
Date of manufacture
Initial retail value
Current used value
Replacement product
As shown in Figure 5.8, you can create the Product table using Object Manager.
Although Helmholtz employs four full-time technicians and two part-time technicians at Frayed Wires, he sends some repair jobs to several outside repair depots. Jobs are sent out for various reasons. For example, if the customer's equipment is still under warranty, the work must be performed at an authorized service center. Also, a repair depot may specialize in repairing older equipment--;work that Helmholtz is no longer interested in performing.
After some explanation, Helmholtz indicates that he needs to keep track of the following (see Figure 5.9): Depot ID
Company name
Street address
City
State
Zip code
Telephone number
Fax number
Name of primary contact
As a conscientious employer, Helmholtz maintains accurate records on each employee who has worked at Frayed Wires. Each employee will be assigned a numeric code, designated as his or her employee ID. Key dates, such as the hire date and the termination date, if any, will be stored in the table. The Employee table will contain the following information: Employee ID
Last name
First nameMiddle initial
Street add
ess
City
State
Zip code
Home telephone number
Social security number
Date of birth
Hire date
Termination date
Figure 5.10 illustrates how to use Object Manager to create the Employee table.
According to Helmholtz, the typical repair scenario begins when a customer walks into Frayed Wires with a nonfunctioning electronic gadget cradled in his or her arms. Most customers don't want to proceed with a repair until they know how much the repair will cost. Often, a technician can't quote a repair price until the broken appliance has been inspected, either by an in-house technician or at a repair depot.
A customer may have more than one item to be repaired. Sometimes, when one appliance stops working, customers also bring in other broken appliances that they've put off having repaired.
The paper repair form (see Figure 5.11) can be represented in the database by two Oracle tables.
Because every repair has a set of facts that are independent of the actual items left for repair, you decide to create a separate Repair_Header table to contain elements that are common to all the items that a customer brings in for repair; these elements are the same regardless of whether the customer leaves 1 item or 100 items. The following data items will be stored in the Repair_Header table (see Figure 5.12): Repair ID
Customer ID (foreign key to Customer table)
Employee ID (foreign key to Employee table)
Deposit amount
Deposit method
Customer credit card number
Customer check number
If you look at the paper repair form that Frayed Wires currently uses, you'll see a section that deals with each item a customer is leaving for repair (or inspection). This portion of the form is referred to as the Repair Item. It contains the following information: Repair ID (foreign key to Repair_Header table)
Item number
Product ID
Manufacturer ID (with Product ID, foreign key to Product table)
Condition code (foreign key to Condition_Code table)
Serial number
Purchase date
Warranty expiration date
Estimated cost
Estimated completion date
Customer authorization status
Customer authorization date
Item status code (foreign key to Item_Status_Code table)
Depot ID (foreign key to Depot table)
Responsible employee ID (foreign key to Employee table)
Item location
Figure 5.13 demonstrates how to use Object Manager to create the Repair_Item table.
When a piece of equipment is sent to a repair depot for an estimate, the depot is responsible for providing a cost and schedule estimate to Frayed Wires. When a customer brings more than one item to be repaired, each item could be repaired in-house or sent to different repair depots. To model this process, the Depot_Estimate table must refer to both the Repair ID and the Item Number. The Depot_Estimate table holds the following data items: Repair ID (foreign key to Repair_Header table)
Item number
Depot ID (foreign key to Depot table)
Labor cost
Parts cost
Total cost
Estimated date for completion
Technician (foreign key to Employee table)
Telephone number
Figure 5.14 shows how to use Object Manager to create the Depot Estimate table.
As Jim Helmholtz has explained it to you, his business is not only labor-intensive but also communication-intensive. He means that many types of communications are taking place, for example:
Keeping track of all of these events is an incredibly error-prone and time-consuming process. To provide superior customer service, Helmholtz wants to streamline this facet of the operation by recording all activity associated with each repair job. His vision is that any technician can quickly look up the activity log for an open repair account without having to interrupt another technician.
Based on everyone's input, your design for the Repair_Log table consists of the following items: Repair ID
Employee ID (foreign key to Employee table)
Item number (with Repair ID, foreign key to Repair_Item table)
Depot ID (foreign key to Depot table)
Action code (foreign key to Action_Code table)
Employee comments
Figure 5.15 outlines the creation of the Repair_Log table using Object Manager.
In your effort to gather information, you've reviewed the existing paper forms that Frayed Wires uses. In addition, you've spoken with most of the potential users of the system that you're designing. You've discovered that the lead technician assigns an initial condition code to each item that a customer brings in for repair. This code serves several purposes. First, the lead technician records the condition of the item--;perhaps it is already damaged, dented, or scratched. Second, the lead technician can indicate what he or she thinks the problem might be--;perhaps a short has damaged a component--;and pass that information on to the technician assigned to the repair.
Based on your investigation, you've assembled a list of about fifteen condition codes that are frequently used. Helmholtz mentions that these codes haven't changed in four years. Then he proceeds to tell you that he's been thinking about adding a new code related to DAT tape drives--;something about the drive mechanism.
At this point, you realize that the application and database will be easier to
maintain if a table is created to store each condition code and its definition. (See
Figure 5.16.) Condition code
Description
The purpose of the Action_Code table is similar to that of the Condition_Code
table--;to simplify the maintenance of the application. The Action_Code table
(see Figure 5.17) is really a lookup table that consists of these two items: Action
code
Description
In addition to the Condition_Code and Action_Code lookup tables, a third lookup
table describes the current status of each item being repaired. In the Repair_Item
table, a code is used to indicate the stat us of an item. The codes include the following:
Code Description
DE The item has been sent to a repair depot for an estimate.
DR The item has been sent to a repair depot to be repaired.
WC Waiting for customer authorization to proceed with repair.
IH The item is being repaired in-house.
IC In-house repair is complete.
DC Depot repair is complete.
WP Waiting for customer to pick up the item.
To simplify the maintenance of the application, you include the following items in
your design for the Item_Status_Code table (see Figure 5.18): Item status code
Description
Use these steps as a guideline when you are designing a database:
