Question

In: Computer Science

Now that Ned can keep track of the salespeople and the various calls they go on,...

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.

Solutions

Expert Solution

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.............


Related Solutions

Scenario: A builder needs a database to keep track of contractors he hires for various projects....
Scenario: A builder needs a database to keep track of contractors he hires for various projects. So far, we have this 2NF relation, with sample data shown. Normalize to 3NF. CONTRACTOR: # ConID, Lname, Fname, JobTitle, Company, Street, City, State, Zip, CompanyPhone, CellPhone ConID Lname Fname JobTitle Company Street City State Zip Phone CellPhone 2 Garcia Mary Carpenter Construct Co 123 Main Portland OR 97204 823-1234 645-5423 14 Jones Tomas Welder Construct Co 123 Main Portland OR 97204 823-1234 344-3475...
Using c++ Design a system to keep track of employee data. The system should keep track...
Using c++ Design a system to keep track of employee data. The system should keep track of an employee’s name, ID number and hourly pay rate in a class called Employee. You may also store any additional data you may need, (hint: you need something extra). This data is stored in a file (user selectable) with the id number, hourly pay rate, and the employee’s full name (example): 17 5.25 Daniel Katz 18 6.75 John F. Jones Start your main...
How can websites keep track of users? Do they always need to use cookies?
How can websites keep track of users? Do they always need to use cookies?
We are introduced to the magnetic field as a way to keep track of the force...
We are introduced to the magnetic field as a way to keep track of the force that arises from lenght contraction in special relativity. A moving distribution of charge becomes more dense in the right frame. So in a way, the magnetic field is just the E-field. However, when I glance over the physics of permanent magnets, it is said that their magnetic field arises from the integral sum of their electrons with aligned spin(which generates a magnetic moment).Spin is...
In this assignment, the program will keep track of the amount of rainfall for a 12-month...
In this assignment, the program will keep track of the amount of rainfall for a 12-month period. The data must be stored in an array of 12 doubles, each element of the array corresponds to one of the months. The program should make use of a second array of 12 strings, which will have the names of the months. These two arrays will be working in parallel. The array holding the month names will be initialized when the array is...
Write a class to keep track of a balance in a bank account with a varying...
Write a class to keep track of a balance in a bank account with a varying annual interest rate. The constructor will set both the balance and the interest rate to some initial values (with defaults of zero). The class should have member functions to change or retrieve the current balance or interest rate. There should also be functions to make a deposit (add to the balance) or withdrawal (subtract from the balance). You should not allow more money to...
This is a simple list table of a company trying to keep track of parts that...
This is a simple list table of a company trying to keep track of parts that they sell and orders that came in purchasing those parts (in other words, not a database but a flat one table file). You will design a database for this company so that they won’t be relying on a simple 1 table list system to keep track of their data. Looking at the table below, produce the 3NF of the data. OrderNum OrderDate PartNum Description...
Write a class to keep track of a balance in a bank account with a varying...
Write a class to keep track of a balance in a bank account with a varying annual interest rate. The constructor will set both the balance and the interest rate to some initial values (with defaults of zero). The class should have member functions to change or retrieve the current balance or interest rate. There should also be functions to make a deposit (add to the balance) or withdrawal (subtract from the balance). You should not allow more money to...
A bakery does not keep track of the number of chocolate chips they put in their cookies.
  A bakery does not keep track of the number of chocolate chips they put in their cookies. The number of chocolate chips is normally distributed with mean µ and variance σ2 = 25, where µ is unknown. A customer buys a dozen of these cookies, and obtains the simple random sample 31, 23, 42, 44, 28, 34, 19, 29, 30, 25, 28, 27 (a) Compute a 95% confidence interval for µ. (b) Compute 90% and 99% confidence intervals for...
C# The Zookeepers need a system to keep track of all their animals. They need to...
C# The Zookeepers need a system to keep track of all their animals. They need to be able to enter all their animals into the system in a way that allows them to identify and locate them. This requires identifying them by species, age and one characteristic unique to their species. There are three cages and the user must input information about the animal in each one. After accepting input for all three cages, the program should output the contents...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT