Section 3: Creation of the Database
In this section you will learn how to create a physical database based on the relational schema developed in Section 2. You may either use the schema you created, or download my Section 2 relational schema.
This section has four parts. First, we will check our relational schema to make sure that all the data types are as they should be. Second, we will create an empty Access database to hold our database. Third, we will use ERwin to generate a set of tables within the database that conform to our relational schema. Fourth, I we will see how we can use our very same E-R diagram to generate tables within Oracle.
Checking the Relational Schema
Before you go further, take a look at your relational schema to make sure that all of the data types and column names are as they should be. This is particularly important in working with Access as a target database, because ERwin aparently mis-handles some of the data types. For example, CHAR(2) is transformed into an Access BYTE datatype rather than the correct TEXT(2). Figure 3.1 shows the Relational Schema from section 2.
Figure 3.1 The Physical Relational Schema Model
The datatypes in this figure all appear to be appropriate.
Creating the Empty Access Database
The next step is to create an empty Access database into which ERwin will create tables. Do this by using Access itself to create a blank database, as shown in Figure 3.2.
Figure 3.2 Creating a new, blank database
Figure 3.3 Creating a new database
Create the database, but do not create any tables or other objects within it.
Now, exit Access. The database will remain as a file (Tutorial.mdb).
Generate a New Database
The final stage involves using ERwin to generate a new database based on the relational schema.
From the Tools menu, choose Forward Engineer/Schema Generation. You will see a window like that in Figure 3.4
Figure 3.4 Schema Generation
At this point, we will stick with the default settings and go right to the schema generation step. This window, however, is the place one could specify in some detail what kinds of objects will, or will not, be included in the schema generation process.
Click on Generate...
At this point you will see a window that prompts you for information necessary to connect to the Access database you just created, shown in Figure 3.5.
Figure 3.5 Acces Connection
You should type in ADMIN as the User Name, and Browse... to find the database you created in the previous step.
When you have made these two entries, click Connect
At this point, ERwin goes off and creates the tables and associated database objects. If all goes well, you will see a window such as the one shown in Figure 3.6, indicating the results of the schema generation.
Figure 3.6 Schema generation
Finally, go back and open up the Access database you created earlier. As Figure 3.7 indicates, your database has now been populated with tables that conform precisely to the database schema created in the previous section.
Congratulations! You may now proceed to add Forms, Queries, Macros, etc. to implement the rest of your database application (ERwin doesn't help you with that.)
Figure 3.7 The Access Database
Targeting a different DBMS
What if you really want to create an Oracle database, not an Access database? You may do so, based on the very same E-R diagram you created before. There are three steps: (1) Generate a new Physical Model with a different database as the chosen target, (2) Generate a database in a fashion similar to that described above.
Return back to the Logical E-R diagram developed in Section 1, shown in Figure 3.8.
Figure 3.8 The Logical database model
From the Tools menu, choose Derive New Model
You will see a window like the one shown in Figure 3.9.
Figure 3.9 Derive New Model
In the section labelled New Model Type, make sure Physical is chosen. In the section labelled Target Database choose, in place of Access 2000, Oracle 8x.
Click Next> . The window shown in Figure 3.10 displays the objects in the logical model that are to be included in the new physical model.
Figure 3.10 Derive Model
If the items in the Auto-Transform Logical Objects section are not checked, then check them. This will ensure that any many-to-many relationships are transformed into a new table and that subtypes properly inherit the primary keys of their supertypes.
Click Next>
You will now see a window dealing with logical to physical name conversion (Figure 3.11). Since our physical database is to have column and table names that are the same as the attribute and entity names in our E-R diagram, we need make no changes.
Figure 3.11 Logical to Physical Name Conversion
Click Finish.
You will now see (Figure 3.12) a new physical model with the same table and column names as before, but the datatypes have now been mapped to Oracle datatypes.
Figure 3.12 The Physical Data Model based on Oracle datatypes.
As before, choose Forward engineer/generate schema from the Tools menu.
At this point, to keep things simple, indicate that ERwin should generate only the tables, views, and the primary keys. In particular, un-check all the Triggers (Shown in Figure 3.13). Also, uncheck Create Procedure, which is one option in the Schema, View, and Table items shown in this figure. The simpler the schema, the more easily it will be created within Oracle.
Figure 3.13 Oracle Schema Generation Options
If you click on the Preview... button, you can see the SQL that ERwin will generate to create your tables in Oracle.
When you are ready, click Generate...
ERwin will prompt you for the necessary information to connect to the Oracle database (Figure 3.14). Here at PKI, you must type in your Oracle username, password, and connect string. Notice that this information is different from that you provided to connect to Access (as it should be).
Figure 3.14 Connecting to Oracle
With any luck, the database tables are now created within your Oracle account.
Figure 3.15 Oracle tables created.