ER Diagramming Tools

While investigating tools for generating ER Diagrams for the data warehouse I’m working on I came across this good article on databasejournal.com which compares three of the top CASE (computer aided software engineering) tools for Oracle.

In the article Steve Callan quickly highlights the differences between Microsoft Visio 2003, Oracle Designer and AllFusion ERwin Data Modeler.

What can we take home from this article? Well, Microsoft Visio is (relatively) cheap at $499 and has good reverse engineering abilities, but won’t write database creation code for you.

Oracle Designer is clumsy, but since it’s bundled with Internet Developer Suite you might already own it; otherwise you’re probably not likely to justify the $5,000 cost of entry.

ERwin really seems like the Cadillac solution for database modeling. The $3,995 price tag will put off most, but like they say in the auto industry, if you’re worried about gas mileage you’re not ready for a Cadillac.

Check out the full article for details. Also check out the Oracle section at DatabseJournal.com for some nice series on Oracle related topics.

oracle, database, rdbms, dbms, oracle database, database management, database design, dba, database administration, er diagramming, case, software engineering

SQL*Plus Substitution Variables

This tutorial from the Oracle Technology Network outline the use of the three types of variables available in SQLPlus. Bind variables, substitution variables and system variables are all explained in this succinct yet thorough tutorial. The best I’ve seen on the subject.

SQL*Plus Substitution Variables from Oracle Technology Network.

For information on other Oracle web resources, check out my other article on the topic.

oracle, dba, database, database administrator, rdbms, database development, database design

PL/SQL Tutorials and Reference

After some web searching I found these two resources on PL/SQL. Of course there are many others, but these two seem fairly good without being overly complicated.

Using Oracle PL/SQL from Stanford University

PL/SQL Reference & Tutorial from Elliot Spencer’s web site

While neither of these sites display the polish of a site like w3schools.com they both have great, well organized information.

For more of my favorite Oracle resources, check out my previous article on Oracle Web Resources

oracle, sql, plsql, dba, database administration, database, database design

Table Creation Examples

Here is a simple example of the SQL used to create a couple tables, populate them, create a view, and grant access to view them.

Create the two tables:

create table name
(
id number not null,
first_name varchar2(30),
last_name varchar2(30) not null,
constraint pk_name primary key (id)
);

create table office_location
(
name_id number not null,
room_number varchar2(6 byte),
building varchar2(20 byte),
constraint person_exists foreign key(name_id)
references name,
constraint room_number_check check (room_number between 0 and 799)
);

Insert some values into each:

insert into name (id, first_name, last_name)
values (1, 'Jon', 'Emmons');

insert into name (id, first_name, last_name)
values (2, 'Matt', 'Batchelder');

insert into name (id, first_name, last_name)
values (3, 'Zach', 'Tirrell');

insert into name (id, first_name, last_name)
values (4, 'Jon', 'Graton');

insert into office_location (name_id, room_number, building)
values (1, '334', 'Hyde');

insert into office_location (name_id, room_number, building)
values (2, '334', 'Hyde');

insert into office_location (name_id, room_number, building)
values (3, '222', 'Hyde');

insert into office_location (name_id, room_number, building)
values (4, '333', 'Hyde');

Extract some meaningful data with a simple join:

select first_name, last_name, building, room_number
from name, office_location
where id=name_id;

Create a view based on that join:

create view name_office as
(
select first_name, last_name, building, room_number
from name, office_location
where id=name_id
);

Select from our new view:

select * from name_office;

Grant access to jemmons to the tables and view (note that you do not have to grant access to the underlying tables for a user to be able to select from the view):

grant select on name to jemmons;

grant select on office_location to jemmons;

grant select on name_office to jemmons;

Technorati tags: , , ,