everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

SQL

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19
<< Previous

Chapter # 01

Next >>


 

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?

     Reviews and Templates for FrontPage
     

Copyright � everythingOracle.bizhat.com 2006 All Rights Reserved.