Consider the relational model for Bearcat Incorporated EMPLOYEE (Emp fin Emp min
ID: 3836533 • Letter: C
Question
Consider the relational model for Bearcat Incorporated EMPLOYEE (Emp fin Emp minit, Emp Iname, Emp nametag, Emp emp eHa, ame, Emp emp eten, Emp address Emp salary Emp pl name, Emp gender Emp datehired Emp etta, Emp eth) PLANT Pl p#, Pl budget, Pl name, Pl emp etta, Pl emp e#n, Pl ngrstadte) BUILDING (Bld building, Bld pl p#) PROJECT rj name, Pri location, Pr p#, Pri pl p#) ASSIGNMENT (Asg prj pH, Asg emp eHa Asg emp een, Asg hrs) DEPENDENT Dep sex, Dep brthdte, Dep name, Dep relhow, Dep emp eHa Dep emp e#n) BCU ACCOUNT (Bcu dep name cil dep relhow. cul dep emp efra Bcu dep emp efen Bcu acct type, Bcu acctif, Bcu-balance Bk emp effa, Bcu emp e#n) PARTICIPATION Par dep name ar dep relhow Par dep emp effa. Par dep emp etin, Par hob name, Par anncost, Par hrsweek HOBBY Hob name, Hob Ioact Hob giact) In addition to the primary key constraints shown in the figure, these tables contain the following constraints (i.e., business rules) PLANT Table No two plants can have the same name. Plant numbers are allowed to range between 10 and 2 inclusive EMPLOYEE Table Each employee must have a first name and a last name. Employee salaries can range between 35,000 and S90,000 inclusive. Valid genders are 'M' and F Each employee must work in an existing plant. The supervisor of an employee must be an existing employee No two employees can have the same first name, middle initial, last name, and nametag combination BUILDING TableExplanation / Answer
Employee Table:
CREATE TABLE Employee(fname char(20) NOT NULL,
miname char(10),
lname char(20) NOT NULL,
nametag char(50) NOT NULL,
emp_#a var(10),
emp_#n var(10),
address var(20),
salary int;
CONSTRAINT salary_ck CHECK (salary between 35000 to 90000),
pl_name char(15) NOT NULL,
e#a var(10),
e#n var(10),
FOREIGN KEY (pl_name,e#a,e#n) REFERENCES Plant (pl_name,e#a,e#n),
gender ENUM( 'M','F'),
datehired date,
PRIMARY KEY(fname,lname,nametag)
);
DEPENDENT TABLE:
CREATE TABLE Dependent ( sex ENUM('M','F','m','f')
bthrdte date,
name char(30) NOT NULL
relhow ENUM('spouse','mother',daughter','son','father') CHECK ((sex='M' AND relhow='father' OR 'son')AND (sex='F' AND relhow='mother' OR 'daughter'))
e#a var(10),e#n var(10) ,
FOREIGN KEY (e#a,e#n) REFERENCES Plant(e#a,e#n)
);
HOBBY TABLE
CREATE Table Hobby (hname char(10),
Ioact ENUM('I','O'),
giact ENUM('G','I')
);