Creating Database Schema Definitions - How to be a Successful Technical Architect for J2EE Applications

Creating Database Schema Definitions

Typically, database administrators use the data model to create relational database schemas for the rest of the team to use. And most database administrators use modeling tools to do the dirty work. Unfortunately, few open source tools for creating schemas are available. Although the process is a bit more involved than what I illustrate in this section, with the help of a qualified database administrator, you can create schemas using the following central algorithm:

1. Directly translate each entity into a table. All attributes of an entity become columns in the table. Explicitly define the primary key in each table.

2. Assign a foreign key in the child entity of each one-to-many relationship. Remember, a foreign key is the primary key of another entity that exists so that you can match data in one entity to another. For example, CUSTOMER_ID will appear as a foreign key in the ACCOUNT table so that you have a way to associate an account with a specific customer using a SQL join.

3. Rewrite each many-to-many relationship by adding an associative table and two one-to-many relationships. An associative table has a primary key that is made up of two foreign keys. For example, look back at figure 7.2 to see the many-to-many relationship between CUSTOMER and PRODUCT. This will get implemented by creating a new table (called CUSTOMER_LINE_ITEM, for example) that relates customers to products.

As an illustration, listing 7.1 translates the ProjectTrak data model from figure 7.6 into Oracle DDL.

Listing 7.1: ProjectTrak DDL for Figure 7.6

create table Project (Project_ID number primary key);

create table Project_Task
 (     Task_ID number primary key,
       Task_Name      varchar(50)    not null,
       Estimate_In_Hrs number,
       Completion_Pct number,
       Project_ID     number         not null,
       Resource_ID    number
 )

create table Resource (Resource_ID number primary key);

ALTER TABLE Project_Task
 ADD CONSTRAINT Project_FK
 FOREIGN KEY (Project_ID)
 REFERENCES Project(Project_ID);

ALTER TABLE Project_Task
 ADD CONSTRAINT Resource_FK
 FOREIGN KEY (Resource_ID)
 REFERENCES Resource(Resource_ID);