Conversion Problems

Exercise Converting ERD to Relation Tables

1.


Office(OfficeID,MgrName,Phone,Address);

Agent(AgentID,Name,Phone,OfficeID)
foreignkey (OfficeID) references Office

Owner(SSN,Name,SpouseName,Profession,SpouseProfession)

Home(HomeID,Street,City,State,Zip,NoBedrms,NoBaths,SqFt,OwnOccupied,Commission,SalesPrice,SSN,AgentID)
foreignkey (AgentID) references Agent
foreignkey (SSN) references Owner



2.


Part(PartNo,PartName);

Supplier(SuppNo,SuppName);

Project(ProjNo,ProjName);

Uses(PartNo,SuppNo,ProjNo);
foreignkey (PartNo) references Part
foreignkey (SuppNo) references Supplier
foreignkey (ProjNo) references Project

3.

Statement(StmtNo,Date,AcctNo);

StmtLine(StmtNo,LineNo,MerName,Amt,TransDate);
foreignkey (StmtNo) references Statement

6.

Agent(AgentID,Name)

Home(HomeNo,Address)

Lists(HomeNo,Agent,Commission)
foreignkey (AgentID) references Agent
foreignkey (HomeNo) references Home

7.

Project(ProjNo,ProjName)

Specialty(SpecNo,SpecName)

ProjectNeeds(ProjNo,SpecNo)
foreignkey (ProjNo) references Project
foreignkey (SpecNo) references Specialty

Contractor(ContrNo,ContrName,SpecNo)
foreignkey (SpecNo) references Specialty

ProvidedBy(ProjNo,SpecNo,ContrName)
foreignkey (ProjNo,SpecNo) references ProjectNeeds
foreignkey (ContrName) references Contractor

8.



แบบที่ 1

Customer(Customer,.....)

Employee(Empno,.......,SupEmpNo)
foreignkey (SupEmpNo) references Employee

Product(ProdNo,.....)

Contains(ProdNo,OrdNo,Qty)
foreignkey (ProdNo) references Product
foreignkey (OrdNo) references Order

Order(OrdNo,........,CustNo,EmpNo)
foreignkey (CustNo) references Customer
foreignkey (EmpNo) references Employee

แบบที่ 2


Employee(EmpNo,.....)
Manager(EmpNo,SupEmpNo)
foreignkey (EmpNo) references Employee
foreignkey (SupEmpNo) references Employee





9.


Provider(ProvNo,....)

Physician(ProvNo,.....)
foreignkey (ProvNo) references Provider


Nurse(ProvNo,....)
foreignkey (ProvNo) references Provider


Patient(PatNo,....)
Item(ItemNo,...)


Visit(VisitNo,PatNo,ProvNo)
foreignkey (PatNo) references Patient
foreignkey (ProvNo) references Physician


Visitdetail(VisitNo,VisitDetailNo,ProvNo,ItemNo,...)
foreignkey (VisitNo) references Visit
foreignkey (ProvNo) references Nurse
foreignkey (ItemNo) references Item