Oracle Database Fundamentals

In this article we will mainly focus on basic database development using Oracle. We will learn how to create new tables, alter them, insert data into the database, update data, retrieve data, delete data and drop tables. We have lots to do, so let's get started.

TABLE OF CONTENTS:

By: Mamun Zaman

Poor Best

Rating:

/ 103

May 01, 2007

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

We will start with the widely used Oracle example of an employee information database. We can store all the information in a table like the one below where each row will represent an employee, and each column will represent employee attributes.

ID	First Name	Last Name	Address	Phone	Joining Date	Salary	Dept	Manager
1	Tom	Tykwar	Avenue A	7126545	20-Apr-04	10000	Sales
2	Joel	Coen	Street 20, Avenue B	7120045	10-Mar-04	20000	Sales	Tom
3	Ethan	Coen	Street 20, Avenue B	7120145	20-May-06	5000	Sales	Tom
4	Stanley	Kubrick		7006545	2-Jan-07	20000	Marketing
5	Martin	Scorsese		7100545	12-Apr-04	10000	Marketing	Stanley
6	Tim	Burton		7126987	7-Dec-05	20000	Marketing	Stanley
7	Gore	Verbinski		7121145	7-Nov-05	12000	HR
8	Chris	Nolan		8126545	7-Oct-05	18000	HR	Gore

But if you look closely at the table above, you can see data duplication in the department and manager columns. There will be a manager in each department, and s/he will be the supervisor for all the employees of that department. This duplication of information is not a major concern for the above table. But it would be if there were a huge number of entries in this table. We can divide this table into the following two tables which will minimize the duplication of data.

Department Table:

Dept	EmpID	DeptID
Sales	1	1
Marketing	4	2
HR	7	3

Employee Table:

EmpID	First Name	Last Name	Address	Phone	Joining Date	Salary	Dept ID
1	Tom	Tykwar	Avenue A	7126545	20-Apr-04	10000	1
2	Joel	Coen	Street 20, Avenue B	7120045	10-Mar-04	20000	1
3	Ethan	Coen	Street 20, Avenue B	7120145	20-May-06	5000	1
4	Stanley	Kubrick		7006545	2-Jan-07	20000	2
5	Martin	Scorsese		7100545	12-Apr-04	10000	2
6	Tim	Burton		7126987	7-Dec-05	20000	2
7	Gore	Verbinski		7121145	7-Nov-05	12000	3
8	Chris	Nolan		8126545	7-Oct-05	18000	3