Vet Scenario ER Diagram 1 has M 1 makes M 1 has M Owner Pet Visit Non_Drug_Treatment 1 M M has use attends 1 1 M 1 M M has 1 Branch Staff_branch Staff Drug_prescription 1 1 M M has has have M 1 1 Equipment_stock Job Drug M 1 has has 1 M has Equipment_type M Drug_stock A pet has exactly one owner. An owner can have one or many pets. A pet may visit the vet zero, one or many times. A visit will be attended by one staff member. A visit to the vet may result in zero one or many non-drug treatment. A branch is used by one or many owners. An owner use exactly one branch. A staff member can work in one or many branches. A branch has 1 or more staff A branch has one or many equipment stock. An equipment stock is found in exactly one branch. An equipment type has 1 or many equipment stock. An equipment stock belong to exactly one equipment type. Replace above two with: A branch as one or more items of equipment. A particular type of equipment can be found at one or more branches A visit to the vet may result in zero, one or many drug prescriptions. A drug may be given from zero, one or many prescriptions. A staff has one job. A job is done by 0,1,m staff. A pet makes zero, one or many visit to the vet. Every branch has one or many drug stock. A drug stock is found in exactly one branch. A visit to the vet may result in zero or one non drug treatment.
Equipment_Branch (EquipmentNO, BranchNO, ……) Equipment_type (EquipmentNo, Description, ……) StaffBranch (StaffNo, BranchNo, ..….) Prescription (DrugNo, VisitNO, Dosage_amt ……) NDT (TreatNo, VisitNO, Description, ……) Visit (VisitNo, PetNo, StaffNO, VisitDate, ..….) Pet (PetNo, OwnerNo, Pet_Name, DOB, Status, ……) Drug_stock(DrugNo, BranchNo, Re-order_level, ……) Drug (DrugNo, Dname, Rec_dosage, ……) Job (JobNo, Job description , job_title,salary_range ……) Owner (OwnerNo, Fname, Lname, Address, Postcode, Town, BranchNo, ……) Branch (BranchNo, AreaCode, Address, Town, PostalCode….) Staff (StaffNo, Fname , Lname, Address, jobNo, ……..)