Question: Create an ER diagram from MS Access based on the following requirements. Show entities , relationships , and attributes . Then convert into a relational

Create an ER diagram from MS Access based on the following requirements. Show entities, relationships, and attributes. Then convert into a relational schema. Show all tables, primary key, foreign key and referential integrity lines.

Pet-Health is a pet health care business that keeps track of:

  • For each employed veterinarian: vname, unique SSN, and a unique vid (veterinarians id).
  • For each client (pet owner): cname, a unique cid (clients id), and multiple phone numbers
  • For each pet patient: pname, ptype, and a unique pid (pets id)
  • For each employed vet-technician: vtechname, unique SSN, and a unique vtechid (vet technicians id).

Additional requirements:

  • Each veterinarian has at least one and up to 25 clients (owners) assigned to him/her Each client (owner) is assigned to exactly one veterinarian
  • Each pet belongs to exactly one client (owner) Clients (owners) can have many pets (Pet Health only keeps track of clients whose pets are treated by Pet Health)
  • Each veterinarian can work with up to 5 vet-technician but does not have to work with any Each vet-technician must work with at least two veterinarians but can work with up to 10 veterinarians
  • Pet Health keeps track of the date when a veterinarian started working with a particular vet-technician
  • A veterinarian does not have to mentor any veterinarians, but may mentor up to 3 other veterinarians A veterinarian is either mentored by one veterinarian or does not have a mentor

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!