What is a database. A database is an organized collection of data. The data can be textual, like order or inventory data, or it can be pictures, programs or anything else that can be stored on a computer in binary form.
A relational database stores the data in the form of tables and columns. A table is the category of data, like Employee, and the columns are information about the category, like name or address.
Some databases have minimal feature sets and only store data, while others include programming languages, facilities and utilities to support enterprise-level applications like ERP and data warehousing. Oracle is the #1 database and has the most advanced feature set.
Oracle is made up of a set of processes running in your operating system. These processes manage how data is stored and how it is accessed. I will cover these processes in detail in the future; but for now we just need to understand that Oracle is a program that is running in the background, maintaining your data for you and figuring out where it should go on your hard drive.
In almost all relational databases, data is accessed through SQL, or Structured Query Language, and Oracle is no exception. SQL allows you to SELECT your data, INSERT new records, UPDATE existing records and DELETE records you want to get rid of. SQL can be embedded in other languages or you can run scripts of SQL directly against the database.
PL/SQL is the procedural language extension to SQL. PL/SQL is a programming language like C, Java or Pascal. In the Oracle world, there is no better way to access your data from inside a program. SQL can be natively embedded in PL/SQL programs. I will be using both SQL and PL/SQL very heavily in my future articles.
PL/SQL is a feature-rich language geared toward developing database applications. PL/SQL is the procedural language of the database, but it is also the procedural language for most of Oracle's tools. Programs that run inside the database are called stored procedures. These stored procedures are almost always PL/SQL, but can be written in Java.
Some of Oracle's tools to access the database and create programs are:
SQL*Plus has a command line interface. With it, you can access the database and write stored procedures, you can run SQL commands to retrieve data and you can run scripts of either SQL, PL/SQL or built-in SQL*Plus commands, or a mixture of those three things.
Oracle Developer is a 4GL GUI application Builder. With Developer, you can create forms, reports, and graphics. Oracle*Forms and Oracle*Reports are two components of Oracle Developer. Earlier versions created client-server applications, but the more recent versions create web applications that run under the Oracle Application Server (OAS). OAS is a web-based application server sold by Oracle. OAS is licensed separately and is very expensive (as are its closed source competitors). The current version is OAS 10g.
HTML DB is a fairly new application builder geared toward web development (added to the DB with release 9iR2). HTML DB does not need an application server. This tool runs from the database and can be presented to the web using the Apache web server that comes with the database. Since this is not an additional license, it provides a cheaper way to develop applications. Developer is a feature-rich thick client with all of the normal GUI widgets. HTML DB is HTML-based and is very thin and limited to the HTML provided widgets.
Oracle Enterprise Manager (OEM) is the Enterprise GUI tool to manage the database. From this tool, you can perform any action in the database that is required. This tool is primarily used for administration but can also be provided to developers for application tuning and monitoring. In Oracle 10g, OEM also provides Grid control.
There are also a lot of third party tools for accessing the database. For our purposes, our primary tool for data access will be SQL*Plus. In the future, I will cover creating applications with HTML DB and accessing your data with some choice third-party tools.
How about Java? Java is the current hot language, especially on the web. I will also be covering Java in the database in future articles. Java is natively supported by Oracle. Technically, you could use Java to create your stored procedures if you chose, but I am not a proponent of that. When you are working inside the database, I believe that you should only resort to Java when you cannot accomplish a task in PL/SQL. As you'll see, there is very little you cannot accomplish with PL/SQL.
It will be hard to learn Oracle if you can't play with it. So where can you get access to this software? Oracle has a technical web site called OTN (Oracle Tech Net) that provides access to all of Oracle's software and all of the documentation for that software. There are also forums and many other tools to use to help you learn Oracle. You have to register to access it, but registration is free. Go to http://otn.oracle.com/ and get an ID today. In future articles, I will be spending a lot of time accessing this web site to download software and documentation. My next article will cover downloading Oracle 10g and installing it.
As a side note, Oracle provides all of their software with a developer's license. This license allows you to try out the software free of charge. You may not create production applications without a paid license, but development with the tools is acceptable.
Also, a note about versioning in Oracle; all products released by Oracle have a version. The current version of the database is 10g Release 1, also written as 10gR1. In addition to this semantic release name, each release has a numeric version. The numeric release for 10gR1 is 10.0.1.3.
http://www.suite101.com/welcome.cfm/oracle
As a team, we were asked to create a database for a hospital that would hold data for patient beds, case notes, expertise, medicine, patients, physicians and staff.
This is achieved with the above SQL code:
Beds:
ALTER TABLE KORELKO.BEDS
DROP PRIMARY KEY CASCADE;
DROP TABLE KORELKO.BEDS CASCADE CONSTRAINTS;
CREATE TABLE KORELKO.BEDS
(
BEDS_ID NUMBER NOT NULL,
PATIENT_ID NUMBER NOT NULL,
PATIENT_ROOM NUMBER NOT NULL,
BED_ROOM NUMBER
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX KORELKO.BEDS_PK ON KORELKO.BEDS
(BEDS_ID)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE OR REPLACE TRIGGER KORELKO."BI_BEDS"
before insert on "BEDS"
for each row
begin
select "BEDS_SEQ".nextval into :NEW.BEDS_ID from dual;
end;
/
ALTER TABLE KORELKO.BEDS ADD (
CONSTRAINT BEDS_PK
PRIMARY KEY
(BEDS_ID)
USING INDEX KORELKO.BEDS_PK);
ALTER TABLE KORELKO.BEDS ADD (
CONSTRAINT BEDS_CON_FK
FOREIGN KEY (PATIENT_ID)
REFERENCES KORELKO.PATIENT (PATIENT_ID));
Case note:
ALTER TABLE KORELKO.CASE_NOTE
DROP PRIMARY KEY CASCADE;
DROP TABLE KORELKO.CASE_NOTE CASCADE CONSTRAINTS;
CREATE TABLE KORELKO.CASE_NOTE
(
CASE_NOTE_ID NUMBER NOT NULL,
PATIENT_ID NUMBER NOT NULL,
TREATMENT VARCHAR2(4000 BYTE),
MEDICINE_ID NUMBER NOT NULL,
STAFF_ID NUMBER,
PHYSICIAN_ID NUMBER,
"date/time" TIMESTAMP(6)
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX KORELKO.CASE_NOTE_PK ON KORELKO.CASE_NOTE
(CASE_NOTE_ID)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE OR REPLACE TRIGGER KORELKO."BI_CASE_NOTE"
before insert on "CASE_NOTE"
for each row
begin
select "CASE_NOTE_SEQ".nextval into :NEW.CASE_NOTE_ID from dual;
end;
/
ALTER TABLE KORELKO.CASE_NOTE ADD (
CONSTRAINT CASE_NOTE_PK
PRIMARY KEY
(CASE_NOTE_ID)
USING INDEX KORELKO.CASE_NOTE_PK);
ALTER TABLE KORELKO.CASE_NOTE ADD (
CONSTRAINT CASE_NOTE_FK
FOREIGN KEY (MEDICINE_ID)
REFERENCES KORELKO.MEDICINE (MEDICINE_ID));
Expertise:
ALTER TABLE KORELKO.EXPERTISE
DROP PRIMARY KEY CASCADE;
DROP TABLE KORELKO.EXPERTISE CASCADE CONSTRAINTS;
CREATE TABLE KORELKO.EXPERTISE
(
EXPERTISE_ID VARCHAR2(50 BYTE) NOT NULL,
DESCRIPTION VARCHAR2(4000 BYTE) NOT NULL,
YEARS_OF_EXPERTISE NUMBER NOT NULL
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX KORELKO.EXPERTISE_PK ON KORELKO.EXPERTISE
(EXPERTISE_ID)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE OR REPLACE TRIGGER KORELKO."BI_EXPERTISE"
before insert on "EXPERTISE"
for each row
begin
select "EXPERTISE_SEQ".nextval into :NEW.EXPERTISE_ID from dual;
end;
/
ALTER TABLE KORELKO.EXPERTISE ADD (
CONSTRAINT EXPERTISE_PK
PRIMARY KEY
(EXPERTISE_ID)
USING INDEX KORELKO.EXPERTISE_PK);
Medicine:
ALTER TABLE KORELKO.MEDICINE
DROP PRIMARY KEY CASCADE;
DROP TABLE KORELKO.MEDICINE CASCADE CONSTRAINTS;
CREATE TABLE KORELKO.MEDICINE
(
MEDICINE_ID NUMBER,
MEDICINE_NAME VARCHAR2(4000 BYTE),
DESCRIPTION VARCHAR2(4000 BYTE)
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX KORELKO.MEDICINE_PK ON KORELKO.MEDICINE
(MEDICINE_ID)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE OR REPLACE TRIGGER KORELKO."BI_MEDICINE"
before insert on "MEDICINE"
for each row
begin
select "MEDICINE_SEQ".nextval into :NEW.MEDICINE_ID from dual;
end;
/
ALTER TABLE KORELKO.MEDICINE ADD (
CONSTRAINT MEDICINE_PK
PRIMARY KEY
(MEDICINE_ID)
USING INDEX KORELKO.MEDICINE_PK);
Patients:
ALTER TABLE KORELKO.PATIENT
DROP PRIMARY KEY CASCADE;
DROP TABLE KORELKO.PATIENT CASCADE CONSTRAINTS;
CREATE TABLE KORELKO.PATIENT
(
PATIENT_ID NUMBER(10) NOT NULL,
LAST_NAME VARCHAR2(50 BYTE),
FIRST_NAME VARCHAR2(50 BYTE),
AGE NUMBER(3),
ADDRESS VARCHAR2(150 BYTE),
PHONE_1 VARCHAR2(20 BYTE),
PHONE_2 VARCHAR2(20 BYTE),
ENTRY_DATE DATE NOT NULL,
EXIT_DATE DATE NOT NULL
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX KORELKO.PATIENT_PK ON KORELKO.PATIENT
(PATIENT_ID)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE OR REPLACE TRIGGER KORELKO."BI_PATIENT"
before insert on "PATIENT"
for each row
begin
select "PATIENT_SEQ".nextval into :NEW.PATIENT_ID from dual;
end;
/
ALTER TABLE KORELKO.PATIENT ADD (
CONSTRAINT PATIENT_PK
PRIMARY KEY
(PATIENT_ID)
USING INDEX KORELKO.PATIENT_PK);
Physicians:
ALTER TABLE KORELKO.PHYSICIAN
DROP PRIMARY KEY CASCADE;
DROP TABLE KORELKO.PHYSICIAN CASCADE CONSTRAINTS;
CREATE TABLE KORELKO.PHYSICIAN
(
PHYSICIAN_ID NUMBER NOT NULL,
SPECIALTY VARCHAR2(100 BYTE) NOT NULL,
PATIENT_ID NUMBER
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX KORELKO.PHYSICIAN_PK ON KORELKO.PHYSICIAN
(PHYSICIAN_ID)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE OR REPLACE TRIGGER KORELKO."BI_PHYSICIAN"
before insert on "PHYSICIAN"
for each row
begin
select "PHYSICIAN_SEQ".nextval into :NEW.PHYSICIAN_ID from dual;
end;
/
ALTER TABLE KORELKO.PHYSICIAN ADD (
CONSTRAINT PHYSICIAN_PK
PRIMARY KEY
(PHYSICIAN_ID)
USING INDEX KORELKO.PHYSICIAN_PK);
ALTER TABLE KORELKO.PHYSICIAN ADD (
CONSTRAINT PHYSICIAN_FK
FOREIGN KEY (PATIENT_ID)
REFERENCES KORELKO.PATIENT (PATIENT_ID));
Staff:
ALTER TABLE KORELKO.STAFF
DROP PRIMARY KEY CASCADE;
DROP TABLE KORELKO.STAFF CASCADE CONSTRAINTS;
CREATE TABLE KORELKO.STAFF
(
STAFF_ID NUMBER(38) NOT NULL,
LAST_NAME VARCHAR2(50 BYTE) NOT NULL,
FIRST_NAME VARCHAR2(50 BYTE) NOT NULL,
PHONE_1 VARCHAR2(20 BYTE) NOT NULL,
PHONE_2 VARCHAR2(20 BYTE) NOT NULL,
ADDRESS VARCHAR2(100 BYTE) NOT NULL,
HIRE_DATE DATE NOT NULL,
EXPERTISE_ID VARCHAR2(100 BYTE) NOT NULL
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX KORELKO.STAFF_PK ON KORELKO.STAFF
(STAFF_ID)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE OR REPLACE TRIGGER KORELKO."BI_STAFF"
before insert on "STAFF"
for each row
begin
select "STAFF_SEQ".nextval into :NEW.STAFF_ID from dual;
end;
/
ALTER TABLE KORELKO.STAFF ADD (
CONSTRAINT STAFF_PK
PRIMARY KEY
(STAFF_ID)
USING INDEX KORELKO.STAFF_PK);
ALTER TABLE KORELKO.STAFF ADD (
CONSTRAINT FK_EXP
FOREIGN KEY (EXPERTISE_ID)
REFERENCES KORELKO.EXPERTISE (EXPERTISE_ID));
By using Toad data modeler 3.5 we connected to our database and we was able to reproduce the schema and the included relations between the tables as shown below.
Relations.JPG
Tables relations Figure.
Our Username and Password (in Oracle 10g)
In order to create tables and forms in Oracle Database 10g Express edition we had to create a new personal username and a new password.
In this situation our username and password in Oracle Database 10g Express edition are:
USERNAME: Korelko
PASSWORD: Korelko
Forms Screenshots
Main screenshot
C:\Users\sprC\Desktop\Main Sceenshot1.jpg
In this main interface form, we have to give a correct username and password, in order to enter the database. After entering username and password then we press Login in order to have access. Also, under the username and password forms we have the opportunity to edit the page or the application.
Create patient Form
C:\Users\sprC\Desktop\Interface.JPG
In this Create patient form, we have the opportunity to create patients forms where we can add some patients elements such as: last name, first name, age, Address, Phone, etc. Furthermore, we can delete a patient by clicking delete button. By clicking back button we can go to the back form.
Add medicine â€" Add patient Form
C:\Users\sprC\Desktop\Add.JPG
Here we can choose if we want to add a medicine or to add a patient in the database. More specifically, by clicking add medicine we can enter add medicine form, and by clicking add patient button we have the opportunity to enter to add patient form. We can logout the interface by pressing the logout button on the top of the right side of the screen.
Create Physician elements Forms
C:\Users\sprC\Desktop\Find.JPG
In this create physician elements forms, we have the opportunity to create a physicians elements such as: physician ID, Specialty and finally Patient ID. Also, we can delete an element by clicking delete button. If we want to fill in the requirement forms we must press the apply changes button, in order to validate the elements.
Create Medicine Form
C:\Users\sprC\Desktop\medicine.JPG
In this create medicine form we can create Medicine names and medicine elements. More specifically, by clicking apply changes we can validate medicine elements. Finally, again in this form we are able to delete, cancel and logout.
Finally, we have the Forms SQL code in the Cd.
