Lab (1) - Oracle Introduction Lab
CS 3430 -  Fall 2001 DBMS

To run Oracle, you need to log into the sc machine.  Your login and password on sc is the same as the one on cs.  Once you log into that machine, you need to modify the .cshrc file in order to be able to run Oracle.

Step (0): Getting started for the first time

You need to go through this step only once.  The following code needs to be in your .cshrc file:

# ----- Oracle Setup -----
setenv PATH_TEST_RESULTS `echo $PATH|grep "\/usr\/local\/bin"`
if ($PATH_TEST_RESULTS == "") then
   setenv PATH /usr/local/bin:$PATH
endif
set ORAENV_ASK = NO
set ORACLE_SID = csos
source /usr/local/bin/coraenv
alias sql+ 'sqlplus " "'
# ----- End Oracle Setup ---
To make sure the new changes are in place, log out.

Step (2): Getting started from now on

Oracle Description
The Oracle SQL*Plus prompt is "SQL>". SQL*Plus is the environment into which SQL is embedded. In addition to the SQL commands we have learned. SQL*Plus includes commands for formatting output, editing commands, running commands, interacting with Unix, etc. You have a handout that describes many SQL*Plus commands. In addition to SQL and SQL*Plus, Oracle provides a programming language called PL/SQL that allows you to embed SQL commands in larger procedures (we will not cover PL/SQL).

The SQL*Plus interpreter has a command buffer that holds the command that you are currently entering or the previous command. The buffer can hold commands that consist of multiple lines. The handout describes how to manipulate the command buffer. The buffer can be executed, modified, saved as a Unix file, loaded from a Unix file, etc.

Sample Database

In order to have a DB in place, one needs to go through some steps.  The first thing we need to do is to create the entities (tables).  These entities are empty to begin with, thus at the next stage, we will populate (insert data) them.  Sometimes, we may need to remove a tuple from a table or delete the table entirely.

Creating Tables
In this lab, we will design the COMPANY database described on Figure (7.5).  In order to create the EMPLOYEE relation, we will type at the command prompt:

CREATE TABLE employee (
  fname varchar2(20),
  minit char,
  lname varchar2(20),
  ssn char(9),
  bdate date,
  address varchar2(50),
  sex char,
  salary number(8,2),
  superssn char(9),
  dno number(2),
  CONSTRAINT employee_ssn_pk PRIMARY KEY (ssn),
  CONSTRAINT employee_superssn_fk FOREIGN KEY (superssn) REFERENCES employee(ssn)
);

Go through the above code line-by-line and write what each lone is doing.  As you can see creating a relation from the command prompt is not easy. The same way we can create the other relations:

CREATE TABLE department (
  dname varchar2(20),
  dnumber number(2),
  mgrssn number(9),
  mgrstartdate date,
  CONSTRAINT department_dnumber_pk PRIMARY KEY (dnumber),
  CONSTRAINT department_dname_uk UNIQUE (dname),
  CONSTRAINT department_mgrssn_fk FOREIGN KEY (mgrssn) REFERENCES employee(ssn)
);

CREATE TABLE dept_locations (
  dnumber number(2),
  dlocation varchar2(20),
  CONSTRAINT dept_loc_dnum_dloc_pk PRIMARY KEY (dnumber, dlocation),
  CONSTRAINT dept_locations_dnumber_fk FOREIGN KEY (dnumber) REFERENCES department(dnumber)
);

CREATE TABLE project (
  pname varchar2(20),
  pnumber number(2),
  plocation varchar2(20),
  dnum integer,
  CONSTRAINT project_pnumber_pk PRIMARY KEY (pnumber),
  CONSTRAINT project_pname_uk UNIQUE (pname),
  CONSTRAINT project_dnum_fk FOREIGN KEY (dnum) REFERENCES department(dnumber)
);

CREATE TABLE works_on (
   essn char(9),
   pno number(2),
   hours number(5,2),
   CONSTRAINT works_on_essn_pno_pk PRIMARY KEY (essn, pno),
   CONSTRAINT works_on_essn_fk FOREIGN KEY (essn) REFERENCES employee(ssn),
   CONSTRAINT works_on_pno_fk FOREIGN KEY (pno) REFERENCES project(pnumber)
);

CREATE TABLE dependent (
   essn char(9),
   dependent_name varchar2(20),
   sex char,
   bdate date,
   relationship varchar(10),
   CONSTRAINT dependent_essn_dname_pk PRIMARY KEY (essn, dependent_name),
   CONSTRAINT dependent_essn_fk FOREIGN KEY (essn) REFERENCES employee(ssn)
);

ALTER TABLE employee ADD (
   CONSTRAINT employee_dno_fk FOREIGN KEY (dno) REFERENCES department(dnumber)
);

At the sql> prompt, type DESCRIBE <table name> to see the schemas of each table.

Populating a Table
Question:
Write the command to insert James E. Borg with SSN = 888665555 into the EMPLOYEE table.  James was born on Nov. 10, 1927.  His address is Houston, TX, he makes $55000/year and is the president of the department 1.

The following command will create one tuple in the EMPLOYEE table:
insert into employee values
('James', 'E', 'Borg', 888665555, '10-NOV-27', '450 Stone, Houston, TX', 'M', 55000.00, null, 1);

You can add more tuples by repeating this command.  You also can include a set of commands in a file and run it to populate the table.  Do you know how?

Here is the list of some other insert commands for different tables. Note that if you have already inserted a tuple and attempt to insert the same tuple again, you will violate the integrity constraint of the relation.

insert into department values
  ('Headquarters', 1, null, '19-JUL-71');
insert into department values
  ('Administration', 4, null, '1-JAN-85');
insert into department values
  ('Research', 5, null, '22-May-78');

insert into employee values
   ('James', 'E', 'Borg', 888665555, '10-NOV-27', '450 Stone, Houston, TX', 'M', 55000.00, null, 1);
insert into employee values
   ('Franklin', 'T', 'Wong', 333445555, '8-DEC-45', '638 Voss, Houston, TX', 'M', 40000.00, 888665555, 5);
insert into employee values
   ('Jennifer', 'S', 'Wallace', 987654321, '20-JUN-31', '291 Berry, Bellaire, TX', 'F', 43000.00, 888665555, 4);
insert into employee values
   ('Ahmad', 'V', 'Jabbar', 987987987, '29-MAR-59', '980 Dallax, Houston, TX', 'M', 25000.00, 987654321, 4);
insert into employee values
   ('John', 'B', 'Smith', 123456789, '9-JAN-55', '731 Fondren, Houston, TX', 'M', 30000.00, 333445555, 5);
insert into employee values
   ('Ramesh', 'K', 'Narayan', 666884444, '15-SEP-52', '975 Fire Oak, Humble, TX', 'M', 38000.00, 333445555, 5);
insert into employee values
   ('Joyce', 'A', 'English', 453453453, '31-JUL-62', '5631 Rice, Houston, TX', 'F', 25000.00, 333445555, 5);
insert into employee values
   ('Alicia', 'J', 'Zelaya', 999887777, '19-JUL-58', '3321 Castle, Spring, TX', 'F', 25000.00, 987654321, 4);

update department
   set mgrssn = 33344555
   where dnumber = 5;

update department
   set mgrssn = 987654321
   where dnumber = 4;

update department
   set mgrssn = 888665555
   where dnumber = 1;

insert into project values
   ('ProductX', 1, 'Bellaire', 5);
insert into project values
   ('ProductY', 2, 'Sugarland', 5);
insert into project values
   ('ProductZ', 3, 'Houston', 5);
insert into project values
   ('Computerization', 10, 'Stafford', 4);
insert into project values
   ('Reorganization', 20, 'Houston', 1);
insert into project values
   ('Newbenefits', 30, 'Stafford', 4);

insert into works_on values
   (123456789, 1, 32.5);
insert into works_on values
   (123456789, 2, 7.5);
insert into works_on values
   (333445555, 2, 10);
insert into works_on values
   (333445555, 3, 10);
insert into works_on values
   (333445555, 10, 10);
insert into works_on values
   (333445555, 20, 10);
insert into works_on values
   (453453453, 1, 20);
insert into works_on values
   (453453453, 2, 20);
insert into works_on values
   (666884444, 3, 40);
insert into works_on values
   (987654321, 20, 15);
insert into works_on values
   (987654321, 30, 20);
insert into works_on values
   (987987987, 10, 35);
insert into works_on values
   (987987987, 30, 5);
insert into works_on values
   (999887777, 10, 10);
insert into works_on values
   (999887777, 30, 30);
insert into works_on values
   (888665555, 20, null);

insert into dependent values
   (123456789, 'Alice', 'F', '31-DEC-78', 'Daughter');
insert into dependent values
   (123456789, 'Elizabeth', 'F', '5-MAY-57', 'Spouse');
insert into dependent values
   (123456789, 'Michael', 'M', '1-JAN-78', 'Son');
insert into dependent values
   (333445555, 'Alice', 'F', '5-APR-76', 'Daughter');
insert into dependent values
   (333445555, 'Joy', 'F', '3-MAY-48', 'Spouse');
insert into dependent values
   (333445555, 'Theodore', 'M', '25-OCT-73', 'Son');
insert into dependent values
   (987654321, 'Abner', 'M', '29-FEB-32', 'Spouse');

insert into dept_locations values
   (1, 'Houston');
insert into dept_locations values
   (4, 'Stafford');
insert into dept_locations values
   (5, 'Bellaire');
insert into dept_locations values
   (5, 'Houston');
insert into dept_locations values
   (5, 'Sugarland');

Try Some Queries

Type:
    SELECT fname, lname
    FROM EMPLOYEE
    WHERE fname = 'James';

Try at least two more SELECT queries on your own.

Homewrok - Due: Friday November 9
Write and run Query (1), Query (2), and Query (4) in section 7.6 of Chapter(7).  Submit the queries and the results in one file as assgn5.  Submit a hardcopy at class time on Friday.