Confusing scenario to draw an ER diagram

Question

Hi to all stack overflow members,

I am studying Entity Relationship Diagrams, in Relational Database module. we have learned about ER diagrams enough to make entities, identify attributes of each entity including primary key, foreign key, relationship between the entities and cardinality constraints.

We got an official scenario from Coventry University, and it is asking to Draw the ER diagram based on that scenario. The problem is; no one of the students got the right answer (not satisfied with the ERD), even instructors and teachers were confused about it. I answered it as what i have understand, however i am not satisfied with my answer.

This is the scenario:

Consider the following scenario modeling project management activities in an organization.

A car sales company offers to its customers the option of purchasing a car using repayment schemes of over 1 year, 2 years, 3 years, 4 years or 5 years.
A customer may purchase one or more cars under the scheme. When a customer purchases a car, he or she gets to choose one of the repayment scheme options for each purchase or may pay the whole amount in one go, meaning a repayment scheme option may be selected by one or more customers or may not be selected at all. A customer will have a unique customer number, a name, an address, and a phone number. A repayment scheme option will have a unique repayment scheme number, a name and the number of years for repayment. A car will have a unique car code, a make, a model, year of manufacture and a price.

The company pays commission on sales of cars to its salespeople and therefore it will also keep information about each sale which will include a unique sales ID and commission to be paid on that sale. The company will also want to know who made the sales and the car involved in that transaction so that the commission can be determined. A salesperson will have a unique salesperson ID, a name, a phone number and an email address.

And this is the requirements/Question: Construct the Entity Relationship Diagram (ERD) for the above given scenario. Identify all the entities, attributes of each entity including primary key, foreign key, relationship between the entities and cardinality constraints. State any assumptions necessary to support your design.

My answer:

I want to know if my answer is right or not? If not/ what is the right answer?

Thank you

Solution

Welcome to StackOverflow. Thanks for showing your own answer.

Feedback on your answer:

You're having some difficulty with keys. None of your foreign keys need to be included in the primary keys.

You're associating each customer with a repayment_scheme_number, which means a customer can select only one repayment scheme for all his purchases. I read the assignment as saying he can choose a scheme per purchase:

When a customer purchases a car, he or she gets to choose one of the repayment scheme options for each purchase

By associating each car with a customer and salesperson, a car can only be sold once, and there's no relation between a sale and the car sold.

You also didn't distinguish between entities and relationships. Foreign key constraints are not relationships, relationships in ER are implemented as tables (or relationship relations in ER terminology, as opposed to entity relations). Foreign key constraints are just integrity constraints to make sure you don't relate non-existent values.

My answer:

I used Chen's notation since it distinguishes between entities and relationships. Foreign key constraints are represented by the lines between entities and relationships.

Implemented directly, the physical model would look like this:

As you can see, this format doesn't indicate which tables represent entities or relationships, though it can be determined by looking at the primary keys of the tables. A somewhat more efficient design can be obtained by denormalizing relations with the same determinant: