2.
Creating and Populating Databases
Now that you have experience running script files and SQL statements in Oracle we
now want to go on to looking at script files in more detail.
Using a script allows you to string SQL commands together. This is how you will
create and populate databases in SQL. You should always use script files to
create your databases, doing this is good practice and will enable you to be
more productive.
EXERCISE TWO
Using a text editor (such as Notepad++), load up and study the SQL commands in
[Link].
What does the script do?
[Link] creates a fairly complex database and inserts some initial data into
the various tables in your table space. The structure of the tables and how they link
together can be found in Appendix 1 of this booklet. This database is used
throughout this book.
Looking at the various table definitions in the back of this booklet, you will see that
the data types available in Oracle SQL differ from those used in Microsoft Access
and SQL Server.
Access Data Type Oracle Equivalent
Text VARCHAR2 or CHAR
CHAR is used where the same
number of characters is always
entered, e.g. CHAR(1) for a single
initial.
VARCHAR2 is used where the actual
number of characters can vary from
one record to the next, e.g. a
surname. You can also use
VARCHAR in place of VARCHAR2.
Number NUMBER(n) where n is the number
of digits required, e.g. NUMBER(3)
allows integers up to 999
NUMBER(n,m) where n is the total
number of digits and m is the number
of decimal places, e.g. NUMBER(6,2)
allows real numbers up to 9999.99
Date/Time DATE. Dates take the format DD-
MON-YYYY, e.g. 12-SEP-2006
Using this information we will look at creating a single table using Oracle.
Cindy Sherper is secretary for the Cess Village Dog Show. She wants a simple
database to hold details of the dogs entered as shown in the following table.
Dog
entry_no NUMBER(3) Primary Key
dog_name VARCHAR2(20)
dog_breed VARCHAR2(25)
dog_sex CHAR(1)
dog_dob DATE
owner_name VARCHAR2(20)
owner_phone VARCHAR2(11)
Note that in Oracle, field names may not have embedded spaces. An underscore “_”
should be used where a space would normally be required. Oracle SQL is not case
sensitive and some users only use upper case, others lower case. Standard
convention is to use uppercase for built in words like SELECT, CREATE,
VARCHAR2, and this is what we suggest you use, however we will not insist on a
particular standard for this module. If you look at [Link], keywords are in
upper case and table names/attribute names, etc. are in lowercase.
The script necessary to create this table would be
DROP TABLE dog;
CREATE TABLE dog
(entry_no NUMBER(3) NOT NULL PRIMARY KEY,
dog_name VARCHAR2(20) NOT NULL,
dog_breed VARCHAR2(25) NOT NULL,
dog_sex CHAR(1) NOT NULL,
dog_dob DATE,
owner_name VARCHAR2(20) NOT NULL,
owner_phone VARCHAR2(11) NOT NULL);
COMMIT;
‘NOT NULL’ means that data must be entered into this field.
COMMIT is a separate command that has the effect of saving the table data to
permanent storage. If you omit the COMMIT command the table will only exist until
you exit from Oracle.
DROP TABLE dog is the command to delete a table called dog. It is customary to
attempt to delete or drop a table from your table space before trying to create it. This
prevents a run time error telling you that the table already exists when you try to
create an existing table. Try to run the script a second time and observe the
difference in output on the screen.
An alternative script is shown below. Notice that the only change is how and where
the primary key is defined. This format should be used where there is a compound
primary key (as we will see later).
DROP TABLE dog;
CREATE TABLE dog
(entry_no NUMBER(3) NOT NULL,
dog_name VARCHAR2(20) NOT NULL,
dog_breed VARCHAR2(25) NOT NULL,
dog_sex CHAR(1) NOT NULL,
dog_dob DATE,
owner_name VARCHAR2(20) NOT NULL,
owner_phone VARCHAR2(11) NOT NULL,
PRIMARY KEY(entry_no));
COMMIT;
Either of these scripts could be extended to allow initial data to be entered in the
table by using the INSERT command.
DROP TABLE dog;
CREATE TABLE dog
(entry_no NUMBER(3) NOT NULL PRIMARY KEY,
dog_name VARCHAR2(20) NOT NULL,
dog_breed VARCHAR2(25) NOT NULL,
dog_sex CHAR(1) NOT NULL,
dog_dob DATE,
owner_name VARCHAR2(20) NOT NULL,
owner_phone VARCHAR2(11) NOT NULL);
INSERT INTO dog
VALUES(001, ‘Tag’, ‘Black Labrador’, ‘m’, ’27-JUL-2023’, ‘Steve Holmes’,
‘01913335555’);
INSERT INTO dog
VALUES(002, ‘Sorrell’, ‘Black Labrador’, ‘f’, ’27-JUL-2023’, ‘Steve Holmes’,
‘01913335555’);
INSERT INTO dog
VALUES(003, ‘Charlie’, ‘Great Dane’, ‘f’, ’20-JUL-2023’, ‘Kay Richardson’,
‘01918881111’);
INSERT INTO dog
VALUES(004, ‘Bonnie’, ‘Pointer’, ‘f’, null , ‘Richard Marley’, ‘01912224455’);
COMMIT;
This script will create the table, enter the four sets of data and save that information.
The easiest way of checking is to type
SELECT * FROM dog;
in the editor area.
Notice that for Bonnie, we have no date of birth information. dog_dob is not a
compulsory field (not defined as not null) so we can leave out this information,
however in the INSERT statement the keyword null must be used, otherwise Oracle
will try to save the owner’s name as the dog’s date of birth and we would get a run
time error.
We can always add extra data records to the table by writing separate INSERT
statements, however that is not very user friendly for your everyday user. Later in
the module we will look at ways of putting a user friendly “front-end” onto an Oracle
database using Oracle Forms.
The biggest problem with all of this is that it is so easy to make an error when typing
the commands. That is why it is so useful to use a text editor.
EXERCISE THREE
Write and then run the script to create the following table. Insert two rows of data as
part of your script. Remember to save your script once you have created it.
Fred Sherper owns and runs a small vehicle hire company called Sherper’s Tanks.
He wants a simple database to hold details of the vehicles as shown below
Vehicle
registration_no VARCHAR2(7) Primary Key
make VARCHAR2(12)
model VARCHAR2(10)
cost_per_day NUMBER(5,2)
last_service DATE
EXERCISE FOUR
Write and then run the script to create the following table. Insert two rows of data as
part of your script.
Louise Smales owns and runs a small riding stables. Several times a year she holds
a gymkhana for local riders and her own liveries. She is finding that keeping a paper
only record of entries is causing some mishaps with entries lost or incorrectly
entered. Louise wants a simple database to hold details of the Competitors as
shown below
(Hint: a horse can be one of three sexes! M = mare, S = stallion, G = gelding and it
is important at competitions to differentiate)
Competitor
entry_no NUMBER(3) Primary Key
rider_firstname VARCHAR2(20)
rider_surname VARCHAR2(20)
rider_dob DATE
contact_phone VARCHAR2(11)
horse_name VARCHAR2(20)
sex CHAR(1)
It is not very realistic to build a database with only one table, so now we will look at
building small databases with more than one table. The basic process is exactly the
same. The big difference comes in building the links between the tables.
Let’s consider the Cindy Sherper dog show example again. A more complete
description of her requirements is given below.
Cindy Sherper is secretary for the Cess Village Dog Show. She requires a simple
database to hold details of the dogs entered, the classes available and which dog
has been entered for which class.
The entity model for this problem and the data dictionary definition are shown below.
Dog Class Entry_Details
entry_no NUMBER(3) class_no VARCHAR2 dog_entry_no NUMBER(3) PK
PK (5)
PK
dog_name VARCHAR2 class_ title VARCHAR2 class_number VARCHAR2(5)
(20) (15) PK
dog_breed VARCHAR2 day VARCHAR2 result NUMBER(2)
(25) (3)
dog_sex CHAR(1) time VARCHAR2
(5)
dog_dob DATE ring_no CHAR(1)
owner_name VARCHAR2
(20)
owner_phon VARCHAR2
e (11)
Dog Class
Entry Details
Writing the script for each separate table should now present few problems, the
difficulty comes in making sure that the table links shown on the entity model are
created. In the script below, the parts that create these table links are shown in bold.
DROP TABLE entry_details;
DROP TABLE class;
DROP TABLE dog;
CREATE TABLE dog
(entry_no NUMBER(3) NOT NULL PRIMARY KEY,
dog_name VARCHAR2(20) NOT NULL,
dog_breed VARCHAR2(25) NOT NULL,
dog_sex CHAR(1) NOT NULL,
dog_dob DATE,
owner_name VARCHAR2(20) NOT NULL,
owner_phone VARCHAR2(11) NOT NULL);
CREATE TABLE class
(class_no VARCHAR2(5) NOT NULL PRIMARY KEY,
class_title VARCHAR2(15) NOT NULL,
day VARCHAR2(3),
time VARCHAR2(5),
ring_no CHAR(1));
CREATE TABLE entry_details
(dog_entry_no NUMBER(3) NOT NULL REFERENCES dog(entry_no),
class_number VARCHAR2(5) NOT NULL REFERENCES class(class_no),
result NUMBER(2),
PRIMARY KEY(dog_entry_no, class_number));
COMMIT;
These two extra clauses ensure that there is referential integrity maintained between
the data held in the various tables. In other words you cannot have a dog_entry_no
of 003 in the entry_details table unless there is an entry_no of 003 in the dog table.
The actual link between the tables MUST be built as part of any query that needs to
retrieve data from more than one table.
SELECT * FROM <tablename>;
This retrieves all the data from the named table. To retrieve data from linked tables
correctly you MUST include a WHERE clause in the select statement as shown
below.
To select all the results for a particular dog:
SELECT dog.dog_name, entry_details.class_number,
entry_details.result
FROM dog, entry_details
WHERE dog.entry_no = entry_details.dog_entry_no;
To select all the results for a particular dog but display the name of the class rather
than just the class number we need to link all three tables
SELECT dog.dog_name, class.class_title, entry_details.result
FROM dog, class, entry_details
WHERE dog.entry_no = entry_details.dog_entry_no
AND class.class_no = entry_details.class_number;
Because referential integrity is being maintained data must be inserted in the parent
tables BEFORE data is inserted in the CHILD table. It is also important to DROP
tables in the opposite order (think about this and why it is). Example data is shown
in the following script.
DROP TABLE entry_details;
DROP TABLE class;
DROP TABLE dog;
CREATE TABLE dog
(entry_no NUMBER(3) NOT NULL PRIMARY KEY,
dog_name VARCHAR2(20) NOT NULL,
dog_breed VARCHAR2(25) NOT NULL,
dog_sex CHAR(1) NOT NULL,
dog_dob DATE,
owner_name VARCHAR2(20) NOT NULL,
owner_phone VARCHAR2(11) NOT NULL);
CREATE TABLE class
(class_no VARCHAR2(5) NOT NULL PRIMARY KEY,
class_title VARCHAR2(15) NOT NULL,
day VARCHAR2(3),
time VARCHAR2(5),
ring_no CHAR(1));
CREATE TABLE entry_details
(dog_entry_no NUMBER(3) NOT NULL REFERENCES dog(entry_no),
class_number VARCHAR2(5) NOT NULL REFERENCES class(class_no),
result NUMBER(2),
PRIMARY KEY(dog_entry_no, class_number));
INSERT INTO dog
VALUES(001, ‘Tag’, ‘Black Labrador’, ‘m’, ’27-JUL-2023’, ‘Steve Holmes’,
‘01913335555’);
INSERT INTO dog
VALUES(002, ‘Sorrell’, ‘Black Labrador’, ‘f’, ’27-JUL-2023’, ‘Steve Holmes’,
‘01913335555’);
INSERT INTO dog
VALUES(003, ‘Charlie’, ‘Great Dane’, ‘f’, ’20-JUL-2023’, ‘Kay Richardson’,
‘01918881111’);
INSERT INTO dog
VALUES(004, ‘Bonnie’, ‘Pointer’, ‘f’, null , ‘Richard Marley’, ‘01912224455’);
INSERT INTO class
VALUES(‘wag01’, ‘waggiest tail’, ‘sat’, ’14.00’, ‘1’);
INSERT INTO class
VALUES(‘pret1’, ‘prettiest bitch’, ‘sat’, ’15.00’, ‘2’);
INSERT INTO entry_details
VALUES(001, ‘wag01’, 01);
INSERT INTO entry_details
VALUES(004, ‘wag01’, 02);
INSERT INTO entry_details
VALUES(002, ‘wag01’, 03);
INSERT INTO entry_details
VALUES(002, ‘pret1’, 01);
INSERT INTO entry_details
VALUES(003, ‘pret1’, 03);
INSERT INTO entry_details
VALUES(004, ‘pret1’, 02);
COMMIT;
EXERCISE FIVE
Write a script to create the database defined below inserting sufficient suitable data
as part of the script. Run queries to check that the tables and links have been set up
correctly.
Fred Sherper owns and runs a small vehicle hire company called Sherper’s Tanks.
He requires a simple database to hold details of the vehicles, customers and hire
record for his vehicles.
The entity model and data dictionary definition are given below.
Vehicle Customer
HireDetails
Vehicle Customer Hire Details
reg_no VARCHA cust_no VARCHAR reg_no VARCHAR
R2(7) PK 2(5) PK 2(7) PK
make VARCHA firstname VARCHAR cust_no VARCHAR
R2(12) 2(15) 2(5) PK
model VARCHA lastname VARCHAR start_date DATE PK
R2(10) 2(20)
cost_per_da NUMBER street VARCHAR end_date DATE
y (5,2) 2(20)
last_service DATE town VARCHAR start_mile NUMBER(
2(15) 6)
postcode VARCHAR end_mile NUMBER(
2(8) 6)
credit_car VARCHAR
d 2(15)
card_no VARCHAR
2(19)
expiry_dat VARCHAR
e 2(5)
EXERCISE SIX
Write a script to create the database defined below inserting sufficient suitable data
as part of the script. Run queries to check that the tables and links have been set up
correctly.
Louise Smales owns and runs a small riding stable. Several times a year she holds
a gymkhana for local riders and her own liveries. She is finding that keeping a paper
only record of entries is causing some mishaps with entries lost or incorrectly
entered. Louise requires a simple database to hold details of the Competitors, the
competition classes and the entries.
The entity model and data dictionary definition are given below
Class Competitor
Entries
Class Competitor Entries
class_n NUMBER(2) entry_no NUMBER( class_no NUMBER
o PK 3) PK (2) PK
title VARCHAR2( rider_firstnam VARCHAR entry_no NUMBER
20) e 2(20) (3) PK
entry_fe NUMBER(3, rider_surname VARCHAR entry_pai CHAR(1)
e 2) 2(20) d
rider_dob DATE result NUMBER
(2)
contact_phone VARCHAR
2(11)
horse_name VARCHAR
2(20)
sex CHAR(1)