Part $1$: ER Design $+$ Relational Schema $(81$ Points$)$

Requirements

a$)$

Design an ER$-$diagram for a car insurance company that implements the following requirements. The database you design should store the information about customer, car, insurance policy, payment for the policy, credit card used for the payment, and accident report.

Customer: Customers are identified by customer id$.$ For each customer, we store a name $($first name, middle name, and last name$),$ gender, age, and address $($one or more$).$ Customers own one or more cars each.

Car: Cars are identified by license number. For each vehicle, we store its brand $($make$),$ model, and year. Each car is associated with zero to any number of recorded accidents.

Policy: A policy is identified by a unique policy number. It includes information about the liability, collision, and deductible. Each policy may cover one or more cars while a car is under exactly one insurance policy. The payment of a policy may not be completed and can be split into multiple payments.

Payment: A payment is identified by the policy for which the payment was made. It consists of the payment number, the due date of the payment, the amount paid, and the date that the payment was received on$.$ A payment is associated with a credit card.

Accident: An accident is reported and identified by a unique report id$.$ It stores the date and place of the accident.

Credit Card: Credit cards are identified by their unique number. For each card, we store the expiration date. A card is associated with a customer and an address. A customer can have multiple credit cards and each card should be associated with exactly one address.

Address: An address consists of a unique address id$,$ street, city, state, and zipcode. The attributes city and state can be derived from the attribute zipcode. Some addresses may not be associated with any customers and credit cards.

b$)$

Translate the ER$-$model you created in Question to a relational schema by following the rules discussed in class Provide the relational schema as a set of SQL scripts. When defining attributes, use the proper data type. The size of the attribute is not important for this question. Your solution should include the answers for each step below.

Translate strong entities

Translate weak entities

Translate multi$-$valued attributes

Translate relationships

Show your solution.

Please do all of part a and b $($create solution for each part in b$)$ thank you