Question

Now that Ned can keep track of the salespeople and the various calls they go on, he would like to begin
tracking his customers sales. Ned has indicated that a customer may place an order for the various
services his company provides and can order many different services on a single order. The orders will
need to keep track of the date the order was place, for what services and the price for those services.
Ned has indicated that the price of the service may change depending on his costs so will need to keep
track of this for each service ordered at the time of order. Ned has also indicated that his salespeople
receive commissions, so he will need to keep track of which customer orders are attributed to the
salesperson responsible for the account at the time of the sale. A salesperson may be moved to a
different account which could potentially cause commission calculation issues, so this must be
accounted for in your design.
Design the necessary Entities in order to keep track of Ned’s customers orders. Update your design
documents and make the additions to the database.

Assignment
1) Update the ERD to include the design of the new Entities. Use Visio to create the ERD. Indicate
the Required attributes on the diagram as well as primary/foreign keys. Display optional vs
mandatory relationships as well as relationship strength using the appropriate symbols.
2) State the business rule relationships of the new entities.
3) Change Ned’s Access Database to include the new entities. Ensure that any necessary referential
integrity rules are enforced between the new entities to ensure integrity of the database.

Answer 1

Question 1:

Number of entities in the diagram are :

1. Customer entity : Attributes are { Customer_ID , Customer_name , PhoneNumber, Email_id}

PK = Customer_ID

2) Order entity : Attributes are { Order_ID , Order_Date , Customer_ID, SalesPerson_ID}

PK = Order_ID

FK = Customer_ID , SalesPerson_ID

3) Services entity : Attributes are { Service_type , price , Order_ID}

PK = Service_type

Fk= Order_ID

4) SalesPerson entity : Attributes are {SalesPerson_ID , name , address ,phoneNumber , commisson }

Pk = SalesPerson_ID

5) Account entity : Attributes are { Account_number , Amount , SalesPerson_ID}

PK= Account_number

FK= SalesPerson_ID

Relationships :

• There are place relationship between Customer entity and Order entity
• There are For relationship between Order entity and Services entity
• There are Attributed relationship between Order entity and SalesPerson entity
• There are Responsible_For relationship between SalesPerson entity and Account entity

Optional vs Mandatory relationships

• Mandatory relationship Between Customer entity and Order entity
• Mandatory relationship between Order entity and Service entity
• Mandatory relationship between Order entity and SalesPerson entity
• Optional relationship between SalesPerson entity and Account entity

ERD :

Question b: Business rule

• One customer can place one or more Order. So there are 1:M relationship between Customer entity and Order entity
• One Order is for one or more service . So there are 1:M relationship between Order entity and Service entity
• One salesPerson attributed one or more order .So there are 1:M relationship between Sales Person entity and Order entity
• One salesPerson can be responsible for one or more Account .So there are 1:M relationship between Sales Person entity and Account entity

I am submit answer for first two part .If you want answer for third part then post it again Thank you.............