Transcript - COP4708

Managing Database With
Oracle
Replacement for Ch10
COP 4708
Introduction
• Oracle is the world’s most popular DBMS - It is a powerful
and robust DBMS that runs on many different operating
systems
• Oracle DBMS engine available in several versions:
– The Personal Edition of Oracle (is available with this text and can
also be downloaded from Oracle)
– Express Edition (the one that we downloaded and installed)
– Standard Edition
– Professional Edition
• Example of Oracle products:
– SQL*Plus: a utility for processing SQL and creating components like
stored procedures and triggers:
• PL/SQL is a programming language that adds programming constructs to
the SQL language
– Oracle Developer (Forms & Reports Builder)
Starting With Oracle
Click on Application
Express to start
working with Oracle
Starting with Oracle
You will get a login screen where you enter your
username (sys) and password (whatever the
password that you used during installing Oracle)
Create User or Use Existing
Once you login you can create a
new user workspace or use
existing workspace
Oracle User Interface
Creating Tables
Click on SQL Workshop,
Object Browser, and
from the Create Object
Menu select Table
Creating Table
Once you click on
the create table
tab you will get
this screen
Fill in the different columns
needed as indicated in the
following slide
Fill in the
name of the
table as
ARTIST from
the View
Ridge Gallery
Database
ARTIST Table
Column Name Column Type Size
NULL
ArtsitID
Number
(38,1) No
Name
Char
25
No
Nationality
Varchar2
30
Yes
BirthDate
Number
(4,0)
Yes
DeceasedDate Number
(4,0)
Yes
ARTIST Table
Once you insert the
column name, type
size and the null
constraint click next
to choose a primary
key for the table
Primary Key
Select Not Populated since
you will entering the values
your self. Then select the
primary key to be ArtistID and
click Next twice to skip Foreign
Key setup to constraints
Adding Constraint
You can choose
the type of
constraint
whether Check
or Unique
The name of the constraint
will be created Automatically
Adding Constraints
• Check Constraints:
– (Nationality In ('Canadian', 'English', 'French',
'German', 'Mexican', 'Russian', 'Spanish', 'US'))
– (BirthDate < DeceasedDate)
– (Birthdate > 1000) and (Birthdate < 2100)
– (DeceasedDate > 1000) and (DeceasedDate <
2100)
• Unique Constraint:
– Name
Adding Constraints
This is a snapshot
of all the
constraints that
have been added.
You will have 4
check and one
unique
Click Finish
then confirm
the table
creation by
clicking on
Create
Table Manipulation
The table has been created
You can
Add a
new
column
You can
modify an
existing
column
You can
rename a
column
You can
drop a
column
You can Drop
(Delete) the table
by clicking on
Drop, or You can
rename the table
by clicking on
Rename
Table Manipulation Exercise
• Add a column called MyColumn with a
data type char (20)
• Rename that column to ArtistAge
• Modify the data type to be Number (5,0)
• Drop that column
Inserting Data Into Table
To insert data into table click on
the Data tab then click Insert Row
Insert Data into table
then click Create and
Create Another
ArtistID
Use the
information
provided here
After the last
entry click
Create tab
Name
Nationality
BirthDate
DeceasedDate
3
Miro
Spanish
1870
1950
4
Kandinsky
Russian
1854
1900
5
Frings
US
1950
Null
6
Klee
German
1900
Null
8
Moos
US
Null
Null
14
Tobey
US
Null
Null
15
Matt
French
Null
Null
16
Chagall
French
Null
Null
Table Including Data
Click on
SQL
You should be able to
have the table with all
data inserted as
shown in the figure
You can count the number
of rows in the table or you
can insert more records
(rows of data)
SQL Code For Artist Table
CREATE TABLE "ARTIST"
( "ARTISTID"
NUMBER(38,1)
NOT NULL,
"NAME"
CHAR(25)
NOT NULL,
"NATIONALITY" VARCHAR2(30),
"BIRTHDATE" NUMBER(4,0),
"DECEASEDDATE" NUMBER(4,0),
This is the automated
code that has been
created by the system
CONSTRAINT "ARTIST_PK" PRIMARY KEY ("ARTISTID"),
CONSTRAINT "ARTIST_CK1" CHECK ((Nationality In ('Canadian',
'English',
'French', 'German', 'Mexican', 'Russian',
'Spanish', 'US')),
CONSTRAINT "ARTIST_CK2" CHECK ((BirthDate <
DeceasedDate)),
CONSTRAINT "ARTIST_CK3" CHECK ((Birthdate > 1000) and
(Birthdate <
2100)),
CONSTRAINT "ARTIST_CK4" CHECK ((DeceasedDate > 1000) and
(DeceasedDate < 2100) ),
CONSTRAINT "ARTIST_UK1" UNIQUE ("NAME")
)
Exercise
• Create WORK table using the following
Information
Column Name Column Type Size
NULL
WorkID
Number
(38,1) No
Title
Varchar2
25
No
Description
Varchar2
1000
Yes
Copy
Varchar2
8
Yes
ArtistID
Number
No
Exercise (Cont.)
• Assign a primary key
• Assign a foreign key remember to add the
reference table as ARTIST and the
reference column
• Add the following constraints:
– Title, copy as unique
Exercise (Cont.)
• Then insert the following information:
WorkID
Title
Description
Copy
ArtistID
505
Mystic Fabric
Opposite meaning
99/135
14
506
Mi Vida
Dark background
7/100
3
507
Slow Embers
From the artist’s originals
HC
14
525
Mystic Fabric
Some water damage
105/135
14
530
Northwest By Night
Wonderful, moody
37/50
16
Work Table Created with Data
You should be able
to get the table
shown in the figure
with populated rows
Click on Model
Database Model
Oracle screen that shows the conceptual model of
the database and the relation between the ARTIST
table and the WORK table
Table Dependencies
When you click on Dependencies
the system will list the tables that
the WORK table depends on
Now click on
Create to create
a View
Creating View
In the view name enter
ArtistNameView
Then write the SQL code
to create the view in the
query box
Viewing and Manipulating the View
The view will
be created
You can click
on Drop to
delete the
view
To look at the view or the
query that you created
click on Data and you
will see this table with
artists’ names
When click on SQL you
will get the code for
creating the view
including the query
CREATE OR REPLACE FORCE VIEW
"ARTISTNAMEVIEW" "ARTISTNAME") AS
Select Name as ArtistName
From ARTIST
Utilities
So we used the
Administration tab, the
object browser tab, the SQL
tab, how about utilities?
Click on utilities then select Data
Load/Unload then select load
Utilities
Select Load
Spreadsheet Data and
you will get this screen
Select New Table
Select Upload File
Spreadsheet file
• Before you upload the file to Oracle open
your Excel sheet that has the table
• Click save as
• Keep the file under the same name in our
case will be (Customer Table)
• Select Save as type (Text (Tab
delimited)(*.text) then save
Uploading Data from Spreadsheet
Browse to the txt file that
you just saved then set
separator \t for tab
Click Next
Creating Table In The Database
Name the table
as CUSTOMER
All columns been
loaded and data type
been recognized
Creating Customer Table
To setup the
primary key
select use an
existing column
and choose
Customer_ID
Then select Not
generated
Then Click Load
Data
Creating Customer Table
Table has been imported
To make sure of that go
to the home page click
Object Browser, browse
for tables, you will see
the customer table been
created and if you click
on Data you will see the
table populated with
data
Customer Table with Populated
Rows
Generating Object Reports
Also under Utilities
tab you can generate
Object Reports that
provides different
reports about the
objects included in
the database, such
as a report about the
different columns,
their type and their
size
Restoring Deleted Objects
Also You can retrieve
or restore object that
has been deleted by
mistake by going to
the Recycle Bin and
choosing Dropped
Objects
Then select from the
list the object that you
would like to restore
Restoring Deleted Objects
Click on Restore
Object