Company Database:

a-)Here is the ER Model

b-)Here is the Relational Model:

 
IMPORTANT NOTICE FOR SOLUTION TYPE:

***THIS ASSIGNMENT WILL BE SOLVED BY THE FOLLOWING THIS TYPE AND EXAMPLE!!!

SOLUTION TYPE:

SELECT   Distinct Lname, Fname

FROM       Employee, Department, Dependent

WHERE    SSN=mgrssn and ssn=essn;

SOLUTION TYPE:

SELECT   pnumber,dnum,lname,address,bdate

FROM       project,department,employee

WHERE    dnum=dnumber and mgrssn=ssn and plocation='Stafford';

EXAMPLE:

STEP# 1: CREATE TABLE dependent

( essn  char(9),

dependent_namevarchar(15),

sex            char,

bdate          date,

relationshipvarchar(8),

primary key (essn,dependent_name));

STEP #2:

INSERT INTO dependent VALUES ('333445555','Alice','F','05-APR-86','Daughter');

INSERT INTO dependent VALUES ('333445555','Theodore','M','25-OCT-83','Son');

INSERT INTO dependent VALUES ('333445555','Joy','F','03-MAY-58','Spouse');

INSERT INTO dependent VALUES ('987654321','Abner','M','28-FEB-42','Spouse');

INSERT INTO dependent VALUES ('123456789','Michael','M','01-JAN-78','Son');

INSERT INTO dependent VALUES ('123456789','Alice','F', '31-DEC-88','Daughter');

INSERT INTO dependent VALUES ('123456789','Elizabeth','F','05-MAY-67','Spouse');

STEP #3: ALTER TABLE dependent ADD CONSTRAINT dependent_essn_fk foreign key (essn)  references employee(ssn);