HR Schema

 


1. jobs

  • Primary Key: job_id

  • Attributes: job_title, min_salary, max_salary

  • Relationship: One job can be assigned to many employees (1:M with employees).

2. employees

  • Primary Key: employee_id

  • Attributes: first_name, last_name, email, phone_number, hire_date, job_id, salary, manager_id, department_id

  • Relationships:

    • To jobs: Many employees can have one job (M:1).

    • To self (manager): Employees may have a manager (self-referencing FK manager_id).

    • To departments: Many employees can belong to one department (M:1).

    • To dependents: One employee can have many dependents (1:M).

3. departments

  • Primary Key: department_id

  • Attributes: department_name, location_id

  • Relationships:

    • To locations: Each department is in one location (M:1).

    • To employees: One department can have many employees (1:M).

4. locations

  • Primary Key: location_id

  • Attributes: street_address, postal_code, city, state_province, country_id

  • Relationships:

    • To departments: One location can host many departments (1:M).

    • To countries: Each location is in one country (M:1).

5. countries

  • Primary Key: country_id

  • Attributes: country_name, region_id

  • Relationship:

    • To locations: One country can have many locations (1:M).

    • To regions: Each country belongs to one region (M:1).

6. regions

  • Primary Key: region_id

  • Attributes: region_name

  • Relationship: One region can have many countries (1:M).

7. dependents

  • Primary Key: dependent_id

  • Attributes: first_name, last_name, relationship, employee_id

  • Relationship: Many dependents can be associated with one employee (M:1).

Summary of Key Relationships:

  • 1:M (one-to-many) between:

    • regions → countries

    • countries → locations

    • locations → departments

    • departments → employees

    • jobs → employees

    • employees → dependents

  • Recursive 1:M within employees (employee-manager relationship)



CREATE TABLE jobs (
  job_id        VARCHAR2(10) PRIMARY KEY,
  job_title     VARCHAR2(50),
  min_salary    NUMBER,
  max_salary    NUMBER
);

INSERT INTO jobs VALUES ('J101', 'Software Engineer', 50000, 100000);
INSERT INTO jobs VALUES ('J102', 'Data Analyst', 45000, 90000);
INSERT INTO jobs VALUES ('J103', 'Project Manager', 60000, 120000);
INSERT INTO jobs VALUES ('J104', 'System Administrator', 40000, 85000);
INSERT INTO jobs VALUES ('J105', 'QA Engineer', 42000, 80000);
INSERT INTO jobs VALUES ('J106', 'HR Specialist', 35000, 70000);
INSERT INTO jobs VALUES ('J107', 'Business Analyst', 48000, 95000);
INSERT INTO jobs VALUES ('J108', 'DevOps Engineer', 55000, 105000);
INSERT INTO jobs VALUES ('J109', 'UI/UX Designer', 47000, 92000);
INSERT INTO jobs VALUES ('J110', 'Database Admin', 50000, 98000);
==========================================================================================

CREATE TABLE employee (
  employee_id     NUMBER PRIMARY KEY,
  first_name      VARCHAR2(50),
  last_name       VARCHAR2(50),
  email           VARCHAR2(100),
  phone_number    VARCHAR2(20),
  hire_date       DATE,
  job_id          VARCHAR2(10),
  salary          NUMBER,
  manager_id      NUMBER,
  department_id   NUMBER,
  FOREIGN KEY (job_id) REFERENCES jobs(job_id),
  FOREIGN KEY (manager_id) REFERENCES employee(employee_id),
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

INSERT INTO employee VALUES (105, 'John', 'Doe', 'john.doe@example.com', '9876543210', TO_DATE('2019-01-10', 'YYYY-MM-DD'), 'J101', 90000, NULL, 10);
INSERT INTO employee VALUES (110, 'Ayesha', 'Khan', 'ayesha.k@example.com', '9876543211', TO_DATE('2020-03-20', 'YYYY-MM-DD'), 'J102', 88000, NULL, 20);
INSERT INTO employee VALUES (115, 'Michael', 'White', 'michael.w@example.com', '9876543212', TO_DATE('2018-05-25', 'YYYY-MM-DD'), 'J103', 91000, NULL, 30);
INSERT INTO employee VALUES (107, 'Neha', 'Reddy', 'neha.r@example.com', '9876543213', TO_DATE('2021-06-15', 'YYYY-MM-DD'), 'J104', 60000, 105, 40);
INSERT INTO employee VALUES (118, 'Ravi', 'Verma', 'ravi.v@example.com', '9876543214', TO_DATE('2021-07-12', 'YYYY-MM-DD'), 'J105', 62000, 110, 50);
INSERT INTO employee VALUES (113, 'Sneha', 'Iyer', 'sneha.i@example.com', '9876543215', TO_DATE('2022-01-10', 'YYYY-MM-DD'), 'J106', 59000, 115, 60);
INSERT INTO employee VALUES (109, 'Karan', 'Shah', 'karan.s@example.com', '9876543216', TO_DATE('2022-03-14', 'YYYY-MM-DD'), 'J107', 64000, 105, 70);
INSERT INTO employee VALUES (106, 'Fatima', 'Ansari', 'fatima.a@example.com', '9876543217', TO_DATE('2023-02-20', 'YYYY-MM-DD'), 'J108', 67000, 110, 80);
INSERT INTO employee VALUES (114, 'Daniel', 'Fernandes', 'daniel.f@example.com', '9876543218', TO_DATE('2021-10-01', 'YYYY-MM-DD'), 'J109', 70000, 115, 90);
INSERT INTO employee VALUES (111, 'Pooja', 'Deshmukh', 'pooja.d@example.com', '9876543219', TO_DATE('2023-04-18', 'YYYY-MM-DD'), 'J110', 69000, 110, 100);
INSERT INTO employee VALUES (119, 'Arjun', 'Nair', 'arjun.n@example.com', '9876543220', TO_DATE('2022-11-05', 'YYYY-MM-DD'), 'J101', 65000, 105, 10);
INSERT INTO employee VALUES (117, 'Linda', 'Thomas', 'linda.t@example.com', '9876543221', TO_DATE('2022-12-22', 'YYYY-MM-DD'), 'J105', 61000, 110, 50);
INSERT INTO employee VALUES (108, 'Raj', 'Kapoor', 'raj.k@example.com', '9876543222', TO_DATE('2020-08-30', 'YYYY-MM-DD'), 'J106', 63000, 115, 60);
INSERT INTO employee VALUES (120, 'Deepika', 'Joshi', 'deepika.j@example.com', '9876543223', TO_DATE('2023-03-01', 'YYYY-MM-DD'), 'J107', 66000, 105, 70);
INSERT INTO employee VALUES (116, 'Zara', 'Sheikh', 'zara.s@example.com', '9876543224', TO_DATE('2021-09-09', 'YYYY-MM-DD'), 'J108', 68000, 110, 80);

==========================================================================================

CREATE TABLE regions (
  region_id   NUMBER PRIMARY KEY,
  region_name VARCHAR2(50)
);

INSERT INTO regions VALUES (1, 'Europe');
INSERT INTO regions VALUES (2, 'Americas');
INSERT INTO regions VALUES (3, 'Asia');
INSERT INTO regions VALUES (4, 'Africa');
INSERT INTO regions VALUES (5, 'Middle East');
INSERT INTO regions VALUES (6, 'Oceania');
INSERT INTO regions VALUES (7, 'South America');
INSERT INTO regions VALUES (8, 'Central America');
INSERT INTO regions VALUES (9, 'Caribbean');
INSERT INTO regions VALUES (10, 'Antarctica');
==========================================================================================

CREATE TABLE countries (
  country_id   VARCHAR2(2) PRIMARY KEY,
  country_name VARCHAR2(50),
  region_id    NUMBER,
  FOREIGN KEY (region_id) REFERENCES regions(region_id)
);

INSERT INTO countries VALUES ('DE', 'Germany', 1);
INSERT INTO countries VALUES ('FR', 'France', 1);
INSERT INTO countries VALUES ('US', 'United States', 2);
INSERT INTO countries VALUES ('CA', 'Canada', 2);
INSERT INTO countries VALUES ('IN', 'India', 3);
INSERT INTO countries VALUES ('CN', 'China', 3);
INSERT INTO countries VALUES ('NG', 'Nigeria', 4);
INSERT INTO countries VALUES ('ZA', 'South Africa', 4);
INSERT INTO countries VALUES ('AU', 'Australia', 6);
INSERT INTO countries VALUES ('NZ', 'New Zealand', 6);
==========================================================================================
CREATE TABLE locations (
  location_id     NUMBER PRIMARY KEY,
  street_address  VARCHAR2(100),
  postal_code     VARCHAR2(20),
  city            VARCHAR2(50),
  state_province  VARCHAR2(50),
  country_id      VARCHAR2(2),
  FOREIGN KEY (country_id) REFERENCES countries(country_id)
);

INSERT INTO locations VALUES (100, 'Kurfürstendamm 1', '10117', 'Berlin', 'Berlin', 'DE');
INSERT INTO locations VALUES (200, '5th Avenue', '10001', 'New York', 'NY', 'US');
INSERT INTO locations VALUES (300, '1 Place Charles de Gaulle', '75008', 'Paris', 'Île-de-France', 'FR');
INSERT INTO locations VALUES (400, 'Bay Street', 'M5J2N8', 'Toronto', 'Ontario', 'CA');
INSERT INTO locations VALUES (500, 'MG Road', '560001', 'Bangalore', 'Karnataka', 'IN');
INSERT INTO locations VALUES (600, 'Zhongguancun St', '100080', 'Beijing', 'Beijing', 'CN');
INSERT INTO locations VALUES (700, 'Awolowo Rd', '101233', 'Lagos', 'Lagos', 'NG');
INSERT INTO locations VALUES (800, 'Long St', '8001', 'Cape Town', 'Western Cape', 'ZA');
INSERT INTO locations VALUES (900, 'George St', '2000', 'Sydney', 'NSW', 'AU');
INSERT INTO locations VALUES (1000, 'Queen St', '6011', 'Wellington', 'Wellington', 'NZ');
==========================================================================================


CREATE TABLE departments (
  department_id   NUMBER PRIMARY KEY,
  department_name VARCHAR2(50),
  location_id     NUMBER,
  FOREIGN KEY (location_id) REFERENCES locations(location_id)
);

INSERT INTO departments VALUES (10, 'Accounting', 100);
INSERT INTO departments VALUES (20, 'Research', 200);
INSERT INTO departments VALUES (30, 'Sales', 300);
INSERT INTO departments VALUES (40, 'Operations', 400);
INSERT INTO departments VALUES (50, 'IT Support', 500);
INSERT INTO departments VALUES (60, 'Human Resources', 600);
INSERT INTO departments VALUES (70, 'Marketing', 700);
INSERT INTO departments VALUES (80, 'Logistics', 800);
INSERT INTO departments VALUES (90, 'Legal', 900);
INSERT INTO departments VALUES (100, 'Administration', 1000);
==========================================================================================
CREATE TABLE dependents (
  dependent_id   NUMBER PRIMARY KEY,
  first_name     VARCHAR2(50),
  last_name      VARCHAR2(50),
  relationship   VARCHAR2(30),
  employee_id    NUMBER,
  FOREIGN KEY (employee_id) REFERENCES employee(employee_id)
);

INSERT INTO dependents VALUES (201, 'Aryan',   'Kumar',   'Son',       105);
INSERT INTO dependents VALUES (208, 'Neha',    'Kumar',   'Daughter',  105);
INSERT INTO dependents VALUES (215, 'Riya',    'Khan',    'Spouse',    110);
INSERT INTO dependents VALUES (223, 'Ayaan',   'Khan',    'Son',       110);
INSERT INTO dependents VALUES (232, 'Zara',    'White',   'Daughter',  115);
INSERT INTO dependents VALUES (219, 'Rakesh',  'Shah',    'Father',    109);
INSERT INTO dependents VALUES (211, 'Sunita',  'Reddy',   'Spouse',    107);
INSERT INTO dependents VALUES (226, 'Mehul',   'Verma',   'Brother',   118);
INSERT INTO dependents VALUES (237, 'Fatima',  'Ansari',  'Mother',    106);
INSERT INTO dependents VALUES (240, 'Leela',   'Iyer',    'Spouse',    113);


Subscribe to: Posts (Atom)