Oracle Database Fundamentals (
Page 1 of 5 ) 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.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 |
We have more than 10 million subscribers, so a proper database design is essential for us, otherwise it would be unmanageable.
|
/ 70 
