Question

Database: Convert E-R diagrams to relational schema and show

• Primary Keys (using underline)

• Foreign Keys (using dotted underline)

• Referential Integrity

Convert the E-R diagrams to relational schema and show: Primary Keys (using underline) Foreign Keys (using dotted underline) . Referential Integrity Your schema should look similar to the example below. CUSTOMER CustName ORDER PROJECT Foect Title EMPLOYEE PROJECT assigecl to Tite BBli EMPLOYEE PROJECT 0 ProectTitle 4 student admitdate D,C gradstudent advisor major mimor class person name phone D,C home homeid street buyer ssn state address zip nobedrms spousename profession spouseprofess minprice bdrms bthrms maxprice ns nobaths ownoccupied commission salesprice workswith lists office agent officeid agenti agentname phone addresS worksat mgrname phone

Answer 1

If you have any doubts, please give me comment...

-- 1

EMPLOYEE(EmpID, EmpName);

PROJECT(ProjectID, ProjectTitle);

assigned_to(EmpID, ProjectID, BillingRate);

EmpID is a foreign key REFERENCES EMPLOYEE(EmpID);

ProjectID is foreign key REFERENCES PROJECT(ProjectID);

-- 2

EMPLOYEE(EmpID, EmpName);

PROJECT(ProjectID, ProjectTitle, runByEmpID);

runByEmpID is a foreign key REFERENCES EMPLOYEE(EmpID);

assigned_to(EmpID, ProjectID, BillingRate);

EmpID is a foreign key REFERENCES EMPLOYEE(EmpID);

ProjectID is foreign key REFERENCES PROJECT(ProjectID);

-- 3

EMPLOYEE(EmpID, EmpName, managerID);

managerID is a foreign key REFERENCES EMPLOYEE(EmpID);

PROJECT(ProjectID, ProjectTitle, runByEmpID);

runByEmpID is a foreign key REFERENCES EMPLOYEE(EmpID);

assigned_to(EmpID, ProjectID, BillingRate);

EmpID is a foreign key REFERENCES EMPLOYEE(EmpID);

ProjectID is foreign key REFERENCES PROJECT(ProjectID);

-- 4

student(stdid, name, gender, dob, admitdate);

undstudent(stdid, major, class);

stdid is a foreign key REFERENCES student(stdid);

gradstudent(stdid, advisor, thesistitle, asststatus);

stdid is a foreign key REFERENCES student(stdid);

-- 5

person(ssn, name, phone);

owner(ssn, spousename, profession, spouseprofess);

ssn is a foreign key REFERENCES person(ssn);

office(officeid, address, mgrname, phone);

agent(agentid, agentname, phone, officeid);

officeid is a foreign key REFERENCES office(officeid);

home(homeid, street, city, state, zip, nobedrms, nobaths, sqft, ownoccupied, commission, salesprice, ownerssn, agentid);

ownerssn is a foreign key REFERENCES person(ssn);

agentid is a foreign key REFERENCES agent(agentid);

buyer(ssn, address, minprice, bdrms, bthrms, maxprice, agentid);

ssn is a foreign key REFERENCES person(ssn);

agentid is a foreign key REFERENCES agent(agentid);

Note:

italics indicates foreign keys