Employee Performance Appraisal System

Database Program

A. Database Program WITHOUT Constraints

👉 Basic table creation (no Primary Key, Foreign Key, Check, Unique)

CREATE TABLE Department (

    dept_id INT,

    dept_name VARCHAR(50),

    location VARCHAR(50)

);

CREATE TABLE Employee (

    emp_id INT,

    emp_name VARCHAR(50),

    designation VARCHAR(50),

    salary DECIMAL(10,2),

    dept_id INT

);

CREATE TABLE Appraisal (

    appraisal_id INT,

    emp_id INT,

    appraisal_year INT,

    performance_score INT,

    rating VARCHAR(20)

);

Insert Sample Data (Without Constraints)

INSERT INTO Department VALUES

(1, 'HR', 'Mumbai'),

(2, 'IT', 'Pune'),

(3, 'Finance', 'Delhi');

INSERT INTO Employee VALUES

(101, 'Amit Sharma', 'Manager', 60000, 1),

(102, 'Neha Verma', 'Developer', 50000, 2),

(103, 'Rahul Patil', 'Analyst', 45000, 3);

INSERT INTO Appraisal VALUES

(1, 101, 2024, 9, 'Excellent'),

(2, 102, 2024, 8, 'Very Good'),

(3, 103, 2024, 6, 'Good');

B. Database Program WITH Constraints

👉 Demonstrates Primary Key, Foreign Key, NOT NULL, UNIQUE, CHECK

Department Table

CREATE TABLE Department1 (

    dept_id INT PRIMARY KEY,

    dept_name VARCHAR(50) NOT NULL UNIQUE,

    location VARCHAR(50) NOT NULL

);

Employee Table

CREATE TABLE Employee1 (

    emp_id INT PRIMARY KEY,

    emp_name VARCHAR(50) NOT NULL,

    designation VARCHAR(50) NOT NULL,

    salary DECIMAL(10,2) CHECK (salary > 0),

    dept_id INT,

    FOREIGN KEY (dept_id) REFERENCES Department1(dept_id)

);

Appraisal Table

CREATE TABLE Appraisal1 (

    appraisal_id INT PRIMARY KEY,

    emp_id INT NOT NULL,

    appraisal_year INT CHECK (appraisal_year >= 2020),

    performance_score INT CHECK (performance_score BETWEEN 1 AND 10),

    rating VARCHAR(20),

    FOREIGN KEY (emp_id) REFERENCES Employee1(emp_id)

);

Create Index (Optional but Recommended)

CREATE INDEX idx_employee_name 

ON Employee(emp_name);

Insert Valid Data (With Constraints)

INSERT INTO Department VALUES

(1, 'HR', 'Mumbai'),

(2, 'IT', 'Pune'),

(3, 'Finance', 'Delhi');

INSERT INTO Employee VALUES

(101, 'Amit Sharma', 'Manager', 60000, 1),

(102, 'Neha Verma', 'Developer', 50000, 2),

(103, 'Rahul Patil', 'Analyst', 45000, 3);

INSERT INTO Appraisal VALUES

(1, 101, 2024, 9, 'Excellent'),

(2, 102, 2024, 8, 'Very Good'),

(3, 103, 2024, 6, 'Good');

C. Explanation

Constraints are used to enforce rules on data to maintain accuracy, consistency, and integrity in the database.

1. Department Table (PRIMARY, UNIQUE, NOT NULL, DEFAULT)

CREATE TABLE Department (

    dept_id INT PRIMARY KEY,

    dept_name VARCHAR(50) NOT NULL UNIQUE,

    location VARCHAR(50) DEFAULT 'Head Office'

);

Constraints Used:

• PRIMARY KEY → dept_id
  
  

• UNIQUE → dept_name
  
  

• NOT NULL → dept_name
  
  

• DEFAULT → location
  
  

2. Employee Table (PRIMARY, FOREIGN, CHECK, NOT NULL, DEFAULT)

CREATE TABLE Employee (

    emp_id INT PRIMARY KEY,

    emp_name VARCHAR(50) NOT NULL,

    designation VARCHAR(50) NOT NULL,

    salary DECIMAL(10,2) 

        CHECK (salary > 0),

    gender CHAR(1) 

        CHECK (gender IN ('M','F')),

    join_date DATE 

        DEFAULT CURRENT_DATE,

    dept_id INT,

    FOREIGN KEY (dept_id) REFERENCES Department(dept_id)

);

Constraints Used:

• PRIMARY KEY → emp_id
  
  

• FOREIGN KEY → dept_id
  
  

• CHECK → salary > 0
  
  

• CHECK (IN) → gender
  
  

• DEFAULT → join_date
  
  

• NOT NULL → emp_name, designation
  
  

3. Appraisal Table (PRIMARY, FOREIGN, CHECK, BETWEEN, DEFAULT)

CREATE TABLE Appraisal (

    appraisal_id INT PRIMARY KEY,

    emp_id INT NOT NULL,

    appraisal_year INT 

        CHECK (appraisal_year BETWEEN 2020 AND 2030),

    performance_score INT 

        CHECK (performance_score BETWEEN 1 AND 10),

    rating VARCHAR(20) DEFAULT 'Pending',

    FOREIGN KEY (emp_id) REFERENCES Employee(emp_id)

);

Constraints Used:

• PRIMARY KEY → appraisal_id
  
  

• FOREIGN KEY → emp_id
  
  

• CHECK BETWEEN → appraisal_year
  
  

• CHECK BETWEEN → performance_score
  
  

• DEFAULT → rating
  
  

4. Insert Data (Showing DEFAULT & BETWEEN in Action)

INSERT INTO Department (dept_id, dept_name)

VALUES (1, 'HR');

👉 location automatically becomes “Head Office”

INSERT INTO Employee 

(emp_id, emp_name, designation, salary, gender, dept_id)

VALUES

(101, 'Amit Sharma', 'Manager', 60000, 'M', 1);

👉 join_date automatically set

INSERT INTO Appraisal 

(appraisal_id, emp_id, appraisal_year, performance_score)

VALUES

(1, 101, 2024, 9);

👉 rating automatically becomes “Pending”

5. Example of INVALID Data (for Viva Explanation)

INSERT INTO Appraisal 

VALUES (2, 101, 2018, 15, 'Excellent');

❌ Error because:

• appraisal_year not BETWEEN 2020 AND 2030
  
  

• performance_score not BETWEEN 1 AND 10
  
  

6. Create Index (Performance Constraint – Logical)

CREATE INDEX idx_employee_name

ON Employee(emp_name);