SQLTeam.com | Weblogs | Forums

Probably a dumb question....Supertype/subtypes


#1

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)
);


#2

Can a employee be part of multiple subtypes?
If yes, then you can create a mapping table wherein you will need to have EmployeeID from Employee table and primary key from Subtable.
You must have a primary key in each of your Subtables and that Primary key will be referenced as foreign key in the mapping table.


#3

You will have a column in the Employees table that tells you which type of employee that person is, COMMISSION or SALES or MANAGEMENT.

Not sure what "EMP_TYPE" means, or if that already identifies which type. You can save a lot disk space by making it a TINYINT rather than NUMERIC(3) as long as all values are between 0 and 255. Likewise, it makes more sense to make the EMP_ID an INT rather than NUMERIC(8).

Btw, 25 chars is not enough for a street_address, it should be at least 35 (U.S.P.S. standard length).