INSTRUCTIONS:

Given the information and the tables (see page 2) below, complete ERD by adding the necessary foreign keys and fully attributed relationships (minimum and maximum cardinality must be included for each relationship).

Use LucidChart.com (or an equivalent diagramming tool) to figure out assignment. Failure to do so will result in reduced points. Include your diagram in your submission.

BUSINESS RULES:

Consider the following business rules for a customer appointment system:

• A mechanic can be scheduled for many appointments, but may also not have any scheduled at all. Each appointment is scheduled with exactly 1 mechanic.
• A customer can schedule 1 or more appointments, but must have had at least one appointment. One appointment is scheduled with exactly 1 customer.
• An appointment will generate exactly 1 invoice, an invoice is generated by only 1 appointment. The invoice will be generated only after the work is complete.
• Each invoice must have exactly one customer, each customer may have one or more invoices.
• Some customers are insured by an insurance company. If they are insured, they can only have one policy. Each policy must have one and only one customer.
• One payment is applied to exactly 1 invoice, and 1 invoice can be paid off over time by several payments. An invoice can be outstanding, having nothing yet paid on it at all.
• One customer can make many payments, but a single payment is made by only 1 customer. One insurance policy can make many payments, but a single payment is made by only 1 insurance policy. Each payment will be paid either by a policy or a customer.

QUESTIONS:

FIRST create the appropriate relationships in the diagramming tool THEN answer these questions:

1. How many entities are there in this diagram and what are they?

1. What is the primary key for tblInvoice?

1. After creating the relationships, what are the attributes for entity tblAppointment?

1. What foreign key(s) did you add to tblCustomer?

1. After creating the relationships, what other entity or entities does tblMechanic connect with and which attributes are be used to make the connection(s)?

tblMechanic tblAppointment tblInvoice PK ID_Mechanic PK ID_ApptNumber PK ID InvNumber Mechanic_FName Appt_Date Inv_DateSent Mechanic_LName Appt_Time Inv Status Appt_Duration Appt_Reason tblCustomer tblInsurancePolicy tblPayment PK ID_Customer PK ID_InsPolicyNum PK ID_PmtNumber Customer_FName Ins_CompanyName Pmt Amount Customer_LName Ins_ClaimsPhone Pmt Date Customer_Street Ins_ClaimsContact Pmt Method Customer_CSZ