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: , , ,

Cube Farm

Cube FarmAnother common term heard around the office is “Cube Farm”. A cube farm is not necessarily a terrible thing, but they need to be planned properly.

Definition: An office filled with cubicles. This really boils down to a room full of people at desks with little more than a smattering of upholstery between them. Cubicles are typically composed of fabric, metal and press-board. Cubicles vary from the full six foot walls on three and a half sides, down to a scant four foot tall partition simply separating you from the next person, if only from the waist down.

When do cube farms work?
In my experience there are two key factors which make cube farms viable, and yes, even beneficial. First, the people within a close proximity are doing very similar tasks. A group of support technicians in a cube farm can generally feed off each other’s knowledge and offer a high quality of support.

The second factor that often plays into cube farm success is that traffic (both walk-in and phone) from outside people is kept to a minimum. Support is the exception to this, however a software developer in a cube farm with support people will be constantly distracted by the support chatter and walk in questions.

To be effective cubes must be planned around the teams and workers they are meant to be occupied by. Cubes put up with little planning just to create a place for employees to work rarely if ever benefit their residents.

When do cube farms fail?
As mentioned above, cube farms often fail due to lack of planning. I am currently working in a cube farm where four different people serve three different functions, all in about 200 square feet.

Another key factor is the height and coverage of the cube. A six foot tall cube wall which completely blocks direct view of coworkers is best. This typically means cubes are almost completely enclosed with just enough space to enter and exit. Cubes with short walls that allow you to see others over them are generally ineffective, and having only one or two walls is not much better than having none.

The two biggest wastes of time in the office are visible distractions and audible distractions. Good cube farms with high walls, workers who spend a minimum of time on the phone, and in an area where foot traffic is minimal can work out very well. Cube farms which are small, poorly laid out, and with no consideration given to the function of the occupants will result in lost productivity, irritability and personal conflicts.

For more information on cube farms check out wikipedia.com’s article on the topic.

Technorati tags: , , ,

Yak Shaving

I picked this term up from Make Magazine, which had an interesting article about yak shaving in Volume 1. Sadly I find it all too applicable in my everyday life.

Definition: Yak shaving refers to a situation where you need to solve a problem, which may seem frivolous, but is necessary so you can solve a problem, so you can solve a problem, so you can solve a problem, so you can solve the actual problem you set out to solve in the first place.

The number of levels of recursion are not important, but I’d say anything less than 3 does not even qualify as yak shaving. Sometimes it is possible to end up in a recursive loop such that without backing out and making a new start you will never accomplish the intended task.

Example: I need to patch a database with the latest patchset from Oracle. First I need to download it from Oracle. To do that I need an account there. I have forgotten the password to my account so I need to make a new one. When I try to make a new one it tells me there is already an account with my email address. Now I need to find out our account representative’s name so I can have them reset my password. Since we’re part of a larger university system I need to call the DBA who handles our accounts, and of course I don’t know who they are so I have to ask around to get that. Then I call the DBA who handles our Oracle contract and they give me the contact information for our Oracle sales rep. I call them and they say they cannot help me because I am not on their records as the contact person for our contract. I then have to call the system level person back and talk them into calling the rep themselves… etcetera.

Origin: The term Yak Shaving is generally considered to be a reference to a Ren and Stimpy short called “Yak Shaving Day”. For a full explanation of Yak Shaving Day, check out this Wikipedia entry on the subject.

Technorati tags: , , ,

Telecommuting Web Resources

With a fairly long commute (45 miles each way) and gas above $3 per gallon I have been doing a little homework on telecommuting, also known as telework.

The Office of Personnel Management and the General Services Administration have some extensive telework resources for federal employees. The site www.telework.gov is full of great information including resources for identifying good candidates for telework, maintaining a good working environment when some employees telework and others do not, and evaluating the performance of teleworkers.

More pertinent to higher education, the University of Minnesota has much of their telecommuting policies and agreements available online at this page including a supervisor’s toolkit for implementing telecommuting. They outline the following potential benefits of telecommuting:

Society:
• Supports the Clean Air Act/Reduces air pollution
• Reduces traffic congestion, fuel consumption
• Supports the Americans with Disabilities Act
• Provides more job opportunities for the disabled, part-time, and semi-retired
University Units:
• Enhances employee productivity and work quality
• Increases long-term recruiting, retention, and loyalty of employees
• Improves employee morale and job satisfaction
• Increases workforce diversity by widening of labor pool
• Reduces overhead costs, especially in capital investments
• Reduces employee sick leave and absenteeism
• Enhances employer image in partnership with 21st century
• Enables employees to work during weather emergencies
Employee:
• Enhances job productivity and work quality
• Improves morale and job satisfaction
• Provides greater degree of responsibility
• Provides greater lifestyle flexibility in meeting family and job needs
• Reduces commuting time and stress
• Reduces transportation costs
• Provides satisfaction from greater employer trust

Of course there are more resources out there, but I believe these two are a good starting point for any employer or employee.

While my institution does not recognize telecommuting, well, let’s just say I’ve still been doing a lot of thinking about it. Hey, it’s better than looking for another job…

Technorati tags: , , , , , , ,