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