Oracle Employee Database Case Study: Create Tables

What is the concept of this Case Study?

We are going to start with a case study that is based on SQL. The codes you will be typically Oracle friendly. So here we have Employee Data, which contains different tables :

1. Employee Table
2. Job Table
3. Department Table
4. Location Table

and all these 4 tables are linked with each other in terms of Primary Keys and Foreign Keys. 

Follow the diagram to understand how these two tables are linked:

In the above diagram Columns in pink color represents the Primary key and the yellow color represents Foreign keys. 

Now let's start with coding and creating the tables that are needed to move ahead with this case study.

1. Location table

we are first going to start with very basic and small values, just to get a clear idea about the creation of tables in a database. The location table consists of 2 columns as shown below:

1. Location_id i.e Primary key
2. City

Follow the below code to Create a Location table:

Create table Location
(
Location_id Number(20) PRIMARY KEY,
City Varchar(20)
);

Output

2. Job Table

The table holds 2 Columns. Job_id is acting as Primary Key and Designation marks to be another column of the table. 
Follow the code below to create Job table:

--Creating Job table
Create Table Job 
(
job_id Number(20) PRIMARY KEY,
Designation Varchar(20)
);

Output

3. Department Table

The above two tables were chosen to be created first because of the reason that it is not containing any linkage. For example, if we had foreign keys on the above Location table then, in that case, one might think to create a reference table first. 

Department Table has 3 Columns. Department_id appears as a Primary key, Name is the second column and Location_id column is the 3rd Column of the Department table and acts as a foreign key for Location_id in Location Table.
Follow the code to create such a table:

--Creating a Table Department
Create table Department
(
Department_id Number(20) PRIMARY KEY,
Name Varchar(15),
Location_id Number(20),
FOREIGN KEY(Location_id)REFERENCES Location (Location_id)
);

Output

4. Employee table

The table consists of 10 Columns. Those 10 columns consist of :

1. ID as a Primary Key
2. Manager_id as a Foreign key that is referring to the ID column of the same table as a primary key.
3. Job_id is the foreign key referring to Job_id in Job Table.
4. Department_id also acts as a foreign key for Department_id as a Primary key in Department Table.
5. First_Name, Last_Name, Middle_Name, Hire_Date, Salary, and Comm stand as other columns of the Employee Table.

Shown below is the code to create Employee Table:

-- Creating a table employee
Create table Employee
( 
ID Number(20) Primary key ,
Last_Name Varchar(20),
First_Name Varchar(20),
Middle_Name Varchar(20),
Job_id Number(20),
Manager_id Number(20),
Hire_Date date,
Salary Number(20),
Comm Number(20),
Department_id Number(20),
Foreign Key(Job_id)REFERENCES JOB(Job_id),
Foreign Key(Manager_id)REFERENCES Employee(ID),
Foreign Key(Department_id) REFERENCES Department(Department_id)
);

Output