Question

Database Schemas:

Please draw the schemas like the example and not just list the entities and attributes! Thanks in advance!

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 OrderDate dob admitdate D,C gradstudent advisor major mimor class person phone D,C home homeid street buyer city ssn state ssn address zip nobedrms spousename profession spouseprofess minprice bdrms ns nobaths bthrms sqft ownoccupied maxprice commission salesprice workswith lists office agent officeicd address agentid agentname phone worksat mgname phone

Answer 1

4)Ans

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

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