|
Basic Introduction
First let us
understand the basic definitions of
elements, fields, and items, columns,
records, tables, datatypes, as well as
primary and foreign keys.
Elements,
Fields and Items
In this
tutorial, we will use the terms: elements,
fields and items interchangeably.
A record also
is the same as a row in a table.
Columns,
Tables, Records, and Datatypes
To understand
the relationships between tables, records,
columns, and datatype consider the
following.
A field is the
smallest unit in a table. A record is
nothing more than a collection of fields or
columns horizontally. A table holds a
collection of records. Now each column must
have its own datatype. A record can be added
or inserted into a table. A value in a
column can be changed and a record in a
table can be deleted.
Primary and
Foreign Keys
Relationships
between two tables are normally established
by defining primary or foreign keys.
The following
diagram shows relationship between the EMP
(Employee) table and the DEPT (Department)
table.
The DEPTNO
column in the EMP table is a foreign key and
the DEPTNO column in the DEPT table is the
primary key.
A primary key
has the immutable responsibility of serving
as a unique identifier in a table. A foreign
key is a column that refers to the primary
key of another table. To join two tables, a
�where clause� is used to set up a table
relationship between primary and foreign
keys.
Entity
Relationship/Logical data Model
"Entity
Relationship Diagram" or "Logical Data
Model" is used to establish relationships
between entities. In the following
diagram-an Entity Relationship Diagram For
Company�s Customers and Their Orders--the
relationship between the customer table and
the order table, is established by their
defined primary and foreign keys.
The foreign
key of the order table refers to the primary
key of the customer table. A customer can
have one or many orders. An order, however,
can refer to one and only one customer.
Composite
Index
A primary key
can be composed of more than one column. We
call it a composite index.
An example of
a composite index is the Item table in the
diagram. Note how the combination of two
columns in the items table was used to
create a primary key. These two columns are
"itemid" and "orderid".
You are going
to use the EMP, ORD, ITEM, CUSTOMERS,
PORTFOLIO, PRICE_HISTORY, etc tables in your
Hands-On training during the entire
tutorial.
Entity
Relationship Diagram For
Company�s
Customers and their Orders
Now, observe
the Entity Relationship Diagram for
Company�s Customers and their portfolio
carefully.
The Customers
table contains ID (customer id), first_name,
last_name, etc. The customer id (ID) is the
primary key of the customer table.
In the
portfolio table you have a composite index
that contains the stock_symbol and
customer_id columns. The customer_id column
is a foreign key of the portfolio table and
it refers to the primary key of the customer
table.
The
stock_history table contains columns such as
symbol, sales, high, low, etc. The symbol
column in the stock_history table is the
primary key. The symbol column as a primary
key will refer to the foreign key (stock_symbol)
in the portfolio table. As you can see a
table can contain many foreign keys but only
one primary key.
The price
table has a composite index. Its composite
index contains the symbol and trade_date
columns. The symbol column in the price
table is a foreign key. It refers to the
primary key of the stock_history table.
From this ERD,
you will know that a customer may have 1 or
many stocks; and a customer�s stock may have
1 or many recorded prices and so on. It is
very important for a developer to understand
the entity relationship diagram of his/her
task. It will assist the developer on
writing its SQL statement.
Study these
two entity relationship diagrams and try to
understand them. Then start to work on our
hands-on exercises.
Entity
Relationship Diagram For
Company�s
Customers and their Stocks Portfolio
Database
Remember, a
collection of all these tables under a
single or many different schemas can be
stored and maintained in a database. A
database, in effect, is a collection of
tables.
DBA vs.
Developers
The integrity,
security, connectivity, performance, and
tuning of a database will be maintained by
DBAs. One of the responsibilities of a DBA
is to plan a contingency for disaster and
ensure recovery of the database.
DBAs maintain
the integrity of a database by using "backup
and recovery". They perform Oracle
client/server connectivity and do
"performance tuning" to maximize
availability and better response time for
their clients. They may use Oracle
Enterprise Management tools to perform and
monitor their tasks.
On the other
hand developers use front-end and back-end
tools along with management tools to perform
their tasks. They develop applications to
manipulate a database�s data. Their
application will query, insert, delete and
update a record or records. They use
front-end tools such as "form builder,"
"report builder," and "graphics builder."
They use back-end tools such as "schema
builder," "procedure builder," and "query
builder." They use project builder tools to
manage and deliver their applications to
their clients.
We strongly
advise you before writing any "SQL"
statements to study first your table and
have a solid understanding of the table's
attributes and its data structure.
Now, let's
observe the employee table. We abbreviated
the employee table to "EMP." The columns in
this table are: Employee number, name, job,
manager's id, hire date, salary, commission,
and department number.
Notice that
the manager id column references to the
employee number. A manager is an employee of
his/her company. On this type of table, you
can establish a "selfjoin" condition.
Before writing
any query spend some time to understand the
structure of the table and its data. A table
contains information that describes an
entity. It has ROWS and COLUMNS.
A row is a
collection of information about a
sub-entity. Here, for example, this table
contains the company's employee information.
A table may
have a primary key. In this table, the first
column (employee number) is a primary key. A
primary key is a unique identifier for each
individual employee. A table can have a
foreign key. Here, the last column
(department number) is a foreign key. A
foreign key of a table always references to
a primary key of another table. In this
table, the foreign key references to the
primary key of the department table. A table
can have unique keys, composite keys, and
index keys. Avoid having too many indexes in
a table.
Notice, the
commission column and manager id columns
have "null values." The employee number
should not have a "null value", since it is
a primary key. Notice that the manager id
column refers to the employee number in the
employee table.
Once you
understand the nature of your data in a
table, you are ready to write a good query
against that table.
Questions:
Q: What are
the definitions of the following items?
column,
record,
table,
item,
field,
element,
primary key,
foreign key, and
datatype.
Q: What is the
relationship between primary and foreign
keys?
Q: Describe
the Entity Relationship diagram and Logical
Data Model.
Q: What is a
composite index?
Q: What are
the responsibilities of an Oracle DBA and
Oracle Developer?
Q: What is a
Database? |
