|
“To accomplish
great things, we must dream as well as act.” Anatole France
(1844 - 1924) |
Read
first then play the video:
SQL-VIDEO--Important
Installation Notes
SQL-VIDEO--Basic
Important Database Notes
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.
|
“Don't judge each
day by the harvest you reap, but by the seeds you plant.”
--Robert Louis Stevenson |
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?
|