In: Computer Science
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.
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 :
Optional vs Mandatory relationships
ERD :
Question b: Business rule
I am submit answer for first two part .If you want answer for third part then post it again Thank you.............