I’m working on a Database project that handles employee information. The specialization of employees are maid, driver, and security guard. I decided to for a schema for each entity set with all local and inherited attributes. And since the specialization is total, I defined generalized entity set “employee” as a “view” relation containing the union of specialization relations. The problem I have is trying to create another table for employee phone number that uses emp_number as foreign key and (emp_number, phone_number) as primary key.

Here is what I have done so far:

CREATE TABLE maid(
  emp_number CHAR(8) PRIMARY KEY,
  emp_name VARCHAR(2),
  address VARCHAR(50),
  birthday DATE
  experience_year INT);

CREATE TABLE driver(
  emp_number CHAR(8) PRIMARY KEY,
  emp_name VARCHAR(2),
  address VARCHAR(50),
  birthday DATE
  license_number CHAR(6));

CREATE TABLE maid(
  emp_number CHAR(8) PRIMARY KEY,
  emp_name VARCHAR(2),
  address VARCHAR(50),
  birthday DATE
  gun_license CHAR(6));

//Defining view of employee with general attributes

CREATE VIEW employee AS
SELECT 
    emp_number,
    emp_name,
    address,
    birthday,
    TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age,
    'maid' AS employee_type
FROM 
    maid
UNION ALL
SELECT 
    emp_number,
    emp_name,
    address,
    birthday,
    TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age,
    'driver' AS employee_type
FROM 
    driver
UNION ALL
SELECT 
    emp_number,
    emp_name,
    address,
    birthday,
    TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age,
    'security_guard' AS employee_type
FROM 
    security_guard;

I tried using the employee view to create the table:

CREATE TABLE emp_phone (
    emp_number CHAR(8)NOT NULL,
    emp_phone CHAR(11) NOT NULL,
    number_type VARCHAR(10),
    PRIMARY KEY (emp_number, emp_phone),
    FOREIGN KEY (emp_number) REFERENCES employee(emp_number)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB;

However, it gave me an error “Foreign Key Constraint is incorrectly formed. Upon research, I realized that you can’t reference a view table.

Is there a better approach in referencing ’emp_number’ for all specialization of employee?