Rookie here. I am trying to do this class project but am completely lost on creating this database with subtypes involved. The professor is of no help so here I am:
Basically i have EMPLOYEE that is the super type of COMMISSION, SALES, and MANAGEMENT. Below is what i have so far but how do i get SQL to discriminate between the 3 types of employees? How do i enter info of an employee into EMPLOYEE so that the attirbutes in the subtypes are filled in/ filtered. Sorry if this doesn't make sense, I am only 2 weeks into SQL and this stuff is hard.
Any help is greatly appreciated, please give an example is the most simple terms
CREATE TABLE EMPLOYEES(
EMP_ID NUMERIC(8) NOT NULL UNIQUE,
EMP_LNAME VARCHAR(30) NOT NULL,
EMP_FNAME VARCHAR(25) NOT NULL,
EMP_MID_INIT VARCHAR(1),
EMP_STREET_ADD VARCHAR (25) NOT NULL,
EMP_CITY VARCHAR(25) NOT NULL,
EMP_STATE VARCHAR (2) NOT NULL,
EMP_ZIP NUMERIC (5) NOT NULL,
EMP_EMAIL VARCHAR(30),
EMP_PHONE VARCHAR(15),
EMP_TYPE NUMERIC (3) NOT NULL,
PRIMARY KEY (EMP_ID)
);
CREATE TABLE MANAGEMENT(
EMP_ID NUMERIC (8) NOT NULL UNIQUE,
POSITION VARCHAR (30)NOT NULL,
SALARY NUMERIC (8) NOT NULL,
GRADE NUMERIC (10) NOT NULL,
FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEES(EMP_ID)
);
CREATE TABLE COMMISSION_BASED(
EMP_ID NUMERIC(8) NOT NULL UNIQUE,
REGION NUMERIC(2) NOT NULL,
TOTAL_REFERRALS NUMERIC(8) NOT NULL,
REFERRAL_FEE NUMERIC (8,2) NOT NULL,
FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEES(EMP_ID)
);
CREATE TABLE SALES_PEOPLE(
EMP_ID NUMERIC(8) NOT NULL UNIQUE,
GROSS_SALES NUMERIC (15,2) NOT NULL,
COMMISSION_RATE NUMERIC (8,2) NOT NULL,
BASE_SALARY NUMERIC(8,2) NOT NULL,
FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEES(EMP_ID)
);